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


2 thoughts on “Deciphering Data from the Department of Transport About Accidents in your Neighbourhood”

  1. Hi Steph I think you have got it but the codes you highlight simply refer to the each individual authority. Its done in codes so that it is easier to see geographical hierarchy (basically the different levels of boundaries) which you cant do with names (as they sometimes change or are spelt wrong etc) A good way to join and compare data from two different sources is by using a “Vlookup” function in excel. This sounds complicated but can be used with a bit of guidance in most common versions of excel. Sadly the world of open data will always mean some things are missing. So you have to work with the best you have available.

    Its not a great site – but this beginners guide to ONS geography is worth investing the time in:

  2. Hi Steph.

    I imagine most councils work in a similar way and actually publish details of requests for crossings or traffic calming and give data on RTCs and so on that then show how they come up with a prioritised list of where they will do works. Obviously the fact that there ain’t a fat lot of money coming from government means not much will be done.

    Here is an example, see appendix C. Sorry, its PDF, but obviously the info could be provided in another format on request. Open data has its uses, but what it doesn’t have, as you’ve identified, is the benefit of (a) the data having been analysed by someone who understands it, (b) some indication of whether the data indicates that action may be warranted/undertaken and (c) the people who’ve written it actually know the area, so will descrive possible actions in a way that local people can understand.

    And no, such reports won’t include stats for every road in a borough, But, if they haven’t made it on to the radar then frankly, there’s very little chance that a campaign would get anywhere anyway.

Leave a Reply

Your email address will not be published. Required fields are marked *