ViolentCrime_SummaryStats.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Violent Crime Statistics - Data Processing Steps\n",
    "\n",
    "This Jupyter notebook documents the repeatable steps of processing the DC violent crime data by Census block group. The below worklow is summarized here:\n",
    "\n",
    "1. Extract violent crime data from full crime dataset (from AWS to Pandas dataframe)\n",
    "2. Group data by block group and unstack to see crimes by crime type\n",
    "3. Complete formatting of pandas dataframe\n",
    "4. Load final violent crime statistics data to AWS\n",
    "\n",
    "Once the violent crime data and the socioeceonomic data are available via AWS, they can be remotely joined and be ready for machine learning analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Load Violent Crime Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# SQL Alchemy\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# PyMySQL \n",
    "import pymysql\n",
    "pymysql.install_as_MySQLdb()\n",
    "\n",
    "# Config variables\n",
    "from config import remote_db_endpoint, remote_db_port\n",
    "from config import remote_dccrime_dbname, remote_dccrime_dbuser, remote_dccrime_dbpwd\n",
    "\n",
    "# Import Pandas\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a remote database engine connection\n",
    "# AWS Database Connection\n",
    "engine = create_engine(f\"mysql://{remote_dccrime_dbuser}:{remote_dccrime_dbpwd}@{remote_db_endpoint}:{remote_db_port}/{remote_dccrime_dbname}\")\n",
    "conn = engine.connect()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1432\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>OFFENSE</th>\n",
       "      <th>BLOCK_GROUP</th>\n",
       "      <th>COUNT(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASSAULT W/DANGEROUS WEAPON</td>\n",
       "      <td></td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ASSAULT W/DANGEROUS WEAPON</td>\n",
       "      <td>000100 1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASSAULT W/DANGEROUS WEAPON</td>\n",
       "      <td>000100 2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASSAULT W/DANGEROUS WEAPON</td>\n",
       "      <td>000100 3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ASSAULT W/DANGEROUS WEAPON</td>\n",
       "      <td>000100 4</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      OFFENSE BLOCK_GROUP  COUNT(*)\n",
       "0  ASSAULT W/DANGEROUS WEAPON                     9\n",
       "1  ASSAULT W/DANGEROUS WEAPON    000100 1         3\n",
       "2  ASSAULT W/DANGEROUS WEAPON    000100 2         3\n",
       "3  ASSAULT W/DANGEROUS WEAPON    000100 3         7\n",
       "4  ASSAULT W/DANGEROUS WEAPON    000100 4        31"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# From AWS database, only extract violent crime incidents and only the offense, block group, and count-of-each.\n",
    "\n",
    "violent_crime = pd.read_sql(\"SELECT OFFENSE, BLOCK_GROUP, COUNT(*) from crime_incidents_all WHERE OFFENSE IN ('ASSAULT W/DANGEROUS WEAPON', 'HOMICIDE', 'ROBBERY', 'SEX ABUSE') GROUP BY OFFENSE, BLOCK_GROUP\", conn)\n",
    "print(len(violent_crime))\n",
    "violent_crime.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "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>Offense</th>\n",
       "      <th>Block Group</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASSAULT W/DANGEROUS WEAPON</td>\n",
       "      <td></td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ASSAULT W/DANGEROUS WEAPON</td>\n",
       "      <td>0001001</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASSAULT W/DANGEROUS WEAPON</td>\n",
       "      <td>0001002</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASSAULT W/DANGEROUS WEAPON</td>\n",
       "      <td>0001003</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ASSAULT W/DANGEROUS WEAPON</td>\n",
       "      <td>0001004</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Offense Block Group  Count\n",
       "0  ASSAULT W/DANGEROUS WEAPON                  9\n",
       "1  ASSAULT W/DANGEROUS WEAPON     0001001      3\n",
       "2  ASSAULT W/DANGEROUS WEAPON     0001002      3\n",
       "3  ASSAULT W/DANGEROUS WEAPON     0001003      7\n",
       "4  ASSAULT W/DANGEROUS WEAPON     0001004     31"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dataframe column formatting\n",
    "violent_crime_df = violent_crime.rename(columns={\"OFFENSE\":\"Offense\", \"BLOCK_GROUP\":\"Block Group\", \"COUNT(*)\": \"Count\"})\n",
    "violent_crime_df['Block Group'] = violent_crime_df['Block Group'].str.replace(\" \",\"\")\n",
    "violent_crime_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Group Data by Block Group and Offense"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group violent crime data by block group and offense\n",
    "grouped_violent = violent_crime_df.groupby(['Block Group', 'Offense'])\n",
    "grouped_violent_df = grouped_violent.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">Count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Offense</th>\n",
       "      <th>ASSAULT W/DANGEROUS WEAPON</th>\n",
       "      <th>HOMICIDE</th>\n",
       "      <th>ROBBERY</th>\n",
       "      <th>SEX ABUSE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Block Group</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0001001</th>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0001002</th>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0001003</th>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0001004</th>\n",
       "      <td>31.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Count                           \n",
       "Offense     ASSAULT W/DANGEROUS WEAPON HOMICIDE ROBBERY SEX ABUSE\n",
       "Block Group                                                      \n",
       "                                   9.0      2.0     4.0       2.0\n",
       "0001001                            3.0      NaN     2.0       1.0\n",
       "0001002                            3.0      NaN    10.0       2.0\n",
       "0001003                            7.0      NaN     8.0       2.0\n",
       "0001004                           31.0      NaN    36.0       7.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Unstack dataframe so that offense types are listed across column headers\n",
    "grouped_violent_df2 = grouped_violent_df.unstack()\n",
    "grouped_violent_df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Clean Dataframe Formatting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "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>ADW</th>\n",
       "      <th>Homicide</th>\n",
       "      <th>Robbery</th>\n",
       "      <th>Sex Abuse</th>\n",
       "      <th>BG</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Block Group</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>0001001</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0001001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0001002</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0001002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0001003</th>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0001003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0001004</th>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0001004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0002011</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0002011</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              ADW  Homicide  Robbery  Sex Abuse       BG\n",
       "Block Group                                             \n",
       "0001001       3.0       0.0      2.0        1.0  0001001\n",
       "0001002       3.0       0.0     10.0        2.0  0001002\n",
       "0001003       7.0       0.0      8.0        2.0  0001003\n",
       "0001004      31.0       0.0     36.0        7.0  0001004\n",
       "0002011       1.0       0.0      5.0        4.0  0002011"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create new cleaner dataframe of crime data\n",
    "ADW_sum = grouped_violent_df2.groupby([\"Block Group\"]).sum()[\"Count\"][\"ASSAULT W/DANGEROUS WEAPON\"]\n",
    "homicide_sum = grouped_violent_df2.groupby([\"Block Group\"]).sum()[\"Count\"][\"HOMICIDE\"]\n",
    "robbery_sum = grouped_violent_df2.groupby([\"Block Group\"]).sum()[\"Count\"][\"ROBBERY\"]\n",
    "sexabuse_sum = grouped_violent_df2.groupby([\"Block Group\"]).sum()[\"Count\"][\"SEX ABUSE\"]\n",
    "\n",
    "crime_totals = pd.DataFrame({\"ADW\": ADW_sum,\n",
    "                                   \"Homicide\": homicide_sum,\n",
    "                                   \"Robbery\": robbery_sum,\n",
    "                                   \"Sex Abuse\": sexabuse_sum})\n",
    "\n",
    "\n",
    "crime_totals.drop(crime_totals.index[0], inplace=True)\n",
    "\n",
    "crime_totals['BG'] = crime_totals.index\n",
    "crime_totals.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Load Violent Crime Data to AWS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(f\"mysql://{remote_dccrime_dbuser}:{remote_dccrime_dbpwd}@{remote_db_endpoint}:{remote_db_port}/{remote_dccrime_dbname}\")\n",
    "conn = engine.connect()\n",
    "\n",
    "#socioecon_data_df.to_sql('socioecon_data', conn)\n",
    "crime_totals.to_sql(name='crime_totals', if_exists='replace', con=conn, chunksize=1000, index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}