Monday, February 10, 2014

Introduction to Pivot Tables

Every now and then you make a discovery that turns out to be mindblowingly wonderful. They don’t happen often but when they do, they are great. I made one of those discoveries one day shortly after I became a full time crime analyst.

As a beginning analyst I quickly found that while it was pretty easy to get data out of a database it was a whole lot harder to analyze these records and turn them into something meaningful. I would export a bunch of records out of our records management system and pull them into a spreadsheet. But then when it came to operations like counting records meeting certain criteria I’d resort to sorting records into categories and either physically counting rows or using the row numbers to determine the number of records meeting my criteria. There had to be a better way.

As it turns out, there was. 

I was playing around in Microsoft Excel, the spreadsheet application we use at my office. I became curious about the “Insert Pivot Table” command. So I hit the F1 or Help key and looked up the help file entry for Pivot Table. This is what it said:

“A PivotTable report is an interactive tool that combines and compares data. You can rotate its rows and columns to see different summaries of the source data and display the details for areas of interest. Use a PivotTable when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. Because a PivotTable is interactive, you can freely experiment with layout of the data to focus on specific details or calculate different summaries, such as counts or averages.”

That sounded exactly like the tool that could make my life easier when it comes to analyzing the data I extracted from our records management system. 

A Pivot Table helps you take a table full of records like this:

and turn it into something like this with just a few mouse clicks.

The real beauty of a Pivot Table lies in that last sentence from the help file description:
“Because a PivotTable is interactive, you can freely experiment with layout of the data to focus on specific details or calculate different summaries, such as counts or averages.”

Interactivity is what really gives a Pivot Table power. It’s pretty easy to create a report in an application like Crystal Reports that will spit out a count of the number of burglaries you had in a given month. But what if as you are analyzing the data you notice that a large number of those burglaries appear to be happening at apartment complexes. 

You can go back and filter out all the burglaries at different types of locations and then re-run the report one at a time or by using the interactivity of a Pivot Table you can drop the Location Type field into your Pivot Table and quickly parse out the numbers of burglaries at all different types of locations. A couple of clicks and you’re looking at a lot of different facets of your dataset. Don’t find the answer you’re looking for, add or remove different fields to look at the data another way. 

Want to know the day of the week frequencies of those apartment burglaries?  Throw the Day of Week field in the mix and within in the space of a few minutes you are determining what day is most common for apartment complex burglaries. 

There are a bunch of online tutorials on how to create and use Pivot Tables. Here’s a text based one from Microsoft. There are also You Tube videos and other websites that can teach you how to be really proficient with them. There is also an excellent book by Mark Stallo and Chris Bruce of the International Association of Crime Analysts called Better Policing with Microsoft Office 2007 that can help you wring the most out of Microsoft Office. It includes a section on Pivot Tables. 

What data could you understand better with a Pivot Table?

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.