socrata-query-language.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Socrata Query Language (SoQL) Clauses and Functions\n",
    "Auhtor: Mark Bauer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Table of Contents\n",
    "=================\n",
    "\n",
    "   1. Introduction\n",
    "   2. Socrata Open Data\n",
    "       * 2.1 Using Socrata Open Data API (SODA)\n",
    "       * 2.2 Using Sodapy\n",
    "       * 2.3 Socrata Query Language or \"SoQL\"\n",
    "   3. Importing Libraries\n",
    "   4. Retrieving Data Directly from Socrata Open Data API (SODA) \n",
    "   5. SoQL with Sodapy\n",
    "       * 5.1 SoQL Clauses\n",
    "       * 5.2 SoQL Function and Keyword Listing  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Introduction  \n",
    "This notebook demonstrates basic queries using SoQL, the Socrata Query Language. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Socrata Open Data\n",
    "\n",
    "## 2.1 Socrata Open Data API (SODA)\n",
    "\n",
    "More information can be found on the offical [Socrata Open Data API (SODA)](https://dev.socrata.com/) website. We use sodapy, a python client, to interact with the Socrata Open Data API.\n",
    "\n",
    "There's a lot of great resources on the website, and I encourage you to read through the [API Docs](https://dev.socrata.com/docs/endpoints.html) to further your understanding.\n",
    "\n",
    "![dev socrata](images/dev-socrata.png)\n",
    "\n",
    "**Source**: https://dev.socrata.com/\n",
    "\n",
    "## 2.2 Sodapy\n",
    "\n",
    "Sodapy - a python client for the Socrata Open Data API.\n",
    "Information about sodapy can be found in its offical docs on [GitHub](https://github.com/xmunoz/sodapy), as well as my notebook tutorial in this project here [sodapy-basics.ipynb](https://github.com/mebauer/sodapy-tutorial-nyc-open-data/blob/main/sodapy-basics.ipynb).\n",
    "\n",
    "\n",
    "In order use sodapy, a **source domain** (i.e. the open data source you are trying to connect to) needs to be passed to the Socrata class. Additionally, if a user wants to query a specific dataset, then the **dataset identifier** (i.e. the dataset id on the given source domain) needs to be passed as well. Below, we identify NYC Open Data's source domain `data.cityofnewyork.us` and the dataset identifier for the NYC 311 data set `erm2-nwe9`. The screenshot is the homepage of the 311 data set from NYC Open Data.\n",
    "\n",
    "![nyc-311-api-docs](images/nyc-311-api-docs.png)  \n",
    "\n",
    "**Source**: https://dev.socrata.com/foundry/data.cityofnewyork.us/erm2-nwe9\n",
    "\n",
    "## 2.3 Socrata Query Language or \"SoQl\"\n",
    "\n",
    "![soql screenshot](images/soql-screenshot.png)\n",
    "\n",
    "**Source**: https://dev.socrata.com/docs/queries/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. 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\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.ticker import FuncFormatter\n",
    "import seaborn as sns\n",
    "import urllib.parse"
   ]
  },
  {
   "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": [
    "## documention for installing watermark: https://github.com/rasbt/watermark\n",
    "%reload_ext watermark\n",
    "%watermark -t -d -v -p pandas,sodapy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Retrieving Data Directly from Socrata Open Data API (SODA)\n",
    "Using the Socrata API by itself and not sodapy."
   ]
  },
  {
   "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": "stdout",
     "output_type": "stream",
     "text": [
      "https://data.cityofnewyork.us/resource/erm2-nwe9.csv?$limit=20\n",
      "(20, 41)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_key</th>\n",
       "      <th>created_date</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>complaint_type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>location_type</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>...</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>taxi_company_borough</th>\n",
       "      <th>taxi_pick_up_location</th>\n",
       "      <th>bridge_highway_name</th>\n",
       "      <th>bridge_highway_direction</th>\n",
       "      <th>road_ramp</th>\n",
       "      <th>bridge_highway_segment</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60939475</td>\n",
       "      <td>2024-04-22T01:51:26.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11375</td>\n",
       "      <td>89-17 69 ROAD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.711328</td>\n",
       "      <td>-73.854657</td>\n",
       "      <td>\\n,  \\n(40.71132832064505, -73.8546568589395)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60938407</td>\n",
       "      <td>2024-04-22T01:51:25.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>10458</td>\n",
       "      <td>621 CRESCENT AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.853811</td>\n",
       "      <td>-73.887464</td>\n",
       "      <td>\\n,  \\n(40.853811374311036, -73.88746426718372)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60935450</td>\n",
       "      <td>2024-04-22T01:50:12.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11235</td>\n",
       "      <td>1245 AVENUE X</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.591954</td>\n",
       "      <td>-73.957513</td>\n",
       "      <td>\\n,  \\n(40.59195382210025, -73.95751272189338)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60935436</td>\n",
       "      <td>2024-04-22T01:50:11.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>11423</td>\n",
       "      <td>184-15 JAMAICA AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.709266</td>\n",
       "      <td>-73.775499</td>\n",
       "      <td>\\n,  \\n(40.70926609915028, -73.77549937142184)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60937446</td>\n",
       "      <td>2024-04-22T01:50:11.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>11373</td>\n",
       "      <td>92-02 CORONA AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.742439</td>\n",
       "      <td>-73.872218</td>\n",
       "      <td>\\n,  \\n(40.742439043264866, -73.87221775214613)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   unique_key             created_date closed_date agency  \\\n",
       "0    60939475  2024-04-22T01:51:26.000         NaN   NYPD   \n",
       "1    60938407  2024-04-22T01:51:25.000         NaN   NYPD   \n",
       "2    60935450  2024-04-22T01:50:12.000         NaN   NYPD   \n",
       "3    60935436  2024-04-22T01:50:11.000         NaN   NYPD   \n",
       "4    60937446  2024-04-22T01:50:11.000         NaN   NYPD   \n",
       "\n",
       "                       agency_name       complaint_type        descriptor  \\\n",
       "0  New York City Police Department  Noise - Residential  Loud Music/Party   \n",
       "1  New York City Police Department   Noise - Commercial  Loud Music/Party   \n",
       "2  New York City Police Department  Noise - Residential  Loud Music/Party   \n",
       "3  New York City Police Department   Noise - Commercial  Loud Music/Party   \n",
       "4  New York City Police Department   Noise - Commercial  Loud Music/Party   \n",
       "\n",
       "                location_type  incident_zip       incident_address  ...  \\\n",
       "0  Residential Building/House         11375          89-17 69 ROAD  ...   \n",
       "1         Club/Bar/Restaurant         10458    621 CRESCENT AVENUE  ...   \n",
       "2  Residential Building/House         11235          1245 AVENUE X  ...   \n",
       "3         Club/Bar/Restaurant         11423  184-15 JAMAICA AVENUE  ...   \n",
       "4         Club/Bar/Restaurant         11373    92-02 CORONA AVENUE  ...   \n",
       "\n",
       "  vehicle_type taxi_company_borough taxi_pick_up_location bridge_highway_name  \\\n",
       "0          NaN                  NaN                   NaN                 NaN   \n",
       "1          NaN                  NaN                   NaN                 NaN   \n",
       "2          NaN                  NaN                   NaN                 NaN   \n",
       "3          NaN                  NaN                   NaN                 NaN   \n",
       "4          NaN                  NaN                   NaN                 NaN   \n",
       "\n",
       "  bridge_highway_direction road_ramp bridge_highway_segment   latitude  \\\n",
       "0                      NaN       NaN                    NaN  40.711328   \n",
       "1                      NaN       NaN                    NaN  40.853811   \n",
       "2                      NaN       NaN                    NaN  40.591954   \n",
       "3                      NaN       NaN                    NaN  40.709266   \n",
       "4                      NaN       NaN                    NaN  40.742439   \n",
       "\n",
       "   longitude                                         location  \n",
       "0 -73.854657    \\n,  \\n(40.71132832064505, -73.8546568589395)  \n",
       "1 -73.887464  \\n,  \\n(40.853811374311036, -73.88746426718372)  \n",
       "2 -73.957513   \\n,  \\n(40.59195382210025, -73.95751272189338)  \n",
       "3 -73.775499   \\n,  \\n(40.70926609915028, -73.77549937142184)  \n",
       "4 -73.872218  \\n,  \\n(40.742439043264866, -73.87221775214613)  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# source domain for NYC Open Data on Socrata\n",
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "\n",
    "# dataset id for NYC 311 on NYC Open Data on Socrata\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "# limit to 20 rows\n",
    "limit = 20\n",
    "\n",
    "# construct url\n",
    "url = 'https://{}/resource/{}.csv?$limit={}'.format(socrata_domain, socrata_dataset_identifier, limit)\n",
    "\n",
    "# sanity check\n",
    "print(url)\n",
    "\n",
    "df = pd.read_csv(url)\n",
    "\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 20 entries, 0 to 19\n",
      "Data columns (total 41 columns):\n",
      " #   Column                          Non-Null Count  Dtype  \n",
      "---  ------                          --------------  -----  \n",
      " 0   unique_key                      20 non-null     int64  \n",
      " 1   created_date                    20 non-null     object \n",
      " 2   closed_date                     3 non-null      object \n",
      " 3   agency                          20 non-null     object \n",
      " 4   agency_name                     20 non-null     object \n",
      " 5   complaint_type                  20 non-null     object \n",
      " 6   descriptor                      20 non-null     object \n",
      " 7   location_type                   19 non-null     object \n",
      " 8   incident_zip                    20 non-null     int64  \n",
      " 9   incident_address                20 non-null     object \n",
      " 10  street_name                     20 non-null     object \n",
      " 11  cross_street_1                  20 non-null     object \n",
      " 12  cross_street_2                  20 non-null     object \n",
      " 13  intersection_street_1           20 non-null     object \n",
      " 14  intersection_street_2           20 non-null     object \n",
      " 15  address_type                    20 non-null     object \n",
      " 16  city                            20 non-null     object \n",
      " 17  landmark                        20 non-null     object \n",
      " 18  facility_type                   0 non-null      float64\n",
      " 19  status                          20 non-null     object \n",
      " 20  due_date                        0 non-null      float64\n",
      " 21  resolution_description          3 non-null      object \n",
      " 22  resolution_action_updated_date  19 non-null     object \n",
      " 23  community_board                 20 non-null     object \n",
      " 24  bbl                             20 non-null     int64  \n",
      " 25  borough                         20 non-null     object \n",
      " 26  x_coordinate_state_plane        20 non-null     int64  \n",
      " 27  y_coordinate_state_plane        20 non-null     int64  \n",
      " 28  open_data_channel_type          20 non-null     object \n",
      " 29  park_facility_name              20 non-null     object \n",
      " 30  park_borough                    20 non-null     object \n",
      " 31  vehicle_type                    0 non-null      float64\n",
      " 32  taxi_company_borough            0 non-null      float64\n",
      " 33  taxi_pick_up_location           1 non-null      object \n",
      " 34  bridge_highway_name             0 non-null      float64\n",
      " 35  bridge_highway_direction        0 non-null      float64\n",
      " 36  road_ramp                       0 non-null      float64\n",
      " 37  bridge_highway_segment          0 non-null      float64\n",
      " 38  latitude                        20 non-null     float64\n",
      " 39  longitude                       20 non-null     float64\n",
      " 40  location                        20 non-null     object \n",
      "dtypes: float64(10), int64(5), object(26)\n",
      "memory usage: 6.5+ KB\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://data.cityofnewyork.us/resource/erm2-nwe9.csv?$where=created_date%20>=%20%272020%27&$limit=20\n",
      "(20, 41)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_key</th>\n",
       "      <th>created_date</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>complaint_type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>location_type</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>...</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>taxi_company_borough</th>\n",
       "      <th>taxi_pick_up_location</th>\n",
       "      <th>bridge_highway_name</th>\n",
       "      <th>bridge_highway_direction</th>\n",
       "      <th>road_ramp</th>\n",
       "      <th>bridge_highway_segment</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60939475</td>\n",
       "      <td>2024-04-22T01:51:26.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11375</td>\n",
       "      <td>89-17 69 ROAD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.711328</td>\n",
       "      <td>-73.854657</td>\n",
       "      <td>\\n,  \\n(40.71132832064505, -73.8546568589395)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60938407</td>\n",
       "      <td>2024-04-22T01:51:25.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>10458</td>\n",
       "      <td>621 CRESCENT AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.853811</td>\n",
       "      <td>-73.887464</td>\n",
       "      <td>\\n,  \\n(40.853811374311036, -73.88746426718372)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60935450</td>\n",
       "      <td>2024-04-22T01:50:12.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11235</td>\n",
       "      <td>1245 AVENUE X</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.591954</td>\n",
       "      <td>-73.957513</td>\n",
       "      <td>\\n,  \\n(40.59195382210025, -73.95751272189338)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60937446</td>\n",
       "      <td>2024-04-22T01:50:11.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>11373</td>\n",
       "      <td>92-02 CORONA AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.742439</td>\n",
       "      <td>-73.872218</td>\n",
       "      <td>\\n,  \\n(40.742439043264866, -73.87221775214613)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60935436</td>\n",
       "      <td>2024-04-22T01:50:11.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>11423</td>\n",
       "      <td>184-15 JAMAICA AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.709266</td>\n",
       "      <td>-73.775499</td>\n",
       "      <td>\\n,  \\n(40.70926609915028, -73.77549937142184)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   unique_key             created_date closed_date agency  \\\n",
       "0    60939475  2024-04-22T01:51:26.000         NaN   NYPD   \n",
       "1    60938407  2024-04-22T01:51:25.000         NaN   NYPD   \n",
       "2    60935450  2024-04-22T01:50:12.000         NaN   NYPD   \n",
       "3    60937446  2024-04-22T01:50:11.000         NaN   NYPD   \n",
       "4    60935436  2024-04-22T01:50:11.000         NaN   NYPD   \n",
       "\n",
       "                       agency_name       complaint_type        descriptor  \\\n",
       "0  New York City Police Department  Noise - Residential  Loud Music/Party   \n",
       "1  New York City Police Department   Noise - Commercial  Loud Music/Party   \n",
       "2  New York City Police Department  Noise - Residential  Loud Music/Party   \n",
       "3  New York City Police Department   Noise - Commercial  Loud Music/Party   \n",
       "4  New York City Police Department   Noise - Commercial  Loud Music/Party   \n",
       "\n",
       "                location_type  incident_zip       incident_address  ...  \\\n",
       "0  Residential Building/House         11375          89-17 69 ROAD  ...   \n",
       "1         Club/Bar/Restaurant         10458    621 CRESCENT AVENUE  ...   \n",
       "2  Residential Building/House         11235          1245 AVENUE X  ...   \n",
       "3         Club/Bar/Restaurant         11373    92-02 CORONA AVENUE  ...   \n",
       "4         Club/Bar/Restaurant         11423  184-15 JAMAICA AVENUE  ...   \n",
       "\n",
       "  vehicle_type taxi_company_borough taxi_pick_up_location bridge_highway_name  \\\n",
       "0          NaN                  NaN                   NaN                 NaN   \n",
       "1          NaN                  NaN                   NaN                 NaN   \n",
       "2          NaN                  NaN                   NaN                 NaN   \n",
       "3          NaN                  NaN                   NaN                 NaN   \n",
       "4          NaN                  NaN                   NaN                 NaN   \n",
       "\n",
       "  bridge_highway_direction road_ramp bridge_highway_segment   latitude  \\\n",
       "0                      NaN       NaN                    NaN  40.711328   \n",
       "1                      NaN       NaN                    NaN  40.853811   \n",
       "2                      NaN       NaN                    NaN  40.591954   \n",
       "3                      NaN       NaN                    NaN  40.742439   \n",
       "4                      NaN       NaN                    NaN  40.709266   \n",
       "\n",
       "   longitude                                         location  \n",
       "0 -73.854657    \\n,  \\n(40.71132832064505, -73.8546568589395)  \n",
       "1 -73.887464  \\n,  \\n(40.853811374311036, -73.88746426718372)  \n",
       "2 -73.957513   \\n,  \\n(40.59195382210025, -73.95751272189338)  \n",
       "3 -73.872218  \\n,  \\n(40.742439043264866, -73.87221775214613)  \n",
       "4 -73.775499   \\n,  \\n(40.70926609915028, -73.77549937142184)  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# WHERE statements\n",
    "year = '2020'\n",
    "column = 'created_date'\n",
    "limit = 20\n",
    "\n",
    "# construct url\n",
    "url = 'https://data.cityofnewyork.us/resource/erm2-nwe9.csv?$where={}%20>=%20%27{}%27&$limit={}'\n",
    "url = url.format(column, year, limit)\n",
    "\n",
    "# sanity check\n",
    "print(url)\n",
    "\n",
    "df = pd.read_csv(url)\n",
    "\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://data.cityofnewyork.us/resource/erm2-nwe9.csv?$query=SELECT%20*%20LIMIT%2020\n",
      "(20, 47)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_key</th>\n",
       "      <th>created_date</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>complaint_type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>location_type</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>...</th>\n",
       "      <th>bridge_highway_segment</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "      <th>:@computed_region_efsh_h5xi</th>\n",
       "      <th>:@computed_region_f5dn_yrer</th>\n",
       "      <th>:@computed_region_yeji_bk3q</th>\n",
       "      <th>:@computed_region_92fq_4b7q</th>\n",
       "      <th>:@computed_region_sbqj_enih</th>\n",
       "      <th>:@computed_region_7mpf_4k6g</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60939475</td>\n",
       "      <td>2024-04-22T01:51:26.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11375</td>\n",
       "      <td>89-17 69 ROAD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.711328</td>\n",
       "      <td>-73.854657</td>\n",
       "      <td>\\n,  \\n(40.71132832064505, -73.8546568589395)</td>\n",
       "      <td>14786</td>\n",
       "      <td>40</td>\n",
       "      <td>3</td>\n",
       "      <td>28</td>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60938407</td>\n",
       "      <td>2024-04-22T01:51:25.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>10458</td>\n",
       "      <td>621 CRESCENT AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.853811</td>\n",
       "      <td>-73.887464</td>\n",
       "      <td>\\n,  \\n(40.853811374311036, -73.88746426718372)</td>\n",
       "      <td>10936</td>\n",
       "      <td>35</td>\n",
       "      <td>5</td>\n",
       "      <td>22</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60935450</td>\n",
       "      <td>2024-04-22T01:50:12.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11235</td>\n",
       "      <td>1245 AVENUE X</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.591954</td>\n",
       "      <td>-73.957513</td>\n",
       "      <td>\\n,  \\n(40.59195382210025, -73.95751272189338)</td>\n",
       "      <td>13826</td>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60935436</td>\n",
       "      <td>2024-04-22T01:50:11.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>11423</td>\n",
       "      <td>184-15 JAMAICA AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.709266</td>\n",
       "      <td>-73.775499</td>\n",
       "      <td>\\n,  \\n(40.70926609915028, -73.77549937142184)</td>\n",
       "      <td>24332</td>\n",
       "      <td>41</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>61</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60937446</td>\n",
       "      <td>2024-04-22T01:50:11.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>11373</td>\n",
       "      <td>92-02 CORONA AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.742439</td>\n",
       "      <td>-73.872218</td>\n",
       "      <td>\\n,  \\n(40.742439043264866, -73.87221775214613)</td>\n",
       "      <td>14784</td>\n",
       "      <td>66</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>68</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   unique_key             created_date closed_date agency  \\\n",
       "0    60939475  2024-04-22T01:51:26.000         NaN   NYPD   \n",
       "1    60938407  2024-04-22T01:51:25.000         NaN   NYPD   \n",
       "2    60935450  2024-04-22T01:50:12.000         NaN   NYPD   \n",
       "3    60935436  2024-04-22T01:50:11.000         NaN   NYPD   \n",
       "4    60937446  2024-04-22T01:50:11.000         NaN   NYPD   \n",
       "\n",
       "                       agency_name       complaint_type        descriptor  \\\n",
       "0  New York City Police Department  Noise - Residential  Loud Music/Party   \n",
       "1  New York City Police Department   Noise - Commercial  Loud Music/Party   \n",
       "2  New York City Police Department  Noise - Residential  Loud Music/Party   \n",
       "3  New York City Police Department   Noise - Commercial  Loud Music/Party   \n",
       "4  New York City Police Department   Noise - Commercial  Loud Music/Party   \n",
       "\n",
       "                location_type  incident_zip       incident_address  ...  \\\n",
       "0  Residential Building/House         11375          89-17 69 ROAD  ...   \n",
       "1         Club/Bar/Restaurant         10458    621 CRESCENT AVENUE  ...   \n",
       "2  Residential Building/House         11235          1245 AVENUE X  ...   \n",
       "3         Club/Bar/Restaurant         11423  184-15 JAMAICA AVENUE  ...   \n",
       "4         Club/Bar/Restaurant         11373    92-02 CORONA AVENUE  ...   \n",
       "\n",
       "  bridge_highway_segment   latitude  longitude  \\\n",
       "0                    NaN  40.711328 -73.854657   \n",
       "1                    NaN  40.853811 -73.887464   \n",
       "2                    NaN  40.591954 -73.957513   \n",
       "3                    NaN  40.709266 -73.775499   \n",
       "4                    NaN  40.742439 -73.872218   \n",
       "\n",
       "                                          location  \\\n",
       "0    \\n,  \\n(40.71132832064505, -73.8546568589395)   \n",
       "1  \\n,  \\n(40.853811374311036, -73.88746426718372)   \n",
       "2   \\n,  \\n(40.59195382210025, -73.95751272189338)   \n",
       "3   \\n,  \\n(40.70926609915028, -73.77549937142184)   \n",
       "4  \\n,  \\n(40.742439043264866, -73.87221775214613)   \n",
       "\n",
       "  :@computed_region_efsh_h5xi :@computed_region_f5dn_yrer  \\\n",
       "0                       14786                          40   \n",
       "1                       10936                          35   \n",
       "2                       13826                          32   \n",
       "3                       24332                          41   \n",
       "4                       14784                          66   \n",
       "\n",
       "  :@computed_region_yeji_bk3q :@computed_region_92fq_4b7q  \\\n",
       "0                           3                          28   \n",
       "1                           5                          22   \n",
       "2                           2                          15   \n",
       "3                           3                           6   \n",
       "4                           3                           5   \n",
       "\n",
       "   :@computed_region_sbqj_enih :@computed_region_7mpf_4k6g  \n",
       "0                           70                          70  \n",
       "1                           31                          31  \n",
       "2                           36                          36  \n",
       "3                           61                          61  \n",
       "4                           68                          68  \n",
       "\n",
       "[5 rows x 47 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query parameters\n",
    "query = 'SELECT%20*%20LIMIT%2020'\n",
    "url = 'https://data.cityofnewyork.us/resource/erm2-nwe9.csv?$query={}'.format(query)\n",
    "\n",
    "# sanity check\n",
    "print(url)\n",
    "\n",
    "df = pd.read_csv(url)\n",
    "\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "My preferred method. I like formatting my queries with the `query` parameter. We can use SQL-style statements as an argument to the `query` parameter. Note the `urllib.parse.quote_plus()` method to format the URL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "%0A++++SELECT%0A++++++++%2A%0A++++WHERE%0A++++++++created_date+%3E%3D+%272020%27%0A++++++++AND+descriptor+%3D%3D+%27Street+Flooding+%28SJ%29%27%0A++++LIMIT%0A++++++++100%0A++++\n",
      "url: https://data.cityofnewyork.us/resource/erm2-nwe9.csv?$query=%0A++++SELECT%0A++++++++%2A%0A++++WHERE%0A++++++++created_date+%3E%3D+%272020%27%0A++++++++AND+descriptor+%3D%3D+%27Street+Flooding+%28SJ%29%27%0A++++LIMIT%0A++++++++100%0A++++\n",
      "(100, 41)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_key</th>\n",
       "      <th>created_date</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>complaint_type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>location_type</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>...</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>taxi_company_borough</th>\n",
       "      <th>taxi_pick_up_location</th>\n",
       "      <th>bridge_highway_name</th>\n",
       "      <th>bridge_highway_direction</th>\n",
       "      <th>road_ramp</th>\n",
       "      <th>bridge_highway_segment</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60937647</td>\n",
       "      <td>2024-04-21T22:56:00.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10036</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.759200</td>\n",
       "      <td>-73.984619</td>\n",
       "      <td>\\n,  \\n(40.7591997120635, -73.98461925002192)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60937648</td>\n",
       "      <td>2024-04-21T21:59:00.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11217</td>\n",
       "      <td>92 FORT GREENE PLACE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.686723</td>\n",
       "      <td>-73.976501</td>\n",
       "      <td>\\n,  \\n(40.68672300881048, -73.97650143173598)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60937649</td>\n",
       "      <td>2024-04-21T13:56:00.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11220</td>\n",
       "      <td>423 46 STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.648265</td>\n",
       "      <td>-74.009499</td>\n",
       "      <td>\\n,  \\n(40.64826519646832, -74.00949923604392)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60938666</td>\n",
       "      <td>2024-04-21T11:14:00.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11377</td>\n",
       "      <td>57-06 31 AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.757292</td>\n",
       "      <td>-73.904415</td>\n",
       "      <td>\\n,  \\n(40.75729191233922, -73.90441538293082)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60929521</td>\n",
       "      <td>2024-04-20T15:32:00.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11369</td>\n",
       "      <td>31-33 95 STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.759697</td>\n",
       "      <td>-73.873614</td>\n",
       "      <td>\\n,  \\n(40.75969682793435, -73.87361408066782)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   unique_key             created_date closed_date agency  \\\n",
       "0    60937647  2024-04-21T22:56:00.000         NaN    DEP   \n",
       "1    60937648  2024-04-21T21:59:00.000         NaN    DEP   \n",
       "2    60937649  2024-04-21T13:56:00.000         NaN    DEP   \n",
       "3    60938666  2024-04-21T11:14:00.000         NaN    DEP   \n",
       "4    60929521  2024-04-20T15:32:00.000         NaN    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  location_type  incident_zip      incident_address  \\\n",
       "0  Street Flooding (SJ)            NaN         10036                   NaN   \n",
       "1  Street Flooding (SJ)            NaN         11217  92 FORT GREENE PLACE   \n",
       "2  Street Flooding (SJ)            NaN         11220         423 46 STREET   \n",
       "3  Street Flooding (SJ)            NaN         11377       57-06 31 AVENUE   \n",
       "4  Street Flooding (SJ)            NaN         11369       31-33 95 STREET   \n",
       "\n",
       "   ... vehicle_type taxi_company_borough taxi_pick_up_location  \\\n",
       "0  ...          NaN                  NaN                   NaN   \n",
       "1  ...          NaN                  NaN                   NaN   \n",
       "2  ...          NaN                  NaN                   NaN   \n",
       "3  ...          NaN                  NaN                   NaN   \n",
       "4  ...          NaN                  NaN                   NaN   \n",
       "\n",
       "  bridge_highway_name bridge_highway_direction road_ramp  \\\n",
       "0                 NaN                      NaN       NaN   \n",
       "1                 NaN                      NaN       NaN   \n",
       "2                 NaN                      NaN       NaN   \n",
       "3                 NaN                      NaN       NaN   \n",
       "4                 NaN                      NaN       NaN   \n",
       "\n",
       "  bridge_highway_segment   latitude  longitude  \\\n",
       "0                    NaN  40.759200 -73.984619   \n",
       "1                    NaN  40.686723 -73.976501   \n",
       "2                    NaN  40.648265 -74.009499   \n",
       "3                    NaN  40.757292 -73.904415   \n",
       "4                    NaN  40.759697 -73.873614   \n",
       "\n",
       "                                         location  \n",
       "0   \\n,  \\n(40.7591997120635, -73.98461925002192)  \n",
       "1  \\n,  \\n(40.68672300881048, -73.97650143173598)  \n",
       "2  \\n,  \\n(40.64826519646832, -74.00949923604392)  \n",
       "3  \\n,  \\n(40.75729191233922, -73.90441538293082)  \n",
       "4  \\n,  \\n(40.75969682793435, -73.87361408066782)  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query parameter as a string\n",
    "query = \"\"\"\n",
    "    SELECT\n",
    "        *\n",
    "    WHERE\n",
    "        created_date >= '2020'\n",
    "        AND descriptor == 'Street Flooding (SJ)'\n",
    "    LIMIT\n",
    "        100\n",
    "    \"\"\"\n",
    "\n",
    "# format values in the url, easier to read\n",
    "safe_string = urllib.parse.quote_plus(query)\n",
    "print(safe_string)\n",
    "\n",
    "# compose url\n",
    "url = 'https://data.cityofnewyork.us/resource/erm2-nwe9.csv?$query={}'.format(safe_string)\n",
    "print('url:', url)\n",
    "\n",
    "df = pd.read_csv(url)\n",
    "\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5. SoQL with Sodapy"
   ]
  },
  {
   "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": "markdown",
   "metadata": {},
   "source": [
    "## 5.1 SoQL Clauses\n",
    "Examples below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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": [
      "shape of data: (10, 36)\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>location_type</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>street_name</th>\n",
       "      <th>...</th>\n",
       "      <th>park_borough</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "      <th>:@computed_region_efsh_h5xi</th>\n",
       "      <th>:@computed_region_f5dn_yrer</th>\n",
       "      <th>:@computed_region_yeji_bk3q</th>\n",
       "      <th>:@computed_region_92fq_4b7q</th>\n",
       "      <th>:@computed_region_sbqj_enih</th>\n",
       "      <th>:@computed_region_7mpf_4k6g</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60939475</td>\n",
       "      <td>2024-04-22T01:51:26.000</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11375</td>\n",
       "      <td>89-17 69 ROAD</td>\n",
       "      <td>69 ROAD</td>\n",
       "      <td>...</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>40.71132832064505</td>\n",
       "      <td>-73.8546568589395</td>\n",
       "      <td>{'latitude': '40.71132832064505', 'longitude':...</td>\n",
       "      <td>14786</td>\n",
       "      <td>40</td>\n",
       "      <td>3</td>\n",
       "      <td>28</td>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60938407</td>\n",
       "      <td>2024-04-22T01:51:25.000</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>10458</td>\n",
       "      <td>621 CRESCENT AVENUE</td>\n",
       "      <td>CRESCENT AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>40.853811374311036</td>\n",
       "      <td>-73.88746426718372</td>\n",
       "      <td>{'latitude': '40.853811374311036', 'longitude'...</td>\n",
       "      <td>10936</td>\n",
       "      <td>35</td>\n",
       "      <td>5</td>\n",
       "      <td>22</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60935450</td>\n",
       "      <td>2024-04-22T01:50:12.000</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Residential</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Residential Building/House</td>\n",
       "      <td>11235</td>\n",
       "      <td>1245 AVENUE X</td>\n",
       "      <td>AVENUE X</td>\n",
       "      <td>...</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>40.59195382210025</td>\n",
       "      <td>-73.95751272189338</td>\n",
       "      <td>{'latitude': '40.59195382210025', 'longitude':...</td>\n",
       "      <td>13826</td>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60935436</td>\n",
       "      <td>2024-04-22T01:50:11.000</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>11423</td>\n",
       "      <td>184-15 JAMAICA AVENUE</td>\n",
       "      <td>JAMAICA AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>40.70926609915028</td>\n",
       "      <td>-73.77549937142184</td>\n",
       "      <td>{'latitude': '40.70926609915028', 'longitude':...</td>\n",
       "      <td>24332</td>\n",
       "      <td>41</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>61</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60937446</td>\n",
       "      <td>2024-04-22T01:50:11.000</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>Noise - Commercial</td>\n",
       "      <td>Loud Music/Party</td>\n",
       "      <td>Club/Bar/Restaurant</td>\n",
       "      <td>11373</td>\n",
       "      <td>92-02 CORONA AVENUE</td>\n",
       "      <td>CORONA AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>40.742439043264866</td>\n",
       "      <td>-73.87221775214613</td>\n",
       "      <td>{'latitude': '40.742439043264866', 'longitude'...</td>\n",
       "      <td>14784</td>\n",
       "      <td>66</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>68</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_key             created_date agency                      agency_name  \\\n",
       "0   60939475  2024-04-22T01:51:26.000   NYPD  New York City Police Department   \n",
       "1   60938407  2024-04-22T01:51:25.000   NYPD  New York City Police Department   \n",
       "2   60935450  2024-04-22T01:50:12.000   NYPD  New York City Police Department   \n",
       "3   60935436  2024-04-22T01:50:11.000   NYPD  New York City Police Department   \n",
       "4   60937446  2024-04-22T01:50:11.000   NYPD  New York City Police Department   \n",
       "\n",
       "        complaint_type        descriptor               location_type  \\\n",
       "0  Noise - Residential  Loud Music/Party  Residential Building/House   \n",
       "1   Noise - Commercial  Loud Music/Party         Club/Bar/Restaurant   \n",
       "2  Noise - Residential  Loud Music/Party  Residential Building/House   \n",
       "3   Noise - Commercial  Loud Music/Party         Club/Bar/Restaurant   \n",
       "4   Noise - Commercial  Loud Music/Party         Club/Bar/Restaurant   \n",
       "\n",
       "  incident_zip       incident_address      street_name  ... park_borough  \\\n",
       "0        11375          89-17 69 ROAD          69 ROAD  ...       QUEENS   \n",
       "1        10458    621 CRESCENT AVENUE  CRESCENT AVENUE  ...        BRONX   \n",
       "2        11235          1245 AVENUE X         AVENUE X  ...     BROOKLYN   \n",
       "3        11423  184-15 JAMAICA AVENUE   JAMAICA AVENUE  ...       QUEENS   \n",
       "4        11373    92-02 CORONA AVENUE    CORONA AVENUE  ...       QUEENS   \n",
       "\n",
       "             latitude           longitude  \\\n",
       "0   40.71132832064505   -73.8546568589395   \n",
       "1  40.853811374311036  -73.88746426718372   \n",
       "2   40.59195382210025  -73.95751272189338   \n",
       "3   40.70926609915028  -73.77549937142184   \n",
       "4  40.742439043264866  -73.87221775214613   \n",
       "\n",
       "                                            location  \\\n",
       "0  {'latitude': '40.71132832064505', 'longitude':...   \n",
       "1  {'latitude': '40.853811374311036', 'longitude'...   \n",
       "2  {'latitude': '40.59195382210025', 'longitude':...   \n",
       "3  {'latitude': '40.70926609915028', 'longitude':...   \n",
       "4  {'latitude': '40.742439043264866', 'longitude'...   \n",
       "\n",
       "  :@computed_region_efsh_h5xi :@computed_region_f5dn_yrer  \\\n",
       "0                       14786                          40   \n",
       "1                       10936                          35   \n",
       "2                       13826                          32   \n",
       "3                       24332                          41   \n",
       "4                       14784                          66   \n",
       "\n",
       "  :@computed_region_yeji_bk3q :@computed_region_92fq_4b7q  \\\n",
       "0                           3                          28   \n",
       "1                           5                          22   \n",
       "2                           2                          15   \n",
       "3                           3                           6   \n",
       "4                           3                           5   \n",
       "\n",
       "  :@computed_region_sbqj_enih :@computed_region_7mpf_4k6g  \n",
       "0                          70                          70  \n",
       "1                          31                          31  \n",
       "2                          36                          36  \n",
       "3                          61                          61  \n",
       "4                          68                          68  \n",
       "\n",
       "[5 rows x 36 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# source domain for NYC Open Data on Socrata\n",
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "\n",
    "# dataset id for NYC 311 on NYC Open Data on Socrata\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "# Socrata - The main class that interacts with the SODA API.\n",
    "# We pass the source domain value of NYC Open data, the app token as 'None',\n",
    "# and set the timeout parameter for '100 seconds'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select all columns, limit our records to 10\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT\n",
    "    *    \n",
    "LIMIT\n",
    "    10\n",
    "\"\"\"\n",
    "\n",
    "# returned as JSON from API / converted to Python list of dictionaries by sodapy\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "# closing client\n",
    "client.close()\n",
    "\n",
    "# convert to pandas DataFrame\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 36 columns):\n",
      " #   Column                          Non-Null Count  Dtype \n",
      "---  ------                          --------------  ----- \n",
      " 0   unique_key                      10 non-null     object\n",
      " 1   created_date                    10 non-null     object\n",
      " 2   agency                          10 non-null     object\n",
      " 3   agency_name                     10 non-null     object\n",
      " 4   complaint_type                  10 non-null     object\n",
      " 5   descriptor                      10 non-null     object\n",
      " 6   location_type                   10 non-null     object\n",
      " 7   incident_zip                    10 non-null     object\n",
      " 8   incident_address                10 non-null     object\n",
      " 9   street_name                     10 non-null     object\n",
      " 10  cross_street_1                  10 non-null     object\n",
      " 11  cross_street_2                  10 non-null     object\n",
      " 12  intersection_street_1           10 non-null     object\n",
      " 13  intersection_street_2           10 non-null     object\n",
      " 14  address_type                    10 non-null     object\n",
      " 15  city                            10 non-null     object\n",
      " 16  landmark                        10 non-null     object\n",
      " 17  status                          10 non-null     object\n",
      " 18  resolution_action_updated_date  10 non-null     object\n",
      " 19  community_board                 10 non-null     object\n",
      " 20  bbl                             10 non-null     object\n",
      " 21  borough                         10 non-null     object\n",
      " 22  x_coordinate_state_plane        10 non-null     object\n",
      " 23  y_coordinate_state_plane        10 non-null     object\n",
      " 24  open_data_channel_type          10 non-null     object\n",
      " 25  park_facility_name              10 non-null     object\n",
      " 26  park_borough                    10 non-null     object\n",
      " 27  latitude                        10 non-null     object\n",
      " 28  longitude                       10 non-null     object\n",
      " 29  location                        10 non-null     object\n",
      " 30  :@computed_region_efsh_h5xi     10 non-null     object\n",
      " 31  :@computed_region_f5dn_yrer     10 non-null     object\n",
      " 32  :@computed_region_yeji_bk3q     10 non-null     object\n",
      " 33  :@computed_region_92fq_4b7q     10 non-null     object\n",
      " 34  :@computed_region_sbqj_enih     10 non-null     object\n",
      " 35  :@computed_region_7mpf_4k6g     10 non-null     object\n",
      "dtypes: object(36)\n",
      "memory usage: 2.9+ KB\n"
     ]
    }
   ],
   "source": [
    "# examine available columns\n",
    "results_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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": [
      "shape of data: (1000, 30)\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>intersection_street_1</th>\n",
       "      <th>intersection_street_2</th>\n",
       "      <th>address_type</th>\n",
       "      <th>...</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>street_name</th>\n",
       "      <th>cross_street_1</th>\n",
       "      <th>cross_street_2</th>\n",
       "      <th>bbl</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>resolution_description</th>\n",
       "      <th>resolution_action_updated_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60937647</td>\n",
       "      <td>2024-04-21T22:56: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>10036</td>\n",
       "      <td>7 AVENUE</td>\n",
       "      <td>WEST 47 STREET</td>\n",
       "      <td>INTERSECTION</td>\n",
       "      <td>...</td>\n",
       "      <td>-73.98461925002192</td>\n",
       "      <td>{'latitude': '40.7591997120635', '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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60937648</td>\n",
       "      <td>2024-04-21T21:59: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>11217</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>-73.97650143173598</td>\n",
       "      <td>{'latitude': '40.68672300881048', 'longitude':...</td>\n",
       "      <td>92 FORT GREENE PLACE</td>\n",
       "      <td>FORT GREENE PLACE</td>\n",
       "      <td>LAFAYETTE AVE</td>\n",
       "      <td>HANSON PL</td>\n",
       "      <td>3021120032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60937649</td>\n",
       "      <td>2024-04-21T13:56: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>11220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>-74.00949923604392</td>\n",
       "      <td>{'latitude': '40.64826519646832', 'longitude':...</td>\n",
       "      <td>423 46 STREET</td>\n",
       "      <td>46 STREET</td>\n",
       "      <td>4 AVE</td>\n",
       "      <td>5 AVE</td>\n",
       "      <td>3007470067</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60938666</td>\n",
       "      <td>2024-04-21T11:14: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>11377</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>-73.90441538293082</td>\n",
       "      <td>{'latitude': '40.75729191233922', 'longitude':...</td>\n",
       "      <td>57-06 31 AVENUE</td>\n",
       "      <td>31 AVENUE</td>\n",
       "      <td>AMTRAK-NY/CONN RAIL LINE</td>\n",
       "      <td>58 ST</td>\n",
       "      <td>4011340019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60929521</td>\n",
       "      <td>2024-04-20T15:32: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>11369</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>-73.87361408066782</td>\n",
       "      <td>{'latitude': '40.75969682793435', 'longitude':...</td>\n",
       "      <td>31-33 95 STREET</td>\n",
       "      <td>95 STREET</td>\n",
       "      <td>31 AVE</td>\n",
       "      <td>JACKSON MILL RD</td>\n",
       "      <td>4014070054</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_key             created_date agency  \\\n",
       "0   60937647  2024-04-21T22:56:00.000    DEP   \n",
       "1   60937648  2024-04-21T21:59:00.000    DEP   \n",
       "2   60937649  2024-04-21T13:56:00.000    DEP   \n",
       "3   60938666  2024-04-21T11:14:00.000    DEP   \n",
       "4   60929521  2024-04-20T15:32: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 intersection_street_1  \\\n",
       "0  Street Flooding (SJ)        10036              7 AVENUE   \n",
       "1  Street Flooding (SJ)        11217                   NaN   \n",
       "2  Street Flooding (SJ)        11220                   NaN   \n",
       "3  Street Flooding (SJ)        11377                   NaN   \n",
       "4  Street Flooding (SJ)        11369                   NaN   \n",
       "\n",
       "  intersection_street_2  address_type  ...           longitude  \\\n",
       "0        WEST 47 STREET  INTERSECTION  ...  -73.98461925002192   \n",
       "1                   NaN       ADDRESS  ...  -73.97650143173598   \n",
       "2                   NaN       ADDRESS  ...  -74.00949923604392   \n",
       "3                   NaN       ADDRESS  ...  -73.90441538293082   \n",
       "4                   NaN       ADDRESS  ...  -73.87361408066782   \n",
       "\n",
       "                                            location      incident_address  \\\n",
       "0  {'latitude': '40.7591997120635', 'longitude': ...                   NaN   \n",
       "1  {'latitude': '40.68672300881048', 'longitude':...  92 FORT GREENE PLACE   \n",
       "2  {'latitude': '40.64826519646832', 'longitude':...         423 46 STREET   \n",
       "3  {'latitude': '40.75729191233922', 'longitude':...       57-06 31 AVENUE   \n",
       "4  {'latitude': '40.75969682793435', 'longitude':...       31-33 95 STREET   \n",
       "\n",
       "         street_name            cross_street_1   cross_street_2         bbl  \\\n",
       "0                NaN                       NaN              NaN         NaN   \n",
       "1  FORT GREENE PLACE             LAFAYETTE AVE        HANSON PL  3021120032   \n",
       "2          46 STREET                     4 AVE            5 AVE  3007470067   \n",
       "3          31 AVENUE  AMTRAK-NY/CONN RAIL LINE            58 ST  4011340019   \n",
       "4          95 STREET                    31 AVE  JACKSON MILL RD  4014070054   \n",
       "\n",
       "  closed_date resolution_description resolution_action_updated_date  \n",
       "0         NaN                    NaN                            NaN  \n",
       "1         NaN                    NaN                            NaN  \n",
       "2         NaN                    NaN                            NaN  \n",
       "3         NaN                    NaN                            NaN  \n",
       "4         NaN                    NaN                            NaN  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select all columns, where the descriptor\n",
    "# is Street Flooding (SJ), limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT\n",
    "    *\n",
    "WHERE\n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "LIMIT\n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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": [
      "sanity check:\n",
      "min: 2011-08-16T23:52:00.000\n",
      "max: 2011-12-31T17:03:00.000\n",
      "\n",
      "shape of data: (1000, 31)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_key</th>\n",
       "      <th>created_date</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>complaint_type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>street_name</th>\n",
       "      <th>...</th>\n",
       "      <th>x_coordinate_state_plane</th>\n",
       "      <th>y_coordinate_state_plane</th>\n",
       "      <th>open_data_channel_type</th>\n",
       "      <th>park_facility_name</th>\n",
       "      <th>park_borough</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "      <th>intersection_street_1</th>\n",
       "      <th>intersection_street_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22426149</td>\n",
       "      <td>2011-12-31T17:03:00.000</td>\n",
       "      <td>2012-01-02T08:50: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>10460</td>\n",
       "      <td>1956 CROTONA PARKWAY</td>\n",
       "      <td>CROTONA PARKWAY</td>\n",
       "      <td>...</td>\n",
       "      <td>1015982</td>\n",
       "      <td>246199</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>Unspecified</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>40.84237755161368</td>\n",
       "      <td>-73.88531510513788</td>\n",
       "      <td>{'latitude': '40.84237755161368', 'longitude':...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22424342</td>\n",
       "      <td>2011-12-30T10:00:00.000</td>\n",
       "      <td>2011-12-31T09:20:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>10024</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>991690</td>\n",
       "      <td>225337</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>Unspecified</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>40.78517106970749</td>\n",
       "      <td>-73.97313367344907</td>\n",
       "      <td>{'latitude': '40.78517106970749', 'longitude':...</td>\n",
       "      <td>WEST 84 STREET</td>\n",
       "      <td>COLUMBUS AVENUE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22425059</td>\n",
       "      <td>2011-12-30T09:25:00.000</td>\n",
       "      <td>2011-12-30T13:55: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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>Unspecified</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GRAHAM CT</td>\n",
       "      <td>26 AVE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22415128</td>\n",
       "      <td>2011-12-29T17:13:00.000</td>\n",
       "      <td>2011-12-30T11:00: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>10306</td>\n",
       "      <td>263 COLONY AVENUE</td>\n",
       "      <td>COLONY AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>958629</td>\n",
       "      <td>147876</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>Unspecified</td>\n",
       "      <td>STATEN ISLAND</td>\n",
       "      <td>40.572524396506175</td>\n",
       "      <td>-74.09222458237058</td>\n",
       "      <td>{'latitude': '40.572524396506175', 'longitude'...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22414065</td>\n",
       "      <td>2011-12-29T12:33:00.000</td>\n",
       "      <td>2011-12-30T11:30:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>10306</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>946267</td>\n",
       "      <td>146214</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>Unspecified</td>\n",
       "      <td>STATEN ISLAND</td>\n",
       "      <td>40.56791819419245</td>\n",
       "      <td>-74.13671306905549</td>\n",
       "      <td>{'latitude': '40.56791819419245', 'longitude':...</td>\n",
       "      <td>AMBER STREET</td>\n",
       "      <td>THOMAS STREET</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_key             created_date              closed_date agency  \\\n",
       "0   22426149  2011-12-31T17:03:00.000  2012-01-02T08:50:00.000    DEP   \n",
       "1   22424342  2011-12-30T10:00:00.000  2011-12-31T09:20:00.000    DEP   \n",
       "2   22425059  2011-12-30T09:25:00.000  2011-12-30T13:55:00.000    DEP   \n",
       "3   22415128  2011-12-29T17:13:00.000  2011-12-30T11:00:00.000    DEP   \n",
       "4   22414065  2011-12-29T12:33:00.000  2011-12-30T11:30: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)        10460  1956 CROTONA PARKWAY  CROTONA PARKWAY   \n",
       "1  Street Flooding (SJ)        10024                   NaN              NaN   \n",
       "2  Street Flooding (SJ)          NaN                   NaN              NaN   \n",
       "3  Street Flooding (SJ)        10306     263 COLONY AVENUE    COLONY AVENUE   \n",
       "4  Street Flooding (SJ)        10306                   NaN              NaN   \n",
       "\n",
       "   ... x_coordinate_state_plane y_coordinate_state_plane  \\\n",
       "0  ...                  1015982                   246199   \n",
       "1  ...                   991690                   225337   \n",
       "2  ...                      NaN                      NaN   \n",
       "3  ...                   958629                   147876   \n",
       "4  ...                   946267                   146214   \n",
       "\n",
       "  open_data_channel_type park_facility_name   park_borough  \\\n",
       "0                UNKNOWN        Unspecified          BRONX   \n",
       "1                UNKNOWN        Unspecified      MANHATTAN   \n",
       "2                UNKNOWN        Unspecified         QUEENS   \n",
       "3                UNKNOWN        Unspecified  STATEN ISLAND   \n",
       "4                UNKNOWN        Unspecified  STATEN ISLAND   \n",
       "\n",
       "             latitude           longitude  \\\n",
       "0   40.84237755161368  -73.88531510513788   \n",
       "1   40.78517106970749  -73.97313367344907   \n",
       "2                 NaN                 NaN   \n",
       "3  40.572524396506175  -74.09222458237058   \n",
       "4   40.56791819419245  -74.13671306905549   \n",
       "\n",
       "                                            location intersection_street_1  \\\n",
       "0  {'latitude': '40.84237755161368', 'longitude':...                   NaN   \n",
       "1  {'latitude': '40.78517106970749', 'longitude':...        WEST 84 STREET   \n",
       "2                                                NaN             GRAHAM CT   \n",
       "3  {'latitude': '40.572524396506175', 'longitude'...                   NaN   \n",
       "4  {'latitude': '40.56791819419245', 'longitude':...          AMBER STREET   \n",
       "\n",
       "  intersection_street_2  \n",
       "0                   NaN  \n",
       "1       COLUMBUS AVENUE  \n",
       "2                26 AVE  \n",
       "3                   NaN  \n",
       "4         THOMAS STREET  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select all columns, where the descriptor\n",
    "# is Street Flooding (SJ) and created_date is between 2011 and 2012, limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    * \n",
    "WHERE \n",
    "    created_date BETWEEN '2011' AND '2012'\n",
    "    AND descriptor == 'Street Flooding (SJ)'\n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('sanity check:')\n",
    "print('min:', results_df.created_date.min())\n",
    "print('max:', results_df.created_date.max())\n",
    "\n",
    "print('\\nshape of data: {}'.format(results_df.shape))\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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": [
      "shape of data: (1000, 30)\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>intersection_street_1</th>\n",
       "      <th>intersection_street_2</th>\n",
       "      <th>address_type</th>\n",
       "      <th>...</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>street_name</th>\n",
       "      <th>cross_street_1</th>\n",
       "      <th>cross_street_2</th>\n",
       "      <th>bbl</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>resolution_description</th>\n",
       "      <th>resolution_action_updated_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60937647</td>\n",
       "      <td>2024-04-21T22:56: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>10036</td>\n",
       "      <td>7 AVENUE</td>\n",
       "      <td>WEST 47 STREET</td>\n",
       "      <td>INTERSECTION</td>\n",
       "      <td>...</td>\n",
       "      <td>-73.98461925002192</td>\n",
       "      <td>{'latitude': '40.7591997120635', '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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60937648</td>\n",
       "      <td>2024-04-21T21:59: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>11217</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>-73.97650143173598</td>\n",
       "      <td>{'latitude': '40.68672300881048', 'longitude':...</td>\n",
       "      <td>92 FORT GREENE PLACE</td>\n",
       "      <td>FORT GREENE PLACE</td>\n",
       "      <td>LAFAYETTE AVE</td>\n",
       "      <td>HANSON PL</td>\n",
       "      <td>3021120032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60937649</td>\n",
       "      <td>2024-04-21T13:56: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>11220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>-74.00949923604392</td>\n",
       "      <td>{'latitude': '40.64826519646832', 'longitude':...</td>\n",
       "      <td>423 46 STREET</td>\n",
       "      <td>46 STREET</td>\n",
       "      <td>4 AVE</td>\n",
       "      <td>5 AVE</td>\n",
       "      <td>3007470067</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60938666</td>\n",
       "      <td>2024-04-21T11:14: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>11377</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>-73.90441538293082</td>\n",
       "      <td>{'latitude': '40.75729191233922', 'longitude':...</td>\n",
       "      <td>57-06 31 AVENUE</td>\n",
       "      <td>31 AVENUE</td>\n",
       "      <td>AMTRAK-NY/CONN RAIL LINE</td>\n",
       "      <td>58 ST</td>\n",
       "      <td>4011340019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60929521</td>\n",
       "      <td>2024-04-20T15:32: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>11369</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>-73.87361408066782</td>\n",
       "      <td>{'latitude': '40.75969682793435', 'longitude':...</td>\n",
       "      <td>31-33 95 STREET</td>\n",
       "      <td>95 STREET</td>\n",
       "      <td>31 AVE</td>\n",
       "      <td>JACKSON MILL RD</td>\n",
       "      <td>4014070054</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_key             created_date agency  \\\n",
       "0   60937647  2024-04-21T22:56:00.000    DEP   \n",
       "1   60937648  2024-04-21T21:59:00.000    DEP   \n",
       "2   60937649  2024-04-21T13:56:00.000    DEP   \n",
       "3   60938666  2024-04-21T11:14:00.000    DEP   \n",
       "4   60929521  2024-04-20T15:32: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 intersection_street_1  \\\n",
       "0  Street Flooding (SJ)        10036              7 AVENUE   \n",
       "1  Street Flooding (SJ)        11217                   NaN   \n",
       "2  Street Flooding (SJ)        11220                   NaN   \n",
       "3  Street Flooding (SJ)        11377                   NaN   \n",
       "4  Street Flooding (SJ)        11369                   NaN   \n",
       "\n",
       "  intersection_street_2  address_type  ...           longitude  \\\n",
       "0        WEST 47 STREET  INTERSECTION  ...  -73.98461925002192   \n",
       "1                   NaN       ADDRESS  ...  -73.97650143173598   \n",
       "2                   NaN       ADDRESS  ...  -74.00949923604392   \n",
       "3                   NaN       ADDRESS  ...  -73.90441538293082   \n",
       "4                   NaN       ADDRESS  ...  -73.87361408066782   \n",
       "\n",
       "                                            location      incident_address  \\\n",
       "0  {'latitude': '40.7591997120635', 'longitude': ...                   NaN   \n",
       "1  {'latitude': '40.68672300881048', 'longitude':...  92 FORT GREENE PLACE   \n",
       "2  {'latitude': '40.64826519646832', 'longitude':...         423 46 STREET   \n",
       "3  {'latitude': '40.75729191233922', 'longitude':...       57-06 31 AVENUE   \n",
       "4  {'latitude': '40.75969682793435', 'longitude':...       31-33 95 STREET   \n",
       "\n",
       "         street_name            cross_street_1   cross_street_2         bbl  \\\n",
       "0                NaN                       NaN              NaN         NaN   \n",
       "1  FORT GREENE PLACE             LAFAYETTE AVE        HANSON PL  3021120032   \n",
       "2          46 STREET                     4 AVE            5 AVE  3007470067   \n",
       "3          31 AVENUE  AMTRAK-NY/CONN RAIL LINE            58 ST  4011340019   \n",
       "4          95 STREET                    31 AVE  JACKSON MILL RD  4014070054   \n",
       "\n",
       "  closed_date resolution_description resolution_action_updated_date  \n",
       "0         NaN                    NaN                            NaN  \n",
       "1         NaN                    NaN                            NaN  \n",
       "2         NaN                    NaN                            NaN  \n",
       "3         NaN                    NaN                            NaN  \n",
       "4         NaN                    NaN                            NaN  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select all columns, where the descriptor\n",
    "# is Street Flooding (SJ), sort the created_date in descending order and limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT\n",
    "    *\n",
    "WHERE\n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "ORDER BY\n",
    "    created_date DESC\n",
    "LIMIT\n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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": [
      "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>borough</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>QUEENS</td>\n",
       "      <td>15203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>10625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>STATEN ISLAND</td>\n",
       "      <td>7069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>3401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BRONX</td>\n",
       "      <td>3160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Unspecified</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         borough  count\n",
       "0         QUEENS  15203\n",
       "1       BROOKLYN  10625\n",
       "2  STATEN ISLAND   7069\n",
       "3      MANHATTAN   3401\n",
       "4          BRONX   3160\n",
       "5    Unspecified     50\n",
       "6            NaN      4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the borough and count grouped by borough,\n",
    "# where the descriptor is Street Flooding (SJ), sort the count in descending order\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    borough, \n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    borough\n",
    "ORDER BY \n",
    "    count DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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": [
      "shape of data: (3, 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>borough</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>QUEENS</td>\n",
       "      <td>15203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>10625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>STATEN ISLAND</td>\n",
       "      <td>7069</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         borough  count\n",
       "0         QUEENS  15203\n",
       "1       BROOKLYN  10625\n",
       "2  STATEN ISLAND   7069"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the borough and count grouped by borough having\n",
    "# more than 5,000 counts, where the descriptor is Street Flooding (SJ),\n",
    "# sort the count in descending order\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    borough, \n",
    "    count(*) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    borough\n",
    "HAVING \n",
    "    count > 5000\n",
    "ORDER BY \n",
    "    count DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.2 SoQL Function and Keyword Listing\n",
    "\n",
    "Examples below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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": [
      "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>114680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>39512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Flood Light Lamp Out</td>\n",
       "      <td>6517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Highway Flooding (SH)</td>\n",
       "      <td>3156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Flood Light Lamp Cycling</td>\n",
       "      <td>2595</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>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Flood Light Lamp Missing</td>\n",
       "      <td>212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Flood Light Lamp Dim</td>\n",
       "      <td>185</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         descriptor   count\n",
       "0  Catch Basin Clogged/Flooding (Use Comments) (SC)  114680\n",
       "1                              Street Flooding (SJ)   39512\n",
       "2                              Flood Light Lamp Out    6517\n",
       "3                             Highway Flooding (SH)    3156\n",
       "4                          Flood Light Lamp Cycling    2595\n",
       "5                                Flooding on Street     673\n",
       "6                               Ready NY - Flooding     271\n",
       "7                       Flood Light Lamp Dayburning     228\n",
       "8                          Flood Light Lamp Missing     212\n",
       "9                              Flood Light Lamp Dim     185"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=1000\n",
    ")\n",
    "\n",
    "# SoQL query string below: select descriptor and count grouped by descriptor,\n",
    "# where the word \"flood\" is in descriptor, sort count in descending order and\n",
    "# limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    descriptor, \n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    LOWER(descriptor) LIKE '%flood%'\n",
    "GROUP BY \n",
    "    descriptor\n",
    "ORDER BY \n",
    "    count DESC\n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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": [
      "shape of data: (1000, 4)\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>descriptor</th>\n",
       "      <th>borough</th>\n",
       "      <th>in_bronx</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60937647</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60937648</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60937649</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60938666</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60929521</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>60924082</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>60916417</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>60905806</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>60908898</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>60908899</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_key            descriptor    borough  in_bronx\n",
       "0   60937647  Street Flooding (SJ)  MANHATTAN     False\n",
       "1   60937648  Street Flooding (SJ)   BROOKLYN     False\n",
       "2   60937649  Street Flooding (SJ)   BROOKLYN     False\n",
       "3   60938666  Street Flooding (SJ)     QUEENS     False\n",
       "4   60929521  Street Flooding (SJ)     QUEENS     False\n",
       "5   60924082  Street Flooding (SJ)  MANHATTAN     False\n",
       "6   60916417  Street Flooding (SJ)     QUEENS     False\n",
       "7   60905806  Street Flooding (SJ)  MANHATTAN     False\n",
       "8   60908898  Street Flooding (SJ)  MANHATTAN     False\n",
       "9   60908899  Street Flooding (SJ)     QUEENS     False"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: elect the descriptor, unique_key, borough, and case(borough != 'BRONX'),\n",
    "# where the descriptor is Street Flooding (SJ), limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    unique_key,\n",
    "    descriptor,\n",
    "    borough,\n",
    "    case(borough != 'BRONX', False, True, True) AS in_bronx\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier, \n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "borough        in_bronx\n",
       "BRONX          True         57\n",
       "BROOKLYN       False       251\n",
       "MANHATTAN      False        73\n",
       "QUEENS         False       429\n",
       "STATEN ISLAND  False       190\n",
       "Name: unique_key, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sanity check\n",
    "(results_df\n",
    " .groupby(by=['borough', 'in_bronx'])['unique_key']\n",
    " .count()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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": [
      "shape of data: (15, 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>year</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00.000</td>\n",
       "      <td>4140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-01T00:00:00.000</td>\n",
       "      <td>3702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-01-01T00:00:00.000</td>\n",
       "      <td>3485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-01-01T00:00:00.000</td>\n",
       "      <td>3434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-01T00:00:00.000</td>\n",
       "      <td>3078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2011-01-01T00:00:00.000</td>\n",
       "      <td>2644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017-01-01T00:00:00.000</td>\n",
       "      <td>2532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2010-01-01T00:00:00.000</td>\n",
       "      <td>2531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2014-01-01T00:00:00.000</td>\n",
       "      <td>2498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016-01-01T00:00:00.000</td>\n",
       "      <td>2262</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      year count\n",
       "0  2018-01-01T00:00:00.000  4140\n",
       "1  2021-01-01T00:00:00.000  3702\n",
       "2  2023-01-01T00:00:00.000  3485\n",
       "3  2019-01-01T00:00:00.000  3434\n",
       "4  2022-01-01T00:00:00.000  3078\n",
       "5  2011-01-01T00:00:00.000  2644\n",
       "6  2017-01-01T00:00:00.000  2532\n",
       "7  2010-01-01T00:00:00.000  2531\n",
       "8  2014-01-01T00:00:00.000  2498\n",
       "9  2016-01-01T00:00:00.000  2262"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the year truncated and the count columns grouped by year,\n",
    "# where the descriptor is Street Flooding (SJ), sort the count in descending order and\n",
    "# limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    date_trunc_y(created_date) AS year,\n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    year\n",
    "ORDER BY \n",
    "    count DESC    \n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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": [
      "shape of data: (172, 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>year_month</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-09-01T00:00:00.000</td>\n",
       "      <td>1035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-09-01T00:00:00.000</td>\n",
       "      <td>932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-11-01T00:00:00.000</td>\n",
       "      <td>710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-08-01T00:00:00.000</td>\n",
       "      <td>595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-03-01T00:00:00.000</td>\n",
       "      <td>576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-12-01T00:00:00.000</td>\n",
       "      <td>530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017-05-01T00:00:00.000</td>\n",
       "      <td>524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2021-07-01T00:00:00.000</td>\n",
       "      <td>499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2011-08-01T00:00:00.000</td>\n",
       "      <td>497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016-02-01T00:00:00.000</td>\n",
       "      <td>490</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                year_month count\n",
       "0  2021-09-01T00:00:00.000  1035\n",
       "1  2023-09-01T00:00:00.000   932\n",
       "2  2018-11-01T00:00:00.000   710\n",
       "3  2021-08-01T00:00:00.000   595\n",
       "4  2024-03-01T00:00:00.000   576\n",
       "5  2022-12-01T00:00:00.000   530\n",
       "6  2017-05-01T00:00:00.000   524\n",
       "7  2021-07-01T00:00:00.000   499\n",
       "8  2011-08-01T00:00:00.000   497\n",
       "9  2016-02-01T00:00:00.000   490"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the year month truncated and the count columns grouped by year month,\n",
    "# where the descriptor is Street Flooding (SJ), sort the count in descending order and\n",
    "# limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    date_trunc_ym(created_date) AS year_month,\n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    year_month\n",
    "ORDER BY \n",
    "    count DESC    \n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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": [
      "shape of data: (1000, 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>year_month_day</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-09-29T00:00:00.000</td>\n",
       "      <td>623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-09-02T00:00:00.000</td>\n",
       "      <td>350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-09-01T00:00:00.000</td>\n",
       "      <td>344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-12-23T00:00:00.000</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-05-05T00:00:00.000</td>\n",
       "      <td>247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2014-12-09T00:00:00.000</td>\n",
       "      <td>226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2014-04-30T00:00:00.000</td>\n",
       "      <td>189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2021-10-26T00:00:00.000</td>\n",
       "      <td>177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-04-16T00:00:00.000</td>\n",
       "      <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-05-08T00:00:00.000</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            year_month_day count\n",
       "0  2023-09-29T00:00:00.000   623\n",
       "1  2021-09-02T00:00:00.000   350\n",
       "2  2021-09-01T00:00:00.000   344\n",
       "3  2022-12-23T00:00:00.000   308\n",
       "4  2017-05-05T00:00:00.000   247\n",
       "5  2014-12-09T00:00:00.000   226\n",
       "6  2014-04-30T00:00:00.000   189\n",
       "7  2021-10-26T00:00:00.000   177\n",
       "8  2018-04-16T00:00:00.000   163\n",
       "9  2013-05-08T00:00:00.000   162"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the year month day and the count columns grouped by year month day,\n",
    "# where the descriptor is Street Flooding (SJ), sort the count in descending order and\n",
    "# limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    date_trunc_ymd(created_date) AS year_month_day,\n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    year_month_day\n",
    "ORDER BY \n",
    "    count DESC    \n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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": [
      "shape of data: (15, 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>year</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018</td>\n",
       "      <td>4140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021</td>\n",
       "      <td>3702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023</td>\n",
       "      <td>3485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019</td>\n",
       "      <td>3434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022</td>\n",
       "      <td>3078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2011</td>\n",
       "      <td>2644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017</td>\n",
       "      <td>2532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2010</td>\n",
       "      <td>2531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2014</td>\n",
       "      <td>2498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016</td>\n",
       "      <td>2262</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year count\n",
       "0  2018  4140\n",
       "1  2021  3702\n",
       "2  2023  3485\n",
       "3  2019  3434\n",
       "4  2022  3078\n",
       "5  2011  2644\n",
       "6  2017  2532\n",
       "7  2010  2531\n",
       "8  2014  2498\n",
       "9  2016  2262"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the year and the count columns grouped by year,\n",
    "# where the descriptor is Street Flooding (SJ), sort the count in descending order and\n",
    "# limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    date_extract_y(created_date) AS year,\n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    year\n",
    "ORDER BY \n",
    "    count DESC    \n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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": [
      "shape of data: (12, 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>month</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>4102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>4081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>3710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>3631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>3333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>3235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3</td>\n",
       "      <td>3203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4</td>\n",
       "      <td>3076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10</td>\n",
       "      <td>2974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>2907</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  month count\n",
       "0     9  4102\n",
       "1     5  4081\n",
       "2     7  3710\n",
       "3     8  3631\n",
       "4    12  3333\n",
       "5     6  3235\n",
       "6     3  3203\n",
       "7     4  3076\n",
       "8    10  2974\n",
       "9     2  2907"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the month and the count columns grouped by month,\n",
    "# where the descriptor is Street Flooding (SJ), sort the count in descending order and\n",
    "# limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    date_extract_m(created_date) AS month,\n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    month\n",
    "ORDER BY \n",
    "    count DESC    \n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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": [
      "shape of data: (31, 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>day</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29</td>\n",
       "      <td>1930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23</td>\n",
       "      <td>1689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30</td>\n",
       "      <td>1620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>13</td>\n",
       "      <td>1540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9</td>\n",
       "      <td>1501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>25</td>\n",
       "      <td>1437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>18</td>\n",
       "      <td>1428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>16</td>\n",
       "      <td>1407</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  day count\n",
       "0  29  1930\n",
       "1   2  1696\n",
       "2   1  1694\n",
       "3  23  1689\n",
       "4  30  1620\n",
       "5  13  1540\n",
       "6   9  1501\n",
       "7  25  1437\n",
       "8  18  1428\n",
       "9  16  1407"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the day and the count day columns grouped by day,\n",
    "# where the descriptor is Street Flooding (SJ), sort the count in descending order and\n",
    "# limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    date_extract_d(created_date) AS day,\n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    day\n",
    "ORDER BY \n",
    "    count DESC    \n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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": [
      "shape of data: (53, 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>week_of_year</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39</td>\n",
       "      <td>1405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18</td>\n",
       "      <td>1337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35</td>\n",
       "      <td>1187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33</td>\n",
       "      <td>1109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30</td>\n",
       "      <td>1077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>51</td>\n",
       "      <td>1052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>50</td>\n",
       "      <td>899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>32</td>\n",
       "      <td>889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23</td>\n",
       "      <td>880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>875</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_of_year count\n",
       "0           39  1405\n",
       "1           18  1337\n",
       "2           35  1187\n",
       "3           33  1109\n",
       "4           30  1077\n",
       "5           51  1052\n",
       "6           50   899\n",
       "7           32   889\n",
       "8           23   880\n",
       "9           10   875"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the week of year and the count columns grouped by week of year,\n",
    "# where the descriptor is Street Flooding (SJ), sort the count in descending order and\n",
    "# limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    date_extract_woy(created_date) AS week_of_year,\n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    week_of_year\n",
    "ORDER BY \n",
    "    count DESC    \n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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": [
      "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>day_of_week</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>7086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>6663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>6523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>6278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>6051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>3461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>3450</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  day_of_week count\n",
       "0           5  7086\n",
       "1           2  6663\n",
       "2           1  6523\n",
       "3           3  6278\n",
       "4           4  6051\n",
       "5           0  3461\n",
       "6           6  3450"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the day of week and the count columns grouped by day of week,\n",
    "# where the descriptor is Street Flooding (SJ), sort the count in descending order and\n",
    "# limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    date_extract_dow(created_date) AS day_of_week,\n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    day_of_week\n",
    "ORDER BY \n",
    "    count DESC    \n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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": [
      "shape of data: (24, 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>hour</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>3364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>3336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>3300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>3006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15</td>\n",
       "      <td>2895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>14</td>\n",
       "      <td>2833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>13</td>\n",
       "      <td>2771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16</td>\n",
       "      <td>2771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>2518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>17</td>\n",
       "      <td>2197</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  hour count\n",
       "0   11  3364\n",
       "1    9  3336\n",
       "2   10  3300\n",
       "3   12  3006\n",
       "4   15  2895\n",
       "5   14  2833\n",
       "6   13  2771\n",
       "7   16  2771\n",
       "8    8  2518\n",
       "9   17  2197"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the hour and the count columns grouped by hour,\n",
    "# where the descriptor is Street Flooding (SJ), and sort the count in descending order and\n",
    "# limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    date_extract_hh(created_date) AS hour,\n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    hour\n",
    "ORDER BY \n",
    "    count DESC    \n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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": [
      "shape of data: (60, 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>minute</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>44</td>\n",
       "      <td>913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29</td>\n",
       "      <td>897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35</td>\n",
       "      <td>890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23</td>\n",
       "      <td>884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32</td>\n",
       "      <td>880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>38</td>\n",
       "      <td>876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>56</td>\n",
       "      <td>874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>47</td>\n",
       "      <td>870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14</td>\n",
       "      <td>863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>856</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  minute count\n",
       "0     44   913\n",
       "1     29   897\n",
       "2     35   890\n",
       "3     23   884\n",
       "4     32   880\n",
       "5     38   876\n",
       "6     56   874\n",
       "7     47   870\n",
       "8     14   863\n",
       "9      2   856"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# SoQL query string below: select the minute and the count columns grouped by minute,\n",
    "# where the descriptor is Street Flooding (SJ), sort the count in descending order and\n",
    "# limit our records to 1,000\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    date_extract_mm(created_date) AS minute,\n",
    "    count(unique_key) AS count\n",
    "WHERE \n",
    "    descriptor == 'Street Flooding (SJ)'\n",
    "GROUP BY \n",
    "    minute\n",
    "ORDER BY \n",
    "    count DESC    \n",
    "LIMIT \n",
    "    1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(\n",
    "    socrata_dataset_identifier,\n",
    "    query=query\n",
    ")\n",
    "client.close()\n",
    "\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print('shape of data: {}'.format(results_df.shape))\n",
    "results_df.head(10)"
   ]
  },
  {
   "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
}