notebooks/02-analysis/plow-01a-summarize-311-complaints.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "638444be",
   "metadata": {},
   "source": [
    "<h1>Snow-Related 311 Complaints in Chicago, 2019-2023</h1>\n",
    "11 January 2024\n",
    "\n",
    "This analysis looks at all four types of snow-related 311 complaints:\n",
    "<table>\n",
    "    <tr><td><strong>SR_SHORT_CODE</strong></td><td><strong>Complaint Description</strong></td><td><strong>Responsible Department</strong></td></tr>\n",
    "    <tr><td>SWSNOREM</td><td>Snow – Uncleared Sidewalk Complaint</td><td>Streets and Sanitation</td></tr>\n",
    "    <tr><td>SDO</td><td>Ice and Snow Removal Request</td><td>Streets and Sanitation</td></tr>\n",
    "    <tr><td>SDW</td><td>Object/Dibs Removal Request</td><td>CDOT</td></tr>\n",
    "    <tr><td>SNPBLBS</td><td>Snow Removal - Protected Bike Lane or Bridge Sidewalk</td><td>CDOT</td></tr> \n",
    "</table>\n",
    "\n",
    "My analysis steps:\n",
    "<ol>\n",
    "<li><a href=\"#read\">Read Data</a>\n",
    "<li><a href=\"#tabulate\">Tabulate by Community Area</a>\n",
    "<li><a href=\"#summarize\">Summarize Results</a>\n",
    "</ol>\n",
    "\n",
    "<br>\n",
    "Reminder that snow \"seasons\" run from July 1 to June 30 of the following year. For example, in my processed data set the season '2020-2021' refers to July 1, 2020 to June 30, 2021. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8be207b6",
   "metadata": {},
   "source": [
    "<h3>Preliminary Findings</h3>\n",
    "<ul>\n",
    "    <li>The five neighborhoods with the most 311 complaints per capita are (in order) Lincoln Square, Logan Square, Uptown, West Town, Lincoln Park\n",
    "        <li>The five neighborhoods with the least 311 complaints per capita are (in order) West Pullman, Riverdale, Mount Greenwood, Pullman, Hegewisch. These all seem to be car-centric neighborhoods.\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2aa78ef7",
   "metadata": {},
   "source": [
    "<a name=\"read\"></a>\n",
    "# 1. Read Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "109b46e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5b8b3ad",
   "metadata": {},
   "source": [
    "### Read snow violations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "38124df9",
   "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_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SR21-00149474</td>\n",
       "      <td>SDO</td>\n",
       "      <td>2021-01-27 13:13:50</td>\n",
       "      <td>1700 W 15TH ST</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Mobile Device</td>\n",
       "      <td>2021-01-27 19:50:47</td>\n",
       "      <td>41.861457</td>\n",
       "      <td>-87.668881</td>\n",
       "      <td>Ice and Snow Removal Request</td>\n",
       "      <td>2021</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-27</td>\n",
       "      <td>2020-2021</td>\n",
       "      <td>28</td>\n",
       "      <td>Near West Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SR21-00177207</td>\n",
       "      <td>SDO</td>\n",
       "      <td>2021-02-01 10:19:34</td>\n",
       "      <td>1300 S HEATH AVE</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Mobile Device</td>\n",
       "      <td>2021-02-01 15:12:41</td>\n",
       "      <td>41.864744</td>\n",
       "      <td>-87.684402</td>\n",
       "      <td>Ice and Snow Removal Request</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>2020-2021</td>\n",
       "      <td>28</td>\n",
       "      <td>Near West Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SR21-00179217</td>\n",
       "      <td>SDO</td>\n",
       "      <td>2021-02-01 13:17:16</td>\n",
       "      <td>3242 W FULTON BLVD</td>\n",
       "      <td>27</td>\n",
       "      <td>28</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Mobile Device</td>\n",
       "      <td>2021-02-01 21:10:54</td>\n",
       "      <td>41.886675</td>\n",
       "      <td>-87.707985</td>\n",
       "      <td>Ice and Snow Removal Request</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>2020-2021</td>\n",
       "      <td>27</td>\n",
       "      <td>East Garfield Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SR21-00269268</td>\n",
       "      <td>SDO</td>\n",
       "      <td>2021-02-17 12:43:47</td>\n",
       "      <td>819 S BISHOP ST</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Mobile Device</td>\n",
       "      <td>2021-02-19 00:58:56</td>\n",
       "      <td>41.871081</td>\n",
       "      <td>-87.662624</td>\n",
       "      <td>Ice and Snow Removal Request</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-02-17</td>\n",
       "      <td>2020-2021</td>\n",
       "      <td>28</td>\n",
       "      <td>Near West Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SR20-05442947</td>\n",
       "      <td>SDW</td>\n",
       "      <td>2020-11-11 19:40:31</td>\n",
       "      <td>3401 W 53RD ST</td>\n",
       "      <td>63</td>\n",
       "      <td>14</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Mobile Device</td>\n",
       "      <td>2020-11-16 06:50:26</td>\n",
       "      <td>41.796901</td>\n",
       "      <td>-87.708733</td>\n",
       "      <td>Snow - Object/Dibs Removal Request</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>2020-2021</td>\n",
       "      <td>63</td>\n",
       "      <td>Gage Park</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       SR_NUMBER SR_SHORT_CODE         CREATED_DATE      STREET_ADDRESS  \\\n",
       "0  SR21-00149474           SDO  2021-01-27 13:13:50      1700 W 15TH ST   \n",
       "1  SR21-00177207           SDO  2021-02-01 10:19:34    1300 S HEATH AVE   \n",
       "2  SR21-00179217           SDO  2021-02-01 13:17:16  3242 W FULTON BLVD   \n",
       "3  SR21-00269268           SDO  2021-02-17 12:43:47     819 S BISHOP ST   \n",
       "4  SR20-05442947           SDW  2020-11-11 19:40:31      3401 W 53RD ST   \n",
       "\n",
       "   COMMUNITY_AREA  WARD     STATUS         ORIGIN          CLOSED_DATE  \\\n",
       "0              28    28  Completed  Mobile Device  2021-01-27 19:50:47   \n",
       "1              28    28  Completed  Mobile Device  2021-02-01 15:12:41   \n",
       "2              27    28  Completed  Mobile Device  2021-02-01 21:10:54   \n",
       "3              28    28  Completed  Mobile Device  2021-02-19 00:58:56   \n",
       "4              63    14  Completed  Mobile Device  2020-11-16 06:50:26   \n",
       "\n",
       "    LATITUDE  LONGITUDE                             SR_TYPE  year  month  \\\n",
       "0  41.861457 -87.668881        Ice and Snow Removal Request  2021      1   \n",
       "1  41.864744 -87.684402        Ice and Snow Removal Request  2021      2   \n",
       "2  41.886675 -87.707985        Ice and Snow Removal Request  2021      2   \n",
       "3  41.871081 -87.662624        Ice and Snow Removal Request  2021      2   \n",
       "4  41.796901 -87.708733  Snow - Object/Dibs Removal Request  2020     11   \n",
       "\n",
       "         date     season  GEOID      community_name  \n",
       "0  2021-01-27  2020-2021     28      Near West Side  \n",
       "1  2021-02-01  2020-2021     28      Near West Side  \n",
       "2  2021-02-01  2020-2021     27  East Garfield Park  \n",
       "3  2021-02-17  2020-2021     28      Near West Side  \n",
       "4  2020-11-11  2020-2021     63           Gage Park  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"../../data/05-finalized/311-complaints-snow-all-types.csv\")\n",
    "df.rename(columns={'COMMUNITY_NAME':'community_name'},inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0d012c65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "67361"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4941624",
   "metadata": {},
   "source": [
    "### read community population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bc063688",
   "metadata": {},
   "outputs": [],
   "source": [
    "# retrieved on 1/11/24, but 2020 Census Population figures should be static\n",
    "\n",
    "#df_population = pd.read_csv(\"../../data/00-raw/population_cmap_2022.csv\")\n",
    "df_population = pd.read_csv(\"../../data/05-finalized/census-by-community.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fea9c0a2",
   "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",
       "    <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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>77 rows × 7 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  \n",
       "0      ALBANY PARK  \n",
       "1   ARCHER HEIGHTS  \n",
       "2    ARMOUR SQUARE  \n",
       "3          ASHBURN  \n",
       "4   AUBURN GRESHAM  \n",
       "..             ...  \n",
       "72       WEST LAWN  \n",
       "73    WEST PULLMAN  \n",
       "74      WEST RIDGE  \n",
       "75       WEST TOWN  \n",
       "76        WOODLAWN  \n",
       "\n",
       "[77 rows x 7 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6d626325",
   "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>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>ALBANY PARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Archer Heights</td>\n",
       "      <td>14196</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>ARMOUR SQUARE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ashburn</td>\n",
       "      <td>41098</td>\n",
       "      <td>ASHBURN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Auburn Gresham</td>\n",
       "      <td>44878</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community_name  2020_pop  community_caps\n",
       "0     Albany Park     48396     ALBANY PARK\n",
       "1  Archer Heights     14196  ARCHER HEIGHTS\n",
       "2   Armour Square     13890   ARMOUR SQUARE\n",
       "3         Ashburn     41098         ASHBURN\n",
       "4  Auburn Gresham     44878  AUBURN GRESHAM"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# simplify dataframe to get only essentials. COMMUNITY_CAPS is useful for linking with community areas boundary shapefiles \n",
    "df_population = df_population[['community_name','2020_pop','community_caps']]\n",
    "df_population.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c35f82a0",
   "metadata": {},
   "source": [
    "<a name=\"tabulate\"></a>\n",
    "# 2. Tabulate All 311 Complaints by Community Area, 2019-2023\n",
    "This exploratory data analysis looked at all four types of snow-related complaints. Skip to the next section to see the \"uncleared sidewalk\" complaints, which were the focus of the Plow the Sidewalks article for South Side Weekly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9a251603",
   "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>SR_TYPE</th>\n",
       "      <th>Ice and Snow Removal Request</th>\n",
       "      <th>Snow - Object/Dibs Removal Request</th>\n",
       "      <th>Snow Removal - Protected Bike Lane or Bridge Sidewalk</th>\n",
       "      <th>Snow – Uncleared Sidewalk Complaint</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>community_name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albany Park</th>\n",
       "      <td>540</td>\n",
       "      <td>195</td>\n",
       "      <td>16</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Archer Heights</th>\n",
       "      <td>205</td>\n",
       "      <td>253</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Armour Square</th>\n",
       "      <td>65</td>\n",
       "      <td>52</td>\n",
       "      <td>16</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ashburn</th>\n",
       "      <td>1506</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Auburn Gresham</th>\n",
       "      <td>1449</td>\n",
       "      <td>179</td>\n",
       "      <td>1</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "SR_TYPE         Ice and Snow Removal Request  \\\n",
       "community_name                                 \n",
       "Albany Park                              540   \n",
       "Archer Heights                           205   \n",
       "Armour Square                             65   \n",
       "Ashburn                                 1506   \n",
       "Auburn Gresham                          1449   \n",
       "\n",
       "SR_TYPE         Snow - Object/Dibs Removal Request  \\\n",
       "community_name                                       \n",
       "Albany Park                                    195   \n",
       "Archer Heights                                 253   \n",
       "Armour Square                                   52   \n",
       "Ashburn                                         75   \n",
       "Auburn Gresham                                 179   \n",
       "\n",
       "SR_TYPE         Snow Removal - Protected Bike Lane or Bridge Sidewalk  \\\n",
       "community_name                                                          \n",
       "Albany Park                                                    16       \n",
       "Archer Heights                                                  0       \n",
       "Armour Square                                                  16       \n",
       "Ashburn                                                         1       \n",
       "Auburn Gresham                                                  1       \n",
       "\n",
       "SR_TYPE         Snow – Uncleared Sidewalk Complaint  \n",
       "community_name                                       \n",
       "Albany Park                                     400  \n",
       "Archer Heights                                   60  \n",
       "Armour Square                                    77  \n",
       "Ashburn                                         108  \n",
       "Auburn Gresham                                  128  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community_by_type = df.pivot_table(index='community_name', columns='SR_TYPE', values='SR_NUMBER', aggfunc='size', fill_value=0)\n",
    "df_community_by_type.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67f770af",
   "metadata": {},
   "source": [
    "### merge 311 complaints data with community data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2247d985",
   "metadata": {
    "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>community_name</th>\n",
       "      <th>Ice and Snow Removal Request</th>\n",
       "      <th>Snow - Object/Dibs Removal Request</th>\n",
       "      <th>Snow Removal - Protected Bike Lane or Bridge Sidewalk</th>\n",
       "      <th>Snow – Uncleared Sidewalk Complaint</th>\n",
       "      <th>2020_pop</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>540</td>\n",
       "      <td>195</td>\n",
       "      <td>16</td>\n",
       "      <td>400</td>\n",
       "      <td>48396</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Archer Heights</td>\n",
       "      <td>205</td>\n",
       "      <td>253</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "      <td>14196</td>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Armour Square</td>\n",
       "      <td>65</td>\n",
       "      <td>52</td>\n",
       "      <td>16</td>\n",
       "      <td>77</td>\n",
       "      <td>13890</td>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ashburn</td>\n",
       "      <td>1506</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>108</td>\n",
       "      <td>41098</td>\n",
       "      <td>ASHBURN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Auburn Gresham</td>\n",
       "      <td>1449</td>\n",
       "      <td>179</td>\n",
       "      <td>1</td>\n",
       "      <td>128</td>\n",
       "      <td>44878</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community_name  Ice and Snow Removal Request  \\\n",
       "0     Albany Park                           540   \n",
       "1  Archer Heights                           205   \n",
       "2   Armour Square                            65   \n",
       "3         Ashburn                          1506   \n",
       "4  Auburn Gresham                          1449   \n",
       "\n",
       "   Snow - Object/Dibs Removal Request  \\\n",
       "0                                 195   \n",
       "1                                 253   \n",
       "2                                  52   \n",
       "3                                  75   \n",
       "4                                 179   \n",
       "\n",
       "   Snow Removal - Protected Bike Lane or Bridge Sidewalk  \\\n",
       "0                                                 16       \n",
       "1                                                  0       \n",
       "2                                                 16       \n",
       "3                                                  1       \n",
       "4                                                  1       \n",
       "\n",
       "   Snow – Uncleared Sidewalk Complaint  2020_pop  community_caps  \n",
       "0                                  400     48396     ALBANY PARK  \n",
       "1                                   60     14196  ARCHER HEIGHTS  \n",
       "2                                   77     13890   ARMOUR SQUARE  \n",
       "3                                  108     41098         ASHBURN  \n",
       "4                                  128     44878  AUBURN GRESHAM  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community_summary = pd.merge(left=df_community_by_type,right=df_population,on='community_name')\n",
    "df_community_summary.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b6332de",
   "metadata": {},
   "source": [
    "### calculate per capita rates of 311 complaints by community"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "71359313",
   "metadata": {},
   "outputs": [],
   "source": [
    "# per 10,000 capita, per year over 4 years\n",
    "df_community_summary['Streets Per 10k'] = \\\n",
    "(10000/4)*df_community_summary['Ice and Snow Removal Request']/df_community_summary['2020_pop']\n",
    "\n",
    "df_community_summary['Dibs Per 10k'] = \\\n",
    "(10000/4)*df_community_summary['Snow - Object/Dibs Removal Request']/df_community_summary['2020_pop']\n",
    "\n",
    "df_community_summary['Sidewalks Per 10k'] = \\\n",
    "(10000/4)*df_community_summary['Snow – Uncleared Sidewalk Complaint']/df_community_summary['2020_pop']\n",
    "\n",
    "df_community_summary['Bike-Bridge Per 10k'] = \\\n",
    "(10000/4)*df_community_summary['Snow Removal - Protected Bike Lane or Bridge Sidewalk']/df_community_summary['2020_pop']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "bcff5366",
   "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>Ice and Snow Removal Request</th>\n",
       "      <th>Snow - Object/Dibs Removal Request</th>\n",
       "      <th>Snow Removal - Protected Bike Lane or Bridge Sidewalk</th>\n",
       "      <th>Snow – Uncleared Sidewalk Complaint</th>\n",
       "      <th>2020_pop</th>\n",
       "      <th>community_caps</th>\n",
       "      <th>Streets Per 10k</th>\n",
       "      <th>Dibs Per 10k</th>\n",
       "      <th>Sidewalks Per 10k</th>\n",
       "      <th>Bike-Bridge Per 10k</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albany Park</td>\n",
       "      <td>540</td>\n",
       "      <td>195</td>\n",
       "      <td>16</td>\n",
       "      <td>400</td>\n",
       "      <td>48396</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>27.894867</td>\n",
       "      <td>10.073147</td>\n",
       "      <td>20.662865</td>\n",
       "      <td>0.826515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Archer Heights</td>\n",
       "      <td>205</td>\n",
       "      <td>253</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "      <td>14196</td>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "      <td>36.101719</td>\n",
       "      <td>44.554804</td>\n",
       "      <td>10.566357</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Armour Square</td>\n",
       "      <td>65</td>\n",
       "      <td>52</td>\n",
       "      <td>16</td>\n",
       "      <td>77</td>\n",
       "      <td>13890</td>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "      <td>11.699064</td>\n",
       "      <td>9.359251</td>\n",
       "      <td>13.858891</td>\n",
       "      <td>2.879770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ashburn</td>\n",
       "      <td>1506</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>108</td>\n",
       "      <td>41098</td>\n",
       "      <td>ASHBURN</td>\n",
       "      <td>91.610297</td>\n",
       "      <td>4.562266</td>\n",
       "      <td>6.569663</td>\n",
       "      <td>0.060830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Auburn Gresham</td>\n",
       "      <td>1449</td>\n",
       "      <td>179</td>\n",
       "      <td>1</td>\n",
       "      <td>128</td>\n",
       "      <td>44878</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>80.718838</td>\n",
       "      <td>9.971478</td>\n",
       "      <td>7.130443</td>\n",
       "      <td>0.055707</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community_name  Ice and Snow Removal Request  \\\n",
       "0     Albany Park                           540   \n",
       "1  Archer Heights                           205   \n",
       "2   Armour Square                            65   \n",
       "3         Ashburn                          1506   \n",
       "4  Auburn Gresham                          1449   \n",
       "\n",
       "   Snow - Object/Dibs Removal Request  \\\n",
       "0                                 195   \n",
       "1                                 253   \n",
       "2                                  52   \n",
       "3                                  75   \n",
       "4                                 179   \n",
       "\n",
       "   Snow Removal - Protected Bike Lane or Bridge Sidewalk  \\\n",
       "0                                                 16       \n",
       "1                                                  0       \n",
       "2                                                 16       \n",
       "3                                                  1       \n",
       "4                                                  1       \n",
       "\n",
       "   Snow – Uncleared Sidewalk Complaint  2020_pop  community_caps  \\\n",
       "0                                  400     48396     ALBANY PARK   \n",
       "1                                   60     14196  ARCHER HEIGHTS   \n",
       "2                                   77     13890   ARMOUR SQUARE   \n",
       "3                                  108     41098         ASHBURN   \n",
       "4                                  128     44878  AUBURN GRESHAM   \n",
       "\n",
       "   Streets Per 10k  Dibs Per 10k  Sidewalks Per 10k  Bike-Bridge Per 10k  \n",
       "0        27.894867     10.073147          20.662865             0.826515  \n",
       "1        36.101719     44.554804          10.566357             0.000000  \n",
       "2        11.699064      9.359251          13.858891             2.879770  \n",
       "3        91.610297      4.562266           6.569663             0.060830  \n",
       "4        80.718838      9.971478           7.130443             0.055707  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community_summary.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf28c695",
   "metadata": {},
   "source": [
    "<a name=\"#summarize\"></a>\n",
    "# 3. Summarize Stats for 311 Uncleared Sidewalk Complaints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5d22b934",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21079"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filter to look at only uncleared sidewalk complaints\n",
    "df_uncleared = df[(df['SR_SHORT_CODE']=='SWSNOREM')]\n",
    "len(df_uncleared)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1431b55",
   "metadata": {},
   "source": [
    "### uncleared complaints by season"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "904dce4a",
   "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>season</th>\n",
       "      <th>complaints</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-2020</td>\n",
       "      <td>6541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-2021</td>\n",
       "      <td>6494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-2022</td>\n",
       "      <td>6416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-2023</td>\n",
       "      <td>1628</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      season  complaints\n",
       "0  2019-2020        6541\n",
       "1  2020-2021        6494\n",
       "2  2021-2022        6416\n",
       "3  2022-2023        1628"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_by_season = df_uncleared.groupby('season').agg(complaints=('SR_NUMBER','count')).reset_index()\n",
    "df_by_season"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39d64698",
   "metadata": {},
   "source": [
    "### uncleared complaints by complaint origin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "53fa1f4f",
   "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>ORIGIN</th>\n",
       "      <th>complaints</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alderman's Office</td>\n",
       "      <td>724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E-Mail</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Generated In House</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Internet</td>\n",
       "      <td>7068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mobile Device</td>\n",
       "      <td>8260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Open311 Interface</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Phone Call</td>\n",
       "      <td>4818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Salesforce Mobile App</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>spot-open311-Chicago+Works</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>spot-open311-SeeClickFix</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       ORIGIN  complaints\n",
       "0           Alderman's Office         724\n",
       "1                      E-Mail          22\n",
       "2          Generated In House           1\n",
       "3                    Internet        7068\n",
       "4               Mobile Device        8260\n",
       "5           Open311 Interface           1\n",
       "6                  Phone Call        4818\n",
       "7       Salesforce Mobile App          36\n",
       "8  spot-open311-Chicago+Works          97\n",
       "9    spot-open311-SeeClickFix          52"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_by_type = df_uncleared.groupby('ORIGIN').agg(complaints=('SR_NUMBER','count')).reset_index()\n",
    "df_by_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "75d158b5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_1627/401749550.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_uncleared['ORIGIN_binned'] = df_uncleared['ORIGIN'].replace(['E-Mail', 'Generated In House', 'Open311 Interface','Salesforce Mobile App','spot-open311-Chicago+Works','spot-open311-SeeClickFix'], 'Other')\n"
     ]
    }
   ],
   "source": [
    "# consolidate other\n",
    "df_uncleared['ORIGIN_binned'] = df_uncleared['ORIGIN'].replace(['E-Mail', 'Generated In House', 'Open311 Interface','Salesforce Mobile App','spot-open311-Chicago+Works','spot-open311-SeeClickFix'], 'Other')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "860b4823",
   "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>ORIGIN_binned</th>\n",
       "      <th>complaints</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alderman's Office</td>\n",
       "      <td>724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Internet</td>\n",
       "      <td>7068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mobile Device</td>\n",
       "      <td>8260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Other</td>\n",
       "      <td>209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Phone Call</td>\n",
       "      <td>4818</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ORIGIN_binned  complaints\n",
       "0  Alderman's Office         724\n",
       "1           Internet        7068\n",
       "2      Mobile Device        8260\n",
       "3              Other         209\n",
       "4         Phone Call        4818"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_by_type = df_uncleared.groupby('ORIGIN_binned').agg(complaints=('SR_NUMBER','count')).reset_index()\n",
    "df_by_type"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ff3ca89",
   "metadata": {},
   "source": [
    "### uncleared complaints by status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f5393911",
   "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>STATUS</th>\n",
       "      <th>SR_NUMBER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Canceled</td>\n",
       "      <td>978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Completed</td>\n",
       "      <td>20041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Open</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      STATUS  SR_NUMBER\n",
       "0   Canceled        978\n",
       "1  Completed      20041\n",
       "2       Open         60"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_by_status = df_uncleared.groupby('STATUS')['SR_NUMBER'].agg('count').reset_index()\n",
    "df_by_status"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65477eed",
   "metadata": {},
   "source": [
    "### uncleared complaints per capita by community"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "81975462",
   "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>Sidewalks Per 10k</th>\n",
       "      <th>Snow – Uncleared Sidewalk Complaint</th>\n",
       "      <th>2020_pop</th>\n",
       "      <th>community_caps</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Lincoln Square</td>\n",
       "      <td>57.354176</td>\n",
       "      <td>929</td>\n",
       "      <td>40494</td>\n",
       "      <td>LINCOLN SQUARE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Logan Square</td>\n",
       "      <td>55.361753</td>\n",
       "      <td>1587</td>\n",
       "      <td>71665</td>\n",
       "      <td>LOGAN SQUARE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Uptown</td>\n",
       "      <td>46.124655</td>\n",
       "      <td>1055</td>\n",
       "      <td>57182</td>\n",
       "      <td>UPTOWN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>West Town</td>\n",
       "      <td>45.824267</td>\n",
       "      <td>1609</td>\n",
       "      <td>87781</td>\n",
       "      <td>WEST TOWN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Lincoln Park</td>\n",
       "      <td>42.628951</td>\n",
       "      <td>1202</td>\n",
       "      <td>70492</td>\n",
       "      <td>LINCOLN PARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Lake View</td>\n",
       "      <td>40.393013</td>\n",
       "      <td>1665</td>\n",
       "      <td>103050</td>\n",
       "      <td>LAKE VIEW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>North Center</td>\n",
       "      <td>37.235860</td>\n",
       "      <td>523</td>\n",
       "      <td>35114</td>\n",
       "      <td>NORTH CENTER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Irving Park</td>\n",
       "      <td>36.195610</td>\n",
       "      <td>752</td>\n",
       "      <td>51940</td>\n",
       "      <td>IRVING PARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Avondale</td>\n",
       "      <td>35.096671</td>\n",
       "      <td>509</td>\n",
       "      <td>36257</td>\n",
       "      <td>AVONDALE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Edgewater</td>\n",
       "      <td>26.511653</td>\n",
       "      <td>597</td>\n",
       "      <td>56296</td>\n",
       "      <td>EDGEWATER</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    community_name  Sidewalks Per 10k  Snow – Uncleared Sidewalk Complaint  \\\n",
       "39  Lincoln Square          57.354176                                  929   \n",
       "40    Logan Square          55.361753                                 1587   \n",
       "66          Uptown          46.124655                                 1055   \n",
       "75       West Town          45.824267                                 1609   \n",
       "38    Lincoln Park          42.628951                                 1202   \n",
       "37       Lake View          40.393013                                 1665   \n",
       "51    North Center          37.235860                                  523   \n",
       "34     Irving Park          36.195610                                  752   \n",
       "7         Avondale          35.096671                                  509   \n",
       "21       Edgewater          26.511653                                  597   \n",
       "\n",
       "    2020_pop  community_caps  \n",
       "39     40494  LINCOLN SQUARE  \n",
       "40     71665    LOGAN SQUARE  \n",
       "66     57182          UPTOWN  \n",
       "75     87781       WEST TOWN  \n",
       "38     70492    LINCOLN PARK  \n",
       "37    103050       LAKE VIEW  \n",
       "51     35114    NORTH CENTER  \n",
       "34     51940     IRVING PARK  \n",
       "7      36257        AVONDALE  \n",
       "21     56296       EDGEWATER  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_uncleared_by_community = df_community_summary[['community_name','Sidewalks Per 10k','Snow – Uncleared Sidewalk Complaint','2020_pop','community_caps']].sort_values(by='Sidewalks Per 10k', ascending=False)\n",
    "df_uncleared_by_community.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "521e9f73",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21079"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get sum of uncleared sidewalks\n",
    "df_uncleared_by_community['Snow – Uncleared Sidewalk Complaint'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d17c836a",
   "metadata": {},
   "source": [
    "# Export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "d7db74a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_uncleared_by_community.to_csv(\"../../results/ssw01-plow/311_uncleared_by_community.csv\", index=False)"
   ]
  }
 ],
 "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
}