lecture_1_demo_solutions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Lecture 1 demo solutions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "## Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "editable": true,
    "id": "Q4l2QosJLzP6",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/kr/nx0m1j811kz5vy8c87ffchzr0000gn/T/ipykernel_69550/3437185872.py:3: 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": [
    "import pandas as pd\n",
    "\n",
    "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": {
    "editable": true,
    "id": "SLUy01x4h_I3",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "## Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "id": "HkmnJPxfiJrY",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "### Which complaints are most common?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Complaint Type\n",
       "Noise - Residential                    41311\n",
       "HEAT/HOT WATER                         39095\n",
       "Illegal Parking                        34297\n",
       "Request Large Bulky Item Collection    30939\n",
       "Blocked Driveway                       25530\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests[\"Complaint Type\"].value_counts().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "Equivalent to:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "editable": true,
    "id": "S90gXzXAiFoC",
    "outputId": "7fb263d5-a01c-42ca-ee21-267a0c0c18bf",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Complaint Type\n",
       "Noise - Residential                    41311\n",
       "HEAT/HOT WATER                         39095\n",
       "Illegal Parking                        34297\n",
       "Request Large Bulky Item Collection    30939\n",
       "Blocked Driveway                       25530\n",
       "dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests.groupby(\"Complaint Type\").size().nlargest()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "id": "S6ERw200iseN",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "### What's the most frequent request per agency?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Agency</th>\n",
       "      <th>Complaint Type</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ACS</th>\n",
       "      <th>Forms</th>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COIB</th>\n",
       "      <th>Forms</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">DCA</th>\n",
       "      <th>Consumer Complaint</th>\n",
       "      <td>2892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DCA / DOH New License Application Request</th>\n",
       "      <td>186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DCAS</th>\n",
       "      <th>Comments</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">TLC</th>\n",
       "      <th>Lost Property</th>\n",
       "      <td>472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Taxi Complaint</th>\n",
       "      <td>2416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Taxi Compliment</th>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Taxi Licensee Complaint</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Taxi Report</th>\n",
       "      <td>318</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>256 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  count\n",
       "Agency Complaint Type                                  \n",
       "ACS    Forms                                         56\n",
       "COIB   Forms                                          1\n",
       "DCA    Consumer Complaint                          2892\n",
       "       DCA / DOH New License Application Request    186\n",
       "DCAS   Comments                                      13\n",
       "...                                                 ...\n",
       "TLC    Lost Property                                472\n",
       "       Taxi Complaint                              2416\n",
       "       Taxi Compliment                               41\n",
       "       Taxi Licensee Complaint                        5\n",
       "       Taxi Report                                  318\n",
       "\n",
       "[256 rows x 1 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests.groupby([\"Agency\", \"Complaint Type\"]).size().to_frame(name=\"count\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "id": "qeYA8-rMlpJa",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "## Exclude bad records from the DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "Let's look at the complaint types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true,
    "scrolled": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests[\"Complaint Type\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "Let's make that a bit easier to read:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true,
    "scrolled": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complaints = requests[\"Complaint Type\"].unique()\n",
    "complaints.sort()\n",
    "list(complaints)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "id": "RrwqSmKSbiYC",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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. See also: [`value_counts()`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.value_counts.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "editable": true,
    "id": "XJ2AK37yb3_E",
    "outputId": "1b7a0085-3826-4b8d-d7b6-57ab60c6db21",
    "scrolled": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "How should we find junk records to delete?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "id": "OlWlBvJCfvll",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "Create a DataFrame that captures the count of records per `Complaint Type` value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true,
    "id": "ebwuknSQgVkl",
    "outputId": "6a996116-3d05-4dab-9e12-176ae42e74e7",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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": 8,
     "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": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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": "code",
   "execution_count": 9,
   "metadata": {
    "editable": true,
    "id": "dztWQGD3uxbD",
    "outputId": "62507280-0ba5-4cb0-f809-d4b8d0c2fbf6",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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>6</th>\n",
       "      <td>APPLIANCE</td>\n",
       "      <td>2539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Abandoned Vehicle</td>\n",
       "      <td>1655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Advocate - Other</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Advocate-Co-opCondo Abatement</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</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>236</th>\n",
       "      <td>WATER LEAK</td>\n",
       "      <td>6641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>Water Conservation</td>\n",
       "      <td>853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238</th>\n",
       "      <td>Water Quality</td>\n",
       "      <td>332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>Water System</td>\n",
       "      <td>12949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240</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",
       "6                        APPLIANCE   2539\n",
       "7                Abandoned Vehicle   1655\n",
       "8                 Advocate - Other     26\n",
       "11   Advocate-Co-opCondo Abatement      4\n",
       "12    Advocate-Personal Exemptions     24\n",
       "..                             ...    ...\n",
       "236                     WATER LEAK   6641\n",
       "237             Water Conservation    853\n",
       "238                  Water Quality    332\n",
       "239                   Water System  12949\n",
       "240                   Window Guard      6\n",
       "\n",
       "[213 rows x 2 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "valid_complaint_counts = counts[counts[\"count\"] >= 3]\n",
    "valid_complaint_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "id": "5AHFe3Nhp1gz",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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": 10,
   "metadata": {
    "editable": true,
    "id": "PII26jb0g8Eg",
    "outputId": "8c839d5d-da47-4576-b1cb-86a147ec52e7",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "valid_complaint_types = valid_complaint_counts[\"Complaint Type\"]\n",
    "is_valid_complaint = requests[\"Complaint Type\"].isin(valid_complaint_types)\n",
    "requests_clean = requests[is_valid_complaint]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "How can we make sure this worked? Let's check how many records there were originally in `requests` vs how many are in `requests_clean`.\n",
    "\n",
    "Before:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "500000"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests[\"Unique Key\"].size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "After:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "499958"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests_clean[\"Unique Key\"].size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "id": "hNv98pVghJ1J",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "Great, now those invalid records will be excluded from our analysis!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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."
   ]
  }
 ],
 "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.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}