Exploring earnings data for the UK [Open Data]

jamiet — Mon, 11 Feb 2013 13:34:02 GMT

I have a burgeoning interest in the world of Open Data which wikipedia describes as:

Open data is the idea that certain data should be freely available to everyone to use and republish as they wish, without restrictions from copyright, patents or other mechanisms of control. The goals of the open data movement are similar to those of other "Open" movements such as open source, open hardware, open content, and open access. The philosophy behind open data has been long established, but the term "open data" itself is recent, gaining popularity with the rise of the Internet and World Wide Web and, especially, with the launch of open-data government initiatives such as Data.gov.
http://en.wikipedia.org/wiki/Open_data

To that end I follow @LondonDatastore on Twitter as they are actively publishing Open Data pertaining to the city in which I live, London. Four days ago they announced 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, http://data.london.gov.uk/datastore/package/earnings-workplace-borough, 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 (which I have complained about before on this blog); the data is spread over multiple worksheets:

Worse, the data is already aggregated and pivoted:

In other words the workbook does not contain the raw row-level data from which this pivoted data is produced. Thankfully a link (https://www.nomisweb.co.uk/query/construct/summary.asp?mode=construct&version=0&dataset=99) 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:

Local Authority
Gender
Full Time or Part Time
Year (1999-2012)

and have made that raw data available in a publicly accessible Excel workbook. You can view that workbook online (only a browser required) at AnnualPay_by_YearGenderLocalAuthorityPartorfulltime.xlsx (from there you can also download for your own analysis).

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

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:

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: AnnualPay_by_YearGenderLocalAuthorityPartorfulltime.xlsx and see what other nuggets you can find! If dataviz is an area that interests you then this is a cracking dataset to explore!

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:

We have an average salary for both male and female (5000 & 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.

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.

@Jamiet

Using Pivot tables in the Office Excel Web App

jamiet — Mon, 13 Aug 2012 13:13:12 GMT

You may have noticed from my recent posts regarding Excel that I have a keen interest in the upcoming release of Office and in particular I’m interested in the capabilities of the Excel web app. One new feature that piqued my interest was that pivot tables and slicers are now displayed in the Excel Web App on SkyDrive and, even better, they are interactive (i.e. even if you don’t have permission to edit the Excel workbook you can still slice’n’dice the pivot table). I wanted to throw together a workbook that enabled me to check out these new features so I headed to my favourite free data resource, the London Datastore, and browsed through the data that they hold on the 2011 UK census, much of which is made available in Excel workbooks.

Its great that London Datastore makes this data available however I dislike the fact that, in many cases, that data is not presented in its rawest form. For example here is a screenshot of a worksheet that shows Number of non-UK short-term residents by sex, local authorities in England and Wales where the data has already been pivoted on Gender and displayed hierarchically by region yet it is not actually presented in an Excel pivot table:

Moreover, the raw unpivoted data is not available. We have effectively been barred from viewing the data in its rawest form. This effectively means I cannot easily interact with the data, we can’t even use that most basic of Excel features – filtering!

To make matters worse, some of the sub-totals are wrong. The sum for Females in Tyne and Wear (Met County) (i.e. Gatehead, Newcastle upon Type, North Tyneside, South Tyneside & Sunderland) is 2000 yet the subtotal given on the spreadsheet is 2100:

So, not only can the data not be analysed it cannot be trusted either!

I figured this could be a lot better so I pasted the leaf-level data into a new worksheet and manually manipulated it so that so that I had the raw data in a table, including Gender as a column:

From here its much easier to build a pivot table and, even better, that pivot table can be interacted with on the web using the Excel Web App. Why is that better? Simple, its because I can give you this URL: http://sdrv.ms/Nb43St and you can go and interact with it yourself; you don’t even need to use Excel, all you need is a web browser.

If you visit that URL you’ll see four worksheets:

“Information” & “Table M02” are the worksheets contained in the original workbook that I downloaded from London Datastore. “Raw data” is the table that I created manually and “Pivot” is the pivot table with slicers. Here’s a screenshot of that pivot table on SkyDrive:

From there you can filter, slice, sort, drill-down and do most of the other stuff that you’re used to doing using pivot tables – if you want more functionality (such as drillthrough, drag-and-drop dimensions between axes) simply download your own copy of the workbook and play around at will in Excel. Notice also that the sub-total for Females in Tyne and Wear (Met County) is, of course, correctly given as 2000, not 2100.

Pretty cool, no? Finally one can interact with Excel pivot tables on the web and, best of all, those workbooks can be shared with the entire world – I have been waiting years for this. Sure, web based spreadsheets have existed for a long time but this is the first time (that I know of) that Excel pivot tables work seamlessly in a web browser and given the amount of Excel workbooks out there, that is a big deal. Click through, play around with the pivot table and slicers to your heart’s content and let me know your thoughts in the comments below.

@Jamiet

Search results matching tag 'London Datastore'

Exploring earnings data for the UK [Open Data]

Using Pivot tables in the Office Excel Web App