Mapping Car Crashes in the UK
A few days ago we were astounded and relieved when a man trapped in a speeding car with no brakes for an hour managed to escape unscathed. The car, which is adapted for disabled drivers, jammed at 200km/h (125mph) and with the brakes not working, it only stopped after the last drop of gas in the tank was gone. Read the full story from the Guardian here.
Luckily, this man wasn’t hurt, but this story prompted us to think about road accidents and to look into accident data here in the UK.
With Mapsdata, we created this interactive marker map visualization of fatal road accidents in the United Kingdom in 2010. You can zoom in and out and click on a marker to get more information about a specific accident, like date and time, number of casualties and of vehicles involved, and information about the police force, weather, light and road conditions.
We believe that data visualizations like this one can be useful in many different ways. Large amounts of data can be very hard to make sense of, but that it can become much clearer and understandable thanks to visualization tools like Mapsdata. With road accidents data, visualizations could potentially help us gain a better understanding of exactly where car crashes happen and give us clues of how to prevent them.
Feel free to explore this data visualization and let us know what you think on Twitter (@Mapsdata).
Read on to discover how we created this visualization, or try for yourself:
How we created this data visualization
The first step in creating a data visualization is to find the data (click here for tips). In this case, we used data from the UK government’s open data portal data.gov.uk. The specific page we found it on is the section concerning road safety. We used the data for 2010, the most recent year.
The second step in creating a data visualization is to structure the data correctly. In this case, the original data was in CSV format but we put it in a spreadsheet, split it into columns, cleaned it up, and it began to make more sense. If you’re unfamiliar with CSV, click here to learn how to use it.
Below are two screen shots: On the left the original CSV data, and on the right the exact same data in the spreadsheet after we had cleaned it up by removing the latitude and longitude columns — which were redundant given that we had BNG coordinates, i.e. easting and northing figures — and other columns we weren’t particularly interested in.
As you can see, the data is not particularly clear. This is because, as sometimes happen with this sort of official data, information is coded. For example in the column “Police Force” we find numbers from 1 to 99. In order to understand these codes, we downloaded a separate file from the same web page, a sort of guide to the data, which contained the keys for the codes in the different columns. Sometimes such keys are also found on a separate sheet in the original data file.
As it was much better to have actual information in our columns, rather than these codes, we replaced the codes with what they meant. To do this, we used the VLOOKUP tool. (To learn more about using VLOOKUP for data visualization, check out our help section and tutorial video.)
In E and F in yellow we copied and pasted the information from the data guide, which lists all the codes and what they correspond to.
We then used D for VLOOKUP to match a code in the original column with its equivalent in the list taken from the guide file.
After completing the VLOOKUP operation, we copied and pasted the new Police Force column (filled with names instead of codes) as values to make sure not to lose them, deleted the now unnecessary columns, and moved on to repeat the process with the other coded columns like Accident severity, Day of the week, etc. We also decided to keep only fatal accidents to keep the amount of data to a more manageable level.
We then finished structuring our data by making sure the columns were labeled correctly, especially the location columns. As we used BNG (British National Grid) for geocoding, our columns needed to be titled “Easting” and “Northing”. To learn about the different types of geocoding information you can use on Mapsdata and how to use them, click here.
Finally, the structured data looked like this:
The third and last step is to actually visualize the data with Mapsdata. We simply connected to the platform, imported our spreadsheet and then chose a type of visualization.
We showed a marker map at the top of this page, but there are four different types of visualizations available, each with its own advantages. Click here to learn more about them, or read on to see the four different data visualizations possible with the accident data.
The interactive map earlier on this page is a marker map, a classic of data visualization. We can see the precise location of each entry, and interrogate each data point to display further information.
In our example, each accident is precisely located on the map, and clicking on a marker lets us see which Police force is in charge of the area, the number of cars involved and other information.
Another possible type of visualization is a heat map.
Heat maps are visually compelling and allow us to understand quickly what the concentration of data points is in every location on the map.
With the accident data, we immediately notice that the larger urban centers like London and Birmingham stand out with very high concentrations, but that the rural areas are far from accident-free too.
A cluster map, although perhaps less visually compelling, has the same advantage as a heat map, it allows us to understand where events are happening in a single glance.
It has the extra strength of displaying actual numbers, instead of a color gradient. It is also possible to interrogate the data further by clicking on each cluster.
Using a bubble map lets us display an extra value in addition to the location of each data point. In the “View” menu of Mapsdata, once we are in bubble map mode, we can choose what column/value to visualize.
With the accident data, we can make the size of the bubble proportionate to the number of vehicles involved for example, in order to spot larger accidents. And each bubble can still be interrogated to display the rest of the information.
Thanks to Mapsdata, any type of data with location information can be visualized on a map, helping you to make sense of the data you have, or to better understand public data you explore.
Let us know what you think of these visualizations via Twitter (@Mapsdata).
And of course you can create your own data visualizations on the Mapsdata online app: