Deciphering Data from the Department of Transport About Accidents in your Neighbourhood

Maze Starts Here
I’m currently attempting to write a post about how data can be used for a community campaign. I’m looking at what data is available to someone who who would like to campaign for a pedestrian crossing outside a school.

Thinking practically – where would I start if I were really going to do this –  I turned to the Department of Transport. They publish road accident data,  road safety spend, population details, road traffic data and more.

They’ve handily created a map of the road traffic accidents from their data on collisions, but I wanted to see if I could find out how that related to the area when bringing other factors into play. For instance 3 accidents in a year is a lot when traffic is generally low  – but 3 accidents in a year in a very high traffic low population area area might not be such a huge problem when considered in context.

I’ve just tried downloading their road traffic dataset, the description they give says it contains “Road traffic estimates broken up by local highways authority, vehicle type and total in miles broken up by year.” Fairly self explanatory, or so I thought. Screenshot_021315_120007_PM

Opening the excel file I quickly realise I need to do more research.

I understand that the numbers in the columns marked with vehicle type is the number of vehicles in millions that have travelled on roads in a specific location but in what location? What does ons_new_code mean? What do the S12000033 etc numbers represent?

I googled to try and find out, I quickly established that the ONS codes are codes from the Office of National Statistics that relate to place but that’s about as far as I could get without downloading other folders of documents to find what I needed from their site and I wasn’t sure which one…

So I turned back to the DfT site, and tried another download – this time looking at their data on population “Residential population data broken up by local highways authority and year.”

Now interestingly this data includes the ONS code and a local authority name so I can a least get an idea of where the codes related to…


So by looking up the local authority name on the population spreadsheet to find the ons code I was able to look up the road traffic data on the other…Or was I? I tried to test out my theory.

If the road traffic data is reported for the same 7 year period as the population data, (which it appeared to be) sorting the ONS_new_code column alphabetically on both spread sheets I should be able to compare the two side by side.

But that’s where my theory falls down. Just by looking at the number of rows on the two spread sheets you can see that the data isn’t easily comparable. There is 1170 rows of data on the population spread sheet and 1435 on the road Traffic Spreadsheet and it’s showing ONS codes that don’t exist on the other.

So it’s back to google I go to try and understand which of the ONS documents I need to decipher what the codes in the DfT data mean so I can ensure I’m looking at the correct information for the area I’m researching.

(Are you still with me?)

I’ve spent over an hour on this already – all the data I need is in front me, if only I could understand it!

Don’t worry – I won’t give up, but I just wanted to share this for anyone else embroiled in making the best use of open data to campaign around traffic, crossings, roads safety and any other term I can think of to encourage you to find this post through search.

Created under the Open Government License
Created under the Open Government License