searchstring-flood.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Searching for String Flood in Large Datasets on NYC Open Data\n",
    "Author: Mark Bauer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sodapy import Socrata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Documention for installing watermark: https://github.com/rasbt/watermark."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Python implementation: CPython\n",
      "Python version       : 3.8.13\n",
      "IPython version      : 8.4.0\n",
      "\n",
      "pandas: 1.4.3\n",
      "sodapy: 2.1.1\n",
      "\n"
     ]
    }
   ],
   "source": [
    "%reload_ext watermark\n",
    "%watermark -t -d -v -p pandas,sodapy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Socrata API\n",
    "I used the Socrata API to retrieve metadata for datasets hosted on NYC Open Data. Documentation can be found here: https://dev.socrata.com/. Additionally, I used sodapy, the python client for the Socrata API, to query the metadata."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note:  \n",
    "`WARNING:root:Requests made without an app_token will be subject to strict throttling limits.`\n",
    "\n",
    "Read more from the SODA documentation here: https://dev.socrata.com/docs/app-tokens.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:root:Requests made without an app_token will be subject to strict throttling limits.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<sodapy.socrata.Socrata object at 0x10bf00b80>\n"
     ]
    }
   ],
   "source": [
    "# source domain for NYC Open Data on Socrata\n",
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "\n",
    "# initialize Socrata object to fetch data\n",
    "client = Socrata(\n",
    "    domain=socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=1000\n",
    ")\n",
    "\n",
    "print(client)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'311 Service Requests from 2010 to Present': 'erm2-nwe9',\n",
       " 'Emergency Response Incidents': 'pasr-j7fb',\n",
       " 'NYCEM Emergency Notifications': '8vv7-7wx3',\n",
       " 'Incidents Responded to by Fire Companies': 'tm6d-hbzd',\n",
       " 'Street Construction Permits (2013-2021)': 'c9sj-fmsg',\n",
       " 'Street Construction Permits (2022-Present)': 'tqtj-sjs8'}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# build dictionary of dataset and id\n",
    "datasets = {\n",
    "    '311 Service Requests from 2010 to Present':'erm2-nwe9',\n",
    "    'Emergency Response Incidents':'pasr-j7fb',\n",
    "    'NYCEM Emergency Notifications':'8vv7-7wx3',\n",
    "    'Incidents Responded to by Fire Companies':'tm6d-hbzd',\n",
    "    'Street Construction Permits (2013-2021)':'c9sj-fmsg',\n",
    "    'Street Construction Permits (2022-Present)':'tqtj-sjs8',\n",
    "}\n",
    "\n",
    "datasets  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Query Datasets\n",
    "Search for word `flood` in the dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 311 Service Requests from 2010 to Present"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (13, 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>112454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>38187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Flood Light Lamp Out</td>\n",
       "      <td>6486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Highway Flooding (SH)</td>\n",
       "      <td>3133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Flood Light Lamp Cycling</td>\n",
       "      <td>2591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Flooding on Street</td>\n",
       "      <td>673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Ready NY - Flooding</td>\n",
       "      <td>271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Flood Light Lamp Dayburning</td>\n",
       "      <td>225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Flood Light Lamp Missing</td>\n",
       "      <td>211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Flood Light Lamp Dim</td>\n",
       "      <td>185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>RAIN GARDEN FLOODING (SRGFLD)</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Flooded</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Flooding on Highway</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          descriptor   count\n",
       "0   Catch Basin Clogged/Flooding (Use Comments) (SC)  112454\n",
       "1                               Street Flooding (SJ)   38187\n",
       "2                               Flood Light Lamp Out    6486\n",
       "3                              Highway Flooding (SH)    3133\n",
       "4                           Flood Light Lamp Cycling    2591\n",
       "5                                 Flooding on Street     673\n",
       "6                                Ready NY - Flooding     271\n",
       "7                        Flood Light Lamp Dayburning     225\n",
       "8                           Flood Light Lamp Missing     211\n",
       "9                               Flood Light Lamp Dim     185\n",
       "10                     RAIN GARDEN FLOODING (SRGFLD)     152\n",
       "11                                           Flooded      56\n",
       "12                               Flooding on Highway      15"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['311 Service Requests from 2010 to Present']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    descriptor, \n",
    "    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(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (38187, 32)\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>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>cross_street_1</th>\n",
       "      <th>...</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>resolution_description</th>\n",
       "      <th>resolution_action_updated_date</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>59921639</td>\n",
       "      <td>2024-01-04T17:41: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>11414</td>\n",
       "      <td>164-40 99 STREET</td>\n",
       "      <td>99 STREET</td>\n",
       "      <td>164 AVE</td>\n",
       "      <td>...</td>\n",
       "      <td>40.650884987043256</td>\n",
       "      <td>-73.8320413316199</td>\n",
       "      <td>{'latitude': '40.650884987043256', 'longitude'...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>59922948</td>\n",
       "      <td>2024-01-04T13:41: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>11204</td>\n",
       "      <td>5901 19 AVENUE</td>\n",
       "      <td>19 AVENUE</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>...</td>\n",
       "      <td>40.621198202891556</td>\n",
       "      <td>-73.98456459773816</td>\n",
       "      <td>{'latitude': '40.621198202891556', 'longitude'...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>59921638</td>\n",
       "      <td>2024-01-04T12:23: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>11236</td>\n",
       "      <td>EAST  103 STREET</td>\n",
       "      <td>EAST  103 STREET</td>\n",
       "      <td>AVENUE N</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2024-01-04T12:30:00.000</td>\n",
       "      <td>The Department of Environmental Protection has...</td>\n",
       "      <td>2024-01-04T12:30:00.000</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>59924254</td>\n",
       "      <td>2024-01-04T11:47: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>11375</td>\n",
       "      <td>108-36 63 ROAD</td>\n",
       "      <td>63 ROAD</td>\n",
       "      <td>108 ST</td>\n",
       "      <td>...</td>\n",
       "      <td>40.73462429006765</td>\n",
       "      <td>-73.84916828941955</td>\n",
       "      <td>{'latitude': '40.73462429006765', 'longitude':...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>59928357</td>\n",
       "      <td>2024-01-04T10:08: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>11236</td>\n",
       "      <td>1445 EAST  103 STREET</td>\n",
       "      <td>EAST  103 STREET</td>\n",
       "      <td>AVENUE N</td>\n",
       "      <td>...</td>\n",
       "      <td>40.63753744604451</td>\n",
       "      <td>-73.88609231636039</td>\n",
       "      <td>{'latitude': '40.63753744604451', 'longitude':...</td>\n",
       "      <td>2024-01-04T10:20:00.000</td>\n",
       "      <td>The Department of Environmental Protection has...</td>\n",
       "      <td>2024-01-04T10:20:00.000</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 × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_key             created_date agency  \\\n",
       "0   59921639  2024-01-04T17:41:00.000    DEP   \n",
       "1   59922948  2024-01-04T13:41:00.000    DEP   \n",
       "2   59921638  2024-01-04T12:23:00.000    DEP   \n",
       "3   59924254  2024-01-04T11:47:00.000    DEP   \n",
       "4   59928357  2024-01-04T10:08: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)        11414       164-40 99 STREET         99 STREET   \n",
       "1  Street Flooding (SJ)        11204         5901 19 AVENUE         19 AVENUE   \n",
       "2  Street Flooding (SJ)        11236       EAST  103 STREET  EAST  103 STREET   \n",
       "3  Street Flooding (SJ)        11375         108-36 63 ROAD           63 ROAD   \n",
       "4  Street Flooding (SJ)        11236  1445 EAST  103 STREET  EAST  103 STREET   \n",
       "\n",
       "  cross_street_1  ...            latitude           longitude  \\\n",
       "0        164 AVE  ...  40.650884987043256   -73.8320413316199   \n",
       "1          59 ST  ...  40.621198202891556  -73.98456459773816   \n",
       "2       AVENUE N  ...                 NaN                 NaN   \n",
       "3         108 ST  ...   40.73462429006765  -73.84916828941955   \n",
       "4       AVENUE N  ...   40.63753744604451  -73.88609231636039   \n",
       "\n",
       "                                            location              closed_date  \\\n",
       "0  {'latitude': '40.650884987043256', 'longitude'...                      NaN   \n",
       "1  {'latitude': '40.621198202891556', 'longitude'...                      NaN   \n",
       "2                                                NaN  2024-01-04T12:30:00.000   \n",
       "3  {'latitude': '40.73462429006765', 'longitude':...                      NaN   \n",
       "4  {'latitude': '40.63753744604451', 'longitude':...  2024-01-04T10:20:00.000   \n",
       "\n",
       "                              resolution_description  \\\n",
       "0                                                NaN   \n",
       "1                                                NaN   \n",
       "2  The Department of Environmental Protection has...   \n",
       "3                                                NaN   \n",
       "4  The Department of Environmental Protection has...   \n",
       "\n",
       "  resolution_action_updated_date intersection_street_1 intersection_street_2  \\\n",
       "0                            NaN                   NaN                   NaN   \n",
       "1                            NaN                   NaN                   NaN   \n",
       "2        2024-01-04T12:30:00.000                   NaN                   NaN   \n",
       "3                            NaN                   NaN                   NaN   \n",
       "4        2024-01-04T10:20:00.000                   NaN                   NaN   \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 32 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['311 Service Requests from 2010 to Present']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    *\n",
    "WHERE \n",
    "    descriptor = 'Street Flooding (SJ)'   \n",
    "LIMIT\n",
    "    50000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Emergency Response Incidents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (7, 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>incident_type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Weather-Flooding</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Other-Henry Hudson Pkwy Flooding Condition</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Structural-Basement Flooding</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Other-Flooding Condition</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Utility-Flooding in Con Ed Substation</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Weather-Coastal Flooding</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Transportation-Roadway Flooding</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                incident_type count\n",
       "0                            Weather-Flooding    42\n",
       "1  Other-Henry Hudson Pkwy Flooding Condition     1\n",
       "2                Structural-Basement Flooding     1\n",
       "3                    Other-Flooding Condition     1\n",
       "4       Utility-Flooding in Con Ed Substation     1\n",
       "5                    Weather-Coastal Flooding     1\n",
       "6             Transportation-Roadway Flooding     1"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['Emergency Response Incidents']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    incident_type, \n",
    "    count(incident_type) as count\n",
    "WHERE \n",
    "    LOWER(incident_type) LIKE '%flood%'   \n",
    "GROUP BY \n",
    "    incident_type\n",
    "ORDER BY \n",
    "    count(incident_type) DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (42, 7)\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>incident_type</th>\n",
       "      <th>location</th>\n",
       "      <th>borough</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>closed_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Weather-Flooding</td>\n",
       "      <td>61-20 Grand Central Parkway</td>\n",
       "      <td>Queens</td>\n",
       "      <td>2021-09-03T17:34:09.000</td>\n",
       "      <td>40.73831609467984</td>\n",
       "      <td>-73.84677660196152</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Weather-Flooding</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>2018-07-17T16:26:09.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-07-17T17:41:26.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Weather-Flooding</td>\n",
       "      <td>73 Place &amp; Cooper Avenue</td>\n",
       "      <td>Queens</td>\n",
       "      <td>2012-09-08T15:45:01.000</td>\n",
       "      <td>40.70399570604266</td>\n",
       "      <td>-73.87709552817279</td>\n",
       "      <td>2012-09-08T16:45:44.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Weather-Flooding</td>\n",
       "      <td>40 Sioux Street</td>\n",
       "      <td>Staten Island</td>\n",
       "      <td>2012-12-27T11:13:51.000</td>\n",
       "      <td>40.57567270482992</td>\n",
       "      <td>-74.08503664729585</td>\n",
       "      <td>2012-12-27T12:52:01.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Weather-Flooding</td>\n",
       "      <td>Bell Blvd. &amp; Northern Blvd</td>\n",
       "      <td>Queens</td>\n",
       "      <td>2019-12-14T08:13:45.000</td>\n",
       "      <td>40.760279999999995</td>\n",
       "      <td>-73.7693685</td>\n",
       "      <td>2019-12-14T11:07:08.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      incident_type                     location        borough  \\\n",
       "0  Weather-Flooding  61-20 Grand Central Parkway         Queens   \n",
       "1  Weather-Flooding                          NaN       Brooklyn   \n",
       "2  Weather-Flooding     73 Place & Cooper Avenue         Queens   \n",
       "3  Weather-Flooding              40 Sioux Street  Staten Island   \n",
       "4  Weather-Flooding   Bell Blvd. & Northern Blvd         Queens   \n",
       "\n",
       "             creation_date            latitude           longitude  \\\n",
       "0  2021-09-03T17:34:09.000   40.73831609467984  -73.84677660196152   \n",
       "1  2018-07-17T16:26:09.000                 NaN                 NaN   \n",
       "2  2012-09-08T15:45:01.000   40.70399570604266  -73.87709552817279   \n",
       "3  2012-12-27T11:13:51.000   40.57567270482992  -74.08503664729585   \n",
       "4  2019-12-14T08:13:45.000  40.760279999999995         -73.7693685   \n",
       "\n",
       "               closed_date  \n",
       "0                      NaN  \n",
       "1  2018-07-17T17:41:26.000  \n",
       "2  2012-09-08T16:45:44.000  \n",
       "3  2012-12-27T12:52:01.000  \n",
       "4  2019-12-14T11:07:08.000  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['Emergency Response Incidents']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    *\n",
    "WHERE \n",
    "    incident_type = 'Weather-Flooding'   \n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NYCEM Emergency Notifications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (970, 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>notification_title</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Flood Advisory (CW)</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Coastal Flood Advisory - Queens South</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Flood Advisory - The Bronx</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Coastal Flood Statement (S.QN)</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Flash Flood Watch (CW)</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Coastal Flood Advisory (S.QN)</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Coastal Flood Statement - Queens South</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Flood Advisory (QN)</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Flash Flood Warning (CW)</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Coastal Flood Statement - Manhattan</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Coastal Flood Statement - Queens South (QN)</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Flash Flood Warning (BK/QN)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Coastal Flood Statement (SI)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Flood Watch (CW)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Flash Flood Warning (QN)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Areal Flood Advisory (SI)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Flood Advisory - Queens</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Flood Warning (SI)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Flood Advisory - Staten Island</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Flood Advisory - NYC</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             notification_title count\n",
       "0                           Flood Advisory (CW)    11\n",
       "1         Coastal Flood Advisory - Queens South     9\n",
       "2                    Flood Advisory - The Bronx     8\n",
       "3                Coastal Flood Statement (S.QN)     8\n",
       "4                        Flash Flood Watch (CW)     8\n",
       "5                 Coastal Flood Advisory (S.QN)     6\n",
       "6        Coastal Flood Statement - Queens South     6\n",
       "7                           Flood Advisory (QN)     6\n",
       "8                      Flash Flood Warning (CW)     6\n",
       "9           Coastal Flood Statement - Manhattan     5\n",
       "10  Coastal Flood Statement - Queens South (QN)     5\n",
       "11                  Flash Flood Warning (BK/QN)     4\n",
       "12                 Coastal Flood Statement (SI)     4\n",
       "13                             Flood Watch (CW)     4\n",
       "14                     Flash Flood Warning (QN)     4\n",
       "15                    Areal Flood Advisory (SI)     4\n",
       "16                      Flood Advisory - Queens     4\n",
       "17                           Flood Warning (SI)     4\n",
       "18               Flood Advisory - Staten Island     4\n",
       "19                         Flood Advisory - NYC     4"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['NYCEM Emergency Notifications']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    notification_title, \n",
    "    count(notification_title) as count\n",
    "WHERE \n",
    "    LOWER(notification_title) LIKE '%flood%'   \n",
    "GROUP BY \n",
    "    notification_title\n",
    "ORDER BY \n",
    "    count(notification_title) DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (1139, 5)\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>record_id</th>\n",
       "      <th>date_and_time</th>\n",
       "      <th>notificationtype</th>\n",
       "      <th>notification_title</th>\n",
       "      <th>email_body</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007</td>\n",
       "      <td>2012-06-06T21:08:00.000</td>\n",
       "      <td>Weather</td>\n",
       "      <td>Flash Flood Warning Staten Island</td>\n",
       "      <td>Notification issued 6/6/12 at 9:08 PM. The Nat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2162</td>\n",
       "      <td>2012-07-18T16:40:00.000</td>\n",
       "      <td>Weather</td>\n",
       "      <td>Flash Flood Warning (QN,MN,BX,BK)</td>\n",
       "      <td>Notification issued 7/18/12 at 4:40 PM. The Na...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2206</td>\n",
       "      <td>2012-07-28T16:07:00.000</td>\n",
       "      <td>Weather</td>\n",
       "      <td>Flash Flood Warning (QN,MN,BX,BK)</td>\n",
       "      <td>Notification issued 7/28/12 at 4:07 PM. The Na...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2217</td>\n",
       "      <td>2012-08-01T12:02:00.000</td>\n",
       "      <td>Weather</td>\n",
       "      <td>Flash Flood Warning (QN,BK,SI)</td>\n",
       "      <td>Notification issued 8/1/12 at 12:02 PM. The Na...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2218</td>\n",
       "      <td>2012-08-01T12:30:00.000</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>Road Closure - Flooding - LIE at Francis Lewis...</td>\n",
       "      <td>Notification issued 8/1/12 at 12:30 PM. Due to...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2220</td>\n",
       "      <td>2012-08-01T16:15:00.000</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>Road Closure - Flooding -(QN)</td>\n",
       "      <td>Notification issued 8/1/12 at 4:10 PM. Followi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2263</td>\n",
       "      <td>2012-08-15T13:35:00.000</td>\n",
       "      <td>Weather</td>\n",
       "      <td>Flash Flood / Severe Thunderstorm Warnings (QN...</td>\n",
       "      <td>Notification issued 8/15/12 at 1:35 PM. The Na...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2266</td>\n",
       "      <td>2012-08-15T15:55:00.000</td>\n",
       "      <td>Weather</td>\n",
       "      <td>Flash Flood &amp; Severe Thunderstorm Warnings Iss...</td>\n",
       "      <td>Notification issued 8/15/12 at 3:55 PM. The Na...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2267</td>\n",
       "      <td>2012-08-15T16:40:00.000</td>\n",
       "      <td>Weather</td>\n",
       "      <td>Flash Flood Warning - BK, QN (SMS Only)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2351</td>\n",
       "      <td>2012-09-08T12:40:00.000</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>Roadway Flooding w/ Lane Closures (QN)</td>\n",
       "      <td>Notification issued 9/8/12 at 12:40 PM. Due to...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  record_id            date_and_time notificationtype  \\\n",
       "0      2007  2012-06-06T21:08:00.000          Weather   \n",
       "1      2162  2012-07-18T16:40:00.000          Weather   \n",
       "2      2206  2012-07-28T16:07:00.000          Weather   \n",
       "3      2217  2012-08-01T12:02:00.000          Weather   \n",
       "4      2218  2012-08-01T12:30:00.000   Transportation   \n",
       "5      2220  2012-08-01T16:15:00.000   Transportation   \n",
       "6      2263  2012-08-15T13:35:00.000          Weather   \n",
       "7      2266  2012-08-15T15:55:00.000          Weather   \n",
       "8      2267  2012-08-15T16:40:00.000          Weather   \n",
       "9      2351  2012-09-08T12:40:00.000   Transportation   \n",
       "\n",
       "                                  notification_title  \\\n",
       "0                  Flash Flood Warning Staten Island   \n",
       "1                  Flash Flood Warning (QN,MN,BX,BK)   \n",
       "2                  Flash Flood Warning (QN,MN,BX,BK)   \n",
       "3                     Flash Flood Warning (QN,BK,SI)   \n",
       "4  Road Closure - Flooding - LIE at Francis Lewis...   \n",
       "5                      Road Closure - Flooding -(QN)   \n",
       "6  Flash Flood / Severe Thunderstorm Warnings (QN...   \n",
       "7  Flash Flood & Severe Thunderstorm Warnings Iss...   \n",
       "8            Flash Flood Warning - BK, QN (SMS Only)   \n",
       "9             Roadway Flooding w/ Lane Closures (QN)   \n",
       "\n",
       "                                          email_body  \n",
       "0  Notification issued 6/6/12 at 9:08 PM. The Nat...  \n",
       "1  Notification issued 7/18/12 at 4:40 PM. The Na...  \n",
       "2  Notification issued 7/28/12 at 4:07 PM. The Na...  \n",
       "3  Notification issued 8/1/12 at 12:02 PM. The Na...  \n",
       "4  Notification issued 8/1/12 at 12:30 PM. Due to...  \n",
       "5  Notification issued 8/1/12 at 4:10 PM. Followi...  \n",
       "6  Notification issued 8/15/12 at 1:35 PM. The Na...  \n",
       "7  Notification issued 8/15/12 at 3:55 PM. The Na...  \n",
       "8                                                NaN  \n",
       "9  Notification issued 9/8/12 at 12:40 PM. Due to...  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['NYCEM Emergency Notifications']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    *\n",
    "WHERE \n",
    "    LOWER(notification_title) LIKE '%flood%' \n",
    "LIMIT\n",
    "    2000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Incidents Responded to by Fire Companies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (1, 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>incident_type_desc</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>812 - Flood assessment</td>\n",
       "      <td>263</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       incident_type_desc count\n",
       "0  812 - Flood assessment   263"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['Incidents Responded to by Fire Companies']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    incident_type_desc, \n",
    "    count(incident_type_desc) as count\n",
    "WHERE \n",
    "    LOWER(incident_type_desc) LIKE '%flood%'   \n",
    "GROUP BY \n",
    "    incident_type_desc\n",
    "ORDER BY \n",
    "    count(incident_type_desc) DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (263, 18)\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>im_incident_key</th>\n",
       "      <th>fire_box</th>\n",
       "      <th>incident_type_desc</th>\n",
       "      <th>incident_date_time</th>\n",
       "      <th>arrival_date_time</th>\n",
       "      <th>units_onscene</th>\n",
       "      <th>last_unit_cleared_date_time</th>\n",
       "      <th>highest_level_desc</th>\n",
       "      <th>total_incident_duration</th>\n",
       "      <th>action_taken1_desc</th>\n",
       "      <th>property_use_desc</th>\n",
       "      <th>street_highway</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>borough_desc</th>\n",
       "      <th>action_taken2_desc</th>\n",
       "      <th>action_taken3_desc</th>\n",
       "      <th>floor</th>\n",
       "      <th>co_detector_present_desc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>55688773</td>\n",
       "      <td>2375</td>\n",
       "      <td>812 - Flood assessment</td>\n",
       "      <td>2013-01-07T07:14:45.000</td>\n",
       "      <td>2013-01-07T07:22:35.000</td>\n",
       "      <td>1</td>\n",
       "      <td>2013-01-07T07:33:12.000</td>\n",
       "      <td>1 - More than initial alarm, less than Signal 7-5</td>\n",
       "      <td>1107</td>\n",
       "      <td>21 - Search</td>\n",
       "      <td>429 - Multifamily dwelling</td>\n",
       "      <td>E 162 ST</td>\n",
       "      <td>10459</td>\n",
       "      <td>2 - Bronx</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>55701874</td>\n",
       "      <td>2891</td>\n",
       "      <td>812 - Flood assessment</td>\n",
       "      <td>2013-01-11T18:53:20.000</td>\n",
       "      <td>2013-01-11T19:02:21.000</td>\n",
       "      <td>1</td>\n",
       "      <td>2013-01-11T19:08:37.000</td>\n",
       "      <td>1 - More than initial alarm, less than Signal 7-5</td>\n",
       "      <td>917</td>\n",
       "      <td>21 - Search</td>\n",
       "      <td>500 - Mercantile, business, other</td>\n",
       "      <td>WESTCHESTER AVE</td>\n",
       "      <td>10472</td>\n",
       "      <td>2 - Bronx</td>\n",
       "      <td>55 - Establish safe area</td>\n",
       "      <td>86 - Investigate</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>55738238</td>\n",
       "      <td>1629</td>\n",
       "      <td>812 - Flood assessment</td>\n",
       "      <td>2013-01-25T16:39:31.000</td>\n",
       "      <td>2013-01-25T16:51:39.000</td>\n",
       "      <td>1</td>\n",
       "      <td>2013-01-25T16:57:05.000</td>\n",
       "      <td>1 - More than initial alarm, less than Signal 7-5</td>\n",
       "      <td>1054</td>\n",
       "      <td>82 - Notify other agencies.</td>\n",
       "      <td>962 - Residential street, road or residential ...</td>\n",
       "      <td>CIRCLE RD</td>\n",
       "      <td>10304</td>\n",
       "      <td>3 - Staten Island</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>55768666</td>\n",
       "      <td>1034</td>\n",
       "      <td>812 - Flood assessment</td>\n",
       "      <td>2013-02-04T17:59:44.000</td>\n",
       "      <td>2013-02-04T18:06:59.000</td>\n",
       "      <td>1</td>\n",
       "      <td>2013-02-04T18:14:42.000</td>\n",
       "      <td>1 - More than initial alarm, less than Signal 7-5</td>\n",
       "      <td>898</td>\n",
       "      <td>86 - Investigate</td>\n",
       "      <td>429 - Multifamily dwelling</td>\n",
       "      <td>PARK AVE</td>\n",
       "      <td>10021</td>\n",
       "      <td>1 - Manhattan</td>\n",
       "      <td>81 - Incident command</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>55786874</td>\n",
       "      <td>9114</td>\n",
       "      <td>812 - Flood assessment</td>\n",
       "      <td>2013-02-11T09:19:39.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-02-11T09:22:34.000</td>\n",
       "      <td>1 - More than initial alarm, less than Signal 7-5</td>\n",
       "      <td>175</td>\n",
       "      <td>86 - Investigate</td>\n",
       "      <td>419 - 1 or 2 family dwelling</td>\n",
       "      <td>73 TER</td>\n",
       "      <td>11367</td>\n",
       "      <td>5 - Queens</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  im_incident_key fire_box      incident_type_desc       incident_date_time  \\\n",
       "0        55688773     2375  812 - Flood assessment  2013-01-07T07:14:45.000   \n",
       "1        55701874     2891  812 - Flood assessment  2013-01-11T18:53:20.000   \n",
       "2        55738238     1629  812 - Flood assessment  2013-01-25T16:39:31.000   \n",
       "3        55768666     1034  812 - Flood assessment  2013-02-04T17:59:44.000   \n",
       "4        55786874     9114  812 - Flood assessment  2013-02-11T09:19:39.000   \n",
       "\n",
       "         arrival_date_time units_onscene last_unit_cleared_date_time  \\\n",
       "0  2013-01-07T07:22:35.000             1     2013-01-07T07:33:12.000   \n",
       "1  2013-01-11T19:02:21.000             1     2013-01-11T19:08:37.000   \n",
       "2  2013-01-25T16:51:39.000             1     2013-01-25T16:57:05.000   \n",
       "3  2013-02-04T18:06:59.000             1     2013-02-04T18:14:42.000   \n",
       "4                      NaN           NaN     2013-02-11T09:22:34.000   \n",
       "\n",
       "                                  highest_level_desc total_incident_duration  \\\n",
       "0  1 - More than initial alarm, less than Signal 7-5                    1107   \n",
       "1  1 - More than initial alarm, less than Signal 7-5                     917   \n",
       "2  1 - More than initial alarm, less than Signal 7-5                    1054   \n",
       "3  1 - More than initial alarm, less than Signal 7-5                     898   \n",
       "4  1 - More than initial alarm, less than Signal 7-5                     175   \n",
       "\n",
       "            action_taken1_desc  \\\n",
       "0                  21 - Search   \n",
       "1                  21 - Search   \n",
       "2  82 - Notify other agencies.   \n",
       "3             86 - Investigate   \n",
       "4             86 - Investigate   \n",
       "\n",
       "                                   property_use_desc   street_highway  \\\n",
       "0                         429 - Multifamily dwelling         E 162 ST   \n",
       "1                  500 - Mercantile, business, other  WESTCHESTER AVE   \n",
       "2  962 - Residential street, road or residential ...        CIRCLE RD   \n",
       "3                         429 - Multifamily dwelling         PARK AVE   \n",
       "4                       419 - 1 or 2 family dwelling           73 TER   \n",
       "\n",
       "  zip_code       borough_desc        action_taken2_desc action_taken3_desc  \\\n",
       "0    10459          2 - Bronx                       NaN                NaN   \n",
       "1    10472          2 - Bronx  55 - Establish safe area   86 - Investigate   \n",
       "2    10304  3 - Staten Island                       NaN                NaN   \n",
       "3    10021      1 - Manhattan     81 - Incident command                NaN   \n",
       "4    11367         5 - Queens                       NaN                NaN   \n",
       "\n",
       "  floor co_detector_present_desc  \n",
       "0   NaN                      NaN  \n",
       "1     1                      NaN  \n",
       "2   NaN                      NaN  \n",
       "3   NaN                      NaN  \n",
       "4   NaN                      NaN  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['Incidents Responded to by Fire Companies']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    *\n",
    "WHERE \n",
    "    incident_type_desc = '812 - Flood assessment'   \n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Street Construction Permits (2013-2021)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (365, 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>permitpurposecomments</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Installation of Sanitary and Storm Sewers, Flo...</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ext. masonry, Prpts, Roof, Flood Elim, TUC rem...</td>\n",
       "      <td>318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FURNISH AND INSTALL FLOOD PROTECTION DEVICES A...</td>\n",
       "      <td>279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ext Masory, Flood Elim, Roofs,Windows</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Prpts, Etx. Masonry, Flood Elim, Security Light</td>\n",
       "      <td>156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>PS148k - Flood Elim, Ext Masonry, Parapets</td>\n",
       "      <td>154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MTA Contract: TUN-MIT-01: Design-Build for Flo...</td>\n",
       "      <td>143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>148th Street Yard Flood Mitigation</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Furnishing and installing Flood Barriers</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Flood Elim, Parapets, Roofs</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Contract C-34839 Flood Mitigation at the 148th...</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Ext. Masonry, Flood Elim, Prpts, Roofs</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Water Infiltration, Flood Elim</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Flood Mitigation / Resiliency at Fan Plant</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Contract A-37612 Furnishing &amp;amp; Installing F...</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>installing flood barriers</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Installation of flood mitigation measures arou...</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>roof, Flood Elim, windows, FA</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Intalling flood barriers</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                permitpurposecomments count\n",
       "0   Installation of Sanitary and Storm Sewers, Flo...   562\n",
       "1   Ext. masonry, Prpts, Roof, Flood Elim, TUC rem...   318\n",
       "2   FURNISH AND INSTALL FLOOD PROTECTION DEVICES A...   279\n",
       "3               Ext Masory, Flood Elim, Roofs,Windows   240\n",
       "4     Prpts, Etx. Masonry, Flood Elim, Security Light   156\n",
       "5          PS148k - Flood Elim, Ext Masonry, Parapets   154\n",
       "6   MTA Contract: TUN-MIT-01: Design-Build for Flo...   143\n",
       "7             DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   134\n",
       "8                  148th Street Yard Flood Mitigation   132\n",
       "9            Furnishing and installing Flood Barriers   129\n",
       "10                        Flood Elim, Parapets, Roofs   110\n",
       "11  Contract C-34839 Flood Mitigation at the 148th...   108\n",
       "12             Ext. Masonry, Flood Elim, Prpts, Roofs    96\n",
       "13                     Water Infiltration, Flood Elim    96\n",
       "14         Flood Mitigation / Resiliency at Fan Plant    96\n",
       "15  Contract A-37612 Furnishing &amp; Installing F...    90\n",
       "16                          installing flood barriers    88\n",
       "17  Installation of flood mitigation measures arou...    84\n",
       "18                      roof, Flood Elim, windows, FA    84\n",
       "19                           Intalling flood barriers    73"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['Street Construction Permits (2013-2021)']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    permitpurposecomments, \n",
    "    count(permitpurposecomments) as count\n",
    "WHERE \n",
    "    LOWER(permitpurposecomments) LIKE '%flood%'   \n",
    "GROUP BY \n",
    "    permitpurposecomments\n",
    "ORDER BY \n",
    "    count(permitpurposecomments) DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (38, 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>permitpurposecomments</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Furnishing and installing Flood Barriers</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>installing flood barriers</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Intalling flood barriers</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Furnishing and Installing Flood Barriers</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MTA contract- install flood barrier, marine do...</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Flood Barrier Work at Astoria Houses Buildings...</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Furnishing and installing flood barriers</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Installation of flood barriers around Con Ed v...</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Installing Flood Barriers</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Installation of structural support for flood b...</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Installing Flood barriers</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NYC School Construction Authority Hurricane Sa...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Flood barrier Test</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>INSTALL HESCO FLOOD BARRIERS ON SIDEWALK</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>installation of Flood Barrier doors (MCDS) fro...</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>flood barrier test</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>INSTALL FLOOD BARRIERS</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>FLOOD BARRIER TESTING</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Partial roadway closing on water street for er...</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                permitpurposecomments count\n",
       "0             DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   134\n",
       "1            Furnishing and installing Flood Barriers   129\n",
       "2                           installing flood barriers    88\n",
       "3                            Intalling flood barriers    73\n",
       "4            Furnishing and Installing Flood Barriers    70\n",
       "5   MTA contract- install flood barrier, marine do...    68\n",
       "6   Flood Barrier Work at Astoria Houses Buildings...    40\n",
       "7            Furnishing and installing flood barriers    16\n",
       "8   Installation of flood barriers around Con Ed v...    14\n",
       "9                           Installing Flood Barriers    12\n",
       "10  Installation of structural support for flood b...    11\n",
       "11                          Installing Flood barriers     9\n",
       "12  NYC School Construction Authority Hurricane Sa...     8\n",
       "13                                 Flood barrier Test     6\n",
       "14           INSTALL HESCO FLOOD BARRIERS ON SIDEWALK     6\n",
       "15  installation of Flood Barrier doors (MCDS) fro...     5\n",
       "16                                 flood barrier test     5\n",
       "17                             INSTALL FLOOD BARRIERS     5\n",
       "18                              FLOOD BARRIER TESTING     4\n",
       "19  Partial roadway closing on water street for er...     4"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['Street Construction Permits (2013-2021)']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    permitpurposecomments, \n",
    "    count(permitpurposecomments) as count\n",
    "WHERE \n",
    "    LOWER(permitpurposecomments) LIKE '%flood barrier%'   \n",
    "GROUP BY \n",
    "    permitpurposecomments\n",
    "ORDER BY \n",
    "    count(permitpurposecomments) DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'DEPLOY / INSTALL FOLDABLE FLOOD BARRIER'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results_df.iloc[0].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (134, 27)\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>permitnumber</th>\n",
       "      <th>applicationtrackingid</th>\n",
       "      <th>sequencenumber</th>\n",
       "      <th>applicationtypeshortdesc</th>\n",
       "      <th>permitstatusid</th>\n",
       "      <th>permitstatusshortdesc</th>\n",
       "      <th>permitseriesid</th>\n",
       "      <th>permitseriesshortdesc</th>\n",
       "      <th>permittypeid</th>\n",
       "      <th>permittypedesc</th>\n",
       "      <th>...</th>\n",
       "      <th>fromstreetname</th>\n",
       "      <th>permitteename</th>\n",
       "      <th>permitpurposecomments</th>\n",
       "      <th>sidewalkshortdesc</th>\n",
       "      <th>createdon</th>\n",
       "      <th>modifiedon</th>\n",
       "      <th>oftcode</th>\n",
       "      <th>equipmenttypedesc</th>\n",
       "      <th>tostreetname</th>\n",
       "      <th>previouspermitnumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>M022021215B09</td>\n",
       "      <td>2021080200764016</td>\n",
       "      <td>0001</td>\n",
       "      <td>New</td>\n",
       "      <td>030</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>02</td>\n",
       "      <td>BUILDING OPERATION PERMIT</td>\n",
       "      <td>0201</td>\n",
       "      <td>PLACE MATERIAL ON STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>VESTRY STREET</td>\n",
       "      <td>TULLY CONSTRUCTION CO. INC.</td>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>CONCRETE</td>\n",
       "      <td>2021-08-03T09:57:58.000</td>\n",
       "      <td>2021-10-01T00:05:10.000</td>\n",
       "      <td>123190132710000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M022021215B10</td>\n",
       "      <td>2021080200764016</td>\n",
       "      <td>0002</td>\n",
       "      <td>New</td>\n",
       "      <td>030</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>02</td>\n",
       "      <td>BUILDING OPERATION PERMIT</td>\n",
       "      <td>0211</td>\n",
       "      <td>OCCUPANCY OF ROADWAY AS STIPULATED</td>\n",
       "      <td>...</td>\n",
       "      <td>VESTRY STREET</td>\n",
       "      <td>TULLY CONSTRUCTION CO. INC.</td>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>CONCRETE</td>\n",
       "      <td>2021-08-03T09:57:58.000</td>\n",
       "      <td>2021-10-01T00:05:10.000</td>\n",
       "      <td>123190132710000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M022021215B11</td>\n",
       "      <td>2021080200764016</td>\n",
       "      <td>0003</td>\n",
       "      <td>New</td>\n",
       "      <td>030</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>02</td>\n",
       "      <td>BUILDING OPERATION PERMIT</td>\n",
       "      <td>0215</td>\n",
       "      <td>OCCUPANCY OF SIDEWALK AS STIPULATED</td>\n",
       "      <td>...</td>\n",
       "      <td>VESTRY STREET</td>\n",
       "      <td>TULLY CONSTRUCTION CO. INC.</td>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>CONCRETE</td>\n",
       "      <td>2021-08-03T09:57:58.000</td>\n",
       "      <td>2021-10-01T00:05:10.000</td>\n",
       "      <td>123190132710000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M022021215B12</td>\n",
       "      <td>2021080200764016</td>\n",
       "      <td>0004</td>\n",
       "      <td>New</td>\n",
       "      <td>030</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>02</td>\n",
       "      <td>BUILDING OPERATION PERMIT</td>\n",
       "      <td>0204</td>\n",
       "      <td>PLACE EQUIPMENT OTHER THAN CRANE OR SHOV</td>\n",
       "      <td>...</td>\n",
       "      <td>VESTRY STREET</td>\n",
       "      <td>TULLY CONSTRUCTION CO. INC.</td>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>CONCRETE</td>\n",
       "      <td>2021-08-03T09:57:58.000</td>\n",
       "      <td>2021-10-01T00:05:10.000</td>\n",
       "      <td>123190132710000000</td>\n",
       "      <td>Backhoe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M022021215B13</td>\n",
       "      <td>2021080200764016</td>\n",
       "      <td>0005</td>\n",
       "      <td>New</td>\n",
       "      <td>030</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>02</td>\n",
       "      <td>BUILDING OPERATION PERMIT</td>\n",
       "      <td>0204</td>\n",
       "      <td>PLACE EQUIPMENT OTHER THAN CRANE OR SHOV</td>\n",
       "      <td>...</td>\n",
       "      <td>VESTRY STREET</td>\n",
       "      <td>TULLY CONSTRUCTION CO. INC.</td>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>CONCRETE</td>\n",
       "      <td>2021-08-03T09:57:58.000</td>\n",
       "      <td>2021-10-01T00:05:10.000</td>\n",
       "      <td>123190132710000000</td>\n",
       "      <td>Boom Truck</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    permitnumber applicationtrackingid sequencenumber  \\\n",
       "0  M022021215B09      2021080200764016           0001   \n",
       "1  M022021215B10      2021080200764016           0002   \n",
       "2  M022021215B11      2021080200764016           0003   \n",
       "3  M022021215B12      2021080200764016           0004   \n",
       "4  M022021215B13      2021080200764016           0005   \n",
       "\n",
       "  applicationtypeshortdesc permitstatusid permitstatusshortdesc  \\\n",
       "0                      New            030               EXPIRED   \n",
       "1                      New            030               EXPIRED   \n",
       "2                      New            030               EXPIRED   \n",
       "3                      New            030               EXPIRED   \n",
       "4                      New            030               EXPIRED   \n",
       "\n",
       "  permitseriesid      permitseriesshortdesc permittypeid  \\\n",
       "0             02  BUILDING OPERATION PERMIT         0201   \n",
       "1             02  BUILDING OPERATION PERMIT         0211   \n",
       "2             02  BUILDING OPERATION PERMIT         0215   \n",
       "3             02  BUILDING OPERATION PERMIT         0204   \n",
       "4             02  BUILDING OPERATION PERMIT         0204   \n",
       "\n",
       "                             permittypedesc  ... fromstreetname  \\\n",
       "0                  PLACE MATERIAL ON STREET  ...  VESTRY STREET   \n",
       "1        OCCUPANCY OF ROADWAY AS STIPULATED  ...  VESTRY STREET   \n",
       "2       OCCUPANCY OF SIDEWALK AS STIPULATED  ...  VESTRY STREET   \n",
       "3  PLACE EQUIPMENT OTHER THAN CRANE OR SHOV  ...  VESTRY STREET   \n",
       "4  PLACE EQUIPMENT OTHER THAN CRANE OR SHOV  ...  VESTRY STREET   \n",
       "\n",
       "                 permitteename                    permitpurposecomments  \\\n",
       "0  TULLY CONSTRUCTION CO. INC.  DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   \n",
       "1  TULLY CONSTRUCTION CO. INC.  DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   \n",
       "2  TULLY CONSTRUCTION CO. INC.  DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   \n",
       "3  TULLY CONSTRUCTION CO. INC.  DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   \n",
       "4  TULLY CONSTRUCTION CO. INC.  DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   \n",
       "\n",
       "  sidewalkshortdesc                createdon               modifiedon  \\\n",
       "0          CONCRETE  2021-08-03T09:57:58.000  2021-10-01T00:05:10.000   \n",
       "1          CONCRETE  2021-08-03T09:57:58.000  2021-10-01T00:05:10.000   \n",
       "2          CONCRETE  2021-08-03T09:57:58.000  2021-10-01T00:05:10.000   \n",
       "3          CONCRETE  2021-08-03T09:57:58.000  2021-10-01T00:05:10.000   \n",
       "4          CONCRETE  2021-08-03T09:57:58.000  2021-10-01T00:05:10.000   \n",
       "\n",
       "              oftcode equipmenttypedesc tostreetname previouspermitnumber  \n",
       "0  123190132710000000               NaN          NaN                  NaN  \n",
       "1  123190132710000000               NaN          NaN                  NaN  \n",
       "2  123190132710000000               NaN          NaN                  NaN  \n",
       "3  123190132710000000           Backhoe          NaN                  NaN  \n",
       "4  123190132710000000        Boom Truck          NaN                  NaN  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['Street Construction Permits (2013-2021)']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    *\n",
    "WHERE \n",
    "    permitpurposecomments = 'DEPLOY / INSTALL FOLDABLE FLOOD BARRIER'   \n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Street Construction Permits (2022-Present)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (105, 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>permitpurposecomments</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Installation of Sanitary and Storm Sewers, Flo...</td>\n",
       "      <td>996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MTA Contract C-33941 - Flood Mitigation and Ro...</td>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Windows, Ext Masonry, Roofs, Prpts. Flood elim.</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Flood Elim, Ext. Masonry, Parapets.</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Exterior Masonry, Roof, Parapet, Flood Elimina...</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Windows, Ext masonry, Roofs, Pprts, Flood elim.</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>PLA Atl- Windows, Flood Elim, Ext Masonry</td>\n",
       "      <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Ext Masory, Flood Elim, Roofs,Windows</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>FURNISH AND INSTALL FLOOD PROTECTION DEVICES A...</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Windows , Roofs, Flood elim.</td>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Pla-Alt- windows- Flood elim- Ext Masonry</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Facade Remediation, Flood Elim, Parapets, Wind...</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>installation of fema flood walls</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Parapets, Roofs, Flood Elim.</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>DDC Flood Wall Construction</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Pla-Alt, Flood Elim, Windows, Ext Masonry</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Staging a concrete pump to place material in c...</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Flood Elim, Parapets, Roofs</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>P36704, Replacement of High Tension Switchgear...</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                permitpurposecomments count\n",
       "0   Installation of Sanitary and Storm Sewers, Flo...   996\n",
       "1             DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   570\n",
       "2   MTA Contract C-33941 - Flood Mitigation and Ro...   220\n",
       "3     Windows, Ext Masonry, Roofs, Prpts. Flood elim.   192\n",
       "4                 Flood Elim, Ext. Masonry, Parapets.   145\n",
       "5   Exterior Masonry, Roof, Parapet, Flood Elimina...   141\n",
       "6     Windows, Ext masonry, Roofs, Pprts, Flood elim.   128\n",
       "7           PLA Atl- Windows, Flood Elim, Ext Masonry   121\n",
       "8               Ext Masory, Flood Elim, Roofs,Windows   120\n",
       "9   FURNISH AND INSTALL FLOOD PROTECTION DEVICES A...   119\n",
       "10                       Windows , Roofs, Flood elim.   115\n",
       "11          Pla-Alt- windows- Flood elim- Ext Masonry    99\n",
       "12  Facade Remediation, Flood Elim, Parapets, Wind...    86\n",
       "13                   installation of fema flood walls    76\n",
       "14                       Parapets, Roofs, Flood Elim.    72\n",
       "15                        DDC Flood Wall Construction    69\n",
       "16          Pla-Alt, Flood Elim, Windows, Ext Masonry    66\n",
       "17  Staging a concrete pump to place material in c...    66\n",
       "18                        Flood Elim, Parapets, Roofs    65\n",
       "19  P36704, Replacement of High Tension Switchgear...    37"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['Street Construction Permits (2022-Present)']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    permitpurposecomments, \n",
    "    count(permitpurposecomments) as count\n",
    "WHERE \n",
    "    LOWER(permitpurposecomments) LIKE '%flood%'   \n",
    "GROUP BY \n",
    "    permitpurposecomments\n",
    "ORDER BY \n",
    "    count(permitpurposecomments) DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (7, 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>permitpurposecomments</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MTA contract- install flood barrier, marine do...</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Flood Barrier Work at Astoria Houses Buildings...</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Test deployment of flood barriers on West Street</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Construct flood barriers on north side of Libe...</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Test deployment of flood barriers</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Installation of flood barriers around Con Ed v...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               permitpurposecomments count\n",
       "0            DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   570\n",
       "1  MTA contract- install flood barrier, marine do...    24\n",
       "2  Flood Barrier Work at Astoria Houses Buildings...    20\n",
       "3   Test deployment of flood barriers on West Street     6\n",
       "4  Construct flood barriers on north side of Libe...     3\n",
       "5                  Test deployment of flood barriers     3\n",
       "6  Installation of flood barriers around Con Ed v...     1"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['Street Construction Permits (2022-Present)']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    permitpurposecomments, \n",
    "    count(permitpurposecomments) as count\n",
    "WHERE \n",
    "    LOWER(permitpurposecomments) LIKE '%flood barrier%'   \n",
    "GROUP BY \n",
    "    permitpurposecomments\n",
    "ORDER BY \n",
    "    count(permitpurposecomments) DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'DEPLOY / INSTALL FOLDABLE FLOOD BARRIER'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results_df.iloc[0].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (134, 27)\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>permitnumber</th>\n",
       "      <th>applicationtrackingid</th>\n",
       "      <th>sequencenumber</th>\n",
       "      <th>applicationtypeshortdesc</th>\n",
       "      <th>permitstatusid</th>\n",
       "      <th>permitstatusshortdesc</th>\n",
       "      <th>permitseriesid</th>\n",
       "      <th>permitseriesshortdesc</th>\n",
       "      <th>permittypeid</th>\n",
       "      <th>permittypedesc</th>\n",
       "      <th>...</th>\n",
       "      <th>fromstreetname</th>\n",
       "      <th>permitteename</th>\n",
       "      <th>permitpurposecomments</th>\n",
       "      <th>sidewalkshortdesc</th>\n",
       "      <th>createdon</th>\n",
       "      <th>modifiedon</th>\n",
       "      <th>oftcode</th>\n",
       "      <th>equipmenttypedesc</th>\n",
       "      <th>tostreetname</th>\n",
       "      <th>previouspermitnumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>M022021215B09</td>\n",
       "      <td>2021080200764016</td>\n",
       "      <td>0001</td>\n",
       "      <td>New</td>\n",
       "      <td>030</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>02</td>\n",
       "      <td>BUILDING OPERATION PERMIT</td>\n",
       "      <td>0201</td>\n",
       "      <td>PLACE MATERIAL ON STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>VESTRY STREET</td>\n",
       "      <td>TULLY CONSTRUCTION CO. INC.</td>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>CONCRETE</td>\n",
       "      <td>2021-08-03T09:57:58.000</td>\n",
       "      <td>2021-10-01T00:05:10.000</td>\n",
       "      <td>123190132710000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M022021215B10</td>\n",
       "      <td>2021080200764016</td>\n",
       "      <td>0002</td>\n",
       "      <td>New</td>\n",
       "      <td>030</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>02</td>\n",
       "      <td>BUILDING OPERATION PERMIT</td>\n",
       "      <td>0211</td>\n",
       "      <td>OCCUPANCY OF ROADWAY AS STIPULATED</td>\n",
       "      <td>...</td>\n",
       "      <td>VESTRY STREET</td>\n",
       "      <td>TULLY CONSTRUCTION CO. INC.</td>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>CONCRETE</td>\n",
       "      <td>2021-08-03T09:57:58.000</td>\n",
       "      <td>2021-10-01T00:05:10.000</td>\n",
       "      <td>123190132710000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M022021215B11</td>\n",
       "      <td>2021080200764016</td>\n",
       "      <td>0003</td>\n",
       "      <td>New</td>\n",
       "      <td>030</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>02</td>\n",
       "      <td>BUILDING OPERATION PERMIT</td>\n",
       "      <td>0215</td>\n",
       "      <td>OCCUPANCY OF SIDEWALK AS STIPULATED</td>\n",
       "      <td>...</td>\n",
       "      <td>VESTRY STREET</td>\n",
       "      <td>TULLY CONSTRUCTION CO. INC.</td>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>CONCRETE</td>\n",
       "      <td>2021-08-03T09:57:58.000</td>\n",
       "      <td>2021-10-01T00:05:10.000</td>\n",
       "      <td>123190132710000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M022021215B12</td>\n",
       "      <td>2021080200764016</td>\n",
       "      <td>0004</td>\n",
       "      <td>New</td>\n",
       "      <td>030</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>02</td>\n",
       "      <td>BUILDING OPERATION PERMIT</td>\n",
       "      <td>0204</td>\n",
       "      <td>PLACE EQUIPMENT OTHER THAN CRANE OR SHOV</td>\n",
       "      <td>...</td>\n",
       "      <td>VESTRY STREET</td>\n",
       "      <td>TULLY CONSTRUCTION CO. INC.</td>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>CONCRETE</td>\n",
       "      <td>2021-08-03T09:57:58.000</td>\n",
       "      <td>2021-10-01T00:05:10.000</td>\n",
       "      <td>123190132710000000</td>\n",
       "      <td>Backhoe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M022021215B13</td>\n",
       "      <td>2021080200764016</td>\n",
       "      <td>0005</td>\n",
       "      <td>New</td>\n",
       "      <td>030</td>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>02</td>\n",
       "      <td>BUILDING OPERATION PERMIT</td>\n",
       "      <td>0204</td>\n",
       "      <td>PLACE EQUIPMENT OTHER THAN CRANE OR SHOV</td>\n",
       "      <td>...</td>\n",
       "      <td>VESTRY STREET</td>\n",
       "      <td>TULLY CONSTRUCTION CO. INC.</td>\n",
       "      <td>DEPLOY / INSTALL FOLDABLE FLOOD BARRIER</td>\n",
       "      <td>CONCRETE</td>\n",
       "      <td>2021-08-03T09:57:58.000</td>\n",
       "      <td>2021-10-01T00:05:10.000</td>\n",
       "      <td>123190132710000000</td>\n",
       "      <td>Boom Truck</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    permitnumber applicationtrackingid sequencenumber  \\\n",
       "0  M022021215B09      2021080200764016           0001   \n",
       "1  M022021215B10      2021080200764016           0002   \n",
       "2  M022021215B11      2021080200764016           0003   \n",
       "3  M022021215B12      2021080200764016           0004   \n",
       "4  M022021215B13      2021080200764016           0005   \n",
       "\n",
       "  applicationtypeshortdesc permitstatusid permitstatusshortdesc  \\\n",
       "0                      New            030               EXPIRED   \n",
       "1                      New            030               EXPIRED   \n",
       "2                      New            030               EXPIRED   \n",
       "3                      New            030               EXPIRED   \n",
       "4                      New            030               EXPIRED   \n",
       "\n",
       "  permitseriesid      permitseriesshortdesc permittypeid  \\\n",
       "0             02  BUILDING OPERATION PERMIT         0201   \n",
       "1             02  BUILDING OPERATION PERMIT         0211   \n",
       "2             02  BUILDING OPERATION PERMIT         0215   \n",
       "3             02  BUILDING OPERATION PERMIT         0204   \n",
       "4             02  BUILDING OPERATION PERMIT         0204   \n",
       "\n",
       "                             permittypedesc  ... fromstreetname  \\\n",
       "0                  PLACE MATERIAL ON STREET  ...  VESTRY STREET   \n",
       "1        OCCUPANCY OF ROADWAY AS STIPULATED  ...  VESTRY STREET   \n",
       "2       OCCUPANCY OF SIDEWALK AS STIPULATED  ...  VESTRY STREET   \n",
       "3  PLACE EQUIPMENT OTHER THAN CRANE OR SHOV  ...  VESTRY STREET   \n",
       "4  PLACE EQUIPMENT OTHER THAN CRANE OR SHOV  ...  VESTRY STREET   \n",
       "\n",
       "                 permitteename                    permitpurposecomments  \\\n",
       "0  TULLY CONSTRUCTION CO. INC.  DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   \n",
       "1  TULLY CONSTRUCTION CO. INC.  DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   \n",
       "2  TULLY CONSTRUCTION CO. INC.  DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   \n",
       "3  TULLY CONSTRUCTION CO. INC.  DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   \n",
       "4  TULLY CONSTRUCTION CO. INC.  DEPLOY / INSTALL FOLDABLE FLOOD BARRIER   \n",
       "\n",
       "  sidewalkshortdesc                createdon               modifiedon  \\\n",
       "0          CONCRETE  2021-08-03T09:57:58.000  2021-10-01T00:05:10.000   \n",
       "1          CONCRETE  2021-08-03T09:57:58.000  2021-10-01T00:05:10.000   \n",
       "2          CONCRETE  2021-08-03T09:57:58.000  2021-10-01T00:05:10.000   \n",
       "3          CONCRETE  2021-08-03T09:57:58.000  2021-10-01T00:05:10.000   \n",
       "4          CONCRETE  2021-08-03T09:57:58.000  2021-10-01T00:05:10.000   \n",
       "\n",
       "              oftcode equipmenttypedesc tostreetname previouspermitnumber  \n",
       "0  123190132710000000               NaN          NaN                  NaN  \n",
       "1  123190132710000000               NaN          NaN                  NaN  \n",
       "2  123190132710000000               NaN          NaN                  NaN  \n",
       "3  123190132710000000           Backhoe          NaN                  NaN  \n",
       "4  123190132710000000        Boom Truck          NaN                  NaN  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_dataset_identifier = datasets['Street Construction Permits (2013-2021)']\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    *\n",
    "WHERE \n",
    "    permitpurposecomments = 'DEPLOY / INSTALL FOLDABLE FLOOD BARRIER'   \n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}