Add GSS codes to a CSV

We work with a lot of CSV files with data about wards, constituencies or local authorities. Occasionally I'll get one that contains a column of names but doesn't include the GSS codes for them. Written names can often be tricky to use in code because there are multiple ways people might refer to "Bristol" e.g. "Bristol", "City of Bristol", "Bristol, City of", "Bristol, City Of" etc. Having the GSS codes makes things a lot less ambiguous and easier to work with.

Over the years I've created a bunch of ad hoc fixes to add the GSS codes to CSV files. Those ad hoc fixes help me but not others. So I thought it was about time I made a simple web-based tool that can be used by anyone.

Our Add GSS Codes tool will let you load a CSV file and display it as a simple table in the page. You can select a column that either contains UK ward names, UK constituency names, or UK local authority names and then use that to create another column containing the appropriate codes. It should be very quick to do.

Having created a simple CSV editing tool to do this, I thought I'd also add some other features. It isn't meant to be a full-on replacement for Excel or Google Sheets so these extra features are limited:

• every cell is editable so it is easy to fix any typos or mistakes;
• quickly remove empty rows or selected columns;
• convert a column of British date format dates into ISO 8601 dates.

I've kept the initial page load to around 35kB (70kB uncompressed). Depending on the type of geography you pick it will also need to load in some JSON files (247kB for wards, 15kB for local authorities, or 28kB for Westminster constituencies). If you load a large CSV file the page may get a bit slower as it has to create a large, editable, table.

I think most people who would want to use this tool are on desktop/laptop computers but it should also function on a mobile device if you want to add GSS codes on-the-go.

The data behind the tool

To get this tool to work I needed both names and codes for various UK geographies. To do this I took the ONS's CodeHistory database (July 2024), along with our own supplemental list of alternate names, to create a bunch of JSON files for each three-character prefix e.g. E08, S14, W05 etc. The tool loads in the appropriate JSON files for the geography you select and then tries to find matches by name and date.

Some geographies have non-unique names. For instance, there are a lot of "Park" wards in the UK. The tool tries to disambiguate these by looking at the parents of uniquely named wards and preferentially using the wards within those areas. So, if you had a CSV file with wards from Calderdale, it would note that Calderdale was the parent of all the wards and use that to select the correct "Park" ward.

All these JSON files are much more compact than the original 57MB file so may be useful for other projects. They are published in our Github repo for the tool.

This is an experimental tool so please report bugs if you find them. I was able to spend time on our mission led projects like this due to the support of our sponsors who help to fund our #RadicallyOpen work - a big thanks to all of them.