Pollster pollster, what do you know?

It's been quite a while since I put something on Tableau public. Once I saw this I couldn't resist. I can't quite get the trend line right, but let that be an exercise for the reader :)

data @peterborough.gov.uk

Following on from the talk from a Peterborough council employee in Big Data world, I had a look for their open data. They have an impressive portal at http://data.peterborough.gov.uk/ , the platform in use (Data Share) is developed by the London borough of Redbridge.

I chanced upon the topical dataset of split of social and affordable housing allocations between British nationals, Europeans and other foreigners. This is of special interest not only because the issue of EU citizens benefits has been at the centre of the Brexit debate, but also because Peterborough has been singled out in a couple of newspaper articles as a place that helps understand the brexit vote (already from the last general election, following the UKIP referendum campaign, and in the aftermath).

Data share has a funny start page where you follow one link to view data and another to download it, each leading to categories of datasets, and each category's link to the list of datasets in the category. Quite a lot of clicking through, especially if you first view and then decide to download. Thankfully there is actually a download button in the viewing area.

Viewing the data shows a table but gives some other options, the interface reminds me a bit of the built in visualisations in the Zeppelin Notepad.

This doesn't live up to the promise though, try any view other than the table and what you get is a visualisation of the number of records in the table per value of the dimension selected for the category axis, not even fitting in one page and not in chronological order either!

It does look like the Peterborough data and the Data share platform haven't been tuned to work with each other when it comes to visualisation. Changing the dimension for the category axis becomes even more revealing:

So this is a bit like the modal value of my CPD hours :) . I have a visualisation telling me that there are two records/quarters in the dataset for which there were 197 houses allocated to british nationals, as opposed to having only one quarter for which 200 houses were allocated. This might interest a conspiracy theorist with numerology fixations, but is far from insightful. Downloading the data and playing with it even in excel can get you a bit further, though the 'Apr-Jun 10' format is not great. Instead we load into tableau, split the period and modify the year to four digits:

'20'+TRIM( SPLIT( [Period], " ", 2 ) )

We also pivot the various nationalities to give a more tableau friendly format

 The remaining issue is the occasional 0-3 value, which might be there to 'anonymize' the dataset rather than single out the hypothetical one Czech family that got a council house in a particular quarter of a particular year. Change the data type to number(whole) which is fine with all other values and gives null for the 0-3 and then add another calculation

ifnull([Pivot Field Values],3)

I could have used zn and turned them to zeros, but if the hypothesis we are investigating is 'the bad immigrants take all the houses' we need to take the 'worst' case scenario. Now the total number of houses allocated fluctuates so looking at percent of total allocations with the table calculation computed at the cell level (i.e. percent of the total for the quarter) we can see how the relative percentages of nationalities fluctuate. As the nationalities are already partially grouped, I group the eastern Europeans in with the rest of the EEA nationals. I also stick in one crucial number from the UKIP campaign Grauniad article: 79.4% of Peterborough's population were born in the UK. Of course there are plenty of British nationals not born in the UK, but lets use what numbers we have at hand.

The end result shows that the percentage of British nationals getting social and affordable housing hovers just under the percentage of British born people in Peterborough. I can guess here that if you are rich or at least well off in Peterborough you are more likely to be British (an assumption that wouldn't necessarily hold in London), so we really need the percentage nationalities of people who can't afford market rent in Peterborough. But in any case it shows that things are not as bad as the benefit tourist story wants you to think. After all many of the EEA nationals in council housing could be working in the warehouses mentioned in the more recent Grauniad article. Which boils the question down to why does the British government not enforce a living wage more strictly, if the benefit seekers are a drain on the national finances.

Aggregating and propagating field values across asynchronous sources

That was a title full of made up jargon, not even standard database/tableau jargon! Hopefully an example will make it clear.

First of all, what do I mean by asynchronous sources data? It is the sort of data that comes from multiple sources, not at the same times (often from separate files/tables, after a union). A screenshot as usual is worth a thousand words.

 In this example let's assume we have a ship that reports its GPS coordinates every half hour, and the energy consumption on board every minute (it's fitted with a smart meter!). But what if we want to come up with energy usage for each position? The easy solution is to come up with a calculated 'half hour time' that truncates the original timestamps to half hour

left([Time],3)+if int(mid([Time],4,1))<3 then '00' else '30' end

 throw that on the level of detail, the x and y on rows and columns, and sum(E) will do the right thing. All well and good. There's the issue of truncating rather than rounding the time, and not interpolating intermediate positions but let's assume that level of accuracy is not important for this.

But what if the position is not consistently every half hour, what if it is less often when it moves slow, and more often when it moves fast? Then, assuming we still don't need to interpolate intermediate positions, we can avoid aggregation and instead try and fill in the null Xs and Ys using table calculations:

ifnull(attr([X]),previous_value(attr([X])))

This is a more robust method for non-regular interval reports, we can even copy paste it into a new sheet as a clipboard source if the table calculations are an issue.

Using the pivot with contracts

2018.1 Update

The contracts dummy data is quite typical of certain troublesome datasets, so I will demonstrate some less boring uses for it.

The Gantt chart is great for visualising but not very useful for doing any further calculations. With this sort of dataset to go any further you need to select the start and end dates and pivot.

The result of the pivot is to put all the dates in the same column, and add a second column telling you whether they are a start or an end date.

So now we can visualise both start and end without a calculated field as they are both on the same date axis. Much more useful though is the following calculated field which I'll call 'increment':

if [Pivot Field Names]='Start Date' then 1 elseif [Pivot Field Names]='End Date' then -1 end

A running sum of increment tells us at any given point the number of 'live' simultaneous contracts.

So finally, here's the visualisation: