Skip to content

Instantly share code, notes, and snippets.

@bclifton

bclifton/2014 UCR Counties with more than 70 percent ORIs reporting.ipynb Secret

Last active August 22, 2016 22:07
Show Gist options
  • Save bclifton/c9ebbcd88cc68f42c647 to your computer and use it in GitHub Desktop.
Save bclifton/c9ebbcd88cc68f42c647 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
2014 UCR Counties with more than 70 percent ORIs reporting.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Methodology for Determining the US Counties with Greater than 70% of Police Departments Reporting Crime Data to the FBI's Uniform Crime Reporting program\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Brian Clifton\n",
"___"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"When you request the Uniform Crime Reporting (UCR) data from the FBI, they send a DVD through the Postal Service and . The data on the DVD comes in a massive and archaic .DAT file format (each around 140MB), with one file per year. Opening the file in a text editor shows that while there are some human readable portions, most of the fields in the document are encoded as numbers, and are unintelligible.\n",
"\n",
"![Snapshot of encoded .DAT file of full UCR data](https://i.imgur.com/LnvPAwB.jpg)\n",
"<br>\n",
"\n",
"In order to transform it into something useful, the FBI provides a PDF explaining how the encoding works. Since the FBI does not supply a script to decode the data yourself, it requires a custom program to process the data into a form that is understandable.\n",
"\n",
"![Codebook for URC Return A decoding](https://i.imgur.com/xGflRbu.jpg)\n",
"<br>\n",
"\n",
"The image above is a screenshot of the PDF showing how to decode each line in the .DAT file. For each line, every character is a numbered sequentially, and it explains that character positions 2-3 in the line represents the \"Numeric State Code\" where numbers `50` is code for Alaska, `01` is code for Alabama, etc. This goes on for another 7,385 characters per line. Each one representing another aspect in the data, the police department's address, the precinct's population, the tally for each crime per month, and many other fields.\n",
"\n",
"Using the PDF as a guide, I wrote a Python script to transform the raw data into a Comma Separated Values file (CSV), a form better suited for analysis. Here is a link to the [scripts for decoding the raw FBI UCR arrests and clearances data](https://github.com/bclifton/UCR).\n",
"\n",
"After transforming all of the data into the human-readable format, the CSV's were uploaded to a SQL database to further improve the speed of analyses.\n",
"\n",
"What follows is a walkthrough of the Python code using [Juypter / IPython notebook](http://jupyter.org/) showing how to determine which counties have greater than 70% of police departments reporting data to the UCR program in 2014. \n",
"\n",
"------\n",
"\n",
"Since we are only looking at the 2014 data, this query was run on the SQL database: `SELECT * FROM UCR_Clearances_80-14 WHERE year=2014;` and the result was saved as the file: `clearances_2014.csv`."
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"import pandas as pd\n",
"import numpy as np"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Using the Python libraries [`pandas`](http://pandas.pydata.org/) and [`numpy`](http://www.numpy.org/) for data processing, the data is loaded into a `pandas` specific data type called a `DataFrame`, which is comparable to a CSV file, but is optimized for large-scale data operations. And finally, the first 5 lines are displayed."
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>key</th>\n",
" <th>ori_code</th>\n",
" <th>year</th>\n",
" <th>month</th>\n",
" <th>group</th>\n",
" <th>division</th>\n",
" <th>state</th>\n",
" <th>state_full</th>\n",
" <th>state_abbr</th>\n",
" <th>county</th>\n",
" <th>seq_number</th>\n",
" <th>juvenile_age</th>\n",
" <th>core_city</th>\n",
" <th>covered_by</th>\n",
" <th>covered_group</th>\n",
" <th>field_office</th>\n",
" <th>last_update</th>\n",
" <th>months_reported</th>\n",
" <th>agency_count</th>\n",
" <th>population_city</th>\n",
" <th>msa</th>\n",
" <th>population_group_1</th>\n",
" <th>population_group_2</th>\n",
" <th>population_1</th>\n",
" <th>population_2</th>\n",
" <th>population_3</th>\n",
" <th>population_source</th>\n",
" <th>follow_up_indication</th>\n",
" <th>special_mailing_address</th>\n",
" <th>special_mailing_group</th>\n",
" <th>agency_name</th>\n",
" <th>agency_state</th>\n",
" <th>agency_address_1</th>\n",
" <th>agency_address_2</th>\n",
" <th>agency_address_3</th>\n",
" <th>agency_address_4</th>\n",
" <th>agency_zip_code</th>\n",
" <th>old_population_group</th>\n",
" <th>actual_last_update</th>\n",
" <th>actual_month_included</th>\n",
" <th>actual_grand_total</th>\n",
" <th>actual_murder_total</th>\n",
" <th>actual_manslaughter_total</th>\n",
" <th>actual_rape_total</th>\n",
" <th>actual_rape_forcible</th>\n",
" <th>actual_rape_attempted</th>\n",
" <th>actual_robbery_total</th>\n",
" <th>actual_robbery_wgun</th>\n",
" <th>actual_robbery_wknife</th>\n",
" <th>actual_robbery_other</th>\n",
" <th>actual_robbery_strongarm</th>\n",
" <th>actual_assault_total</th>\n",
" <th>actual_assault_wgun</th>\n",
" <th>actual_assault_wknife</th>\n",
" <th>actual_assault_other</th>\n",
" <th>actual_assault_whands</th>\n",
" <th>actual_assault_simple</th>\n",
" <th>actual_burglary_total</th>\n",
" <th>actual_burglary_forcible</th>\n",
" <th>actual_burglary_notforcible</th>\n",
" <th>actual_burglary_attempted</th>\n",
" <th>actual_larceny_total</th>\n",
" <th>actual_larceny_motor</th>\n",
" <th>actual_larceny_auto</th>\n",
" <th>actual_larceny_truck</th>\n",
" <th>actual_larceny_other</th>\n",
" <th>actual_larceny_lt_50</th>\n",
" <th>cleared_last_update</th>\n",
" <th>cleared_month_included</th>\n",
" <th>cleared_grand_total</th>\n",
" <th>cleared_murder_total</th>\n",
" <th>cleared_manslaughter_total</th>\n",
" <th>cleared_rape_total</th>\n",
" <th>cleared_rape_forcible</th>\n",
" <th>cleared_rape_attempted</th>\n",
" <th>cleared_robbery_total</th>\n",
" <th>cleared_robbery_wgun</th>\n",
" <th>cleared_robbery_wknife</th>\n",
" <th>cleared_robbery_other</th>\n",
" <th>cleared_robbery_strongarm</th>\n",
" <th>cleared_assault_total</th>\n",
" <th>cleared_assault_wgun</th>\n",
" <th>cleared_assault_wknife</th>\n",
" <th>cleared_assault_other</th>\n",
" <th>cleared_assault_whands</th>\n",
" <th>cleared_assault_simple</th>\n",
" <th>cleared_burglary_total</th>\n",
" <th>cleared_burglary_forcible</th>\n",
" <th>cleared_burglary_notforcible</th>\n",
" <th>cleared_burglary_attempted</th>\n",
" <th>cleared_larceny_total</th>\n",
" <th>cleared_larceny_motor</th>\n",
" <th>cleared_larceny_auto</th>\n",
" <th>cleared_larceny_truck</th>\n",
" <th>cleared_larceny_other</th>\n",
" <th>cleared_larceny_lt_50</th>\n",
" <th>unfounded_last_update</th>\n",
" <th>unfounded_month_included</th>\n",
" <th>unfounded_grand_total</th>\n",
" <th>unfounded_murder_total</th>\n",
" <th>unfounded_manslaughter_total</th>\n",
" <th>unfounded_rape_total</th>\n",
" <th>unfounded_rape_forcible</th>\n",
" <th>unfounded_rape_attempted</th>\n",
" <th>unfounded_robbery_total</th>\n",
" <th>unfounded_robbery_wgun</th>\n",
" <th>unfounded_robbery_wknife</th>\n",
" <th>unfounded_robbery_other</th>\n",
" <th>unfounded_robbery_strongarm</th>\n",
" <th>unfounded_assault_total</th>\n",
" <th>unfounded_assault_wgun</th>\n",
" <th>unfounded_assault_wknife</th>\n",
" <th>unfounded_assault_other</th>\n",
" <th>unfounded_assault_whands</th>\n",
" <th>unfounded_assault_simple</th>\n",
" <th>unfounded_burglary_total</th>\n",
" <th>unfounded_burglary_forcible</th>\n",
" <th>unfounded_burglary_notforcible</th>\n",
" <th>unfounded_burglary_attempted</th>\n",
" <th>unfounded_larceny_total</th>\n",
" <th>unfounded_larceny_motor</th>\n",
" <th>unfounded_larceny_auto</th>\n",
" <th>unfounded_larceny_truck</th>\n",
" <th>unfounded_larceny_other</th>\n",
" <th>unfounded_larceny_lt_50</th>\n",
" <th>under18_last_update</th>\n",
" <th>under18_month_included</th>\n",
" <th>under18_grand_total</th>\n",
" <th>under18_murder_total</th>\n",
" <th>under18_manslaughter_total</th>\n",
" <th>under18_rape_total</th>\n",
" <th>under18_rape_forcible</th>\n",
" <th>under18_rape_attempted</th>\n",
" <th>under18_robbery_total</th>\n",
" <th>under18_robbery_wgun</th>\n",
" <th>under18_robbery_wknife</th>\n",
" <th>under18_robbery_other</th>\n",
" <th>under18_robbery_strongarm</th>\n",
" <th>under18_assault_total</th>\n",
" <th>under18_assault_wgun</th>\n",
" <th>under18_assault_wknife</th>\n",
" <th>under18_assault_other</th>\n",
" <th>under18_assault_whands</th>\n",
" <th>under18_assault_simple</th>\n",
" <th>under18_burglary_total</th>\n",
" <th>under18_burglary_forcible</th>\n",
" <th>under18_burglary_notforcible</th>\n",
" <th>under18_burglary_attempted</th>\n",
" <th>under18_larceny_total</th>\n",
" <th>under18_larceny_motor</th>\n",
" <th>under18_larceny_auto</th>\n",
" <th>under18_larceny_truck</th>\n",
" <th>under18_larceny_other</th>\n",
" <th>under18_larceny_lt_50</th>\n",
" <th>officers_assaulted</th>\n",
" <th>officers_killed_accident</th>\n",
" <th>officers_killed_felonious</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>ALAST00-14</td>\n",
" <td>ALAST00</td>\n",
" <td>2014</td>\n",
" <td>2</td>\n",
" <td>Non-MSA counties under 10,000</td>\n",
" <td>East South Central</td>\n",
" <td>AL - Alabama</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td></td>\n",
" <td>70402</td>\n",
" <td>00</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3490</td>\n",
" <td>62386</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>ALABAMA HIGHWAY PATROL</td>\n",
" <td>ALA</td>\n",
" <td>CHIEF OF HIGHWAY PATROL</td>\n",
" <td>AL HIGHWAY PARTOL HEADQUARTERS</td>\n",
" <td>500 DEXTER AVE</td>\n",
" <td>MONTGOMERY, AL</td>\n",
" <td>36130</td>\n",
" <td>8</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>ALAST00-14</td>\n",
" <td>ALAST00</td>\n",
" <td>2014</td>\n",
" <td>3</td>\n",
" <td>Non-MSA counties under 10,000</td>\n",
" <td>East South Central</td>\n",
" <td>AL - Alabama</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td></td>\n",
" <td>70402</td>\n",
" <td>00</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3490</td>\n",
" <td>62386</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>ALABAMA HIGHWAY PATROL</td>\n",
" <td>ALA</td>\n",
" <td>CHIEF OF HIGHWAY PATROL</td>\n",
" <td>AL HIGHWAY PARTOL HEADQUARTERS</td>\n",
" <td>500 DEXTER AVE</td>\n",
" <td>MONTGOMERY, AL</td>\n",
" <td>36130</td>\n",
" <td>8</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>ALAST00-14</td>\n",
" <td>ALAST00</td>\n",
" <td>2014</td>\n",
" <td>1</td>\n",
" <td>Non-MSA counties under 10,000</td>\n",
" <td>East South Central</td>\n",
" <td>AL - Alabama</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td></td>\n",
" <td>70402</td>\n",
" <td>00</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3490</td>\n",
" <td>62386</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>ALABAMA HIGHWAY PATROL</td>\n",
" <td>ALA</td>\n",
" <td>CHIEF OF HIGHWAY PATROL</td>\n",
" <td>AL HIGHWAY PARTOL HEADQUARTERS</td>\n",
" <td>500 DEXTER AVE</td>\n",
" <td>MONTGOMERY, AL</td>\n",
" <td>36130</td>\n",
" <td>8</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>ALAST00-14</td>\n",
" <td>ALAST00</td>\n",
" <td>2014</td>\n",
" <td>6</td>\n",
" <td>Non-MSA counties under 10,000</td>\n",
" <td>East South Central</td>\n",
" <td>AL - Alabama</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td></td>\n",
" <td>70402</td>\n",
" <td>00</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3490</td>\n",
" <td>62386</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>ALABAMA HIGHWAY PATROL</td>\n",
" <td>ALA</td>\n",
" <td>CHIEF OF HIGHWAY PATROL</td>\n",
" <td>AL HIGHWAY PARTOL HEADQUARTERS</td>\n",
" <td>500 DEXTER AVE</td>\n",
" <td>MONTGOMERY, AL</td>\n",
" <td>36130</td>\n",
" <td>8</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>ALAST00-14</td>\n",
" <td>ALAST00</td>\n",
" <td>2014</td>\n",
" <td>7</td>\n",
" <td>Non-MSA counties under 10,000</td>\n",
" <td>East South Central</td>\n",
" <td>AL - Alabama</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td></td>\n",
" <td>70402</td>\n",
" <td>00</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3490</td>\n",
" <td>62386</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>ALABAMA HIGHWAY PATROL</td>\n",
" <td>ALA</td>\n",
" <td>CHIEF OF HIGHWAY PATROL</td>\n",
" <td>AL HIGHWAY PARTOL HEADQUARTERS</td>\n",
" <td>500 DEXTER AVE</td>\n",
" <td>MONTGOMERY, AL</td>\n",
" <td>36130</td>\n",
" <td>8</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" key ori_code year month group division state state_full state_abbr county seq_number juvenile_age core_city covered_by covered_group field_office last_update months_reported agency_count population_city msa population_group_1 population_group_2 population_1 population_2 population_3 population_source follow_up_indication special_mailing_address special_mailing_group agency_name agency_state agency_address_1 agency_address_2 agency_address_3 agency_address_4 agency_zip_code old_population_group actual_last_update actual_month_included actual_grand_total actual_murder_total actual_manslaughter_total actual_rape_total actual_rape_forcible actual_rape_attempted actual_robbery_total actual_robbery_wgun actual_robbery_wknife actual_robbery_other actual_robbery_strongarm actual_assault_total actual_assault_wgun \\\n",
"0 ALAST00-14 ALAST00 2014 2 Non-MSA counties under 10,000 East South Central AL - Alabama Alabama AL 70402 00 N 3490 62386 0 1 0 NaN 0 0 0 0 0 0 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. ALABAMA HIGHWAY PATROL ALA CHIEF OF HIGHWAY PATROL AL HIGHWAY PARTOL HEADQUARTERS 500 DEXTER AVE MONTGOMERY, AL 36130 8 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"1 ALAST00-14 ALAST00 2014 3 Non-MSA counties under 10,000 East South Central AL - Alabama Alabama AL 70402 00 N 3490 62386 0 1 0 NaN 0 0 0 0 0 0 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. ALABAMA HIGHWAY PATROL ALA CHIEF OF HIGHWAY PATROL AL HIGHWAY PARTOL HEADQUARTERS 500 DEXTER AVE MONTGOMERY, AL 36130 8 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"2 ALAST00-14 ALAST00 2014 1 Non-MSA counties under 10,000 East South Central AL - Alabama Alabama AL 70402 00 N 3490 62386 0 1 0 NaN 0 0 0 0 0 0 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. ALABAMA HIGHWAY PATROL ALA CHIEF OF HIGHWAY PATROL AL HIGHWAY PARTOL HEADQUARTERS 500 DEXTER AVE MONTGOMERY, AL 36130 8 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"3 ALAST00-14 ALAST00 2014 6 Non-MSA counties under 10,000 East South Central AL - Alabama Alabama AL 70402 00 N 3490 62386 0 1 0 NaN 0 0 0 0 0 0 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. ALABAMA HIGHWAY PATROL ALA CHIEF OF HIGHWAY PATROL AL HIGHWAY PARTOL HEADQUARTERS 500 DEXTER AVE MONTGOMERY, AL 36130 8 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"4 ALAST00-14 ALAST00 2014 7 Non-MSA counties under 10,000 East South Central AL - Alabama Alabama AL 70402 00 N 3490 62386 0 1 0 NaN 0 0 0 0 0 0 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. ALABAMA HIGHWAY PATROL ALA CHIEF OF HIGHWAY PATROL AL HIGHWAY PARTOL HEADQUARTERS 500 DEXTER AVE MONTGOMERY, AL 36130 8 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"\n",
" actual_assault_wknife actual_assault_other actual_assault_whands actual_assault_simple actual_burglary_total actual_burglary_forcible actual_burglary_notforcible actual_burglary_attempted actual_larceny_total actual_larceny_motor actual_larceny_auto actual_larceny_truck actual_larceny_other actual_larceny_lt_50 cleared_last_update cleared_month_included cleared_grand_total cleared_murder_total cleared_manslaughter_total cleared_rape_total cleared_rape_forcible cleared_rape_attempted cleared_robbery_total cleared_robbery_wgun cleared_robbery_wknife cleared_robbery_other cleared_robbery_strongarm cleared_assault_total cleared_assault_wgun cleared_assault_wknife cleared_assault_other cleared_assault_whands cleared_assault_simple cleared_burglary_total cleared_burglary_forcible cleared_burglary_notforcible cleared_burglary_attempted cleared_larceny_total cleared_larceny_motor cleared_larceny_auto cleared_larceny_truck cleared_larceny_other \\\n",
"0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"\n",
" cleared_larceny_lt_50 unfounded_last_update unfounded_month_included unfounded_grand_total unfounded_murder_total unfounded_manslaughter_total unfounded_rape_total unfounded_rape_forcible unfounded_rape_attempted unfounded_robbery_total unfounded_robbery_wgun unfounded_robbery_wknife unfounded_robbery_other unfounded_robbery_strongarm unfounded_assault_total unfounded_assault_wgun unfounded_assault_wknife unfounded_assault_other unfounded_assault_whands unfounded_assault_simple unfounded_burglary_total unfounded_burglary_forcible unfounded_burglary_notforcible unfounded_burglary_attempted unfounded_larceny_total unfounded_larceny_motor unfounded_larceny_auto unfounded_larceny_truck unfounded_larceny_other unfounded_larceny_lt_50 under18_last_update under18_month_included under18_grand_total under18_murder_total under18_manslaughter_total under18_rape_total under18_rape_forcible under18_rape_attempted under18_robbery_total under18_robbery_wgun \\\n",
"0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"\n",
" under18_robbery_wknife under18_robbery_other under18_robbery_strongarm under18_assault_total under18_assault_wgun under18_assault_wknife under18_assault_other under18_assault_whands under18_assault_simple under18_burglary_total under18_burglary_forcible under18_burglary_notforcible under18_burglary_attempted under18_larceny_total under18_larceny_motor under18_larceny_auto under18_larceny_truck under18_larceny_other under18_larceny_lt_50 officers_assaulted officers_killed_accident officers_killed_felonious \n",
"0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"clearances_data_by_month = pd.read_csv('clearances_2014.csv', low_memory=False)\n",
"clearances_data_by_month.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Note there is a field in the data labeled `ori_code`, this is the FBI's unique identifying code for each police department.\n",
"\n",
"From here, we want to aggregate the data for each month to find the totals per year. To do so, we'll first select all of the columns in the data that we do **not** want to aggregate:"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"columns_to_groupby = ['key', 'ori_code', 'year', 'group', 'division', 'state', 'state_full', 'state_abbr', 'county', 'seq_number', 'juvenile_age', 'core_city', 'covered_by', 'covered_group', 'field_office', 'last_update', 'months_reported', 'agency_count', 'population_city', 'msa', 'population_group_1', 'population_group_2', 'population_1', 'population_2', 'population_3', 'population_source', 'follow_up_indication', 'special_mailing_address', 'special_mailing_group', 'agency_name', 'agency_state', 'agency_address_1', 'agency_address_2', 'agency_address_3', 'agency_address_4', 'agency_zip_code', 'old_population_group']"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Then, using the `pandas` `groupby` function, we'll create a new dataframe that contains the sum total of the crimes per police department per year."
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false,
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>key</th>\n",
" <th>ori_code</th>\n",
" <th>year</th>\n",
" <th>group</th>\n",
" <th>division</th>\n",
" <th>state</th>\n",
" <th>state_full</th>\n",
" <th>state_abbr</th>\n",
" <th>county</th>\n",
" <th>seq_number</th>\n",
" <th>juvenile_age</th>\n",
" <th>core_city</th>\n",
" <th>covered_by</th>\n",
" <th>covered_group</th>\n",
" <th>field_office</th>\n",
" <th>months_reported</th>\n",
" <th>agency_count</th>\n",
" <th>population_city</th>\n",
" <th>msa</th>\n",
" <th>population_group_1</th>\n",
" <th>population_group_2</th>\n",
" <th>population_1</th>\n",
" <th>population_2</th>\n",
" <th>population_3</th>\n",
" <th>population_source</th>\n",
" <th>follow_up_indication</th>\n",
" <th>special_mailing_address</th>\n",
" <th>special_mailing_group</th>\n",
" <th>agency_name</th>\n",
" <th>agency_state</th>\n",
" <th>agency_address_1</th>\n",
" <th>agency_address_2</th>\n",
" <th>agency_address_3</th>\n",
" <th>agency_address_4</th>\n",
" <th>agency_zip_code</th>\n",
" <th>old_population_group</th>\n",
" <th>actual_grand_total</th>\n",
" <th>actual_murder_total</th>\n",
" <th>actual_manslaughter_total</th>\n",
" <th>actual_rape_total</th>\n",
" <th>actual_rape_forcible</th>\n",
" <th>actual_rape_attempted</th>\n",
" <th>actual_robbery_total</th>\n",
" <th>actual_robbery_wgun</th>\n",
" <th>actual_robbery_wknife</th>\n",
" <th>actual_robbery_other</th>\n",
" <th>actual_robbery_strongarm</th>\n",
" <th>actual_assault_total</th>\n",
" <th>actual_assault_wgun</th>\n",
" <th>actual_assault_wknife</th>\n",
" <th>actual_assault_other</th>\n",
" <th>actual_assault_whands</th>\n",
" <th>actual_assault_simple</th>\n",
" <th>actual_burglary_total</th>\n",
" <th>actual_burglary_forcible</th>\n",
" <th>actual_burglary_notforcible</th>\n",
" <th>actual_burglary_attempted</th>\n",
" <th>actual_larceny_total</th>\n",
" <th>actual_larceny_motor</th>\n",
" <th>actual_larceny_auto</th>\n",
" <th>actual_larceny_truck</th>\n",
" <th>actual_larceny_other</th>\n",
" <th>actual_larceny_lt_50</th>\n",
" <th>cleared_grand_total</th>\n",
" <th>cleared_murder_total</th>\n",
" <th>cleared_manslaughter_total</th>\n",
" <th>cleared_rape_total</th>\n",
" <th>cleared_rape_forcible</th>\n",
" <th>cleared_rape_attempted</th>\n",
" <th>cleared_robbery_total</th>\n",
" <th>cleared_robbery_wgun</th>\n",
" <th>cleared_robbery_wknife</th>\n",
" <th>cleared_robbery_other</th>\n",
" <th>cleared_robbery_strongarm</th>\n",
" <th>cleared_assault_total</th>\n",
" <th>cleared_assault_wgun</th>\n",
" <th>cleared_assault_wknife</th>\n",
" <th>cleared_assault_other</th>\n",
" <th>cleared_assault_whands</th>\n",
" <th>cleared_assault_simple</th>\n",
" <th>cleared_burglary_total</th>\n",
" <th>cleared_burglary_forcible</th>\n",
" <th>cleared_burglary_notforcible</th>\n",
" <th>cleared_burglary_attempted</th>\n",
" <th>cleared_larceny_total</th>\n",
" <th>cleared_larceny_motor</th>\n",
" <th>cleared_larceny_auto</th>\n",
" <th>cleared_larceny_truck</th>\n",
" <th>cleared_larceny_other</th>\n",
" <th>cleared_larceny_lt_50</th>\n",
" <th>unfounded_grand_total</th>\n",
" <th>unfounded_murder_total</th>\n",
" <th>unfounded_manslaughter_total</th>\n",
" <th>unfounded_rape_total</th>\n",
" <th>unfounded_rape_forcible</th>\n",
" <th>unfounded_rape_attempted</th>\n",
" <th>unfounded_robbery_total</th>\n",
" <th>unfounded_robbery_wgun</th>\n",
" <th>unfounded_robbery_wknife</th>\n",
" <th>unfounded_robbery_other</th>\n",
" <th>unfounded_robbery_strongarm</th>\n",
" <th>unfounded_assault_total</th>\n",
" <th>unfounded_assault_wgun</th>\n",
" <th>unfounded_assault_wknife</th>\n",
" <th>unfounded_assault_other</th>\n",
" <th>unfounded_assault_whands</th>\n",
" <th>unfounded_assault_simple</th>\n",
" <th>unfounded_burglary_total</th>\n",
" <th>unfounded_burglary_forcible</th>\n",
" <th>unfounded_burglary_notforcible</th>\n",
" <th>unfounded_burglary_attempted</th>\n",
" <th>unfounded_larceny_total</th>\n",
" <th>unfounded_larceny_motor</th>\n",
" <th>unfounded_larceny_auto</th>\n",
" <th>unfounded_larceny_truck</th>\n",
" <th>unfounded_larceny_other</th>\n",
" <th>unfounded_larceny_lt_50</th>\n",
" <th>under18_grand_total</th>\n",
" <th>under18_murder_total</th>\n",
" <th>under18_manslaughter_total</th>\n",
" <th>under18_rape_total</th>\n",
" <th>under18_rape_forcible</th>\n",
" <th>under18_rape_attempted</th>\n",
" <th>under18_robbery_total</th>\n",
" <th>under18_robbery_wgun</th>\n",
" <th>under18_robbery_wknife</th>\n",
" <th>under18_robbery_other</th>\n",
" <th>under18_robbery_strongarm</th>\n",
" <th>under18_assault_total</th>\n",
" <th>under18_assault_wgun</th>\n",
" <th>under18_assault_wknife</th>\n",
" <th>under18_assault_other</th>\n",
" <th>under18_assault_whands</th>\n",
" <th>under18_assault_simple</th>\n",
" <th>under18_burglary_total</th>\n",
" <th>under18_burglary_forcible</th>\n",
" <th>under18_burglary_notforcible</th>\n",
" <th>under18_burglary_attempted</th>\n",
" <th>under18_larceny_total</th>\n",
" <th>under18_larceny_motor</th>\n",
" <th>under18_larceny_auto</th>\n",
" <th>under18_larceny_truck</th>\n",
" <th>under18_larceny_other</th>\n",
" <th>under18_larceny_lt_50</th>\n",
" <th>officers_assaulted</th>\n",
" <th>officers_killed_accident</th>\n",
" <th>officers_killed_felonious</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>AK00101-14</td>\n",
" <td>AK00101</td>\n",
" <td>2014</td>\n",
" <td>Cities from 250,000 thru 499,999</td>\n",
" <td>Pacific</td>\n",
" <td>AK - Alaska</td>\n",
" <td>Alaska</td>\n",
" <td>AK</td>\n",
" <td></td>\n",
" <td>01302</td>\n",
" <td>18</td>\n",
" <td>Y</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3030</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>301306</td>\n",
" <td>38</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>299455</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>ANCHORAGE</td>\n",
" <td>ALASKA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>ANCHORAGE POLICE DEPARTMENT</td>\n",
" <td>4501 ELMORE RD</td>\n",
" <td>ANCHORAGE, AK</td>\n",
" <td>99507</td>\n",
" <td>3</td>\n",
" <td>18170</td>\n",
" <td>12</td>\n",
" <td>3</td>\n",
" <td>392</td>\n",
" <td>356</td>\n",
" <td>36</td>\n",
" <td>496</td>\n",
" <td>110</td>\n",
" <td>40</td>\n",
" <td>51</td>\n",
" <td>295</td>\n",
" <td>5736</td>\n",
" <td>294</td>\n",
" <td>311</td>\n",
" <td>490</td>\n",
" <td>610</td>\n",
" <td>4031</td>\n",
" <td>1375</td>\n",
" <td>909</td>\n",
" <td>358</td>\n",
" <td>108</td>\n",
" <td>9217</td>\n",
" <td>939</td>\n",
" <td>673</td>\n",
" <td>194</td>\n",
" <td>72</td>\n",
" <td>0</td>\n",
" <td>5437</td>\n",
" <td>9</td>\n",
" <td>2</td>\n",
" <td>78</td>\n",
" <td>64</td>\n",
" <td>14</td>\n",
" <td>156</td>\n",
" <td>22</td>\n",
" <td>16</td>\n",
" <td>15</td>\n",
" <td>103</td>\n",
" <td>3203</td>\n",
" <td>155</td>\n",
" <td>217</td>\n",
" <td>274</td>\n",
" <td>389</td>\n",
" <td>2168</td>\n",
" <td>133</td>\n",
" <td>75</td>\n",
" <td>52</td>\n",
" <td>6</td>\n",
" <td>1713</td>\n",
" <td>143</td>\n",
" <td>104</td>\n",
" <td>34</td>\n",
" <td>5</td>\n",
" <td>0</td>\n",
" <td>151</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>13</td>\n",
" <td>13</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>5</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>58</td>\n",
" <td>71</td>\n",
" <td>53</td>\n",
" <td>17</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>539</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>12</td>\n",
" <td>11</td>\n",
" <td>1</td>\n",
" <td>12</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>8</td>\n",
" <td>239</td>\n",
" <td>9</td>\n",
" <td>13</td>\n",
" <td>14</td>\n",
" <td>30</td>\n",
" <td>173</td>\n",
" <td>20</td>\n",
" <td>11</td>\n",
" <td>7</td>\n",
" <td>2</td>\n",
" <td>248</td>\n",
" <td>7</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>267</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>AK00102-14</td>\n",
" <td>AK00102</td>\n",
" <td>2014</td>\n",
" <td>Cities from 25,000 thru 49,999</td>\n",
" <td>Pacific</td>\n",
" <td>AK - Alaska</td>\n",
" <td>Alaska</td>\n",
" <td>AK</td>\n",
" <td></td>\n",
" <td>19872</td>\n",
" <td>18</td>\n",
" <td>Y</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3030</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>32477</td>\n",
" <td>258</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>32505</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>FAIRBANKS</td>\n",
" <td>ALASKA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>FAIRBANKS POLICE DEPARTMENT</td>\n",
" <td>911 CUSHMAN ST</td>\n",
" <td>FAIRBANKS, AK</td>\n",
" <td>99701</td>\n",
" <td>4</td>\n",
" <td>1893</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>39</td>\n",
" <td>38</td>\n",
" <td>1</td>\n",
" <td>52</td>\n",
" <td>22</td>\n",
" <td>8</td>\n",
" <td>0</td>\n",
" <td>22</td>\n",
" <td>551</td>\n",
" <td>23</td>\n",
" <td>41</td>\n",
" <td>31</td>\n",
" <td>24</td>\n",
" <td>432</td>\n",
" <td>155</td>\n",
" <td>91</td>\n",
" <td>51</td>\n",
" <td>13</td>\n",
" <td>969</td>\n",
" <td>123</td>\n",
" <td>70</td>\n",
" <td>34</td>\n",
" <td>19</td>\n",
" <td>0</td>\n",
" <td>782</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>15</td>\n",
" <td>5</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>8</td>\n",
" <td>369</td>\n",
" <td>9</td>\n",
" <td>26</td>\n",
" <td>20</td>\n",
" <td>21</td>\n",
" <td>293</td>\n",
" <td>16</td>\n",
" <td>8</td>\n",
" <td>6</td>\n",
" <td>2</td>\n",
" <td>345</td>\n",
" <td>31</td>\n",
" <td>19</td>\n",
" <td>11</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>115</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>49</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>42</td>\n",
" <td>18</td>\n",
" <td>5</td>\n",
" <td>7</td>\n",
" <td>6</td>\n",
" <td>34</td>\n",
" <td>8</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>66</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>27</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>26</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>31</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>30</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>AK00117-14</td>\n",
" <td>AK00117</td>\n",
" <td>2014</td>\n",
" <td>Cities from 2,500 thru 9,999</td>\n",
" <td>Pacific</td>\n",
" <td>AK - Alaska</td>\n",
" <td>Alaska</td>\n",
" <td>AK</td>\n",
" <td></td>\n",
" <td>47202</td>\n",
" <td>00</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3030</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>6583</td>\n",
" <td>38</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6453</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>PALMER</td>\n",
" <td>ALASKA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>PALMER POLICE DEPARTMENT</td>\n",
" <td>423 S VALLEY WAY</td>\n",
" <td>PALMER, AK</td>\n",
" <td>99645</td>\n",
" <td>7</td>\n",
" <td>253</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>79</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>75</td>\n",
" <td>22</td>\n",
" <td>7</td>\n",
" <td>14</td>\n",
" <td>1</td>\n",
" <td>130</td>\n",
" <td>14</td>\n",
" <td>6</td>\n",
" <td>7</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>125</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6</td>\n",
" <td>5</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>77</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>72</td>\n",
" <td>8</td>\n",
" <td>1</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" <td>26</td>\n",
" <td>7</td>\n",
" <td>4</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>29</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>11</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>11</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>12</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>16</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>8</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AK00126-14</td>\n",
" <td>AK00126</td>\n",
" <td>2014</td>\n",
" <td>Cities under 2,500</td>\n",
" <td>Pacific</td>\n",
" <td>AK - Alaska</td>\n",
" <td>Alaska</td>\n",
" <td>AK</td>\n",
" <td></td>\n",
" <td>19875</td>\n",
" <td></td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3030</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>258</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>FAIRBANKS INTRNL AIRPORT</td>\n",
" <td>ALASKA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>FAIRBANKS INTERNATIONAL AIRPRT</td>\n",
" <td>6450 AIRPORT WAY</td>\n",
" <td>FAIRBANKS, AK</td>\n",
" <td>99709</td>\n",
" <td></td>\n",
" <td>22</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>11</td>\n",
" <td>4</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>9</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>AK00127-14</td>\n",
" <td>AK00127</td>\n",
" <td>2014</td>\n",
" <td>Cities under 2,500</td>\n",
" <td>Pacific</td>\n",
" <td>AK - Alaska</td>\n",
" <td>Alaska</td>\n",
" <td>AK</td>\n",
" <td></td>\n",
" <td>01317</td>\n",
" <td>00</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3030</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>38</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>ANCHORAGE INTRNTL AIRPOR</td>\n",
" <td>ALASKA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>ANCHORAGE INTERNATIONAL AIRPRT</td>\n",
" <td>PO BOX 190629</td>\n",
" <td>ANCHORAGE, AK</td>\n",
" <td>99519</td>\n",
" <td></td>\n",
" <td>144</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>5</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>5</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>133</td>\n",
" <td>4</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>12</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>8</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" key ori_code year group division state state_full state_abbr county seq_number juvenile_age core_city covered_by covered_group field_office months_reported agency_count population_city msa population_group_1 population_group_2 population_1 population_2 population_3 population_source follow_up_indication special_mailing_address special_mailing_group agency_name agency_state agency_address_1 agency_address_2 agency_address_3 agency_address_4 agency_zip_code old_population_group actual_grand_total actual_murder_total actual_manslaughter_total actual_rape_total actual_rape_forcible actual_rape_attempted actual_robbery_total actual_robbery_wgun actual_robbery_wknife actual_robbery_other actual_robbery_strongarm actual_assault_total actual_assault_wgun actual_assault_wknife actual_assault_other actual_assault_whands \\\n",
"0 AK00101-14 AK00101 2014 Cities from 250,000 thru 499,999 Pacific AK - Alaska Alaska AK 01302 18 Y 3030 12 1 301306 38 0 0 299455 0 0 2 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. ANCHORAGE ALASKA CHIEF OF POLICE ANCHORAGE POLICE DEPARTMENT 4501 ELMORE RD ANCHORAGE, AK 99507 3 18170 12 3 392 356 36 496 110 40 51 295 5736 294 311 490 610 \n",
"1 AK00102-14 AK00102 2014 Cities from 25,000 thru 49,999 Pacific AK - Alaska Alaska AK 19872 18 Y 3030 12 1 32477 258 0 0 32505 0 0 2 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. FAIRBANKS ALASKA CHIEF OF POLICE FAIRBANKS POLICE DEPARTMENT 911 CUSHMAN ST FAIRBANKS, AK 99701 4 1893 4 0 39 38 1 52 22 8 0 22 551 23 41 31 24 \n",
"2 AK00117-14 AK00117 2014 Cities from 2,500 thru 9,999 Pacific AK - Alaska Alaska AK 47202 00 N 3030 12 1 6583 38 0 0 6453 0 0 2 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. PALMER ALASKA CHIEF OF POLICE PALMER POLICE DEPARTMENT 423 S VALLEY WAY PALMER, AK 99645 7 253 0 0 7 6 1 1 1 0 0 0 79 1 1 2 0 \n",
"3 AK00126-14 AK00126 2014 Cities under 2,500 Pacific AK - Alaska Alaska AK 19875 N 3030 12 1 0 258 0 0 0 0 0 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. FAIRBANKS INTRNL AIRPORT ALASKA CHIEF OF POLICE FAIRBANKS INTERNATIONAL AIRPRT 6450 AIRPORT WAY FAIRBANKS, AK 99709 22 0 0 0 0 0 0 0 0 0 0 6 0 0 0 0 \n",
"4 AK00127-14 AK00127 2014 Cities under 2,500 Pacific AK - Alaska Alaska AK 01317 00 N 3030 12 1 0 38 0 0 0 0 0 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. ANCHORAGE INTRNTL AIRPOR ALASKA CHIEF OF POLICE ANCHORAGE INTERNATIONAL AIRPRT PO BOX 190629 ANCHORAGE, AK 99519 144 0 0 0 0 0 0 0 0 0 0 5 0 0 0 0 \n",
"\n",
" actual_assault_simple actual_burglary_total actual_burglary_forcible actual_burglary_notforcible actual_burglary_attempted actual_larceny_total actual_larceny_motor actual_larceny_auto actual_larceny_truck actual_larceny_other actual_larceny_lt_50 cleared_grand_total cleared_murder_total cleared_manslaughter_total cleared_rape_total cleared_rape_forcible cleared_rape_attempted cleared_robbery_total cleared_robbery_wgun cleared_robbery_wknife cleared_robbery_other cleared_robbery_strongarm cleared_assault_total cleared_assault_wgun cleared_assault_wknife cleared_assault_other cleared_assault_whands cleared_assault_simple cleared_burglary_total cleared_burglary_forcible cleared_burglary_notforcible cleared_burglary_attempted cleared_larceny_total cleared_larceny_motor cleared_larceny_auto cleared_larceny_truck cleared_larceny_other cleared_larceny_lt_50 unfounded_grand_total unfounded_murder_total unfounded_manslaughter_total \\\n",
"0 4031 1375 909 358 108 9217 939 673 194 72 0 5437 9 2 78 64 14 156 22 16 15 103 3203 155 217 274 389 2168 133 75 52 6 1713 143 104 34 5 0 151 0 0 \n",
"1 432 155 91 51 13 969 123 70 34 19 0 782 4 0 2 1 1 15 5 2 0 8 369 9 26 20 21 293 16 8 6 2 345 31 19 11 1 0 115 0 0 \n",
"2 75 22 7 14 1 130 14 6 7 1 0 125 0 0 6 5 1 1 1 0 0 0 77 1 2 2 0 72 8 1 6 1 26 7 4 3 0 0 29 0 0 \n",
"3 6 1 0 0 1 11 4 3 1 0 0 6 0 0 0 0 0 0 0 0 0 0 3 0 0 0 0 3 0 0 0 0 1 2 1 1 0 0 9 0 0 \n",
"4 5 2 1 1 0 133 4 3 1 0 0 12 0 0 0 0 0 0 0 0 0 0 3 0 0 0 0 3 1 0 1 0 8 0 0 0 0 0 3 0 0 \n",
"\n",
" unfounded_rape_total unfounded_rape_forcible unfounded_rape_attempted unfounded_robbery_total unfounded_robbery_wgun unfounded_robbery_wknife unfounded_robbery_other unfounded_robbery_strongarm unfounded_assault_total unfounded_assault_wgun unfounded_assault_wknife unfounded_assault_other unfounded_assault_whands unfounded_assault_simple unfounded_burglary_total unfounded_burglary_forcible unfounded_burglary_notforcible unfounded_burglary_attempted unfounded_larceny_total unfounded_larceny_motor unfounded_larceny_auto unfounded_larceny_truck unfounded_larceny_other unfounded_larceny_lt_50 under18_grand_total under18_murder_total under18_manslaughter_total under18_rape_total under18_rape_forcible under18_rape_attempted under18_robbery_total under18_robbery_wgun under18_robbery_wknife under18_robbery_other under18_robbery_strongarm under18_assault_total under18_assault_wgun under18_assault_wknife under18_assault_other under18_assault_whands \\\n",
"0 13 13 0 1 0 0 0 1 3 1 0 0 1 1 5 2 3 0 58 71 53 17 1 0 539 1 0 12 11 1 12 4 0 0 8 239 9 13 14 30 \n",
"1 4 4 0 2 0 0 0 2 49 2 3 1 1 42 18 5 7 6 34 8 6 1 1 0 66 3 0 0 0 0 1 0 0 0 1 27 0 1 0 0 \n",
"2 3 3 0 0 0 0 0 0 11 0 0 0 0 11 1 0 1 0 12 2 1 0 1 0 16 0 0 0 0 0 0 0 0 0 0 8 0 1 0 0 \n",
"3 0 0 0 0 0 0 0 0 2 0 0 0 0 2 0 0 0 0 7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"4 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"\n",
" under18_assault_simple under18_burglary_total under18_burglary_forcible under18_burglary_notforcible under18_burglary_attempted under18_larceny_total under18_larceny_motor under18_larceny_auto under18_larceny_truck under18_larceny_other under18_larceny_lt_50 officers_assaulted officers_killed_accident officers_killed_felonious \n",
"0 173 20 11 7 2 248 7 1 3 3 0 267 0 0 \n",
"1 26 1 0 0 1 31 3 1 2 0 0 30 0 0 \n",
"2 7 0 0 0 0 7 1 1 0 0 0 2 0 0 \n",
"3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"clearances_data_by_year = clearances_data_by_month.groupby(columns_to_groupby, as_index=False).agg(np.sum)\n",
"clearances_data_by_year.drop(['month', 'last_update'], axis=1, inplace=True)\n",
"clearances_data_by_year.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"In order to determine what percentage of the counties have police departments reporting statistics to the FBI, we need to first know which police departments fall within which counties. Fortunately there is a CSV provided by the [National Archive of Criminal Justice Data](http://www.icpsr.umich.edu/icpsrweb/landing.jsp) and Interuniversity Consortium for Political and Social Research (ICPSR), known as the Law Enforcement Agency Identifiers Crosswalk, which can downloaded here: [http://www.icpsr.umich.edu/icpsrweb/NACJD/studies/35158](http://www.icpsr.umich.edu/icpsrweb/NACJD/studies/35158). The key here is it links the FBI's ORI code to the code used for US counties known as a FIPS code or [Federal Information Processing Standard](https://en.wikipedia.org/wiki/FIPS_county_code) .\n",
"\n",
"After downloading the file, we import it the same way we imported `clearances_2014.csv`, using `pandas`. However, this time we need to specify the datatype of the `FIPS` column, which although it is numerical it is not an integer, so we need to import it as a string, otherwise we'll lose the leading 0 in some of the FIPS codes."
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>FIPS</th>\n",
" <th>ori</th>\n",
" <th>COUNTY</th>\n",
" <th>POP</th>\n",
" <th>HOUSE</th>\n",
" <th>MILES</th>\n",
" <th>ZIPCODE</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>01001</td>\n",
" <td>AL00402</td>\n",
" <td>Autauga</td>\n",
" <td>820</td>\n",
" <td>384</td>\n",
" <td>7.730675</td>\n",
" <td>36003</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>01001</td>\n",
" <td>AL00401</td>\n",
" <td>Autauga</td>\n",
" <td>24303</td>\n",
" <td>9562</td>\n",
" <td>23.167956</td>\n",
" <td>36067</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>01001</td>\n",
" <td>AL00400</td>\n",
" <td>Autauga</td>\n",
" <td>43671</td>\n",
" <td>17662</td>\n",
" <td>595.968032</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>01003</td>\n",
" <td>AL00501</td>\n",
" <td>Baldwin</td>\n",
" <td>7820</td>\n",
" <td>2950</td>\n",
" <td>7.999429</td>\n",
" <td>36507</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>01003</td>\n",
" <td>AL00508</td>\n",
" <td>Baldwin</td>\n",
" <td>16581</td>\n",
" <td>7222</td>\n",
" <td>13.474516</td>\n",
" <td>36526</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" FIPS ori COUNTY POP HOUSE MILES ZIPCODE\n",
"0 01001 AL00402 Autauga 820 384 7.730675 36003\n",
"1 01001 AL00401 Autauga 24303 9562 23.167956 36067\n",
"2 01001 AL00400 Autauga 43671 17662 595.968032 NaN\n",
"3 01003 AL00501 Baldwin 7820 2950 7.999429 36507\n",
"4 01003 AL00508 Baldwin 16581 7222 13.474516 36526"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"datatypes = {'FIPS': str}\n",
"ori_to_fips = pd.read_csv('ori_to_fips.csv', dtype=datatypes)\n",
"ori_to_fips.head()"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Total number of FIPS counties: 3147\n"
]
}
],
"source": [
"print 'Total number of FIPS counties: {}'.format(len(ori_to_fips['FIPS'].unique()))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The next step is to merge the UCR `clearances_2014.csv` data with the `ori_to_fips.csv` data. To do so, we'll select only the columns we want to use from the `ori_to_fips`:"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"ori_to_fips = ori_to_fips[['FIPS', 'ori']]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Then using the `pandas` `merge` function to join the two DataFrames together, specifying the names of the columns from the respective DataFrames we are merging, `ori_code` from `clearances_data_by_year` and simply `ori` from `ori_to_fips`."
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"collapsed": false,
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>key</th>\n",
" <th>ori_code</th>\n",
" <th>year</th>\n",
" <th>group</th>\n",
" <th>division</th>\n",
" <th>state</th>\n",
" <th>state_full</th>\n",
" <th>state_abbr</th>\n",
" <th>county</th>\n",
" <th>seq_number</th>\n",
" <th>juvenile_age</th>\n",
" <th>core_city</th>\n",
" <th>covered_by</th>\n",
" <th>covered_group</th>\n",
" <th>field_office</th>\n",
" <th>months_reported</th>\n",
" <th>agency_count</th>\n",
" <th>population_city</th>\n",
" <th>msa</th>\n",
" <th>population_group_1</th>\n",
" <th>population_group_2</th>\n",
" <th>population_1</th>\n",
" <th>population_2</th>\n",
" <th>population_3</th>\n",
" <th>population_source</th>\n",
" <th>follow_up_indication</th>\n",
" <th>special_mailing_address</th>\n",
" <th>special_mailing_group</th>\n",
" <th>agency_name</th>\n",
" <th>agency_state</th>\n",
" <th>agency_address_1</th>\n",
" <th>agency_address_2</th>\n",
" <th>agency_address_3</th>\n",
" <th>agency_address_4</th>\n",
" <th>agency_zip_code</th>\n",
" <th>old_population_group</th>\n",
" <th>actual_grand_total</th>\n",
" <th>actual_murder_total</th>\n",
" <th>actual_manslaughter_total</th>\n",
" <th>actual_rape_total</th>\n",
" <th>actual_rape_forcible</th>\n",
" <th>actual_rape_attempted</th>\n",
" <th>actual_robbery_total</th>\n",
" <th>actual_robbery_wgun</th>\n",
" <th>actual_robbery_wknife</th>\n",
" <th>actual_robbery_other</th>\n",
" <th>actual_robbery_strongarm</th>\n",
" <th>actual_assault_total</th>\n",
" <th>actual_assault_wgun</th>\n",
" <th>actual_assault_wknife</th>\n",
" <th>actual_assault_other</th>\n",
" <th>actual_assault_whands</th>\n",
" <th>actual_assault_simple</th>\n",
" <th>actual_burglary_total</th>\n",
" <th>actual_burglary_forcible</th>\n",
" <th>actual_burglary_notforcible</th>\n",
" <th>actual_burglary_attempted</th>\n",
" <th>actual_larceny_total</th>\n",
" <th>actual_larceny_motor</th>\n",
" <th>actual_larceny_auto</th>\n",
" <th>actual_larceny_truck</th>\n",
" <th>actual_larceny_other</th>\n",
" <th>actual_larceny_lt_50</th>\n",
" <th>cleared_grand_total</th>\n",
" <th>cleared_murder_total</th>\n",
" <th>cleared_manslaughter_total</th>\n",
" <th>cleared_rape_total</th>\n",
" <th>cleared_rape_forcible</th>\n",
" <th>cleared_rape_attempted</th>\n",
" <th>cleared_robbery_total</th>\n",
" <th>cleared_robbery_wgun</th>\n",
" <th>cleared_robbery_wknife</th>\n",
" <th>cleared_robbery_other</th>\n",
" <th>cleared_robbery_strongarm</th>\n",
" <th>cleared_assault_total</th>\n",
" <th>cleared_assault_wgun</th>\n",
" <th>cleared_assault_wknife</th>\n",
" <th>cleared_assault_other</th>\n",
" <th>cleared_assault_whands</th>\n",
" <th>cleared_assault_simple</th>\n",
" <th>cleared_burglary_total</th>\n",
" <th>cleared_burglary_forcible</th>\n",
" <th>cleared_burglary_notforcible</th>\n",
" <th>cleared_burglary_attempted</th>\n",
" <th>cleared_larceny_total</th>\n",
" <th>cleared_larceny_motor</th>\n",
" <th>cleared_larceny_auto</th>\n",
" <th>cleared_larceny_truck</th>\n",
" <th>cleared_larceny_other</th>\n",
" <th>cleared_larceny_lt_50</th>\n",
" <th>unfounded_grand_total</th>\n",
" <th>unfounded_murder_total</th>\n",
" <th>unfounded_manslaughter_total</th>\n",
" <th>unfounded_rape_total</th>\n",
" <th>unfounded_rape_forcible</th>\n",
" <th>unfounded_rape_attempted</th>\n",
" <th>unfounded_robbery_total</th>\n",
" <th>unfounded_robbery_wgun</th>\n",
" <th>unfounded_robbery_wknife</th>\n",
" <th>unfounded_robbery_other</th>\n",
" <th>unfounded_robbery_strongarm</th>\n",
" <th>unfounded_assault_total</th>\n",
" <th>unfounded_assault_wgun</th>\n",
" <th>unfounded_assault_wknife</th>\n",
" <th>unfounded_assault_other</th>\n",
" <th>unfounded_assault_whands</th>\n",
" <th>unfounded_assault_simple</th>\n",
" <th>unfounded_burglary_total</th>\n",
" <th>unfounded_burglary_forcible</th>\n",
" <th>unfounded_burglary_notforcible</th>\n",
" <th>unfounded_burglary_attempted</th>\n",
" <th>unfounded_larceny_total</th>\n",
" <th>unfounded_larceny_motor</th>\n",
" <th>unfounded_larceny_auto</th>\n",
" <th>unfounded_larceny_truck</th>\n",
" <th>unfounded_larceny_other</th>\n",
" <th>unfounded_larceny_lt_50</th>\n",
" <th>under18_grand_total</th>\n",
" <th>under18_murder_total</th>\n",
" <th>under18_manslaughter_total</th>\n",
" <th>under18_rape_total</th>\n",
" <th>under18_rape_forcible</th>\n",
" <th>under18_rape_attempted</th>\n",
" <th>under18_robbery_total</th>\n",
" <th>under18_robbery_wgun</th>\n",
" <th>under18_robbery_wknife</th>\n",
" <th>under18_robbery_other</th>\n",
" <th>under18_robbery_strongarm</th>\n",
" <th>under18_assault_total</th>\n",
" <th>under18_assault_wgun</th>\n",
" <th>under18_assault_wknife</th>\n",
" <th>under18_assault_other</th>\n",
" <th>under18_assault_whands</th>\n",
" <th>under18_assault_simple</th>\n",
" <th>under18_burglary_total</th>\n",
" <th>under18_burglary_forcible</th>\n",
" <th>under18_burglary_notforcible</th>\n",
" <th>under18_burglary_attempted</th>\n",
" <th>under18_larceny_total</th>\n",
" <th>under18_larceny_motor</th>\n",
" <th>under18_larceny_auto</th>\n",
" <th>under18_larceny_truck</th>\n",
" <th>under18_larceny_other</th>\n",
" <th>under18_larceny_lt_50</th>\n",
" <th>officers_assaulted</th>\n",
" <th>officers_killed_accident</th>\n",
" <th>officers_killed_felonious</th>\n",
" <th>FIPS</th>\n",
" <th>ori</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>AK00101-14</td>\n",
" <td>AK00101</td>\n",
" <td>2014</td>\n",
" <td>Cities from 250,000 thru 499,999</td>\n",
" <td>Pacific</td>\n",
" <td>AK - Alaska</td>\n",
" <td>Alaska</td>\n",
" <td>AK</td>\n",
" <td></td>\n",
" <td>01302</td>\n",
" <td>18</td>\n",
" <td>Y</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3030</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>301306</td>\n",
" <td>38</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>299455</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>ANCHORAGE</td>\n",
" <td>ALASKA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>ANCHORAGE POLICE DEPARTMENT</td>\n",
" <td>4501 ELMORE RD</td>\n",
" <td>ANCHORAGE, AK</td>\n",
" <td>99507</td>\n",
" <td>3</td>\n",
" <td>18170</td>\n",
" <td>12</td>\n",
" <td>3</td>\n",
" <td>392</td>\n",
" <td>356</td>\n",
" <td>36</td>\n",
" <td>496</td>\n",
" <td>110</td>\n",
" <td>40</td>\n",
" <td>51</td>\n",
" <td>295</td>\n",
" <td>5736</td>\n",
" <td>294</td>\n",
" <td>311</td>\n",
" <td>490</td>\n",
" <td>610</td>\n",
" <td>4031</td>\n",
" <td>1375</td>\n",
" <td>909</td>\n",
" <td>358</td>\n",
" <td>108</td>\n",
" <td>9217</td>\n",
" <td>939</td>\n",
" <td>673</td>\n",
" <td>194</td>\n",
" <td>72</td>\n",
" <td>0</td>\n",
" <td>5437</td>\n",
" <td>9</td>\n",
" <td>2</td>\n",
" <td>78</td>\n",
" <td>64</td>\n",
" <td>14</td>\n",
" <td>156</td>\n",
" <td>22</td>\n",
" <td>16</td>\n",
" <td>15</td>\n",
" <td>103</td>\n",
" <td>3203</td>\n",
" <td>155</td>\n",
" <td>217</td>\n",
" <td>274</td>\n",
" <td>389</td>\n",
" <td>2168</td>\n",
" <td>133</td>\n",
" <td>75</td>\n",
" <td>52</td>\n",
" <td>6</td>\n",
" <td>1713</td>\n",
" <td>143</td>\n",
" <td>104</td>\n",
" <td>34</td>\n",
" <td>5</td>\n",
" <td>0</td>\n",
" <td>151</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>13</td>\n",
" <td>13</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>5</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>58</td>\n",
" <td>71</td>\n",
" <td>53</td>\n",
" <td>17</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>539</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>12</td>\n",
" <td>11</td>\n",
" <td>1</td>\n",
" <td>12</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>8</td>\n",
" <td>239</td>\n",
" <td>9</td>\n",
" <td>13</td>\n",
" <td>14</td>\n",
" <td>30</td>\n",
" <td>173</td>\n",
" <td>20</td>\n",
" <td>11</td>\n",
" <td>7</td>\n",
" <td>2</td>\n",
" <td>248</td>\n",
" <td>7</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>267</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>02020</td>\n",
" <td>AK00101</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>AK00102-14</td>\n",
" <td>AK00102</td>\n",
" <td>2014</td>\n",
" <td>Cities from 25,000 thru 49,999</td>\n",
" <td>Pacific</td>\n",
" <td>AK - Alaska</td>\n",
" <td>Alaska</td>\n",
" <td>AK</td>\n",
" <td></td>\n",
" <td>19872</td>\n",
" <td>18</td>\n",
" <td>Y</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3030</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>32477</td>\n",
" <td>258</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>32505</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>FAIRBANKS</td>\n",
" <td>ALASKA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>FAIRBANKS POLICE DEPARTMENT</td>\n",
" <td>911 CUSHMAN ST</td>\n",
" <td>FAIRBANKS, AK</td>\n",
" <td>99701</td>\n",
" <td>4</td>\n",
" <td>1893</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>39</td>\n",
" <td>38</td>\n",
" <td>1</td>\n",
" <td>52</td>\n",
" <td>22</td>\n",
" <td>8</td>\n",
" <td>0</td>\n",
" <td>22</td>\n",
" <td>551</td>\n",
" <td>23</td>\n",
" <td>41</td>\n",
" <td>31</td>\n",
" <td>24</td>\n",
" <td>432</td>\n",
" <td>155</td>\n",
" <td>91</td>\n",
" <td>51</td>\n",
" <td>13</td>\n",
" <td>969</td>\n",
" <td>123</td>\n",
" <td>70</td>\n",
" <td>34</td>\n",
" <td>19</td>\n",
" <td>0</td>\n",
" <td>782</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>15</td>\n",
" <td>5</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>8</td>\n",
" <td>369</td>\n",
" <td>9</td>\n",
" <td>26</td>\n",
" <td>20</td>\n",
" <td>21</td>\n",
" <td>293</td>\n",
" <td>16</td>\n",
" <td>8</td>\n",
" <td>6</td>\n",
" <td>2</td>\n",
" <td>345</td>\n",
" <td>31</td>\n",
" <td>19</td>\n",
" <td>11</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>115</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>49</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>42</td>\n",
" <td>18</td>\n",
" <td>5</td>\n",
" <td>7</td>\n",
" <td>6</td>\n",
" <td>34</td>\n",
" <td>8</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>66</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>27</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>26</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>31</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>30</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>02090</td>\n",
" <td>AK00102</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>AK00117-14</td>\n",
" <td>AK00117</td>\n",
" <td>2014</td>\n",
" <td>Cities from 2,500 thru 9,999</td>\n",
" <td>Pacific</td>\n",
" <td>AK - Alaska</td>\n",
" <td>Alaska</td>\n",
" <td>AK</td>\n",
" <td></td>\n",
" <td>47202</td>\n",
" <td>00</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3030</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>6583</td>\n",
" <td>38</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6453</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>PALMER</td>\n",
" <td>ALASKA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>PALMER POLICE DEPARTMENT</td>\n",
" <td>423 S VALLEY WAY</td>\n",
" <td>PALMER, AK</td>\n",
" <td>99645</td>\n",
" <td>7</td>\n",
" <td>253</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>79</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>75</td>\n",
" <td>22</td>\n",
" <td>7</td>\n",
" <td>14</td>\n",
" <td>1</td>\n",
" <td>130</td>\n",
" <td>14</td>\n",
" <td>6</td>\n",
" <td>7</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>125</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6</td>\n",
" <td>5</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>77</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>72</td>\n",
" <td>8</td>\n",
" <td>1</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" <td>26</td>\n",
" <td>7</td>\n",
" <td>4</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>29</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>11</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>11</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>12</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>16</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>8</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>02170</td>\n",
" <td>AK00117</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AK00126-14</td>\n",
" <td>AK00126</td>\n",
" <td>2014</td>\n",
" <td>Cities under 2,500</td>\n",
" <td>Pacific</td>\n",
" <td>AK - Alaska</td>\n",
" <td>Alaska</td>\n",
" <td>AK</td>\n",
" <td></td>\n",
" <td>19875</td>\n",
" <td></td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3030</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>258</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>FAIRBANKS INTRNL AIRPORT</td>\n",
" <td>ALASKA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>FAIRBANKS INTERNATIONAL AIRPRT</td>\n",
" <td>6450 AIRPORT WAY</td>\n",
" <td>FAIRBANKS, AK</td>\n",
" <td>99709</td>\n",
" <td></td>\n",
" <td>22</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>11</td>\n",
" <td>4</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>9</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>02090</td>\n",
" <td>AK00126</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>AK00127-14</td>\n",
" <td>AK00127</td>\n",
" <td>2014</td>\n",
" <td>Cities under 2,500</td>\n",
" <td>Pacific</td>\n",
" <td>AK - Alaska</td>\n",
" <td>Alaska</td>\n",
" <td>AK</td>\n",
" <td></td>\n",
" <td>01317</td>\n",
" <td>00</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3030</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>38</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td></td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>ANCHORAGE INTRNTL AIRPOR</td>\n",
" <td>ALASKA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>ANCHORAGE INTERNATIONAL AIRPRT</td>\n",
" <td>PO BOX 190629</td>\n",
" <td>ANCHORAGE, AK</td>\n",
" <td>99519</td>\n",
" <td></td>\n",
" <td>144</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>5</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>5</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>133</td>\n",
" <td>4</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>12</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>8</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>02020</td>\n",
" <td>AK00127</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" key ori_code year group division state state_full state_abbr county seq_number juvenile_age core_city covered_by covered_group field_office months_reported agency_count population_city msa population_group_1 population_group_2 population_1 population_2 population_3 population_source follow_up_indication special_mailing_address special_mailing_group agency_name agency_state agency_address_1 agency_address_2 agency_address_3 agency_address_4 agency_zip_code old_population_group actual_grand_total actual_murder_total actual_manslaughter_total actual_rape_total actual_rape_forcible actual_rape_attempted actual_robbery_total actual_robbery_wgun actual_robbery_wknife actual_robbery_other actual_robbery_strongarm actual_assault_total actual_assault_wgun actual_assault_wknife actual_assault_other actual_assault_whands \\\n",
"0 AK00101-14 AK00101 2014 Cities from 250,000 thru 499,999 Pacific AK - Alaska Alaska AK 01302 18 Y 3030 12 1 301306 38 0 0 299455 0 0 2 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. ANCHORAGE ALASKA CHIEF OF POLICE ANCHORAGE POLICE DEPARTMENT 4501 ELMORE RD ANCHORAGE, AK 99507 3 18170 12 3 392 356 36 496 110 40 51 295 5736 294 311 490 610 \n",
"1 AK00102-14 AK00102 2014 Cities from 25,000 thru 49,999 Pacific AK - Alaska Alaska AK 19872 18 Y 3030 12 1 32477 258 0 0 32505 0 0 2 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. FAIRBANKS ALASKA CHIEF OF POLICE FAIRBANKS POLICE DEPARTMENT 911 CUSHMAN ST FAIRBANKS, AK 99701 4 1893 4 0 39 38 1 52 22 8 0 22 551 23 41 31 24 \n",
"2 AK00117-14 AK00117 2014 Cities from 2,500 thru 9,999 Pacific AK - Alaska Alaska AK 47202 00 N 3030 12 1 6583 38 0 0 6453 0 0 2 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. PALMER ALASKA CHIEF OF POLICE PALMER POLICE DEPARTMENT 423 S VALLEY WAY PALMER, AK 99645 7 253 0 0 7 6 1 1 1 0 0 0 79 1 1 2 0 \n",
"3 AK00126-14 AK00126 2014 Cities under 2,500 Pacific AK - Alaska Alaska AK 19875 N 3030 12 1 0 258 0 0 0 0 0 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. FAIRBANKS INTRNL AIRPORT ALASKA CHIEF OF POLICE FAIRBANKS INTERNATIONAL AIRPRT 6450 AIRPORT WAY FAIRBANKS, AK 99709 22 0 0 0 0 0 0 0 0 0 0 6 0 0 0 0 \n",
"4 AK00127-14 AK00127 2014 Cities under 2,500 Pacific AK - Alaska Alaska AK 01317 00 N 3030 12 1 0 38 0 0 0 0 0 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. ANCHORAGE INTRNTL AIRPOR ALASKA CHIEF OF POLICE ANCHORAGE INTERNATIONAL AIRPRT PO BOX 190629 ANCHORAGE, AK 99519 144 0 0 0 0 0 0 0 0 0 0 5 0 0 0 0 \n",
"\n",
" actual_assault_simple actual_burglary_total actual_burglary_forcible actual_burglary_notforcible actual_burglary_attempted actual_larceny_total actual_larceny_motor actual_larceny_auto actual_larceny_truck actual_larceny_other actual_larceny_lt_50 cleared_grand_total cleared_murder_total cleared_manslaughter_total cleared_rape_total cleared_rape_forcible cleared_rape_attempted cleared_robbery_total cleared_robbery_wgun cleared_robbery_wknife cleared_robbery_other cleared_robbery_strongarm cleared_assault_total cleared_assault_wgun cleared_assault_wknife cleared_assault_other cleared_assault_whands cleared_assault_simple cleared_burglary_total cleared_burglary_forcible cleared_burglary_notforcible cleared_burglary_attempted cleared_larceny_total cleared_larceny_motor cleared_larceny_auto cleared_larceny_truck cleared_larceny_other cleared_larceny_lt_50 unfounded_grand_total unfounded_murder_total unfounded_manslaughter_total \\\n",
"0 4031 1375 909 358 108 9217 939 673 194 72 0 5437 9 2 78 64 14 156 22 16 15 103 3203 155 217 274 389 2168 133 75 52 6 1713 143 104 34 5 0 151 0 0 \n",
"1 432 155 91 51 13 969 123 70 34 19 0 782 4 0 2 1 1 15 5 2 0 8 369 9 26 20 21 293 16 8 6 2 345 31 19 11 1 0 115 0 0 \n",
"2 75 22 7 14 1 130 14 6 7 1 0 125 0 0 6 5 1 1 1 0 0 0 77 1 2 2 0 72 8 1 6 1 26 7 4 3 0 0 29 0 0 \n",
"3 6 1 0 0 1 11 4 3 1 0 0 6 0 0 0 0 0 0 0 0 0 0 3 0 0 0 0 3 0 0 0 0 1 2 1 1 0 0 9 0 0 \n",
"4 5 2 1 1 0 133 4 3 1 0 0 12 0 0 0 0 0 0 0 0 0 0 3 0 0 0 0 3 1 0 1 0 8 0 0 0 0 0 3 0 0 \n",
"\n",
" unfounded_rape_total unfounded_rape_forcible unfounded_rape_attempted unfounded_robbery_total unfounded_robbery_wgun unfounded_robbery_wknife unfounded_robbery_other unfounded_robbery_strongarm unfounded_assault_total unfounded_assault_wgun unfounded_assault_wknife unfounded_assault_other unfounded_assault_whands unfounded_assault_simple unfounded_burglary_total unfounded_burglary_forcible unfounded_burglary_notforcible unfounded_burglary_attempted unfounded_larceny_total unfounded_larceny_motor unfounded_larceny_auto unfounded_larceny_truck unfounded_larceny_other unfounded_larceny_lt_50 under18_grand_total under18_murder_total under18_manslaughter_total under18_rape_total under18_rape_forcible under18_rape_attempted under18_robbery_total under18_robbery_wgun under18_robbery_wknife under18_robbery_other under18_robbery_strongarm under18_assault_total under18_assault_wgun under18_assault_wknife under18_assault_other under18_assault_whands \\\n",
"0 13 13 0 1 0 0 0 1 3 1 0 0 1 1 5 2 3 0 58 71 53 17 1 0 539 1 0 12 11 1 12 4 0 0 8 239 9 13 14 30 \n",
"1 4 4 0 2 0 0 0 2 49 2 3 1 1 42 18 5 7 6 34 8 6 1 1 0 66 3 0 0 0 0 1 0 0 0 1 27 0 1 0 0 \n",
"2 3 3 0 0 0 0 0 0 11 0 0 0 0 11 1 0 1 0 12 2 1 0 1 0 16 0 0 0 0 0 0 0 0 0 0 8 0 1 0 0 \n",
"3 0 0 0 0 0 0 0 0 2 0 0 0 0 2 0 0 0 0 7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"4 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"\n",
" under18_assault_simple under18_burglary_total under18_burglary_forcible under18_burglary_notforcible under18_burglary_attempted under18_larceny_total under18_larceny_motor under18_larceny_auto under18_larceny_truck under18_larceny_other under18_larceny_lt_50 officers_assaulted officers_killed_accident officers_killed_felonious FIPS ori \n",
"0 173 20 11 7 2 248 7 1 3 3 0 267 0 0 02020 AK00101 \n",
"1 26 1 0 0 1 31 3 1 2 0 0 30 0 0 02090 AK00102 \n",
"2 7 0 0 0 0 7 1 1 0 0 0 2 0 0 02170 AK00117 \n",
"3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 02090 AK00126 \n",
"4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 02020 AK00127 "
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"clearances_data_by_year = clearances_data_by_year.merge(ori_to_fips, left_on='ori_code', right_on='ori', how='inner')\n",
"clearances_data_by_year.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"What happens in this operation is for every row in the `clearances_data_by_year` it uses the value in the `ori_code` column to lookup the corresponding FIPS code in the `ori_to_fips` data. The result is a new column labeled `FIPS` is added to the DataFrame based on the data in provided in `ori_to_fips` giving us all of the data we need in one place in `clearances_data_by_year`."
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"fips_groups = clearances_data_by_year.groupby(['FIPS'])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Once the DataFrames are combined, we again use `groupby` function. Instead of producing an aggregation like we did before, we are now using `groupby` to create a series of DataFrames for each FIPS code. Think of it as taking the nationwide data and creating a subset for each county instead. By doing so, we can determine what percentage of police departments are reporting per county.\n",
"\n",
"In order to determine which counties have greater than 70% of the police departments reporting, there are two ways in which we can determine what percentage of the police departments per county are reporting crime statistics to the FBI's UCR program. The first is by counting how many police departments are represented per county, and of those, how many are actually reporting non-zero numbers to the FBI. And the second method is to use the populations covered by the police departments as a metric to gauge how much of the county's population is represented in the crime statistics.\n",
"\n",
"Consider the following hypothetical example: suppose there is a county that is home 5 police departments and 1,000,000 people. Police Department A covers 500,000 citizens, whereas Departments B, C, D, and E each cover 125,000 people. Using the first method, if Departments B, C, D, and E all are reporting, but Department A does not, then while 80% of all departments are reporting, it only represents 50% of the population, which is equally reflected of the crime per population for the county if only Department A reported numbers to the FBI, however, since one department only represents 20% of the total departments for that county, they wouldn't pass the greater than 70% threshold. Whereas if we calculate the percent reporting based upon the percent of the county's total population covered by those police departments, then we have a better reflection of the crime statistics per county because it is based on the population."
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": false
},
"source": [
"This is the method for determining if the number police departments reporting statistics to the FBI is greater than 70%:"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": false,
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Total number of counties in the UCR data: 1174\n",
"Number of counties with > 70% of police departments reporting: 711\n",
"Number of counties with < 70% of police departments reporting: 463\n"
]
}
],
"source": [
"data_columns = ['actual_grand_total', 'actual_murder_total', 'actual_manslaughter_total', 'actual_rape_total', 'actual_rape_forcible', 'actual_rape_attempted', 'actual_robbery_total', 'actual_robbery_wgun', 'actual_robbery_wknife', 'actual_robbery_other', 'actual_robbery_strongarm', 'actual_assault_total', 'actual_assault_wgun', 'actual_assault_wknife', 'actual_assault_other', 'actual_assault_whands', 'actual_assault_simple', 'actual_burglary_total', 'actual_burglary_forcible', 'actual_burglary_notforcible', 'actual_burglary_attempted', 'actual_larceny_total', 'actual_larceny_motor', 'actual_larceny_auto', 'actual_larceny_truck', 'actual_larceny_other', 'actual_larceny_lt_50', 'cleared_grand_total', 'cleared_murder_total', 'cleared_manslaughter_total', 'cleared_rape_total', 'cleared_rape_forcible', 'cleared_rape_attempted', 'cleared_robbery_total', 'cleared_robbery_wgun', 'cleared_robbery_wknife', 'cleared_robbery_other', 'cleared_robbery_strongarm', 'cleared_assault_total', 'cleared_assault_wgun', 'cleared_assault_wknife', 'cleared_assault_other', 'cleared_assault_whands', 'cleared_assault_simple', 'cleared_burglary_total', 'cleared_burglary_forcible', 'cleared_burglary_notforcible', 'cleared_burglary_attempted', 'cleared_larceny_total', 'cleared_larceny_motor', 'cleared_larceny_auto', 'cleared_larceny_truck', 'cleared_larceny_other', 'cleared_larceny_lt_50', 'unfounded_grand_total', 'unfounded_murder_total', 'unfounded_manslaughter_total', 'unfounded_rape_total', 'unfounded_rape_forcible', 'unfounded_rape_attempted', 'unfounded_robbery_total', 'unfounded_robbery_wgun', 'unfounded_robbery_wknife', 'unfounded_robbery_other', 'unfounded_robbery_strongarm', 'unfounded_assault_total', 'unfounded_assault_wgun', 'unfounded_assault_wknife', 'unfounded_assault_other', 'unfounded_assault_whands', 'unfounded_assault_simple', 'unfounded_burglary_total', 'unfounded_burglary_forcible', 'unfounded_burglary_notforcible', 'unfounded_burglary_attempted', 'unfounded_larceny_total', 'unfounded_larceny_motor', 'unfounded_larceny_auto', 'unfounded_larceny_truck', 'unfounded_larceny_other', 'unfounded_larceny_lt_50', 'under18_grand_total', 'under18_murder_total', 'under18_manslaughter_total', 'under18_rape_total', 'under18_rape_forcible', 'under18_rape_attempted', 'under18_robbery_total', 'under18_robbery_wgun', 'under18_robbery_wknife', 'under18_robbery_other', 'under18_robbery_strongarm', 'under18_assault_total', 'under18_assault_wgun', 'under18_assault_wknife', 'under18_assault_other', 'under18_assault_whands', 'under18_assault_simple', 'under18_burglary_total', 'under18_burglary_forcible', 'under18_burglary_notforcible', 'under18_burglary_attempted', 'under18_larceny_total', 'under18_larceny_motor', 'under18_larceny_auto', 'under18_larceny_truck', 'under18_larceny_other', 'under18_larceny_lt_50', 'officers_assaulted', 'officers_killed_accident', 'officers_killed_felonious']\n",
"counties_with_more_than_70_percent_reporting = []\n",
"counties_with_less_than_70_percent_reporting = []\n",
"\n",
"for FIPS_code, clearance_data in fips_groups:\n",
" \n",
" number_of_ORIs = float(len(clearance_data))\n",
" reporting_ORIs = []\n",
" \n",
" for index, row_data in clearance_data.iterrows():\n",
" if row_data[data_columns].sum() > 0:\n",
" reporting_ORIs.append(index)\n",
" \n",
" percent_reporting = len(reporting_ORIs) / number_of_ORIs\n",
" \n",
" if percent_reporting >= 0.7:\n",
" counties_with_more_than_70_percent_reporting.append(clearance_data)\n",
" else:\n",
" counties_with_less_than_70_percent_reporting.append(clearance_data)\n",
" \n",
"print 'Total number of counties in the UCR data: {}'.format(len(fips_groups))\n",
"print 'Number of counties with > 70% of police departments reporting: {}'.format(len(counties_with_more_than_70_percent_reporting))\n",
"print 'Number of counties with < 70% of police departments reporting: {}'.format(len(counties_with_less_than_70_percent_reporting))"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"collapsed": false,
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>key</th>\n",
" <th>ori_code</th>\n",
" <th>year</th>\n",
" <th>group</th>\n",
" <th>division</th>\n",
" <th>state</th>\n",
" <th>state_full</th>\n",
" <th>state_abbr</th>\n",
" <th>county</th>\n",
" <th>seq_number</th>\n",
" <th>juvenile_age</th>\n",
" <th>core_city</th>\n",
" <th>covered_by</th>\n",
" <th>covered_group</th>\n",
" <th>field_office</th>\n",
" <th>months_reported</th>\n",
" <th>agency_count</th>\n",
" <th>population_city</th>\n",
" <th>msa</th>\n",
" <th>population_group_1</th>\n",
" <th>population_group_2</th>\n",
" <th>population_1</th>\n",
" <th>population_2</th>\n",
" <th>population_3</th>\n",
" <th>population_source</th>\n",
" <th>follow_up_indication</th>\n",
" <th>special_mailing_address</th>\n",
" <th>special_mailing_group</th>\n",
" <th>agency_name</th>\n",
" <th>agency_state</th>\n",
" <th>agency_address_1</th>\n",
" <th>agency_address_2</th>\n",
" <th>agency_address_3</th>\n",
" <th>agency_address_4</th>\n",
" <th>agency_zip_code</th>\n",
" <th>old_population_group</th>\n",
" <th>actual_grand_total</th>\n",
" <th>actual_murder_total</th>\n",
" <th>actual_manslaughter_total</th>\n",
" <th>actual_rape_total</th>\n",
" <th>actual_rape_forcible</th>\n",
" <th>actual_rape_attempted</th>\n",
" <th>actual_robbery_total</th>\n",
" <th>actual_robbery_wgun</th>\n",
" <th>actual_robbery_wknife</th>\n",
" <th>actual_robbery_other</th>\n",
" <th>actual_robbery_strongarm</th>\n",
" <th>actual_assault_total</th>\n",
" <th>actual_assault_wgun</th>\n",
" <th>actual_assault_wknife</th>\n",
" <th>actual_assault_other</th>\n",
" <th>actual_assault_whands</th>\n",
" <th>actual_assault_simple</th>\n",
" <th>actual_burglary_total</th>\n",
" <th>actual_burglary_forcible</th>\n",
" <th>actual_burglary_notforcible</th>\n",
" <th>actual_burglary_attempted</th>\n",
" <th>actual_larceny_total</th>\n",
" <th>actual_larceny_motor</th>\n",
" <th>actual_larceny_auto</th>\n",
" <th>actual_larceny_truck</th>\n",
" <th>actual_larceny_other</th>\n",
" <th>actual_larceny_lt_50</th>\n",
" <th>cleared_grand_total</th>\n",
" <th>cleared_murder_total</th>\n",
" <th>cleared_manslaughter_total</th>\n",
" <th>cleared_rape_total</th>\n",
" <th>cleared_rape_forcible</th>\n",
" <th>cleared_rape_attempted</th>\n",
" <th>cleared_robbery_total</th>\n",
" <th>cleared_robbery_wgun</th>\n",
" <th>cleared_robbery_wknife</th>\n",
" <th>cleared_robbery_other</th>\n",
" <th>cleared_robbery_strongarm</th>\n",
" <th>cleared_assault_total</th>\n",
" <th>cleared_assault_wgun</th>\n",
" <th>cleared_assault_wknife</th>\n",
" <th>cleared_assault_other</th>\n",
" <th>cleared_assault_whands</th>\n",
" <th>cleared_assault_simple</th>\n",
" <th>cleared_burglary_total</th>\n",
" <th>cleared_burglary_forcible</th>\n",
" <th>cleared_burglary_notforcible</th>\n",
" <th>cleared_burglary_attempted</th>\n",
" <th>cleared_larceny_total</th>\n",
" <th>cleared_larceny_motor</th>\n",
" <th>cleared_larceny_auto</th>\n",
" <th>cleared_larceny_truck</th>\n",
" <th>cleared_larceny_other</th>\n",
" <th>cleared_larceny_lt_50</th>\n",
" <th>unfounded_grand_total</th>\n",
" <th>unfounded_murder_total</th>\n",
" <th>unfounded_manslaughter_total</th>\n",
" <th>unfounded_rape_total</th>\n",
" <th>unfounded_rape_forcible</th>\n",
" <th>unfounded_rape_attempted</th>\n",
" <th>unfounded_robbery_total</th>\n",
" <th>unfounded_robbery_wgun</th>\n",
" <th>unfounded_robbery_wknife</th>\n",
" <th>unfounded_robbery_other</th>\n",
" <th>unfounded_robbery_strongarm</th>\n",
" <th>unfounded_assault_total</th>\n",
" <th>unfounded_assault_wgun</th>\n",
" <th>unfounded_assault_wknife</th>\n",
" <th>unfounded_assault_other</th>\n",
" <th>unfounded_assault_whands</th>\n",
" <th>unfounded_assault_simple</th>\n",
" <th>unfounded_burglary_total</th>\n",
" <th>unfounded_burglary_forcible</th>\n",
" <th>unfounded_burglary_notforcible</th>\n",
" <th>unfounded_burglary_attempted</th>\n",
" <th>unfounded_larceny_total</th>\n",
" <th>unfounded_larceny_motor</th>\n",
" <th>unfounded_larceny_auto</th>\n",
" <th>unfounded_larceny_truck</th>\n",
" <th>unfounded_larceny_other</th>\n",
" <th>unfounded_larceny_lt_50</th>\n",
" <th>under18_grand_total</th>\n",
" <th>under18_murder_total</th>\n",
" <th>under18_manslaughter_total</th>\n",
" <th>under18_rape_total</th>\n",
" <th>under18_rape_forcible</th>\n",
" <th>under18_rape_attempted</th>\n",
" <th>under18_robbery_total</th>\n",
" <th>under18_robbery_wgun</th>\n",
" <th>under18_robbery_wknife</th>\n",
" <th>under18_robbery_other</th>\n",
" <th>under18_robbery_strongarm</th>\n",
" <th>under18_assault_total</th>\n",
" <th>under18_assault_wgun</th>\n",
" <th>under18_assault_wknife</th>\n",
" <th>under18_assault_other</th>\n",
" <th>under18_assault_whands</th>\n",
" <th>under18_assault_simple</th>\n",
" <th>under18_burglary_total</th>\n",
" <th>under18_burglary_forcible</th>\n",
" <th>under18_burglary_notforcible</th>\n",
" <th>under18_burglary_attempted</th>\n",
" <th>under18_larceny_total</th>\n",
" <th>under18_larceny_motor</th>\n",
" <th>under18_larceny_auto</th>\n",
" <th>under18_larceny_truck</th>\n",
" <th>under18_larceny_other</th>\n",
" <th>under18_larceny_lt_50</th>\n",
" <th>officers_assaulted</th>\n",
" <th>officers_killed_accident</th>\n",
" <th>officers_killed_felonious</th>\n",
" <th>FIPS</th>\n",
" <th>ori</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>71</th>\n",
" <td>AL00500-14</td>\n",
" <td>AL00500</td>\n",
" <td>2014</td>\n",
" <td>MSA counties from 25,000 thru 99,999</td>\n",
" <td>East South Central</td>\n",
" <td>AL - Alabama</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>002</td>\n",
" <td>70978</td>\n",
" <td>16</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3490</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>96312</td>\n",
" <td>201</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>95295</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>BALDWIN</td>\n",
" <td>ALA</td>\n",
" <td>SHERIFF</td>\n",
" <td>BALDWIN COUNTY</td>\n",
" <td>310 HAND AVE</td>\n",
" <td>BAY MINETTE, AL</td>\n",
" <td>36507</td>\n",
" <td>9</td>\n",
" <td>1397</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>18</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>16</td>\n",
" <td>1</td>\n",
" <td>713</td>\n",
" <td>5</td>\n",
" <td>25</td>\n",
" <td>41</td>\n",
" <td>5</td>\n",
" <td>637</td>\n",
" <td>230</td>\n",
" <td>121</td>\n",
" <td>106</td>\n",
" <td>3</td>\n",
" <td>405</td>\n",
" <td>27</td>\n",
" <td>11</td>\n",
" <td>5</td>\n",
" <td>11</td>\n",
" <td>0</td>\n",
" <td>770</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>15</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>14</td>\n",
" <td>1</td>\n",
" <td>571</td>\n",
" <td>3</td>\n",
" <td>20</td>\n",
" <td>33</td>\n",
" <td>2</td>\n",
" <td>513</td>\n",
" <td>48</td>\n",
" <td>23</td>\n",
" <td>22</td>\n",
" <td>3</td>\n",
" <td>123</td>\n",
" <td>10</td>\n",
" <td>5</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>26</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>10</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>10</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>10</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>30</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>26</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>22</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>01003</td>\n",
" <td>AL00500</td>\n",
" </tr>\n",
" <tr>\n",
" <th>72</th>\n",
" <td>AL00501-14</td>\n",
" <td>AL00501</td>\n",
" <td>2014</td>\n",
" <td>Cities from 2,500 thru 9,999</td>\n",
" <td>East South Central</td>\n",
" <td>AL - Alabama</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>002</td>\n",
" <td>03655</td>\n",
" <td>16</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3490</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>9087</td>\n",
" <td>201</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>8398</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>BAY MINETTE</td>\n",
" <td>ALA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>BAY MINETTE POLICE DEPARTMENT</td>\n",
" <td>301 D'OLIVE ST</td>\n",
" <td>BAY MINETTE, AL</td>\n",
" <td>36507</td>\n",
" <td>5</td>\n",
" <td>542</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>11</td>\n",
" <td>9</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>207</td>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>25</td>\n",
" <td>4</td>\n",
" <td>174</td>\n",
" <td>58</td>\n",
" <td>35</td>\n",
" <td>23</td>\n",
" <td>0</td>\n",
" <td>255</td>\n",
" <td>8</td>\n",
" <td>6</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>178</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>8</td>\n",
" <td>8</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>62</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>9</td>\n",
" <td>2</td>\n",
" <td>49</td>\n",
" <td>17</td>\n",
" <td>14</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>87</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>19</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>10</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>9</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>01003</td>\n",
" <td>AL00501</td>\n",
" </tr>\n",
" <tr>\n",
" <th>73</th>\n",
" <td>AL00502-14</td>\n",
" <td>AL00502</td>\n",
" <td>2014</td>\n",
" <td>Cities from 10,000 thru 24,999</td>\n",
" <td>East South Central</td>\n",
" <td>AL - Alabama</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>002</td>\n",
" <td>20058</td>\n",
" <td>16</td>\n",
" <td>Y</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3490</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>17914</td>\n",
" <td>201</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>16847</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>FAIRHOPE</td>\n",
" <td>ALA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>FAIRHOPE POLICE DEPARTMENT</td>\n",
" <td>PO BOX 429</td>\n",
" <td>FAIRHOPE, AL</td>\n",
" <td>36533</td>\n",
" <td>6</td>\n",
" <td>1027</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>6</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>453</td>\n",
" <td>3</td>\n",
" <td>7</td>\n",
" <td>28</td>\n",
" <td>6</td>\n",
" <td>409</td>\n",
" <td>111</td>\n",
" <td>64</td>\n",
" <td>38</td>\n",
" <td>9</td>\n",
" <td>443</td>\n",
" <td>10</td>\n",
" <td>10</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>460</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>317</td>\n",
" <td>1</td>\n",
" <td>6</td>\n",
" <td>19</td>\n",
" <td>5</td>\n",
" <td>286</td>\n",
" <td>20</td>\n",
" <td>11</td>\n",
" <td>8</td>\n",
" <td>1</td>\n",
" <td>115</td>\n",
" <td>6</td>\n",
" <td>6</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>40</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>28</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>8</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>01003</td>\n",
" <td>AL00502</td>\n",
" </tr>\n",
" <tr>\n",
" <th>74</th>\n",
" <td>AL00503-14</td>\n",
" <td>AL00503</td>\n",
" <td>2014</td>\n",
" <td>Cities from 10,000 thru 24,999</td>\n",
" <td>East South Central</td>\n",
" <td>AL - Alabama</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>002</td>\n",
" <td>21144</td>\n",
" <td>16</td>\n",
" <td>Y</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3490</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>16101</td>\n",
" <td>201</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>15615</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>FOLEY</td>\n",
" <td>ALA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>FOLEY POLICE DEPARTMENT</td>\n",
" <td>200 E SECTION AVE</td>\n",
" <td>FOLEY, AL</td>\n",
" <td>36535</td>\n",
" <td>6</td>\n",
" <td>1095</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>13</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>5</td>\n",
" <td>297</td>\n",
" <td>2</td>\n",
" <td>9</td>\n",
" <td>24</td>\n",
" <td>1</td>\n",
" <td>261</td>\n",
" <td>98</td>\n",
" <td>53</td>\n",
" <td>36</td>\n",
" <td>9</td>\n",
" <td>664</td>\n",
" <td>21</td>\n",
" <td>17</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>615</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>10</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>5</td>\n",
" <td>198</td>\n",
" <td>1</td>\n",
" <td>9</td>\n",
" <td>20</td>\n",
" <td>1</td>\n",
" <td>167</td>\n",
" <td>31</td>\n",
" <td>19</td>\n",
" <td>11</td>\n",
" <td>1</td>\n",
" <td>356</td>\n",
" <td>18</td>\n",
" <td>15</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>25</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>19</td>\n",
" <td>5</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>60</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>17</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>14</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>41</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>01003</td>\n",
" <td>AL00503</td>\n",
" </tr>\n",
" <tr>\n",
" <th>75</th>\n",
" <td>AL00504-14</td>\n",
" <td>AL00504</td>\n",
" <td>2014</td>\n",
" <td>Cities from 10,000 thru 24,999</td>\n",
" <td>East South Central</td>\n",
" <td>AL - Alabama</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>002</td>\n",
" <td>25356</td>\n",
" <td>00</td>\n",
" <td>N</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>3490</td>\n",
" <td>12</td>\n",
" <td>1</td>\n",
" <td>10927</td>\n",
" <td>201</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>10233</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>Send a Follow-Up</td>\n",
" <td>Not a special mailing address</td>\n",
" <td>Agency is a Contributor but not on the mailing list, they are not sent forms.</td>\n",
" <td>GULF SHORES</td>\n",
" <td>ALA</td>\n",
" <td>CHIEF OF POLICE</td>\n",
" <td>GULF SHORES POLICE DEPARTMENT</td>\n",
" <td>PO BOX 896</td>\n",
" <td>GULF SHORES, AL</td>\n",
" <td>36547</td>\n",
" <td>7</td>\n",
" <td>980</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>7</td>\n",
" <td>0</td>\n",
" <td>6</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>301</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>16</td>\n",
" <td>8</td>\n",
" <td>274</td>\n",
" <td>127</td>\n",
" <td>48</td>\n",
" <td>79</td>\n",
" <td>0</td>\n",
" <td>515</td>\n",
" <td>23</td>\n",
" <td>22</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>256</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>5</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>157</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>9</td>\n",
" <td>2</td>\n",
" <td>143</td>\n",
" <td>6</td>\n",
" <td>4</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>82</td>\n",
" <td>5</td>\n",
" <td>5</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>11</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>6</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>01003</td>\n",
" <td>AL00504</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" key ori_code year group division state state_full state_abbr county seq_number juvenile_age core_city covered_by covered_group field_office months_reported agency_count population_city msa population_group_1 population_group_2 population_1 population_2 population_3 population_source follow_up_indication special_mailing_address special_mailing_group agency_name agency_state agency_address_1 agency_address_2 agency_address_3 agency_address_4 agency_zip_code old_population_group actual_grand_total actual_murder_total actual_manslaughter_total actual_rape_total actual_rape_forcible actual_rape_attempted actual_robbery_total actual_robbery_wgun actual_robbery_wknife actual_robbery_other actual_robbery_strongarm actual_assault_total actual_assault_wgun actual_assault_wknife actual_assault_other actual_assault_whands \\\n",
"71 AL00500-14 AL00500 2014 MSA counties from 25,000 thru 99,999 East South Central AL - Alabama Alabama AL 002 70978 16 N 3490 12 1 96312 201 0 0 95295 0 0 2 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. BALDWIN ALA SHERIFF BALDWIN COUNTY 310 HAND AVE BAY MINETTE, AL 36507 9 1397 0 0 4 4 0 18 1 0 16 1 713 5 25 41 5 \n",
"72 AL00501-14 AL00501 2014 Cities from 2,500 thru 9,999 East South Central AL - Alabama Alabama AL 002 03655 16 N 3490 12 1 9087 201 0 0 8398 0 0 2 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. BAY MINETTE ALA CHIEF OF POLICE BAY MINETTE POLICE DEPARTMENT 301 D'OLIVE ST BAY MINETTE, AL 36507 5 542 0 0 3 3 0 11 9 1 1 0 207 0 4 25 4 \n",
"73 AL00502-14 AL00502 2014 Cities from 10,000 thru 24,999 East South Central AL - Alabama Alabama AL 002 20058 16 Y 3490 12 1 17914 201 0 0 16847 0 0 2 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. FAIRHOPE ALA CHIEF OF POLICE FAIRHOPE POLICE DEPARTMENT PO BOX 429 FAIRHOPE, AL 36533 6 1027 0 0 3 3 0 7 6 0 0 1 453 3 7 28 6 \n",
"74 AL00503-14 AL00503 2014 Cities from 10,000 thru 24,999 East South Central AL - Alabama Alabama AL 002 21144 16 Y 3490 12 1 16101 201 0 0 15615 0 0 2 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. FOLEY ALA CHIEF OF POLICE FOLEY POLICE DEPARTMENT 200 E SECTION AVE FOLEY, AL 36535 6 1095 1 0 1 1 0 13 6 1 1 5 297 2 9 24 1 \n",
"75 AL00504-14 AL00504 2014 Cities from 10,000 thru 24,999 East South Central AL - Alabama Alabama AL 002 25356 00 N 3490 12 1 10927 201 0 0 10233 0 0 2 Send a Follow-Up Not a special mailing address Agency is a Contributor but not on the mailing list, they are not sent forms. GULF SHORES ALA CHIEF OF POLICE GULF SHORES POLICE DEPARTMENT PO BOX 896 GULF SHORES, AL 36547 7 980 1 0 7 7 0 6 3 0 1 2 301 2 1 16 8 \n",
"\n",
" actual_assault_simple actual_burglary_total actual_burglary_forcible actual_burglary_notforcible actual_burglary_attempted actual_larceny_total actual_larceny_motor actual_larceny_auto actual_larceny_truck actual_larceny_other actual_larceny_lt_50 cleared_grand_total cleared_murder_total cleared_manslaughter_total cleared_rape_total cleared_rape_forcible cleared_rape_attempted cleared_robbery_total cleared_robbery_wgun cleared_robbery_wknife cleared_robbery_other cleared_robbery_strongarm cleared_assault_total cleared_assault_wgun cleared_assault_wknife cleared_assault_other cleared_assault_whands cleared_assault_simple cleared_burglary_total cleared_burglary_forcible cleared_burglary_notforcible cleared_burglary_attempted cleared_larceny_total cleared_larceny_motor cleared_larceny_auto cleared_larceny_truck cleared_larceny_other cleared_larceny_lt_50 unfounded_grand_total unfounded_murder_total unfounded_manslaughter_total \\\n",
"71 637 230 121 106 3 405 27 11 5 11 0 770 0 0 3 3 0 15 0 0 14 1 571 3 20 33 2 513 48 23 22 3 123 10 5 2 3 0 26 0 0 \n",
"72 174 58 35 23 0 255 8 6 2 0 0 178 0 0 2 2 0 8 8 0 0 0 62 0 2 9 2 49 17 14 3 0 87 2 1 1 0 0 6 0 0 \n",
"73 409 111 64 38 9 443 10 10 0 0 0 460 0 0 1 1 0 1 1 0 0 0 317 1 6 19 5 286 20 11 8 1 115 6 6 0 0 0 40 0 0 \n",
"74 261 98 53 36 9 664 21 17 1 3 0 615 1 0 1 1 0 10 4 0 1 5 198 1 9 20 1 167 31 19 11 1 356 18 15 0 3 0 25 0 0 \n",
"75 274 127 48 79 0 515 23 22 0 1 0 256 0 0 1 1 0 5 3 0 1 1 157 2 1 9 2 143 6 4 2 0 82 5 5 0 0 0 1 0 0 \n",
"\n",
" unfounded_rape_total unfounded_rape_forcible unfounded_rape_attempted unfounded_robbery_total unfounded_robbery_wgun unfounded_robbery_wknife unfounded_robbery_other unfounded_robbery_strongarm unfounded_assault_total unfounded_assault_wgun unfounded_assault_wknife unfounded_assault_other unfounded_assault_whands unfounded_assault_simple unfounded_burglary_total unfounded_burglary_forcible unfounded_burglary_notforcible unfounded_burglary_attempted unfounded_larceny_total unfounded_larceny_motor unfounded_larceny_auto unfounded_larceny_truck unfounded_larceny_other unfounded_larceny_lt_50 under18_grand_total under18_murder_total under18_manslaughter_total under18_rape_total under18_rape_forcible under18_rape_attempted under18_robbery_total under18_robbery_wgun under18_robbery_wknife under18_robbery_other under18_robbery_strongarm under18_assault_total under18_assault_wgun under18_assault_wknife under18_assault_other under18_assault_whands \\\n",
"71 2 2 0 1 0 0 1 0 10 0 0 0 0 10 3 0 2 1 10 0 0 0 0 0 30 0 0 0 0 0 0 0 0 0 0 26 0 2 2 0 \n",
"72 0 0 0 0 0 0 0 0 2 0 0 0 0 2 1 0 1 0 3 0 0 0 0 0 19 0 0 0 0 0 0 0 0 0 0 10 0 0 1 0 \n",
"73 2 2 0 1 0 0 0 1 4 0 0 0 0 4 4 0 4 0 28 1 1 0 0 0 8 0 0 0 0 0 0 0 0 0 0 4 0 1 0 0 \n",
"74 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 19 5 4 0 1 0 60 0 0 0 0 0 0 0 0 0 0 17 1 0 2 0 \n",
"75 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 11 0 0 1 1 0 0 0 0 0 0 3 0 0 0 0 \n",
"\n",
" under18_assault_simple under18_burglary_total under18_burglary_forcible under18_burglary_notforcible under18_burglary_attempted under18_larceny_total under18_larceny_motor under18_larceny_auto under18_larceny_truck under18_larceny_other under18_larceny_lt_50 officers_assaulted officers_killed_accident officers_killed_felonious FIPS ori \n",
"71 22 1 0 1 0 3 0 0 0 0 0 1 0 0 01003 AL00500 \n",
"72 9 2 1 1 0 7 0 0 0 0 0 0 0 0 01003 AL00501 \n",
"73 3 0 0 0 0 4 0 0 0 0 0 0 0 0 01003 AL00502 \n",
"74 14 2 0 2 0 41 0 0 0 0 0 0 0 0 01003 AL00503 \n",
"75 3 1 1 0 0 6 0 0 0 0 0 2 0 0 01003 AL00504 "
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.concat(counties_with_more_than_70_percent_reporting).head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"And this is the method for determining if the percent of the population covered by the departments reporting statistics to the FBI is greater than 70% of the county's population:"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Total number of counties in the UCR data: 1174\n",
"Number of counties with > 70% of the population represented in the police departments reporting: 1104\n",
"Number of counties with < 70% of the population represented in the police departments reporting: 61\n"
]
}
],
"source": [
"data_columns = ['actual_grand_total', 'actual_murder_total', 'actual_manslaughter_total', 'actual_rape_total', 'actual_rape_forcible', 'actual_rape_attempted', 'actual_robbery_total', 'actual_robbery_wgun', 'actual_robbery_wknife', 'actual_robbery_other', 'actual_robbery_strongarm', 'actual_assault_total', 'actual_assault_wgun', 'actual_assault_wknife', 'actual_assault_other', 'actual_assault_whands', 'actual_assault_simple', 'actual_burglary_total', 'actual_burglary_forcible', 'actual_burglary_notforcible', 'actual_burglary_attempted', 'actual_larceny_total', 'actual_larceny_motor', 'actual_larceny_auto', 'actual_larceny_truck', 'actual_larceny_other', 'actual_larceny_lt_50', 'cleared_grand_total', 'cleared_murder_total', 'cleared_manslaughter_total', 'cleared_rape_total', 'cleared_rape_forcible', 'cleared_rape_attempted', 'cleared_robbery_total', 'cleared_robbery_wgun', 'cleared_robbery_wknife', 'cleared_robbery_other', 'cleared_robbery_strongarm', 'cleared_assault_total', 'cleared_assault_wgun', 'cleared_assault_wknife', 'cleared_assault_other', 'cleared_assault_whands', 'cleared_assault_simple', 'cleared_burglary_total', 'cleared_burglary_forcible', 'cleared_burglary_notforcible', 'cleared_burglary_attempted', 'cleared_larceny_total', 'cleared_larceny_motor', 'cleared_larceny_auto', 'cleared_larceny_truck', 'cleared_larceny_other', 'cleared_larceny_lt_50', 'unfounded_grand_total', 'unfounded_murder_total', 'unfounded_manslaughter_total', 'unfounded_rape_total', 'unfounded_rape_forcible', 'unfounded_rape_attempted', 'unfounded_robbery_total', 'unfounded_robbery_wgun', 'unfounded_robbery_wknife', 'unfounded_robbery_other', 'unfounded_robbery_strongarm', 'unfounded_assault_total', 'unfounded_assault_wgun', 'unfounded_assault_wknife', 'unfounded_assault_other', 'unfounded_assault_whands', 'unfounded_assault_simple', 'unfounded_burglary_total', 'unfounded_burglary_forcible', 'unfounded_burglary_notforcible', 'unfounded_burglary_attempted', 'unfounded_larceny_total', 'unfounded_larceny_motor', 'unfounded_larceny_auto', 'unfounded_larceny_truck', 'unfounded_larceny_other', 'unfounded_larceny_lt_50', 'under18_grand_total', 'under18_murder_total', 'under18_manslaughter_total', 'under18_rape_total', 'under18_rape_forcible', 'under18_rape_attempted', 'under18_robbery_total', 'under18_robbery_wgun', 'under18_robbery_wknife', 'under18_robbery_other', 'under18_robbery_strongarm', 'under18_assault_total', 'under18_assault_wgun', 'under18_assault_wknife', 'under18_assault_other', 'under18_assault_whands', 'under18_assault_simple', 'under18_burglary_total', 'under18_burglary_forcible', 'under18_burglary_notforcible', 'under18_burglary_attempted', 'under18_larceny_total', 'under18_larceny_motor', 'under18_larceny_auto', 'under18_larceny_truck', 'under18_larceny_other', 'under18_larceny_lt_50', 'officers_assaulted', 'officers_killed_accident', 'officers_killed_felonious']\n",
"counties_with_more_than_70_percent_reporting_pop = []\n",
"counties_with_less_than_70_percent_reporting_pop = []\n",
"\n",
"for FIPS_code, clearance_data in fips_groups:\n",
" \n",
" if clearance_data.population_city.values.sum() > 0:\n",
" \n",
" county_population_total = clearance_data.population_city.values.sum()\n",
" population_of_reporting_ORI = 0\n",
"\n",
" for index, row_data in clearance_data.iterrows():\n",
" if row_data[data_columns].sum() > 0:\n",
" population_of_reporting_ORI += row_data.population_city\n",
"\n",
" percent_reporting = population_of_reporting_ORI / float(county_population_total)\n",
"\n",
" if percent_reporting >= 0.7:\n",
" counties_with_more_than_70_percent_reporting_pop.append(clearance_data)\n",
" else:\n",
" counties_with_less_than_70_percent_reporting_pop.append(clearance_data)\n",
" else:\n",
" continue\n",
" \n",
"print 'Total number of counties in the UCR data: {}'.format(len(fips_groups))\n",
"print 'Number of counties with > 70% of the population represented in the police departments reporting: {}'.format(len(counties_with_more_than_70_percent_reporting_pop))\n",
"print 'Number of counties with < 70% of the population represented in the police departments reporting: {}'.format(len(counties_with_less_than_70_percent_reporting_pop))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can see how these two methods produce significantly different results when we divide each by the total number of counties in the data:"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Percent of counties with > 70% of police departments reporting data to the FBI:\n",
"0.61%\n"
]
}
],
"source": [
"total_counties_in_the_UCR_data = len(fips_groups)\n",
"number_of_counties_with_more_than_70 = len(counties_with_more_than_70_percent_reporting)\n",
"print 'Percent of counties with > 70% of police departments reporting data to the FBI:'\n",
"print '{0:.2f}%' .format(number_of_counties_with_more_than_70 / float(total_counties_in_the_UCR_data))"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"collapsed": false,
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Percent of counties with > 70% of the population covered by the police departments \n",
"reporting data to the FBI:\n",
"0.94%\n"
]
}
],
"source": [
"total_counties_in_the_UCR_data = len(fips_groups)\n",
"number_of_counties_with_more_than_70_population = len(counties_with_more_than_70_percent_reporting_pop)\n",
"print 'Percent of counties with > 70% of the population covered by the police departments \\nreporting data to the FBI:'\n",
"print '{0:.2f}%' .format(number_of_counties_with_more_than_70_population / float(total_counties_in_the_UCR_data))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"While this data represents the offical and most comprehensive look at the state of crime and the effectiveness of policing throughout the country, since the UCR program is voluntary, many of the police departments in the country do not participate in the program. This leaves massive gaps in our ability to fully analyze the crime statistics in the United States. Many precincts are missing from the data, in recent years, notably, New York City have failed to report data to the UCR program. Additionally, sometimes entire states exempt themselves from reporting crime statistics to the FBI, or include statistics for clearances but not arrests, such as Alabama, Florida, and Illinois."
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 2",
"language": "python",
"name": "python2"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 2
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython2",
"version": "2.7.10"
}
},
"nbformat": 4,
"nbformat_minor": 0
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment