Don’t Cycle in London

London bike theft data by street

 

3D Mapping in Excel (formerly Power Maps) is one of our favourite features and can make a huge impact on a dull spreadsheet of data.  We were working with the Metropolitan Police recently during their upgrade from Windows XP and we created a customised Excel analysis demo on crime data.

UK crime data is publicly available and we envisaged mapping crimes across police forces.  It turns out there’s quite a lot of crime in the country.  So we limited the data to just the Met Police.  Still a lot of crime.  Then we filtered to just show bicycle thefts.  Still a lot.  So we limited the data to between January and November 2015.  Still 13,500 recorded cycle thefts just within those eleven months.  So the moral is don’t cycle in London.

Turn Dull Data into a Compelling Story

Imagine you are a crime prevention officer (or perhaps you already are in which case just imagine you have a different name).  Your experience tells you a bike anti-theft campaign in Richmond will pay dividends in lowering the crime figures for the area.  You want to take the data to a budget holder to ask for some cash for bike marking, lockable posts, etc. and you show them the following:

London bike theft data sample

 

It doesn’t paint a compelling argument to obtain budget.  And there are 13,500 of these rows too.  Now luckily, you recently saw an awesome awareness session from someone at ImageFrame when they ran a Buzz Day at your office and you recalled Excel 3D maps.

3D maps allows you to create a graphical report (called a tour) with pages (called scenes) on which you can plot data with geographical information such as postcode, town, latitude/longitude.  For example, in the first scene of our bike theft tour, we mapped the count of bike thefts grouped by London Borough.  This gives us a good overview.  Then we mapped the count again but using lat/lon for accuracy down to street level.  This clearly shows us correlation we don’t see from the data alone; the high concentrations of bike thefts are from train stations (notice the highest brown column in the first picture in this post).

3D maps also allow us to overlay different data sets so we could show crime data overlaid onto demographic information.

If that wasn’t enough, we can include a timeline so the map ‘matures’ and plots the data gradually in relation to dates.  This allows us to see which months are the hotspots for bike thefts.

We’ll blog about how to create a 3D map in the near future but for now you can download our sample data set here and the completed map report as a video here.  Once you have the data set, open it in Excel, select the Insert tab and 3D map then Open 3D Maps.  You’ll see our tour already created for you.  In Office 2013, the ribbon tabs will refer to Power Map instead of 3D Map.

Open 3D Maps in Excel

 

Have a play and if you do cycle in London, make sure you have a really good bike lock.