This week I found this beautiful visualisation in ‘London: The Information Capital’ about crime in London. A great subject and a great viz, but I knew it would be challenging to build in Tableau. So I gave it a go.
I knew where I needed to start. I needed the data for crimes in London, and I needed a shape file of London.
Well the police forces of England, Wales and Northern Ireland have recently made a huge push towards providing open data. They have created a site where you can easily pull off csv’s which list all recorded crimes going back to December 2010. This data gives details on the crime type, it’s location (longitude and latitudes) and the current outcome.
I downloaded each month for 2016 and focussed my download to the Metropolitan Police (the policy force of Greater London).
Now I had all my data; next step.
I had a list of things that I knew I needed to do from here;
- Create shapes of each 200m square area in London
- Plot each of the crimes in London on top of this shape file
- Use this to summarize the largest crime type for each 200m square area
- Create in Tableau
It was pretty evident from the get go that Alteryx was needed for this ReViz, given it’s spatial analytics capabilities.
So here’s how I did it.
- Input the Great Britain Regions shape file into Alteryx, and filter to just London
- Bring in the ‘Make Grid’ tool. Reference the spatial object from your shape file, and select the grid size, in my case ‘0.2 Kilometers’.
This turned my one shape file of London into 40,453 individual spatial objects.This tool was something that I had never seen before in Alteryx. In fact I started trying to make my own grid using some complex maths and formulas before realising it was there. Here is how Alteryx defines the tool.’The Make Grid tool takes a specified spatial object (point or polygon ) and creates a grid based on the spatial object. The resulting grid is either a single grid, bound to the extent of the input spatial objects, or individual grids that dissect each input polygon.’
- Next was to input my crime data. I had 12 files (one for each month from 2016. A quick tip here is that you can bring in multiple files at the same time by using the * as a wildcard character.
- I now needed to use my longitudes and latitudes to make a spatial match between my crimes and my grids. Think of this as a spatial join or spatial lookup. Alteryx is going to return me the spatial object with which my crimes lie.
In order to do this I must first use the ‘create points’ tool to turn my latitude and longitude data into a spatial object.
The configuration for your spatial match is dependant upon how you configure your inputs. In my case my crimes were directed into the ‘T-Input’ and my areas the ‘U-Input’. So therefore my ‘join’ was set as ‘Where Target Within Universe’.
- Now I just wanted to return my top crimes for each of my areas. So I used the summarize tool, grouping by ‘grid name’ and ‘crime type’ and also doing a count of ‘crime type’. I also needed to bring through my spatial object. This left me with a list of my grid areas, the different crime types and how often the crime types had occurred within that area.I now needed to rank my crimes within each grid area. So I first sorted my data (using the ‘sort tool’, first by ‘grid name’ and then my count of crimes.Now in cases where we have groups in our data set, then we cannot simply rank using the ‘Record ID’ tool and instead must use the ‘Multi-Row Formula’ tool which allows us to reference rows above or below that of the cell that is being calculated.
if [Row-1:GridName] != [GridName] then 1 else [Row-1:Rank]+1 endif
This formula evaluates whether the ‘Grid Name’ in the cell below is equal to that of the current cell. If it is not we want our ranking to start again (so 1), otherwise we want to use the ranking of our previous row, and add 1 (thus increasing our rank value).
Now we just need to filter out those values where the rank does not equal 1.
WOO, we have our data set. We have a list of each 200m area in London, alongside the top crime in that area, as well as a spatial object for that area.
- There is one final stage to our Alteryx workflow. At present Tableau does not support spatial data types so we must use Alteryx to turn our spatial objects into polygons that we can use in Tableau.Thankfully, there’s an app for that, created by Info Lab Tableau Zen, Craig Bloodworth.I simply add the macro in, reference my spatial object, and then add an output as a TDE file.
- Now time to build the viz. Well Craig has created this video which shows users how to use the output to create your polygon map in Tableau. (In short, double click latitude, double click longitude, change the mark type to Polygon, place ‘PointID’ on the Path shelf (making sure it is discrete) and place ‘PolygonID’ on the detail shelf (again making sure it is discrete).It is then a simple case of dragging crime type to colour.(I of course did some minor formatting before publishing to my Tableau Public profile.)
I think the design process for recreating this visualisation shows just why I do this sort of thing. It was challenging, I found out more about the tools available in alteryx, and I learnt a new visualisation technique.
What more could you want from a nights work!
For those interested in seeing the full workflow I have made it available for download.
See you again soon.