Update on Check Your Gun Stats
Posted Saturday January 5, 2013 at 1:25:46 pm in Projects
CheckYourGunStats.com is my current side project. It is a site to view crime rates, murder rates, assault rates, and robbery rates for the USA as a whole as well as each individual state. Here are some updates and notes:
- The Disaster Center (who uses FBI data) has crime rates (but doesn't break down weapons used in robberies, assaults, or murders) for years 1960-2011. This data is available in an HTML table (on separate pages), so I was able to use HtmlAgilityPack, NCrawler to retrieve the data for the US, states...including the population.
- The FBI has HTML tables for robberies, murders, and assaults with weapon breakdown, but only from 2004-2011. All other uniform crime reports (UCR) are available in PDF format from 1995-2003. However, the pages aren't easily crawled (they don't use a set pattern), but they do provide Excel downloads of each report. I can script against these.
- The FBI doesn't keep a tally of the USA as a whole for the above statistics. It's necessary for me to ensure that each state (and Washington DC) is in my database before using a script to tally the statistics up for the USA for each year.
- Some of the FBI statistics are incomplete, i.e. for some years Florida, Alabama, Washington DC, Kansas, etc. data is missing altogether (i.e. no murder rate data, for one) or some pieces are missing (i.e. murders by firearm). In these instances I'm doing my best to find this data based off of the state's UCR. Sometimes I'm successful. Sometimes I'm not. Right now I'm having issues with Kansas's pre 2004 records.
- Some state UCR's are very poorly done. For example. some of Hawaii's UCR's use percentages when breaking down the type of weapon used in a murder. So, if there are 311 murders in Hawaii, they may say something like 8% used a firearm. This number comes out to 24.88%. So, if we round up here, there are situations where other percentages that end up with the final number being 312. So, it's important to note with some of this data that it is incomplete.
- A challenge (and something I'll make a note of) is just because a UCR from 2004 might say 2,300 murders, it doesn't mean that that number is the same that might be used in a 2011 UCR. Cases take some time to close, so murder, assault, robbery, etc. numbers change not only on a state level...but once the federal tally is done...also on the federal level.
So, right now I definitively (with some holes I need to close up) have data for:
- Crime rates (1960-2011)
- Murder rates (1995-2011) with weapon breakdown
- Assault rates (1995-2011) with weapon breakdown
- Robbery rates (1995-2011) with weapon breakdown
- Population (1960-2011)
In case you're curious, I've had to create the following scripts:
- One to crawl the Disaster Center for state and country breakdown for 1960-2011 crime rates
- One to crawl the Disaster Center for city/county 1980-2005 crime rates. This data most likely will be used in very limited fashion since finding murder, robbery, and assault data on a city/county level would be a very, very time consuming process.
- One to import data from the Excel documents from 2004-2011 for murder, robbery, and assault rates
- One to import data from the 1995-2003 PDF's. Essentially I took the tables I needed, saved them to a text file, and then created a script to import that data for each murder, robbery, and assault rate report.
- One to save the population for each state, country, and city/county (as reported by each local police department) in the country.
- One to tally the USA total numbers for murder, assault, and crime rates (breakdown by weapon) in the event that the year has all 50 states as well as DC. This will allow users to view a breakdown by coutnry as opposed to looking up each state individually.
What I have done site wise:
- Line charts for each state and the USA from 1995-2011 (if data possible) for murder, assault, and robbery rates (with weapon breakdown). Basically these are 3 separate reports for each state and the USA. A table listing with all the data (and source) is located under the chart)
- Line charts for each state and the USA from 1960-2011 for crime rates.
I'm thinking of launching the site right now just to get that data out there. I still have a lot to do:
- Find the statistics for some of the holes of some reports since the FBI's data isn't as complete as I'd like it
- Create accompanying reports that not only lists whole numbers but also per capita (since I do have population data)
- Get as many key events imported as possible so we can show an event in each line graph (per year). I.e. you can see in a graph that might have 1990-2011 data that the Brady Act went into effect in 1994)
- Have those events shown in each report if that report has date related data (right now, each of my reports do)
In case you're wondering, I'm using jQuery DataTables, Google Charts for the front end. For the backend it's an ASP.NET MVC4 site with a MySQL database using PetaPoco for the ORM.
This is no simple process. But it's coming along. Every data point will be cited. Once again, the purpose of this site is simply to show data in a meaningful manner and to give users an idea of what key events (just legislation based) have happened over the years. It's not to sway anybody one way or another. My opinions are kept off of this site.
The opinions expressed herein are my own personal opinions and do not represent my employer’s view
© Copyright 2012, Stephen Adams