Working Notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Depedencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from pathlib import Path\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Analysis & Manipulation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Census Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_data_path = str(Path.cwd().joinpath('calls/census_data/census_data.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_df = pd.read_csv(census_data_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GEO.id</th>\n",
       "      <th>GEO.id2</th>\n",
       "      <th>GEO.display-label</th>\n",
       "      <th>D001</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Id</td>\n",
       "      <td>Id2</td>\n",
       "      <td>Geography</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8600000US06390</td>\n",
       "      <td>06390</td>\n",
       "      <td>ZCTA5 06390</td>\n",
       "      <td>236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8600000US10001</td>\n",
       "      <td>10001</td>\n",
       "      <td>ZCTA5 10001</td>\n",
       "      <td>21102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8600000US10002</td>\n",
       "      <td>10002</td>\n",
       "      <td>ZCTA5 10002</td>\n",
       "      <td>81410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8600000US10003</td>\n",
       "      <td>10003</td>\n",
       "      <td>ZCTA5 10003</td>\n",
       "      <td>56024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           GEO.id GEO.id2 GEO.display-label   D001\n",
       "0              Id     Id2         Geography  Total\n",
       "1  8600000US06390   06390       ZCTA5 06390    236\n",
       "2  8600000US10001   10001       ZCTA5 10001  21102\n",
       "3  8600000US10002   10002       ZCTA5 10002  81410\n",
       "4  8600000US10003   10003       ZCTA5 10003  56024"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Database Connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_path = str(Path.cwd().joinpath('calls/app.db'))\n",
    "conn = sqlite3.connect(db_path)\n",
    "# cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = pd.read_sql_query('select * from locations', conn, index_col = \"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.zip = db.zip.astype('float', inplace = True)\n",
    "db.zip = db.zip.astype('int')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zip</th>\n",
       "      <th>borough_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10027</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11223</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10461</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10002</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>11385</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      zip  borough_id\n",
       "id                   \n",
       "1   10027           1\n",
       "2   11223           2\n",
       "3   10461           3\n",
       "4   10002           1\n",
       "5   11385           4"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading in data\n",
    "Data read in and non-relevent columns dropped (same as from the original)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2785: DtypeWarning: Columns (17) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "raw_data = pd.read_csv('calls/everything.csv', index_col=0, dtype={'incident_zip': str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_data.drop(columns = ['Incident Address', 'Street Name', 'Cross Street 1', 'Cross Street 2',\n",
    "       'Intersection Street 1', 'Intersection Street 2', 'Address Type',\n",
    "       'City', 'Landmark', 'Park Facility Name', 'Park Borough', 'Vehicle Type',\n",
    "       'Taxi Company Borough', 'Taxi Pick Up Location', 'Bridge Highway Name',\n",
    "       'Bridge Highway Direction', 'Road Ramp', 'Bridge Highway Segment',\n",
    "       'Latitude', 'Longitude', 'Status', 'Facility Type', 'Due Date', 'Resolution Description',\n",
    "       'Resolution Action Updated Date', 'Community Board', 'BBL',], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Created Date', 'Closed Date', 'Agency', 'Agency Name',\n",
       "       'Complaint Type', 'Descriptor', 'Location Type', 'Incident Zip',\n",
       "       'Borough', 'X Coordinate (State Plane)', 'Y Coordinate (State Plane)',\n",
       "       'Open Data Channel Type', 'Location'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>Borough</th>\n",
       "      <th>X Coordinate (State Plane)</th>\n",
       "      <th>Y Coordinate (State Plane)</th>\n",
       "      <th>Open Data Channel Type</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unique Key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38490120</th>\n",
       "      <td>02/17/2018 10:03:19 AM</td>\n",
       "      <td>02/17/2018 10:42:22 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>10027.0</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>995846.0</td>\n",
       "      <td>236544.0</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>(40.81592674227958, -73.95810673319008)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Created Date             Closed Date Agency  \\\n",
       "Unique Key                                                          \n",
       "38490120    02/17/2018 10:03:19 AM  02/17/2018 10:42:22 AM   NYPD   \n",
       "\n",
       "                                Agency Name       Complaint Type  \\\n",
       "Unique Key                                                         \n",
       "38490120    New York City Police Department  Noise - Residential   \n",
       "\n",
       "                  Descriptor               Location Type  Incident Zip  \\\n",
       "Unique Key                                                               \n",
       "38490120    Loud Music/Party  Residential Building/House       10027.0   \n",
       "\n",
       "              Borough  X Coordinate (State Plane)  Y Coordinate (State Plane)  \\\n",
       "Unique Key                                                                      \n",
       "38490120    MANHATTAN                    995846.0                    236544.0   \n",
       "\n",
       "           Open Data Channel Type                                 Location  \n",
       "Unique Key                                                                  \n",
       "38490120                   ONLINE  (40.81592674227958, -73.95810673319008)  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fixing the incident zip datatype after filling in NaN values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>Borough</th>\n",
       "      <th>X Coordinate (State Plane)</th>\n",
       "      <th>Y Coordinate (State Plane)</th>\n",
       "      <th>Open Data Channel Type</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unique Key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38490120</th>\n",
       "      <td>02/17/2018 10:03:19 AM</td>\n",
       "      <td>02/17/2018 10:42:22 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>10027</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>995846</td>\n",
       "      <td>236544</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>(40.81592674227958, -73.95810673319008)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Created Date             Closed Date Agency  \\\n",
       "Unique Key                                                          \n",
       "38490120    02/17/2018 10:03:19 AM  02/17/2018 10:42:22 AM   NYPD   \n",
       "\n",
       "                                Agency Name       Complaint Type  \\\n",
       "Unique Key                                                         \n",
       "38490120    New York City Police Department  Noise - Residential   \n",
       "\n",
       "                  Descriptor               Location Type  Incident Zip  \\\n",
       "Unique Key                                                               \n",
       "38490120    Loud Music/Party  Residential Building/House         10027   \n",
       "\n",
       "              Borough X Coordinate (State Plane) Y Coordinate (State Plane)  \\\n",
       "Unique Key                                                                    \n",
       "38490120    MANHATTAN                     995846                     236544   \n",
       "\n",
       "           Open Data Channel Type                                 Location  \n",
       "Unique Key                                                                  \n",
       "38490120                   ONLINE  (40.81592674227958, -73.95810673319008)  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data.fillna(\"00000\", inplace=True)\n",
    "raw_data['Incident Zip'] = raw_data['Incident Zip'].astype('float')\n",
    "raw_data['Incident Zip'] = raw_data['Incident Zip'].astype('int')\n",
    "raw_data.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creating Population column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>Borough</th>\n",
       "      <th>X Coordinate (State Plane)</th>\n",
       "      <th>Y Coordinate (State Plane)</th>\n",
       "      <th>Open Data Channel Type</th>\n",
       "      <th>Location</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unique Key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38490120</th>\n",
       "      <td>02/17/2018 10:03:19 AM</td>\n",
       "      <td>02/17/2018 10:42:22 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>10027</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>995846</td>\n",
       "      <td>236544</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>(40.81592674227958, -73.95810673319008)</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38490121</th>\n",
       "      <td>02/17/2018 08:40:26 AM</td>\n",
       "      <td>02/17/2018 03:34:25 PM</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>11223</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>990992</td>\n",
       "      <td>156670</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>(40.59669663536788, -73.97572291796888)</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38490122</th>\n",
       "      <td>02/17/2018 08:32:52 PM</td>\n",
       "      <td>02/17/2018 11:35:48 PM</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>10461</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>1.03131e+06</td>\n",
       "      <td>249965</td>\n",
       "      <td>PHONE</td>\n",
       "      <td>(40.8526457301192, -73.82990522576051)</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Created Date             Closed Date Agency  \\\n",
       "Unique Key                                                          \n",
       "38490120    02/17/2018 10:03:19 AM  02/17/2018 10:42:22 AM   NYPD   \n",
       "38490121    02/17/2018 08:40:26 AM  02/17/2018 03:34:25 PM   NYPD   \n",
       "38490122    02/17/2018 08:32:52 PM  02/17/2018 11:35:48 PM   NYPD   \n",
       "\n",
       "                                Agency Name       Complaint Type  \\\n",
       "Unique Key                                                         \n",
       "38490120    New York City Police Department  Noise - Residential   \n",
       "38490121    New York City Police Department  Noise - Residential   \n",
       "38490122    New York City Police Department  Noise - Residential   \n",
       "\n",
       "                  Descriptor               Location Type  Incident Zip  \\\n",
       "Unique Key                                                               \n",
       "38490120    Loud Music/Party  Residential Building/House         10027   \n",
       "38490121    Loud Music/Party  Residential Building/House         11223   \n",
       "38490122    Loud Music/Party  Residential Building/House         10461   \n",
       "\n",
       "              Borough X Coordinate (State Plane) Y Coordinate (State Plane)  \\\n",
       "Unique Key                                                                    \n",
       "38490120    MANHATTAN                     995846                     236544   \n",
       "38490121     BROOKLYN                     990992                     156670   \n",
       "38490122        BRONX                1.03131e+06                     249965   \n",
       "\n",
       "           Open Data Channel Type                                 Location  \\\n",
       "Unique Key                                                                   \n",
       "38490120                   ONLINE  (40.81592674227958, -73.95810673319008)   \n",
       "38490121                   ONLINE  (40.59669663536788, -73.97572291796888)   \n",
       "38490122                    PHONE   (40.8526457301192, -73.82990522576051)   \n",
       "\n",
       "            Population  \n",
       "Unique Key              \n",
       "38490120             0  \n",
       "38490121             0  \n",
       "38490122             0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data.loc[:,'Population'] = pd.Series(0, index=raw_data.index)\n",
    "raw_data.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_df.drop(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1794"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(census_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mapping population information to zip<br><br>\n",
    "GEO.id2 is zipcode in the census_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['GEO.id', 'GEO.id2', 'GEO.display-label', 'D001'], dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_dict = {}\n",
    "\n",
    "for index, row in census_df.iterrows():    \n",
    "    pop_dict[row['GEO.id2']] = pop_dict.get(row['GEO.id2'], row.D001)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>Borough</th>\n",
       "      <th>X Coordinate (State Plane)</th>\n",
       "      <th>Y Coordinate (State Plane)</th>\n",
       "      <th>Open Data Channel Type</th>\n",
       "      <th>Location</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unique Key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38490120</th>\n",
       "      <td>02/17/2018 10:03:19 AM</td>\n",
       "      <td>02/17/2018 10:42:22 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>10027</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>995846</td>\n",
       "      <td>236544</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>(40.81592674227958, -73.95810673319008)</td>\n",
       "      <td>59707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38490121</th>\n",
       "      <td>02/17/2018 08:40:26 AM</td>\n",
       "      <td>02/17/2018 03:34:25 PM</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>11223</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>990992</td>\n",
       "      <td>156670</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>(40.59669663536788, -73.97572291796888)</td>\n",
       "      <td>78731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38490122</th>\n",
       "      <td>02/17/2018 08:32:52 PM</td>\n",
       "      <td>02/17/2018 11:35:48 PM</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>10461</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>1.03131e+06</td>\n",
       "      <td>249965</td>\n",
       "      <td>PHONE</td>\n",
       "      <td>(40.8526457301192, -73.82990522576051)</td>\n",
       "      <td>50502</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Created Date             Closed Date Agency  \\\n",
       "Unique Key                                                          \n",
       "38490120    02/17/2018 10:03:19 AM  02/17/2018 10:42:22 AM   NYPD   \n",
       "38490121    02/17/2018 08:40:26 AM  02/17/2018 03:34:25 PM   NYPD   \n",
       "38490122    02/17/2018 08:32:52 PM  02/17/2018 11:35:48 PM   NYPD   \n",
       "\n",
       "                                Agency Name       Complaint Type  \\\n",
       "Unique Key                                                         \n",
       "38490120    New York City Police Department  Noise - Residential   \n",
       "38490121    New York City Police Department  Noise - Residential   \n",
       "38490122    New York City Police Department  Noise - Residential   \n",
       "\n",
       "                  Descriptor               Location Type Incident Zip  \\\n",
       "Unique Key                                                              \n",
       "38490120    Loud Music/Party  Residential Building/House        10027   \n",
       "38490121    Loud Music/Party  Residential Building/House        11223   \n",
       "38490122    Loud Music/Party  Residential Building/House        10461   \n",
       "\n",
       "              Borough X Coordinate (State Plane) Y Coordinate (State Plane)  \\\n",
       "Unique Key                                                                    \n",
       "38490120    MANHATTAN                     995846                     236544   \n",
       "38490121     BROOKLYN                     990992                     156670   \n",
       "38490122        BRONX                1.03131e+06                     249965   \n",
       "\n",
       "           Open Data Channel Type                                 Location  \\\n",
       "Unique Key                                                                   \n",
       "38490120                   ONLINE  (40.81592674227958, -73.95810673319008)   \n",
       "38490121                   ONLINE  (40.59669663536788, -73.97572291796888)   \n",
       "38490122                    PHONE   (40.8526457301192, -73.82990522576051)   \n",
       "\n",
       "           Population  \n",
       "Unique Key             \n",
       "38490120        59707  \n",
       "38490121        78731  \n",
       "38490122        50502  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data['Incident Zip'] = raw_data['Incident Zip'].astype('str')\n",
    "raw_data['Population'] = raw_data['Incident Zip'].map(pop_dict)\n",
    "raw_data.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checked to ensure correct mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "59707\n",
      "78731\n",
      "50502\n"
     ]
    }
   ],
   "source": [
    "print(pop_dict['10027'])\n",
    "print(pop_dict['11223'])\n",
    "print(pop_dict['10461'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Created Date                  object\n",
       "Closed Date                   object\n",
       "Agency                        object\n",
       "Agency Name                   object\n",
       "Complaint Type                object\n",
       "Descriptor                    object\n",
       "Location Type                 object\n",
       "Incident Zip                  object\n",
       "Borough                       object\n",
       "X Coordinate (State Plane)    object\n",
       "Y Coordinate (State Plane)    object\n",
       "Open Data Channel Type        object\n",
       "Location                      object\n",
       "Population                    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now mapping neighborhoods onto zipcode as well"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "hood_path = str(Path.cwd().joinpath('calls/neighborhood.csv'))\n",
    "hoods = pd.read_csv(hood_path, index_col=0)\n",
    "hoods_dict = hoods.to_dict(orient='index')\n",
    "for k, v in hoods_dict.items():\n",
    "    hoods_dict[k] = hoods_dict[k]['Neighborhood']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# hoods.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{10001: 'Chelsea and Clinton',\n",
       " 10002: 'Lower East Side',\n",
       " 10003: 'Lower East Side',\n",
       " 10004: 'Lower Manhattan',\n",
       " 10005: 'Lower Manhattan',\n",
       " 10006: 'Lower Manhattan',\n",
       " 10007: 'Lower Manhattan',\n",
       " 10009: 'Lower East Side',\n",
       " 10010: 'Gramercy Park and Murray Hill',\n",
       " 10011: 'Chelsea and Clinton',\n",
       " 10012: 'Greenwich Village and Soho',\n",
       " 10013: 'Greenwich Village and Soho',\n",
       " 10014: 'Greenwich Village and Soho',\n",
       " 10016: 'Gramercy Park and Murray Hill',\n",
       " 10017: 'Gramercy Park and Murray Hill',\n",
       " 10018: 'Chelsea and Clinton',\n",
       " 10019: 'Chelsea and Clinton',\n",
       " 10020: 'Chelsea and Clinton',\n",
       " 10021: 'Upper East Side',\n",
       " 10022: 'Gramercy Park and Murray Hill',\n",
       " 10023: 'Upper West Side',\n",
       " 10024: 'Upper West Side',\n",
       " 10025: 'Upper West Side',\n",
       " 10026: 'Central Harlem',\n",
       " 10027: 'Central Harlem',\n",
       " 10028: 'Upper East Side',\n",
       " 10029: 'East Harlem',\n",
       " 10030: 'Central Harlem',\n",
       " 10031: 'Inwood and Washington Heights',\n",
       " 10032: 'Inwood and Washington Heights',\n",
       " 10033: 'Inwood and Washington Heights',\n",
       " 10034: 'Inwood and Washington Heights',\n",
       " 10035: 'East Harlem',\n",
       " 10036: 'Chelsea and Clinton',\n",
       " 10037: 'Central Harlem',\n",
       " 10038: 'Lower Manhattan',\n",
       " 10039: 'Central Harlem',\n",
       " 10040: 'Inwood and Washington Heights',\n",
       " 10044: 'Upper East Side',\n",
       " 10065: 'Upper East Side',\n",
       " 10075: 'Upper East Side',\n",
       " 10128: 'Upper East Side',\n",
       " 10280: 'Lower Manhattan',\n",
       " 10301: 'Stapleton and St. George',\n",
       " 10302: 'Port Richmond',\n",
       " 10303: 'Port Richmond',\n",
       " 10304: 'Stapleton and St. George',\n",
       " 10305: 'Stapleton and St. George',\n",
       " 10306: 'South Shore',\n",
       " 10307: 'South Shore',\n",
       " 10308: 'South Shore',\n",
       " 10309: 'South Shore',\n",
       " 10310: 'Port Richmond',\n",
       " 10312: 'South Shore',\n",
       " 10314: 'Mid-Island',\n",
       " 10451: 'High Bridge and Morrisania',\n",
       " 10452: 'High Bridge and Morrisania',\n",
       " 10453: 'Central Bronx',\n",
       " 10454: 'Hunts Point and Mott Haven',\n",
       " 10455: 'Hunts Point and Mott Haven',\n",
       " 10456: 'High Bridge and Morrisania',\n",
       " 10457: 'Central Bronx',\n",
       " 10458: 'Bronx Park and Fordham',\n",
       " 10459: 'Hunts Point and Mott Haven',\n",
       " 10460: 'Central Bronx',\n",
       " 10461: 'Southeast Bronx',\n",
       " 10462: 'Southeast Bronx',\n",
       " 10463: 'Kingsbridge and Riverdale',\n",
       " 10464: 'Southeast Bronx',\n",
       " 10465: 'Southeast Bronx',\n",
       " 10466: 'Northeast Bronx',\n",
       " 10467: 'Bronx Park and Fordham',\n",
       " 10468: 'Bronx Park and Fordham',\n",
       " 10469: 'Northeast Bronx',\n",
       " 10470: 'Northeast Bronx',\n",
       " 10471: 'Kingsbridge and Riverdale',\n",
       " 10472: 'Southeast Bronx',\n",
       " 10473: 'Southeast Bronx',\n",
       " 10474: 'Hunts Point and Mott Haven',\n",
       " 10475: 'Northeast Bronx',\n",
       " 11004: 'Southeast Queens',\n",
       " 11005: 'Southeast Queens',\n",
       " 11101: 'Northwest Queens',\n",
       " 11102: 'Northwest Queens',\n",
       " 11103: 'Northwest Queens',\n",
       " 11104: 'Northwest Queens',\n",
       " 11105: 'Northwest Queens',\n",
       " 11106: 'Northwest Queens',\n",
       " 11201: 'Northwest Brooklyn',\n",
       " 11203: 'Flatbush',\n",
       " 11204: 'Borough Park',\n",
       " 11205: 'Northwest Brooklyn',\n",
       " 11206: 'Bushwick and Williamsburg',\n",
       " 11207: 'East New York and New Lots',\n",
       " 11208: 'East New York and New Lots',\n",
       " 11209: 'Southwest Brooklyn',\n",
       " 11210: 'Flatbush',\n",
       " 11211: 'Greenpoint',\n",
       " 11212: 'Central Brooklyn',\n",
       " 11213: 'Central Brooklyn',\n",
       " 11214: 'Southwest Brooklyn',\n",
       " 11215: 'Northwest Brooklyn',\n",
       " 11216: 'Central Brooklyn',\n",
       " 11217: 'Northwest Brooklyn',\n",
       " 11218: 'Borough Park',\n",
       " 11219: 'Borough Park',\n",
       " 11220: 'Sunset Park',\n",
       " 11221: 'Bushwick and Williamsburg',\n",
       " 11222: 'Greenpoint',\n",
       " 11223: 'Southern Brooklyn',\n",
       " 11224: 'Southern Brooklyn',\n",
       " 11225: 'Flatbush',\n",
       " 11226: 'Flatbush',\n",
       " 11228: 'Southwest Brooklyn',\n",
       " 11229: 'Southern Brooklyn',\n",
       " 11230: 'Borough Park',\n",
       " 11231: 'Northwest Brooklyn',\n",
       " 11232: 'Sunset Park',\n",
       " 11233: 'Central Brooklyn',\n",
       " 11234: 'Canarsie and Flatlands',\n",
       " 11235: 'Southern Brooklyn',\n",
       " 11236: 'Canarsie and Flatlands',\n",
       " 11237: 'Bushwick and Williamsburg',\n",
       " 11238: 'Central Brooklyn',\n",
       " 11239: 'Canarsie and Flatlands',\n",
       " 11354: 'North Queens',\n",
       " 11355: 'North Queens',\n",
       " 11356: 'North Queens',\n",
       " 11357: 'North Queens',\n",
       " 11358: 'North Queens',\n",
       " 11359: 'North Queens',\n",
       " 11360: 'North Queens',\n",
       " 11361: 'Northeast Queens',\n",
       " 11362: 'Northeast Queens',\n",
       " 11363: 'Northeast Queens',\n",
       " 11364: 'Northeast Queens',\n",
       " 11365: 'Central Queens',\n",
       " 11366: 'Central Queens',\n",
       " 11367: 'Central Queens',\n",
       " 11368: 'West Queens',\n",
       " 11369: 'West Queens',\n",
       " 11370: 'West Queens',\n",
       " 11372: 'West Queens',\n",
       " 11373: 'West Queens',\n",
       " 11374: 'West Central Queens',\n",
       " 11375: 'West Central Queens',\n",
       " 11377: 'West Queens',\n",
       " 11378: 'West Queens',\n",
       " 11379: 'West Central Queens',\n",
       " 11385: 'West Central Queens',\n",
       " 11411: 'Southeast Queens',\n",
       " 11412: 'Jamaica',\n",
       " 11413: 'Southeast Queens',\n",
       " 11414: 'Southwest Queens',\n",
       " 11415: 'Southwest Queens',\n",
       " 11416: 'Southwest Queens',\n",
       " 11417: 'Southwest Queens',\n",
       " 11418: 'Southwest Queens',\n",
       " 11419: 'Southwest Queens',\n",
       " 11420: 'Southwest Queens',\n",
       " 11421: 'Southwest Queens',\n",
       " 11422: 'Southeast Queens',\n",
       " 11423: 'Jamaica',\n",
       " 11426: 'Southeast Queens',\n",
       " 11427: 'Southeast Queens',\n",
       " 11428: 'Southeast Queens',\n",
       " 11429: 'Southeast Queens',\n",
       " 11432: 'Jamaica',\n",
       " 11433: 'Jamaica',\n",
       " 11434: 'Jamaica',\n",
       " 11435: 'Jamaica',\n",
       " 11436: 'Jamaica',\n",
       " 11691: 'Rockaways',\n",
       " 11692: 'Rockaways',\n",
       " 11693: 'Rockaways',\n",
       " 11694: 'Rockaways',\n",
       " 11695: 'Rockaways',\n",
       " 11697: 'Rockaways'}"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hoods_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_data['Incident Zip'] = raw_data['Incident Zip'].astype('int')\n",
    "raw_data['Neighborhood'] = raw_data['Incident Zip'].map(hoods_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>Borough</th>\n",
       "      <th>X Coordinate (State Plane)</th>\n",
       "      <th>Y Coordinate (State Plane)</th>\n",
       "      <th>Open Data Channel Type</th>\n",
       "      <th>Location</th>\n",
       "      <th>Population</th>\n",
       "      <th>Neighborhood</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unique Key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38490120</th>\n",
       "      <td>02/17/2018 10:03:19 AM</td>\n",
       "      <td>02/17/2018 10:42:22 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>10027</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>995846</td>\n",
       "      <td>236544</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>(40.81592674227958, -73.95810673319008)</td>\n",
       "      <td>59707</td>\n",
       "      <td>Central Harlem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38490121</th>\n",
       "      <td>02/17/2018 08:40:26 AM</td>\n",
       "      <td>02/17/2018 03:34:25 PM</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>11223</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>990992</td>\n",
       "      <td>156670</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>(40.59669663536788, -73.97572291796888)</td>\n",
       "      <td>78731</td>\n",
       "      <td>Southern Brooklyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38490122</th>\n",
       "      <td>02/17/2018 08:32:52 PM</td>\n",
       "      <td>02/17/2018 11:35:48 PM</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>10461</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>1.03131e+06</td>\n",
       "      <td>249965</td>\n",
       "      <td>PHONE</td>\n",
       "      <td>(40.8526457301192, -73.82990522576051)</td>\n",
       "      <td>50502</td>\n",
       "      <td>Southeast Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38490123</th>\n",
       "      <td>02/17/2018 02:07:29 PM</td>\n",
       "      <td>02/17/2018 02:21:28 PM</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>10002</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>987058</td>\n",
       "      <td>198677</td>\n",
       "      <td>PHONE</td>\n",
       "      <td>(40.71199877267997, -73.98987125976987)</td>\n",
       "      <td>81410</td>\n",
       "      <td>Lower East Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38490124</th>\n",
       "      <td>02/17/2018 10:52:04 PM</td>\n",
       "      <td>02/18/2018 01:30:55 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>11385</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>1.01212e+06</td>\n",
       "      <td>191989</td>\n",
       "      <td>PHONE</td>\n",
       "      <td>(40.693598357245065, -73.89951223535249)</td>\n",
       "      <td>98592</td>\n",
       "      <td>West Central Queens</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Created Date             Closed Date Agency  \\\n",
       "Unique Key                                                          \n",
       "38490120    02/17/2018 10:03:19 AM  02/17/2018 10:42:22 AM   NYPD   \n",
       "38490121    02/17/2018 08:40:26 AM  02/17/2018 03:34:25 PM   NYPD   \n",
       "38490122    02/17/2018 08:32:52 PM  02/17/2018 11:35:48 PM   NYPD   \n",
       "38490123    02/17/2018 02:07:29 PM  02/17/2018 02:21:28 PM   NYPD   \n",
       "38490124    02/17/2018 10:52:04 PM  02/18/2018 01:30:55 AM   NYPD   \n",
       "\n",
       "                                Agency Name       Complaint Type  \\\n",
       "Unique Key                                                         \n",
       "38490120    New York City Police Department  Noise - Residential   \n",
       "38490121    New York City Police Department  Noise - Residential   \n",
       "38490122    New York City Police Department  Noise - Residential   \n",
       "38490123    New York City Police Department  Noise - Residential   \n",
       "38490124    New York City Police Department  Noise - Residential   \n",
       "\n",
       "                  Descriptor               Location Type  Incident Zip  \\\n",
       "Unique Key                                                               \n",
       "38490120    Loud Music/Party  Residential Building/House         10027   \n",
       "38490121    Loud Music/Party  Residential Building/House         11223   \n",
       "38490122    Loud Music/Party  Residential Building/House         10461   \n",
       "38490123    Loud Music/Party  Residential Building/House         10002   \n",
       "38490124    Loud Music/Party  Residential Building/House         11385   \n",
       "\n",
       "              Borough X Coordinate (State Plane) Y Coordinate (State Plane)  \\\n",
       "Unique Key                                                                    \n",
       "38490120    MANHATTAN                     995846                     236544   \n",
       "38490121     BROOKLYN                     990992                     156670   \n",
       "38490122        BRONX                1.03131e+06                     249965   \n",
       "38490123    MANHATTAN                     987058                     198677   \n",
       "38490124       QUEENS                1.01212e+06                     191989   \n",
       "\n",
       "           Open Data Channel Type                                  Location  \\\n",
       "Unique Key                                                                    \n",
       "38490120                   ONLINE   (40.81592674227958, -73.95810673319008)   \n",
       "38490121                   ONLINE   (40.59669663536788, -73.97572291796888)   \n",
       "38490122                    PHONE    (40.8526457301192, -73.82990522576051)   \n",
       "38490123                    PHONE   (40.71199877267997, -73.98987125976987)   \n",
       "38490124                    PHONE  (40.693598357245065, -73.89951223535249)   \n",
       "\n",
       "           Population         Neighborhood  \n",
       "Unique Key                                  \n",
       "38490120        59707       Central Harlem  \n",
       "38490121        78731    Southern Brooklyn  \n",
       "38490122        50502      Southeast Bronx  \n",
       "38490123        81410      Lower East Side  \n",
       "38490124        98592  West Central Queens  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each zip code (and for each neighborhood) \n",
    "\n",
    "Calls/person \n",
    "Calls of each type/person \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating the neighborhoods information\n",
    "Scrapped a website to get the zips by neighborhood info using requests and BeautifulSoup; did some manipulation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter - \n"
     ]
    }
   ],
   "source": [
    "# import urllib.request, urllib.parse, urllib.error\n",
    "# from bs4 import BeautifulSoup\n",
    "# import ssl\n",
    "# import pandas as pd\n",
    "\n",
    "# ctx = ssl.create_default_context()\n",
    "# ctx.check_hostname = False\n",
    "# ctx.verify_mode = ssl.CERT_NONE\n",
    "\n",
    "# url = input('Enter - ')\n",
    "# if len(url) < 1: url = 'https://www.health.ny.gov/statistics/cancer/registry/appendix/neighborhoods.htm'\n",
    "\n",
    "# soup = BeautifulSoup(urllib.request.urlopen(url, context=ctx).read(), 'html.parser')\n",
    "\n",
    "# tags = soup('td')\n",
    "\n",
    "# #Pass tags to this function. It will pull out each neighborhood and each set of zip codes,\n",
    "# #and then zip those together into a dictionary with neighborhoods paired to their zipcodes\n",
    "\n",
    "# def create_hood_dict(tag_list):\n",
    "#     neighborhood = []\n",
    "#     zip_codes = []\n",
    "#     for tag in tag_list:\n",
    "#         if str(tag).startswith('<td headers=\"header2\">'):\n",
    "#             hood = tag.text.lstrip()\n",
    "#             neighborhood.append(hood)\n",
    "#         elif str(tag).startswith('<td headers=\"header3\">'):\n",
    "#             zips_1 = tag.text.split(\",\")\n",
    "#             zips_2 = [zip.lstrip() for zip in zips_1]\n",
    "#             zip_codes.append(zips_2)\n",
    "#         else:\n",
    "#             continue\n",
    "#     result = zip(neighborhood, zip_codes)\n",
    "#     neighborhoods_local = dict(result)\n",
    "#     return neighborhoods_local\n",
    "\n",
    "\n",
    "# def persist(dict_name):\n",
    "#     better_dict = {}\n",
    "    \n",
    "#     for k, v in dict_name.items():\n",
    "#         for zipcode in v: \n",
    "#             better_dict[zipcode] = better_dict.get(zipcode, k)\n",
    "            \n",
    "#     data = test3 = pd.DataFrame.from_dict(better_dict, orient = 'index', columns = ['Neighborhood'])\n",
    "#     data.to_csv('calls/neighborhood.csv', mode = 'w+')\n",
    "#     return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "hood_dict = create_hood_dict(tags)\n",
    "test1 = persist(hood_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Borough Park': ['11204.0', '11218.0', '11219.0', '11230.0'],\n",
       " 'Bronx Park and Fordham': ['10458.0', '10467.0', '10468.0'],\n",
       " 'Bushwick and Williamsburg': ['11206.0', '11221.0', '11237.0'],\n",
       " 'Canarsie and Flatlands': ['11234.0', '11236.0', '11239.0'],\n",
       " 'Central Bronx': ['10453.0', '10457.0', '10460.0'],\n",
       " 'Central Brooklyn': ['11212.0', '11213.0', '11216.0', '11233.0', '11238.0'],\n",
       " 'Central Harlem': ['10026.0', '10027.0', '10030.0', '10037.0', '10039.0'],\n",
       " 'Central Queens': ['11365.0', '11366.0', '11367.0'],\n",
       " 'Chelsea and Clinton': ['10001.0',\n",
       "  '10011.0',\n",
       "  '10018.0',\n",
       "  '10019.0',\n",
       "  '10020.0',\n",
       "  '10036.0'],\n",
       " 'East Harlem': ['10029.0', '10035.0'],\n",
       " 'East New York and New Lots': ['11207.0', '11208.0'],\n",
       " 'Flatbush': ['11203.0', '11210.0', '11225.0', '11226.0'],\n",
       " 'Gramercy Park and Murray Hill': ['10010.0', '10016.0', '10017.0', '10022.0'],\n",
       " 'Greenpoint': ['11211.0', '11222.0'],\n",
       " 'Greenwich Village and Soho': ['10012.0', '10013.0', '10014.0'],\n",
       " 'High Bridge and Morrisania': ['10451.0', '10452.0', '10456.0'],\n",
       " 'Hunts Point and Mott Haven': ['10454.0', '10455.0', '10459.0', '10474.0'],\n",
       " 'Inwood and Washington Heights': ['10031.0',\n",
       "  '10032.0',\n",
       "  '10033.0',\n",
       "  '10034.0',\n",
       "  '10040.0'],\n",
       " 'Jamaica': ['11412.0',\n",
       "  '11423.0',\n",
       "  '11432.0',\n",
       "  '11433.0',\n",
       "  '11434.0',\n",
       "  '11435.0',\n",
       "  '11436.0'],\n",
       " 'Kingsbridge and Riverdale': ['10463.0', '10471.0'],\n",
       " 'Lower East Side': ['10002.0', '10003.0', '10009.0'],\n",
       " 'Lower Manhattan': ['10004.0',\n",
       "  '10005.0',\n",
       "  '10006.0',\n",
       "  '10007.0',\n",
       "  '10038.0',\n",
       "  '10280.0'],\n",
       " 'Mid-Island': ['10314.0'],\n",
       " 'North Queens': ['11354.0',\n",
       "  '11355.0',\n",
       "  '11356.0',\n",
       "  '11357.0',\n",
       "  '11358.0',\n",
       "  '11359.0',\n",
       "  '11360.0'],\n",
       " 'Northeast Bronx': ['10466.0', '10469.0', '10470.0', '10475.0'],\n",
       " 'Northeast Queens': ['11361.0', '11362.0', '11363.0', '11364.0'],\n",
       " 'Northwest Brooklyn': ['11201.0', '11205.0', '11215.0', '11217.0', '11231.0'],\n",
       " 'Northwest Queens': ['11101.0',\n",
       "  '11102.0',\n",
       "  '11103.0',\n",
       "  '11104.0',\n",
       "  '11105.0',\n",
       "  '11106.0'],\n",
       " 'Port Richmond': ['10302.0', '10303.0', '10310.0'],\n",
       " 'Rockaways': ['11691.0',\n",
       "  '11692.0',\n",
       "  '11693.0',\n",
       "  '11694.0',\n",
       "  '11695.0',\n",
       "  '11697.0'],\n",
       " 'South Shore': ['10306.0', '10307.0', '10308.0', '10309.0', '10312.0'],\n",
       " 'Southeast Bronx': ['10461.0',\n",
       "  '10462.0',\n",
       "  '10464.0',\n",
       "  '10465.0',\n",
       "  '10472.0',\n",
       "  '10473.0'],\n",
       " 'Southeast Queens': ['11004.0',\n",
       "  '11005.0',\n",
       "  '11411.0',\n",
       "  '11413.0',\n",
       "  '11422.0',\n",
       "  '11426.0',\n",
       "  '11427.0',\n",
       "  '11428.0',\n",
       "  '11429.0'],\n",
       " 'Southern Brooklyn': ['11223.0', '11224.0', '11229.0', '11235.0'],\n",
       " 'Southwest Brooklyn': ['11209.0', '11214.0', '11228.0'],\n",
       " 'Southwest Queens': ['11414.0',\n",
       "  '11415.0',\n",
       "  '11416.0',\n",
       "  '11417.0',\n",
       "  '11418.0',\n",
       "  '11419.0',\n",
       "  '11420.0',\n",
       "  '11421.0'],\n",
       " 'Stapleton and St. George': ['10301.0', '10304.0', '10305.0'],\n",
       " 'Sunset Park': ['11220.0', '11232.0'],\n",
       " 'Upper East Side': ['10021.0',\n",
       "  '10028.0',\n",
       "  '10044.0',\n",
       "  '10065.0',\n",
       "  '10075.0',\n",
       "  '10128.0'],\n",
       " 'Upper West Side': ['10023.0', '10024.0', '10025.0'],\n",
       " 'West Central Queens': ['11374.0', '11375.0', '11379.0', '11385.0'],\n",
       " 'West Queens': ['11368.0',\n",
       "  '11369.0',\n",
       "  '11370.0',\n",
       "  '11372.0',\n",
       "  '11373.0',\n",
       "  '11377.0',\n",
       "  '11378.0']}"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hood_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Neighborhood</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11101</th>\n",
       "      <td>Northwest Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10018</th>\n",
       "      <td>Chelsea and Clinton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10025</th>\n",
       "      <td>Upper West Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10466</th>\n",
       "      <td>Northeast Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10301</th>\n",
       "      <td>Stapleton and St. George</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11411</th>\n",
       "      <td>Southeast Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10033</th>\n",
       "      <td>Inwood and Washington Heights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10006</th>\n",
       "      <td>Lower Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11212</th>\n",
       "      <td>Central Brooklyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11691</th>\n",
       "      <td>Rockaways</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10044</th>\n",
       "      <td>Upper East Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11206</th>\n",
       "      <td>Bushwick and Williamsburg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10007</th>\n",
       "      <td>Lower Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11373</th>\n",
       "      <td>West Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11694</th>\n",
       "      <td>Rockaways</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10471</th>\n",
       "      <td>Kingsbridge and Riverdale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11209</th>\n",
       "      <td>Southwest Brooklyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10075</th>\n",
       "      <td>Upper East Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10472</th>\n",
       "      <td>Southeast Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11419</th>\n",
       "      <td>Southwest Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11237</th>\n",
       "      <td>Bushwick and Williamsburg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10009</th>\n",
       "      <td>Lower East Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11414</th>\n",
       "      <td>Southwest Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10034</th>\n",
       "      <td>Inwood and Washington Heights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11378</th>\n",
       "      <td>West Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11423</th>\n",
       "      <td>Jamaica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10469</th>\n",
       "      <td>Northeast Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10309</th>\n",
       "      <td>South Shore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11433</th>\n",
       "      <td>Jamaica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10026</th>\n",
       "      <td>Central Harlem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11375</th>\n",
       "      <td>West Central Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11205</th>\n",
       "      <td>Northwest Brooklyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11213</th>\n",
       "      <td>Central Brooklyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10019</th>\n",
       "      <td>Chelsea and Clinton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10040</th>\n",
       "      <td>Inwood and Washington Heights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10035</th>\n",
       "      <td>East Harlem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11358</th>\n",
       "      <td>North Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11215</th>\n",
       "      <td>Northwest Brooklyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10474</th>\n",
       "      <td>Hunts Point and Mott Haven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11421</th>\n",
       "      <td>Southwest Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11103</th>\n",
       "      <td>Northwest Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11214</th>\n",
       "      <td>Southwest Brooklyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11211</th>\n",
       "      <td>Greenpoint</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10312</th>\n",
       "      <td>South Shore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10020</th>\n",
       "      <td>Chelsea and Clinton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11422</th>\n",
       "      <td>Southeast Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10002</th>\n",
       "      <td>Lower East Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11232</th>\n",
       "      <td>Sunset Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11203</th>\n",
       "      <td>Flatbush</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10017</th>\n",
       "      <td>Gramercy Park and Murray Hill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11428</th>\n",
       "      <td>Southeast Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10306</th>\n",
       "      <td>South Shore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10021</th>\n",
       "      <td>Upper East Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11435</th>\n",
       "      <td>Jamaica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11369</th>\n",
       "      <td>West Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11105</th>\n",
       "      <td>Northwest Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10011</th>\n",
       "      <td>Chelsea and Clinton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10461</th>\n",
       "      <td>Southeast Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10452</th>\n",
       "      <td>High Bridge and Morrisania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11225</th>\n",
       "      <td>Flatbush</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>178 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Neighborhood\n",
       "11101               Northwest Queens\n",
       "10018            Chelsea and Clinton\n",
       "10025                Upper West Side\n",
       "10466                Northeast Bronx\n",
       "10301       Stapleton and St. George\n",
       "11411               Southeast Queens\n",
       "10033  Inwood and Washington Heights\n",
       "10006                Lower Manhattan\n",
       "11212               Central Brooklyn\n",
       "11691                      Rockaways\n",
       "10044                Upper East Side\n",
       "11206      Bushwick and Williamsburg\n",
       "10007                Lower Manhattan\n",
       "11373                    West Queens\n",
       "11694                      Rockaways\n",
       "10471      Kingsbridge and Riverdale\n",
       "11209             Southwest Brooklyn\n",
       "10075                Upper East Side\n",
       "10472                Southeast Bronx\n",
       "11419               Southwest Queens\n",
       "11237      Bushwick and Williamsburg\n",
       "10009                Lower East Side\n",
       "11414               Southwest Queens\n",
       "10034  Inwood and Washington Heights\n",
       "11378                    West Queens\n",
       "11423                        Jamaica\n",
       "10469                Northeast Bronx\n",
       "10309                    South Shore\n",
       "11433                        Jamaica\n",
       "10026                 Central Harlem\n",
       "...                              ...\n",
       "11375            West Central Queens\n",
       "11205             Northwest Brooklyn\n",
       "11213               Central Brooklyn\n",
       "10019            Chelsea and Clinton\n",
       "10040  Inwood and Washington Heights\n",
       "10035                    East Harlem\n",
       "11358                   North Queens\n",
       "11215             Northwest Brooklyn\n",
       "10474     Hunts Point and Mott Haven\n",
       "11421               Southwest Queens\n",
       "11103               Northwest Queens\n",
       "11214             Southwest Brooklyn\n",
       "11211                     Greenpoint\n",
       "10312                    South Shore\n",
       "10020            Chelsea and Clinton\n",
       "11422               Southeast Queens\n",
       "10002                Lower East Side\n",
       "11232                    Sunset Park\n",
       "11203                       Flatbush\n",
       "10017  Gramercy Park and Murray Hill\n",
       "11428               Southeast Queens\n",
       "10306                    South Shore\n",
       "10021                Upper East Side\n",
       "11435                        Jamaica\n",
       "11369                    West Queens\n",
       "11105               Northwest Queens\n",
       "10011            Chelsea and Clinton\n",
       "10461                Southeast Bronx\n",
       "10452     High Bridge and Morrisania\n",
       "11225                       Flatbush\n",
       "\n",
       "[178 rows x 1 columns]"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>Borough</th>\n",
       "      <th>X Coordinate (State Plane)</th>\n",
       "      <th>Y Coordinate (State Plane)</th>\n",
       "      <th>Open Data Channel Type</th>\n",
       "      <th>Location</th>\n",
       "      <th>Population</th>\n",
       "      <th>Neighborhood</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unique Key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38490120</th>\n",
       "      <td>02/17/2018 10:03:19 AM</td>\n",
       "      <td>02/17/2018 10:42:22 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>10027</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>995846</td>\n",
       "      <td>236544</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>(40.81592674227958, -73.95810673319008)</td>\n",
       "      <td>59707</td>\n",
       "      <td>Central Harlem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38490121</th>\n",
       "      <td>02/17/2018 08:40:26 AM</td>\n",
       "      <td>02/17/2018 03:34:25 PM</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>11223</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>990992</td>\n",
       "      <td>156670</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>(40.59669663536788, -73.97572291796888)</td>\n",
       "      <td>78731</td>\n",
       "      <td>Southern Brooklyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38490122</th>\n",
       "      <td>02/17/2018 08:32:52 PM</td>\n",
       "      <td>02/17/2018 11:35:48 PM</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>10461</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>1.03131e+06</td>\n",
       "      <td>249965</td>\n",
       "      <td>PHONE</td>\n",
       "      <td>(40.8526457301192, -73.82990522576051)</td>\n",
       "      <td>50502</td>\n",
       "      <td>Southeast Bronx</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Created Date             Closed Date Agency  \\\n",
       "Unique Key                                                          \n",
       "38490120    02/17/2018 10:03:19 AM  02/17/2018 10:42:22 AM   NYPD   \n",
       "38490121    02/17/2018 08:40:26 AM  02/17/2018 03:34:25 PM   NYPD   \n",
       "38490122    02/17/2018 08:32:52 PM  02/17/2018 11:35:48 PM   NYPD   \n",
       "\n",
       "                                Agency Name       Complaint Type  \\\n",
       "Unique Key                                                         \n",
       "38490120    New York City Police Department  Noise - Residential   \n",
       "38490121    New York City Police Department  Noise - Residential   \n",
       "38490122    New York City Police Department  Noise - Residential   \n",
       "\n",
       "                  Descriptor               Location Type  Incident Zip  \\\n",
       "Unique Key                                                               \n",
       "38490120    Loud Music/Party  Residential Building/House         10027   \n",
       "38490121    Loud Music/Party  Residential Building/House         11223   \n",
       "38490122    Loud Music/Party  Residential Building/House         10461   \n",
       "\n",
       "              Borough X Coordinate (State Plane) Y Coordinate (State Plane)  \\\n",
       "Unique Key                                                                    \n",
       "38490120    MANHATTAN                     995846                     236544   \n",
       "38490121     BROOKLYN                     990992                     156670   \n",
       "38490122        BRONX                1.03131e+06                     249965   \n",
       "\n",
       "           Open Data Channel Type                                 Location  \\\n",
       "Unique Key                                                                   \n",
       "38490120                   ONLINE  (40.81592674227958, -73.95810673319008)   \n",
       "38490121                   ONLINE  (40.59669663536788, -73.97572291796888)   \n",
       "38490122                    PHONE   (40.8526457301192, -73.82990522576051)   \n",
       "\n",
       "           Population       Neighborhood  \n",
       "Unique Key                                \n",
       "38490120        59707     Central Harlem  \n",
       "38490121        78731  Southern Brooklyn  \n",
       "38490122        50502    Southeast Bronx  "
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "# raw_dict = raw_data.to_dict(orient='index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{0: 2791,\n",
       " 83: 36,\n",
       " 10000: 162,\n",
       " 10001: 3091,\n",
       " 10002: 10948,\n",
       " 10003: 6572,\n",
       " 10004: 229,\n",
       " 10005: 405,\n",
       " 10006: 172,\n",
       " 10007: 818,\n",
       " 10009: 9227,\n",
       " 10010: 1939,\n",
       " 10011: 4045,\n",
       " 10012: 4251,\n",
       " 10013: 2674,\n",
       " 10014: 3471,\n",
       " 10016: 3476,\n",
       " 10017: 1010,\n",
       " 10018: 1016,\n",
       " 10019: 2855,\n",
       " 10020: 25,\n",
       " 10021: 1731,\n",
       " 10022: 1450,\n",
       " 10023: 2517,\n",
       " 10024: 3095,\n",
       " 10025: 6637,\n",
       " 10026: 7152,\n",
       " 10027: 7672,\n",
       " 10028: 2501,\n",
       " 10029: 7590,\n",
       " 10030: 4500,\n",
       " 10031: 15036,\n",
       " 10032: 10794,\n",
       " 10033: 10117,\n",
       " 10034: 10215,\n",
       " 10035: 4279,\n",
       " 10036: 3783,\n",
       " 10037: 2105,\n",
       " 10038: 1248,\n",
       " 10039: 4713,\n",
       " 10040: 12228,\n",
       " 10041: 11,\n",
       " 10044: 48,\n",
       " 10048: 1,\n",
       " 10065: 1286,\n",
       " 10069: 58,\n",
       " 10075: 1256,\n",
       " 10103: 2,\n",
       " 10106: 2,\n",
       " 10107: 3,\n",
       " 10110: 3,\n",
       " 10111: 1,\n",
       " 10112: 5,\n",
       " 10118: 18,\n",
       " 10119: 12,\n",
       " 10120: 9,\n",
       " 10121: 38,\n",
       " 10122: 3,\n",
       " 10123: 1,\n",
       " 10128: 2658,\n",
       " 10151: 1,\n",
       " 10153: 1,\n",
       " 10155: 1,\n",
       " 10162: 9,\n",
       " 10165: 1,\n",
       " 10168: 1,\n",
       " 10169: 1,\n",
       " 10171: 1,\n",
       " 10172: 1,\n",
       " 10173: 3,\n",
       " 10175: 1,\n",
       " 10177: 3,\n",
       " 10178: 1,\n",
       " 10271: 1,\n",
       " 10278: 12,\n",
       " 10279: 1,\n",
       " 10280: 121,\n",
       " 10281: 17,\n",
       " 10282: 31,\n",
       " 10301: 3366,\n",
       " 10302: 1985,\n",
       " 10303: 2372,\n",
       " 10304: 3964,\n",
       " 10305: 2942,\n",
       " 10306: 4596,\n",
       " 10307: 1111,\n",
       " 10308: 2624,\n",
       " 10309: 2058,\n",
       " 10310: 2949,\n",
       " 10312: 4601,\n",
       " 10314: 6543,\n",
       " 10451: 4718,\n",
       " 10452: 9972,\n",
       " 10453: 8020,\n",
       " 10454: 2843,\n",
       " 10455: 4267,\n",
       " 10456: 10015,\n",
       " 10457: 9061,\n",
       " 10458: 8916,\n",
       " 10459: 5094,\n",
       " 10460: 5878,\n",
       " 10461: 2947,\n",
       " 10462: 4298,\n",
       " 10463: 6310,\n",
       " 10464: 337,\n",
       " 10465: 2552,\n",
       " 10466: 4549,\n",
       " 10467: 10435,\n",
       " 10468: 11343,\n",
       " 10469: 3496,\n",
       " 10470: 1133,\n",
       " 10471: 983,\n",
       " 10472: 8780,\n",
       " 10473: 3691,\n",
       " 10474: 1634,\n",
       " 10475: 308,\n",
       " 10583: 1,\n",
       " 11001: 85,\n",
       " 11004: 405,\n",
       " 11005: 1,\n",
       " 11040: 73,\n",
       " 11096: 1,\n",
       " 11101: 3422,\n",
       " 11102: 2727,\n",
       " 11103: 3798,\n",
       " 11104: 1737,\n",
       " 11105: 2133,\n",
       " 11106: 3184,\n",
       " 11109: 93,\n",
       " 11201: 3778,\n",
       " 11203: 5905,\n",
       " 11204: 2406,\n",
       " 11205: 7253,\n",
       " 11206: 10728,\n",
       " 11207: 8286,\n",
       " 11208: 7994,\n",
       " 11209: 4083,\n",
       " 11210: 3067,\n",
       " 11211: 10005,\n",
       " 11212: 5319,\n",
       " 11213: 7992,\n",
       " 11214: 2555,\n",
       " 11215: 5380,\n",
       " 11216: 10406,\n",
       " 11217: 5003,\n",
       " 11218: 4142,\n",
       " 11219: 2511,\n",
       " 11220: 6582,\n",
       " 11221: 12506,\n",
       " 11222: 4925,\n",
       " 11223: 2753,\n",
       " 11224: 2077,\n",
       " 11225: 9118,\n",
       " 11226: 12379,\n",
       " 11228: 1037,\n",
       " 11229: 3604,\n",
       " 11230: 3743,\n",
       " 11231: 2382,\n",
       " 11232: 2272,\n",
       " 11233: 7630,\n",
       " 11234: 3777,\n",
       " 11235: 4281,\n",
       " 11236: 4383,\n",
       " 11237: 12072,\n",
       " 11238: 10920,\n",
       " 11239: 145,\n",
       " 11241: 2,\n",
       " 11242: 1,\n",
       " 11249: 4228,\n",
       " 11354: 1672,\n",
       " 11355: 1684,\n",
       " 11356: 1454,\n",
       " 11357: 1132,\n",
       " 11358: 1179,\n",
       " 11360: 315,\n",
       " 11361: 989,\n",
       " 11362: 326,\n",
       " 11363: 131,\n",
       " 11364: 606,\n",
       " 11365: 1123,\n",
       " 11366: 852,\n",
       " 11367: 1302,\n",
       " 11368: 6419,\n",
       " 11369: 2237,\n",
       " 11370: 1108,\n",
       " 11372: 3581,\n",
       " 11373: 3766,\n",
       " 11374: 1217,\n",
       " 11375: 2221,\n",
       " 11377: 4239,\n",
       " 11378: 1568,\n",
       " 11379: 1132,\n",
       " 11385: 7983,\n",
       " 11411: 578,\n",
       " 11412: 1682,\n",
       " 11413: 1376,\n",
       " 11414: 1421,\n",
       " 11415: 1027,\n",
       " 11416: 1982,\n",
       " 11417: 2378,\n",
       " 11418: 2963,\n",
       " 11419: 4346,\n",
       " 11420: 4153,\n",
       " 11421: 3548,\n",
       " 11422: 1060,\n",
       " 11423: 1682,\n",
       " 11426: 614,\n",
       " 11427: 829,\n",
       " 11428: 1003,\n",
       " 11429: 884,\n",
       " 11430: 10,\n",
       " 11432: 2865,\n",
       " 11433: 2453,\n",
       " 11434: 2540,\n",
       " 11435: 2541,\n",
       " 11436: 1263,\n",
       " 11691: 3290,\n",
       " 11692: 960,\n",
       " 11693: 1032,\n",
       " 11694: 1210,\n",
       " 11697: 16,\n",
       " 12345: 1}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def total_complaints():\n",
    "    total_complaints_dict = {}\n",
    "    for i in list(set(raw_data['Incident Zip'])):\n",
    "        total_complaints_dict[i] = total_complaints_dict.get(i, len(raw_data.loc[raw_data['Incident Zip'] == i]))\n",
    "    return total_complaints_dict\n",
    "\n",
    "total_complaints_dict = total_complaints()\n",
    "total_complaints_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "Unable to coerce to Series, length must be 15: given 1",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-182-70a85c358e24>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      8\u001b[0m     \u001b[0;32mreturn\u001b[0m \u001b[0mspecific_complaints_dict\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      9\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 10\u001b[0;31m \u001b[0mspecific_complaints_dict\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mspecific_complaint_type_count\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Loud Music/Party'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     11\u001b[0m \u001b[0mspecific_complaints_dict\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-182-70a85c358e24>\u001b[0m in \u001b[0;36mspecific_complaint_type_count\u001b[0;34m(complaint_type)\u001b[0m\n\u001b[1;32m      5\u001b[0m     \u001b[0;32mfor\u001b[0m \u001b[0mi\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mlist\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mset\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mraw_data\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Incident Zip'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m         specific_complaints_dict[i] = specific_complaints_dict.get(i, len(raw_data.loc([raw_data['Incident Zip'] == i]\n\u001b[0;32m----> 7\u001b[0;31m                                                                     & raw_data.loc[raw_data['Descriptor'] == complaint_type])))\n\u001b[0m\u001b[1;32m      8\u001b[0m     \u001b[0;32mreturn\u001b[0m \u001b[0mspecific_complaints_dict\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      9\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/anaconda3/lib/python3.5/site-packages/pandas/core/ops.py\u001b[0m in \u001b[0;36mf\u001b[0;34m(self, other, axis, level, fill_value)\u001b[0m\n\u001b[1;32m   1545\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mother\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdefault_axis\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlevel\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfill_value\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1546\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1547\u001b[0;31m         \u001b[0mother\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_align_method_FRAME\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mother\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1548\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1549\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mother\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mABCDataFrame\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m  \u001b[0;31m# Another DataFrame\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/anaconda3/lib/python3.5/site-packages/pandas/core/ops.py\u001b[0m in \u001b[0;36m_align_method_FRAME\u001b[0;34m(left, right, axis)\u001b[0m\n\u001b[1;32m   1479\u001b[0m           not isinstance(right, (ABCSeries, ABCDataFrame))):\n\u001b[1;32m   1480\u001b[0m         \u001b[0;31m# GH17901\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1481\u001b[0;31m         \u001b[0mright\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mto_series\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mright\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1482\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1483\u001b[0m     \u001b[0;32mreturn\u001b[0m \u001b[0mright\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/anaconda3/lib/python3.5/site-packages/pandas/core/ops.py\u001b[0m in \u001b[0;36mto_series\u001b[0;34m(right)\u001b[0m\n\u001b[1;32m   1454\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mleft\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mright\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1455\u001b[0m                 raise ValueError(msg.format(req_len=len(left.columns),\n\u001b[0;32m-> 1456\u001b[0;31m                                             given_len=len(right)))\n\u001b[0m\u001b[1;32m   1457\u001b[0m             \u001b[0mright\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mleft\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_constructor_sliced\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mright\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mleft\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1458\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mright\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mValueError\u001b[0m: Unable to coerce to Series, length must be 15: given 1"
     ]
    }
   ],
   "source": [
    "raw_data['Descriptor'] = raw_data['Descriptor'].astype('str')\n",
    "\n",
    "def specific_complaint_type_count(complaint_type):\n",
    "    specific_complaints_dict = {}\n",
    "    for i in list(set(raw_data['Incident Zip'])):\n",
    "        specific_complaints_dict[i] = specific_complaints_dict.get(i, len(raw_data.loc([raw_data['Incident Zip'] == i]\n",
    "                                                                    & raw_data.loc[raw_data['Descriptor'] == complaint_type])))\n",
    "    return specific_complaints_dict\n",
    "\n",
    "specific_complaints_dict = specific_complaint_type_count('Loud Music/Party')\n",
    "specific_complaints_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# now call them, and build out a dataframe, then get your 'max', 'min' 'avg' and per capita of those "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "222"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "len(list(set(raw_data['Incident Zip'])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "& df['other_column'].isin(some_values)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Updating SQL Database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notes on creating and populating a new column, courtesy of StackOverflow\n",
    "\n",
    "ALTER TABLE table ADD COLUMN col3 desired_datatype;\n",
    "\n",
    "SELECT a, b, (case when (a + b) % 2 = 1 then 'Y' else 'N' end) AS col3 FROM table t;\n",
    "\n",
    "OR  \n",
    "\n",
    "UPDATE table SET col3 = (case when (a + b) % 2 = 1 then 'Y' else 'N' end) ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Important Code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_44 = pd.read_sql(\"SELECT * FROM locations\", conn, index_col = \"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Fixing Datatype \n",
    "\n",
    "test_44['zip'] = test_44['zip'].astype('float')\n",
    "test_44['zip'] = test_44['zip'].astype('int')\n",
    "\n",
    "#Population \n",
    "\n",
    "test_44['zip']= test_44['zip'].astype('str')\n",
    "test_44['population'] = test_44['zip'].map(pop_dict)\n",
    "\n",
    "#Neighborhoods \n",
    "\n",
    "test_44['zip'] = test_44['zip'].astype('int')\n",
    "test_44['neighborhood'] = test_44['zip'].map(hoods_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "zip              0\n",
       "borough_id       0\n",
       "population      21\n",
       "neighborhood    46\n",
       "dtype: int64"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_44.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I was getting an error about NaN values, apparently some of these zipcodes don't belong to any neighborhood\n",
    "# or a listed population \n",
    "\n",
    "test_44.fillna({'population':0, 'neighborhood':'Undefined'}, inplace=True)\n",
    "test_44['neighborhood'] = test_44['neighborhood'].astype('str')\n",
    "test_44['population'] = test_44['population'].astype('int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zip</th>\n",
       "      <th>borough_id</th>\n",
       "      <th>population</th>\n",
       "      <th>neighborhood</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10027</td>\n",
       "      <td>1</td>\n",
       "      <td>59707</td>\n",
       "      <td>Central Harlem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11223</td>\n",
       "      <td>2</td>\n",
       "      <td>78731</td>\n",
       "      <td>Southern Brooklyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10461</td>\n",
       "      <td>3</td>\n",
       "      <td>50502</td>\n",
       "      <td>Southeast Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10002</td>\n",
       "      <td>1</td>\n",
       "      <td>81410</td>\n",
       "      <td>Lower East Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>11385</td>\n",
       "      <td>4</td>\n",
       "      <td>98592</td>\n",
       "      <td>West Central Queens</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      zip  borough_id  population         neighborhood\n",
       "id                                                    \n",
       "1   10027           1       59707       Central Harlem\n",
       "2   11223           2       78731    Southern Brooklyn\n",
       "3   10461           3       50502      Southeast Bronx\n",
       "4   10002           1       81410      Lower East Side\n",
       "5   11385           4       98592  West Central Queens"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_44.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_44.to_sql(\"locations\", conn, if_exists = 'replace', index_label = \"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zip</th>\n",
       "      <th>borough_id</th>\n",
       "      <th>population</th>\n",
       "      <th>neighborhood</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10027</td>\n",
       "      <td>1</td>\n",
       "      <td>59707</td>\n",
       "      <td>Central Harlem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11223</td>\n",
       "      <td>2</td>\n",
       "      <td>78731</td>\n",
       "      <td>Southern Brooklyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10461</td>\n",
       "      <td>3</td>\n",
       "      <td>50502</td>\n",
       "      <td>Southeast Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10002</td>\n",
       "      <td>1</td>\n",
       "      <td>81410</td>\n",
       "      <td>Lower East Side</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>11385</td>\n",
       "      <td>4</td>\n",
       "      <td>98592</td>\n",
       "      <td>West Central Queens</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      zip  borough_id  population         neighborhood\n",
       "id                                                    \n",
       "1   10027           1       59707       Central Harlem\n",
       "2   11223           2       78731    Southern Brooklyn\n",
       "3   10461           3       50502      Southeast Bronx\n",
       "4   10002           1       81410      Lower East Side\n",
       "5   11385           4       98592  West Central Queens"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_write_success = pd.read_sql(\"SELECT * FROM locations\", conn, index_col = \"id\")\n",
    "test_write_success.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(222, 4)"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_write_success.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "zip              int64\n",
       "borough_id       int64\n",
       "population       int64\n",
       "neighborhood    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_write_success.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comparison Metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "borough_id\n",
       "1    1577374\n",
       "2    2504700\n",
       "3    1382480\n",
       "4    2311269\n",
       "5     468730\n",
       "6      38982\n",
       "Name: population, dtype: int64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_write_success.groupby('borough_id')['population'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "neighborhood_pops = dict(test_write_success.groupby('neighborhood')['population'].sum())\n",
    "# could also use list here!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "zip\n",
       "0            0\n",
       "83           0\n",
       "10000        0\n",
       "10001    21102\n",
       "10002    81410\n",
       "10003    56024\n",
       "10004     3089\n",
       "10005     7135\n",
       "10006     3011\n",
       "10007     6988\n",
       "10009    61347\n",
       "10010    31834\n",
       "10011    50984\n",
       "10012    24090\n",
       "10013    27700\n",
       "10014    31959\n",
       "10016    54183\n",
       "10017    16575\n",
       "10018     5229\n",
       "10019    42870\n",
       "10020        0\n",
       "10021    43631\n",
       "10022    31924\n",
       "10023    60998\n",
       "10024    59283\n",
       "10025    94600\n",
       "10026    34003\n",
       "10027    59707\n",
       "10028    45141\n",
       "10029    76003\n",
       "         ...  \n",
       "11385    98592\n",
       "11411    18556\n",
       "11412    34882\n",
       "11413    38912\n",
       "11414    26148\n",
       "11415    19341\n",
       "11416    24861\n",
       "11417    28967\n",
       "11418    36256\n",
       "11419    47211\n",
       "11420    44354\n",
       "11421    39127\n",
       "11422    30425\n",
       "11423    29987\n",
       "11426    17590\n",
       "11427    23593\n",
       "11428    19168\n",
       "11429    25105\n",
       "11430      184\n",
       "11432    60809\n",
       "11433    32687\n",
       "11434    59129\n",
       "11435    53687\n",
       "11436    17949\n",
       "11691    60035\n",
       "11692    18540\n",
       "11693    11916\n",
       "11694    20408\n",
       "11697     4079\n",
       "12345        0\n",
       "Name: population, Length: 222, dtype: int64"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_write_success.groupby('zip')['population'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fixing \"For One Address\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "complaints_df = pd.read_sql(\"SELECT * FROM complaints\", conn, index_col = \"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>status</th>\n",
       "      <th>method</th>\n",
       "      <th>date</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>location_id</th>\n",
       "      <th>borough_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Closed</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>02/17/2018</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>1</td>\n",
       "      <td>MANHATTAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Closed</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>02/17/2018</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>2</td>\n",
       "      <td>BROOKLYN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Closed</td>\n",
       "      <td>PHONE</td>\n",
       "      <td>02/17/2018</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>3</td>\n",
       "      <td>BRONX</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                type  status  method        date agency_name  location_id  \\\n",
       "id                                                                          \n",
       "1   Loud Music/Party  Closed  ONLINE  02/17/2018        NYPD            1   \n",
       "2   Loud Music/Party  Closed  ONLINE  02/17/2018        NYPD            2   \n",
       "3   Loud Music/Party  Closed   PHONE  02/17/2018        NYPD            3   \n",
       "\n",
       "   borough_name  \n",
       "id               \n",
       "1     MANHATTAN  \n",
       "2      BROOKLYN  \n",
       "3         BRONX  "
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complaints_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "complaints_df.replace(to_replace = \"For One Address\", value = \"Tree Request\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['type', 'status', 'method', 'date', 'agency_name', 'location_id',\n",
       "       'borough_name'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complaints_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>status</th>\n",
       "      <th>method</th>\n",
       "      <th>date</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>borough_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>location_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7672</td>\n",
       "      <td>7672</td>\n",
       "      <td>7672</td>\n",
       "      <td>7672</td>\n",
       "      <td>7672</td>\n",
       "      <td>7672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2753</td>\n",
       "      <td>2753</td>\n",
       "      <td>2753</td>\n",
       "      <td>2753</td>\n",
       "      <td>2753</td>\n",
       "      <td>2753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2947</td>\n",
       "      <td>2947</td>\n",
       "      <td>2947</td>\n",
       "      <td>2947</td>\n",
       "      <td>2947</td>\n",
       "      <td>2947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10948</td>\n",
       "      <td>10948</td>\n",
       "      <td>10948</td>\n",
       "      <td>10948</td>\n",
       "      <td>10948</td>\n",
       "      <td>10948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7983</td>\n",
       "      <td>7983</td>\n",
       "      <td>7983</td>\n",
       "      <td>7983</td>\n",
       "      <td>7983</td>\n",
       "      <td>7983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6310</td>\n",
       "      <td>6310</td>\n",
       "      <td>6310</td>\n",
       "      <td>6310</td>\n",
       "      <td>6310</td>\n",
       "      <td>6310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7590</td>\n",
       "      <td>7590</td>\n",
       "      <td>7590</td>\n",
       "      <td>7590</td>\n",
       "      <td>7590</td>\n",
       "      <td>7590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10920</td>\n",
       "      <td>10920</td>\n",
       "      <td>10920</td>\n",
       "      <td>10920</td>\n",
       "      <td>10920</td>\n",
       "      <td>10920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10215</td>\n",
       "      <td>10215</td>\n",
       "      <td>10215</td>\n",
       "      <td>10215</td>\n",
       "      <td>10215</td>\n",
       "      <td>10215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10728</td>\n",
       "      <td>10728</td>\n",
       "      <td>10728</td>\n",
       "      <td>10728</td>\n",
       "      <td>10728</td>\n",
       "      <td>10728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1985</td>\n",
       "      <td>1985</td>\n",
       "      <td>1985</td>\n",
       "      <td>1985</td>\n",
       "      <td>1985</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7994</td>\n",
       "      <td>7994</td>\n",
       "      <td>7994</td>\n",
       "      <td>7994</td>\n",
       "      <td>7994</td>\n",
       "      <td>7994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>829</td>\n",
       "      <td>829</td>\n",
       "      <td>829</td>\n",
       "      <td>829</td>\n",
       "      <td>829</td>\n",
       "      <td>829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10117</td>\n",
       "      <td>10117</td>\n",
       "      <td>10117</td>\n",
       "      <td>10117</td>\n",
       "      <td>10117</td>\n",
       "      <td>10117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15036</td>\n",
       "      <td>15036</td>\n",
       "      <td>15036</td>\n",
       "      <td>15036</td>\n",
       "      <td>15036</td>\n",
       "      <td>15036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>12506</td>\n",
       "      <td>12506</td>\n",
       "      <td>12506</td>\n",
       "      <td>12506</td>\n",
       "      <td>12506</td>\n",
       "      <td>12506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4601</td>\n",
       "      <td>4601</td>\n",
       "      <td>4601</td>\n",
       "      <td>4601</td>\n",
       "      <td>4601</td>\n",
       "      <td>4601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10406</td>\n",
       "      <td>10406</td>\n",
       "      <td>10406</td>\n",
       "      <td>10406</td>\n",
       "      <td>10406</td>\n",
       "      <td>10406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>3691</td>\n",
       "      <td>3691</td>\n",
       "      <td>3691</td>\n",
       "      <td>3691</td>\n",
       "      <td>3691</td>\n",
       "      <td>3691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>4596</td>\n",
       "      <td>4596</td>\n",
       "      <td>4596</td>\n",
       "      <td>4596</td>\n",
       "      <td>4596</td>\n",
       "      <td>4596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2624</td>\n",
       "      <td>2624</td>\n",
       "      <td>2624</td>\n",
       "      <td>2624</td>\n",
       "      <td>2624</td>\n",
       "      <td>2624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>7992</td>\n",
       "      <td>7992</td>\n",
       "      <td>7992</td>\n",
       "      <td>7992</td>\n",
       "      <td>7992</td>\n",
       "      <td>7992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>4267</td>\n",
       "      <td>4267</td>\n",
       "      <td>4267</td>\n",
       "      <td>4267</td>\n",
       "      <td>4267</td>\n",
       "      <td>4267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>12072</td>\n",
       "      <td>12072</td>\n",
       "      <td>12072</td>\n",
       "      <td>12072</td>\n",
       "      <td>12072</td>\n",
       "      <td>12072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>818</td>\n",
       "      <td>818</td>\n",
       "      <td>818</td>\n",
       "      <td>818</td>\n",
       "      <td>818</td>\n",
       "      <td>818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>3366</td>\n",
       "      <td>3366</td>\n",
       "      <td>3366</td>\n",
       "      <td>3366</td>\n",
       "      <td>3366</td>\n",
       "      <td>3366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>5003</td>\n",
       "      <td>5003</td>\n",
       "      <td>5003</td>\n",
       "      <td>5003</td>\n",
       "      <td>5003</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1982</td>\n",
       "      <td>1982</td>\n",
       "      <td>1982</td>\n",
       "      <td>1982</td>\n",
       "      <td>1982</td>\n",
       "      <td>1982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>8780</td>\n",
       "      <td>8780</td>\n",
       "      <td>8780</td>\n",
       "      <td>8780</td>\n",
       "      <td>8780</td>\n",
       "      <td>8780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>3496</td>\n",
       "      <td>3496</td>\n",
       "      <td>3496</td>\n",
       "      <td>3496</td>\n",
       "      <td>3496</td>\n",
       "      <td>3496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>222 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              type  status  method   date  agency_name  borough_name\n",
       "location_id                                                         \n",
       "1             7672    7672    7672   7672         7672          7672\n",
       "2             2753    2753    2753   2753         2753          2753\n",
       "3             2947    2947    2947   2947         2947          2947\n",
       "4            10948   10948   10948  10948        10948         10948\n",
       "5             7983    7983    7983   7983         7983          7983\n",
       "6             6310    6310    6310   6310         6310          6310\n",
       "7             7590    7590    7590   7590         7590          7590\n",
       "8            10920   10920   10920  10920        10920         10920\n",
       "9            10215   10215   10215  10215        10215         10215\n",
       "10           10728   10728   10728  10728        10728         10728\n",
       "11            1985    1985    1985   1985         1985          1985\n",
       "12            7994    7994    7994   7994         7994          7994\n",
       "13             829     829     829    829          829           829\n",
       "14           10117   10117   10117  10117        10117         10117\n",
       "15           15036   15036   15036  15036        15036         15036\n",
       "16           12506   12506   12506  12506        12506         12506\n",
       "17            4601    4601    4601   4601         4601          4601\n",
       "18           10406   10406   10406  10406        10406         10406\n",
       "19            3691    3691    3691   3691         3691          3691\n",
       "20            4596    4596    4596   4596         4596          4596\n",
       "21            2624    2624    2624   2624         2624          2624\n",
       "22            7992    7992    7992   7992         7992          7992\n",
       "23            4267    4267    4267   4267         4267          4267\n",
       "24           12072   12072   12072  12072        12072         12072\n",
       "25             818     818     818    818          818           818\n",
       "26            3366    3366    3366   3366         3366          3366\n",
       "27            5003    5003    5003   5003         5003          5003\n",
       "28            1982    1982    1982   1982         1982          1982\n",
       "29            8780    8780    8780   8780         8780          8780\n",
       "30            3496    3496    3496   3496         3496          3496\n",
       "...            ...     ...     ...    ...          ...           ...\n",
       "193              9       9       9      9            9             9\n",
       "194              5       5       5      5            5             5\n",
       "195              1       1       1      1            1             1\n",
       "196             36      36      36     36           36            36\n",
       "197              1       1       1      1            1             1\n",
       "198             17      17      17     17           17            17\n",
       "199             12      12      12     12           12            12\n",
       "200              1       1       1      1            1             1\n",
       "201              3       3       3      3            3             3\n",
       "202              3       3       3      3            3             3\n",
       "203              1       1       1      1            1             1\n",
       "204              1       1       1      1            1             1\n",
       "205              1       1       1      1            1             1\n",
       "206              3       3       3      3            3             3\n",
       "207             11      11      11     11           11            11\n",
       "208              1       1       1      1            1             1\n",
       "209              1       1       1      1            1             1\n",
       "210              1       1       1      1            1             1\n",
       "211              1       1       1      1            1             1\n",
       "212              1       1       1      1            1             1\n",
       "213              1       1       1      1            1             1\n",
       "214              1       1       1      1            1             1\n",
       "215              2       2       2      2            2             2\n",
       "216              2       2       2      2            2             2\n",
       "217              3       3       3      3            3             3\n",
       "218              1       1       1      1            1             1\n",
       "219              1       1       1      1            1             1\n",
       "220              1       1       1      1            1             1\n",
       "221              1       1       1      1            1             1\n",
       "222              1       1       1      1            1             1\n",
       "\n",
       "[222 rows x 6 columns]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complaints_df.groupby('location_id').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "complaints_df.to_sql(\"complaints\", conn, if_exists = 'replace', index_label = \"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}