query_tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "suspended-roulette",
   "metadata": {},
   "source": [
    "# Analyzing 311 Street Flooding Complaints in NYC for Tropical Storm Elsa (7/9) as well as the Flash Flood the day before (7/8) \n",
    "### Note: complaint duration is capped on 7/12\n",
    "\n",
    "Mark Bauer  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "czech-attribute",
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sodapy import Socrata\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "aware-riding",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The watermark extension is already loaded. To reload it, use:\n",
      "  %reload_ext watermark\n"
     ]
    }
   ],
   "source": [
    "%load_ext watermark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "wound-waste",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPython 3.7.1\n",
      "IPython 7.20.0\n",
      "\n",
      "numpy 1.19.2\n",
      "pandas 1.2.1\n"
     ]
    }
   ],
   "source": [
    "%watermark -v -p numpy,pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "gross-cleveland",
   "metadata": {},
   "outputs": [],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "# socrata token\n",
    "# register for one here: https://dev.socrata.com/docs/app-tokens.html\n",
    "socrata_token = os.environ.get(\"SODAPY_APPTOKEN\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "connected-corps",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['unique_key',\n",
       " 'created_date',\n",
       " 'closed_date',\n",
       " 'agency',\n",
       " 'agency_name',\n",
       " 'complaint_type',\n",
       " 'descriptor',\n",
       " 'location_type',\n",
       " 'incident_zip',\n",
       " 'incident_address',\n",
       " 'street_name',\n",
       " 'cross_street_1',\n",
       " 'cross_street_2',\n",
       " 'intersection_street_1',\n",
       " 'intersection_street_2',\n",
       " 'address_type',\n",
       " 'city',\n",
       " 'landmark',\n",
       " 'facility_type',\n",
       " 'status',\n",
       " 'due_date',\n",
       " 'resolution_description',\n",
       " 'resolution_action_updated_date',\n",
       " 'community_board',\n",
       " 'bbl',\n",
       " 'borough',\n",
       " 'x_coordinate_state_plane',\n",
       " 'y_coordinate_state_plane',\n",
       " 'open_data_channel_type',\n",
       " 'park_facility_name',\n",
       " 'park_borough',\n",
       " 'vehicle_type',\n",
       " 'taxi_company_borough',\n",
       " 'taxi_pick_up_location',\n",
       " 'bridge_highway_name',\n",
       " 'bridge_highway_direction',\n",
       " 'road_ramp',\n",
       " 'bridge_highway_segment',\n",
       " 'latitude',\n",
       " 'longitude',\n",
       " 'location',\n",
       " ':@computed_region_efsh_h5xi',\n",
       " ':@computed_region_f5dn_yrer',\n",
       " ':@computed_region_yeji_bk3q',\n",
       " ':@computed_region_92fq_4b7q',\n",
       " ':@computed_region_sbqj_enih']"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client = Socrata(socrata_domain, socrata_token)\n",
    "\n",
    "# good practice to review the columns\n",
    "metadata = client.get_metadata(socrata_dataset_identifier)\n",
    "[x['fieldName'] for x in metadata['columns']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "characteristic-handbook",
   "metadata": {},
   "source": [
    "# exploring the full dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "reverse-charleston",
   "metadata": {},
   "source": [
    "## checking for the word 'flood' in the descriptor column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "refined-provincial",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11, 2)\n"
     ]
    },
    {
     "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>descriptor</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Catch Basin Clogged/Flooding (Use Comments) (SC)</td>\n",
       "      <td>92577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>28748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Flood Light Lamp Out</td>\n",
       "      <td>6042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Highway Flooding (SH)</td>\n",
       "      <td>2871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Flood Light Lamp Cycling</td>\n",
       "      <td>2522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Ready NY - Flooding</td>\n",
       "      <td>271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Flood Light Lamp Dayburning</td>\n",
       "      <td>207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Flood Light Lamp Missing</td>\n",
       "      <td>193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Flood Light Lamp Dim</td>\n",
       "      <td>178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>RAIN GARDEN FLOODING (SRGFLD)</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Flooded</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          descriptor  count\n",
       "0   Catch Basin Clogged/Flooding (Use Comments) (SC)  92577\n",
       "1                               Street Flooding (SJ)  28748\n",
       "2                               Flood Light Lamp Out   6042\n",
       "3                              Highway Flooding (SH)   2871\n",
       "4                           Flood Light Lamp Cycling   2522\n",
       "5                                Ready NY - Flooding    271\n",
       "6                        Flood Light Lamp Dayburning    207\n",
       "7                           Flood Light Lamp Missing    193\n",
       "8                               Flood Light Lamp Dim    178\n",
       "9                      RAIN GARDEN FLOODING (SRGFLD)    107\n",
       "10                                           Flooded     48"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client = Socrata(\"data.cityofnewyork.us\", socrata_token, timeout=1000)\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    descriptor, count(descriptor) AS count\n",
    "WHERE \n",
    "    LOWER(descriptor) LIKE '%flood%'\n",
    "GROUP BY \n",
    "    descriptor\n",
    "ORDER BY \n",
    "    count(descriptor) DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\"erm2-nwe9\", query=query)\n",
    "\n",
    "# Convert to pandas DataFrame\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(results_df.shape)\n",
    "results_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "skilled-navigator",
   "metadata": {},
   "source": [
    "## checking for all the descriptor values where complaint_type = 'Sewer'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "signed-moses",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(27, 2)\n"
     ]
    },
    {
     "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>descriptor</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sewer Backup (Use Comments) (SA)</td>\n",
       "      <td>154364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Catch Basin Clogged/Flooding (Use Comments) (SC)</td>\n",
       "      <td>92577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Catch Basin Sunken/Damaged/Raised (SC1)</td>\n",
       "      <td>29260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>28748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Manhole Cover Broken/Making Noise (SB)</td>\n",
       "      <td>19793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Manhole Cover Missing (Emergency) (SA3)</td>\n",
       "      <td>18083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Sewer Odor (SA2)</td>\n",
       "      <td>15935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Defective/Missing Curb Piece (SC4)</td>\n",
       "      <td>8642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Manhole Overflow (Use Comments) (SA1)</td>\n",
       "      <td>7181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Catch Basin Search (SC2)</td>\n",
       "      <td>4154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Catch Basin Grating Missing (SA4)</td>\n",
       "      <td>3857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Manhole Sunken/Damaged/Raised (SB1)</td>\n",
       "      <td>3455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Highway Flooding (SH)</td>\n",
       "      <td>2871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Culvert Blocked/Needs Cleaning (SE)</td>\n",
       "      <td>2419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Other Sewer Problem (Use Comments) (SZZ)</td>\n",
       "      <td>1698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Sewer Break (SBR)</td>\n",
       "      <td>1038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Plate Noisy/Sunken/Raised (SB5)</td>\n",
       "      <td>875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Plate Missing/Moved-Exposing Hole (SB4)</td>\n",
       "      <td>646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>RAIN GARDEN DEBRIS (SRGDBR)</td>\n",
       "      <td>461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Street Cave-In / Depression (SG)</td>\n",
       "      <td>302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Trench (SZZ1)</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>RAIN GARDEN FLOODING (SRGFLD)</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>DAMAGE STRUCTURE/RAILING (SRGDM)</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Street Cave-In *Dep Internal Use Only* (SG1)</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Recurring after SOAP *FOR DEP INTERNAL USE ONL...</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Catch Basin Connection Broken *Dep Internal Us...</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>SOAP segment *FOR DEP INTERNAL USE ONLY* (SOAP...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           descriptor   count\n",
       "0                    Sewer Backup (Use Comments) (SA)  154364\n",
       "1    Catch Basin Clogged/Flooding (Use Comments) (SC)   92577\n",
       "2             Catch Basin Sunken/Damaged/Raised (SC1)   29260\n",
       "3                                Street Flooding (SJ)   28748\n",
       "4              Manhole Cover Broken/Making Noise (SB)   19793\n",
       "5             Manhole Cover Missing (Emergency) (SA3)   18083\n",
       "6                                    Sewer Odor (SA2)   15935\n",
       "7                  Defective/Missing Curb Piece (SC4)    8642\n",
       "8               Manhole Overflow (Use Comments) (SA1)    7181\n",
       "9                            Catch Basin Search (SC2)    4154\n",
       "10                  Catch Basin Grating Missing (SA4)    3857\n",
       "11                Manhole Sunken/Damaged/Raised (SB1)    3455\n",
       "12                              Highway Flooding (SH)    2871\n",
       "13                Culvert Blocked/Needs Cleaning (SE)    2419\n",
       "14           Other Sewer Problem (Use Comments) (SZZ)    1698\n",
       "15                                  Sewer Break (SBR)    1038\n",
       "16                    Plate Noisy/Sunken/Raised (SB5)     875\n",
       "17            Plate Missing/Moved-Exposing Hole (SB4)     646\n",
       "18                        RAIN GARDEN DEBRIS (SRGDBR)     461\n",
       "19                   Street Cave-In / Depression (SG)     302\n",
       "20                                      Trench (SZZ1)     192\n",
       "21                      RAIN GARDEN FLOODING (SRGFLD)     107\n",
       "22                   DAMAGE STRUCTURE/RAILING (SRGDM)      68\n",
       "23       Street Cave-In *Dep Internal Use Only* (SG1)      31\n",
       "24  Recurring after SOAP *FOR DEP INTERNAL USE ONL...      29\n",
       "25  Catch Basin Connection Broken *Dep Internal Us...      19\n",
       "26  SOAP segment *FOR DEP INTERNAL USE ONLY* (SOAP...       1"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client = Socrata(\"data.cityofnewyork.us\", socrata_token, timeout=1000)\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    descriptor, count(descriptor) AS count\n",
    "WHERE \n",
    "    complaint_type='Sewer'\n",
    "GROUP BY \n",
    "    descriptor\n",
    "ORDER BY \n",
    "    count(descriptor) DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\"erm2-nwe9\", query=query)\n",
    "\n",
    "# Convert to pandas DataFrame\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(results_df.shape)\n",
    "results_df.head(len(results_df))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "continuous-turner",
   "metadata": {},
   "source": [
    "# query data for only street flooding complaints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "gorgeous-driving",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(28748, 37)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_key</th>\n",
       "      <th>created_date</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>complaint_type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>street_name</th>\n",
       "      <th>...</th>\n",
       "      <th>location</th>\n",
       "      <th>day</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>hour</th>\n",
       "      <th>minute</th>\n",
       "      <th>year_month_day</th>\n",
       "      <th>intersection_street_1</th>\n",
       "      <th>intersection_street_2</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>due_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50465779</td>\n",
       "      <td>2021-05-04T17:58:00.000</td>\n",
       "      <td>2021-05-05T07:15:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>11226</td>\n",
       "      <td>2225 TILDEN AVENUE</td>\n",
       "      <td>TILDEN AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.64660766955987', 'longitude':...</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>58</td>\n",
       "      <td>2021-05-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50471923</td>\n",
       "      <td>2021-05-04T10:25:00.000</td>\n",
       "      <td>2021-05-05T08:45:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>11234</td>\n",
       "      <td>1477 EAST   51 STREET</td>\n",
       "      <td>EAST   51 STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.62449690722713', 'longitude':...</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>25</td>\n",
       "      <td>2021-05-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>45123962</td>\n",
       "      <td>2019-12-10T12:13:00.000</td>\n",
       "      <td>2019-12-11T11:45:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>11434</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.66016771824149', 'longitude':...</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>2019-12-10</td>\n",
       "      <td>147 AVENUE</td>\n",
       "      <td>ROCKAWAY BOULEVARD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45123981</td>\n",
       "      <td>2019-12-10T16:23:00.000</td>\n",
       "      <td>2019-12-11T08:30:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>10022</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.761668277085114', 'longitude'...</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>23</td>\n",
       "      <td>2019-12-10</td>\n",
       "      <td>5 AVENUE</td>\n",
       "      <td>WEST 55 STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>45123982</td>\n",
       "      <td>2019-12-10T14:30:00.000</td>\n",
       "      <td>2019-12-15T09:20:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>11205</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.69909044367837', 'longitude':...</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>30</td>\n",
       "      <td>2019-12-10</td>\n",
       "      <td>FLUSHING AVENUE</td>\n",
       "      <td>SPENCER STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_key             created_date              closed_date agency  \\\n",
       "0   50465779  2021-05-04T17:58:00.000  2021-05-05T07:15:00.000    DEP   \n",
       "1   50471923  2021-05-04T10:25:00.000  2021-05-05T08:45:00.000    DEP   \n",
       "2   45123962  2019-12-10T12:13:00.000  2019-12-11T11:45:00.000    DEP   \n",
       "3   45123981  2019-12-10T16:23:00.000  2019-12-11T08:30:00.000    DEP   \n",
       "4   45123982  2019-12-10T14:30:00.000  2019-12-15T09:20:00.000    DEP   \n",
       "\n",
       "                              agency_name complaint_type  \\\n",
       "0  Department of Environmental Protection          Sewer   \n",
       "1  Department of Environmental Protection          Sewer   \n",
       "2  Department of Environmental Protection          Sewer   \n",
       "3  Department of Environmental Protection          Sewer   \n",
       "4  Department of Environmental Protection          Sewer   \n",
       "\n",
       "             descriptor incident_zip       incident_address       street_name  \\\n",
       "0  Street Flooding (SJ)        11226     2225 TILDEN AVENUE     TILDEN AVENUE   \n",
       "1  Street Flooding (SJ)        11234  1477 EAST   51 STREET  EAST   51 STREET   \n",
       "2  Street Flooding (SJ)        11434                    NaN               NaN   \n",
       "3  Street Flooding (SJ)        10022                    NaN               NaN   \n",
       "4  Street Flooding (SJ)        11205                    NaN               NaN   \n",
       "\n",
       "   ...                                           location day day_of_week  \\\n",
       "0  ...  {'latitude': '40.64660766955987', 'longitude':...   4           2   \n",
       "1  ...  {'latitude': '40.62449690722713', 'longitude':...   4           2   \n",
       "2  ...  {'latitude': '40.66016771824149', 'longitude':...  10           2   \n",
       "3  ...  {'latitude': '40.761668277085114', 'longitude'...  10           2   \n",
       "4  ...  {'latitude': '40.69909044367837', 'longitude':...  10           2   \n",
       "\n",
       "  hour minute year_month_day intersection_street_1 intersection_street_2  \\\n",
       "0   17     58     2021-05-04                   NaN                   NaN   \n",
       "1   10     25     2021-05-04                   NaN                   NaN   \n",
       "2   12     13     2019-12-10            147 AVENUE    ROCKAWAY BOULEVARD   \n",
       "3   16     23     2019-12-10              5 AVENUE        WEST 55 STREET   \n",
       "4   14     30     2019-12-10       FLUSHING AVENUE        SPENCER STREET   \n",
       "\n",
       "  facility_type due_date  \n",
       "0           NaN      NaN  \n",
       "1           NaN      NaN  \n",
       "2           NaN      NaN  \n",
       "3           NaN      NaN  \n",
       "4           NaN      NaN  \n",
       "\n",
       "[5 rows x 37 columns]"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client = Socrata(\"data.cityofnewyork.us\", socrata_token, timeout=1000)\n",
    "\n",
    "# query all columns as well as extracting date information as new columns\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    *,\n",
    "    date_extract_d(created_date) AS day,\n",
    "    date_extract_dow(created_date) AS day_of_week,\n",
    "    date_extract_hh(created_date) AS hour,\n",
    "    date_extract_mm(created_date) AS minute,\n",
    "    date_trunc_ymd(created_date) AS year_month_day\n",
    "WHERE \n",
    "    descriptor = 'Street Flooding (SJ)'\n",
    "LIMIT \n",
    "    1000000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\"erm2-nwe9\", query=query)\n",
    "\n",
    "# Convert to pandas DataFrame\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "# format new date column\n",
    "results_df['year_month_day'] = results_df['year_month_day'].str.split('T').str[0]\n",
    "\n",
    "print(results_df.shape)\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "legal-latino",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of total records: 28,748.\n",
      "\n",
      "date information:\n",
      "Min: 2010-01-02T08:26:00.000.\n",
      "Max: 2021-07-14T23:05:00.000\n"
     ]
    }
   ],
   "source": [
    "print('Number of total records: {:,}.'.format(len(results_df)))\n",
    "\n",
    "print('\\ndate information:')\n",
    "print('Min: {}.'.format(results_df.created_date.min()))\n",
    "print('Max: {}'.format(results_df.created_date.max()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "prostate-lingerie",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(190, 37)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_key</th>\n",
       "      <th>created_date</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>complaint_type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>street_name</th>\n",
       "      <th>...</th>\n",
       "      <th>location</th>\n",
       "      <th>day</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>hour</th>\n",
       "      <th>minute</th>\n",
       "      <th>year_month_day</th>\n",
       "      <th>intersection_street_1</th>\n",
       "      <th>intersection_street_2</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>due_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>51143675</td>\n",
       "      <td>2021-07-08T16:04:00.000</td>\n",
       "      <td>2021-07-09T10:30:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>10471</td>\n",
       "      <td>6485 BROADWAY</td>\n",
       "      <td>BROADWAY</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.905351378307486', 'longitude'...</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>2021-07-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51145094</td>\n",
       "      <td>2021-07-08T16:00:00.000</td>\n",
       "      <td>2021-07-08T20:45:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>10034</td>\n",
       "      <td>200 NAGLE AVENUE</td>\n",
       "      <td>NAGLE AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.86210236351487', 'longitude':...</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-07-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51146482</td>\n",
       "      <td>2021-07-08T21:04:00.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>11236</td>\n",
       "      <td>1035 EAST  102 STREET</td>\n",
       "      <td>EAST  102 STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.6437429889248', 'longitude': ...</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "      <td>2021-07-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51148924</td>\n",
       "      <td>2021-07-08T17:04:00.000</td>\n",
       "      <td>2021-07-09T07:44:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>10471</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.900731405929164', 'longitude'...</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>2021-07-08</td>\n",
       "      <td>FIELDSTON ROAD</td>\n",
       "      <td>WEST 253 STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51139519</td>\n",
       "      <td>2021-07-08T16:44:00.000</td>\n",
       "      <td>2021-07-09T17:15:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>11432</td>\n",
       "      <td>172-39 HIGHLAND AVENUE</td>\n",
       "      <td>HIGHLAND AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.71338015366497', 'longitude':...</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>44</td>\n",
       "      <td>2021-07-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_key             created_date              closed_date agency  \\\n",
       "0   51143675  2021-07-08T16:04:00.000  2021-07-09T10:30:00.000    DEP   \n",
       "1   51145094  2021-07-08T16:00:00.000  2021-07-08T20:45:00.000    DEP   \n",
       "2   51146482  2021-07-08T21:04:00.000                      NaN    DEP   \n",
       "3   51148924  2021-07-08T17:04:00.000  2021-07-09T07:44:00.000    DEP   \n",
       "4   51139519  2021-07-08T16:44:00.000  2021-07-09T17:15:00.000    DEP   \n",
       "\n",
       "                              agency_name complaint_type  \\\n",
       "0  Department of Environmental Protection          Sewer   \n",
       "1  Department of Environmental Protection          Sewer   \n",
       "2  Department of Environmental Protection          Sewer   \n",
       "3  Department of Environmental Protection          Sewer   \n",
       "4  Department of Environmental Protection          Sewer   \n",
       "\n",
       "             descriptor incident_zip        incident_address  \\\n",
       "0  Street Flooding (SJ)        10471           6485 BROADWAY   \n",
       "1  Street Flooding (SJ)        10034        200 NAGLE AVENUE   \n",
       "2  Street Flooding (SJ)        11236   1035 EAST  102 STREET   \n",
       "3  Street Flooding (SJ)        10471                     NaN   \n",
       "4  Street Flooding (SJ)        11432  172-39 HIGHLAND AVENUE   \n",
       "\n",
       "        street_name  ...                                           location  \\\n",
       "0          BROADWAY  ...  {'latitude': '40.905351378307486', 'longitude'...   \n",
       "1      NAGLE AVENUE  ...  {'latitude': '40.86210236351487', 'longitude':...   \n",
       "2  EAST  102 STREET  ...  {'latitude': '40.6437429889248', 'longitude': ...   \n",
       "3               NaN  ...  {'latitude': '40.900731405929164', 'longitude'...   \n",
       "4   HIGHLAND AVENUE  ...  {'latitude': '40.71338015366497', 'longitude':...   \n",
       "\n",
       "  day day_of_week hour minute year_month_day intersection_street_1  \\\n",
       "0   8           4   16      4     2021-07-08                   NaN   \n",
       "1   8           4   16      0     2021-07-08                   NaN   \n",
       "2   8           4   21      4     2021-07-08                   NaN   \n",
       "3   8           4   17      4     2021-07-08        FIELDSTON ROAD   \n",
       "4   8           4   16     44     2021-07-08                   NaN   \n",
       "\n",
       "  intersection_street_2 facility_type due_date  \n",
       "0                   NaN           NaN      NaN  \n",
       "1                   NaN           NaN      NaN  \n",
       "2                   NaN           NaN      NaN  \n",
       "3       WEST 253 STREET           NaN      NaN  \n",
       "4                   NaN           NaN      NaN  \n",
       "\n",
       "[5 rows x 37 columns]"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# saving only data for hurricane elsa\n",
    "elsa_df = results_df.loc[(results_df[\"year_month_day\"] > '2021-07-07') & \n",
    "                         (results_df[\"year_month_day\"] < '2021-07-13')]\n",
    "\n",
    "# reset index\n",
    "elsa_df = elsa_df.reset_index(drop=True)\n",
    "\n",
    "print(elsa_df.shape)\n",
    "elsa_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "average-museum",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of total records: 190.\n",
      "\n",
      "date information:\n",
      "Min: 2021-07-08T08:17:00.000.\n",
      "Max: 2021-07-12T20:31:00.000\n"
     ]
    }
   ],
   "source": [
    "print('Number of total records: {:,}.'.format(len(elsa_df))) \n",
    "\n",
    "print('\\ndate information:')\n",
    "print('Min: {}.'.format(elsa_df.created_date.min()))\n",
    "print('Max: {}'.format(elsa_df.created_date.max()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "respected-footage",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number of complaints not closed: 53.\n",
      "% of total: 27.89%.\n"
     ]
    }
   ],
   "source": [
    "print('number of complaints not closed: {}.'.format(elsa_df['closed_date'].isnull().sum()))\n",
    "print('% of total: {:.2f}%.'.format(elsa_df['closed_date'].isnull().sum() / len(elsa_df) * 100))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "greenhouse-madness",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 190 entries, 0 to 189\n",
      "Data columns (total 37 columns):\n",
      " #   Column                          Non-Null Count  Dtype \n",
      "---  ------                          --------------  ----- \n",
      " 0   unique_key                      190 non-null    object\n",
      " 1   created_date                    190 non-null    object\n",
      " 2   closed_date                     137 non-null    object\n",
      " 3   agency                          190 non-null    object\n",
      " 4   agency_name                     190 non-null    object\n",
      " 5   complaint_type                  190 non-null    object\n",
      " 6   descriptor                      190 non-null    object\n",
      " 7   incident_zip                    190 non-null    object\n",
      " 8   incident_address                151 non-null    object\n",
      " 9   street_name                     151 non-null    object\n",
      " 10  cross_street_1                  149 non-null    object\n",
      " 11  cross_street_2                  149 non-null    object\n",
      " 12  address_type                    190 non-null    object\n",
      " 13  city                            190 non-null    object\n",
      " 14  status                          190 non-null    object\n",
      " 15  resolution_description          140 non-null    object\n",
      " 16  resolution_action_updated_date  140 non-null    object\n",
      " 17  community_board                 190 non-null    object\n",
      " 18  bbl                             145 non-null    object\n",
      " 19  borough                         190 non-null    object\n",
      " 20  x_coordinate_state_plane        190 non-null    object\n",
      " 21  y_coordinate_state_plane        190 non-null    object\n",
      " 22  open_data_channel_type          190 non-null    object\n",
      " 23  park_facility_name              190 non-null    object\n",
      " 24  park_borough                    190 non-null    object\n",
      " 25  latitude                        190 non-null    object\n",
      " 26  longitude                       190 non-null    object\n",
      " 27  location                        190 non-null    object\n",
      " 28  day                             190 non-null    object\n",
      " 29  day_of_week                     190 non-null    object\n",
      " 30  hour                            190 non-null    object\n",
      " 31  minute                          190 non-null    object\n",
      " 32  year_month_day                  190 non-null    object\n",
      " 33  intersection_street_1           39 non-null     object\n",
      " 34  intersection_street_2           39 non-null     object\n",
      " 35  facility_type                   0 non-null      object\n",
      " 36  due_date                        0 non-null      object\n",
      "dtypes: object(37)\n",
      "memory usage: 55.0+ KB\n"
     ]
    }
   ],
   "source": [
    "# concise summary of a DataFrame\n",
    "elsa_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "blocked-deployment",
   "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>sample_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>unique_key</th>\n",
       "      <td>51143675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>created_date</th>\n",
       "      <td>2021-07-08T16:04:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>closed_date</th>\n",
       "      <td>2021-07-09T10:30:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>agency</th>\n",
       "      <td>DEP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>agency_name</th>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>complaint_type</th>\n",
       "      <td>Sewer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>descriptor</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>incident_zip</th>\n",
       "      <td>10471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>incident_address</th>\n",
       "      <td>6485 BROADWAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_name</th>\n",
       "      <td>BROADWAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cross_street_1</th>\n",
       "      <td>DAVID SHERIDAN PLZA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cross_street_2</th>\n",
       "      <td>W 259 ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>address_type</th>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <td>BRONX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>status</th>\n",
       "      <td>Closed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>resolution_description</th>\n",
       "      <td>The Department of Environment Protection inspe...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>resolution_action_updated_date</th>\n",
       "      <td>2021-07-09T10:30:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>community_board</th>\n",
       "      <td>08 BRONX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bbl</th>\n",
       "      <td>2058560378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>borough</th>\n",
       "      <td>BRONX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>x_coordinate_state_plane</th>\n",
       "      <td>1012872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y_coordinate_state_plane</th>\n",
       "      <td>269139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>open_data_channel_type</th>\n",
       "      <td>ONLINE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>park_facility_name</th>\n",
       "      <td>Unspecified</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>park_borough</th>\n",
       "      <td>BRONX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>latitude</th>\n",
       "      <td>40.905351378307486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>longitude</th>\n",
       "      <td>-73.89645716668805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>location</th>\n",
       "      <td>{'latitude': '40.905351378307486', 'longitude'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day_of_week</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hour</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>minute</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year_month_day</th>\n",
       "      <td>2021-07-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>intersection_street_1</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>intersection_street_2</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>facility_type</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>due_date</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                     sample_value\n",
       "unique_key                                                               51143675\n",
       "created_date                                              2021-07-08T16:04:00.000\n",
       "closed_date                                               2021-07-09T10:30:00.000\n",
       "agency                                                                        DEP\n",
       "agency_name                                Department of Environmental Protection\n",
       "complaint_type                                                              Sewer\n",
       "descriptor                                                   Street Flooding (SJ)\n",
       "incident_zip                                                                10471\n",
       "incident_address                                                    6485 BROADWAY\n",
       "street_name                                                              BROADWAY\n",
       "cross_street_1                                                DAVID SHERIDAN PLZA\n",
       "cross_street_2                                                           W 259 ST\n",
       "address_type                                                              ADDRESS\n",
       "city                                                                        BRONX\n",
       "status                                                                     Closed\n",
       "resolution_description          The Department of Environment Protection inspe...\n",
       "resolution_action_updated_date                            2021-07-09T10:30:00.000\n",
       "community_board                                                          08 BRONX\n",
       "bbl                                                                    2058560378\n",
       "borough                                                                     BRONX\n",
       "x_coordinate_state_plane                                                  1012872\n",
       "y_coordinate_state_plane                                                   269139\n",
       "open_data_channel_type                                                     ONLINE\n",
       "park_facility_name                                                    Unspecified\n",
       "park_borough                                                                BRONX\n",
       "latitude                                                       40.905351378307486\n",
       "longitude                                                      -73.89645716668805\n",
       "location                        {'latitude': '40.905351378307486', 'longitude'...\n",
       "day                                                                             8\n",
       "day_of_week                                                                     4\n",
       "hour                                                                           16\n",
       "minute                                                                          4\n",
       "year_month_day                                                         2021-07-08\n",
       "intersection_street_1                                                         NaN\n",
       "intersection_street_2                                                         NaN\n",
       "facility_type                                                                 NaN\n",
       "due_date                                                                      NaN"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# concise summary of a DataFrame with sample value\n",
    "T = elsa_df.head(1).T\n",
    "T.rename(columns={0:'sample_value'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "bizarre-donna",
   "metadata": {},
   "outputs": [],
   "source": [
    "# exporting data as csv\n",
    "elsa_df.to_csv('elsa_311_calls.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "satisfied-football",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CODE_OF_CONDUCT.md    README.md             elsa_311_calls.csv\r\n",
      "LICENSE               download_data.png     query_tutorial.ipynb\r\n"
     ]
    }
   ],
   "source": [
    "# sanity check\n",
    "%ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "invisible-prophet",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of total records: 190.\n",
      "\n",
      "Min: 2021-07-08T08:17:00.000.\n",
      "Max: 2021-07-12T20:31:00.000\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_key</th>\n",
       "      <th>created_date</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>complaint_type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>street_name</th>\n",
       "      <th>...</th>\n",
       "      <th>location</th>\n",
       "      <th>day</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>hour</th>\n",
       "      <th>minute</th>\n",
       "      <th>year_month_day</th>\n",
       "      <th>intersection_street_1</th>\n",
       "      <th>intersection_street_2</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>due_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>51143675</td>\n",
       "      <td>2021-07-08T16:04:00.000</td>\n",
       "      <td>2021-07-09T10:30:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>10471</td>\n",
       "      <td>6485 BROADWAY</td>\n",
       "      <td>BROADWAY</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.905351378307486', 'longitude'...</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>2021-07-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51145094</td>\n",
       "      <td>2021-07-08T16:00:00.000</td>\n",
       "      <td>2021-07-08T20:45:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>10034</td>\n",
       "      <td>200 NAGLE AVENUE</td>\n",
       "      <td>NAGLE AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.86210236351487', 'longitude':...</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-07-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51146482</td>\n",
       "      <td>2021-07-08T21:04:00.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>11236</td>\n",
       "      <td>1035 EAST  102 STREET</td>\n",
       "      <td>EAST  102 STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.6437429889248', 'longitude': ...</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "      <td>2021-07-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51148924</td>\n",
       "      <td>2021-07-08T17:04:00.000</td>\n",
       "      <td>2021-07-09T07:44:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>10471</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.900731405929164', 'longitude'...</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>2021-07-08</td>\n",
       "      <td>FIELDSTON ROAD</td>\n",
       "      <td>WEST 253 STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51139519</td>\n",
       "      <td>2021-07-08T16:44:00.000</td>\n",
       "      <td>2021-07-09T17:15:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>11432</td>\n",
       "      <td>172-39 HIGHLAND AVENUE</td>\n",
       "      <td>HIGHLAND AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>{'latitude': '40.71338015366497', 'longitude':...</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>44</td>\n",
       "      <td>2021-07-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   unique_key             created_date              closed_date agency  \\\n",
       "0    51143675  2021-07-08T16:04:00.000  2021-07-09T10:30:00.000    DEP   \n",
       "1    51145094  2021-07-08T16:00:00.000  2021-07-08T20:45:00.000    DEP   \n",
       "2    51146482  2021-07-08T21:04:00.000                      NaN    DEP   \n",
       "3    51148924  2021-07-08T17:04:00.000  2021-07-09T07:44:00.000    DEP   \n",
       "4    51139519  2021-07-08T16:44:00.000  2021-07-09T17:15:00.000    DEP   \n",
       "\n",
       "                              agency_name complaint_type  \\\n",
       "0  Department of Environmental Protection          Sewer   \n",
       "1  Department of Environmental Protection          Sewer   \n",
       "2  Department of Environmental Protection          Sewer   \n",
       "3  Department of Environmental Protection          Sewer   \n",
       "4  Department of Environmental Protection          Sewer   \n",
       "\n",
       "             descriptor  incident_zip        incident_address  \\\n",
       "0  Street Flooding (SJ)         10471           6485 BROADWAY   \n",
       "1  Street Flooding (SJ)         10034        200 NAGLE AVENUE   \n",
       "2  Street Flooding (SJ)         11236   1035 EAST  102 STREET   \n",
       "3  Street Flooding (SJ)         10471                     NaN   \n",
       "4  Street Flooding (SJ)         11432  172-39 HIGHLAND AVENUE   \n",
       "\n",
       "        street_name  ...                                           location  \\\n",
       "0          BROADWAY  ...  {'latitude': '40.905351378307486', 'longitude'...   \n",
       "1      NAGLE AVENUE  ...  {'latitude': '40.86210236351487', 'longitude':...   \n",
       "2  EAST  102 STREET  ...  {'latitude': '40.6437429889248', 'longitude': ...   \n",
       "3               NaN  ...  {'latitude': '40.900731405929164', 'longitude'...   \n",
       "4   HIGHLAND AVENUE  ...  {'latitude': '40.71338015366497', 'longitude':...   \n",
       "\n",
       "  day day_of_week hour minute year_month_day intersection_street_1  \\\n",
       "0   8           4   16      4     2021-07-08                   NaN   \n",
       "1   8           4   16      0     2021-07-08                   NaN   \n",
       "2   8           4   21      4     2021-07-08                   NaN   \n",
       "3   8           4   17      4     2021-07-08        FIELDSTON ROAD   \n",
       "4   8           4   16     44     2021-07-08                   NaN   \n",
       "\n",
       "  intersection_street_2  facility_type due_date  \n",
       "0                   NaN            NaN      NaN  \n",
       "1                   NaN            NaN      NaN  \n",
       "2                   NaN            NaN      NaN  \n",
       "3       WEST 253 STREET            NaN      NaN  \n",
       "4                   NaN            NaN      NaN  \n",
       "\n",
       "[5 rows x 37 columns]"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sanity check\n",
    "test = pd.read_csv('elsa_311_calls.csv')\n",
    "\n",
    "print('Number of total records: {:,}.\\n'.format(len(test)))      \n",
    "print('Min: {}.'.format(test.created_date.min()))\n",
    "print('Max: {}'.format(test.created_date.max()))\n",
    "\n",
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "international-cooking",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}