Link postcode with constituency

TL:DR

I was impressed by the Democratic Dashboard website recently. You pop in your postcode and it tells you lots of info about your electoral constituency, the demographic make up, as well as the political history.

I was thinking that it would be interesting to link postcode with that kind of information for other purposes, but want an automated way of doing it. Therefore, I took advantage of some of the governments vaunted open data to link between postcode and constituency (I can’t find an existing lookup with this already in). Following a process suggested (but not implemented) here.

First, I downloaded the Office of National Statistics postcode directory (search ONSPD here), which links postcode with [pcon], a code for the parliamentary constituency. Then, I looked up which parliamentary constituency that code refers to using the code history database, also from the stats portal above. Then, I just printed out a list of all the postcodes in the UK (guess how many there are…. 2.5 million!), and the constituency that postcode is in. See this git repo. There is a lot of rows, so you are going to need something better than Excel to analyse it.

The next step is to download the demographic data from Democratic Dashboard and then will have a link from every postcode (actually 2.54 of the 2.56 million, lost a few along the way) to the demographic info for that constituency.

Ok, so it isn’t exactly person-level data (constituencies generally have 50-90 000 people) but if you have a geographically distributed outbreak of Salmonella across lots of difference constituencies, it might help with your hypothesis generation if you can see whether it seems to be affecting more or less affluent areas.

Advertisements

2 thoughts on “Link postcode with constituency

  1. Great information, thanks! That’s helped a little charitable project I’m helping with.

    Given I was only looking for a single column of data (Postcode mapped to Constituency), I actually could still get it to work in Excel. It makes the file 35Mb in size just with the 2.5mil rows of two columns (actually three sets of two columns with a little under a million rows in each of the three) and the VLOOKUP then just does =IFERROR(VLOOKUP[on list one],IFERROR(VLOOKUP([on list two],VLOOKUP([on list three]))), which does actually work and still just about opens and saves on my (newish and goodish but not top-of-the-range) laptop with Excel 2013.

    But yes, that’s not really ‘analyse it’ so you’re right, adding much more than that would probably kill Excel 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s