{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:01.666277Z",
     "iopub.status.busy": "2024-03-18T21:43:01.665749Z",
     "iopub.status.idle": "2024-03-18T21:43:01.765220Z",
     "shell.execute_reply": "2024-03-18T21:43:01.764890Z"
    },
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# a bit of setup just for lecture; ignore me\n",
    "import numpy as np\n",
    "\n",
    "np.random.seed(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "SA9q8DQPL6oZ",
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Class 1: Working with data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "- Questions?\n",
    "- How was the homework? 👍/👎"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Fundamental programming concepts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "From the PB&J exercise last week, ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "### Objects\n",
    "\n",
    "- Jelly\n",
    "- Jar of peanut butter\n",
    "- Knife\n",
    "\n",
    "In code, they represent a \"thing\", which often has associated actions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "### Abstraction\n",
    "\n",
    "Verbs like:\n",
    "\n",
    "- \"Open\"\n",
    "- \"Spread\"\n",
    "\n",
    "In code, known as functions/methods, which hide the details of what's happening (the \"implementation\"). Similarly, \"the sandwich\" is an abstraction for an object, which contains other objects."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "### Pronouns/nicknames\n",
    "\n",
    "- \"It\"\n",
    "- \"PB\"\n",
    "\n",
    "In code, known as variables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "- **Variables:**\n",
    "   - Statistics: [\"any characteristics, number, or quantity that can be measured or counted,\"](https://www.abs.gov.au/websitedbs/D3310114.nsf/home/statistical+language+-+what+are+variables) which we'll see as columns of tables\n",
    "   - Programming: Buckets for values\n",
    "- **Functions:** Buckets for code\n",
    "   - You'll learn more about these in the homework"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Variables and functions\n",
    "\n",
    "- Make it possible to reuse the respective thing elsewhere\n",
    "- [Can be named _almost_ anything you want](https://realpython.com/python-variables/#variable-names)\n",
    "- Should have names that are meaningful, for readability\n",
    "\n",
    "Also:\n",
    "\n",
    "- Function arguments are basically variables within that function, which get assigned to whatever is passed in"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "- Functions == methods\n",
    "- Arguments == parameters\n",
    "\n",
    "For simplicity, we'll use them interchangeably."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Packages\n",
    "\n",
    "- a.k.a. \"libraries\" or \"modules\"\n",
    "- Developers have create them to make code/functionality reusable and easily sharable\n",
    "- Software plugins that you `import`\n",
    "- Main packages we’ll use:\n",
    "    - `pandas`\n",
    "    - `plotly`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "### Operator precedence\n",
    "\n",
    "a.k.a. order of operations, like [PEMDAS](https://en.wikipedia.org/wiki/Order_of_operations#Mnemonics) from math"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "```python\n",
    "answer = \"No\"\n",
    "\n",
    "answer == \"Yes\" or \"yes\"\n",
    "```\n",
    "\n",
    "What will this evaluate to?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "[Evaluation order](https://docs.python.org/3/reference/expressions.html#evaluation-order) and [operator precedence](https://docs.python.org/3/reference/expressions.html#operator-precedence)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "```python\n",
    "answer = \"No\"\n",
    "\n",
    "\n",
    "result =  answer == \"Yes\"  or \"yes\"\n",
    "#           ↓\n",
    "result =   \"No\"  == \"Yes\"  or \"yes\"\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "```python\n",
    "#                 ↓\n",
    "result = ( \"No\"  == \"Yes\") or \"yes\"\n",
    "# `==` has higher precedence than `or`\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "```python\n",
    "#                 ↓\n",
    "result =        False      or \"yes\"\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "```python\n",
    "#                          ↓\n",
    "result =                 \"yes\"\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:01.768076Z",
     "iopub.status.busy": "2024-03-18T21:43:01.767932Z",
     "iopub.status.idle": "2024-03-18T21:43:01.771687Z",
     "shell.execute_reply": "2024-03-18T21:43:01.771348Z"
    },
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'yes'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "answer = \"No\"\n",
    "\n",
    "result = answer == \"Yes\" or \"yes\"\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "**Takeaway:** Code is better when readable. Use parentheses so the reader doesn't have to think!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Working with CSVs in pure Python\n",
    "\n",
    "We will use Python's CSV [DictReader](https://docs.python.org/3/library/csv.html#csv.DictReader). We'll open the file, parse it as a CSV, then operate row by row."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Example\n",
    "\n",
    "```python\n",
    "import csv\n",
    "\n",
    "\n",
    "# set up the file object\n",
    "with open(\"people.csv\") as csvfile:\n",
    "    # set up the reader\n",
    "    reader = csv.DictReader(csvfile)\n",
    "    # loop through the rows\n",
    "    for row in reader:\n",
    "        # access the data in various columns\n",
    "        first = row[\"first_name\"]\n",
    "        last = row[\"last_name\"]\n",
    "\n",
    "        print(f\"{first} {last}\")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### [In-class exercise](https://python-public-policy.afeld.me/en/{{school_slug}}/lecture_1_exercise.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 311 requests\n",
    "\n",
    "Who's called 311 before?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "[NYC 311 homepage](https://portal.311.nyc.gov/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### [311 data](https://data.cityofnewyork.us/Social-Services/311-Service-Requests-from-2010-to-Present/erm2-nwe9)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "CRAqTQ2rbXAA",
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Today's goal\n",
    "\n",
    "- Which 311 complaints are most common?\n",
    "- Which agencies are responsible for handling them?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9rvnMzjSMK36",
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Pandas\n",
    "\n",
    "- A Python package (bundled up code that you can reuse)\n",
    "- Very common for data science in Python\n",
    "- [A lot like R](https://pandas.pydata.org/docs/getting_started/comparison/comparison_with_r.html)\n",
    "   - Both organize around \"data frames\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Start by importing necessary packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:01.792529Z",
     "iopub.status.busy": "2024-03-18T21:43:01.792361Z",
     "iopub.status.idle": "2024-03-18T21:43:02.006698Z",
     "shell.execute_reply": "2024-03-18T21:43:02.006369Z"
    },
    "id": "Q4l2QosJLzP6"
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "R1G04BmMMFJb",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Read and save 311 Service Requests dataset as a pandas dataframe\n",
    "\n",
    "We're using a sample to make it easier/faster to work with. This will take a while (~30 seconds)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:02.008609Z",
     "iopub.status.busy": "2024-03-18T21:43:02.008468Z",
     "iopub.status.idle": "2024-03-18T21:43:06.797885Z",
     "shell.execute_reply": "2024-03-18T21:43:06.797553Z"
    },
    "id": "iQgE8qFAMbiF"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/kr/nx0m1j811kz5vy8c87ffchzr0000gn/T/ipykernel_27038/4056869583.py:1: DtypeWarning: Columns (8,20,31,34) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  requests = pd.read_csv(\n"
     ]
    }
   ],
   "source": [
    "requests = pd.read_csv(\n",
    "    \"https://storage.googleapis.com/python-public-policy2/data/311_requests_2018-19_sample.csv.zip\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ignore the `DtypeWarning` for now; we'll come back to it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MgQ0ykVeNFsF",
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Preview the data contents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:06.799693Z",
     "iopub.status.busy": "2024-03-18T21:43:06.799573Z",
     "iopub.status.idle": "2024-03-18T21:43:06.810635Z",
     "shell.execute_reply": "2024-03-18T21:43:06.810381Z"
    },
    "id": "YdtFBy0Il7E7",
    "outputId": "7bb49293-60dd-4df6-e5df-1eec7f2343ce",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>Unique Key</th>\n",
       "      <th>Created Date</th>\n",
       "      <th>Closed Date</th>\n",
       "      <th>Agency</th>\n",
       "      <th>Agency Name</th>\n",
       "      <th>Complaint Type</th>\n",
       "      <th>Descriptor</th>\n",
       "      <th>Location Type</th>\n",
       "      <th>Incident Zip</th>\n",
       "      <th>Incident Address</th>\n",
       "      <th>...</th>\n",
       "      <th>Vehicle Type</th>\n",
       "      <th>Taxi Company Borough</th>\n",
       "      <th>Taxi Pick Up Location</th>\n",
       "      <th>Bridge Highway Name</th>\n",
       "      <th>Bridge Highway Direction</th>\n",
       "      <th>Road Ramp</th>\n",
       "      <th>Bridge Highway Segment</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39885889</td>\n",
       "      <td>08/01/2018 12:05:13 AM</td>\n",
       "      <td>08/01/2018 12:05:13 AM</td>\n",
       "      <td>DOT</td>\n",
       "      <td>Department of Transportation</td>\n",
       "      <td>Street Condition</td>\n",
       "      <td>Pothole</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11235</td>\n",
       "      <td>3143 SHORE PARKWAY</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.585156</td>\n",
       "      <td>-73.959119</td>\n",
       "      <td>(40.585155533520144, -73.95911915841708)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39886470</td>\n",
       "      <td>08/01/2018 12:06:05 AM</td>\n",
       "      <td>08/01/2018 12:06:05 AM</td>\n",
       "      <td>DOT</td>\n",
       "      <td>Department of Transportation</td>\n",
       "      <td>Street Condition</td>\n",
       "      <td>Pothole</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11235</td>\n",
       "      <td>3153 SHORE PARKWAY</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.585218</td>\n",
       "      <td>-73.958608</td>\n",
       "      <td>(40.58521848090658, -73.95860788382927)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39893543</td>\n",
       "      <td>08/01/2018 12:06:16 AM</td>\n",
       "      <td>08/03/2018 02:03:55 PM</td>\n",
       "      <td>HPD</td>\n",
       "      <td>Department of Housing Preservation and Develop...</td>\n",
       "      <td>HEAT/HOT WATER</td>\n",
       "      <td>ENTIRE BUILDING</td>\n",
       "      <td>RESIDENTIAL BUILDING</td>\n",
       "      <td>11221</td>\n",
       "      <td>729 LAFAYETTE AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.690733</td>\n",
       "      <td>-73.943964</td>\n",
       "      <td>(40.69073285353906, -73.943963521266)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39886233</td>\n",
       "      <td>08/01/2018 12:06:29 AM</td>\n",
       "      <td>08/01/2018 02:54:24 AM</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Banging/Pounding</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11693</td>\n",
       "      <td>82-01 BEACH CHANNEL DRIVE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.589931</td>\n",
       "      <td>-73.808896</td>\n",
       "      <td>(40.58993080750793, -73.80889570815852)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39880309</td>\n",
       "      <td>08/01/2018 12:06:51 AM</td>\n",
       "      <td>08/01/2018 04:54:26 AM</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11216</td>\n",
       "      <td>64 HERKIMER STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.679716</td>\n",
       "      <td>-73.951234</td>\n",
       "      <td>(40.67971590505359, -73.95123396494363)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unique Key            Created Date             Closed Date Agency  \\\n",
       "0    39885889  08/01/2018 12:05:13 AM  08/01/2018 12:05:13 AM    DOT   \n",
       "1    39886470  08/01/2018 12:06:05 AM  08/01/2018 12:06:05 AM    DOT   \n",
       "2    39893543  08/01/2018 12:06:16 AM  08/03/2018 02:03:55 PM    HPD   \n",
       "3    39886233  08/01/2018 12:06:29 AM  08/01/2018 02:54:24 AM   NYPD   \n",
       "4    39880309  08/01/2018 12:06:51 AM  08/01/2018 04:54:26 AM   NYPD   \n",
       "\n",
       "                                         Agency Name       Complaint Type  \\\n",
       "0                       Department of Transportation     Street Condition   \n",
       "1                       Department of Transportation     Street Condition   \n",
       "2  Department of Housing Preservation and Develop...       HEAT/HOT WATER   \n",
       "3                    New York City Police Department  Noise - Residential   \n",
       "4                    New York City Police Department  Noise - Residential   \n",
       "\n",
       "         Descriptor               Location Type Incident Zip  \\\n",
       "0           Pothole                         NaN        11235   \n",
       "1           Pothole                         NaN        11235   \n",
       "2   ENTIRE BUILDING        RESIDENTIAL BUILDING        11221   \n",
       "3  Banging/Pounding  Residential Building/House        11693   \n",
       "4  Loud Music/Party  Residential Building/House        11216   \n",
       "\n",
       "            Incident Address  ... Vehicle Type Taxi Company Borough  \\\n",
       "0         3143 SHORE PARKWAY  ...          NaN                  NaN   \n",
       "1         3153 SHORE PARKWAY  ...          NaN                  NaN   \n",
       "2       729 LAFAYETTE AVENUE  ...          NaN                  NaN   \n",
       "3  82-01 BEACH CHANNEL DRIVE  ...          NaN                  NaN   \n",
       "4         64 HERKIMER STREET  ...          NaN                  NaN   \n",
       "\n",
       "  Taxi Pick Up Location Bridge Highway Name Bridge Highway Direction  \\\n",
       "0                   NaN                 NaN                      NaN   \n",
       "1                   NaN                 NaN                      NaN   \n",
       "2                   NaN                 NaN                      NaN   \n",
       "3                   NaN                 NaN                      NaN   \n",
       "4                   NaN                 NaN                      NaN   \n",
       "\n",
       "  Road Ramp Bridge Highway Segment   Latitude  Longitude  \\\n",
       "0       NaN                    NaN  40.585156 -73.959119   \n",
       "1       NaN                    NaN  40.585218 -73.958608   \n",
       "2       NaN                    NaN  40.690733 -73.943964   \n",
       "3       NaN                    NaN  40.589931 -73.808896   \n",
       "4       NaN                    NaN  40.679716 -73.951234   \n",
       "\n",
       "                                   Location  \n",
       "0  (40.585155533520144, -73.95911915841708)  \n",
       "1   (40.58521848090658, -73.95860788382927)  \n",
       "2     (40.69073285353906, -73.943963521266)  \n",
       "3   (40.58993080750793, -73.80889570815852)  \n",
       "4   (40.67971590505359, -73.95123396494363)  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests.head()  # defaults to providing the first 5 if you don't specify a number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:06.812229Z",
     "iopub.status.busy": "2024-03-18T21:43:06.812114Z",
     "iopub.status.idle": "2024-03-18T21:43:06.819498Z",
     "shell.execute_reply": "2024-03-18T21:43:06.819232Z"
    },
    "id": "WgDL8mn5MPTh",
    "outputId": "6fae2f9d-681d-454b-f80d-809df194e855",
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>Unique Key</th>\n",
       "      <th>Created Date</th>\n",
       "      <th>Closed Date</th>\n",
       "      <th>Agency</th>\n",
       "      <th>Agency Name</th>\n",
       "      <th>Complaint Type</th>\n",
       "      <th>Descriptor</th>\n",
       "      <th>Location Type</th>\n",
       "      <th>Incident Zip</th>\n",
       "      <th>Incident Address</th>\n",
       "      <th>...</th>\n",
       "      <th>Vehicle Type</th>\n",
       "      <th>Taxi Company Borough</th>\n",
       "      <th>Taxi Pick Up Location</th>\n",
       "      <th>Bridge Highway Name</th>\n",
       "      <th>Bridge Highway Direction</th>\n",
       "      <th>Road Ramp</th>\n",
       "      <th>Bridge Highway Segment</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>499990</th>\n",
       "      <td>43622247</td>\n",
       "      <td>08/24/2019 01:33:43 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11385.0</td>\n",
       "      <td>578 FAIRVIEW AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.707576</td>\n",
       "      <td>-73.907325</td>\n",
       "      <td>(40.70757578135031, -73.90732527364065)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499991</th>\n",
       "      <td>43620877</td>\n",
       "      <td>08/24/2019 01:34:32 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11201.0</td>\n",
       "      <td>160 NAVY WALK</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.693967</td>\n",
       "      <td>-73.980210</td>\n",
       "      <td>(40.6939671536727, -73.98020958205214)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499992</th>\n",
       "      <td>43619232</td>\n",
       "      <td>08/24/2019 01:38:44 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Street/Sidewalk</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Street/Sidewalk</td>\n",
       "      <td>11238.0</td>\n",
       "      <td>981 DEAN STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.678030</td>\n",
       "      <td>-73.957620</td>\n",
       "      <td>(40.67803039848778, -73.95762012074778)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499993</th>\n",
       "      <td>43626613</td>\n",
       "      <td>08/24/2019 01:43:57 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Street/Sidewalk</td>\n",
       "      <td>Loud Talking</td>\n",
       "      <td>Street/Sidewalk</td>\n",
       "      <td>10023.0</td>\n",
       "      <td>WEST   65 STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.775372</td>\n",
       "      <td>-73.987710</td>\n",
       "      <td>(40.7753720958196, -73.98770974232366)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499994</th>\n",
       "      <td>43619756</td>\n",
       "      <td>08/24/2019 01:44:27 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11208.0</td>\n",
       "      <td>211 NICHOLS AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.685495</td>\n",
       "      <td>-73.868876</td>\n",
       "      <td>(40.68549515215576, -73.8688762456483)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499995</th>\n",
       "      <td>43622302</td>\n",
       "      <td>08/24/2019 01:46:09 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>10009.0</td>\n",
       "      <td>431 EAST    9 STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.727536</td>\n",
       "      <td>-73.983295</td>\n",
       "      <td>(40.72753608835362, -73.98329522742081)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499996</th>\n",
       "      <td>43619709</td>\n",
       "      <td>08/24/2019 01:49:49 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>10304.0</td>\n",
       "      <td>191 BROAD STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.624157</td>\n",
       "      <td>-74.081006</td>\n",
       "      <td>(40.62415703282506, -74.08100614362155)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499997</th>\n",
       "      <td>43623124</td>\n",
       "      <td>08/24/2019 01:56:35 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Street/Sidewalk</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Street/Sidewalk</td>\n",
       "      <td>10031.0</td>\n",
       "      <td>534 WEST  153 STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.830718</td>\n",
       "      <td>-73.945006</td>\n",
       "      <td>(40.83071800761314, -73.94500557250639)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499998</th>\n",
       "      <td>43625595</td>\n",
       "      <td>08/24/2019 01:56:40 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>10452.0</td>\n",
       "      <td>EAST  170 STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.839882</td>\n",
       "      <td>-73.916783</td>\n",
       "      <td>(40.839882158779105, -73.91678321635897)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499999</th>\n",
       "      <td>43622817</td>\n",
       "      <td>08/24/2019 01:57:58 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Store/Commercial</td>\n",
       "      <td>10033.0</td>\n",
       "      <td>247 AUDUBON AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.846376</td>\n",
       "      <td>-73.934048</td>\n",
       "      <td>(40.84637632367179, -73.93404825809533)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Unique Key            Created Date Closed Date Agency  \\\n",
       "499990    43622247  08/24/2019 01:33:43 AM         NaN   NYPD   \n",
       "499991    43620877  08/24/2019 01:34:32 AM         NaN   NYPD   \n",
       "499992    43619232  08/24/2019 01:38:44 AM         NaN   NYPD   \n",
       "499993    43626613  08/24/2019 01:43:57 AM         NaN   NYPD   \n",
       "499994    43619756  08/24/2019 01:44:27 AM         NaN   NYPD   \n",
       "499995    43622302  08/24/2019 01:46:09 AM         NaN   NYPD   \n",
       "499996    43619709  08/24/2019 01:49:49 AM         NaN   NYPD   \n",
       "499997    43623124  08/24/2019 01:56:35 AM         NaN   NYPD   \n",
       "499998    43625595  08/24/2019 01:56:40 AM         NaN   NYPD   \n",
       "499999    43622817  08/24/2019 01:57:58 AM         NaN   NYPD   \n",
       "\n",
       "                            Agency Name           Complaint Type  \\\n",
       "499990  New York City Police Department      Noise - Residential   \n",
       "499991  New York City Police Department      Noise - Residential   \n",
       "499992  New York City Police Department  Noise - Street/Sidewalk   \n",
       "499993  New York City Police Department  Noise - Street/Sidewalk   \n",
       "499994  New York City Police Department      Noise - Residential   \n",
       "499995  New York City Police Department      Noise - Residential   \n",
       "499996  New York City Police Department      Noise - Residential   \n",
       "499997  New York City Police Department  Noise - Street/Sidewalk   \n",
       "499998  New York City Police Department       Noise - Commercial   \n",
       "499999  New York City Police Department       Noise - Commercial   \n",
       "\n",
       "              Descriptor               Location Type Incident Zip  \\\n",
       "499990  Loud Music/Party  Residential Building/House      11385.0   \n",
       "499991  Loud Music/Party  Residential Building/House      11201.0   \n",
       "499992  Loud Music/Party             Street/Sidewalk      11238.0   \n",
       "499993      Loud Talking             Street/Sidewalk      10023.0   \n",
       "499994  Loud Music/Party  Residential Building/House      11208.0   \n",
       "499995  Loud Music/Party  Residential Building/House      10009.0   \n",
       "499996  Loud Music/Party  Residential Building/House      10304.0   \n",
       "499997  Loud Music/Party             Street/Sidewalk      10031.0   \n",
       "499998  Loud Music/Party         Club/Bar/Restaurant      10452.0   \n",
       "499999  Loud Music/Party            Store/Commercial      10033.0   \n",
       "\n",
       "            Incident Address  ... Vehicle Type Taxi Company Borough  \\\n",
       "499990   578 FAIRVIEW AVENUE  ...          NaN                  NaN   \n",
       "499991         160 NAVY WALK  ...          NaN                  NaN   \n",
       "499992       981 DEAN STREET  ...          NaN                  NaN   \n",
       "499993      WEST   65 STREET  ...          NaN                  NaN   \n",
       "499994    211 NICHOLS AVENUE  ...          NaN                  NaN   \n",
       "499995  431 EAST    9 STREET  ...          NaN                  NaN   \n",
       "499996      191 BROAD STREET  ...          NaN                  NaN   \n",
       "499997  534 WEST  153 STREET  ...          NaN                  NaN   \n",
       "499998      EAST  170 STREET  ...          NaN                  NaN   \n",
       "499999    247 AUDUBON AVENUE  ...          NaN                  NaN   \n",
       "\n",
       "       Taxi Pick Up Location Bridge Highway Name Bridge Highway Direction  \\\n",
       "499990                   NaN                 NaN                      NaN   \n",
       "499991                   NaN                 NaN                      NaN   \n",
       "499992                   NaN                 NaN                      NaN   \n",
       "499993                   NaN                 NaN                      NaN   \n",
       "499994                   NaN                 NaN                      NaN   \n",
       "499995                   NaN                 NaN                      NaN   \n",
       "499996                   NaN                 NaN                      NaN   \n",
       "499997                   NaN                 NaN                      NaN   \n",
       "499998                   NaN                 NaN                      NaN   \n",
       "499999                   NaN                 NaN                      NaN   \n",
       "\n",
       "       Road Ramp Bridge Highway Segment   Latitude  Longitude  \\\n",
       "499990       NaN                    NaN  40.707576 -73.907325   \n",
       "499991       NaN                    NaN  40.693967 -73.980210   \n",
       "499992       NaN                    NaN  40.678030 -73.957620   \n",
       "499993       NaN                    NaN  40.775372 -73.987710   \n",
       "499994       NaN                    NaN  40.685495 -73.868876   \n",
       "499995       NaN                    NaN  40.727536 -73.983295   \n",
       "499996       NaN                    NaN  40.624157 -74.081006   \n",
       "499997       NaN                    NaN  40.830718 -73.945006   \n",
       "499998       NaN                    NaN  40.839882 -73.916783   \n",
       "499999       NaN                    NaN  40.846376 -73.934048   \n",
       "\n",
       "                                        Location  \n",
       "499990   (40.70757578135031, -73.90732527364065)  \n",
       "499991    (40.6939671536727, -73.98020958205214)  \n",
       "499992   (40.67803039848778, -73.95762012074778)  \n",
       "499993    (40.7753720958196, -73.98770974232366)  \n",
       "499994    (40.68549515215576, -73.8688762456483)  \n",
       "499995   (40.72753608835362, -73.98329522742081)  \n",
       "499996   (40.62415703282506, -74.08100614362155)  \n",
       "499997   (40.83071800761314, -73.94500557250639)  \n",
       "499998  (40.839882158779105, -73.91678321635897)  \n",
       "499999   (40.84637632367179, -73.93404825809533)  \n",
       "\n",
       "[10 rows x 41 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests.tail(10)  # last 10 records in the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:06.820998Z",
     "iopub.status.busy": "2024-03-18T21:43:06.820891Z",
     "iopub.status.idle": "2024-03-18T21:43:06.833115Z",
     "shell.execute_reply": "2024-03-18T21:43:06.832859Z"
    },
    "id": "eH6-z-3vM0QI",
    "outputId": "46ab7edf-2b18-43f2-8d83-f90f0cc91be3",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>Unique Key</th>\n",
       "      <th>Created Date</th>\n",
       "      <th>Closed Date</th>\n",
       "      <th>Agency</th>\n",
       "      <th>Agency Name</th>\n",
       "      <th>Complaint Type</th>\n",
       "      <th>Descriptor</th>\n",
       "      <th>Location Type</th>\n",
       "      <th>Incident Zip</th>\n",
       "      <th>Incident Address</th>\n",
       "      <th>...</th>\n",
       "      <th>Vehicle Type</th>\n",
       "      <th>Taxi Company Borough</th>\n",
       "      <th>Taxi Pick Up Location</th>\n",
       "      <th>Bridge Highway Name</th>\n",
       "      <th>Bridge Highway Direction</th>\n",
       "      <th>Road Ramp</th>\n",
       "      <th>Bridge Highway Segment</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>311150</th>\n",
       "      <td>42044618</td>\n",
       "      <td>03/25/2019 10:16:38 PM</td>\n",
       "      <td>03/25/2019 11:47:09 PM</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Talking</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>10453.0</td>\n",
       "      <td>1800 POPHAM AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.851389</td>\n",
       "      <td>-73.917651</td>\n",
       "      <td>(40.85138859491611, -73.91765079814148)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32142</th>\n",
       "      <td>40107788</td>\n",
       "      <td>08/25/2018 10:24:40 AM</td>\n",
       "      <td>08/25/2018 04:30:08 PM</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Blocked Driveway</td>\n",
       "      <td>No Access</td>\n",
       "      <td>Street/Sidewalk</td>\n",
       "      <td>11419.0</td>\n",
       "      <td>133-05 107 AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.687721</td>\n",
       "      <td>-73.811536</td>\n",
       "      <td>(40.6877211401399, -73.8115361385404)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120849</th>\n",
       "      <td>40692417</td>\n",
       "      <td>10/29/2018 07:23:28 AM</td>\n",
       "      <td>11/01/2018 09:52:41 PM</td>\n",
       "      <td>HPD</td>\n",
       "      <td>Department of Housing Preservation and Develop...</td>\n",
       "      <td>HEAT/HOT WATER</td>\n",
       "      <td>APARTMENT ONLY</td>\n",
       "      <td>RESIDENTIAL BUILDING</td>\n",
       "      <td>10040</td>\n",
       "      <td>660 FT WASHINGTON AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.856431</td>\n",
       "      <td>-73.936092</td>\n",
       "      <td>(40.856431325229096, -73.93609157070478)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>471582</th>\n",
       "      <td>43386392</td>\n",
       "      <td>07/26/2019 11:17:21 PM</td>\n",
       "      <td>07/27/2019 02:36:26 AM</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>10303.0</td>\n",
       "      <td>351 HARBOR ROAD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.627510</td>\n",
       "      <td>-74.160446</td>\n",
       "      <td>(40.62751009515846, -74.16044618563123)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455213</th>\n",
       "      <td>43253780</td>\n",
       "      <td>07/11/2019 03:23:28 PM</td>\n",
       "      <td>07/12/2019 11:25:43 AM</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Illegal Parking</td>\n",
       "      <td>Double Parked Blocking Traffic</td>\n",
       "      <td>Street/Sidewalk</td>\n",
       "      <td>11204.0</td>\n",
       "      <td>61 STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Unique Key            Created Date             Closed Date Agency  \\\n",
       "311150    42044618  03/25/2019 10:16:38 PM  03/25/2019 11:47:09 PM   NYPD   \n",
       "32142     40107788  08/25/2018 10:24:40 AM  08/25/2018 04:30:08 PM   NYPD   \n",
       "120849    40692417  10/29/2018 07:23:28 AM  11/01/2018 09:52:41 PM    HPD   \n",
       "471582    43386392  07/26/2019 11:17:21 PM  07/27/2019 02:36:26 AM   NYPD   \n",
       "455213    43253780  07/11/2019 03:23:28 PM  07/12/2019 11:25:43 AM   NYPD   \n",
       "\n",
       "                                              Agency Name  \\\n",
       "311150                    New York City Police Department   \n",
       "32142                     New York City Police Department   \n",
       "120849  Department of Housing Preservation and Develop...   \n",
       "471582                    New York City Police Department   \n",
       "455213                    New York City Police Department   \n",
       "\n",
       "             Complaint Type                      Descriptor  \\\n",
       "311150  Noise - Residential                    Loud Talking   \n",
       "32142      Blocked Driveway                       No Access   \n",
       "120849       HEAT/HOT WATER                  APARTMENT ONLY   \n",
       "471582  Noise - Residential                Loud Music/Party   \n",
       "455213      Illegal Parking  Double Parked Blocking Traffic   \n",
       "\n",
       "                     Location Type Incident Zip          Incident Address  \\\n",
       "311150  Residential Building/House      10453.0        1800 POPHAM AVENUE   \n",
       "32142              Street/Sidewalk      11419.0         133-05 107 AVENUE   \n",
       "120849        RESIDENTIAL BUILDING        10040  660 FT WASHINGTON AVENUE   \n",
       "471582  Residential Building/House      10303.0           351 HARBOR ROAD   \n",
       "455213             Street/Sidewalk      11204.0                 61 STREET   \n",
       "\n",
       "        ... Vehicle Type Taxi Company Borough Taxi Pick Up Location  \\\n",
       "311150  ...          NaN                  NaN                   NaN   \n",
       "32142   ...          NaN                  NaN                   NaN   \n",
       "120849  ...          NaN                  NaN                   NaN   \n",
       "471582  ...          NaN                  NaN                   NaN   \n",
       "455213  ...          NaN                  NaN                   NaN   \n",
       "\n",
       "       Bridge Highway Name Bridge Highway Direction Road Ramp  \\\n",
       "311150                 NaN                      NaN       NaN   \n",
       "32142                  NaN                      NaN       NaN   \n",
       "120849                 NaN                      NaN       NaN   \n",
       "471582                 NaN                      NaN       NaN   \n",
       "455213                 NaN                      NaN       NaN   \n",
       "\n",
       "       Bridge Highway Segment   Latitude  Longitude  \\\n",
       "311150                    NaN  40.851389 -73.917651   \n",
       "32142                     NaN  40.687721 -73.811536   \n",
       "120849                    NaN  40.856431 -73.936092   \n",
       "471582                    NaN  40.627510 -74.160446   \n",
       "455213                    NaN        NaN        NaN   \n",
       "\n",
       "                                        Location  \n",
       "311150   (40.85138859491611, -73.91765079814148)  \n",
       "32142      (40.6877211401399, -73.8115361385404)  \n",
       "120849  (40.856431325229096, -73.93609157070478)  \n",
       "471582   (40.62751009515846, -74.16044618563123)  \n",
       "455213                                       NaN  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests.sample(5)  # random sample of size determined by you"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Pandas data structures\n",
    "\n",
    "<!-- source: https://docs.google.com/document/d/1HGw2BdbuXSIwcgDWXkzZGPXYr5yJ_WEM3Gw-nLoHzCo/edit#heading=h.7z4rqdvodt9j -->\n",
    "\n",
    "![Diagram showing a DataFrame, Series, labels, and indexes](extras/img/data_structures-1.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "f-YNgxGPNRgi",
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## How many records are in the dataset?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7DPo85wSNU6q",
    "slideshow": {
     "slide_type": "subslide"
    },
    "toc-hr-collapsed": true,
    "toc-nb-collapsed": true
   },
   "source": [
    "### `info()` method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:06.834842Z",
     "iopub.status.busy": "2024-03-18T21:43:06.834732Z",
     "iopub.status.idle": "2024-03-18T21:43:07.889619Z",
     "shell.execute_reply": "2024-03-18T21:43:07.889306Z"
    },
    "id": "--ben4hfmTaB",
    "outputId": "62bae542-8fda-40c4-82f6-7a6410c2a90b"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 500000 entries, 0 to 499999\n",
      "Data columns (total 41 columns):\n",
      " #   Column                          Non-Null Count   Dtype  \n",
      "---  ------                          --------------   -----  \n",
      " 0   Unique Key                      500000 non-null  int64  \n",
      " 1   Created Date                    500000 non-null  object \n",
      " 2   Closed Date                     476156 non-null  object \n",
      " 3   Agency                          500000 non-null  object \n",
      " 4   Agency Name                     500000 non-null  object \n",
      " 5   Complaint Type                  500000 non-null  object \n",
      " 6   Descriptor                      492534 non-null  object \n",
      " 7   Location Type                   392590 non-null  object \n",
      " 8   Incident Zip                    480411 non-null  object \n",
      " 9   Incident Address                434544 non-null  object \n",
      " 10  Street Name                     434519 non-null  object \n",
      " 11  Cross Street 1                  300838 non-null  object \n",
      " 12  Cross Street 2                  299635 non-null  object \n",
      " 13  Intersection Street 1           107383 non-null  object \n",
      " 14  Intersection Street 2           107048 non-null  object \n",
      " 15  Address Type                    451018 non-null  object \n",
      " 16  City                            476649 non-null  object \n",
      " 17  Landmark                        32521 non-null   object \n",
      " 18  Facility Type                   134923 non-null  object \n",
      " 19  Status                          500000 non-null  object \n",
      " 20  Due Date                        171559 non-null  object \n",
      " 21  Resolution Description          457389 non-null  object \n",
      " 22  Resolution Action Updated Date  488801 non-null  object \n",
      " 23  Community Board                 500000 non-null  object \n",
      " 24  BBL                             407355 non-null  float64\n",
      " 25  Borough                         500000 non-null  object \n",
      " 26  X Coordinate (State Plane)      470830 non-null  float64\n",
      " 27  Y Coordinate (State Plane)      470830 non-null  float64\n",
      " 28  Open Data Channel Type          500000 non-null  object \n",
      " 29  Park Facility Name              499973 non-null  object \n",
      " 30  Park Borough                    500000 non-null  object \n",
      " 31  Vehicle Type                    37 non-null      object \n",
      " 32  Taxi Company Borough            403 non-null     object \n",
      " 33  Taxi Pick Up Location           4475 non-null    object \n",
      " 34  Bridge Highway Name             697 non-null     object \n",
      " 35  Bridge Highway Direction        766 non-null     object \n",
      " 36  Road Ramp                       759 non-null     object \n",
      " 37  Bridge Highway Segment          1027 non-null    object \n",
      " 38  Latitude                        470830 non-null  float64\n",
      " 39  Longitude                       470830 non-null  float64\n",
      " 40  Location                        470830 non-null  object \n",
      "dtypes: float64(5), int64(1), object(35)\n",
      "memory usage: 156.4+ MB\n"
     ]
    }
   ],
   "source": [
    "requests.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jy54_jkFNYm3",
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## What are the distinct sets of values in columns that seem most useful?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "UqfGEk09uHsN",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### [`unique()`](https://pandas.pydata.org/docs/reference/api/pandas.Series.unique.html) method for getting list of unique values\n",
    "\n",
    "Let's look at the \"status\" column. What are the status options for these 311 complaints?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:07.891481Z",
     "iopub.status.busy": "2024-03-18T21:43:07.891353Z",
     "iopub.status.idle": "2024-03-18T21:43:07.905418Z",
     "shell.execute_reply": "2024-03-18T21:43:07.905137Z"
    },
    "id": "cIVITVAQN3_c",
    "outputId": "d98eba00-5a05-4327-ea0a-eeb92d63a09d"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Closed', 'In Progress', 'Pending', 'Started', 'Open', 'Assigned',\n",
       "       'Email Sent'], dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests[\"Status\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:07.906984Z",
     "iopub.status.busy": "2024-03-18T21:43:07.906885Z",
     "iopub.status.idle": "2024-03-18T21:43:07.923033Z",
     "shell.execute_reply": "2024-03-18T21:43:07.922733Z"
    },
    "id": "W_tjsqNSp5vd",
    "outputId": "672b21e3-d0ca-45e3-ed2b-964187e8276b",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['UNKNOWN', 'MOBILE', 'PHONE', 'ONLINE', 'OTHER'], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests[\"Open Data Channel Type\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:07.924847Z",
     "iopub.status.busy": "2024-03-18T21:43:07.924736Z",
     "iopub.status.idle": "2024-03-18T21:43:07.939335Z",
     "shell.execute_reply": "2024-03-18T21:43:07.939051Z"
    },
    "id": "uYrNPIqfqhz5",
    "outputId": "70677b3d-8f73-4a69-d6ed-86a7821197bf",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['DOT', 'HPD', 'NYPD', 'DSNY', 'DEP', 'DOB', 'DPR', 'DOHMH', 'DHS',\n",
       "       'DCA', 'DOF', 'DFTA', 'TLC', 'HRA', 'EDC', 'DOE', 'DCAS', 'DOITT',\n",
       "       'ACS', 'DCP', 'NYCEM', 'FDNY', 'TAX', 'MOC', 'TAT', 'COIB', 'DVS'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests[\"Agency\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qeYA8-rMlpJa",
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Excluding bad records from the dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "RgP7ehPsmozX",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Let's look at the list of distinct complaint types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:07.941031Z",
     "iopub.status.busy": "2024-03-18T21:43:07.940907Z",
     "iopub.status.idle": "2024-03-18T21:43:07.962391Z",
     "shell.execute_reply": "2024-03-18T21:43:07.962133Z"
    },
    "id": "cU0-Bnjfb5ft",
    "outputId": "a9da5ac2-ca37-4897-cf03-199f783dc430"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Street Condition', 'HEAT/HOT WATER', 'Noise - Residential',\n",
       "       'Illegal Parking', 'Request Large Bulky Item Collection', 'Noise',\n",
       "       'Noise - Street/Sidewalk', 'Electronics Waste Appointment',\n",
       "       'Blocked Driveway', 'Dirty Conditions', 'Curb Condition',\n",
       "       'Noise - Commercial', 'General Construction/Plumbing',\n",
       "       'Traffic Signal Condition', 'Street Light Condition', 'Lead',\n",
       "       'Street Sign - Damaged', 'Noise - Vehicle', 'New Tree Request',\n",
       "       'Sanitation Condition', 'Mosquitoes', 'WATER LEAK',\n",
       "       'UNSANITARY CONDITION', 'Root/Sewer/Sidewalk Condition',\n",
       "       'Dead/Dying Tree', 'Derelict Vehicles', 'Collection Truck Noise',\n",
       "       'Sewer', 'GENERAL', 'Overflowing Litter Baskets', 'Vacant Lot',\n",
       "       'Sidewalk Condition', 'PAINT/PLASTER', 'Building/Use',\n",
       "       'Street Sign - Dangling', 'Construction Safety Enforcement',\n",
       "       'PLUMBING', 'Derelict Vehicle', 'Homeless Person Assistance',\n",
       "       'ELECTRIC', 'Water System', 'Damaged Tree',\n",
       "       'Missed Collection (All Materials)', 'Animal Abuse',\n",
       "       'Borough Office', 'Elevator', 'Food Establishment',\n",
       "       'Maintenance or Facility', 'FLOORING/STAIRS', 'Consumer Complaint',\n",
       "       'Overgrown Tree/Branches', 'Graffiti', 'APPLIANCE',\n",
       "       'Other Enforcement', 'DOF Property - Payment Issue',\n",
       "       'Hazardous Materials', 'Water Conservation', 'Water Quality',\n",
       "       'Plumbing', 'DOF Property - Update Account', 'DOOR/WINDOW',\n",
       "       'Housing - Low Income Senior', 'BEST/Site Safety', 'SCRIE',\n",
       "       'Animal in a Park', 'DOF Parking - Payment Issue',\n",
       "       'For Hire Vehicle Complaint', 'Benefit Card Replacement',\n",
       "       'DOF Property - Reduction Issue',\n",
       "       'Special Projects Inspection Team (SPIT)', 'SAFETY',\n",
       "       'Taxi Complaint', 'Beach/Pool/Sauna Complaint',\n",
       "       'Homeless Encampment', 'DOF Property - RPIE Issue',\n",
       "       'Recycling Enforcement', 'DPR Internal', 'DRIE',\n",
       "       'Street Sign - Missing', 'Taxi Report',\n",
       "       'DOF Parking - Request Status', 'Air Quality',\n",
       "       'Broken Parking Meter', 'Mold', 'HPD Literature Request',\n",
       "       'Poison Ivy', 'Non-Emergency Police Matter',\n",
       "       'DOF Property - Request Copy', 'Housing Options',\n",
       "       'Illegal Tree Damage', 'Traffic', 'Noise - Park', 'Asbestos',\n",
       "       'DCA / DOH New License Application Request', 'Cooling Tower',\n",
       "       'Drinking', 'Indoor Sewage', 'Vending',\n",
       "       'Emergency Response Team (ERT)', 'Electrical',\n",
       "       'DOF Property - Owner Issue', 'Parking Card', 'Indoor Air Quality',\n",
       "       'Violation of Park Rules', 'Mobile Food Vendor',\n",
       "       'Highway Condition', 'ELEVATOR', 'Noise - Helicopter',\n",
       "       'Unsanitary Animal Pvt Property', 'Rodent', 'Standing Water',\n",
       "       'Food Poisoning', 'Litter Basket / Request', 'Day Care',\n",
       "       'Executive Inspections', 'DOF Parking - Tax Exemption',\n",
       "       'OUTSIDE BUILDING', 'DOF Parking - Request Copy',\n",
       "       'DOF Property - Property Value', 'School Maintenance', 'Question',\n",
       "       'Industrial Waste', 'Senior Center Complaint', 'Smoking',\n",
       "       'Quality of Life', 'Derelict Bicycle', 'Ferry Complaint',\n",
       "       'Sweeping/Missed', 'Ferry Inquiry', 'Sweeping/Inadequate',\n",
       "       'Found Property', 'LinkNYC', 'Sustainability Enforcement',\n",
       "       'Taxpayer Advocate Inquiry', 'Investigations and Discipline (IAD)',\n",
       "       'Noise - House of Worship', 'Scaffold Safety', 'Pet Shop',\n",
       "       'Bus Stop Shelter Complaint', 'Forms', 'Elder Abuse',\n",
       "       'Drug Activity', 'Unsanitary Pigeon Condition', 'Comments',\n",
       "       'Boilers', 'Building Marshals office',\n",
       "       'Home Delivered Meal - Missed Delivery',\n",
       "       'Bike/Roller/Skate Chronic', 'Posting Advertisement',\n",
       "       'Research Questions', 'Urinating in Public',\n",
       "       'Public Payphone Complaint', 'Unleashed Dog', 'Facades',\n",
       "       'Illegal Animal Sold', 'Cranes and Derricks',\n",
       "       'Bus Stop Shelter Placement', 'Panhandling', 'Bridge Condition',\n",
       "       'Illegal Animal Kept as Pet', 'OEM Literature Request',\n",
       "       'Advocate-Personal Exemptions', 'Abandoned Vehicle',\n",
       "       \"Alzheimer's Care\", 'Taxi Compliment', 'Disorderly Youth',\n",
       "       'Public Assembly', 'Advocate-Prop Refunds/Credits',\n",
       "       'Unsanitary Condition', 'DOF Parking - DMV Clearance',\n",
       "       'Home Delivered Meal Complaint',\n",
       "       'Transportation Provider Complaint', 'Ferry Permit',\n",
       "       'For Hire Vehicle Report', 'Harboring Bees/Wasps',\n",
       "       'Advocate - Other', 'Home Care Provider Complaint',\n",
       "       'Miscellaneous Categories', 'Municipal Parking Facility', 'FATF',\n",
       "       'Special Natural Area District (SNAD)', 'Illegal Fireworks',\n",
       "       'Highway Sign - Damaged', 'Drinking Water', 'Calorie Labeling',\n",
       "       'Case Management Agency Complaint', 'Bike Rack Condition',\n",
       "       'Unsanitary Animal Facility', 'Plant', 'Public Toilet',\n",
       "       'Tattooing', 'Animal Facility - No Permit', 'Electronics Waste',\n",
       "       'General', 'Snow', 'Unlicensed Dog', 'Taxi Licensee Complaint',\n",
       "       'FHV Licensee Complaint', 'Overflowing Recycling Baskets',\n",
       "       'Highway Sign - Missing', 'Non-Residential Heat',\n",
       "       'Special Operations', 'Safety', 'Radioactive Material',\n",
       "       'Dispatched Taxi Complaint', 'Lifeguard', 'Window Guard',\n",
       "       'Select Message Type...', 'Bereavement Support Group',\n",
       "       'Sweeping/Missed-Inadequate', 'Squeegee', 'Tunnel Condition',\n",
       "       'Foam Ban Enforcement', 'Request Xmas Tree Collection',\n",
       "       'Advocate-Business Tax', 'Advocate - RPIE',\n",
       "       'Highway Sign - Dangling', 'X-Ray Machine/Equipment',\n",
       "       'Advocate-Co-opCondo Abatement', 'Forensic Engineering',\n",
       "       'Outside Building', 'Lost Property',\n",
       "       '(select extractvalue(xmltyp...', \"Misc. Comments' and 7702=77...\",\n",
       "       'qfix4${695*589}lixaf', \"eval(compile('for x in rang...\",\n",
       "       'Misc. Comments\"|echo 2...', 'Misc. Comments\"|ping -...',\n",
       "       \"Misc. Comments'|echo y9v6fc...\", 'file:///c:/windows/win.ini',\n",
       "       \"Misc. Comments'|ping -c 21 ...\", '../../../../../../../../../...',\n",
       "       '\\\\windows\\\\win.ini', '..././..././..././..././......',\n",
       "       'idexf3mrb7)(!(objectClass=*)', 'Misc. Comments  BCC:a5glxzf...',\n",
       "       '$(sleep 11)', '@(9313*3464)', \"{!xmlparser v='<!DOCTYPE...\",\n",
       "       '../WEB-INF/web.xml', 'Animal-Abuse', 'Missed Collection',\n",
       "       'DEP Street Condition', 'Green Taxi Report',\n",
       "       'Green Taxi Complaint', 'Bottled Water',\n",
       "       'Single Occupancy Bathroom', 'Appliance'], dtype=object)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests[\"Complaint Type\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Let's make that a bit easier to read:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:07.964080Z",
     "iopub.status.busy": "2024-03-18T21:43:07.963967Z",
     "iopub.status.idle": "2024-03-18T21:43:07.986101Z",
     "shell.execute_reply": "2024-03-18T21:43:07.985852Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['$(sleep 11)',\n",
       " '(select extractvalue(xmltyp...',\n",
       " '..././..././..././..././......',\n",
       " '../../../../../../../../../...',\n",
       " '../WEB-INF/web.xml',\n",
       " '@(9313*3464)',\n",
       " 'APPLIANCE',\n",
       " 'Abandoned Vehicle',\n",
       " 'Advocate - Other',\n",
       " 'Advocate - RPIE',\n",
       " 'Advocate-Business Tax',\n",
       " 'Advocate-Co-opCondo Abatement',\n",
       " 'Advocate-Personal Exemptions',\n",
       " 'Advocate-Prop Refunds/Credits',\n",
       " 'Air Quality',\n",
       " \"Alzheimer's Care\",\n",
       " 'Animal Abuse',\n",
       " 'Animal Facility - No Permit',\n",
       " 'Animal in a Park',\n",
       " 'Animal-Abuse',\n",
       " 'Appliance',\n",
       " 'Asbestos',\n",
       " 'BEST/Site Safety',\n",
       " 'Beach/Pool/Sauna Complaint',\n",
       " 'Benefit Card Replacement',\n",
       " 'Bereavement Support Group',\n",
       " 'Bike Rack Condition',\n",
       " 'Bike/Roller/Skate Chronic',\n",
       " 'Blocked Driveway',\n",
       " 'Boilers',\n",
       " 'Borough Office',\n",
       " 'Bottled Water',\n",
       " 'Bridge Condition',\n",
       " 'Broken Parking Meter',\n",
       " 'Building Marshals office',\n",
       " 'Building/Use',\n",
       " 'Bus Stop Shelter Complaint',\n",
       " 'Bus Stop Shelter Placement',\n",
       " 'Calorie Labeling',\n",
       " 'Case Management Agency Complaint',\n",
       " 'Collection Truck Noise',\n",
       " 'Comments',\n",
       " 'Construction Safety Enforcement',\n",
       " 'Consumer Complaint',\n",
       " 'Cooling Tower',\n",
       " 'Cranes and Derricks',\n",
       " 'Curb Condition',\n",
       " 'DCA / DOH New License Application Request',\n",
       " 'DEP Street Condition',\n",
       " 'DOF Parking - DMV Clearance',\n",
       " 'DOF Parking - Payment Issue',\n",
       " 'DOF Parking - Request Copy',\n",
       " 'DOF Parking - Request Status',\n",
       " 'DOF Parking - Tax Exemption',\n",
       " 'DOF Property - Owner Issue',\n",
       " 'DOF Property - Payment Issue',\n",
       " 'DOF Property - Property Value',\n",
       " 'DOF Property - RPIE Issue',\n",
       " 'DOF Property - Reduction Issue',\n",
       " 'DOF Property - Request Copy',\n",
       " 'DOF Property - Update Account',\n",
       " 'DOOR/WINDOW',\n",
       " 'DPR Internal',\n",
       " 'DRIE',\n",
       " 'Damaged Tree',\n",
       " 'Day Care',\n",
       " 'Dead/Dying Tree',\n",
       " 'Derelict Bicycle',\n",
       " 'Derelict Vehicle',\n",
       " 'Derelict Vehicles',\n",
       " 'Dirty Conditions',\n",
       " 'Disorderly Youth',\n",
       " 'Dispatched Taxi Complaint',\n",
       " 'Drinking',\n",
       " 'Drinking Water',\n",
       " 'Drug Activity',\n",
       " 'ELECTRIC',\n",
       " 'ELEVATOR',\n",
       " 'Elder Abuse',\n",
       " 'Electrical',\n",
       " 'Electronics Waste',\n",
       " 'Electronics Waste Appointment',\n",
       " 'Elevator',\n",
       " 'Emergency Response Team (ERT)',\n",
       " 'Executive Inspections',\n",
       " 'FATF',\n",
       " 'FHV Licensee Complaint',\n",
       " 'FLOORING/STAIRS',\n",
       " 'Facades',\n",
       " 'Ferry Complaint',\n",
       " 'Ferry Inquiry',\n",
       " 'Ferry Permit',\n",
       " 'Foam Ban Enforcement',\n",
       " 'Food Establishment',\n",
       " 'Food Poisoning',\n",
       " 'For Hire Vehicle Complaint',\n",
       " 'For Hire Vehicle Report',\n",
       " 'Forensic Engineering',\n",
       " 'Forms',\n",
       " 'Found Property',\n",
       " 'GENERAL',\n",
       " 'General',\n",
       " 'General Construction/Plumbing',\n",
       " 'Graffiti',\n",
       " 'Green Taxi Complaint',\n",
       " 'Green Taxi Report',\n",
       " 'HEAT/HOT WATER',\n",
       " 'HPD Literature Request',\n",
       " 'Harboring Bees/Wasps',\n",
       " 'Hazardous Materials',\n",
       " 'Highway Condition',\n",
       " 'Highway Sign - Damaged',\n",
       " 'Highway Sign - Dangling',\n",
       " 'Highway Sign - Missing',\n",
       " 'Home Care Provider Complaint',\n",
       " 'Home Delivered Meal - Missed Delivery',\n",
       " 'Home Delivered Meal Complaint',\n",
       " 'Homeless Encampment',\n",
       " 'Homeless Person Assistance',\n",
       " 'Housing - Low Income Senior',\n",
       " 'Housing Options',\n",
       " 'Illegal Animal Kept as Pet',\n",
       " 'Illegal Animal Sold',\n",
       " 'Illegal Fireworks',\n",
       " 'Illegal Parking',\n",
       " 'Illegal Tree Damage',\n",
       " 'Indoor Air Quality',\n",
       " 'Indoor Sewage',\n",
       " 'Industrial Waste',\n",
       " 'Investigations and Discipline (IAD)',\n",
       " 'Lead',\n",
       " 'Lifeguard',\n",
       " 'LinkNYC',\n",
       " 'Litter Basket / Request',\n",
       " 'Lost Property',\n",
       " 'Maintenance or Facility',\n",
       " 'Misc. Comments  BCC:a5glxzf...',\n",
       " 'Misc. Comments\"|echo 2...',\n",
       " 'Misc. Comments\"|ping -...',\n",
       " \"Misc. Comments' and 7702=77...\",\n",
       " \"Misc. Comments'|echo y9v6fc...\",\n",
       " \"Misc. Comments'|ping -c 21 ...\",\n",
       " 'Miscellaneous Categories',\n",
       " 'Missed Collection',\n",
       " 'Missed Collection (All Materials)',\n",
       " 'Mobile Food Vendor',\n",
       " 'Mold',\n",
       " 'Mosquitoes',\n",
       " 'Municipal Parking Facility',\n",
       " 'New Tree Request',\n",
       " 'Noise',\n",
       " 'Noise - Commercial',\n",
       " 'Noise - Helicopter',\n",
       " 'Noise - House of Worship',\n",
       " 'Noise - Park',\n",
       " 'Noise - Residential',\n",
       " 'Noise - Street/Sidewalk',\n",
       " 'Noise - Vehicle',\n",
       " 'Non-Emergency Police Matter',\n",
       " 'Non-Residential Heat',\n",
       " 'OEM Literature Request',\n",
       " 'OUTSIDE BUILDING',\n",
       " 'Other Enforcement',\n",
       " 'Outside Building',\n",
       " 'Overflowing Litter Baskets',\n",
       " 'Overflowing Recycling Baskets',\n",
       " 'Overgrown Tree/Branches',\n",
       " 'PAINT/PLASTER',\n",
       " 'PLUMBING',\n",
       " 'Panhandling',\n",
       " 'Parking Card',\n",
       " 'Pet Shop',\n",
       " 'Plant',\n",
       " 'Plumbing',\n",
       " 'Poison Ivy',\n",
       " 'Posting Advertisement',\n",
       " 'Public Assembly',\n",
       " 'Public Payphone Complaint',\n",
       " 'Public Toilet',\n",
       " 'Quality of Life',\n",
       " 'Question',\n",
       " 'Radioactive Material',\n",
       " 'Recycling Enforcement',\n",
       " 'Request Large Bulky Item Collection',\n",
       " 'Request Xmas Tree Collection',\n",
       " 'Research Questions',\n",
       " 'Rodent',\n",
       " 'Root/Sewer/Sidewalk Condition',\n",
       " 'SAFETY',\n",
       " 'SCRIE',\n",
       " 'Safety',\n",
       " 'Sanitation Condition',\n",
       " 'Scaffold Safety',\n",
       " 'School Maintenance',\n",
       " 'Select Message Type...',\n",
       " 'Senior Center Complaint',\n",
       " 'Sewer',\n",
       " 'Sidewalk Condition',\n",
       " 'Single Occupancy Bathroom',\n",
       " 'Smoking',\n",
       " 'Snow',\n",
       " 'Special Natural Area District (SNAD)',\n",
       " 'Special Operations',\n",
       " 'Special Projects Inspection Team (SPIT)',\n",
       " 'Squeegee',\n",
       " 'Standing Water',\n",
       " 'Street Condition',\n",
       " 'Street Light Condition',\n",
       " 'Street Sign - Damaged',\n",
       " 'Street Sign - Dangling',\n",
       " 'Street Sign - Missing',\n",
       " 'Sustainability Enforcement',\n",
       " 'Sweeping/Inadequate',\n",
       " 'Sweeping/Missed',\n",
       " 'Sweeping/Missed-Inadequate',\n",
       " 'Tattooing',\n",
       " 'Taxi Complaint',\n",
       " 'Taxi Compliment',\n",
       " 'Taxi Licensee Complaint',\n",
       " 'Taxi Report',\n",
       " 'Taxpayer Advocate Inquiry',\n",
       " 'Traffic',\n",
       " 'Traffic Signal Condition',\n",
       " 'Transportation Provider Complaint',\n",
       " 'Tunnel Condition',\n",
       " 'UNSANITARY CONDITION',\n",
       " 'Unleashed Dog',\n",
       " 'Unlicensed Dog',\n",
       " 'Unsanitary Animal Facility',\n",
       " 'Unsanitary Animal Pvt Property',\n",
       " 'Unsanitary Condition',\n",
       " 'Unsanitary Pigeon Condition',\n",
       " 'Urinating in Public',\n",
       " 'Vacant Lot',\n",
       " 'Vending',\n",
       " 'Violation of Park Rules',\n",
       " 'WATER LEAK',\n",
       " 'Water Conservation',\n",
       " 'Water Quality',\n",
       " 'Water System',\n",
       " 'Window Guard',\n",
       " 'X-Ray Machine/Equipment',\n",
       " '\\\\windows\\\\win.ini',\n",
       " \"eval(compile('for x in rang...\",\n",
       " 'file:///c:/windows/win.ini',\n",
       " 'idexf3mrb7)(!(objectClass=*)',\n",
       " 'qfix4${695*589}lixaf',\n",
       " \"{!xmlparser v='<!DOCTYPE...\"]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complaints = requests[\"Complaint Type\"].unique()\n",
    "complaints.sort()\n",
    "list(complaints)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "RrwqSmKSbiYC",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Let's see how frequently these invalid Complaint Type values appear in the data.\n",
    "\n",
    "Use `.groupby().size()` to get the count of 311 requests per complaint type value. This is very similar to [pivot tables](https://support.google.com/docs/answer/1272900) in spreadsheets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:07.987711Z",
     "iopub.status.busy": "2024-03-18T21:43:07.987618Z",
     "iopub.status.idle": "2024-03-18T21:43:08.013392Z",
     "shell.execute_reply": "2024-03-18T21:43:08.013102Z"
    },
    "id": "XJ2AK37yb3_E",
    "outputId": "1b7a0085-3826-4b8d-d7b6-57ab60c6db21",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Complaint Type\n",
       "$(sleep 11)                                      1\n",
       "(select extractvalue(xmltyp...                   1\n",
       "..././..././..././..././......                   1\n",
       "../../../../../../../../../...                   2\n",
       "../WEB-INF/web.xml                               1\n",
       "@(9313*3464)                                     1\n",
       "APPLIANCE                                     2539\n",
       "Abandoned Vehicle                             1655\n",
       "Advocate - Other                                26\n",
       "Advocate - RPIE                                  1\n",
       "Advocate-Business Tax                            2\n",
       "Advocate-Co-opCondo Abatement                    4\n",
       "Advocate-Personal Exemptions                    24\n",
       "Advocate-Prop Refunds/Credits                   39\n",
       "Air Quality                                   1457\n",
       "Alzheimer's Care                                25\n",
       "Animal Abuse                                  1282\n",
       "Animal Facility - No Permit                     10\n",
       "Animal in a Park                               456\n",
       "Animal-Abuse                                   261\n",
       "Appliance                                        1\n",
       "Asbestos                                       426\n",
       "BEST/Site Safety                               359\n",
       "Beach/Pool/Sauna Complaint                      42\n",
       "Benefit Card Replacement                      1649\n",
       "Bereavement Support Group                        1\n",
       "Bike Rack Condition                             20\n",
       "Bike/Roller/Skate Chronic                       97\n",
       "Blocked Driveway                             25530\n",
       "Boilers                                        449\n",
       "Borough Office                                 900\n",
       "Bottled Water                                    2\n",
       "Bridge Condition                                64\n",
       "Broken Parking Meter                          2499\n",
       "Building Marshals office                       311\n",
       "Building/Use                                  4869\n",
       "Bus Stop Shelter Complaint                     172\n",
       "Bus Stop Shelter Placement                      43\n",
       "Calorie Labeling                                 4\n",
       "Case Management Agency Complaint                 4\n",
       "Collection Truck Noise                          36\n",
       "Comments                                        16\n",
       "Construction Safety Enforcement               1740\n",
       "Consumer Complaint                            2892\n",
       "Cooling Tower                                    7\n",
       "Cranes and Derricks                             48\n",
       "Curb Condition                                1476\n",
       "DCA / DOH New License Application Request      186\n",
       "DEP Street Condition                            46\n",
       "DOF Parking - DMV Clearance                     22\n",
       "DOF Parking - Payment Issue                    532\n",
       "DOF Parking - Request Copy                      97\n",
       "DOF Parking - Request Status                    94\n",
       "DOF Parking - Tax Exemption                    157\n",
       "DOF Property - Owner Issue                     280\n",
       "DOF Property - Payment Issue                  1092\n",
       "DOF Property - Property Value                  106\n",
       "DOF Property - RPIE Issue                       80\n",
       "DOF Property - Reduction Issue                1846\n",
       "DOF Property - Request Copy                   1000\n",
       "DOF Property - Update Account                  802\n",
       "DOOR/WINDOW                                   6479\n",
       "DPR Internal                                   245\n",
       "DRIE                                           233\n",
       "Damaged Tree                                  6065\n",
       "Day Care                                        98\n",
       "Dead/Dying Tree                               1995\n",
       "Derelict Bicycle                               457\n",
       "Derelict Vehicle                              6056\n",
       "Derelict Vehicles                             6759\n",
       "Dirty Conditions                              6776\n",
       "Disorderly Youth                                10\n",
       "Dispatched Taxi Complaint                        5\n",
       "Drinking                                       311\n",
       "Drinking Water                                  16\n",
       "Drug Activity                                  352\n",
       "ELECTRIC                                      4670\n",
       "ELEVATOR                                       250\n",
       "Elder Abuse                                     43\n",
       "Electrical                                     551\n",
       "Electronics Waste                                1\n",
       "Electronics Waste Appointment                 5535\n",
       "Elevator                                      3004\n",
       "Emergency Response Team (ERT)                  872\n",
       "Executive Inspections                           73\n",
       "FATF                                            23\n",
       "FHV Licensee Complaint                           4\n",
       "FLOORING/STAIRS                               4177\n",
       "Facades                                         72\n",
       "Ferry Complaint                                 38\n",
       "Ferry Inquiry                                   96\n",
       "Ferry Permit                                     8\n",
       "Foam Ban Enforcement                             5\n",
       "Food Establishment                            2061\n",
       "Food Poisoning                                 549\n",
       "For Hire Vehicle Complaint                    2789\n",
       "For Hire Vehicle Report                        112\n",
       "Forensic Engineering                             1\n",
       "Forms                                           57\n",
       "Found Property                                  25\n",
       "GENERAL                                       4565\n",
       "General                                        188\n",
       "General Construction/Plumbing                 9537\n",
       "Graffiti                                      4286\n",
       "Green Taxi Complaint                             7\n",
       "Green Taxi Report                                2\n",
       "HEAT/HOT WATER                               39095\n",
       "HPD Literature Request                         405\n",
       "Harboring Bees/Wasps                            31\n",
       "Hazardous Materials                            549\n",
       "Highway Condition                              628\n",
       "Highway Sign - Damaged                          20\n",
       "Highway Sign - Dangling                          3\n",
       "Highway Sign - Missing                          12\n",
       "Home Care Provider Complaint                     4\n",
       "Home Delivered Meal - Missed Delivery           15\n",
       "Home Delivered Meal Complaint                   13\n",
       "Homeless Encampment                            977\n",
       "Homeless Person Assistance                    3670\n",
       "Housing - Low Income Senior                    768\n",
       "Housing Options                                251\n",
       "Illegal Animal Kept as Pet                      37\n",
       "Illegal Animal Sold                              7\n",
       "Illegal Fireworks                              138\n",
       "Illegal Parking                              34297\n",
       "Illegal Tree Damage                            642\n",
       "Indoor Air Quality                             848\n",
       "Indoor Sewage                                  214\n",
       "Industrial Waste                               263\n",
       "Investigations and Discipline (IAD)            152\n",
       "Lead                                          1825\n",
       "Lifeguard                                        7\n",
       "LinkNYC                                         40\n",
       "Litter Basket / Request                        463\n",
       "Lost Property                                  472\n",
       "Maintenance or Facility                       3055\n",
       "Misc. Comments  BCC:a5glxzf...                   1\n",
       "Misc. Comments\"|echo 2...                        1\n",
       "Misc. Comments\"|ping -...                        1\n",
       "Misc. Comments' and 7702=77...                   1\n",
       "Misc. Comments'|echo y9v6fc...                   1\n",
       "Misc. Comments'|ping -c 21 ...                   1\n",
       "Miscellaneous Categories                        34\n",
       "Missed Collection                               55\n",
       "Missed Collection (All Materials)             8340\n",
       "Mobile Food Vendor                             383\n",
       "Mold                                            81\n",
       "Mosquitoes                                     139\n",
       "Municipal Parking Facility                      15\n",
       "New Tree Request                              3715\n",
       "Noise                                        10937\n",
       "Noise - Commercial                            7894\n",
       "Noise - Helicopter                             303\n",
       "Noise - House of Worship                       148\n",
       "Noise - Park                                   792\n",
       "Noise - Residential                          41311\n",
       "Noise - Street/Sidewalk                      16876\n",
       "Noise - Vehicle                               7244\n",
       "Non-Emergency Police Matter                   1628\n",
       "Non-Residential Heat                           169\n",
       "OEM Literature Request                          25\n",
       "OUTSIDE BUILDING                               150\n",
       "Other Enforcement                             1349\n",
       "Outside Building                                 2\n",
       "Overflowing Litter Baskets                     140\n",
       "Overflowing Recycling Baskets                    1\n",
       "Overgrown Tree/Branches                       2935\n",
       "PAINT/PLASTER                                 9877\n",
       "PLUMBING                                      9007\n",
       "Panhandling                                     96\n",
       "Parking Card                                     2\n",
       "Pet Shop                                        28\n",
       "Plant                                           22\n",
       "Plumbing                                      1482\n",
       "Poison Ivy                                      47\n",
       "Posting Advertisement                           23\n",
       "Public Assembly                                  1\n",
       "Public Payphone Complaint                       75\n",
       "Public Toilet                                    9\n",
       "Quality of Life                                 65\n",
       "Question                                       171\n",
       "Radioactive Material                             3\n",
       "Recycling Enforcement                          134\n",
       "Request Large Bulky Item Collection          30939\n",
       "Request Xmas Tree Collection                     2\n",
       "Research Questions                               4\n",
       "Rodent                                        6253\n",
       "Root/Sewer/Sidewalk Condition                 1485\n",
       "SAFETY                                        1668\n",
       "SCRIE                                          770\n",
       "Safety                                          65\n",
       "Sanitation Condition                          7151\n",
       "Scaffold Safety                                 38\n",
       "School Maintenance                             442\n",
       "Select Message Type...                           1\n",
       "Senior Center Complaint                         52\n",
       "Sewer                                         6757\n",
       "Sidewalk Condition                            6488\n",
       "Single Occupancy Bathroom                        4\n",
       "Smoking                                        456\n",
       "Snow                                           297\n",
       "Special Natural Area District (SNAD)            14\n",
       "Special Operations                              39\n",
       "Special Projects Inspection Team (SPIT)       1168\n",
       "Squeegee                                         1\n",
       "Standing Water                                 484\n",
       "Street Condition                             16895\n",
       "Street Light Condition                       13425\n",
       "Street Sign - Damaged                         1176\n",
       "Street Sign - Dangling                         482\n",
       "Street Sign - Missing                         1080\n",
       "Sustainability Enforcement                     247\n",
       "Sweeping/Inadequate                             55\n",
       "Sweeping/Missed                                174\n",
       "Sweeping/Missed-Inadequate                      11\n",
       "Tattooing                                       21\n",
       "Taxi Complaint                                2416\n",
       "Taxi Compliment                                 41\n",
       "Taxi Licensee Complaint                          5\n",
       "Taxi Report                                    318\n",
       "Taxpayer Advocate Inquiry                       19\n",
       "Traffic                                        868\n",
       "Traffic Signal Condition                      7306\n",
       "Transportation Provider Complaint                7\n",
       "Tunnel Condition                                 1\n",
       "UNSANITARY CONDITION                         14913\n",
       "Unleashed Dog                                  128\n",
       "Unlicensed Dog                                   6\n",
       "Unsanitary Animal Facility                      15\n",
       "Unsanitary Animal Pvt Property                 327\n",
       "Unsanitary Condition                           942\n",
       "Unsanitary Pigeon Condition                    103\n",
       "Urinating in Public                            112\n",
       "Vacant Lot                                     318\n",
       "Vending                                        873\n",
       "Violation of Park Rules                        492\n",
       "WATER LEAK                                    6641\n",
       "Water Conservation                             853\n",
       "Water Quality                                  332\n",
       "Water System                                 12949\n",
       "Window Guard                                     6\n",
       "X-Ray Machine/Equipment                          1\n",
       "\\windows\\win.ini                                 1\n",
       "eval(compile('for x in rang...                   1\n",
       "file:///c:/windows/win.ini                       1\n",
       "idexf3mrb7)(!(objectClass=*)                     1\n",
       "qfix4${695*589}lixaf                             1\n",
       "{!xmlparser v='<!DOCTYPE...                      1\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "with pd.option_context(\"display.max_rows\", 1000):\n",
    "    display(requests.groupby(\"Complaint Type\").size())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "```python\n",
    "with pd.option_context(\"display.max_rows\", 1000):\n",
    "    display(...)\n",
    "```\n",
    "\n",
    "What this code is doing: showing all cells in a DataFrame with [rich output](https://ipython.readthedocs.io/en/stable/interactive/plotting.html#rich-outputs)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "```python\n",
    "requests.groupby('Complaint Type').size()\n",
    "```\n",
    "\n",
    "What this code is doing:\n",
    "\n",
    "1. Group the records in the dataset based on their `Complaint Type` value\n",
    "1. Count the records that have been grouped together by their shared `Complaint Type` value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "How should we find junk records to delete?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "OlWlBvJCfvll",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "It looks like most invalid complaint types only have a few records. Try excluding all complaint type categories with < 3 records, assuming that all complaint type categories with < 3 instances in the data are bad data entries.\n",
    "\n",
    "Why 3? It's arbitrary. We're looking for trends in the data, and in this case we don't care about low frequency entries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Create a dataframe that captures the count of records per `Complaint Type` value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.015351Z",
     "iopub.status.busy": "2024-03-18T21:43:08.015216Z",
     "iopub.status.idle": "2024-03-18T21:43:08.040453Z",
     "shell.execute_reply": "2024-03-18T21:43:08.040194Z"
    },
    "id": "ebwuknSQgVkl",
    "outputId": "6a996116-3d05-4dab-9e12-176ae42e74e7",
    "slideshow": {
     "slide_type": "-"
    }
   },
   "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>Complaint Type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>$(sleep 11)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(select extractvalue(xmltyp...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>..././..././..././..././......</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>../../../../../../../../../...</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>../WEB-INF/web.xml</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>eval(compile('for x in rang...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>file:///c:/windows/win.ini</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>idexf3mrb7)(!(objectClass=*)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>qfix4${695*589}lixaf</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>{!xmlparser v='&lt;!DOCTYPE...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>248 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Complaint Type  count\n",
       "0                       $(sleep 11)      1\n",
       "1    (select extractvalue(xmltyp...      1\n",
       "2    ..././..././..././..././......      1\n",
       "3    ../../../../../../../../../...      2\n",
       "4                ../WEB-INF/web.xml      1\n",
       "..                              ...    ...\n",
       "243  eval(compile('for x in rang...      1\n",
       "244      file:///c:/windows/win.ini      1\n",
       "245    idexf3mrb7)(!(objectClass=*)      1\n",
       "246            qfix4${695*589}lixaf      1\n",
       "247     {!xmlparser v='<!DOCTYPE...      1\n",
       "\n",
       "[248 rows x 2 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counts = requests.groupby(\"Complaint Type\").size().reset_index(name=\"count\")\n",
    "counts\n",
    "# .reset_index(name='count') allows us to name the new column that contains the count of rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "_vsxKXnto7RQ",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "You can also use `.count()` but [the output is a little different](https://stackoverflow.com/questions/33346591/what-is-the-difference-between-size-and-count-in-pandas)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Let's create a \"series\" that only lists the `Complaint Type` values that have record counts >= 3.**\n",
    "\n",
    "Remember: A single column from a pandas dataframe is called a series. It's essentially a list containing all the values in the column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "```python\n",
    "requests.groupby('Complaint Type').size().reset_index(name='size')\n",
    "```\n",
    "\n",
    "is equivalent to:\n",
    "\n",
    "```python\n",
    "requests.groupby('Complaint Type', as_index=False).size()\n",
    "```\n",
    "\n",
    "Without the `reset_index()` or `as_index=False`, the `Complaint Type` values are returned as the index. See also: [`value_counts()`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.value_counts.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.042269Z",
     "iopub.status.busy": "2024-03-18T21:43:08.042142Z",
     "iopub.status.idle": "2024-03-18T21:43:08.044993Z",
     "shell.execute_reply": "2024-03-18T21:43:08.044740Z"
    },
    "id": "dztWQGD3uxbD",
    "outputId": "62507280-0ba5-4cb0-f809-d4b8d0c2fbf6",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6                          APPLIANCE\n",
       "7                  Abandoned Vehicle\n",
       "8                   Advocate - Other\n",
       "11     Advocate-Co-opCondo Abatement\n",
       "12      Advocate-Personal Exemptions\n",
       "                   ...              \n",
       "236                       WATER LEAK\n",
       "237               Water Conservation\n",
       "238                    Water Quality\n",
       "239                     Water System\n",
       "240                     Window Guard\n",
       "Name: Complaint Type, Length: 213, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "valid_complaint_types = counts[\"Complaint Type\"][counts[\"count\"] >= 3]\n",
    "valid_complaint_types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5AHFe3Nhp1gz",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Filter our `requests` dataframe to only keep the rows where the `Complaint Type` value is in the `valid_complaint_types` series we created in the previous step. Save the result in a new dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.046491Z",
     "iopub.status.busy": "2024-03-18T21:43:08.046388Z",
     "iopub.status.idle": "2024-03-18T21:43:08.169343Z",
     "shell.execute_reply": "2024-03-18T21:43:08.169044Z"
    },
    "id": "PII26jb0g8Eg",
    "outputId": "8c839d5d-da47-4576-b1cb-86a147ec52e7"
   },
   "outputs": [],
   "source": [
    "df_cleaned = requests[requests[\"Complaint Type\"].isin(valid_complaint_types)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "How can we make sure this worked? Let's check how many records there were originally in `requests` vs how many are in `df_cleaned`.\n",
    "\n",
    "Before:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.171151Z",
     "iopub.status.busy": "2024-03-18T21:43:08.171052Z",
     "iopub.status.idle": "2024-03-18T21:43:08.173266Z",
     "shell.execute_reply": "2024-03-18T21:43:08.173028Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "500000"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests[\"Unique Key\"].size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.174939Z",
     "iopub.status.busy": "2024-03-18T21:43:08.174811Z",
     "iopub.status.idle": "2024-03-18T21:43:08.177060Z",
     "shell.execute_reply": "2024-03-18T21:43:08.176785Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "499958"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cleaned[\"Unique Key\"].size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "We can also print the set of complaint_type values from our cleaned dataframe to make sure they look correct."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.178630Z",
     "iopub.status.busy": "2024-03-18T21:43:08.178519Z",
     "iopub.status.idle": "2024-03-18T21:43:08.186941Z",
     "shell.execute_reply": "2024-03-18T21:43:08.186683Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "349480                         Borough Office\n",
       "254578                               PLUMBING\n",
       "198644    Request Large Bulky Item Collection\n",
       "319316                 Street Light Condition\n",
       "29580           General Construction/Plumbing\n",
       "398404                 Street Light Condition\n",
       "303870                 HPD Literature Request\n",
       "86399                                   Sewer\n",
       "22631                 Noise - Street/Sidewalk\n",
       "398130                        Illegal Parking\n",
       "Name: Complaint Type, dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cleaned[\"Complaint Type\"].sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hNv98pVghJ1J",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Great, now those invalid records will be excluded from our analysis!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Another approach to excluding those invalid records would be to use [regular expressions (\"RegExes\")](https://www.w3schools.com/python/python_regex.asp) to find records with weird characters."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Filtering rows\n",
    "\n",
    "Slicing and dicing is done through [indexing](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "![DataFrame](extras/img/data_structures-2.jpg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Boolean indexing\n",
    "\n",
    "![DataFrame and Series](extras/img/data_structures-3.jpg)\n",
    "\n",
    "_The table on the right is missing the `state` column - need to fix._"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### How it works"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<video controls width=\"700\" src=\"https://github.com/afeld/python-public-policy/raw/main/extras/img/boolean-indexing.mp4\"></video>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "SLUy01x4h_I3",
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Done with clean up! Time for the actual analysis: \n",
    "### Which 311 complaints are most common and which agencies are responsible for handling them?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HkmnJPxfiJrY",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Which complaints are the most common?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.188911Z",
     "iopub.status.busy": "2024-03-18T21:43:08.188796Z",
     "iopub.status.idle": "2024-03-18T21:43:08.213778Z",
     "shell.execute_reply": "2024-03-18T21:43:08.213514Z"
    },
    "id": "S90gXzXAiFoC",
    "outputId": "7fb263d5-a01c-42ca-ee21-267a0c0c18bf"
   },
   "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>Complaint Type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>APPLIANCE</td>\n",
       "      <td>2539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Abandoned Vehicle</td>\n",
       "      <td>1655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Advocate - Other</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Advocate-Co-opCondo Abatement</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Advocate-Personal Exemptions</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>WATER LEAK</td>\n",
       "      <td>6641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>Water Conservation</td>\n",
       "      <td>853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>Water Quality</td>\n",
       "      <td>332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>Water System</td>\n",
       "      <td>12949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>Window Guard</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>213 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Complaint Type  count\n",
       "0                        APPLIANCE   2539\n",
       "1                Abandoned Vehicle   1655\n",
       "2                 Advocate - Other     26\n",
       "3    Advocate-Co-opCondo Abatement      4\n",
       "4     Advocate-Personal Exemptions     24\n",
       "..                             ...    ...\n",
       "208                     WATER LEAK   6641\n",
       "209             Water Conservation    853\n",
       "210                  Water Quality    332\n",
       "211                   Water System  12949\n",
       "212                   Window Guard      6\n",
       "\n",
       "[213 rows x 2 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cleaned.groupby(\"Complaint Type\").size().reset_index(name=\"count\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`.reset_index(name='count')` isn't necessary, but it's helpful to include because it allows us to name the new column that contains the count of rows."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "J8kKgIZdiOom",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Which agencies are responsible for handling these complaint categories?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.215610Z",
     "iopub.status.busy": "2024-03-18T21:43:08.215488Z",
     "iopub.status.idle": "2024-03-18T21:43:08.258566Z",
     "shell.execute_reply": "2024-03-18T21:43:08.258285Z"
    },
    "id": "UVRyyXt8iUgF",
    "outputId": "b570ace6-1864-48b3-8ff7-17d5a8b1e024"
   },
   "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>Agency</th>\n",
       "      <th>Complaint Type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACS</td>\n",
       "      <td>Forms</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COIB</td>\n",
       "      <td>Forms</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DCA</td>\n",
       "      <td>Consumer Complaint</td>\n",
       "      <td>2892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DCA</td>\n",
       "      <td>DCA / DOH New License Application Request</td>\n",
       "      <td>186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DCAS</td>\n",
       "      <td>Comments</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>TLC</td>\n",
       "      <td>Lost Property</td>\n",
       "      <td>472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>TLC</td>\n",
       "      <td>Taxi Complaint</td>\n",
       "      <td>2416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>TLC</td>\n",
       "      <td>Taxi Compliment</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>TLC</td>\n",
       "      <td>Taxi Licensee Complaint</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>TLC</td>\n",
       "      <td>Taxi Report</td>\n",
       "      <td>318</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>221 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Agency                             Complaint Type  count\n",
       "0      ACS                                      Forms     56\n",
       "1     COIB                                      Forms      1\n",
       "2      DCA                         Consumer Complaint   2892\n",
       "3      DCA  DCA / DOH New License Application Request    186\n",
       "4     DCAS                                   Comments     13\n",
       "..     ...                                        ...    ...\n",
       "216    TLC                              Lost Property    472\n",
       "217    TLC                             Taxi Complaint   2416\n",
       "218    TLC                            Taxi Compliment     41\n",
       "219    TLC                    Taxi Licensee Complaint      5\n",
       "220    TLC                                Taxi Report    318\n",
       "\n",
       "[221 rows x 3 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cleaned.groupby([\"Agency\", \"Complaint Type\"]).size().reset_index(name=\"count\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hwUfwRFIianT",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Which agencies receive the most total 311 requests?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.260176Z",
     "iopub.status.busy": "2024-03-18T21:43:08.260059Z",
     "iopub.status.idle": "2024-03-18T21:43:08.279343Z",
     "shell.execute_reply": "2024-03-18T21:43:08.279069Z"
    },
    "id": "c8go2qe9iZiE",
    "outputId": "21a6f3b2-da1e-4dd3-c68f-ba1f03f2b66d"
   },
   "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>Agency</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACS</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COIB</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DCA</td>\n",
       "      <td>3078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DCAS</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DCP</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DEP</td>\n",
       "      <td>36283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DFTA</td>\n",
       "      <td>1182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DHS</td>\n",
       "      <td>3670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DOB</td>\n",
       "      <td>26024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DOE</td>\n",
       "      <td>442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>DOF</td>\n",
       "      <td>7223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>DOHMH</td>\n",
       "      <td>12690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>DOITT</td>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>DOT</td>\n",
       "      <td>52001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>DPR</td>\n",
       "      <td>21086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>DSNY</td>\n",
       "      <td>73227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>EDC</td>\n",
       "      <td>303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>HPD</td>\n",
       "      <td>105634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>HRA</td>\n",
       "      <td>1649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>NYCEM</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NYPD</td>\n",
       "      <td>148884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>TAT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>TAX</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>TLC</td>\n",
       "      <td>6194</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Agency   count\n",
       "0     ACS      56\n",
       "1    COIB       1\n",
       "2     DCA    3078\n",
       "3    DCAS     149\n",
       "4     DCP       4\n",
       "5     DEP   36283\n",
       "6    DFTA    1182\n",
       "7     DHS    3670\n",
       "8     DOB   26024\n",
       "9     DOE     442\n",
       "10    DOF    7223\n",
       "11  DOHMH   12690\n",
       "12  DOITT     115\n",
       "13    DOT   52001\n",
       "14    DPR   21086\n",
       "15   DSNY   73227\n",
       "16    EDC     303\n",
       "17    HPD  105634\n",
       "18    HRA    1649\n",
       "19  NYCEM      25\n",
       "20   NYPD  148884\n",
       "21    TAT       1\n",
       "22    TAX      37\n",
       "23    TLC    6194"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cleaned.groupby(\"Agency\").size().reset_index(name=\"count\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "S6ERw200iseN",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### What is the most frequent request per agency?\n",
    "\n",
    "First, create a dataframe that contains the count of complaints per `Agency` per `Complaint Type`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.281041Z",
     "iopub.status.busy": "2024-03-18T21:43:08.280931Z",
     "iopub.status.idle": "2024-03-18T21:43:08.324166Z",
     "shell.execute_reply": "2024-03-18T21:43:08.323887Z"
    }
   },
   "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>Agency</th>\n",
       "      <th>Complaint Type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACS</td>\n",
       "      <td>Forms</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COIB</td>\n",
       "      <td>Forms</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DCA</td>\n",
       "      <td>Consumer Complaint</td>\n",
       "      <td>2892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DCA</td>\n",
       "      <td>DCA / DOH New License Application Request</td>\n",
       "      <td>186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DCAS</td>\n",
       "      <td>Comments</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DCAS</td>\n",
       "      <td>Question</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DCP</td>\n",
       "      <td>Research Questions</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Air Quality</td>\n",
       "      <td>1457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Asbestos</td>\n",
       "      <td>316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DEP</td>\n",
       "      <td>FATF</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Hazardous Materials</td>\n",
       "      <td>549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Industrial Waste</td>\n",
       "      <td>263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Lead</td>\n",
       "      <td>1825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Noise</td>\n",
       "      <td>10937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Plant</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>6757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Water Conservation</td>\n",
       "      <td>853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Water Quality</td>\n",
       "      <td>332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Water System</td>\n",
       "      <td>12949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>DFTA</td>\n",
       "      <td>Alzheimer's Care</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Agency                             Complaint Type  count\n",
       "0     ACS                                      Forms     56\n",
       "1    COIB                                      Forms      1\n",
       "2     DCA                         Consumer Complaint   2892\n",
       "3     DCA  DCA / DOH New License Application Request    186\n",
       "4    DCAS                                   Comments     13\n",
       "5    DCAS                                   Question    136\n",
       "6     DCP                         Research Questions      4\n",
       "7     DEP                                Air Quality   1457\n",
       "8     DEP                                   Asbestos    316\n",
       "9     DEP                                       FATF     23\n",
       "10    DEP                        Hazardous Materials    549\n",
       "11    DEP                           Industrial Waste    263\n",
       "12    DEP                                       Lead   1825\n",
       "13    DEP                                      Noise  10937\n",
       "14    DEP                                      Plant     22\n",
       "15    DEP                                      Sewer   6757\n",
       "16    DEP                         Water Conservation    853\n",
       "17    DEP                              Water Quality    332\n",
       "18    DEP                               Water System  12949\n",
       "19   DFTA                           Alzheimer's Care     25"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agency_counts = df_cleaned.groupby([\"Agency\", \"Complaint Type\"]).size().reset_index(name=\"count\")\n",
    "agency_counts.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Use `drop_duplicates()` to keep the row with the highest value per `Agency`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.325980Z",
     "iopub.status.busy": "2024-03-18T21:43:08.325857Z",
     "iopub.status.idle": "2024-03-18T21:43:08.330577Z",
     "shell.execute_reply": "2024-03-18T21:43:08.330304Z"
    },
    "id": "_3o0pW8Xixt0",
    "outputId": "d6e82795-7abb-4c71-b712-58b26adb55dd",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>Agency</th>\n",
       "      <th>Complaint Type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>NYPD</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>41311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>HPD</td>\n",
       "      <td>HEAT/HOT WATER</td>\n",
       "      <td>39095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>NYPD</td>\n",
       "      <td>Illegal Parking</td>\n",
       "      <td>34297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>DSNY</td>\n",
       "      <td>Request Large Bulky Item Collection</td>\n",
       "      <td>30939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>NYPD</td>\n",
       "      <td>Blocked Driveway</td>\n",
       "      <td>25530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>DOT</td>\n",
       "      <td>Highway Sign - Dangling</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Radioactive Material</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>DPR</td>\n",
       "      <td>Unsanitary Condition</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>TAT</td>\n",
       "      <td>Question</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COIB</td>\n",
       "      <td>Forms</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>221 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Agency                       Complaint Type  count\n",
       "198   NYPD                  Noise - Residential  41311\n",
       "169    HPD                       HEAT/HOT WATER  39095\n",
       "194   NYPD                      Illegal Parking  34297\n",
       "154   DSNY  Request Large Bulky Item Collection  30939\n",
       "186   NYPD                     Blocked Driveway  25530\n",
       "..     ...                                  ...    ...\n",
       "119    DOT              Highway Sign - Dangling      3\n",
       "94   DOHMH                 Radioactive Material      3\n",
       "139    DPR                 Unsanitary Condition      1\n",
       "207    TAT                             Question      1\n",
       "1     COIB                                Forms      1\n",
       "\n",
       "[221 rows x 3 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted_agency_counts = agency_counts.sort_values(\"count\", ascending=False)\n",
    "sorted_agency_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.332354Z",
     "iopub.status.busy": "2024-03-18T21:43:08.332209Z",
     "iopub.status.idle": "2024-03-18T21:43:08.336822Z",
     "shell.execute_reply": "2024-03-18T21:43:08.336511Z"
    },
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>Agency</th>\n",
       "      <th>Complaint Type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>NYPD</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>41311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>HPD</td>\n",
       "      <td>HEAT/HOT WATER</td>\n",
       "      <td>39095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>DSNY</td>\n",
       "      <td>Request Large Bulky Item Collection</td>\n",
       "      <td>30939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>DOT</td>\n",
       "      <td>Street Condition</td>\n",
       "      <td>16895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Water System</td>\n",
       "      <td>12949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>DOB</td>\n",
       "      <td>General Construction/Plumbing</td>\n",
       "      <td>9537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>6253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>DPR</td>\n",
       "      <td>Damaged Tree</td>\n",
       "      <td>6065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>DHS</td>\n",
       "      <td>Homeless Person Assistance</td>\n",
       "      <td>3670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DCA</td>\n",
       "      <td>Consumer Complaint</td>\n",
       "      <td>2892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>TLC</td>\n",
       "      <td>For Hire Vehicle Complaint</td>\n",
       "      <td>2789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>DOF</td>\n",
       "      <td>DOF Property - Reduction Issue</td>\n",
       "      <td>1846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>HRA</td>\n",
       "      <td>Benefit Card Replacement</td>\n",
       "      <td>1649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>DFTA</td>\n",
       "      <td>Housing - Low Income Senior</td>\n",
       "      <td>768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>DOE</td>\n",
       "      <td>School Maintenance</td>\n",
       "      <td>442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>EDC</td>\n",
       "      <td>Noise - Helicopter</td>\n",
       "      <td>303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DCAS</td>\n",
       "      <td>Question</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>DOITT</td>\n",
       "      <td>Public Payphone Complaint</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACS</td>\n",
       "      <td>Forms</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>TAX</td>\n",
       "      <td>Question</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>NYCEM</td>\n",
       "      <td>OEM Literature Request</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DCP</td>\n",
       "      <td>Research Questions</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>TAT</td>\n",
       "      <td>Question</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COIB</td>\n",
       "      <td>Forms</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Agency                       Complaint Type  count\n",
       "198   NYPD                  Noise - Residential  41311\n",
       "169    HPD                       HEAT/HOT WATER  39095\n",
       "154   DSNY  Request Large Bulky Item Collection  30939\n",
       "124    DOT                     Street Condition  16895\n",
       "18     DEP                         Water System  12949\n",
       "42     DOB        General Construction/Plumbing   9537\n",
       "95   DOHMH                               Rodent   6253\n",
       "132    DPR                         Damaged Tree   6065\n",
       "29     DHS           Homeless Person Assistance   3670\n",
       "2      DCA                   Consumer Complaint   2892\n",
       "212    TLC           For Hire Vehicle Complaint   2789\n",
       "67     DOF       DOF Property - Reduction Issue   1846\n",
       "180    HRA             Benefit Card Replacement   1649\n",
       "25    DFTA          Housing - Low Income Senior    768\n",
       "53     DOE                   School Maintenance    442\n",
       "161    EDC                   Noise - Helicopter    303\n",
       "5     DCAS                             Question    136\n",
       "106  DOITT            Public Payphone Complaint     75\n",
       "0      ACS                                Forms     56\n",
       "209    TAX                             Question     34\n",
       "181  NYCEM               OEM Literature Request     25\n",
       "6      DCP                   Research Questions      4\n",
       "207    TAT                             Question      1\n",
       "1     COIB                                Forms      1"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_agency_counts = sorted_agency_counts.drop_duplicates(\"Agency\")\n",
    "top_agency_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "For nicer display, order alphabetically:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.338710Z",
     "iopub.status.busy": "2024-03-18T21:43:08.338578Z",
     "iopub.status.idle": "2024-03-18T21:43:08.342457Z",
     "shell.execute_reply": "2024-03-18T21:43:08.342170Z"
    },
    "slideshow": {
     "slide_type": "-"
    }
   },
   "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>Agency</th>\n",
       "      <th>Complaint Type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACS</td>\n",
       "      <td>Forms</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COIB</td>\n",
       "      <td>Forms</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DCA</td>\n",
       "      <td>Consumer Complaint</td>\n",
       "      <td>2892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DCAS</td>\n",
       "      <td>Question</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DCP</td>\n",
       "      <td>Research Questions</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Water System</td>\n",
       "      <td>12949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>DFTA</td>\n",
       "      <td>Housing - Low Income Senior</td>\n",
       "      <td>768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>DHS</td>\n",
       "      <td>Homeless Person Assistance</td>\n",
       "      <td>3670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>DOB</td>\n",
       "      <td>General Construction/Plumbing</td>\n",
       "      <td>9537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>DOE</td>\n",
       "      <td>School Maintenance</td>\n",
       "      <td>442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>DOF</td>\n",
       "      <td>DOF Property - Reduction Issue</td>\n",
       "      <td>1846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>6253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>DOITT</td>\n",
       "      <td>Public Payphone Complaint</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>DOT</td>\n",
       "      <td>Street Condition</td>\n",
       "      <td>16895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>DPR</td>\n",
       "      <td>Damaged Tree</td>\n",
       "      <td>6065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>DSNY</td>\n",
       "      <td>Request Large Bulky Item Collection</td>\n",
       "      <td>30939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>EDC</td>\n",
       "      <td>Noise - Helicopter</td>\n",
       "      <td>303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>HPD</td>\n",
       "      <td>HEAT/HOT WATER</td>\n",
       "      <td>39095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>HRA</td>\n",
       "      <td>Benefit Card Replacement</td>\n",
       "      <td>1649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>NYCEM</td>\n",
       "      <td>OEM Literature Request</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>NYPD</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>41311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>TAT</td>\n",
       "      <td>Question</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>TAX</td>\n",
       "      <td>Question</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>TLC</td>\n",
       "      <td>For Hire Vehicle Complaint</td>\n",
       "      <td>2789</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Agency                       Complaint Type  count\n",
       "0      ACS                                Forms     56\n",
       "1     COIB                                Forms      1\n",
       "2      DCA                   Consumer Complaint   2892\n",
       "5     DCAS                             Question    136\n",
       "6      DCP                   Research Questions      4\n",
       "18     DEP                         Water System  12949\n",
       "25    DFTA          Housing - Low Income Senior    768\n",
       "29     DHS           Homeless Person Assistance   3670\n",
       "42     DOB        General Construction/Plumbing   9537\n",
       "53     DOE                   School Maintenance    442\n",
       "67     DOF       DOF Property - Reduction Issue   1846\n",
       "95   DOHMH                               Rodent   6253\n",
       "106  DOITT            Public Payphone Complaint     75\n",
       "124    DOT                     Street Condition  16895\n",
       "132    DPR                         Damaged Tree   6065\n",
       "154   DSNY  Request Large Bulky Item Collection  30939\n",
       "161    EDC                   Noise - Helicopter    303\n",
       "169    HPD                       HEAT/HOT WATER  39095\n",
       "180    HRA             Benefit Card Replacement   1649\n",
       "181  NYCEM               OEM Literature Request     25\n",
       "198   NYPD                  Noise - Residential  41311\n",
       "207    TAT                             Question      1\n",
       "209    TAX                             Question     34\n",
       "212    TLC           For Hire Vehicle Complaint   2789"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_agency_counts.sort_values(\"Agency\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Another way, only sorting it once:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-18T21:43:08.343961Z",
     "iopub.status.busy": "2024-03-18T21:43:08.343870Z",
     "iopub.status.idle": "2024-03-18T21:43:08.348591Z",
     "shell.execute_reply": "2024-03-18T21:43:08.348320Z"
    }
   },
   "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>Agency</th>\n",
       "      <th>Complaint Type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACS</td>\n",
       "      <td>Forms</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COIB</td>\n",
       "      <td>Forms</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DCA</td>\n",
       "      <td>Consumer Complaint</td>\n",
       "      <td>2892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DCAS</td>\n",
       "      <td>Question</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DCP</td>\n",
       "      <td>Research Questions</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>DEP</td>\n",
       "      <td>Water System</td>\n",
       "      <td>12949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>DFTA</td>\n",
       "      <td>Housing - Low Income Senior</td>\n",
       "      <td>768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>DHS</td>\n",
       "      <td>Homeless Person Assistance</td>\n",
       "      <td>3670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>DOB</td>\n",
       "      <td>General Construction/Plumbing</td>\n",
       "      <td>9537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>DOE</td>\n",
       "      <td>School Maintenance</td>\n",
       "      <td>442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>DOF</td>\n",
       "      <td>DOF Property - Reduction Issue</td>\n",
       "      <td>1846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>6253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>DOITT</td>\n",
       "      <td>Public Payphone Complaint</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>DOT</td>\n",
       "      <td>Street Condition</td>\n",
       "      <td>16895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>DPR</td>\n",
       "      <td>Damaged Tree</td>\n",
       "      <td>6065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>DSNY</td>\n",
       "      <td>Request Large Bulky Item Collection</td>\n",
       "      <td>30939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>EDC</td>\n",
       "      <td>Noise - Helicopter</td>\n",
       "      <td>303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>HPD</td>\n",
       "      <td>HEAT/HOT WATER</td>\n",
       "      <td>39095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>HRA</td>\n",
       "      <td>Benefit Card Replacement</td>\n",
       "      <td>1649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>NYCEM</td>\n",
       "      <td>OEM Literature Request</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>NYPD</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>41311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>TAT</td>\n",
       "      <td>Question</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>TAX</td>\n",
       "      <td>Question</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>TLC</td>\n",
       "      <td>For Hire Vehicle Complaint</td>\n",
       "      <td>2789</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Agency                       Complaint Type  count\n",
       "0      ACS                                Forms     56\n",
       "1     COIB                                Forms      1\n",
       "2      DCA                   Consumer Complaint   2892\n",
       "5     DCAS                             Question    136\n",
       "6      DCP                   Research Questions      4\n",
       "18     DEP                         Water System  12949\n",
       "25    DFTA          Housing - Low Income Senior    768\n",
       "29     DHS           Homeless Person Assistance   3670\n",
       "42     DOB        General Construction/Plumbing   9537\n",
       "53     DOE                   School Maintenance    442\n",
       "67     DOF       DOF Property - Reduction Issue   1846\n",
       "95   DOHMH                               Rodent   6253\n",
       "106  DOITT            Public Payphone Complaint     75\n",
       "124    DOT                     Street Condition  16895\n",
       "132    DPR                         Damaged Tree   6065\n",
       "154   DSNY  Request Large Bulky Item Collection  30939\n",
       "161    EDC                   Noise - Helicopter    303\n",
       "169    HPD                       HEAT/HOT WATER  39095\n",
       "180    HRA             Benefit Card Replacement   1649\n",
       "181  NYCEM               OEM Literature Request     25\n",
       "198   NYPD                  Noise - Residential  41311\n",
       "207    TAT                             Question      1\n",
       "209    TAX                             Question     34\n",
       "212    TLC           For Hire Vehicle Complaint   2789"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agency_counts.sort_values([\"Agency\", \"count\"]).drop_duplicates(\"Agency\", keep=\"last\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Some pandas/Jupyter best practices\n",
    "\n",
    "- Make variable names descriptive\n",
    "    - Ignore that all examples use `requests`\n",
    "- Only do one thing per line\n",
    "    - Makes troubleshooting easier\n",
    "- Make notebooks [idempotent](https://en.wikipedia.org/wiki/Idempotence)\n",
    "    - Makes your work reproducible\n",
    "    - Use `Restart and run all` (⏩ button in toolbar)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ddj8VVZRixCn",
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## [Homework 1](https://python-public-policy.afeld.me/en/{{school_slug}}/hw_1.html)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python [conda env:python-public-policy] *",
   "language": "python",
   "name": "conda-env-python-public-policy-py"
  },
  "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": 4
}