Monday, August 1, 2016

Simple Temporal Analysis Heat Maps in Excel

I originally wrote this post for a software company’s blog in 2014. This company was bought out and recently their blog and website have been removed from the web permanently. I am reposting it here for posterity.

I recently had a conversation with someone who was doing research into how crime analysts work. One question he had concerned the acceptance of analytical products by officers. I told him that it had been my experience that officers were hungry for direction in the form of analytical products. They pretty readily accept analysis that would help them improve their effectiveness in their crime fighting mission.

One of the easier analysis you can conduct is a basic temporal analysis. For example, do your officers know what days of the week certain crimes are more likely to occur? At the agency where I work, Thursdays are consistently one of the busiest days for Calls for Service. Because this trend has been so consistent over the years, we scheduled the officers rotating days off to where no officers were regularly off on Thursdays.

But day of week isn’t the only temporal factor, time of day is as well. Certain types of crimes are more likely to occur at certain times of the day. Vehicle burglars are more likely to operate during the overnight hours when cars are more likely to be parked for longer periods and people are more likely to be asleep and not paying attention to their vehicles. Residential burglars most often operate during the day when people are more likely to be at work or school and not at home. But, this temporal routine goes out the window on the weekends when people are more likely to be home regardless of the time of day.

Crime is often cyclical because people, both victims and offenders have adapted to temporal routines. Because some of these temporal cycles are so regular, we can use temporal analysis to discover patterns in these cycles that allow us to better ensure that we can bring the appropriate resources to bear to deal with them.

In the example in today’s post, we’re going to look at some of the temporal patterns in Driving While Intoxicated arrests. Imagine in this scenario you are a police patrol supervisor and you want to ensure that your officers who conduct DWI enforcement are working during the times and days that they are most likely to arrest a DWI offender. One way to do this is with a simple temporal analysis called a “heat map”. This chart will quickly tell us the times and days when most DWI arrests are made.

I’m pretty fortunate to have access to some pretty sophisticated crime analysis software. But even if you don’t have specialized crime analysis software, it’s pretty easy to create a temporal heat map using nothing more than Microsoft Excel.

What I did to prepare my data is I organized the times of day and day of week of DWI cases into an Excel spreadsheet. One way is to manually input this into a spreadsheet but another way would be to export a tabular listing of this data from your records management system. What we want is a spreadsheet that has the time of day for each DWI arrest in one column and the time of day in another. It should look something like this:

Now that we have our data set, we can populate the table for a temporal heat. We’re going to use a Pivot Table and Conditional Formatting to generate this heat map. Select Insert Pivot Table from the ribbon menu in Windows or go to Data > Analysis > Pivot Table on a Mac. Then you need to set your Row Labels to the Hour field, the Column Labels to the Day and Values to Count of Day. Now you should get a pivot table with the numbers in each cell like this:

Now we’re going to use Excel’s Conditional Formatting to give each cell a color that corresponds to the value in each cell. Select the cells within the pivot table then go to the Home Ribbon and select Conditional Formatting > Color Scales and then choose an appropriate color scale. I prefer the one that uses hotter colors (orange, red) for bigger numbers and cooler colors (green) for smaller ones.

Once you’ve done this you should get a temporal heat map that looks like this:

This heat map makes it pretty easy to see that most DWI arrests are occurring on Fridays, Saturdays and Sundays between 1AM and 3AM. This is kind of information that a patrol supervisor could use to make actionable decisions about scheduling officers to work DWI enforcement. A couple of cautions are in order. If possible, try to avoid looking at just time or just day by themselves. Just because one day is hotter than another or one time is more prevalent does not mean that that particular time on that particular day is most prevalent. Combining both time and day into a heat map gets around this problem.

Also, if you are basing your analysis on known cases or arrests as we did in this one, it doesn’t mean that more offenses aren’t happening that you aren’t discovering. If we aren’t making many DWI arrests during the daytime is it because there are much fewer DWI’s out there or are we just not looking for them at that time or both?

What kinds of crimes could you understand better with a temporal heat map?

No comments:

Post a Comment

I reserve the right to remove defamatory, libelous, inappropriate or otherwise stupid comments. If you are a spammer or are link baiting in the comments, a pox be upon you. The same goes for people trying to sell stuff. Your comment will be deleted without mercy.