Mashing up electoral data – follow on from oUseful post

I just saw a great post by Tony Hirst on his oUseful blog where he nicely laid out how to use Open refine plus various APIs to research which candidates were standing for election in the wards in which they live. This kind of mashup of various tools is something I cannot resist playing around with, so I ‘borrowed’ his scraper wiki data to show how to do the same thing in one shot in Excel. I gave myself a time limit of 2 hours to see how much I could get done, including writing it up.

This is pretty straightforward since I already have all the raw material I need from previous posts concerning the rest/Excel library. I’ve pushed it all in one module so the process is automated from start to finish,

First – the scraperwiki data.

Tony has already created a database which i’m going to pick up using a scraperwiki query – the same one I posted about on Excel Liberation some months ago. Here’s how to do that.

 

Now we have an excel sheet with all that scraper wiki data loaded – it starts like this

 

Extract the postcodes and add a couple of extra columns

You’ll notice that I’m not really accessing any Excel ranges here. That’s because the cDataSet class handles all that drudgery for me. All I have to do to add a couple of columns, and extract out the postcode from the address is this.
 

See if the address is in the ward

mySociety API can take a postcode and return all sorts of government classifications, so we need to to create a restlibrary entry for that API. It looks like this

   

 

Lookup mySociety and extract ward name for given postcode

Now we’ve defined the API, we can just reference it and let it know which column contains the post code to analyze. The data returned contains the name in an area object of type “UTE”, so it’s just a question of looking at the UTE entry returned by the API for the postcode and comparing it to the ward

 

Results

 

There were a couple of missing postcodes, so it needs cleaned up, but here’s a quick chart of the results.

Next steps

In his post, Tony went on to do some mapping using Google Fusion Tables and Google Maps. I planned to use Excel to Google Earth/Maps vizApps to create an earth representation of this data, and also to make a Google Apps Script restlibrary app – but I only gave myself a couple of hours and time’s up. I’ll maybe get round to those in a further post – I already have all the components so it’s a small job.

Complete code

You can find the code by downloading the cDataSet.xlsm workbook from Excel liberation. It’s in the scraperWiki module.
About brucemcp 223 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

Be the first to comment

Leave a Reply

Your email address will not be published.


*


14 − 13 =