Matching MP's to addresses using postcodes

A database that can hold over two million records within a table.

Microsoft Excel or an equivalent piece of spreadsheet software for basic editing.

You will need the most up to date Office for National Statistics Postcode directory file. This is downloaded from the Office of National Statistics website, located at:

https://geoportal.statistics.gov.uk/

Search for file 'ONSPD'.

Also on the same website, download the latest copy of 'Westminster parliamentary constituencies names and codes' data. This is displayed on the site as a web table and is available as a downloadable CSV file.

You will also need a current list of MP's and their Parliamentary Constituencies from:

http://www.parliament.uk/mps-lords-and-offices/mps/#nav-A

This is not downloadable as a file, but the data can be copied from the website. At time of writing the site is being altered and awaiting the results of an election.

The Office for National Statistics Postcode Directory relates both current and terminated postcodes in the United Kingdom to a range of current statutory administrative, electoral, health and other area geographies.

The data we need for this exercise is postcode data and the field 'PCON' which contains the relevant Parliamentary Constituency code for each postcode in the UK.

The data in the ONS Postcode directory file is around 200MB in size in ZIP format and the data is presented in three different parts:

• The complete dataset in CSV format
• The complete dataset as a large text file
• The data split into 124 individual CSV files based on postcode

Additionally there is a user guide and a documents folder containing a number of other CSV and XLSX files.

Caution: Don't try to open the large CSV file by double-clicking it to open in Excel , as it will not open as it contains too many records.

Note that in the source CSV data, each field that contains text is wrapped in quotes. There are no commas within these fields, so it is safe to remove the quotes.

If you have a problem removing the quotes when importing the data, a workaround is to split the large file into smaller manageable chunks using external software and then remove the quotes. Splitting into nine files will give you batches of 300,000 records or so which is easy enough for Excel to cope with.

Free software is available to split the data, such as CSV Chunker at:

http://www.scaled-solutions.com/blog/open-source-csv-file-splitter

Once the file is split into smaller chunks, the fastest way to remove the quotes is to open each split CSV file in Excel and re-save the file as a CSV file. This is an almost instant process and is much quicker than trying to remove quotes using a text editor. This will also reduce the file size of the CSV files and should speed up the import process when you import the data into your database.

The fields that are important for the exercise are PCON, which contains the parliamentary constituency code and the postcode field.

Although the data is also split into 124 files, I wouldn't advise using these individually unless you have a specific purpose in doing so as 124 files is far too cumbersome to work with.

This is a relatively small file at 650 records plus header and contains only three fields:

• PCONnnCD - code containing 9 characters that matches up to the PCON field in the ONS Postcode Directory
• PCONnnNM - Full name of the corresponding parliamentary constituency
• FID - [ID] Integer

Be aware that there is a file within the ONS Postcode Directory that contains a list of Westminster Parliamentary Constituencies. At the time of collating this page, it is identical in content to the data in this section without the FID field, but it also contains an additional two records, which are:

Code: L99999999 - Name: (pseudo) Channel Islands
Code: M99999999 - Name: (pseudo) Isle of Man

The Channel Islands and the Isle of Man do not have an MP at Westminster, so it would appear that this data is here as a placeholder. Therefore it is less confusing to use the separately downloaded file.

The link given lists all 650 MP's:

http://www.parliament.uk/mps-lords-and-offices/mps/#nav-A

At time of writing, the site is both being altered and awaiting population with the results of a general election.

It was previously possible to copy and paste all data from one page, but the changes to the site in late 2019 has resulted in the data being split across multiple sub-pages using pagination.

When the page is completed, Wikipedia should have a current list of MP's, parties and constituencies. However, this is not an official document.

https://en.wikipedia.org/wiki/List_of_MPs_elected_in_the_2019_United_Kingdom_general_election

Care should also be taken once time has elapsed since the election in case there has been a mid-term by election, although changes have historically been listed further down the page.

It is recommended that you copy the data from the Westminster parliamentary constituencies that you previously downloaded into an Excel workbook.

Copy the names and respective political party into the workbook against the name of the constituency.

Use VLOOKUP or INDEX/MATCH to match up the parliamentary code/parliamentary name data onto your MP list that you have just created. This will not only save you having to use a separate table in your database, but it will highlight if there are any errors with your data cleansing and/or highlight any inconsistencies with how constituencies are spelt.

When your data is complete, it can be saved as a CSV file.

You can now load the three data sets into your database software(this will be two data sets if you have merged the MP data to the constituency data).

Link your data to the ONS Postcode Directory by the postcode field, then link the PCON field to the PCONnnCD field in the Parliamentary Constituency data to find the full name of the constituency.

From this constituency field, you can now match against the constituency name in the compiled MP data to find the corresponding MP name and political party.

Updating the MP's list in future

There is a list on Wikipedia of current MP's and the parties they belong to. If MP's change mid-term to become independent or if there are by-elections held, the latest changes can usually be found towards the bottom of the page. It is also organised into date order, so will save you searching and comparing existing records.

Consult this list for changes if you need to repeat the process. This can prevent you from having to save and edit a new list of MP's as you will only need to make minor changes to your existing data rather than starting from scratch.

https://en.wikipedia.org/wiki/List_of_MPs_elected_in_the_2019_United_Kingdom_general_election

Note that this link will change following a new general election.

MP contact details

The parliament website contains information on other methods to contact MP's, such as their official email address, social media details etc. At the time of compiling this document, this information is not listed on the main page and requires a further link to be clicked on for each MP to access. Official email addresses do seem to follow the same format.

Mapping your data

The ONS Postcode Directory data also contains Eastings and Northings data which is very useful for mapping data. This data can be converted to latitude and longitude values which can be loaded into mapping software. Basically this allows you to plot your customers on a map via their postcode. Just match your customer postcode with the ONS postcode to return the Eastings and Northings data. Then convert these values to latitude and longitude co-ordinates to save in your source data.