Wednesday, December 10, 2008

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.


Anonymous said...

Seems like it has been just over 1 officer per year since I have been here. Sad that we have greater than half of Omaha's population, but not half of their police officer numbers. I know for years we have talked about uping the numbers per 1,000 citizens, but do you think it will ever happen? I am sure you have said in the past, but what is the ideal number YOU would like to be at? Other than money, what would it take to get to your ideal number?

Tom Casady said...


Yes, it's a recurring theme on my blog.

I've always advocated 1.5 officers per 1,000 as a reasonable target for Lincoln, because:

1. That would put us in about the same place among similarly-sized cities as our violent crime rate places us: top of the bottom third.

2. It could actually be done. It's probably pie-in-the-sky to expect that we'd ever be close to the average, because it would cost a fortune, and if you haven't noticed, everyone running for public office runs on the same platform: "I won't raise your taxes." But 1.5 could be done, if the citizens really wanted to, without a large tax burden.

3. It would still represent a very good value to taxpayers. We would still be nowhere near the average size. Rather, we would continue to lean and mean, forced to maintain our efficiency and provide good value to the citizens.

4. It would make a definite difference to our ability to serve the community safely. Add 3 or 4 officers to the department, and the change is so small that it's not very noticeable. Add 61 officers and the related support staff, and we are suddenly able to soar.

Will it every happen? That depends on our elected officials, and ultimately upon the citizens who elect them.

Anonymous said...

I think a novel idea would be to push for an increase in sales tax at the retail level. This of course would have to begin at the State level to repeal the cap on sales taxes. One only need observe our neighbors to the south like KS, OK, and TX. Some of their cities average anywhere from 8.25%-9%. With those type of funds available it would go a long way at strengthening local LE. Also, the "tax burden" would be equal, and is essentially not noticed. Just my two cents.

Trina said...

Thanks for posting that, Chief. I'll check it out and see if I can put something useful together for my Chief.