Child shooting victims in the 11th district.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How many children, 16 and under, have been shot in the 11th District each year since 2012?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're going to do this two ways.  First, by checking if the coordinates in each record are in the District 11 boundary and then by checking the explicit `District` column in each record."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load the shooting victims data from NewsroomDB\n",
    "\n",
    "NewsroomDB is the Tribune's proprietary database for tracking data that needs to be manually entered and validated rather than something that can be ingested from an official source.  It's mostly used to track shooting victims and homicides.  As far as I know, CPD doesn't provide granular data on shooting victims and the definition of homicide can be tricky (and vary from source to source).\n",
    "\n",
    "We'll grab shooting victims from the `shootings` collection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loaded 11908 shooting victims\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import requests\n",
    "\n",
    "def get_table_url(table_name, base_url=os.environ['NEWSROOMDB_URL']):\n",
    "    return '{}table/json/{}'.format(os.environ['NEWSROOMDB_URL'], table_name)\n",
    "\n",
    "def get_table_data(table_name):\n",
    "    url = get_table_url(table_name)\n",
    "    \n",
    "    try:\n",
    "        r = requests.get(url)\n",
    "        return r.json()\n",
    "    except:\n",
    "        print(\"Request failed. Probably because the response is huge.  We should fix this.\")\n",
    "        return get_table_data(table_name)\n",
    "\n",
    "shooting_victims = get_table_data('shootings')\n",
    "\n",
    "print(\"Loaded {} shooting victims\".format(len(shooting_victims)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Load police district boundaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "from shapely.geometry import shape\n",
    "\n",
    "# The City of Chicago's Socrata-based Data Portal provides a GeoJSON export of its spatial datasets.\n",
    "# We'll use this so we don't have to save spatial data to the repo.\n",
    "POLICE_DISTRICT_BOUNDARIES_GEOJSON_URL = \"https://data.cityofchicago.org/api/geospatial/fthy-xz3r?method=export&format=GeoJSON\"\n",
    "\n",
    "r = requests.get(POLICE_DISTRICT_BOUNDARIES_GEOJSON_URL)\n",
    "police_districts = r.json()\n",
    "\n",
    "# Get the District 11 GeoJSON feature\n",
    "district_11_feature = next(f for f in police_districts['features']\n",
    "                           if f['properties']['dist_num'] == \"11\")\n",
    "\n",
    "# Convert it to a Shapely shape so we can detect our \n",
    "district_11_boundary = shape(district_11_feature['geometry'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Annotate and filter shooting victims"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "import re\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "def parse_date(s):\n",
    "    try:\n",
    "        return datetime.strptime(s, '%Y-%m-%d').date()\n",
    "    except ValueError:\n",
    "        return None\n",
    "    \n",
    "def parse_coordinates(coordinate_str):\n",
    "    \"\"\"Convert a lat, lng string to a pair of lng, lat floats\"\"\"\n",
    "    try:\n",
    "        lat, lng = [float(c) for c in re.sub(r'[\\(\\) ]', '', coordinate_str).split(',')]\n",
    "        return lng, lat\n",
    "    except ValueError:\n",
    "        return None\n",
    "    \n",
    "def parse_age(age_str):\n",
    "    try:\n",
    "        return int(age_str)\n",
    "    except ValueError:\n",
    "        return None\n",
    "    \n",
    "def get_year(shooting_date):\n",
    "    try:\n",
    "        return shooting_date.year\n",
    "    except AttributeError:\n",
    "        return None\n",
    "\n",
    "shooting_victims_df = pd.DataFrame(shooting_victims)\n",
    "shooting_victims_df['Date'] = shooting_victims_df['Date'].apply(parse_date)\n",
    "shooting_victims_df['Age'] = shooting_victims_df['Age'].apply(parse_age)\n",
    "shooting_victims_df['coordinates'] = shooting_victims_df['Geocode Override'].apply(parse_coordinates)\n",
    "shooting_victims_df['year'] = shooting_victims_df['Date'].apply(get_year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "child_shooting_victims = shooting_victims_df[shooting_victims_df['Age'] < 18]\n",
    "child_shooting_victims_16_and_under = child_shooting_victims[child_shooting_victims['Age'] <= 16]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "child_shooting_victims_since_2012 = child_shooting_victims[child_shooting_victims['year'] >= 2012]\n",
    "child_shooting_victims_16_and_under_since_2012 = child_shooting_victims_16_and_under[child_shooting_victims_16_and_under['year'] >= 2012]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There have been 121 victims, under 18 years of age, who have been shot in the 11th district since 2012\n",
      "There have been 82 victims, age 16 or under, who have been shot in the 11th district since 2012\n"
     ]
    }
   ],
   "source": [
    "from shapely.geometry import Point\n",
    "\n",
    "def is_in_11th_district(shooting_coordinates):\n",
    "    try:\n",
    "        shooting_point = Point(shooting_coordinates[0], shooting_coordinates[1])\n",
    "        return district_11_boundary.contains(shooting_point)\n",
    "    except TypeError:\n",
    "        return False\n",
    "    \n",
    "child_shooting_victims_since_2012_in_11th_dist = child_shooting_victims_since_2012[\n",
    "    child_shooting_victims_since_2012['coordinates'].apply(is_in_11th_district)\n",
    "]\n",
    "\n",
    "\n",
    "child_shooting_victims_16_and_under_since_2012_in_11th_dist = child_shooting_victims_16_and_under_since_2012[\n",
    "    child_shooting_victims_16_and_under_since_2012['coordinates'].apply(is_in_11th_district)\n",
    "]\n",
    "\n",
    "print(\"There have been {} victims, under 18 years of age, who have been shot in the 11th district since 2012\".format(\n",
    "    len(child_shooting_victims_since_2012_in_11th_dist)))\n",
    "\n",
    "print(\"There have been {} victims, age 16 or under, who have been shot in the 11th district since 2012\".format(\n",
    "    len(child_shooting_victims_16_and_under_since_2012_in_11th_dist)))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11\n",
      "10\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n",
      "11\n"
     ]
    }
   ],
   "source": [
    "# Sanity check our filter\n",
    "# It looks like one of our rows has a district of 10.  Maybe this is because of bad\n",
    "# data entry\n",
    "for i, victim in child_shooting_victims_16_and_under_since_2012_in_11th_dist.iterrows():\n",
    "    print(victim['District'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num_victims</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012.0</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013.0</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014.0</th>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015.0</th>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016.0</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        num_victims\n",
       "year               \n",
       "2012.0           21\n",
       "2013.0           11\n",
       "2014.0           38\n",
       "2015.0           34\n",
       "2016.0           17"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "child_shooting_victims_since_2012_in_11th_dist_by_year = pd.DataFrame(\n",
    "    child_shooting_victims_since_2012_in_11th_dist.groupby('year').size(),\n",
    "    columns=['num_victims']\n",
    ")\n",
    "child_shooting_victims_since_2012_in_11th_dist_by_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num_victims</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012.0</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013.0</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014.0</th>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015.0</th>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016.0</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        num_victims\n",
       "year               \n",
       "2012.0           17\n",
       "2013.0            9\n",
       "2014.0           25\n",
       "2015.0           23\n",
       "2016.0            8"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "child_shooting_victims_16_and_under_since_2012_in_11th_dist_by_year = pd.DataFrame(\n",
    "    child_shooting_victims_16_and_under_since_2012_in_11th_dist.groupby('year').size(),\n",
    "    columns=['num_victims']\n",
    ")\n",
    "child_shooting_victims_16_and_under_since_2012_in_11th_dist_by_year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's just use the \"District\" column\n",
    "\n",
    "After doing all the spatial stuff, I realized there is a \"District\" column I could have used to filter.  Note that in the above data, there is one row with a \"District\" column of \"10\".  Perhaps this is because it was mislabeled."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There have been 124 victims, under age 18, who have been shot in the 11th district since 2012\n",
      "There have been 124 victims, under age 18, who have been shot in the 11th district since 2012\n"
     ]
    }
   ],
   "source": [
    "def is_11th_district(district):\n",
    "    try:\n",
    "        return int(district) == 11\n",
    "    except ValueError:\n",
    "        return False\n",
    "\n",
    "child_shooting_victims_since_2012_in_11th_dist = child_shooting_victims_since_2012[child_shooting_victims_since_2012['District'].apply(is_11th_district)]\n",
    "print(\"There have been {} victims, under age 18, who have been shot in the 11th district since 2012\".format(\n",
    "    len(child_shooting_victims_since_2012_in_11th_dist)))\n",
    "child_shooting_victims_16_and_under_since_2012_in_11th_dist = child_shooting_victims_16_and_under_since_2012[child_shooting_victims_16_and_under_since_2012['District'].apply(is_11th_district)]\n",
    "print(\"There have been {} victims, under age 18, who have been shot in the 11th district since 2012\".format(\n",
    "    len(child_shooting_victims_since_2012_in_11th_dist)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num_victims</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012.0</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013.0</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014.0</th>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015.0</th>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016.0</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        num_victims\n",
       "year               \n",
       "2012.0           21\n",
       "2013.0           11\n",
       "2014.0           41\n",
       "2015.0           34\n",
       "2016.0           17"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "child_shooting_victims_since_2012_in_11th_dist_by_year = pd.DataFrame(\n",
    "    child_shooting_victims_since_2012_in_11th_dist.groupby('year').size(),\n",
    "    columns=['num_victims']\n",
    ")\n",
    "child_shooting_victims_since_2012_in_11th_dist_by_year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, it looks like using this method there are 3 more victims in 2014.  This could be due to bad or missing coordinates in some of the rows that cause them to not be categorized when we use the coordinates to detect district."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num_victims</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012.0</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013.0</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014.0</th>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015.0</th>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016.0</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        num_victims\n",
       "year               \n",
       "2012.0           17\n",
       "2013.0            9\n",
       "2014.0           28\n",
       "2015.0           23\n",
       "2016.0            8"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "child_shooting_victims_16_and_under_since_2012_in_11th_dist_by_year = pd.DataFrame(\n",
    "    child_shooting_victims_16_and_under_since_2012_in_11th_dist.groupby('year').size(),\n",
    "    columns=['num_victims']\n",
    ")\n",
    "child_shooting_victims_16_and_under_since_2012_in_11th_dist_by_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.4.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}