FBI Crime Data API.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "611ad43d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-22T00:19:38.721289Z",
     "start_time": "2023-06-22T00:19:38.716290Z"
    }
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import dill\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from requests.exceptions import RequestException\n",
    "\n",
    "api = 'sqVBzLYS6LjUvcsegM3scIPEFIJbGhhWQaIOgN3o'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c9d15ce6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-22T00:19:39.876635Z",
     "start_time": "2023-06-22T00:19:39.841634Z"
    }
   },
   "outputs": [],
   "source": [
    "# Loads data from FBI API\n",
    "\n",
    "crime_df_agency = pd.read_pickle('crime_df_agency.pkl')\n",
    "USAandCO_df = pd.read_pickle('USAandCO_df.pkl')\n",
    "# with open('mvtdata.pkd', 'rb') as f:\n",
    "#     mvtdict = dill.load(f)\n",
    "    \n",
    "# with open('burgdata.pkd', 'rb') as f:\n",
    "#     burgdict = dill.load(f)\n",
    "\n",
    "#crime_df_agency.to_pickle('crime_df_agency.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "2f12c020",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-24T04:25:11.845951Z",
     "start_time": "2023-06-24T04:25:11.838950Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['agency', 'year', 'mvt stolen value total', 'mvt offense count',\n",
       "       'mvt cleared', 'county_name', 'agency_type_name',\n",
       "       'burglary value total', 'burglary offense count', 'robbery value total',\n",
       "       'robbery offense count', 'larceny value total', 'larceny offense count',\n",
       "       'homicide cleared', 'homicide actual', 'aggravated-assault cleared',\n",
       "       'aggravated-assault actual', 'arson cleared', 'arson actual',\n",
       "       'Curfew and Loitering Law Violations', 'Disorderly Conduct',\n",
       "       'Driving Under the Influence', 'Drug Abuse Violations - Grand Total',\n",
       "       'Drunkenness', 'Embezzlement', 'Forgery and Counterfeiting', 'Fraud',\n",
       "       'Gambling - Total', 'Human Trafficking - Commercial Sex Acts',\n",
       "       'Human Trafficking - Involuntary Servitude', 'Larceny - Theft',\n",
       "       'Liquor Laws', 'Manslaughter by Negligence',\n",
       "       'Offenses Against the Family and Children',\n",
       "       'Prostitution and Commercialized Vice', 'Rape', 'Simple Assault',\n",
       "       'Stolen Property: Buying, Receiving, Possessing', 'Suspicion',\n",
       "       'Vagrancy', 'Vandalism', 'Weapons: Carrying, Possessing, Etc.',\n",
       "       'Opium or Cocaine or Their Derivatives',\n",
       "       'Other - Dangerous Nonnarcotic Drugs', 'Synthetic Narcotics',\n",
       "       'Drug Sale/Manufacturing - Subtotal'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Columns included in crime_df_agency, assembled in this notebook\n",
    "crime_df_agency.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce9e34de",
   "metadata": {},
   "source": [
    "# Methodology / Notes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7d02553",
   "metadata": {},
   "source": [
    "This notebook ingests and processes data from the FBI Crime Data API. <br>\n",
    "Documentation: https://cde.ucr.cjis.gov/LATEST/webapp/#/pages/docApi <br> <br>\n",
    "The end data processing for this project is performed on data grouped by county. However, the dataframe <b>'crime_df_agency.pkl'</b>, in which the data is still grouped by agency, was saved to disk to maintain information related to how crime was reported within Colorado.  The data was processed to enable easy conversion to county grouping when needed. No further data processing or feature engineering is performed in this notebook. <br> \n",
    "\n",
    "The section 'Linked Offenses' was added to double check if offenses that were revealed to be correlated are frequently part of the same crime.\n",
    "\n",
    "Information was obtained from the 'Agency', 'Arrest', 'Expanded Property', and 'Summarized' server variables. Each batch of API requests takes about 5-10 minutes with the current grouping. The requests for 'Expanded Property' are needlessly separated by offense-- if necessary to run again, I would recommend altering the code to run more similarly to the 'Arrests' section. <br>\n",
    "\n",
    "<b>General outline:</b>\n",
    "1) Information on reporting agencies within Colorado were downloaded using the url format: <br>\n",
    "'https://api.usa.gov/crime/fbi/cde/agency/byStateAbbr/CO?' <br>\n",
    "2) Sixteen agencies have their jurisdiction listed in multiple counties. Most of these are small towns, but this also included cities Aurora and Westminster. Both of these cities were in the top 10 worst cities for automobile theft within the entire USA for 2022, so it seemed important to account for them in a more detailed fashion than dividing all values for a city evenly between its counties.  Accordingly, counts were distributed among the different counties a city occupies based on the relative populations reported by the 2020 US Census (taken from Wikipedia). When this info was unavailable, as it was for a few smaller cities, a qualitative comparison of city / county borders was used. The distributions were encoded into a dictionary for later use.\n",
    "3) Motor vehicle theft (mvt) information was downloaded from both the 'Expanded Property' and 'Summarized' databases. The former contains information on the value that was stolen for each year, while the latter contains information on how many cases were cleared in a specific year.\n",
    "4) Burglary, robbery, and larceny data was only obtained from the 'Expanded Property' database, therefore, clearance information is currently not present in the crime dataframe, though values stolen are.\n",
    "5) Homicide, aggravated assault, and arson offenses / amounts cleared were obtained from the 'Summarized' database.\n",
    "6) Information on arrests for various crimes was obtained from the 'Arrests' database. Additional information can be obtained by querying specific offenses, but this was only done for 'drug sales', as I think drug use patterns are potentially more relevant than e.g. gambling offenses. Trying to obtain more detailed info on 'drug possession' returned a broken link, though it's listed as a valid query.\n",
    "7) A new dataframe was created from a merge of the various API queries and the agency info. New rows were created for multi-county agencies, with the crime data distributed according to the previously decided appropriate values.  The old, multi-county rows were dropped.\n",
    "\n",
    "<b>Notes</b>\n",
    "- Counts for homicide and motor vehicle thefts were checked at various stages with external sources, and matched well. Along with errors in code, there was also the possibility that my ignoring agencies without a specified county (e.g. State Police) would lead to a miscount.  This did not appear to be the case.\n",
    "- Reporting consistency seems good. There were 4 agencies with no county: Southern Ute Tribal, Colorado Bureau of Investigation,  State Patrol, and Ute Mountain Tribal, leaving 243 agencies included in the dataframe. For 22 years of data, 5,346 rows should be expected. Prior to distributing by county, the dataframe has 4,898 rows, indicating the dataset is 92% complete regarding MVT offense counts. Qualitatively, while I was analyzing the data, it seemed any lack of reporting was from small agencies where they possibly had no offenses to report.\n",
    "- It does seem as though most NaN or null values are just cases where the value is 0, and the agency didn't bother reporting it. However, just as a check: there are 28,031 cells with null values out of 225,308 total, or about 12%. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbcae1fb",
   "metadata": {},
   "source": [
    "# FBI Crime Data API"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e21c6207",
   "metadata": {},
   "source": [
    "Base URL: api.usa.gov/crime/fbi/cde/ <br>\n",
    "API Key:  sqVBzLYS6LjUvcsegM3scIPEFIJbGhhWQaIOgN3o <br>\n",
    "Denver ORI: CODPD0000"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cf1c064",
   "metadata": {},
   "source": [
    "### Linked Offenses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8fc383f4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-22T00:19:48.440958Z",
     "start_time": "2023-06-22T00:19:46.996950Z"
    }
   },
   "outputs": [],
   "source": [
    "mvt_linked = requests.get('https://api.usa.gov/crime/fbi/cde/nibrs/state/CO/motor-vehicle-theft/offense/linkedoffense?',\n",
    "                       params = {'API_KEY': api})\n",
    "linked_offenses = pd.DataFrame(mvt_linked.json()['data'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c3bba0f0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-22T00:21:48.885711Z",
     "start_time": "2023-06-22T00:21:48.865640Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['state_abbr', 'year', 'population', 'violent_crime', 'homicide',\n",
       "       'robbery', 'aggravated_assault', 'property_crime', 'burglary',\n",
       "       'larceny', 'motor_vehicle_theft', 'arson'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CO_df = USAandCO_df[USAandCO_df['state_abbr'] == 'CO']\n",
    "CO_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d4a45f22",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-22T00:19:50.229993Z",
     "start_time": "2023-06-22T00:19:50.215997Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ruggb\\AppData\\Local\\Temp\\ipykernel_23216\\2344074352.py:1: FutureWarning: The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.\n",
      "  CO_df2 = crime_df_agency.groupby('year').sum()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Index(['mvt stolen value total', 'mvt offense count', 'mvt cleared',\n",
       "       'burglary value total', 'burglary offense count', 'robbery value total',\n",
       "       'robbery offense count', 'larceny value total', 'larceny offense count',\n",
       "       'homicide cleared', 'homicide actual', 'aggravated-assault cleared',\n",
       "       'aggravated-assault actual', 'arson cleared', 'arson actual',\n",
       "       'Curfew and Loitering Law Violations', 'Disorderly Conduct',\n",
       "       'Driving Under the Influence', 'Drug Abuse Violations - Grand Total',\n",
       "       'Drunkenness', 'Embezzlement', 'Forgery and Counterfeiting', 'Fraud',\n",
       "       'Gambling - Total', 'Human Trafficking - Commercial Sex Acts',\n",
       "       'Human Trafficking - Involuntary Servitude', 'Larceny - Theft',\n",
       "       'Liquor Laws', 'Manslaughter by Negligence',\n",
       "       'Offenses Against the Family and Children',\n",
       "       'Prostitution and Commercialized Vice', 'Rape', 'Simple Assault',\n",
       "       'Stolen Property: Buying, Receiving, Possessing', 'Suspicion',\n",
       "       'Vagrancy', 'Vandalism', 'Weapons: Carrying, Possessing, Etc.',\n",
       "       'Opium or Cocaine or Their Derivatives',\n",
       "       'Other - Dangerous Nonnarcotic Drugs', 'Synthetic Narcotics',\n",
       "       'Drug Sale/Manufacturing - Subtotal'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CO_df2 = crime_df_agency.groupby('year').sum()\n",
    "CO_df2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f2d434f9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-15T01:34:33.681854Z",
     "start_time": "2023-06-15T01:34:33.670852Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['data_year', 'Identity Theft', 'Fondling', 'Bribery',\n",
       "       'Stolen Property Offenses', 'Pocket-Picking',\n",
       "       'Murder and Nonnegligent Manslaughter', 'Welfare Fraud',\n",
       "       'Extortion/Blackmail', 'Theft from Coin-operated Machine or Device',\n",
       "       'Statutory Rape', 'Intimidation', 'Gambling Equipment Violation',\n",
       "       'Shoplifting', 'Robbery', 'Incest', 'Animal Cruelty',\n",
       "       'Destruction/Damage/Vandalism of Property',\n",
       "       'Operating/Promoting/Assisting Gambling', 'Hacking/Computer Invasion',\n",
       "       'Sports Tampering', 'Kidnapping/Abduction', 'Motor Vehicle Theft',\n",
       "       'Impersonation', 'Weapon Law Violations', 'Negligent Manslaughter',\n",
       "       'Wire Fraud', 'Theft From Building',\n",
       "       'Theft of Motor Vehicle Parts or Accessories',\n",
       "       'False Pretenses/Swindle/Confidence game', 'Drug Equipment Violations',\n",
       "       'Sexual Assault with an Object', 'Counterfeiting/Forgery',\n",
       "       'Purse-snatching', 'Prostitution',\n",
       "       'Credit Card/Automated Teller Machine Fraud',\n",
       "       'Pornography/Obscene Material',\n",
       "       'Human Trafficking, Commercial Sex Acts', 'Betting/Wagering',\n",
       "       'Human Trafficking, Involuntary Servitude', 'Purchasing Prostitution',\n",
       "       'Theft from Motor Vehicle', 'Drug/Narcotic Violations',\n",
       "       'Aggravated Assault', 'Simple Assault',\n",
       "       'Assisting or Promoting Prostitution', 'Burglary/Breaking & Entering',\n",
       "       'Arson', 'All Other Larceny', 'Sodomy', 'Embezzlement'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "linked_offenses.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "98794339",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-15T01:42:17.548957Z",
     "start_time": "2023-06-15T01:42:17.531970Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data_year</th>\n",
       "      <th>Identity Theft</th>\n",
       "      <th>Fondling</th>\n",
       "      <th>Bribery</th>\n",
       "      <th>Stolen Property Offenses</th>\n",
       "      <th>Pocket-Picking</th>\n",
       "      <th>Murder and Nonnegligent Manslaughter</th>\n",
       "      <th>Welfare Fraud</th>\n",
       "      <th>Extortion/Blackmail</th>\n",
       "      <th>Theft from Coin-operated Machine or Device</th>\n",
       "      <th>...</th>\n",
       "      <th>Theft from Motor Vehicle</th>\n",
       "      <th>Drug/Narcotic Violations</th>\n",
       "      <th>Aggravated Assault</th>\n",
       "      <th>Simple Assault</th>\n",
       "      <th>Assisting or Promoting Prostitution</th>\n",
       "      <th>Burglary/Breaking &amp; Entering</th>\n",
       "      <th>Arson</th>\n",
       "      <th>All Other Larceny</th>\n",
       "      <th>Sodomy</th>\n",
       "      <th>Embezzlement</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2018</td>\n",
       "      <td>126</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>59</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>14</td>\n",
       "      <td>...</td>\n",
       "      <td>884</td>\n",
       "      <td>330</td>\n",
       "      <td>110</td>\n",
       "      <td>86</td>\n",
       "      <td>1</td>\n",
       "      <td>541</td>\n",
       "      <td>13</td>\n",
       "      <td>495</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    data_year  Identity Theft  Fondling  Bribery  Stolen Property Offenses  \\\n",
       "24       2018             126         2        2                        59   \n",
       "\n",
       "    Pocket-Picking  Murder and Nonnegligent Manslaughter  Welfare Fraud  \\\n",
       "24               1                                     2              0   \n",
       "\n",
       "    Extortion/Blackmail  Theft from Coin-operated Machine or Device  ...  \\\n",
       "24                    5                                          14  ...   \n",
       "\n",
       "    Theft from Motor Vehicle  Drug/Narcotic Violations  Aggravated Assault  \\\n",
       "24                       884                       330                 110   \n",
       "\n",
       "    Simple Assault  Assisting or Promoting Prostitution  \\\n",
       "24              86                                    1   \n",
       "\n",
       "    Burglary/Breaking & Entering  Arson  All Other Larceny  Sodomy  \\\n",
       "24                           541     13                495       0   \n",
       "\n",
       "    Embezzlement  \n",
       "24             3  \n",
       "\n",
       "[1 rows x 51 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "linked_offenses[linked_offenses['data_year'] == 2018]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "423d8a4f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-15T01:31:47.620000Z",
     "start_time": "2023-06-15T01:31:47.598942Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>percent of mvt linked to Robbery</th>\n",
       "      <th>percent of Robbery linked to mvt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001</td>\n",
       "      <td>0.004763</td>\n",
       "      <td>0.028129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2003</td>\n",
       "      <td>0.008766</td>\n",
       "      <td>0.053505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>0.012498</td>\n",
       "      <td>0.080235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2005</td>\n",
       "      <td>0.007663</td>\n",
       "      <td>0.050659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2006</td>\n",
       "      <td>0.024206</td>\n",
       "      <td>0.130753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2007</td>\n",
       "      <td>0.036023</td>\n",
       "      <td>0.173360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2008</td>\n",
       "      <td>0.022474</td>\n",
       "      <td>0.088132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2009</td>\n",
       "      <td>0.048077</td>\n",
       "      <td>0.177989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2010</td>\n",
       "      <td>0.026617</td>\n",
       "      <td>0.094817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2011</td>\n",
       "      <td>0.136339</td>\n",
       "      <td>0.454683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2012</td>\n",
       "      <td>0.396661</td>\n",
       "      <td>1.415094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2013</td>\n",
       "      <td>0.159693</td>\n",
       "      <td>0.637755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2014</td>\n",
       "      <td>0.222240</td>\n",
       "      <td>0.921962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2015</td>\n",
       "      <td>0.174760</td>\n",
       "      <td>0.844646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2016</td>\n",
       "      <td>0.050914</td>\n",
       "      <td>0.283688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2017</td>\n",
       "      <td>0.018228</td>\n",
       "      <td>0.103413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2018</td>\n",
       "      <td>0.045419</td>\n",
       "      <td>0.264480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2019</td>\n",
       "      <td>0.027620</td>\n",
       "      <td>0.161464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2020</td>\n",
       "      <td>0.032839</td>\n",
       "      <td>0.252270</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    year  percent of mvt linked to Robbery  percent of Robbery linked to mvt\n",
       "0   2000                          0.000000                          0.000000\n",
       "1   2001                          0.004763                          0.028129\n",
       "2   2002                          0.000000                          0.000000\n",
       "3   2003                          0.008766                          0.053505\n",
       "4   2004                          0.012498                          0.080235\n",
       "5   2005                          0.007663                          0.050659\n",
       "6   2006                          0.024206                          0.130753\n",
       "7   2007                          0.036023                          0.173360\n",
       "8   2008                          0.022474                          0.088132\n",
       "9   2009                          0.048077                          0.177989\n",
       "10  2010                          0.026617                          0.094817\n",
       "11  2011                          0.136339                          0.454683\n",
       "12  2012                          0.396661                          1.415094\n",
       "13  2013                          0.159693                          0.637755\n",
       "14  2014                          0.222240                          0.921962\n",
       "15  2015                          0.174760                          0.844646\n",
       "16  2016                          0.050914                          0.283688\n",
       "17  2017                          0.018228                          0.103413\n",
       "18  2018                          0.045419                          0.264480\n",
       "19  2019                          0.027620                          0.161464\n",
       "20  2020                          0.032839                          0.252270"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate percent of offenses related to mvt\n",
    "\n",
    "offense1 = 'robbery' # offense name in USAandCO_df\n",
    "offense2 = 'Robbery' # name of same offense in linked_offenses\n",
    "CO_df = USAandCO_df.loc[USAandCO_df['state_abbr'] == 'CO'].copy()\n",
    "CO_df[offense2 + ' linked'] = linked_offenses[linked_offenses['data_year'] >= 2000][offense2]\n",
    "CO_df['percent of mvt linked to ' + offense2] = linked_offenses[offense2] / CO_df['motor_vehicle_theft'] *100\n",
    "CO_df['percent of ' + offense2 +' linked to mvt'] = linked_offenses[offense2] / CO_df[offense1] *100\n",
    "CO_df[['year', 'percent of mvt linked to ' + offense2, 'percent of ' + offense2 +' linked to mvt']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "d61d4377",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-15T00:21:42.692311Z",
     "start_time": "2023-06-15T00:21:42.671871Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "percent of mvt linked to Robbery    0.069324\n",
       "percent of Robbery linked to mvt    0.296049\n",
       "dtype: float64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CO_df[['percent of mvt linked to ' + offense2, 'percent of ' + offense2 +' linked to mvt']].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c20161bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "offense1 = 'larceny' # offense name in USAandCO_df\n",
    "offense2 = 'Robbery' # name of same offense in linked_offenses\n",
    "CO_df = USAandCO_df.loc[USAandCO_df['state_abbr'] == 'CO'].copy()\n",
    "CO_df[offense2 + ' linked'] = linked_offenses[linked_offenses['data_year'] >= 2000][offense2]\n",
    "CO_df['percent of mvt linked to ' + offense2] = linked_offenses[offense2] / CO_df['motor_vehicle_theft'] *100\n",
    "CO_df['percent of ' + offense2 +' linked to mvt'] = linked_offenses[offense2] / CO_df[offense1] *100\n",
    "CO_df[['year', 'percent of mvt linked to ' + offense2, 'percent of ' + offense2 +' linked to mvt']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77d447af",
   "metadata": {},
   "source": [
    "### National and state-wide info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "389fb172",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-12T22:50:43.685787Z",
     "start_time": "2023-06-12T22:50:42.063555Z"
    }
   },
   "outputs": [],
   "source": [
    "national = requests.get('https://api.usa.gov/crime/fbi/cde/estimate/national?from=2000&to=2025',\n",
    "                       params = {'API_KEY': api})\n",
    "\n",
    "state = requests.get('https://api.usa.gov/crime/fbi/cde/estimate/state/CO?from=2000&to=2025',\n",
    "                       params = {'API_KEY': api})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8ab6cdb4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-12T22:50:47.369951Z",
     "start_time": "2023-06-12T22:50:47.351664Z"
    }
   },
   "outputs": [],
   "source": [
    "national_df = pd.DataFrame(national.json()).drop(['state_id', 'rape_legacy', 'rape_revised'], axis = 1)\n",
    "national_df['state_abbr'] = 'USA'\n",
    "\n",
    "state_df = pd.DataFrame(state.json()).drop(['state_id', 'rape_legacy', 'rape_revised'], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b5687f67",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-12T22:52:20.183094Z",
     "start_time": "2023-06-12T22:52:20.168556Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['state_abbr', 'year', 'population', 'violent_crime', 'homicide',\n",
       "       'robbery', 'aggravated_assault', 'property_crime', 'burglary',\n",
       "       'larceny', 'motor_vehicle_theft', 'arson'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "USAandCO_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "274123a0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-12T22:54:02.996630Z",
     "start_time": "2023-06-12T22:54:02.977158Z"
    }
   },
   "outputs": [],
   "source": [
    "USAandCO_df = pd.concat([national_df, state_df])\n",
    "cols2num = ['year', 'population', 'violent_crime', 'homicide',\n",
    "       'robbery', 'aggravated_assault', 'property_crime', 'burglary',\n",
    "       'larceny', 'motor_vehicle_theft', 'arson']\n",
    "USAandCO_df[cols2num] = USAandCO_df[cols2num].apply(pd.to_numeric)\n",
    "\n",
    "#USAandCO_df.to_pickle('USAandCO_df.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "22f2f134",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-13T00:31:37.748016Z",
     "start_time": "2023-06-13T00:31:37.733015Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "      <th>violent_crime</th>\n",
       "      <th>homicide</th>\n",
       "      <th>robbery</th>\n",
       "      <th>aggravated_assault</th>\n",
       "      <th>property_crime</th>\n",
       "      <th>burglary</th>\n",
       "      <th>larceny</th>\n",
       "      <th>motor_vehicle_theft</th>\n",
       "      <th>arson</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>USA</td>\n",
       "      <td>2019</td>\n",
       "      <td>328329953</td>\n",
       "      <td>1250393</td>\n",
       "      <td>16669</td>\n",
       "      <td>268483</td>\n",
       "      <td>822017</td>\n",
       "      <td>6995235</td>\n",
       "      <td>1118096</td>\n",
       "      <td>5152267</td>\n",
       "      <td>724872</td>\n",
       "      <td>35919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>USA</td>\n",
       "      <td>2020</td>\n",
       "      <td>329484123</td>\n",
       "      <td>1313105</td>\n",
       "      <td>21570</td>\n",
       "      <td>243600</td>\n",
       "      <td>921505</td>\n",
       "      <td>6452038</td>\n",
       "      <td>1035314</td>\n",
       "      <td>4606324</td>\n",
       "      <td>810400</td>\n",
       "      <td>43602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>CO</td>\n",
       "      <td>2019</td>\n",
       "      <td>5758486</td>\n",
       "      <td>22149</td>\n",
       "      <td>229</td>\n",
       "      <td>3716</td>\n",
       "      <td>14117</td>\n",
       "      <td>150564</td>\n",
       "      <td>20266</td>\n",
       "      <td>108575</td>\n",
       "      <td>21723</td>\n",
       "      <td>899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>CO</td>\n",
       "      <td>2020</td>\n",
       "      <td>5807719</td>\n",
       "      <td>24570</td>\n",
       "      <td>294</td>\n",
       "      <td>3964</td>\n",
       "      <td>16660</td>\n",
       "      <td>164582</td>\n",
       "      <td>23246</td>\n",
       "      <td>110884</td>\n",
       "      <td>30452</td>\n",
       "      <td>1330</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_abbr  year  population  violent_crime  homicide  robbery  \\\n",
       "19        USA  2019   328329953        1250393     16669   268483   \n",
       "20        USA  2020   329484123        1313105     21570   243600   \n",
       "19         CO  2019     5758486          22149       229     3716   \n",
       "20         CO  2020     5807719          24570       294     3964   \n",
       "\n",
       "    aggravated_assault  property_crime  burglary  larceny  \\\n",
       "19              822017         6995235   1118096  5152267   \n",
       "20              921505         6452038   1035314  4606324   \n",
       "19               14117          150564     20266   108575   \n",
       "20               16660          164582     23246   110884   \n",
       "\n",
       "    motor_vehicle_theft  arson  \n",
       "19               724872  35919  \n",
       "20               810400  43602  \n",
       "19                21723    899  \n",
       "20                30452   1330  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "USAandCO_df[USAandCO_df['year'] > 2018]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d787720f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "121187b7",
   "metadata": {},
   "source": [
    "### Obtaining agency information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8757ce5a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-05T22:22:17.048898Z",
     "start_time": "2023-06-05T22:22:15.469961Z"
    }
   },
   "outputs": [],
   "source": [
    "# sample agency url: 'https://api.usa.gov/crime/fbi/cde/agency/byStateAbbr/CO?API_KEY=sqVBzLYS6LjUvcsegM3scIPEFIJbGhhWQaIOgN3o'\n",
    "agencies = requests.get('https://api.usa.gov/crime/fbi/cde/agency/byStateAbbr/CO?',\n",
    "                       params = {'API_KEY': api})\n",
    "\n",
    "agency_df = pd.DataFrame(agencies.json())\n",
    "agency_df['county_name'] = agency_df['county_name'].str.split(', ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 366,
   "id": "2cf1d9ee",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T20:50:34.590349Z",
     "start_time": "2023-06-07T20:50:34.572227Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ori</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>state_name</th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>division_name</th>\n",
       "      <th>region_name</th>\n",
       "      <th>region_desc</th>\n",
       "      <th>county_name</th>\n",
       "      <th>agency_type_name</th>\n",
       "      <th>nibrs</th>\n",
       "      <th>nibrs_start_date</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CO0010000</td>\n",
       "      <td>Adams County Sheriff's Office</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "      <td>Region IV</td>\n",
       "      <td>[ADAMS]</td>\n",
       "      <td>County</td>\n",
       "      <td>True</td>\n",
       "      <td>1997-01-01T00:00:00.000Z</td>\n",
       "      <td>39.874325</td>\n",
       "      <td>-104.331872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CO0010100</td>\n",
       "      <td>Aurora Police Department</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "      <td>Region IV</td>\n",
       "      <td>[ADAMS, ARAPAHOE, DOUGLAS]</td>\n",
       "      <td>City</td>\n",
       "      <td>True</td>\n",
       "      <td>1985-01-01T00:00:00.000Z</td>\n",
       "      <td>39.70955</td>\n",
       "      <td>-104.81493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CO0010200</td>\n",
       "      <td>Brighton Police Department</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "      <td>Region IV</td>\n",
       "      <td>[ADAMS, WELD]</td>\n",
       "      <td>City</td>\n",
       "      <td>True</td>\n",
       "      <td>2004-09-01T00:00:00.000Z</td>\n",
       "      <td>39.874325</td>\n",
       "      <td>-104.331872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CO0010300</td>\n",
       "      <td>Commerce City Police Department</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "      <td>Region IV</td>\n",
       "      <td>[ADAMS]</td>\n",
       "      <td>City</td>\n",
       "      <td>True</td>\n",
       "      <td>1997-01-01T00:00:00.000Z</td>\n",
       "      <td>39.874325</td>\n",
       "      <td>-104.331872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CO0010400</td>\n",
       "      <td>Thornton Police Department</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "      <td>Region IV</td>\n",
       "      <td>[ADAMS]</td>\n",
       "      <td>City</td>\n",
       "      <td>True</td>\n",
       "      <td>2012-01-01T00:00:00.000Z</td>\n",
       "      <td>39.868996</td>\n",
       "      <td>-104.984215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>CO0640100</td>\n",
       "      <td>Broomfield Police Department</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "      <td>Region IV</td>\n",
       "      <td>[BROOMFIELD]</td>\n",
       "      <td>City</td>\n",
       "      <td>True</td>\n",
       "      <td>2001-01-01T00:00:00.000Z</td>\n",
       "      <td>39.921543</td>\n",
       "      <td>-105.07059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>COCBI0000</td>\n",
       "      <td>Colorado Bureau of Investigation</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "      <td>Region IV</td>\n",
       "      <td>[NOT SPECIFIED]</td>\n",
       "      <td>Other State Agency</td>\n",
       "      <td>True</td>\n",
       "      <td>2008-01-01T00:00:00.000Z</td>\n",
       "      <td>39.727325</td>\n",
       "      <td>-105.10969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>COCSP0000</td>\n",
       "      <td>State Patrol</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "      <td>Region IV</td>\n",
       "      <td>[NOT SPECIFIED]</td>\n",
       "      <td>State Police</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-01-01T00:00:00.000Z</td>\n",
       "      <td>39.72749</td>\n",
       "      <td>-105.10969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>CODI01100</td>\n",
       "      <td>Ute Mountain Tribal</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "      <td>Region IV</td>\n",
       "      <td>[NOT SPECIFIED]</td>\n",
       "      <td>Tribal</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-01-01T00:00:00.000Z</td>\n",
       "      <td>37.338025</td>\n",
       "      <td>-108.595786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>CODPD0000</td>\n",
       "      <td>Denver Police Department</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "      <td>Region IV</td>\n",
       "      <td>[DENVER]</td>\n",
       "      <td>City</td>\n",
       "      <td>True</td>\n",
       "      <td>2005-01-01T00:00:00.000Z</td>\n",
       "      <td>39.737377</td>\n",
       "      <td>-104.99167</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>247 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           ori                       agency_name state_name state_abbr  \\\n",
       "0    CO0010000     Adams County Sheriff's Office   Colorado         CO   \n",
       "1    CO0010100          Aurora Police Department   Colorado         CO   \n",
       "2    CO0010200        Brighton Police Department   Colorado         CO   \n",
       "3    CO0010300   Commerce City Police Department   Colorado         CO   \n",
       "4    CO0010400        Thornton Police Department   Colorado         CO   \n",
       "..         ...                               ...        ...        ...   \n",
       "242  CO0640100      Broomfield Police Department   Colorado         CO   \n",
       "243  COCBI0000  Colorado Bureau of Investigation   Colorado         CO   \n",
       "244  COCSP0000                      State Patrol   Colorado         CO   \n",
       "245  CODI01100               Ute Mountain Tribal   Colorado         CO   \n",
       "246  CODPD0000          Denver Police Department   Colorado         CO   \n",
       "\n",
       "    division_name region_name region_desc                 county_name  \\\n",
       "0        Mountain        West   Region IV                     [ADAMS]   \n",
       "1        Mountain        West   Region IV  [ADAMS, ARAPAHOE, DOUGLAS]   \n",
       "2        Mountain        West   Region IV               [ADAMS, WELD]   \n",
       "3        Mountain        West   Region IV                     [ADAMS]   \n",
       "4        Mountain        West   Region IV                     [ADAMS]   \n",
       "..            ...         ...         ...                         ...   \n",
       "242      Mountain        West   Region IV                [BROOMFIELD]   \n",
       "243      Mountain        West   Region IV             [NOT SPECIFIED]   \n",
       "244      Mountain        West   Region IV             [NOT SPECIFIED]   \n",
       "245      Mountain        West   Region IV             [NOT SPECIFIED]   \n",
       "246      Mountain        West   Region IV                    [DENVER]   \n",
       "\n",
       "       agency_type_name  nibrs          nibrs_start_date   latitude  \\\n",
       "0                County   True  1997-01-01T00:00:00.000Z  39.874325   \n",
       "1                  City   True  1985-01-01T00:00:00.000Z   39.70955   \n",
       "2                  City   True  2004-09-01T00:00:00.000Z  39.874325   \n",
       "3                  City   True  1997-01-01T00:00:00.000Z  39.874325   \n",
       "4                  City   True  2012-01-01T00:00:00.000Z  39.868996   \n",
       "..                  ...    ...                       ...        ...   \n",
       "242                City   True  2001-01-01T00:00:00.000Z  39.921543   \n",
       "243  Other State Agency   True  2008-01-01T00:00:00.000Z  39.727325   \n",
       "244        State Police   True  2006-01-01T00:00:00.000Z   39.72749   \n",
       "245              Tribal   True  2021-01-01T00:00:00.000Z  37.338025   \n",
       "246                City   True  2005-01-01T00:00:00.000Z  39.737377   \n",
       "\n",
       "       longitude  \n",
       "0    -104.331872  \n",
       "1     -104.81493  \n",
       "2    -104.331872  \n",
       "3    -104.331872  \n",
       "4    -104.984215  \n",
       "..           ...  \n",
       "242   -105.07059  \n",
       "243   -105.10969  \n",
       "244   -105.10969  \n",
       "245  -108.595786  \n",
       "246   -104.99167  \n",
       "\n",
       "[247 rows x 13 columns]"
      ]
     },
     "execution_count": 366,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agency_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 428,
   "id": "dc35885c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T22:27:38.422454Z",
     "start_time": "2023-06-07T22:27:38.413275Z"
    }
   },
   "outputs": [],
   "source": [
    "multicounty = agency_df[['agency_name','county_name']][agency_df['county_name'].apply(lambda x: len(x) > 1)].values.tolist()\n",
    "multicounty = [x[0] for x in multicounty]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 429,
   "id": "439f80f1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T22:27:43.016697Z",
     "start_time": "2023-06-07T22:27:43.003357Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Aurora Police Department',\n",
       " 'Brighton Police Department',\n",
       " 'Westminster Police Department',\n",
       " 'Northglenn Police Department',\n",
       " 'Littleton Police Department',\n",
       " 'Bow Mar Police Department',\n",
       " 'Longmont Police Department',\n",
       " 'Basalt Police Department',\n",
       " 'Green Mountain Falls Police Department',\n",
       " 'Arvada Police Department',\n",
       " 'Timnath Police Department',\n",
       " 'Center Police Department',\n",
       " 'Windsor Police Department',\n",
       " 'Erie Police Department',\n",
       " 'Johnstown Police Department',\n",
       " 'Lochbuie Police Department']"
      ]
     },
     "execution_count": 429,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "multicounty"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7014f02f",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### Deciding how to distribute values for agencies with multiple counties"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "addb2be1",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Populations taken from Wikipedia citing the 2020 census. <br>\n",
    "Helpful link: https://www.google.com/maps/d/u/0/viewer?mid=15C5-O3Pt28PZ8axPFxRTP-ANhon-w6N7&hl=en_US&ll=40.18824668844791%2C-104.5735925866669&z=9 <br>\n",
    "\n",
    "Departments in more than one county: <br><br>\n",
    "1 \tAurora Police Department \t[ADAMS, ARAPAHOE, DOUGLAS] <br>\n",
    "336,035 residing in Arapahoe County, 47,720 residing in Adams County, and 2,506 residing in Douglas County. 386,261 total. <br>\n",
    "87.0% in Arapahoe, 12.4% in Adams, 0.6% in Douglas\n",
    "\n",
    "2 \tBrighton Police Department \t[ADAMS, WELD] <br>\n",
    "39,718 residing in Adams County and 365 residing in Weld County\n",
    "\n",
    "3 \tWestminster Police Department \t[ADAMS, JEFFERSON] <br>\n",
    "71,240 residing in Adams County and 45,077 residing in Jefferson County. 116,317 total\n",
    "61.2% in Adams, 38.8% in Jefferson\n",
    "\n",
    "\n",
    "4 \tNorthglenn Police Department \t[ADAMS, WELD] <br>\n",
    "As of the 2020 census the city's population was 38,131.  Pretty much all in Adams County.\n",
    "\n",
    "5 \tLittleton Police Department \t[ARAPAHOE, DOUGLAS, JEFFERSON] <br>\n",
    "Mostly in Arapahoe County\n",
    "\n",
    "6 \tBow Mar Police Department \t[ARAPAHOE, JEFFERSON] <br>\n",
    "The town population was 853 at the 2020 United States Census with 587 residing in Arapahoe County and 266 residing in Jefferson County. 68.8% in Arapahoe, 31.2% in Jefferson\n",
    "\n",
    "\n",
    "7 \tLongmont Police Department \t[BOULDER, WELD] <br>\n",
    "Looks like most of the city is in Boulder county based on map\n",
    "\n",
    "8 \tBasalt Police Department \t[EAGLE, PITKIN] <br>\n",
    "The town population was 3,984 at the 2020 United States Census with 2,917 residing in Eagle County and 1,067 residing in Pitkin County. 73.2% in Eagle, 26.8% in Pitkin\n",
    "\n",
    "9 \tGreen Mountain Falls Police Department \t[EL PASO, TELLER] <br>\n",
    "The town population was 646 at the 2020 United States Census with 622 residents in El Paso County and 24 residents in Teller County.\n",
    "\n",
    "10 \tArvada Police Department \t[ADAMS, JEFFERSON] <br>\n",
    "The city population was 124,402 at the 2020 United States Census, with 121,510 residing in Jefferson County and 2,892 residing in Adams County.\n",
    "\n",
    "11 \tTimnath Police Department \t[LARIMER, WELD] <br>\n",
    "Pretty much all in Larimer.  Wikipedia doesn't even list Weld County\n",
    "\n",
    "12 \tCenter Police Department \t[RIO GRANDE, SAGUACHE] <br>\n",
    "The town's population was 1,929 at the 2020 United States Census with 1,885 residing in Saguache County and 44 residing in Rio Grande County.\n",
    "\n",
    "13 \tWindsor Police Department \t[LARIMER, WELD] <br>\n",
    "Mostly in Weld County\n",
    "\n",
    "14 \tErie Police Department \t[BOULDER, WELD] <br>\n",
    "At the 2020 census, 17,387 (58%) Erie residents lived in Weld County and 12,651 (42%) lived in Boulder County.\n",
    "\n",
    "15 \tJohnstown Police Department \t[LARIMER, WELD] <br>\n",
    "Mostly in Weld County\n",
    "\n",
    "16 \tLochbuie Police Department \t[ADAMS, WELD] <br>\n",
    "Mostly in Weld County\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "9d3f0135",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-05T22:58:16.659684Z",
     "start_time": "2023-06-05T22:58:16.647288Z"
    },
    "code_folding": [
     0
    ],
    "hidden": true
   },
   "outputs": [],
   "source": [
    "county_dict = {\n",
    "    'Aurora Police Department': {'ADAMS': 0.124, 'ARAPAHOE': 0.87, 'DOUGLAS': 0.006},\n",
    "    'Brighton Police Department': {'ADAMS': 1},\n",
    "    'Westminster Police Department': {'ADAMS': 0.612, 'JEFFERSON': 0.388},\n",
    "    'Northglenn Police Department': {'ADAMS': 1},\n",
    "    'Littleton Police Department': {'ARAPAHOE': 1},\n",
    "    'Bow Mar Police Department': {'ARAPAHOE': 0.688, 'JEFFERSON': 0.312},\n",
    "    'Longmont Police Department': {'BOULDER': 1},\n",
    "    'Basalt Police Department': {'EAGLE': 0.732, 'PITKIN':0.268},\n",
    "    'Green Mountain Falls Police Department': {'EL PASO': 1},\n",
    "    'Arvada Police Department': {'JEFFERSON': 1},\n",
    "    'Timnath Police Department': {'LARIMER': 1},\n",
    "    'Center Police Department': {'SAGUACHE': 1},\n",
    "    'Windsor Police Department': {'WELD': 1},\n",
    "    'Erie Police Department': {'WELD': 0.58, 'BOULDER': 0.42},\n",
    "    'Johnstown Police Department': {'WELD': 1},\n",
    "    'Lochbuie Police Department': {'WELD': 1}\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "9be3f428",
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ori</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>state_name</th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>division_name</th>\n",
       "      <th>region_name</th>\n",
       "      <th>region_desc</th>\n",
       "      <th>county_name</th>\n",
       "      <th>agency_type_name</th>\n",
       "      <th>nibrs</th>\n",
       "      <th>nibrs_start_date</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>COCSP0000</td>\n",
       "      <td>State Patrol</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "      <td>Region IV</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>State Police</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-01-01T00:00:00.000Z</td>\n",
       "      <td>39.72749</td>\n",
       "      <td>-105.10969</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           ori   agency_name state_name state_abbr division_name region_name  \\\n",
       "244  COCSP0000  State Patrol   Colorado         CO      Mountain        West   \n",
       "\n",
       "    region_desc    county_name agency_type_name  nibrs  \\\n",
       "244   Region IV  NOT SPECIFIED     State Police   True   \n",
       "\n",
       "             nibrs_start_date  latitude   longitude  \n",
       "244  2006-01-01T00:00:00.000Z  39.72749  -105.10969  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# State Patrol (CO) ORI is COCSP0000\n",
    "# However, it doesn't seem to process many crimes... random years I checked showed ~10s of MVTs vs. Denver PD's 1000s\n",
    "\n",
    "# Colorado Bureau of Investigation (COCBI0000) also showed low amounts of MVTs (only 1 in 20 years)\n",
    "agency_df[agency_df['agency_type_name'] == 'State Police']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b53387c",
   "metadata": {},
   "source": [
    "### Accessing MVT offense totals by department"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f90da930",
   "metadata": {},
   "source": [
    "Example requests url for MVT by agency, obtained from first format listed under \"Expanded Property Crime\":\n",
    "https://api.usa.gov/crime/fbi/cde/supplemental/agency/CO0010000/motor-vehicle-theft/offense/byYearRange?from=2015&to=2015&API_KEY=iiHnOKfno2Mgkt5AynpvPpUQTEyxE77jo1RU8PIv <br><br>\n",
    "\n",
    "Offenses under \"Expanded Property Crime\": 'burglary', 'robbery', 'motor-vehicle-theft', 'larceny'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "7ee79a95",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-06T01:04:10.067004Z",
     "start_time": "2023-06-06T01:04:10.054661Z"
    }
   },
   "outputs": [],
   "source": [
    "ORI_dict = dict(zip(agency_df['agency_name'],agency_df['ori']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "e94d7156",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-06T01:55:14.956994Z",
     "start_time": "2023-06-06T01:55:14.949983Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'CODPD0000'"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ORI_dict['Denver Police Department']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "7fe71c5c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-06T01:04:11.568470Z",
     "start_time": "2023-06-06T01:04:11.562024Z"
    }
   },
   "outputs": [],
   "source": [
    "# Offense types: 'motor-vehicle-theft', \n",
    "def agency_offenses(ori, offense, startyear = 2000, endyear = 2023):\n",
    "    agency_mvt = requests.get('https://api.usa.gov/crime/fbi/cde/supplemental/agency/' + ori +'/' + offense +'/offense/byYearRange?',\n",
    "                       params = {'API_KEY': api,\n",
    "                                'from': startyear,\n",
    "                                'to': endyear},\n",
    "                       timeout = 10)\n",
    "    return agency_mvt.json()['data']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3189e17",
   "metadata": {},
   "source": [
    "#### MVT offenses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "79f93e14",
   "metadata": {
    "code_folding": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error occurred while making the API request: Expecting value: line 1 column 1 (char 0)\n"
     ]
    }
   ],
   "source": [
    "# Collect mvt data.  \n",
    "# Error occurs for 'Division of Gaming Criminal Enforcement and Investigations Section'.  I don't think it matters\n",
    "mvtdict = {}\n",
    "ORI_dict_error = {}\n",
    "for key in ORI_dict:\n",
    "    try:\n",
    "        data = agency_offenses(ORI_dict[key], 'motor-vehicle-theft')\n",
    "        mvtdict[key] = data\n",
    "        \n",
    "    except RequestException as e:\n",
    "        print(f\"Error occurred while making the API request: {str(e)}\")\n",
    "        ORI_dict_error[key] = ORI_dict[key]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e01f447b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-05T22:20:58.512470Z",
     "start_time": "2023-06-05T22:20:58.492991Z"
    }
   },
   "outputs": [],
   "source": [
    "# Process mvt data\n",
    "rows = []\n",
    "for key in list(mvtdict.keys()):\n",
    "    for item in mvtdict[key]:\n",
    "        mvtrows = {'agency' : key,\n",
    "                  'year' : item['data_year'],\n",
    "                  'mvt stolen value total' : item['Stolen Value Total'],\n",
    "                  'mvt offense count' : item['Offense Count']}\n",
    "        rows.append(mvtrows)\n",
    "        \n",
    "mvt_df = pd.DataFrame(rows)\n",
    "mvt_df['mvt offense count'] = pd.to_numeric(mvt_df['mvt offense count'], errors = 'coerce')\n",
    "mvt_df['mvt stolen value total'] = pd.to_numeric(mvt_df['mvt stolen value total'], errors = 'coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "id": "02073d57",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T01:58:21.438995Z",
     "start_time": "2023-06-07T01:55:25.942626Z"
    }
   },
   "outputs": [],
   "source": [
    "# MVT with clearance rate\n",
    "mvtclearance = []\n",
    "ORI_dict_error = {}\n",
    "for key in ORI_dict:\n",
    "    ori = ORI_dict[key]\n",
    "    url = 'https://api.usa.gov/crime/fbi/cde/summarized/agency/' + ori + '/motor-vehicle-theft?from=2000&to=2023&API_KEY=sqVBzLYS6LjUvcsegM3scIPEFIJbGhhWQaIOgN3o'\n",
    "    try:\n",
    "        data = requests.get(url).json()\n",
    "        for entry in data:\n",
    "            mvtclearance.append({'agency': key,\n",
    "                                 'year': entry['data_year'],\n",
    "                                 'mvt cleared': entry['cleared']})\n",
    "        \n",
    "    except RequestException as e:\n",
    "        print(f\"Error occurred while making the API request: {str(e)}\")\n",
    "        ORI_dict_error[key] = ORI_dict[key]\n",
    "        \n",
    "# with open('clearancedata.pkd', 'wb') as f:\n",
    "#     dill.dump(mvtclearance, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "id": "d27d6a36",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T02:01:18.502325Z",
     "start_time": "2023-06-07T02:01:18.491223Z"
    }
   },
   "outputs": [],
   "source": [
    "clearance_df = pd.DataFrame(mvtclearance)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "id": "0a7b4890",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T02:15:56.206956Z",
     "start_time": "2023-06-07T02:15:56.197920Z"
    }
   },
   "outputs": [],
   "source": [
    "mvt_df = mvt_df.merge(clearance_df, on = ['agency', 'year'], how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7fed717",
   "metadata": {},
   "source": [
    "#### Burglary offenses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "18a3ca3b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-06T01:12:22.519244Z",
     "start_time": "2023-06-06T01:06:20.629828Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error occurred while making the API request: Expecting value: line 1 column 1 (char 0)\n",
      "Error occurred while making the API request: Expecting value: line 1 column 1 (char 0)\n"
     ]
    }
   ],
   "source": [
    "# Collect burglary data. Error also occured for 'Nunn' department\n",
    "\n",
    "burgdict = {}\n",
    "ORI_dict_error = {}\n",
    "for key in ORI_dict:\n",
    "    try:\n",
    "        data = agency_offenses(ORI_dict[key], 'burglary')\n",
    "        burgdict[key] = data\n",
    "        \n",
    "    except RequestException as e:\n",
    "        print(f\"Error occurred while making the API request: {str(e)}\")\n",
    "        ORI_dict_error[key] = ORI_dict[key]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "e763c7de",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-06T01:18:51.696594Z",
     "start_time": "2023-06-06T01:18:51.609128Z"
    }
   },
   "outputs": [],
   "source": [
    "# with open('burgdata.pkd', 'wb') as f:\n",
    "#     dill.dump(burgdict, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "862cf255",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-06T01:22:14.997402Z",
     "start_time": "2023-06-06T01:22:14.974436Z"
    }
   },
   "outputs": [],
   "source": [
    "rows = []\n",
    "for key in list(burgdict.keys()):\n",
    "    for item in burgdict[key]:\n",
    "        burgrows = {'agency' : key,\n",
    "                  'year' : item['data_year'],\n",
    "                  'burglary value total' : item['Stolen Value Total'],\n",
    "                  'burglary offense count' : item['Offense Count']}\n",
    "        rows.append(burgrows)\n",
    "        \n",
    "burg_df = pd.DataFrame(rows)\n",
    "burg_df['burglary value total'] = pd.to_numeric(burg_df['burglary value total'], errors = 'coerce')\n",
    "burg_df['burglary offense count'] = pd.to_numeric(burg_df['burglary offense count'], errors = 'coerce')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84f93d07",
   "metadata": {},
   "source": [
    "#### Robbery Offenses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "1ccb430c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-06T01:33:23.278674Z",
     "start_time": "2023-06-06T01:27:25.851002Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error occurred while making the API request: Expecting value: line 1 column 1 (char 0)\n",
      "Error occurred while making the API request: Expecting value: line 1 column 1 (char 0)\n"
     ]
    }
   ],
   "source": [
    "# Returned error for 'Nunn' again. Looks like it's a very, very small town\n",
    "robdict = {}\n",
    "ORI_dict_error = {}\n",
    "for key in ORI_dict:\n",
    "    try:\n",
    "        data = agency_offenses(ORI_dict[key], 'robbery')\n",
    "        robdict[key] = data\n",
    "        \n",
    "    except RequestException as e:\n",
    "        print(f\"Error occurred while making the API request: {str(e)}\")\n",
    "        ORI_dict_error[key] = ORI_dict[key]\n",
    "        \n",
    "# with open('robdata.pkd', 'wb') as f:\n",
    "#     dill.dump(robdict, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "c3b3ca96",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-06T01:35:45.451095Z",
     "start_time": "2023-06-06T01:35:45.424524Z"
    }
   },
   "outputs": [],
   "source": [
    "rows = []\n",
    "for key in list(robdict.keys()):\n",
    "    for item in robdict[key]:\n",
    "        robrows = {'agency' : key,\n",
    "                  'year' : item['data_year'],\n",
    "                  'robbery value total' : item['Stolen Value Total'],\n",
    "                  'robbery offense count' : item['Offense Count']}\n",
    "        rows.append(robrows)\n",
    "        \n",
    "rob_df = pd.DataFrame(rows)\n",
    "rob_df['robbery value total'] = pd.to_numeric(rob_df['robbery value total'], errors = 'coerce')\n",
    "rob_df['robbery offense count'] = pd.to_numeric(rob_df['robbery offense count'], errors = 'coerce')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b47e4cf",
   "metadata": {},
   "source": [
    "#### Larceny Offenses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "id": "11ce62a3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-06T01:45:14.815152Z",
     "start_time": "2023-06-06T01:39:13.000275Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error occurred while making the API request: Expecting value: line 1 column 1 (char 0)\n",
      "Error occurred while making the API request: Expecting value: line 1 column 1 (char 0)\n"
     ]
    }
   ],
   "source": [
    "# Returned error for 'Nunn' again. Looks like it's a very, very small town\n",
    "larcdict = {}\n",
    "ORI_dict_error = {}\n",
    "for key in ORI_dict:\n",
    "    try:\n",
    "        data = agency_offenses(ORI_dict[key], 'larceny')\n",
    "        larcdict[key] = data\n",
    "        \n",
    "    except RequestException as e:\n",
    "        print(f\"Error occurred while making the API request: {str(e)}\")\n",
    "        ORI_dict_error[key] = ORI_dict[key]\n",
    "        \n",
    "# with open('larcdata.pkd', 'wb') as f:\n",
    "#     dill.dump(larcdict, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "id": "7a8b6044",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-06T01:47:29.159306Z",
     "start_time": "2023-06-06T01:47:29.130069Z"
    }
   },
   "outputs": [],
   "source": [
    "rows = []\n",
    "for key in list(larcdict.keys()):\n",
    "    for item in larcdict[key]:\n",
    "        larcrows = {'agency' : key,\n",
    "                  'year' : item['data_year'],\n",
    "                  'larceny value total' : item['Stolen Value Total'],\n",
    "                  'larceny offense count' : item['Offense Count']}\n",
    "        rows.append(larcrows)\n",
    "        \n",
    "larc_df = pd.DataFrame(rows)\n",
    "larc_df['larceny value total'] = pd.to_numeric(larc_df['larceny value total'], errors = 'coerce')\n",
    "larc_df['larceny offense count'] = pd.to_numeric(larc_df['larceny offense count'], errors = 'coerce')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02f58a9f",
   "metadata": {},
   "source": [
    "#### Offenses in 'Summarized' section\n",
    "Includes homicide, aggravated assault, and arson <br>\n",
    "Info on total counts and amount cleared"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "id": "eb9c10c9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T18:23:15.691380Z",
     "start_time": "2023-06-07T18:15:15.068513Z"
    }
   },
   "outputs": [],
   "source": [
    "summarizedinfo = []\n",
    "offenses = ['homicide', 'aggravated-assault', 'arson']\n",
    "ORI_dict_error = {}\n",
    "for key in ORI_dict:\n",
    "    ori = ORI_dict[key]\n",
    "    for offense in offenses:\n",
    "        url = 'https://api.usa.gov/crime/fbi/cde/summarized/agency/' + ori + '/' + offense + '?from=2000&to=2023&API_KEY=sqVBzLYS6LjUvcsegM3scIPEFIJbGhhWQaIOgN3o'\n",
    "        try:\n",
    "            data = requests.get(url).json()\n",
    "            for entry in data:\n",
    "                summarizedinfo.append({'agency': key,\n",
    "                                     'year': entry['data_year'],\n",
    "                                     offense + ' cleared': entry['cleared'],\n",
    "                                     offense + ' actual': entry['actual']})\n",
    "\n",
    "        except RequestException as e:\n",
    "            print(f\"Error occurred while making the API request: {str(e)}\")\n",
    "            ORI_dict_error[key] = ORI_dict[key]\n",
    "            \n",
    "# with open('summarizeddata.pkd', 'wb') as f:\n",
    "#      dill.dump(summarizedinfo, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "id": "b3d78144",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T18:36:04.566216Z",
     "start_time": "2023-06-07T18:36:04.518229Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ruggb\\AppData\\Local\\Temp\\ipykernel_3404\\2145377135.py:3: FutureWarning: The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.\n",
      "  summarized_df.groupby('year').sum()\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>homicide cleared</th>\n",
       "      <th>homicide actual</th>\n",
       "      <th>aggravated-assault cleared</th>\n",
       "      <th>aggravated-assault actual</th>\n",
       "      <th>arson cleared</th>\n",
       "      <th>arson actual</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>69.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>5436.0</td>\n",
       "      <td>8863.0</td>\n",
       "      <td>141.0</td>\n",
       "      <td>740.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>100.0</td>\n",
       "      <td>144.0</td>\n",
       "      <td>5223.0</td>\n",
       "      <td>8864.0</td>\n",
       "      <td>242.0</td>\n",
       "      <td>1545.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>92.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>5411.0</td>\n",
       "      <td>9251.0</td>\n",
       "      <td>304.0</td>\n",
       "      <td>1443.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>101.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>5607.0</td>\n",
       "      <td>9115.0</td>\n",
       "      <td>289.0</td>\n",
       "      <td>1471.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>121.0</td>\n",
       "      <td>197.0</td>\n",
       "      <td>6584.0</td>\n",
       "      <td>10839.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>1158.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>160.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>7521.0</td>\n",
       "      <td>12622.0</td>\n",
       "      <td>336.0</td>\n",
       "      <td>1050.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>169.0</td>\n",
       "      <td>216.0</td>\n",
       "      <td>8748.0</td>\n",
       "      <td>14390.0</td>\n",
       "      <td>275.0</td>\n",
       "      <td>992.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>159.0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>8550.0</td>\n",
       "      <td>14010.0</td>\n",
       "      <td>264.0</td>\n",
       "      <td>898.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>195.0</td>\n",
       "      <td>293.0</td>\n",
       "      <td>9267.0</td>\n",
       "      <td>16450.0</td>\n",
       "      <td>361.0</td>\n",
       "      <td>1327.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>223.0</td>\n",
       "      <td>358.0</td>\n",
       "      <td>9301.0</td>\n",
       "      <td>18753.0</td>\n",
       "      <td>366.0</td>\n",
       "      <td>1341.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>22 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      homicide cleared  homicide actual  aggravated-assault cleared  \\\n",
       "year                                                                  \n",
       "2000              69.0            126.0                      5436.0   \n",
       "2001             100.0            144.0                      5223.0   \n",
       "2002              92.0            170.0                      5411.0   \n",
       "2003             101.0            170.0                      5607.0   \n",
       "2004             121.0            197.0                      6584.0   \n",
       "...                ...              ...                         ...   \n",
       "2017             160.0            220.0                      7521.0   \n",
       "2018             169.0            216.0                      8748.0   \n",
       "2019             159.0            227.0                      8550.0   \n",
       "2020             195.0            293.0                      9267.0   \n",
       "2021             223.0            358.0                      9301.0   \n",
       "\n",
       "      aggravated-assault actual  arson cleared  arson actual  \n",
       "year                                                          \n",
       "2000                     8863.0          141.0         740.0  \n",
       "2001                     8864.0          242.0        1545.0  \n",
       "2002                     9251.0          304.0        1443.0  \n",
       "2003                     9115.0          289.0        1471.0  \n",
       "2004                    10839.0          226.0        1158.0  \n",
       "...                         ...            ...           ...  \n",
       "2017                    12622.0          336.0        1050.0  \n",
       "2018                    14390.0          275.0         992.0  \n",
       "2019                    14010.0          264.0         898.0  \n",
       "2020                    16450.0          361.0        1327.0  \n",
       "2021                    18753.0          366.0        1341.0  \n",
       "\n",
       "[22 rows x 6 columns]"
      ]
     },
     "execution_count": 255,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summarized_df = pd.DataFrame(summarizedinfo)\n",
    "summarized_df = summarized_df.groupby(['agency', 'year'], as_index=False).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a930e2eb",
   "metadata": {},
   "source": [
    "#### Arrests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "id": "3c47c567",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T19:28:32.705161Z",
     "start_time": "2023-06-07T19:23:06.648494Z"
    },
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "arrestskeys = [\n",
    "    \"data_year\",\n",
    "    \"Curfew and Loitering Law Violations\",\n",
    "    \"Disorderly Conduct\",\n",
    "    \"Driving Under the Influence\",\n",
    "    \"Drug Abuse Violations - Grand Total\",\n",
    "    \"Drunkenness\",\n",
    "    \"Embezzlement\",\n",
    "    \"Forgery and Counterfeiting\",\n",
    "    \"Fraud\",\n",
    "    \"Gambling - Total\",\n",
    "    \"Human Trafficking - Commercial Sex Acts\",\n",
    "    \"Human Trafficking - Involuntary Servitude\",\n",
    "    \"Larceny - Theft\",\n",
    "    \"Liquor Laws\",\n",
    "    \"Manslaughter by Negligence\",\n",
    "    \"Offenses Against the Family and Children\",\n",
    "    \"Prostitution and Commercialized Vice\",\n",
    "    \"Rape\",\n",
    "    \"Simple Assault\",\n",
    "    \"Stolen Property: Buying, Receiving, Possessing\",\n",
    "    \"Suspicion\",\n",
    "    \"Vagrancy\",\n",
    "    \"Vandalism\",\n",
    "    \"Weapons: Carrying, Possessing, Etc.\",\n",
    "    \"Sex Offenses (Except Rape, and Prostitution and Commercialized Vice)\"\n",
    "    \"Marijuana\",\n",
    "    \"Opium or Cocaine or Their Derivatives\",\n",
    "    \"Other - Dangerous Nonnarcotic Drugs\",\n",
    "    \"Synthetic Narcotics\",\n",
    "    \"Drug Sale/Manufacturing - Subtotal\"\n",
    "]\n",
    "\n",
    "arrestinfo = []\n",
    "offenses = ['all', 'drug_sales']\n",
    "ORI_dict_error = {}\n",
    "for key in ORI_dict:\n",
    "    ori = ORI_dict[key]\n",
    "    for offense in offenses:\n",
    "        url = 'https://api.usa.gov/crime/fbi/cde/arrest/agency/' + ori + '/' + offense + '?from=2000&to=2025&API_KEY=sqVBzLYS6LjUvcsegM3scIPEFIJbGhhWQaIOgN3o'\n",
    "        try:\n",
    "            data = requests.get(url).json()['data']\n",
    "            for entry in data:\n",
    "                newentry = {'agency': key, 'year': entry['data_year']}\n",
    "                for arresttype in arrestskeys:\n",
    "                    newentry[arresttype] = entry.get(arresttype)\n",
    "                arrestinfo.append(newentry)\n",
    "\n",
    "        except RequestException as e:\n",
    "            print(f\"Error occurred while making the API request: {str(e)}\")\n",
    "            ORI_dict_error[key] = ORI_dict[key]\n",
    "            \n",
    "# with open('arrestdata.pkd', 'wb') as f:\n",
    "#      dill.dump(arrestinfo, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "id": "c0045143",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T19:44:12.197152Z",
     "start_time": "2023-06-07T19:44:12.140100Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ruggb\\AppData\\Local\\Temp\\ipykernel_3404\\2233900165.py:1: FutureWarning: The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.\n",
      "  arrest_df = pd.DataFrame(arrestinfo).groupby(['agency', 'year'], as_index=False).sum().drop('data_year', axis = 1)\n"
     ]
    }
   ],
   "source": [
    "arrest_df = pd.DataFrame(arrestinfo).groupby(['agency', 'year'], as_index=False).sum().drop('data_year', axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e34e9d4",
   "metadata": {},
   "source": [
    "### Joining crime data to agency info, clean dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 399,
   "id": "cbd5ca59",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T21:33:21.306481Z",
     "start_time": "2023-06-07T21:33:21.288475Z"
    }
   },
   "outputs": [],
   "source": [
    "# merge mvt df with agency df for county information\n",
    "big_df = mvt_df.merge(agency_df, left_on = 'agency', right_on = 'agency_name')\n",
    "big_df = big_df.drop(columns = ['state_name', 'state_abbr', 'division_name', 'region_name', 'region_desc', 'ori',\n",
    "                               'nibrs', 'nibrs_start_date', 'latitude', 'longitude', 'agency_name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 400,
   "id": "dfa266f2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T21:33:21.633837Z",
     "start_time": "2023-06-07T21:33:21.602700Z"
    }
   },
   "outputs": [],
   "source": [
    "# Merge results of different API requests\n",
    "big_df = big_df.merge(burg_df, how = 'left')\n",
    "big_df = big_df.merge(rob_df, how = 'left')\n",
    "big_df = big_df.merge(larc_df, how = 'left')\n",
    "big_df = big_df.merge(summarized_df, how = 'left')\n",
    "big_df = big_df.merge(arrest_df, how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 401,
   "id": "8b8d504b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T21:33:22.405215Z",
     "start_time": "2023-06-07T21:33:22.256217Z"
    }
   },
   "outputs": [],
   "source": [
    "## Distribute values for agencies with multiple counties\n",
    "multicounty_df = big_df[big_df['county_name'].apply(lambda x: len(x) > 1)]\n",
    "safecolumns = ['year', 'agency', 'county_name', 'agency_type_name']\n",
    "new_rows = []\n",
    "for index, row in multicounty_df.iterrows():\n",
    "    agency = row['agency']\n",
    "    county_percentages = county_dict.get(agency, {})\n",
    "    for county, percentage in county_percentages.items():\n",
    "        new_row = {}\n",
    "        for column in multicounty_df.columns:\n",
    "            if column not in safecolumns and not np.isnan(row[column]):\n",
    "                new_row[column] = round(row[column] * percentage)\n",
    "            else:\n",
    "                new_row[column] = row[column]\n",
    "            new_row['county_name'] = [county]\n",
    "        new_rows.append(new_row)\n",
    "\n",
    "# Create a new DataFrame from the new rows and merge with original\n",
    "new_df = pd.DataFrame(new_rows)\n",
    "big_df = pd.concat([big_df, new_df], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 402,
   "id": "40b8502f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T21:33:28.196744Z",
     "start_time": "2023-06-07T21:33:28.174397Z"
    }
   },
   "outputs": [],
   "source": [
    "# Delete original multi-county rows, pop county names out of list\n",
    "big_df = big_df[big_df['county_name'].str.len() < 2]\n",
    "big_df['county_name'] = big_df['county_name'].apply(lambda x: x[0])\n",
    "big_df = big_df[big_df['county_name'] != 'NOT SPECIFIED']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 433,
   "id": "995b88b5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T22:43:38.962022Z",
     "start_time": "2023-06-07T22:43:38.945999Z"
    }
   },
   "outputs": [],
   "source": [
    "crime_df_agency = big_df.reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 435,
   "id": "90874002",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T22:44:01.067452Z",
     "start_time": "2023-06-07T22:44:01.041390Z"
    }
   },
   "outputs": [],
   "source": [
    "#crime_df_agency.to_pickle('crime_df_agency.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 405,
   "id": "616ae0b5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T21:34:59.427146Z",
     "start_time": "2023-06-07T21:34:59.395002Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ruggb\\AppData\\Local\\Temp\\ipykernel_3404\\1530980104.py:3: FutureWarning: The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.\n",
      "  crime_df_agency.groupby(['county_name'], as_index = False).sum() \\\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county_name</th>\n",
       "      <th>year</th>\n",
       "      <th>mvt stolen value total</th>\n",
       "      <th>mvt offense count</th>\n",
       "      <th>mvt cleared</th>\n",
       "      <th>burglary value total</th>\n",
       "      <th>burglary offense count</th>\n",
       "      <th>robbery value total</th>\n",
       "      <th>robbery offense count</th>\n",
       "      <th>larceny value total</th>\n",
       "      <th>...</th>\n",
       "      <th>Simple Assault</th>\n",
       "      <th>Stolen Property: Buying, Receiving, Possessing</th>\n",
       "      <th>Suspicion</th>\n",
       "      <th>Vagrancy</th>\n",
       "      <th>Vandalism</th>\n",
       "      <th>Weapons: Carrying, Possessing, Etc.</th>\n",
       "      <th>Opium or Cocaine or Their Derivatives</th>\n",
       "      <th>Other - Dangerous Nonnarcotic Drugs</th>\n",
       "      <th>Synthetic Narcotics</th>\n",
       "      <th>Drug Sale/Manufacturing - Subtotal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>DENVER</td>\n",
       "      <td>88462</td>\n",
       "      <td>913413243</td>\n",
       "      <td>122601</td>\n",
       "      <td>14098.0</td>\n",
       "      <td>295312094.0</td>\n",
       "      <td>115347.0</td>\n",
       "      <td>36562085.0</td>\n",
       "      <td>25577.0</td>\n",
       "      <td>370442027.0</td>\n",
       "      <td>...</td>\n",
       "      <td>75273.0</td>\n",
       "      <td>2090.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4275.0</td>\n",
       "      <td>19468.0</td>\n",
       "      <td>12164.0</td>\n",
       "      <td>7192.0</td>\n",
       "      <td>1947.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>10966.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARAPAHOE</td>\n",
       "      <td>512638</td>\n",
       "      <td>403571896</td>\n",
       "      <td>60753</td>\n",
       "      <td>4613.0</td>\n",
       "      <td>198830319.0</td>\n",
       "      <td>66053.0</td>\n",
       "      <td>21669511.0</td>\n",
       "      <td>13370.0</td>\n",
       "      <td>278315655.0</td>\n",
       "      <td>...</td>\n",
       "      <td>47502.0</td>\n",
       "      <td>1963.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1029.0</td>\n",
       "      <td>14605.0</td>\n",
       "      <td>6911.0</td>\n",
       "      <td>2073.0</td>\n",
       "      <td>852.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>4403.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ADAMS</td>\n",
       "      <td>390037</td>\n",
       "      <td>405224948</td>\n",
       "      <td>58361</td>\n",
       "      <td>5909.0</td>\n",
       "      <td>116761875.0</td>\n",
       "      <td>51354.0</td>\n",
       "      <td>11409325.0</td>\n",
       "      <td>6508.0</td>\n",
       "      <td>335521418.0</td>\n",
       "      <td>...</td>\n",
       "      <td>33553.0</td>\n",
       "      <td>3452.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>402.0</td>\n",
       "      <td>12868.0</td>\n",
       "      <td>5181.0</td>\n",
       "      <td>890.0</td>\n",
       "      <td>1267.0</td>\n",
       "      <td>221.0</td>\n",
       "      <td>3431.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>EL PASO</td>\n",
       "      <td>407991</td>\n",
       "      <td>372589847</td>\n",
       "      <td>46723</td>\n",
       "      <td>7723.0</td>\n",
       "      <td>216716805.0</td>\n",
       "      <td>84380.0</td>\n",
       "      <td>15314783.0</td>\n",
       "      <td>10964.0</td>\n",
       "      <td>299013763.0</td>\n",
       "      <td>...</td>\n",
       "      <td>44461.0</td>\n",
       "      <td>1543.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>18489.0</td>\n",
       "      <td>6921.0</td>\n",
       "      <td>1839.0</td>\n",
       "      <td>2036.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>5239.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>JEFFERSON</td>\n",
       "      <td>548755</td>\n",
       "      <td>317540970</td>\n",
       "      <td>43786</td>\n",
       "      <td>4690.0</td>\n",
       "      <td>107911718.0</td>\n",
       "      <td>50589.0</td>\n",
       "      <td>10201959.0</td>\n",
       "      <td>6274.0</td>\n",
       "      <td>207580533.0</td>\n",
       "      <td>...</td>\n",
       "      <td>33012.0</td>\n",
       "      <td>3353.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>217.0</td>\n",
       "      <td>14722.0</td>\n",
       "      <td>3561.0</td>\n",
       "      <td>341.0</td>\n",
       "      <td>720.0</td>\n",
       "      <td>410.0</td>\n",
       "      <td>2377.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>PUEBLO</td>\n",
       "      <td>122598</td>\n",
       "      <td>105319450</td>\n",
       "      <td>15651</td>\n",
       "      <td>1645.0</td>\n",
       "      <td>64918706.0</td>\n",
       "      <td>35721.0</td>\n",
       "      <td>4770579.0</td>\n",
       "      <td>4016.0</td>\n",
       "      <td>77779750.0</td>\n",
       "      <td>...</td>\n",
       "      <td>14451.0</td>\n",
       "      <td>179.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>3318.0</td>\n",
       "      <td>1186.0</td>\n",
       "      <td>550.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>958.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>WELD</td>\n",
       "      <td>876777</td>\n",
       "      <td>96267502</td>\n",
       "      <td>10010</td>\n",
       "      <td>2253.0</td>\n",
       "      <td>46107217.0</td>\n",
       "      <td>20015.0</td>\n",
       "      <td>2098369.0</td>\n",
       "      <td>1516.0</td>\n",
       "      <td>69737983.0</td>\n",
       "      <td>...</td>\n",
       "      <td>18901.0</td>\n",
       "      <td>804.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>5663.0</td>\n",
       "      <td>1636.0</td>\n",
       "      <td>397.0</td>\n",
       "      <td>740.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>1732.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>BOULDER</td>\n",
       "      <td>347818</td>\n",
       "      <td>84924091</td>\n",
       "      <td>9739</td>\n",
       "      <td>1947.0</td>\n",
       "      <td>54743718.0</td>\n",
       "      <td>23786.0</td>\n",
       "      <td>2374199.0</td>\n",
       "      <td>1668.0</td>\n",
       "      <td>122738289.0</td>\n",
       "      <td>...</td>\n",
       "      <td>16925.0</td>\n",
       "      <td>501.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>6042.0</td>\n",
       "      <td>1254.0</td>\n",
       "      <td>657.0</td>\n",
       "      <td>516.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1790.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>LARIMER</td>\n",
       "      <td>245341</td>\n",
       "      <td>66904077</td>\n",
       "      <td>8731</td>\n",
       "      <td>1918.0</td>\n",
       "      <td>36855454.0</td>\n",
       "      <td>22742.0</td>\n",
       "      <td>2766497.0</td>\n",
       "      <td>1362.0</td>\n",
       "      <td>101014043.0</td>\n",
       "      <td>...</td>\n",
       "      <td>18391.0</td>\n",
       "      <td>1019.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3340.0</td>\n",
       "      <td>8233.0</td>\n",
       "      <td>2446.0</td>\n",
       "      <td>346.0</td>\n",
       "      <td>1395.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>2486.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>MESA</td>\n",
       "      <td>243177</td>\n",
       "      <td>46758038</td>\n",
       "      <td>6438</td>\n",
       "      <td>1355.0</td>\n",
       "      <td>27644232.0</td>\n",
       "      <td>15554.0</td>\n",
       "      <td>905658.0</td>\n",
       "      <td>926.0</td>\n",
       "      <td>61193065.0</td>\n",
       "      <td>...</td>\n",
       "      <td>11911.0</td>\n",
       "      <td>314.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>4257.0</td>\n",
       "      <td>1625.0</td>\n",
       "      <td>402.0</td>\n",
       "      <td>955.0</td>\n",
       "      <td>251.0</td>\n",
       "      <td>2121.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   county_name    year  mvt stolen value total  mvt offense count  \\\n",
       "16      DENVER   88462               913413243             122601   \n",
       "2     ARAPAHOE  512638               403571896              60753   \n",
       "0        ADAMS  390037               405224948              58361   \n",
       "20     EL PASO  407991               372589847              46723   \n",
       "30   JEFFERSON  548755               317540970              43786   \n",
       "51      PUEBLO  122598               105319450              15651   \n",
       "62        WELD  876777                96267502              10010   \n",
       "6      BOULDER  347818                84924091               9739   \n",
       "35     LARIMER  245341                66904077               8731   \n",
       "39        MESA  243177                46758038               6438   \n",
       "\n",
       "    mvt cleared  burglary value total  burglary offense count  \\\n",
       "16      14098.0           295312094.0                115347.0   \n",
       "2        4613.0           198830319.0                 66053.0   \n",
       "0        5909.0           116761875.0                 51354.0   \n",
       "20       7723.0           216716805.0                 84380.0   \n",
       "30       4690.0           107911718.0                 50589.0   \n",
       "51       1645.0            64918706.0                 35721.0   \n",
       "62       2253.0            46107217.0                 20015.0   \n",
       "6        1947.0            54743718.0                 23786.0   \n",
       "35       1918.0            36855454.0                 22742.0   \n",
       "39       1355.0            27644232.0                 15554.0   \n",
       "\n",
       "    robbery value total  robbery offense count  larceny value total  ...  \\\n",
       "16           36562085.0                25577.0          370442027.0  ...   \n",
       "2            21669511.0                13370.0          278315655.0  ...   \n",
       "0            11409325.0                 6508.0          335521418.0  ...   \n",
       "20           15314783.0                10964.0          299013763.0  ...   \n",
       "30           10201959.0                 6274.0          207580533.0  ...   \n",
       "51            4770579.0                 4016.0           77779750.0  ...   \n",
       "62            2098369.0                 1516.0           69737983.0  ...   \n",
       "6             2374199.0                 1668.0          122738289.0  ...   \n",
       "35            2766497.0                 1362.0          101014043.0  ...   \n",
       "39             905658.0                  926.0           61193065.0  ...   \n",
       "\n",
       "    Simple Assault  Stolen Property: Buying, Receiving, Possessing  Suspicion  \\\n",
       "16         75273.0                                          2090.0        0.0   \n",
       "2          47502.0                                          1963.0       24.0   \n",
       "0          33553.0                                          3452.0       24.0   \n",
       "20         44461.0                                          1543.0        5.0   \n",
       "30         33012.0                                          3353.0       16.0   \n",
       "51         14451.0                                           179.0        0.0   \n",
       "62         18901.0                                           804.0        3.0   \n",
       "6          16925.0                                           501.0        3.0   \n",
       "35         18391.0                                          1019.0        0.0   \n",
       "39         11911.0                                           314.0        7.0   \n",
       "\n",
       "    Vagrancy  Vandalism  Weapons: Carrying, Possessing, Etc.  \\\n",
       "16    4275.0    19468.0                              12164.0   \n",
       "2     1029.0    14605.0                               6911.0   \n",
       "0      402.0    12868.0                               5181.0   \n",
       "20      42.0    18489.0                               6921.0   \n",
       "30     217.0    14722.0                               3561.0   \n",
       "51      92.0     3318.0                               1186.0   \n",
       "62      54.0     5663.0                               1636.0   \n",
       "6       56.0     6042.0                               1254.0   \n",
       "35    3340.0     8233.0                               2446.0   \n",
       "39      34.0     4257.0                               1625.0   \n",
       "\n",
       "    Opium or Cocaine or Their Derivatives  \\\n",
       "16                                 7192.0   \n",
       "2                                  2073.0   \n",
       "0                                   890.0   \n",
       "20                                 1839.0   \n",
       "30                                  341.0   \n",
       "51                                  550.0   \n",
       "62                                  397.0   \n",
       "6                                   657.0   \n",
       "35                                  346.0   \n",
       "39                                  402.0   \n",
       "\n",
       "    Other - Dangerous Nonnarcotic Drugs  Synthetic Narcotics  \\\n",
       "16                               1947.0                142.0   \n",
       "2                                 852.0                 63.0   \n",
       "0                                1267.0                221.0   \n",
       "20                               2036.0                105.0   \n",
       "30                                720.0                410.0   \n",
       "51                                240.0                 43.0   \n",
       "62                                740.0                171.0   \n",
       "6                                 516.0                 60.0   \n",
       "35                               1395.0                157.0   \n",
       "39                                955.0                251.0   \n",
       "\n",
       "    Drug Sale/Manufacturing - Subtotal  \n",
       "16                             10966.0  \n",
       "2                               4403.0  \n",
       "0                               3431.0  \n",
       "20                              5239.0  \n",
       "30                              2377.0  \n",
       "51                               958.0  \n",
       "62                              1732.0  \n",
       "6                               1790.0  \n",
       "35                              2486.0  \n",
       "39                              2121.0  \n",
       "\n",
       "[10 rows x 44 columns]"
      ]
     },
     "execution_count": 405,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Display top counties for auto theft\n",
    "pd.set_option('display.max_rows', 10)\n",
    "crime_df_agency.groupby(['county_name'], as_index = False).sum() \\\n",
    "    .nlargest(10, 'mvt offense count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 407,
   "id": "42f5a373",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-07T21:41:01.954767Z",
     "start_time": "2023-06-07T21:41:01.933490Z"
    }
   },
   "outputs": [],
   "source": [
    "crime_df = crime_df_agency.groupby(['county_name', 'year'], as_index = False).sum(numeric_only = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "aa52fa86",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T19:08:24.816115Z",
     "start_time": "2023-06-08T19:08:24.793086Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'crime_df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[9], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[43mcrime_df\u001b[49m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'crime_df' is not defined"
     ]
    }
   ],
   "source": [
    "crime_df"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  },
  "nbTranslate": {
   "displayLangs": [
    "*"
   ],
   "hotkey": "alt-t",
   "langInMainMenu": true,
   "sourceLang": "en",
   "targetLang": "fr",
   "useGoogleTranslate": true
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}