Quick Investigation - Road Traffic Accidents in Milton Keynes¶

This notebook has been updated from an earlier example which has rotted over time. IT IS OF HISTORICAL INTEREST ONLY AND THE CODE MAY NOT RUN, NOT LEAST BECUAE THE DATA FILES IT RELIES ON HAVE DISAPPEARED. It includes notes showing where the original notebook had broken, and fixes made to it. In many cases, looking to web resources for assistance will turn up thimgs that are almost what you want, or not quite, or that worked once, and have since rotted. An effective practitioner will then take what they can for the resource, or if only minimal fixes are required, make them themselves and prerhaps contribute them back. That's what open practice is all about.

This example data investigation represents a preliminary exploration of road traffic accident data for Milton Keynes. Feel free to use it as a basis for a quick exploration of the data for your local area.

Note that the conversation descends into Unix commandline arcana in several places to parse some of the downloaded text files. We will not be formally teaching you how to use Unix commandline commands in this module. However, in many cases, you should be able to copy the commands and just change the bits that are obviously parameter values to use values of your own. Alternatively, you may choose to use a different technique to achieve a similar effect. As with many data wrangling exercises, there is typically more than one way to do it...

First locate some data... A quick search identifies Stats19 as a source of RTA incident data in the UK. We can find data files on data.gov.uk:

Okay, so that URL has rotted. Running a web search for something like "Stats19 data" might turn up a current source, such as https://data.gov.uk/dataset/cb7ae6f0-4be6-4935-9277-47e5ce24a11f/road-safety-data (how hard was that?).

Alternatively, we could check the Internet Archive with the zip URL, which turns up an archived copy:

Let's unzip the data and then preview it:

The accident location is given as a lat/long co-ordinate, and several administrative region codes are given as well: in particular, the corresponding LSOA - Lower Super Output Area - in which the accident occurred. This is a low level administrative geography region. (If you work with UK administrative datasets, you will find it useful to familiarise yourself with the administrative geograhy of the UK. A good place to start is the ONS guide to UK administrative geography.)

We can try find the LSOAs in the Milton Keynes administrative area by looking up the codes with a search over an LSOA catalogue file using the search term Milton Keynes. So where can we find administrative area data? How about ONS - the Office of National Statistics?

Okay, seems like the web archive doesn't have that.But if we web search with the filename (Lower_layer_super_output_areas_(E+W)_2001_Names_and_Codes.zip) we can quickly find an old data.gov.uk page that appears to have some similar data, albeit for 2011: https://data.gov.uk/dataset/c4644a20-16e0-447b-be05-7a23e8d1517d/lower-layer-super-output-areas-december-2011-names-and-codes-in-england-and-wales

Let's have a quick peek at the data...

LOOKS LIKE WE HAVE 11 IN THE COLNAMES NOT 01.

Let's do a test case on a search for Milton Keynes.

Rather than load all the data into pandas dataframes and work with it in a notebook, I'm going to do the preliminary work using some of the commandline tools provided as part of the csvkit suite.

(Feel free to try to do a similar set of operations yourself using pandas.)

csvgrep is named after the grep *nix file search commandline tool. The -c argument identifies the column we want to search in, and the -m argument determines the search phrase.

We then pipe - | - the result into the csvcut tool, which extracts the contents of the column or columns specified by the -c flag. The final two parts of the command remove the first, header line from the result (tail -n +2 - show the tail of the file starting at the second line) and then the next five.

This is where we need to take a deep breath and think clearly. What do we actually want to do with the accident dataset? We have some accident data in one file that includes a column identifying the LSOA. In another file we have a list of LSOA codes that are listed against a common name for a district and area code. If we can get a list a LSOA codes associated with Milton Keynes, then we can search the accidents file against each of these codes to get a list of accidents occurring within LSOAs associated with Milton Keynes.

csvgrep will allow us to search for rows containing Milton Keynes in the LSOA name column and extract the corresponding LSOA codes. We can then use these codes as the basis for a search for accidents occurring in those areas in the accidents dataset.

Let's grab the LSOA codes for areas that mention Milton Keynes in their name and pipe the output of the full filter search - without a header - into a new file.

We can now use the csvgrep command to search the contents of the accidents file for rows that contain the LSOA code using LSOA codes for Milton Keynes, which is to say, the codes we grabbed into a separate file.

Let's do a quick count to see how many there were:

The next step is to quickly pop these markers onto a map to have a look at them. I'm going to use the folium library.

Let's create a base map.

Now let's see if we can add the accident markers to it. We have the accident data loaded in to a pandas dataframe, so we can can use apply() to call a function for each row that will add a marker to the map.

Recalling that there were several thousand accidents, let's limit ourselves to just putting a handful of markers on the map for now to try things out!

Leaflet maps have a plugin that can automatically cluster large numbers of markers, rather than displaying them all at once, and will then unpack the clusters to individual markers as you zoom in on a region.

This means that if there is a large number of markers in a similar area, we can cluster them at low zoom levels and only reveal the individual markers as we zoom into the area to view more local detail.

Zooming in to this map, one thing I notice is how the roundabouts appear to act as focal points for accidents.

What Next?¶

Feel free to build on this investigation, or use it as the basis to carry out a similar preliminary investigation of data from your own area.

Upload your completed investigation notebook to OpenDesignStudio, either privately, or share it with other students so that you can compare notes about the techniques you used in your separate investigations and/or discuss with each other what you discovered.