Search results matching tags 'excel' and 'dataviz'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=excel,dataviz&orTags=0Search results matching tags 'excel' and 'dataviz'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Exploring earnings data for the UK [Open Data]http://sqlblog.com/blogs/jamie_thomson/archive/2013/02/11/exploring-earnings-data-for-the-uk-open-data.aspxMon, 11 Feb 2013 13:34:02 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47637jamiet<p>I have a burgeoning interest in the world of Open Data which wikipedia describes as:</p> <blockquote> <p><em><b>Open data</b> is the idea that certain </em><a href="http://en.wikipedia.org/wiki/Data"><em>data</em></a><em> should be freely available to everyone to use and republish as they wish, without restrictions from </em><a href="http://en.wikipedia.org/wiki/Copyright"><em>copyright</em></a><em>, </em><a href="http://en.wikipedia.org/wiki/Patent"><em>patents</em></a><em> or other mechanisms of control. The goals of the open data movement are similar to those of other &quot;Open&quot; movements such as </em><a href="http://en.wikipedia.org/wiki/Open_source"><em>open source</em></a><em>, </em><a href="http://en.wikipedia.org/wiki/Open_hardware"><em>open hardware</em></a><em>, </em><a href="http://en.wikipedia.org/wiki/Open_content"><em>open content</em></a><em>, and </em><a href="http://en.wikipedia.org/wiki/Open_access_(publishing)"><em>open access</em></a><em>. The philosophy behind open data has been long established</em><em>, but the term &quot;open data&quot; itself is recent, gaining popularity with the rise of the </em><a href="http://en.wikipedia.org/wiki/Internet"><em>Internet</em></a><em> and </em><a href="http://en.wikipedia.org/wiki/World_Wide_Web"><em>World Wide Web</em></a><em> and, especially, with the launch of open-data government initiatives such as </em><a href="http://en.wikipedia.org/wiki/Data.gov"><em>Data.gov</em></a><em>. <br /></em><a title="http://en.wikipedia.org/wiki/Open_data" href="http://en.wikipedia.org/wiki/Open_data"><em>http://en.wikipedia.org/wiki/Open_data</em></a></p> </blockquote> <p>To that end I follow <a href="https://twitter.com/londondatastore" target="_blank">@LondonDatastore</a> on Twitter as they are actively publishing Open Data pertaining to the city in which I live, London. Four days ago they <a href="https://twitter.com/londondatastore/status/299543409553711104" target="_blank">announced</a> that a new dataset had been released that provided earnings information, not just for London but for the whole country and going back many years too. The provided link, <a title="http://data.london.gov.uk/datastore/package/earnings-workplace-borough" href="http://data.london.gov.uk/datastore/package/earnings-workplace-borough">http://data.london.gov.uk/datastore/package/earnings-workplace-borough</a>, brings up a page from where one can download an Excel workbook containing some data. Unfortunately the data in that workbook is not, in my opinion, provided in such a manner that makes it easily explorable (<a href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/08/13/using-pivot-tables-in-the-office-excel-web-app.aspx" target="_blank">which I have complained about before on this blog</a>); the data is spread over multiple worksheets:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_3BDA8261.png"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_5B1D2934.png" width="383" height="37" /></a></p> </blockquote> <p>Worse, the data is already aggregated and pivoted:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_330A7A15.png"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_66663D71.png" width="577" height="138" /></a></p> </blockquote> <p>In other words the workbook does not contain the raw row-level data from which this pivoted data is produced. Thankfully a link (<a title="https://www.nomisweb.co.uk/query/construct/summary.asp?mode=construct&amp;version=0&amp;dataset=99" href="https://www.nomisweb.co.uk/query/construct/summary.asp?mode=construct&amp;version=0&amp;dataset=99">https://www.nomisweb.co.uk/query/construct/summary.asp?mode=construct&amp;version=0&amp;dataset=99</a>) is provided from where the raw data can be downloaded. I found the interface there to be slightly clunky but that’s a minor quibble – that raw data is available should be considered a major boon. I downloaded earnings data for:</p> <ul> <li>Local Authority </li> <li>Gender </li> <li>Full Time or Part Time </li> <li>Year (1999-2012) </li> </ul> <p>and have made that raw data available in a publicly accessible Excel workbook. You can view that workbook online (only a browser required) at <a href="http://sdrv.ms/VPvjcD" target="_blank">AnnualPay_by_YearGenderLocalAuthorityPartorfulltime.xlsx</a> (from there you can also download for your own analysis).</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_6521A492.png"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_6E9A6308.png" width="634" height="372" /></a></p> <p>The workbook also provides summaries over the raw data by way of pivot tables and charts. Arguably its clear to see, for example, that a gender imbalance exists although perhaps the gap may be lessening somewhat</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_088E232B.png"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_112E7BB7.png" width="904" height="470" /></a></p> <p>Its interesting to note that the average salary for Males dropped off in 2009/2010. Perhaps the economic events of 2008 are the cause for that, checking out data for only City of London (the traditional financial hub of the UK) suggests that may well be the case:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5666D2E0.png"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_662667E4.png" width="901" height="441" /></a></p> <p>There will be many more nuggets of information available in this data, all we need to do is set the data free so that people can find it for themselves. That is the aim of this blog post so hit the link: <a title="http://sdrv.ms/VPvjcD" href="http://sdrv.ms/VPvjcD" target="_blank">AnnualPay_by_YearGenderLocalAuthorityPartorfulltime.xlsx</a> and see what other nuggets you can find! If <a href="https://twitter.com/search?q=dataviz" target="_blank">dataviz</a> is an area that interests you then this is a cracking dataset to explore!</p> <hr /> <p>One other point I want to make is that the raw data is provided as a mean average which means that each aggregated figure is a mean average of some mean averages. This isn’t good as it distorts the data as I demonstrate with this simple example:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_0C1E8DDA.png"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_0B4627F0.png" width="384" height="82" /></a></p> <p>We have an average salary for both male and female (5000 &amp; 9000 respectively) and the average of those two averages is 7000. However if we take the total “total salary” / total “tally of people asked” (127000 / 23) then the average is quite different – 5521.73.</p> </blockquote> <p>That’s a large discrepancy even for only two rows of data and it highlights the problem of providing averages rather than the figures that created those averages. To me this is a data quality issue – the raw source data does not provide the requisite level of detail to enable accurate analysis. Quality of data is paramount.</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p>