Charting Crime Stats

[foxtrot1014]

Hello,

I have a spreadsheet that I dug up from my local PD with over 100k lines of crimes committed in my city over the last few years. I'm trying to extract some tangible data from it. Mainly, I want to create a chart that shows whether or not crime is increasing or decreasing (number of incidents per week/month/year).

I've found similar posts and the solution usually involves extracting week numbers and using a pivot chart, but the directions are vague/written for someone with a better understanding of excel than me.

How can I create a chart that will allow me to visually compare crime rates?

In case your curious why I'm doing this; I bought a new house a couple years ago in Tacoma. Since I've moved in, it appears that non violent crime has been increasing dramatically in my neighborhood. I have had property stolen and vandalized on my own property and cops have been called various times regarding suspicious activity/crimes in progress and they don't respond. I woke a few days ago to find someone bashed the taillights on my '79 Chevy C20. When I called the police, they said that non-violent crimes in Tacoma have to be filed online and that an officer will not respond. I pleaded with dispatch about the trend and police never responding and how it's getting out of hand and that at this point an officer needs to do something--she hung up. So I called back and finally a sergeant showed up at my house and spent 20 minutes very condescendingly berating me for wasting his time before leaving without even having looked at the damage done to my truck. Considering he treated me so unprofessionally, I asked for his badge number (wasn't wearing anything but an embroidered badge w/o number) and he told me to figure it out myself (I got his name, rank, and squad car number). The dispatchers and officers said the only thing I could do was contact my Community Liaison Officer. I did, it's been over a week and no reply. I even filed a complaint with IA and was told I'd be receiving a call--no call returned as of yet.

Basically, crime appears to be increasing in my neighborhood, but the PD website only shows stats for six months, if you look very carefully you can find a link to a Motorola website to download the complete dataset which is what I did. I want to take that massive amount of data and extract some actionable data from it so that I can approach my neighbors to form some sort of alliance whether it be a neighborhood crime watch or a group that collectively files a well-sourced complaint with the City and PD regarding officers' lack of response to the plethora of crimes being committed in our neighborhood. There's also always the possibility that I'm being overly-sensitive and crime is actually DECREASING meaning I'm wasting a lot of my energy now.

 

[Jon Peltier]

So save the data somewhere, so in case you mess it up you can start over.

Look at the data, see if you can remove some columns or even some rows without changing your analysis.

The important data now should have a date (and maybe time), and information about each crime. It might only be two columns (date and name of crime), or there might be more, maybe something about the severity of the crime or whatever.

Select the data, and insert a pivot table. Put the date field into the rows area. Drag the crime description into the values area, and make sure it says Count of .... This gives you a large pivot table with a column of dates, and number of crimes on each date.

So now you can group the date field. Right click on it and choose Group. To group by month, check Month and Year in the list. To group by week, check Days, and enter 7 as Number of Days.

You can make a line or column chart from this pivot table, and it will show the increase or decrease over the time period of the data.

 

[foxtrot1014]

Thanks for the primer! I haven't used excel for much more than calculating tables since Office 2010. Excel has improved greatly (once you know how to use it).

There weren't too many superfluous columns, so I just let them be. I started by determining the lattitude and longittude of what I've defined as my immediate neighborhood (eg 42.2220-42.2270 and -123.3450--123.3550) then sorted all the data by lattitude and copied everything within that range to a new table, sorted by longitude and copied everything within that range to a new table and had all the data for my neighborhood (maybe not scientifically, but close enough for this type of assessment).

From there, I did as you explained. I got some charts and visual information and made some sense of it all. I don't know if this forum is strictly about using excel or if there's interest in the data analysis, but if so, here's what I found:
- Crime in my defined neighborhood has gone up since I moved here, but
- It's actually down over a six year period, and
- Is kind of volatile because the sample size is somewhat small (<1 crime/day on average).
- Crime in Tacoma is increasing (which was information easily found online without this obfuscated process)
- There seems to be a trend of crime slowing down around February (maybe taxes have an effect?)

 

[foxtrot1014]

Here are the charts I made.

 

[Smitty]

Welcome to the Board!

You might want to check your images, as they're not posting for me.

Regarding the advice you got from Jon, I should point out that he's pretty much the best charting guy on the planet.