notebooks/02-analysis/fines-01a-aggregate-by-community.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "638444be",
   "metadata": {},
   "source": [
    "# Summarize by Community\n",
    "last updated 3/3/24"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "488cdc75",
   "metadata": {},
   "source": [
    "<a name=\"read\"></a>\n",
    "# 1. Read and Prepare Geocoded Fines and 311 Complaints Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "109b46e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9c1c3a47",
   "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>docket</th>\n",
       "      <th>dept</th>\n",
       "      <th>violation_address</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>community</th>\n",
       "      <th>ward_1523</th>\n",
       "      <th>violation_date</th>\n",
       "      <th>n_records</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19DS68300L</td>\n",
       "      <td>STRTSAN</td>\n",
       "      <td>4710 S WESTERN AVE</td>\n",
       "      <td>41.807859</td>\n",
       "      <td>-87.68479703503766</td>\n",
       "      <td>BRIGHTON PARK</td>\n",
       "      <td>15</td>\n",
       "      <td>2019-11-13</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19DS69216L</td>\n",
       "      <td>STRTSAN</td>\n",
       "      <td>1425 W MORSE AVE</td>\n",
       "      <td>42.0074513</td>\n",
       "      <td>-87.6668285</td>\n",
       "      <td>ROGERS PARK</td>\n",
       "      <td>49</td>\n",
       "      <td>2019-11-13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19DS70010L</td>\n",
       "      <td>STRTSAN</td>\n",
       "      <td>715 E 47TH ST</td>\n",
       "      <td>41.8093383</td>\n",
       "      <td>-87.6080127</td>\n",
       "      <td>GRAND BOULEVARD</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-11-13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>19DS72153L</td>\n",
       "      <td>STRTSAN</td>\n",
       "      <td>300 W WASHINGTON ST</td>\n",
       "      <td>41.8818694</td>\n",
       "      <td>-87.7401431</td>\n",
       "      <td>WEST GARFIELD PARK</td>\n",
       "      <td>28</td>\n",
       "      <td>2019-11-12</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19DS72160L</td>\n",
       "      <td>STRTSAN</td>\n",
       "      <td>6929 N SHERIDAN RD</td>\n",
       "      <td>41.9598134</td>\n",
       "      <td>-87.654693</td>\n",
       "      <td>UPTOWN</td>\n",
       "      <td>46</td>\n",
       "      <td>2019-11-14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       docket     dept    violation_address         lat                long  \\\n",
       "0  19DS68300L  STRTSAN   4710 S WESTERN AVE   41.807859  -87.68479703503766   \n",
       "1  19DS69216L  STRTSAN     1425 W MORSE AVE  42.0074513         -87.6668285   \n",
       "2  19DS70010L  STRTSAN        715 E 47TH ST  41.8093383         -87.6080127   \n",
       "3  19DS72153L  STRTSAN  300 W WASHINGTON ST  41.8818694         -87.7401431   \n",
       "4  19DS72160L  STRTSAN   6929 N SHERIDAN RD  41.9598134          -87.654693   \n",
       "\n",
       "            community ward_1523 violation_date  n_records  \n",
       "0       BRIGHTON PARK        15     2019-11-13          2  \n",
       "1         ROGERS PARK        49     2019-11-13          1  \n",
       "2     GRAND BOULEVARD         4     2019-11-13          1  \n",
       "3  WEST GARFIELD PARK        28     2019-11-12          5  \n",
       "4              UPTOWN        46     2019-11-14          1  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fines = pd.read_csv(\"../../data/05-finalized/dockets-summary.csv\")\n",
    "df_fines.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e44124bf",
   "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>SR_NUMBER</th>\n",
       "      <th>SR_SHORT_CODE</th>\n",
       "      <th>CREATED_DATE</th>\n",
       "      <th>STREET_ADDRESS</th>\n",
       "      <th>COMMUNITY_AREA</th>\n",
       "      <th>WARD</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>ORIGIN</th>\n",
       "      <th>CLOSED_DATE</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>SR_TYPE</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>date</th>\n",
       "      <th>season</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>community_caps</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SR20-05649092</td>\n",
       "      <td>SWSNOREM</td>\n",
       "      <td>2020-12-17 13:24:37</td>\n",
       "      <td>5200 S BLACKSTONE AVE</td>\n",
       "      <td>41</td>\n",
       "      <td>4</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Phone Call</td>\n",
       "      <td>2020-12-18 15:12:51</td>\n",
       "      <td>41.800909</td>\n",
       "      <td>-87.590515</td>\n",
       "      <td>Snow – Uncleared Sidewalk Complaint</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>2020-12-17</td>\n",
       "      <td>2020-2021</td>\n",
       "      <td>41</td>\n",
       "      <td>HYDE PARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SR21-00001364</td>\n",
       "      <td>SWSNOREM</td>\n",
       "      <td>2021-01-01 11:40:33</td>\n",
       "      <td>66 E CHESTNUT ST</td>\n",
       "      <td>8</td>\n",
       "      <td>42</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Mobile Device</td>\n",
       "      <td>2021-01-04 14:33:44</td>\n",
       "      <td>41.898399</td>\n",
       "      <td>-87.625723</td>\n",
       "      <td>Snow – Uncleared Sidewalk Complaint</td>\n",
       "      <td>2021</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>2020-2021</td>\n",
       "      <td>8</td>\n",
       "      <td>NEAR NORTH SIDE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SR21-00000774</td>\n",
       "      <td>SWSNOREM</td>\n",
       "      <td>2021-01-01 09:11:15</td>\n",
       "      <td>2648 N WHIPPLE ST</td>\n",
       "      <td>22</td>\n",
       "      <td>32</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Internet</td>\n",
       "      <td>2021-01-06 13:37:58</td>\n",
       "      <td>41.930233</td>\n",
       "      <td>-87.703706</td>\n",
       "      <td>Snow – Uncleared Sidewalk Complaint</td>\n",
       "      <td>2021</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>2020-2021</td>\n",
       "      <td>22</td>\n",
       "      <td>LOGAN SQUARE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SR20-05723378</td>\n",
       "      <td>SWSNOREM</td>\n",
       "      <td>2020-12-31 07:19:37</td>\n",
       "      <td>5015 N SPRINGFIELD AVE</td>\n",
       "      <td>14</td>\n",
       "      <td>39</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Mobile Device</td>\n",
       "      <td>2021-01-08 15:30:57</td>\n",
       "      <td>41.972439</td>\n",
       "      <td>-87.725430</td>\n",
       "      <td>Snow – Uncleared Sidewalk Complaint</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-2021</td>\n",
       "      <td>14</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SR21-00014080</td>\n",
       "      <td>SWSNOREM</td>\n",
       "      <td>2021-01-04 14:07:09</td>\n",
       "      <td>3757 N BELL AVE</td>\n",
       "      <td>5</td>\n",
       "      <td>47</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Internet</td>\n",
       "      <td>2021-01-06 11:37:30</td>\n",
       "      <td>41.950316</td>\n",
       "      <td>-87.684472</td>\n",
       "      <td>Snow – Uncleared Sidewalk Complaint</td>\n",
       "      <td>2021</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-04</td>\n",
       "      <td>2020-2021</td>\n",
       "      <td>5</td>\n",
       "      <td>NORTH CENTER</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       SR_NUMBER SR_SHORT_CODE         CREATED_DATE          STREET_ADDRESS  \\\n",
       "0  SR20-05649092      SWSNOREM  2020-12-17 13:24:37   5200 S BLACKSTONE AVE   \n",
       "1  SR21-00001364      SWSNOREM  2021-01-01 11:40:33        66 E CHESTNUT ST   \n",
       "2  SR21-00000774      SWSNOREM  2021-01-01 09:11:15       2648 N WHIPPLE ST   \n",
       "3  SR20-05723378      SWSNOREM  2020-12-31 07:19:37  5015 N SPRINGFIELD AVE   \n",
       "4  SR21-00014080      SWSNOREM  2021-01-04 14:07:09         3757 N BELL AVE   \n",
       "\n",
       "   COMMUNITY_AREA  WARD     STATUS         ORIGIN          CLOSED_DATE  \\\n",
       "0              41     4  Completed     Phone Call  2020-12-18 15:12:51   \n",
       "1               8    42  Completed  Mobile Device  2021-01-04 14:33:44   \n",
       "2              22    32  Completed       Internet  2021-01-06 13:37:58   \n",
       "3              14    39  Completed  Mobile Device  2021-01-08 15:30:57   \n",
       "4               5    47  Completed       Internet  2021-01-06 11:37:30   \n",
       "\n",
       "    LATITUDE  LONGITUDE                              SR_TYPE  year  month  \\\n",
       "0  41.800909 -87.590515  Snow – Uncleared Sidewalk Complaint  2020     12   \n",
       "1  41.898399 -87.625723  Snow – Uncleared Sidewalk Complaint  2021      1   \n",
       "2  41.930233 -87.703706  Snow – Uncleared Sidewalk Complaint  2021      1   \n",
       "3  41.972439 -87.725430  Snow – Uncleared Sidewalk Complaint  2020     12   \n",
       "4  41.950316 -87.684472  Snow – Uncleared Sidewalk Complaint  2021      1   \n",
       "\n",
       "         date     season  GEOID   community_caps  \n",
       "0  2020-12-17  2020-2021     41        HYDE PARK  \n",
       "1  2021-01-01  2020-2021      8  NEAR NORTH SIDE  \n",
       "2  2021-01-01  2020-2021     22     LOGAN SQUARE  \n",
       "3  2020-12-31  2020-2021     14      ALBANY PARK  \n",
       "4  2021-01-04  2020-2021      5     NORTH CENTER  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_311 = pd.read_csv(\"../../data/05-finalized/311-complaints-unshoveled.csv\")\n",
    "df_311['community_caps']=df_311['COMMUNITY_NAME'].str.upper()\n",
    "df_311 = df_311.drop(columns=['COMMUNITY_NAME'])\n",
    "df_311.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6a3cec9",
   "metadata": {},
   "source": [
    "### census data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1ba83769",
   "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>community_name</th>\n",
       "      <th>2020_pop</th>\n",
       "      <th>vac_hu</th>\n",
       "      <th>hu_tot</th>\n",
       "      <th>vacant</th>\n",
       "      <th>vacperc</th>\n",
       "      <th>community_caps</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albany Park</td>\n",
       "      <td>48396</td>\n",
       "      <td>1448.000000</td>\n",
       "      <td>18230.000000</td>\n",
       "      <td>5.322983</td>\n",
       "      <td>0.004331</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Archer Heights</td>\n",
       "      <td>14196</td>\n",
       "      <td>406.000000</td>\n",
       "      <td>4538.000000</td>\n",
       "      <td>29.231599</td>\n",
       "      <td>0.022770</td>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Armour Square</td>\n",
       "      <td>13890</td>\n",
       "      <td>393.513552</td>\n",
       "      <td>5860.348806</td>\n",
       "      <td>15.696404</td>\n",
       "      <td>0.024625</td>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ashburn</td>\n",
       "      <td>41098</td>\n",
       "      <td>540.000000</td>\n",
       "      <td>13479.000000</td>\n",
       "      <td>59.468213</td>\n",
       "      <td>0.019123</td>\n",
       "      <td>ASHBURN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Auburn Gresham</td>\n",
       "      <td>44878</td>\n",
       "      <td>3364.000000</td>\n",
       "      <td>20617.000000</td>\n",
       "      <td>119.122147</td>\n",
       "      <td>0.049388</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community_name  2020_pop       vac_hu        hu_tot      vacant   vacperc  \\\n",
       "0     Albany Park     48396  1448.000000  18230.000000    5.322983  0.004331   \n",
       "1  Archer Heights     14196   406.000000   4538.000000   29.231599  0.022770   \n",
       "2   Armour Square     13890   393.513552   5860.348806   15.696404  0.024625   \n",
       "3         Ashburn     41098   540.000000  13479.000000   59.468213  0.019123   \n",
       "4  Auburn Gresham     44878  3364.000000  20617.000000  119.122147  0.049388   \n",
       "\n",
       "   community_caps  \n",
       "0     ALBANY PARK  \n",
       "1  ARCHER HEIGHTS  \n",
       "2   ARMOUR SQUARE  \n",
       "3         ASHBURN  \n",
       "4  AUBURN GRESHAM  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_population = pd.read_csv(\"../../data/05-finalized/census-by-community.csv\")\n",
    "df_population.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e2e0a4d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "77"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_population)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b477dc1",
   "metadata": {},
   "source": [
    "<a name=\"community\"></a>\n",
    "# Aggregate Dockets by Community\n",
    "Note that 7 communities have no dockets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c4496c81",
   "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>community</th>\n",
       "      <th>n_BAFCONP</th>\n",
       "      <th>n_POLICE</th>\n",
       "      <th>n_STRTSAN</th>\n",
       "      <th>n_TRANPORT</th>\n",
       "      <th>n_unknown</th>\n",
       "      <th>n_dockets</th>\n",
       "      <th>community_caps</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>26</td>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASHBURN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>ASHBURN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        community  n_BAFCONP  n_POLICE  n_STRTSAN  n_TRANPORT  n_unknown  \\\n",
       "0     ALBANY PARK          0         0          0          15          0   \n",
       "1  ARCHER HEIGHTS          0         0          0          14          0   \n",
       "2   ARMOUR SQUARE          0         0          0          26          0   \n",
       "3         ASHBURN          0         0          0           2          0   \n",
       "4  AUBURN GRESHAM          0         0          3          16          0   \n",
       "\n",
       "   n_dockets  community_caps  \n",
       "0         15     ALBANY PARK  \n",
       "1         14  ARCHER HEIGHTS  \n",
       "2         26   ARMOUR SQUARE  \n",
       "3          2         ASHBURN  \n",
       "4         19  AUBURN GRESHAM  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_comm_fines = df_fines.pivot_table(index='community',\n",
    "                             columns='dept',\n",
    "                             values='docket',\n",
    "                             aggfunc=['count'],\n",
    "                             fill_value=0).reset_index()\n",
    "# Flatten the MultiIndex in columns\n",
    "df_comm_fines.columns = ['_'.join(col).strip() for col in df_comm_fines.columns.values]\n",
    "\n",
    "df_comm_fines = df_comm_fines.rename(columns={'community_':'community'})\n",
    "df_comm_fines.rename(columns=lambda x: x.replace('count_','n_'), inplace=True)\n",
    "\n",
    "# Reset the index to flatten it\n",
    "df_comm_fines.reset_index(drop=True, inplace=True)\n",
    "\n",
    "df_comm_fines['n_dockets'] = df_comm_fines.filter(like='n_').sum(axis=1)\n",
    "df_comm_fines['community_caps']=df_comm_fines['community']\n",
    "#df_comm_fines = df_comm_fines.drop(columns=['community'])\n",
    "\n",
    "df_comm_fines.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f920fb57",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1918"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirm totals\n",
    "df_comm_fines['n_dockets'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ec12b659",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_comm_fines)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a16428f",
   "metadata": {},
   "source": [
    "# Aggregate 311 Complaints by Community"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b4e51574",
   "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>community_caps</th>\n",
       "      <th>n_311_complaints</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASHBURN</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community_caps  n_311_complaints\n",
       "0     ALBANY PARK               400\n",
       "1  ARCHER HEIGHTS                60\n",
       "2   ARMOUR SQUARE                77\n",
       "3         ASHBURN               108\n",
       "4  AUBURN GRESHAM               128"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_comm_311 = df_311.groupby('community_caps').size().reset_index(name='n_311_complaints')\n",
    "df_comm_311['community_name']=df_comm_311['community_caps'].str.title()\n",
    "df_comm_311 = df_comm_311.drop(columns=['community_name'])\n",
    "df_comm_311.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c2656360",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "77"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_comm_311)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f207eb5f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21079"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_comm_311['n_311_complaints'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f1e9dd2",
   "metadata": {},
   "source": [
    "# Merge Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11e8b7d1",
   "metadata": {},
   "source": [
    "### Merge Community Census Data with Dockets and 311 complaints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "60ceb99c",
   "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>community_name</th>\n",
       "      <th>2020_pop</th>\n",
       "      <th>vac_hu</th>\n",
       "      <th>hu_tot</th>\n",
       "      <th>vacant</th>\n",
       "      <th>vacperc</th>\n",
       "      <th>community_caps</th>\n",
       "      <th>community</th>\n",
       "      <th>n_BAFCONP</th>\n",
       "      <th>n_POLICE</th>\n",
       "      <th>n_STRTSAN</th>\n",
       "      <th>n_TRANPORT</th>\n",
       "      <th>n_unknown</th>\n",
       "      <th>n_dockets</th>\n",
       "      <th>n_311_complaints</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albany Park</td>\n",
       "      <td>48396</td>\n",
       "      <td>1448.000000</td>\n",
       "      <td>18230.000000</td>\n",
       "      <td>5.322983</td>\n",
       "      <td>0.004331</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Archer Heights</td>\n",
       "      <td>14196</td>\n",
       "      <td>406.000000</td>\n",
       "      <td>4538.000000</td>\n",
       "      <td>29.231599</td>\n",
       "      <td>0.022770</td>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Armour Square</td>\n",
       "      <td>13890</td>\n",
       "      <td>393.513552</td>\n",
       "      <td>5860.348806</td>\n",
       "      <td>15.696404</td>\n",
       "      <td>0.024625</td>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>26</td>\n",
       "      <td>0.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ashburn</td>\n",
       "      <td>41098</td>\n",
       "      <td>540.000000</td>\n",
       "      <td>13479.000000</td>\n",
       "      <td>59.468213</td>\n",
       "      <td>0.019123</td>\n",
       "      <td>ASHBURN</td>\n",
       "      <td>ASHBURN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Auburn Gresham</td>\n",
       "      <td>44878</td>\n",
       "      <td>3364.000000</td>\n",
       "      <td>20617.000000</td>\n",
       "      <td>119.122147</td>\n",
       "      <td>0.049388</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community_name  2020_pop       vac_hu        hu_tot      vacant   vacperc  \\\n",
       "0     Albany Park     48396  1448.000000  18230.000000    5.322983  0.004331   \n",
       "1  Archer Heights     14196   406.000000   4538.000000   29.231599  0.022770   \n",
       "2   Armour Square     13890   393.513552   5860.348806   15.696404  0.024625   \n",
       "3         Ashburn     41098   540.000000  13479.000000   59.468213  0.019123   \n",
       "4  Auburn Gresham     44878  3364.000000  20617.000000  119.122147  0.049388   \n",
       "\n",
       "   community_caps       community  n_BAFCONP  n_POLICE  n_STRTSAN  n_TRANPORT  \\\n",
       "0     ALBANY PARK     ALBANY PARK          0         0          0          15   \n",
       "1  ARCHER HEIGHTS  ARCHER HEIGHTS          0         0          0          14   \n",
       "2   ARMOUR SQUARE   ARMOUR SQUARE          0         0          0          26   \n",
       "3         ASHBURN         ASHBURN          0         0          0           2   \n",
       "4  AUBURN GRESHAM  AUBURN GRESHAM          0         0          3          16   \n",
       "\n",
       "   n_unknown  n_dockets  n_311_complaints  \n",
       "0        0.0       15.0               400  \n",
       "1        0.0       14.0                60  \n",
       "2        0.0       26.0                77  \n",
       "3        0.0        2.0               108  \n",
       "4        0.0       19.0               128  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community = pd.merge(df_population,df_comm_fines, on = 'community_caps', how='left')\n",
    "df_community = pd.merge(df_community,df_comm_311, on = 'community_caps')\n",
    "df_community.fillna(0, inplace=True)\n",
    "df_community['n_BAFCONP']=df_community['n_BAFCONP'].astype(int)\n",
    "df_community['n_POLICE']=df_community['n_POLICE'].astype(int)\n",
    "df_community['n_STRTSAN']=df_community['n_STRTSAN'].astype(int)\n",
    "df_community['n_TRANPORT']=df_community['n_TRANPORT'].astype(int)\n",
    "df_community.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b105f8cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "77"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirm I have 77 communities\n",
    "len(df_community)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "4f83936d",
   "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>community_name</th>\n",
       "      <th>2020_pop</th>\n",
       "      <th>vac_hu</th>\n",
       "      <th>hu_tot</th>\n",
       "      <th>vacant</th>\n",
       "      <th>vacperc</th>\n",
       "      <th>community_caps</th>\n",
       "      <th>community</th>\n",
       "      <th>n_BAFCONP</th>\n",
       "      <th>n_POLICE</th>\n",
       "      <th>n_STRTSAN</th>\n",
       "      <th>n_TRANPORT</th>\n",
       "      <th>n_unknown</th>\n",
       "      <th>n_dockets</th>\n",
       "      <th>n_311_complaints</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Loop</td>\n",
       "      <td>42298</td>\n",
       "      <td>3598.0</td>\n",
       "      <td>24565.0</td>\n",
       "      <td>30.45673</td>\n",
       "      <td>0.028743</td>\n",
       "      <td>LOOP</td>\n",
       "      <td>LOOP</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>29</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>177</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community_name  2020_pop  vac_hu   hu_tot    vacant   vacperc  \\\n",
       "65           Loop     42298  3598.0  24565.0  30.45673  0.028743   \n",
       "\n",
       "   community_caps community  n_BAFCONP  n_POLICE  n_STRTSAN  n_TRANPORT  \\\n",
       "65           LOOP      LOOP          0         0          2          29   \n",
       "\n",
       "    n_unknown  n_dockets  n_311_complaints  \n",
       "65        0.0       31.0               177  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community[df_community['community_name']=='Loop']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32f04871",
   "metadata": {},
   "source": [
    "# Calculations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "115dee3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_community['dp10k'] = \\\n",
    "(10000/4)*df_community['n_dockets']/df_community['2020_pop']\n",
    "\n",
    "df_community['streets_p10k'] = \\\n",
    "(10000/4)*df_community['n_STRTSAN']/df_community['2020_pop']\n",
    "\n",
    "df_community['cdot_p10k'] = \\\n",
    "(10000/4)*df_community['n_TRANPORT']/df_community['2020_pop']\n",
    "\n",
    "df_community['police_p10k'] = \\\n",
    "(10000/4)*df_community['n_POLICE']/df_community['2020_pop']\n",
    "\n",
    "df_community['n_p10k'] = \\\n",
    "(10000/4)*df_community['n_BAFCONP']/df_community['2020_pop']\n",
    "\n",
    "df_community['311_p10k'] = \\\n",
    "(10000/4)*df_community['n_311_complaints']/df_community['2020_pop']\n",
    "\n",
    "df_community['complaint_ratio'] = df_community['n_TRANPORT']/df_community['n_311_complaints']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f46aa5df",
   "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>community_name</th>\n",
       "      <th>2020_pop</th>\n",
       "      <th>vac_hu</th>\n",
       "      <th>hu_tot</th>\n",
       "      <th>vacant</th>\n",
       "      <th>vacperc</th>\n",
       "      <th>community_caps</th>\n",
       "      <th>community</th>\n",
       "      <th>n_BAFCONP</th>\n",
       "      <th>n_POLICE</th>\n",
       "      <th>...</th>\n",
       "      <th>n_unknown</th>\n",
       "      <th>n_dockets</th>\n",
       "      <th>n_311_complaints</th>\n",
       "      <th>dp10k</th>\n",
       "      <th>streets_p10k</th>\n",
       "      <th>cdot_p10k</th>\n",
       "      <th>police_p10k</th>\n",
       "      <th>n_p10k</th>\n",
       "      <th>311_p10k</th>\n",
       "      <th>complaint_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albany Park</td>\n",
       "      <td>48396</td>\n",
       "      <td>1448.000000</td>\n",
       "      <td>18230.000000</td>\n",
       "      <td>5.322983</td>\n",
       "      <td>0.004331</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>400</td>\n",
       "      <td>0.774857</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.774857</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>20.662865</td>\n",
       "      <td>0.037500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Archer Heights</td>\n",
       "      <td>14196</td>\n",
       "      <td>406.000000</td>\n",
       "      <td>4538.000000</td>\n",
       "      <td>29.231599</td>\n",
       "      <td>0.022770</td>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>60</td>\n",
       "      <td>2.465483</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.465483</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.566357</td>\n",
       "      <td>0.233333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Armour Square</td>\n",
       "      <td>13890</td>\n",
       "      <td>393.513552</td>\n",
       "      <td>5860.348806</td>\n",
       "      <td>15.696404</td>\n",
       "      <td>0.024625</td>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>77</td>\n",
       "      <td>4.679626</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.679626</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>13.858891</td>\n",
       "      <td>0.337662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ashburn</td>\n",
       "      <td>41098</td>\n",
       "      <td>540.000000</td>\n",
       "      <td>13479.000000</td>\n",
       "      <td>59.468213</td>\n",
       "      <td>0.019123</td>\n",
       "      <td>ASHBURN</td>\n",
       "      <td>ASHBURN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>108</td>\n",
       "      <td>0.121660</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.121660</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.569663</td>\n",
       "      <td>0.018519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Auburn Gresham</td>\n",
       "      <td>44878</td>\n",
       "      <td>3364.000000</td>\n",
       "      <td>20617.000000</td>\n",
       "      <td>119.122147</td>\n",
       "      <td>0.049388</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>128</td>\n",
       "      <td>1.058425</td>\n",
       "      <td>0.167120</td>\n",
       "      <td>0.891305</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>7.130443</td>\n",
       "      <td>0.125000</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",
       "      <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>72</th>\n",
       "      <td>West Lawn</td>\n",
       "      <td>33662</td>\n",
       "      <td>525.000000</td>\n",
       "      <td>9701.000000</td>\n",
       "      <td>46.394184</td>\n",
       "      <td>0.024551</td>\n",
       "      <td>WEST LAWN</td>\n",
       "      <td>WEST LAWN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>146</td>\n",
       "      <td>1.262551</td>\n",
       "      <td>0.148535</td>\n",
       "      <td>1.114016</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.843087</td>\n",
       "      <td>0.102740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>West Pullman</td>\n",
       "      <td>26104</td>\n",
       "      <td>2147.000000</td>\n",
       "      <td>11210.000000</td>\n",
       "      <td>221.071452</td>\n",
       "      <td>0.096914</td>\n",
       "      <td>WEST PULLMAN</td>\n",
       "      <td>WEST PULLMAN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>23</td>\n",
       "      <td>0.478854</td>\n",
       "      <td>0.095771</td>\n",
       "      <td>0.287312</td>\n",
       "      <td>0.095771</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.202728</td>\n",
       "      <td>0.130435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>West Ridge</td>\n",
       "      <td>77122</td>\n",
       "      <td>2185.000000</td>\n",
       "      <td>28054.000000</td>\n",
       "      <td>11.765845</td>\n",
       "      <td>0.005207</td>\n",
       "      <td>WEST RIDGE</td>\n",
       "      <td>WEST RIDGE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>804</td>\n",
       "      <td>0.777988</td>\n",
       "      <td>0.226913</td>\n",
       "      <td>0.551075</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>26.062602</td>\n",
       "      <td>0.021144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>West Town</td>\n",
       "      <td>87781</td>\n",
       "      <td>3177.000000</td>\n",
       "      <td>41891.000000</td>\n",
       "      <td>62.407906</td>\n",
       "      <td>0.021311</td>\n",
       "      <td>WEST TOWN</td>\n",
       "      <td>WEST TOWN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>1609</td>\n",
       "      <td>2.762557</td>\n",
       "      <td>0.028480</td>\n",
       "      <td>2.734077</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>45.824267</td>\n",
       "      <td>0.059664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>Woodlawn</td>\n",
       "      <td>24425</td>\n",
       "      <td>2436.000000</td>\n",
       "      <td>11960.000000</td>\n",
       "      <td>122.772101</td>\n",
       "      <td>0.092636</td>\n",
       "      <td>WOODLAWN</td>\n",
       "      <td>WOODLAWN</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>120</td>\n",
       "      <td>0.409417</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.204708</td>\n",
       "      <td>0.204708</td>\n",
       "      <td>12.282497</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>77 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    community_name  2020_pop       vac_hu        hu_tot      vacant   vacperc  \\\n",
       "0      Albany Park     48396  1448.000000  18230.000000    5.322983  0.004331   \n",
       "1   Archer Heights     14196   406.000000   4538.000000   29.231599  0.022770   \n",
       "2    Armour Square     13890   393.513552   5860.348806   15.696404  0.024625   \n",
       "3          Ashburn     41098   540.000000  13479.000000   59.468213  0.019123   \n",
       "4   Auburn Gresham     44878  3364.000000  20617.000000  119.122147  0.049388   \n",
       "..             ...       ...          ...           ...         ...       ...   \n",
       "72       West Lawn     33662   525.000000   9701.000000   46.394184  0.024551   \n",
       "73    West Pullman     26104  2147.000000  11210.000000  221.071452  0.096914   \n",
       "74      West Ridge     77122  2185.000000  28054.000000   11.765845  0.005207   \n",
       "75       West Town     87781  3177.000000  41891.000000   62.407906  0.021311   \n",
       "76        Woodlawn     24425  2436.000000  11960.000000  122.772101  0.092636   \n",
       "\n",
       "    community_caps       community  n_BAFCONP  n_POLICE  ...  n_unknown  \\\n",
       "0      ALBANY PARK     ALBANY PARK          0         0  ...        0.0   \n",
       "1   ARCHER HEIGHTS  ARCHER HEIGHTS          0         0  ...        0.0   \n",
       "2    ARMOUR SQUARE   ARMOUR SQUARE          0         0  ...        0.0   \n",
       "3          ASHBURN         ASHBURN          0         0  ...        0.0   \n",
       "4   AUBURN GRESHAM  AUBURN GRESHAM          0         0  ...        0.0   \n",
       "..             ...             ...        ...       ...  ...        ...   \n",
       "72       WEST LAWN       WEST LAWN          0         0  ...        0.0   \n",
       "73    WEST PULLMAN    WEST PULLMAN          0         1  ...        0.0   \n",
       "74      WEST RIDGE      WEST RIDGE          0         0  ...        0.0   \n",
       "75       WEST TOWN       WEST TOWN          0         0  ...        0.0   \n",
       "76        WOODLAWN        WOODLAWN          2         2  ...        0.0   \n",
       "\n",
       "    n_dockets  n_311_complaints     dp10k  streets_p10k  cdot_p10k  \\\n",
       "0        15.0               400  0.774857      0.000000   0.774857   \n",
       "1        14.0                60  2.465483      0.000000   2.465483   \n",
       "2        26.0                77  4.679626      0.000000   4.679626   \n",
       "3         2.0               108  0.121660      0.000000   0.121660   \n",
       "4        19.0               128  1.058425      0.167120   0.891305   \n",
       "..        ...               ...       ...           ...        ...   \n",
       "72       17.0               146  1.262551      0.148535   1.114016   \n",
       "73        5.0                23  0.478854      0.095771   0.287312   \n",
       "74       24.0               804  0.777988      0.226913   0.551075   \n",
       "75       97.0              1609  2.762557      0.028480   2.734077   \n",
       "76        4.0               120  0.409417      0.000000   0.000000   \n",
       "\n",
       "    police_p10k    n_p10k   311_p10k  complaint_ratio  \n",
       "0      0.000000  0.000000  20.662865         0.037500  \n",
       "1      0.000000  0.000000  10.566357         0.233333  \n",
       "2      0.000000  0.000000  13.858891         0.337662  \n",
       "3      0.000000  0.000000   6.569663         0.018519  \n",
       "4      0.000000  0.000000   7.130443         0.125000  \n",
       "..          ...       ...        ...              ...  \n",
       "72     0.000000  0.000000  10.843087         0.102740  \n",
       "73     0.095771  0.000000   2.202728         0.130435  \n",
       "74     0.000000  0.000000  26.062602         0.021144  \n",
       "75     0.000000  0.000000  45.824267         0.059664  \n",
       "76     0.204708  0.204708  12.282497         0.000000  \n",
       "\n",
       "[77 rows x 22 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19845025",
   "metadata": {},
   "source": [
    "### verify totals\n",
    "compare these totals with ward-level summaries\n",
    "\n",
    "* n_BAFCONP                                                         2\n",
    "* n_POLICE                                                         25\n",
    "* n_STRTSAN                                                       495\n",
    "* n_TRANPORT                                                     1359\n",
    "* n_dockets                                                      1881\n",
    "* n_311_complaints                                                21079"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "9b316af7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community['n_BAFCONP'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f8c587bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community['n_POLICE'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "50bd70cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "497"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community['n_STRTSAN'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "97fa0e0b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1388"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community['n_TRANPORT'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "98e2c0a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1913.0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community['n_dockets'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ba07887b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21079"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community['n_311_complaints'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "669a8f16",
   "metadata": {},
   "source": [
    "# Export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "672d4178",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_community.to_csv(\"../../results/ssw02-fines/community-summary.csv\", index= False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34888c7f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}