Sunday, January 11, 2009

Heat Charts for Crime Stats Analysis

I recently got a request for a $50 Project from Kurt Smith with the San Diego County Sheriff's Department Crime Analysis Unit. (Cool, huh?)

Kurt wanted to apply the heat charting techniques I've posted about before to crime statistics on a Time-of-Day vs. Day-of-Week form. He shot me over some sample data on vehicle thefts. A simple Copy --> Paste Special... --> Values operation later and here's what we've got...

I'm obviously biased, but I think heat charts is a great way to visualize this sort of data, which can tend to get lost in tabular form. In this example, you can easily see the clustering occur just after midnight on the weekends.

The SDSD's apparently got a few Excel geek's on staff, as Kurt tells me that their resident Excel afficianado, Ted, has "...played with it and built some other ranges that approximate standard deviation, 'thirds' and so on from percentiles..." and that they're "...going to begin using it to replace our Excel surface charts for when particular crime types are being reported (we use split times and some aoristic analysis, depending on the crime)..." Whoa...slow're losing me, Kurt.

Kurt also tells me that Lincoln's very own Police Chief Tom Casady is a stats/spreadsheet junky, too. Who knew this? Apparently there's a whole hidden world of Excel geeks: Cops! I'll have to subscribe to Chief Casady's blog.

Excel_Geek Insiders subscribers, I've sent this file out before, but just in case you've lost it, this new version is on it's way.