Data to crunch

Every year, the FBI publishes a compendium of data from 17,000 or so police agencies that participate in the Uniform Crime Reporting (UCR) program by submitting their data to the FBI. The program is voluntary, and while not all agencies submit, this represents that vast majority of state, county, municipal, and university police agencies in the country. The thick publication that results, Crime in the United States (CIUS) is an annual publication in which the FBI compiles volume and rate of crime offenses for the nation, the states, and individual agencies. This report also includes arrest, clearance, and law enforcement employee data. I sometimes refer to the report as the UCR.

If you go to the UCR website, before you can actually open the report, you are cautioned about the hazards of ranking agencies. The FBI's disclaimer warns that "rough rankings provide no insight into the numerous variables that mold crime in a particular town, city, county, state, or region. Consequently, they lead to simplistic and/or incomplete analyses that often create misleading perceptions adversely affecting communities and their residents." The FBI is correct, and the UCR data must always be understood in the context of how it is collected: these data are submitted by participating agencies, and are only as good as the individual reporting and coding practices of those agencies. I could blog for a month on that topic.

Despite the warnings, though, comparing cities using UCR data is as American as apple pie: everybody does it, everyone wants to know how their robbery rate compares to others, where they stack up in police-citizen ratios, and so forth. Since there are common questions, I have created my own Excel spreadsheets over the years to massage the FBI data by adding a few calculated fields, performing sorts based on population or geography, and so forth.

This got a lot easier a few years ago, when the individual tables in the report became available as .xls files. Now I just grab Table 8 (Offenses known to the police by City) and Table 78 (law enforcement employment by City) and have at it. I write the formulae I need to calculate new columns I want, apply filters and sorts, and use copy and paste to create some sheets that meet my specific purposes: crime data for cities within 50,000 or Lincoln's population, for example, or police officer/population ratios for cities in Nebraska and the surrounding states. I spend about a day on this project every fall when the UCR is published, and I'm prepared all year long when some reporter, city council member, or reader of The Chief's Corner asks a question containing "how do we compare?"

In a recent phone conversation with a colleague, Chris Bruce, the crime analyst at the Danvers, Massachusetts Police Department, I learned that he does the same thing. Lots of analysts from other police agencies read this blog, so in the interest of sharing with others, I've posted my Excel workbook and you are free to download it. It's almost 5 meg, so you' may need to be patient--it took just under a minute on my home wireless network this morning. Table 8 and 78 are the last two sheets, and for all the others that include both crime and employment data together, I only included cities that reported both. Some cities had crime data but no employment data, or vice versa. There were a handful of cities with incomplete employment data (such as no population listed) that I also did not include.

Feel free to contact me if you have any questions.