notebooks/02-analysis/plow-01b-summarize-fines-by-community.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "638444be",
   "metadata": {},
   "source": [
    "<h1>Snow Clearance Fines, 2019-2023</h1>\n",
    "31 January 2024\n",
    "updated 3/6/24\n",
    "\n",
    "This analysis looks at administrative hearing dockets for uncleared sidewalks, based on FOIA data requested from the Department of Administrative Hearings (H064920-011124.xlsx).\n",
    "\n",
    "Calculations of total fines are commented out for now. I rebuilt the data pipeline in March 2024 to support part II of the investigation, and need to revise the calculation of total fines.\n",
    "\n",
    "<br>\n",
    "My analysis steps:\n",
    "<ol>\n",
    "<li><a href=\"#read\">Read Data</a>\n",
    "    <li><a href=\"#summarize\">Summarize Citywide</a>- by issuing department, by year, by community area\n",
    "        <li><a href=\"#community\">Summarize by Community</a>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a6cce54",
   "metadata": {},
   "source": [
    "### Preliminary Findings\n",
    "<ul>\n",
    "    <li>Englewood, Garfield Ridge, and West Englewood are the three communities with the highest number of dockets per capita\n",
    "    <li>17 of 25 police-related dockets related to snow clearance were in Englewood, West Englewood, Garfield Ridge, and Belmont Cragin\n",
    "    </ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "488cdc75",
   "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\n",
    "# import requests\n",
    "import numpy as np\n",
    "import altair as alt\n",
    "#import datetime as dt #would only need this if I manipulated dates post-API data retrieval"
   ]
  },
  {
   "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>docket</th>\n",
       "      <th>dept</th>\n",
       "      <th>violation_address</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>community_caps</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_caps 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 = pd.read_csv(\"../../data/05-finalized/dockets-summary.csv\")\n",
    "df.rename(columns={'community':'community_caps'},inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0d012c65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1918"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3948f53",
   "metadata": {},
   "source": [
    "<a name=\"summarize\"></a>\n",
    "# 2. Summarize Citywide"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c741ecf8",
   "metadata": {},
   "source": [
    "### by season"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3fc0049e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_season(month, year):\n",
    "# Input:\n",
    "#   month: numeric month of the year (1-12)\n",
    "#   year (int): 4 digit year\n",
    "# Returns:\n",
    "#   season in yyyy-yyyy format. E.g. 2022-2023 means winter 2022-2023, or July 2022 to June 2023.\n",
    "\n",
    "    if month >= 7:\n",
    "        return f\"{str(year)[-4:]}-{str(year + 1)[-4:]}\"\n",
    "    else:\n",
    "        return f\"{str(year - 1)[-4:]}-{str(year)[-4:]}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "aebc455b",
   "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_caps</th>\n",
       "      <th>ward_1523</th>\n",
       "      <th>violation_date</th>\n",
       "      <th>n_records</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>date</th>\n",
       "      <th>season</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",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>2019-11-13</td>\n",
       "      <td>2019-2020</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",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>2019-11-13</td>\n",
       "      <td>2019-2020</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",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>2019-11-13</td>\n",
       "      <td>2019-2020</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",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>2019-11-12</td>\n",
       "      <td>2019-2020</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",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>2019-11-14</td>\n",
       "      <td>2019-2020</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_caps ward_1523 violation_date  n_records  year  month  \\\n",
       "0       BRIGHTON PARK        15     2019-11-13          2  2019     11   \n",
       "1         ROGERS PARK        49     2019-11-13          1  2019     11   \n",
       "2     GRAND BOULEVARD         4     2019-11-13          1  2019     11   \n",
       "3  WEST GARFIELD PARK        28     2019-11-12          5  2019     11   \n",
       "4              UPTOWN        46     2019-11-14          1  2019     11   \n",
       "\n",
       "         date     season  \n",
       "0  2019-11-13  2019-2020  \n",
       "1  2019-11-13  2019-2020  \n",
       "2  2019-11-13  2019-2020  \n",
       "3  2019-11-12  2019-2020  \n",
       "4  2019-11-14  2019-2020  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# format and parse dates\n",
    "df['violation_date'] = pd.to_datetime(df['violation_date'])\n",
    "df['year'] = df['violation_date'].dt.year\n",
    "df['month'] = df['violation_date'].dt.month\n",
    "df['date'] = df['violation_date'].dt.date\n",
    "df['season'] = df.apply(lambda row: get_season(row['month'], row['year']), axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "78bde3ae",
   "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>n_dockets</th>\n",
       "      <th>n_records</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-2020</td>\n",
       "      <td>357</td>\n",
       "      <td>357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-2021</td>\n",
       "      <td>764</td>\n",
       "      <td>764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-2022</td>\n",
       "      <td>700</td>\n",
       "      <td>700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-2023</td>\n",
       "      <td>97</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      season  n_dockets  n_records\n",
       "0  2019-2020        357        357\n",
       "1  2020-2021        764        764\n",
       "2  2021-2022        700        700\n",
       "3  2022-2023         97         97"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# by season\n",
    "df.groupby('season').agg(\n",
    "#     sum_fine_amt=('Imposed Fine Detailed', 'sum'),\n",
    "#     max_fine=('Imposed Fine Detailed', 'max'),\n",
    "    n_dockets=('docket','nunique'),\n",
    "    n_records=('docket','count')\n",
    ").reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8be764b",
   "metadata": {},
   "source": [
    "### by issuing department"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "493acf8d",
   "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>dept</th>\n",
       "      <th>n_dockets</th>\n",
       "      <th>n_records</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BAFCONP</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>POLICE</td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>STRTSAN</td>\n",
       "      <td>497</td>\n",
       "      <td>497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TRANPORT</td>\n",
       "      <td>1393</td>\n",
       "      <td>1393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>unknown</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       dept  n_dockets  n_records\n",
       "0   BAFCONP          2          2\n",
       "1    POLICE         25         25\n",
       "2   STRTSAN        497        497\n",
       "3  TRANPORT       1393       1393\n",
       "4   unknown          1          1"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# by issuing department\n",
    "df.groupby('dept').agg(\n",
    "#     sum_fine_amt=('Imposed Fine Detailed', 'sum'),\n",
    "    n_dockets=('docket','nunique'),\n",
    "    n_records=('docket','count')\n",
    ").reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fd52113",
   "metadata": {},
   "source": [
    "### by Address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a366c2b1",
   "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>violation_address</th>\n",
       "      <th>n_dockets</th>\n",
       "      <th>n_records</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>717</th>\n",
       "      <td>3110 W 61ST ST</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100  UNKNOWN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1427</th>\n",
       "      <td>627 W RANDOLPH ST</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1058</th>\n",
       "      <td>4710 S WESTERN AVE</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1722</th>\n",
       "      <td>932 W 59TH ST</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       violation_address  n_dockets  n_records\n",
       "717       3110 W 61ST ST          6          6\n",
       "1          100  UNKNOWN           4          4\n",
       "1427   627 W RANDOLPH ST          4          4\n",
       "1058  4710 S WESTERN AVE          4          4\n",
       "1722       932 W 59TH ST          4          4"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_by_address = df.groupby(['violation_address']).agg(\n",
    "#     sum_fine_amt=('Imposed Fine Detailed', 'sum'),\n",
    "    n_dockets=('docket','nunique'),\n",
    "    n_records=('docket','count')\n",
    ").reset_index()\n",
    "df_by_address[df_by_address['n_dockets']>=3].sort_values(\"n_dockets\",ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c3c14e0",
   "metadata": {},
   "source": [
    "### distribution of fines by year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a1138607",
   "metadata": {},
   "outputs": [],
   "source": [
    "# bins = [0, 1, 150, 500, 501, 5000]\n",
    "# labels = ['0', '1-150', '151-499', '500', '501-5000']\n",
    "\n",
    "# df['Binned Fine'] = pd.cut(df['Imposed Fine Detailed'], bins=bins, labels=labels, right=False, include_lowest=True)\n",
    "\n",
    "# pivot_table = df.pivot_table(\n",
    "#     values='Imposed Fine Detailed',  # We sum over the original fine amounts\n",
    "#     index='Binned Fine',  # Use the binned column as the new index\n",
    "#     columns='season',  # Using 'season' for the columns\n",
    "#     aggfunc='count',  # Summing up the fine amounts\n",
    "#     fill_value=0  # Replace NaN with 0\n",
    "# )\n",
    "\n",
    "# pivot_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2824ca2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# pivot_table_long = pivot_table.reset_index().melt(id_vars='Binned Fine', var_name='Season', value_name='Count')\n",
    "\n",
    "# # Create the histogram (bar chart) with Altair\n",
    "# chart = alt.Chart(pivot_table_long).mark_bar().encode(\n",
    "#     x=alt.X('Binned Fine:N', title='Fine Amount Bins', sort=labels),  # Ensure custom order\n",
    "#     y=alt.Y('sum(Count):Q', title='Count'),\n",
    "#     color='Season:N',\n",
    "#     column='Season:N'  # Separate charts for each season\n",
    "# ).properties(\n",
    "#     width=220,\n",
    "#     height=200\n",
    "# ).resolve_scale(\n",
    "#     y='independent'  # This allows each chart to have a separate Y-axis scale\n",
    "# ).properties(\n",
    "#     width=220,\n",
    "#     height=200\n",
    "# )\n",
    "\n",
    "# chart.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb3c733c",
   "metadata": {},
   "source": [
    "<a name=\"community\"></a>\n",
    "# 3. Summarize by Community"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0e93ea9",
   "metadata": {},
   "source": [
    "### by community"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "39691875",
   "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_dockets</th>\n",
       "      <th>n_records</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "      <td>26</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASHBURN</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community_caps  n_dockets  n_records\n",
       "0     ALBANY PARK         15         15\n",
       "1  ARCHER HEIGHTS         14         14\n",
       "2   ARMOUR SQUARE         26         26\n",
       "3         ASHBURN          2          2\n",
       "4  AUBURN GRESHAM         19         19"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community = df.groupby('community_caps').agg(\n",
    "#     sum_fine_amt=('Imposed Fine Detailed', 'sum'),\n",
    "    n_dockets=('docket','nunique'),\n",
    "    n_records=('docket','count')\n",
    ").reset_index()\n",
    "df_community.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "2f3e8c13",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "community_caps    object\n",
       "n_dockets          int64\n",
       "n_records          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4941624",
   "metadata": {},
   "source": [
    "### read community population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "bc063688",
   "metadata": {},
   "outputs": [],
   "source": [
    "# retrieved on 1/11/24, but 2020 Census Population figures should be static\n",
    "df_population = pd.read_csv(\"../../data/05-finalized/census-by-community.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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_caps</th>\n",
       "      <th>2020_pop</th>\n",
       "      <th>community_name</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_caps  2020_pop  community_name\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": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# simplify dataframe to get only essentials\n",
    "df_population = df_population[['community_caps','2020_pop','community_name']]\n",
    "df_population.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f52e7fab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "community_caps    object\n",
       "2020_pop           int64\n",
       "community_name    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_population.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67f770af",
   "metadata": {},
   "source": [
    "### merge in community population data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2247d985",
   "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_dockets</th>\n",
       "      <th>n_records</th>\n",
       "      <th>2020_pop</th>\n",
       "      <th>community_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>15</td>\n",
       "      <td>15</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>14</td>\n",
       "      <td>14</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>26</td>\n",
       "      <td>26</td>\n",
       "      <td>13890</td>\n",
       "      <td>Armour Square</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASHBURN</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>41098</td>\n",
       "      <td>Ashburn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>44878</td>\n",
       "      <td>Auburn Gresham</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community_caps  n_dockets  n_records  2020_pop  community_name\n",
       "0     ALBANY PARK         15         15     48396     Albany Park\n",
       "1  ARCHER HEIGHTS         14         14     14196  Archer Heights\n",
       "2   ARMOUR SQUARE         26         26     13890   Armour Square\n",
       "3         ASHBURN          2          2     41098         Ashburn\n",
       "4  AUBURN GRESHAM         19         19     44878  Auburn Gresham"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community_summary = pd.merge(df_community,df_population,on='community_caps')\n",
    "df_community_summary.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "71359313",
   "metadata": {},
   "outputs": [],
   "source": [
    "# per 10,000 capita, per year over 4 years\n",
    "df_community_summary['dockets per 10k'] = \\\n",
    "(10000/4)*df_community_summary['n_dockets']/df_community_summary['2020_pop']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c4d667bd",
   "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_dockets</th>\n",
       "      <th>n_records</th>\n",
       "      <th>2020_pop</th>\n",
       "      <th>community_name</th>\n",
       "      <th>dockets per 10k</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>48396</td>\n",
       "      <td>Albany Park</td>\n",
       "      <td>0.774857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ARCHER HEIGHTS</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14196</td>\n",
       "      <td>Archer Heights</td>\n",
       "      <td>2.465483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "      <td>26</td>\n",
       "      <td>26</td>\n",
       "      <td>13890</td>\n",
       "      <td>Armour Square</td>\n",
       "      <td>4.679626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASHBURN</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>41098</td>\n",
       "      <td>Ashburn</td>\n",
       "      <td>0.121660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>44878</td>\n",
       "      <td>Auburn Gresham</td>\n",
       "      <td>1.058425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>WEST LAWN</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>33662</td>\n",
       "      <td>West Lawn</td>\n",
       "      <td>1.262551</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>WEST PULLMAN</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>26104</td>\n",
       "      <td>West Pullman</td>\n",
       "      <td>0.478854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>WEST RIDGE</td>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "      <td>77122</td>\n",
       "      <td>West Ridge</td>\n",
       "      <td>0.777988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>WEST TOWN</td>\n",
       "      <td>97</td>\n",
       "      <td>97</td>\n",
       "      <td>87781</td>\n",
       "      <td>West Town</td>\n",
       "      <td>2.762557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>WOODLAWN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>24425</td>\n",
       "      <td>Woodlawn</td>\n",
       "      <td>0.409417</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>71 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    community_caps  n_dockets  n_records  2020_pop  community_name  \\\n",
       "0      ALBANY PARK         15         15     48396     Albany Park   \n",
       "1   ARCHER HEIGHTS         14         14     14196  Archer Heights   \n",
       "2    ARMOUR SQUARE         26         26     13890   Armour Square   \n",
       "3          ASHBURN          2          2     41098         Ashburn   \n",
       "4   AUBURN GRESHAM         19         19     44878  Auburn Gresham   \n",
       "..             ...        ...        ...       ...             ...   \n",
       "66       WEST LAWN         17         17     33662       West Lawn   \n",
       "67    WEST PULLMAN          5          5     26104    West Pullman   \n",
       "68      WEST RIDGE         24         24     77122      West Ridge   \n",
       "69       WEST TOWN         97         97     87781       West Town   \n",
       "70        WOODLAWN          4          4     24425        Woodlawn   \n",
       "\n",
       "    dockets per 10k  \n",
       "0          0.774857  \n",
       "1          2.465483  \n",
       "2          4.679626  \n",
       "3          0.121660  \n",
       "4          1.058425  \n",
       "..              ...  \n",
       "66         1.262551  \n",
       "67         0.478854  \n",
       "68         0.777988  \n",
       "69         2.762557  \n",
       "70         0.409417  \n",
       "\n",
       "[71 rows x 6 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community_summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "36c6ba69",
   "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_dockets</th>\n",
       "      <th>n_records</th>\n",
       "      <th>2020_pop</th>\n",
       "      <th>community_name</th>\n",
       "      <th>dockets per 10k</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>ENGLEWOOD</td>\n",
       "      <td>69</td>\n",
       "      <td>69</td>\n",
       "      <td>24369</td>\n",
       "      <td>Englewood</td>\n",
       "      <td>7.078666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>WEST ENGLEWOOD</td>\n",
       "      <td>75</td>\n",
       "      <td>75</td>\n",
       "      <td>29647</td>\n",
       "      <td>West Englewood</td>\n",
       "      <td>6.324417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>GARFIELD RIDGE</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>35439</td>\n",
       "      <td>Garfield Ridge</td>\n",
       "      <td>6.278394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>GRAND BOULEVARD</td>\n",
       "      <td>52</td>\n",
       "      <td>52</td>\n",
       "      <td>24589</td>\n",
       "      <td>Grand Boulevard</td>\n",
       "      <td>5.286917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>OAKLAND</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>6799</td>\n",
       "      <td>Oakland</td>\n",
       "      <td>4.780115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>EAST GARFIELD PARK</td>\n",
       "      <td>38</td>\n",
       "      <td>38</td>\n",
       "      <td>19992</td>\n",
       "      <td>East Garfield Park</td>\n",
       "      <td>4.751901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARMOUR SQUARE</td>\n",
       "      <td>26</td>\n",
       "      <td>26</td>\n",
       "      <td>13890</td>\n",
       "      <td>Armour Square</td>\n",
       "      <td>4.679626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>BRIGHTON PARK</td>\n",
       "      <td>82</td>\n",
       "      <td>82</td>\n",
       "      <td>45053</td>\n",
       "      <td>Brighton Park</td>\n",
       "      <td>4.550196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>LINCOLN PARK</td>\n",
       "      <td>127</td>\n",
       "      <td>127</td>\n",
       "      <td>70492</td>\n",
       "      <td>Lincoln Park</td>\n",
       "      <td>4.504057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>KENWOOD</td>\n",
       "      <td>33</td>\n",
       "      <td>33</td>\n",
       "      <td>19116</td>\n",
       "      <td>Kenwood</td>\n",
       "      <td>4.315756</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        community_caps  n_dockets  n_records  2020_pop      community_name  \\\n",
       "20           ENGLEWOOD         69         69     24369           Englewood   \n",
       "64      WEST ENGLEWOOD         75         75     29647      West Englewood   \n",
       "24      GARFIELD RIDGE         89         89     35439      Garfield Ridge   \n",
       "25     GRAND BOULEVARD         52         52     24589     Grand Boulevard   \n",
       "51             OAKLAND         13         13      6799             Oakland   \n",
       "17  EAST GARFIELD PARK         38         38     19992  East Garfield Park   \n",
       "2        ARMOUR SQUARE         26         26     13890       Armour Square   \n",
       "10       BRIGHTON PARK         82         82     45053       Brighton Park   \n",
       "35        LINCOLN PARK        127        127     70492        Lincoln Park   \n",
       "33             KENWOOD         33         33     19116             Kenwood   \n",
       "\n",
       "    dockets per 10k  \n",
       "20         7.078666  \n",
       "64         6.324417  \n",
       "24         6.278394  \n",
       "25         5.286917  \n",
       "51         4.780115  \n",
       "17         4.751901  \n",
       "2          4.679626  \n",
       "10         4.550196  \n",
       "35         4.504057  \n",
       "33         4.315756  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_community_summary.sort_values('dockets per 10k',ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "4eb78241",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_community_summary.to_csv(\"../../results/ssw-part1-admin-hearings-by-community.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba263ca1",
   "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
}