socrata-query-language.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The Socrata Query Language (SoQL)\n",
    "## Socrata Open Data API (SODA) Tutorial Using NYC Open Data\n",
    "Author: Mark Bauer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Table of Contents\n",
    "=================\n",
    "\n",
    "   1. Introduction\n",
    "   2. The Socrata Open Data API\n",
    "       * 2.1 Using Socrata Open Data API (SODA)\n",
    "       * 2.2 Using Sodapy\n",
    "       * 2.3 Socrata Query Language (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 how to interact with the [Socrata Open Data API (SODA)](https://dev.socrata.com/) and introduces the [Socrata Query Language (SoQL)](https://dev.socrata.com/docs/queries/), which is used for querying data from Socrata-powered platforms. We’ll use SoQL to fetch data from NYC Open Data. Additionally, this notebook introduces [Sodapy](https://github.com/xmunoz/sodapy), a Python client for SODA, and shows how to use Sodapy alongside SoQL to extract and work with data.\n",
    "\n",
    "I'll demonstrate how to work with the Socrata API Endpoint as well as Sodapy, but my preferred method of retrieving data is with Sodapy. However, please note that the Sodapy project is now archived on GitHub."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Socrata Open Data\n",
    "\n",
    "## 2.1 Socrata Open Data API (SODA)\n",
    "\n",
    "The Socrata Open Data API (SODA) provides a programmatic way to access datasets, not only from NYC Open Data but from a wide range of sources globally. In my experience, it's one of the most efficient and user-friendly methods for accessing open data.\n",
    "\n",
    "For more information, I encourage you to visit the official [Socrata Open Data API](https://dev.socrata.com/) website. You'll find a wealth of helpful resources there, and I highly recommend reviewing the [API Docs](https://dev.socrata.com/docs/endpoints.html) to deepen your understanding of how to query data effectively and efficiently. This guide is intended to complement the official documentation and help you get started quickly.\n",
    "\n",
    "![dev socrata](images/dev-socrata.png)\n",
    "\n",
    "Source: https://dev.socrata.com/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 Sodapy\n",
    "\n",
    "In addition to accessing datasets from NYC Open Data via the Socrata API Endpoint, you can also use Sodapy, a Python client. You can find more information about Sodapy in the [official documentation](https://github.com/xmunoz/sodapy) on GitHub. For an introduction to basic workflows, you can also refer to my tutorial [sodapy-basics.ipynb](https://github.com/mebauer/sodapy-tutorial-nyc-opendata/blob/main/socrata-api-basics.ipynb).\n",
    "\n",
    "### Attention\n",
    "When querying all records, be sure to set the `limit` parameter to a value large enough to exceed the total number of records in your dataset. If the `limit` value is set to exactly the same number as the records returned, you likely haven’t retrieved all the data. To avoid this, choose a `limit` that is larger than the dataset’s total size."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Please note** that the Sodapy project is publicy archived on GitHub and is read-only.\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 dataset from NYC Open Data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![nyc-311-api-docs](images/nyc-311-api-docs.png)  \n",
    "\n",
    "Source: https://dev.socrata.com/foundry/data.cityofnewyork.us/erm2-nwe9"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3 Socrata Query Language (SoQL)\n",
    "If you're familiar with SQL, you'll feel right at home with SoQL. This notebook demonstrates how to use SoQL to fetch data from NYC Open Data.\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",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.ticker import FuncFormatter\n",
    "import seaborn as sns\n",
    "import urllib.parse\n",
    "\n",
    "# sodapy\n",
    "from sodapy import Socrata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Python implementation: CPython\n",
      "Python version       : 3.11.0\n",
      "IPython version      : 8.6.0\n",
      "\n",
      "pandas    : 1.5.1\n",
      "numpy     : 1.23.4\n",
      "matplotlib: 3.6.2\n",
      "seaborn   : 0.12.1\n",
      "sodapy    : 2.2.0\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# documention for installing watermark: https://github.com/rasbt/watermark, perform for reproducibility\n",
    "%reload_ext watermark\n",
    "%watermark -t -d -v -p pandas,numpy,matplotlib,seaborn,sodapy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Retrieving Data Directly from the Socrata Open Data API Endpoint\n",
    "In this section, we’ll walk through how to retrieve data directly from the Socrata Open Data API (SODA) by constructing URLs with specific parameters. While I typically use Sodapy, the Python client for Socrata, when possible, it’s useful to understand how to work with the API directly.\n",
    "\n",
    "Note:  \n",
    "`WARNING:root:Requests made without an app_token will be subject to strict throttling limits.`  \n",
    "\n",
    "To avoid these limits, it's recommended to use an app token when making API requests."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Preview URL: https://data.cityofnewyork.us/resource/bs59-f3nu.csv?$limit=50000\n",
      "(14361, 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>the_geom</th>\n",
       "      <th>asset_id</th>\n",
       "      <th>gi_id</th>\n",
       "      <th>dep_contra</th>\n",
       "      <th>dep_cont_1</th>\n",
       "      <th>row_onsite</th>\n",
       "      <th>project_na</th>\n",
       "      <th>asset_type</th>\n",
       "      <th>status</th>\n",
       "      <th>asset_x_co</th>\n",
       "      <th>...</th>\n",
       "      <th>asset_leng</th>\n",
       "      <th>asset_widt</th>\n",
       "      <th>asset_area</th>\n",
       "      <th>gi_feature</th>\n",
       "      <th>tree_latin</th>\n",
       "      <th>tree_commo</th>\n",
       "      <th>constructi</th>\n",
       "      <th>construc_1</th>\n",
       "      <th>program_ar</th>\n",
       "      <th>status_gro</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>POINT (-73.81167623024226 40.69138622900597)</td>\n",
       "      <td>94002.0</td>\n",
       "      <td>1A</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWB</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.036475e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>17.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>Chionanthus retusus</td>\n",
       "      <td>Chinese Fringetree</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>POINT (-73.81228577606385 40.69238458134393)</td>\n",
       "      <td>94012.0</td>\n",
       "      <td>GS6A</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWGS</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.036305e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>45.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No Tree</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>POINT (-73.8122344420821 40.69312522070409)</td>\n",
       "      <td>94017.0</td>\n",
       "      <td>GS8C</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWGS</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.036319e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No Tree</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>POINT (-73.8120597400255 40.6931738947353)</td>\n",
       "      <td>94019.0</td>\n",
       "      <td>GS8E</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWGS</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.036368e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No Tree</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>POINT (-73.81310191327061 40.69279332424906)</td>\n",
       "      <td>94021.0</td>\n",
       "      <td>10A</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWB</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.036079e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>Quercus palustris</td>\n",
       "      <td>Pin Oak</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       the_geom  asset_id gi_id dep_contra  \\\n",
       "0  POINT (-73.81167623024226 40.69138622900597)   94002.0    1A  GQJA03-02   \n",
       "1  POINT (-73.81228577606385 40.69238458134393)   94012.0  GS6A  GQJA03-02   \n",
       "2   POINT (-73.8122344420821 40.69312522070409)   94017.0  GS8C  GQJA03-02   \n",
       "3    POINT (-73.8120597400255 40.6931738947353)   94019.0  GS8E  GQJA03-02   \n",
       "4  POINT (-73.81310191327061 40.69279332424906)   94021.0   10A  GQJA03-02   \n",
       "\n",
       "  dep_cont_1 row_onsite           project_na asset_type  \\\n",
       "0          2        ROW  DDC JAM-003 Phase 2       ROWB   \n",
       "1          2        ROW  DDC JAM-003 Phase 2      ROWGS   \n",
       "2          2        ROW  DDC JAM-003 Phase 2      ROWGS   \n",
       "3          2        ROW  DDC JAM-003 Phase 2      ROWGS   \n",
       "4          2        ROW  DDC JAM-003 Phase 2       ROWB   \n",
       "\n",
       "                           status    asset_x_co  ...  asset_leng asset_widt  \\\n",
       "0  Constructed (Full Maintenance)  1.036475e+06  ...        17.0        5.0   \n",
       "1  Constructed (Full Maintenance)  1.036305e+06  ...        13.0        3.5   \n",
       "2  Constructed (Full Maintenance)  1.036319e+06  ...        20.0        3.5   \n",
       "3  Constructed (Full Maintenance)  1.036368e+06  ...        20.0        3.5   \n",
       "4  Constructed (Full Maintenance)  1.036079e+06  ...        13.0        4.0   \n",
       "\n",
       "  asset_area gi_feature           tree_latin          tree_commo  constructi  \\\n",
       "0       85.0   Standard  Chionanthus retusus  Chinese Fringetree   GCJA03-2A   \n",
       "1       45.5        NaN              No Tree                 NaN   GCJA03-2A   \n",
       "2       70.0        NaN              No Tree                 NaN   GCJA03-2A   \n",
       "3       70.0        NaN              No Tree                 NaN   GCJA03-2A   \n",
       "4       52.0   Standard    Quercus palustris             Pin Oak   GCJA03-2A   \n",
       "\n",
       "   construc_1          program_ar   status_gro  \n",
       "0   Package-1  Right of Way (ROW)  Constructed  \n",
       "1   Package-1  Right of Way (ROW)  Constructed  \n",
       "2   Package-1  Right of Way (ROW)  Constructed  \n",
       "3   Package-1  Right of Way (ROW)  Constructed  \n",
       "4   Package-1  Right of Way (ROW)  Constructed  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# define the source domain for accessing NYC Open Data via Socrata API\n",
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "\n",
    "# define the dataset identifier for the DEP Green Infrastructure dataset on Socrata\n",
    "socrata_dataset_identifier = 'bs59-f3nu'\n",
    "\n",
    "# set the row limit for the query (in this case, fetch full dataset with a high value)\n",
    "limit = 50_000\n",
    "\n",
    "# construct the full URL to access the dataset, specifying the limit parameter\n",
    "url = f'https://{socrata_domain}/resource/{socrata_dataset_identifier}.csv?$limit={limit}'\n",
    "\n",
    "# preview the constructed URL to ensure it's correct before making the request\n",
    "print(f'Preview URL: {url}')\n",
    "\n",
    "# load the dataset directly from the constructed URL into a pandas DataFrame\n",
    "df = pd.read_csv(url)\n",
    "\n",
    "# perform basic sanity checks on the DataFrame\n",
    "# print the shape of the DataFrame (rows, columns)\n",
    "print(df.shape)\n",
    "\n",
    "# display the first few rows of the DataFrame to verify the data\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14361 entries, 0 to 14360\n",
      "Data columns (total 30 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   the_geom    14361 non-null  object \n",
      " 1   asset_id    14361 non-null  float64\n",
      " 2   gi_id       14361 non-null  object \n",
      " 3   dep_contra  14361 non-null  object \n",
      " 4   dep_cont_1  14361 non-null  object \n",
      " 5   row_onsite  14361 non-null  object \n",
      " 6   project_na  14361 non-null  object \n",
      " 7   asset_type  14361 non-null  object \n",
      " 8   status      14361 non-null  object \n",
      " 9   asset_x_co  14361 non-null  float64\n",
      " 10  asset_y_co  14361 non-null  float64\n",
      " 11  borough     14361 non-null  object \n",
      " 12  sewer_type  14361 non-null  object \n",
      " 13  outfall     14361 non-null  object \n",
      " 14  nyc_waters  14361 non-null  object \n",
      " 15  bbl         14361 non-null  float64\n",
      " 16  secondary_  14361 non-null  float64\n",
      " 17  community_  14361 non-null  float64\n",
      " 18  city_counc  14361 non-null  float64\n",
      " 19  assembly_d  14361 non-null  float64\n",
      " 20  asset_leng  14361 non-null  float64\n",
      " 21  asset_widt  14361 non-null  float64\n",
      " 22  asset_area  14361 non-null  float64\n",
      " 23  gi_feature  12090 non-null  object \n",
      " 24  tree_latin  12464 non-null  object \n",
      " 25  tree_commo  3358 non-null   object \n",
      " 26  constructi  12955 non-null  object \n",
      " 27  construc_1  12955 non-null  object \n",
      " 28  program_ar  14361 non-null  object \n",
      " 29  status_gro  14361 non-null  object \n",
      "dtypes: float64(11), object(19)\n",
      "memory usage: 3.3+ MB\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## WHERE Statements\n",
    "Notice how to use WHERE statements to construct a URL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Preview URL: https://data.cityofnewyork.us/resource/bs59-f3nu.csv?$where=asset_area%20>=%20%27200%27&$limit=50000\n",
      "(1428, 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>the_geom</th>\n",
       "      <th>asset_id</th>\n",
       "      <th>gi_id</th>\n",
       "      <th>dep_contra</th>\n",
       "      <th>dep_cont_1</th>\n",
       "      <th>row_onsite</th>\n",
       "      <th>project_na</th>\n",
       "      <th>asset_type</th>\n",
       "      <th>status</th>\n",
       "      <th>asset_x_co</th>\n",
       "      <th>...</th>\n",
       "      <th>asset_leng</th>\n",
       "      <th>asset_widt</th>\n",
       "      <th>asset_area</th>\n",
       "      <th>gi_feature</th>\n",
       "      <th>tree_latin</th>\n",
       "      <th>tree_commo</th>\n",
       "      <th>constructi</th>\n",
       "      <th>construc_1</th>\n",
       "      <th>program_ar</th>\n",
       "      <th>status_gro</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>POINT (-73.93538660699062 40.69408170243197)</td>\n",
       "      <td>138299.0</td>\n",
       "      <td>ROO2-05SRa</td>\n",
       "      <td>GKNC15-02-OS9</td>\n",
       "      <td>2</td>\n",
       "      <td>Onsite</td>\n",
       "      <td>Roosevelt II Houses</td>\n",
       "      <td>Subsurface Storage</td>\n",
       "      <td>Constructed</td>\n",
       "      <td>1.002168e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Public Onsite</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>POINT (-73.92090216658133 40.66345958422758)</td>\n",
       "      <td>158143.0</td>\n",
       "      <td>UNST-1</td>\n",
       "      <td>BEPA-GR-1</td>\n",
       "      <td>GR</td>\n",
       "      <td>ROW</td>\n",
       "      <td>Nitrogen Consent Order SW Pilot EBP</td>\n",
       "      <td>ROWB</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.006194e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>40.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>Quercus bicolor</td>\n",
       "      <td>Swamp White Oak</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>POINT (-73.91994796986424 40.670230728097)</td>\n",
       "      <td>158144.0</td>\n",
       "      <td>HOWAV-1</td>\n",
       "      <td>BEPA-GR-1</td>\n",
       "      <td>GR</td>\n",
       "      <td>ROW</td>\n",
       "      <td>Nitrogen Consent Order SW Pilot EBP</td>\n",
       "      <td>ROWB</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.006457e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>40.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>Type B - Stormwater Inlet</td>\n",
       "      <td>Liquidambar styraciflua</td>\n",
       "      <td>Sweetgum</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>POINT (-73.91198377084235 40.6728839138885)</td>\n",
       "      <td>158145.0</td>\n",
       "      <td>EPKY-1</td>\n",
       "      <td>BEPA-GR-1</td>\n",
       "      <td>GR</td>\n",
       "      <td>ROW</td>\n",
       "      <td>Nitrogen Consent Order SW Pilot EBP</td>\n",
       "      <td>ROWB</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.008665e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>40.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>Liquidambar styraciflua</td>\n",
       "      <td>Sweetgum</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>POINT (-73.75503439377286 40.71276892893676)</td>\n",
       "      <td>158154.0</td>\n",
       "      <td>99AV-1</td>\n",
       "      <td>BEPA-GR-1</td>\n",
       "      <td>GR</td>\n",
       "      <td>ROW</td>\n",
       "      <td>Nitrogen Consent Order SW Pilot EBP</td>\n",
       "      <td>ROWB</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.052161e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>40.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>Type B - Stormwater Inlet</td>\n",
       "      <td>Nyssa sylvatica</td>\n",
       "      <td>Blackgum</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       the_geom  asset_id       gi_id  \\\n",
       "0  POINT (-73.93538660699062 40.69408170243197)  138299.0  ROO2-05SRa   \n",
       "1  POINT (-73.92090216658133 40.66345958422758)  158143.0      UNST-1   \n",
       "2    POINT (-73.91994796986424 40.670230728097)  158144.0     HOWAV-1   \n",
       "3   POINT (-73.91198377084235 40.6728839138885)  158145.0      EPKY-1   \n",
       "4  POINT (-73.75503439377286 40.71276892893676)  158154.0      99AV-1   \n",
       "\n",
       "      dep_contra dep_cont_1 row_onsite                           project_na  \\\n",
       "0  GKNC15-02-OS9          2     Onsite                  Roosevelt II Houses   \n",
       "1      BEPA-GR-1         GR        ROW  Nitrogen Consent Order SW Pilot EBP   \n",
       "2      BEPA-GR-1         GR        ROW  Nitrogen Consent Order SW Pilot EBP   \n",
       "3      BEPA-GR-1         GR        ROW  Nitrogen Consent Order SW Pilot EBP   \n",
       "4      BEPA-GR-1         GR        ROW  Nitrogen Consent Order SW Pilot EBP   \n",
       "\n",
       "           asset_type                          status    asset_x_co  ...  \\\n",
       "0  Subsurface Storage                     Constructed  1.002168e+06  ...   \n",
       "1                ROWB  Constructed (Full Maintenance)  1.006194e+06  ...   \n",
       "2                ROWB  Constructed (Full Maintenance)  1.006457e+06  ...   \n",
       "3                ROWB  Constructed (Full Maintenance)  1.008665e+06  ...   \n",
       "4                ROWB  Constructed (Full Maintenance)  1.052161e+06  ...   \n",
       "\n",
       "   asset_leng asset_widt asset_area                 gi_feature  \\\n",
       "0         0.0        0.0      200.0                        NaN   \n",
       "1        40.0        5.0      200.0                   Standard   \n",
       "2        40.0        5.0      200.0  Type B - Stormwater Inlet   \n",
       "3        40.0        5.0      200.0                   Standard   \n",
       "4        40.0        5.0      200.0  Type B - Stormwater Inlet   \n",
       "\n",
       "                tree_latin       tree_commo  constructi  construc_1  \\\n",
       "0                      NaN              NaN         NaN         NaN   \n",
       "1          Quercus bicolor  Swamp White Oak         NaN         NaN   \n",
       "2  Liquidambar styraciflua         Sweetgum         NaN         NaN   \n",
       "3  Liquidambar styraciflua         Sweetgum         NaN         NaN   \n",
       "4          Nyssa sylvatica         Blackgum         NaN         NaN   \n",
       "\n",
       "           program_ar   status_gro  \n",
       "0       Public Onsite  Constructed  \n",
       "1  Right of Way (ROW)  Constructed  \n",
       "2  Right of Way (ROW)  Constructed  \n",
       "3  Right of Way (ROW)  Constructed  \n",
       "4  Right of Way (ROW)  Constructed  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# define the dataset identifier for the DEP Green Infrastructure dataset on Socrata\n",
    "socrata_dataset_identifier = 'bs59-f3nu'\n",
    "\n",
    "# WHERE statements\n",
    "area = '200' # units sq ft\n",
    "column = 'asset_area'\n",
    "limit = 50000\n",
    "\n",
    "# construct url\n",
    "url = f'https://data.cityofnewyork.us/resource/{socrata_dataset_identifier}.csv?$where={column}%20>=%20%27{area}%27&$limit={limit}'\n",
    "\n",
    "print(f'Preview URL: {url}')\n",
    "\n",
    "# read data into pandas DataFrame\n",
    "df = pd.read_csv(url)\n",
    "\n",
    "# sanity check\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      1428.000000\n",
       "mean       4124.047766\n",
       "std       17884.673299\n",
       "min         200.000000\n",
       "25%         556.000000\n",
       "50%        1175.165000\n",
       "75%        2680.000000\n",
       "max      435000.000000\n",
       "Name: asset_area, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sanity check\n",
    "df['asset_area'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1428 entries, 0 to 1427\n",
      "Data columns (total 30 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   the_geom    1428 non-null   object \n",
      " 1   asset_id    1428 non-null   float64\n",
      " 2   gi_id       1428 non-null   object \n",
      " 3   dep_contra  1428 non-null   object \n",
      " 4   dep_cont_1  1428 non-null   object \n",
      " 5   row_onsite  1428 non-null   object \n",
      " 6   project_na  1428 non-null   object \n",
      " 7   asset_type  1428 non-null   object \n",
      " 8   status      1428 non-null   object \n",
      " 9   asset_x_co  1428 non-null   float64\n",
      " 10  asset_y_co  1428 non-null   float64\n",
      " 11  borough     1428 non-null   object \n",
      " 12  sewer_type  1428 non-null   object \n",
      " 13  outfall     1428 non-null   object \n",
      " 14  nyc_waters  1428 non-null   object \n",
      " 15  bbl         1428 non-null   float64\n",
      " 16  secondary_  1428 non-null   float64\n",
      " 17  community_  1428 non-null   float64\n",
      " 18  city_counc  1428 non-null   float64\n",
      " 19  assembly_d  1428 non-null   float64\n",
      " 20  asset_leng  1428 non-null   float64\n",
      " 21  asset_widt  1428 non-null   float64\n",
      " 22  asset_area  1428 non-null   float64\n",
      " 23  gi_feature  115 non-null    object \n",
      " 24  tree_latin  134 non-null    object \n",
      " 25  tree_commo  65 non-null     object \n",
      " 26  constructi  369 non-null    object \n",
      " 27  construc_1  369 non-null    object \n",
      " 28  program_ar  1428 non-null   object \n",
      " 29  status_gro  1428 non-null   object \n",
      "dtypes: float64(11), object(19)\n",
      "memory usage: 334.8+ KB\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## QUERY Parameters\n",
    "Notice how to use the Socrata API QUERY parameter to construct a URL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Preview URL: https://data.cityofnewyork.us/resource/bs59-f3nu.csv?$query=SELECT%20*%20LIMIT%2050000\n",
      "(14361, 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>the_geom</th>\n",
       "      <th>asset_id</th>\n",
       "      <th>gi_id</th>\n",
       "      <th>dep_contra</th>\n",
       "      <th>dep_cont_1</th>\n",
       "      <th>row_onsite</th>\n",
       "      <th>project_na</th>\n",
       "      <th>asset_type</th>\n",
       "      <th>status</th>\n",
       "      <th>asset_x_co</th>\n",
       "      <th>...</th>\n",
       "      <th>asset_leng</th>\n",
       "      <th>asset_widt</th>\n",
       "      <th>asset_area</th>\n",
       "      <th>gi_feature</th>\n",
       "      <th>tree_latin</th>\n",
       "      <th>tree_commo</th>\n",
       "      <th>constructi</th>\n",
       "      <th>construc_1</th>\n",
       "      <th>program_ar</th>\n",
       "      <th>status_gro</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>POINT (-73.81167623024226 40.69138622900597)</td>\n",
       "      <td>94002.0</td>\n",
       "      <td>1A</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWB</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.036475e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>17.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>Chionanthus retusus</td>\n",
       "      <td>Chinese Fringetree</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>POINT (-73.81228577606385 40.69238458134393)</td>\n",
       "      <td>94012.0</td>\n",
       "      <td>GS6A</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWGS</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.036305e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>45.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No Tree</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>POINT (-73.8122344420821 40.69312522070409)</td>\n",
       "      <td>94017.0</td>\n",
       "      <td>GS8C</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWGS</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.036319e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No Tree</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>POINT (-73.8120597400255 40.6931738947353)</td>\n",
       "      <td>94019.0</td>\n",
       "      <td>GS8E</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWGS</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.036368e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No Tree</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>POINT (-73.81310191327061 40.69279332424906)</td>\n",
       "      <td>94021.0</td>\n",
       "      <td>10A</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWB</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1.036079e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>Quercus palustris</td>\n",
       "      <td>Pin Oak</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       the_geom  asset_id gi_id dep_contra  \\\n",
       "0  POINT (-73.81167623024226 40.69138622900597)   94002.0    1A  GQJA03-02   \n",
       "1  POINT (-73.81228577606385 40.69238458134393)   94012.0  GS6A  GQJA03-02   \n",
       "2   POINT (-73.8122344420821 40.69312522070409)   94017.0  GS8C  GQJA03-02   \n",
       "3    POINT (-73.8120597400255 40.6931738947353)   94019.0  GS8E  GQJA03-02   \n",
       "4  POINT (-73.81310191327061 40.69279332424906)   94021.0   10A  GQJA03-02   \n",
       "\n",
       "  dep_cont_1 row_onsite           project_na asset_type  \\\n",
       "0          2        ROW  DDC JAM-003 Phase 2       ROWB   \n",
       "1          2        ROW  DDC JAM-003 Phase 2      ROWGS   \n",
       "2          2        ROW  DDC JAM-003 Phase 2      ROWGS   \n",
       "3          2        ROW  DDC JAM-003 Phase 2      ROWGS   \n",
       "4          2        ROW  DDC JAM-003 Phase 2       ROWB   \n",
       "\n",
       "                           status    asset_x_co  ...  asset_leng asset_widt  \\\n",
       "0  Constructed (Full Maintenance)  1.036475e+06  ...        17.0        5.0   \n",
       "1  Constructed (Full Maintenance)  1.036305e+06  ...        13.0        3.5   \n",
       "2  Constructed (Full Maintenance)  1.036319e+06  ...        20.0        3.5   \n",
       "3  Constructed (Full Maintenance)  1.036368e+06  ...        20.0        3.5   \n",
       "4  Constructed (Full Maintenance)  1.036079e+06  ...        13.0        4.0   \n",
       "\n",
       "  asset_area gi_feature           tree_latin          tree_commo  constructi  \\\n",
       "0       85.0   Standard  Chionanthus retusus  Chinese Fringetree   GCJA03-2A   \n",
       "1       45.5        NaN              No Tree                 NaN   GCJA03-2A   \n",
       "2       70.0        NaN              No Tree                 NaN   GCJA03-2A   \n",
       "3       70.0        NaN              No Tree                 NaN   GCJA03-2A   \n",
       "4       52.0   Standard    Quercus palustris             Pin Oak   GCJA03-2A   \n",
       "\n",
       "   construc_1          program_ar   status_gro  \n",
       "0   Package-1  Right of Way (ROW)  Constructed  \n",
       "1   Package-1  Right of Way (ROW)  Constructed  \n",
       "2   Package-1  Right of Way (ROW)  Constructed  \n",
       "3   Package-1  Right of Way (ROW)  Constructed  \n",
       "4   Package-1  Right of Way (ROW)  Constructed  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# define the dataset identifier for the DEP Green Infrastructure dataset on Socrata\n",
    "socrata_dataset_identifier = 'bs59-f3nu'\n",
    "\n",
    "# QUERY parameter\n",
    "query = 'SELECT%20*%20LIMIT%2050000'\n",
    "url = f'https://data.cityofnewyork.us/resource/{socrata_dataset_identifier}.csv?$query={query}'\n",
    "\n",
    "print(f'Preview URL: {url}')\n",
    "\n",
    "# read data into pandas DataFrame\n",
    "df = pd.read_csv(url)\n",
    "\n",
    "# sanity check\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14361 entries, 0 to 14360\n",
      "Data columns (total 30 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   the_geom    14361 non-null  object \n",
      " 1   asset_id    14361 non-null  float64\n",
      " 2   gi_id       14361 non-null  object \n",
      " 3   dep_contra  14361 non-null  object \n",
      " 4   dep_cont_1  14361 non-null  object \n",
      " 5   row_onsite  14361 non-null  object \n",
      " 6   project_na  14361 non-null  object \n",
      " 7   asset_type  14361 non-null  object \n",
      " 8   status      14361 non-null  object \n",
      " 9   asset_x_co  14361 non-null  float64\n",
      " 10  asset_y_co  14361 non-null  float64\n",
      " 11  borough     14361 non-null  object \n",
      " 12  sewer_type  14361 non-null  object \n",
      " 13  outfall     14361 non-null  object \n",
      " 14  nyc_waters  14361 non-null  object \n",
      " 15  bbl         14361 non-null  float64\n",
      " 16  secondary_  14361 non-null  float64\n",
      " 17  community_  14361 non-null  float64\n",
      " 18  city_counc  14361 non-null  float64\n",
      " 19  assembly_d  14361 non-null  float64\n",
      " 20  asset_leng  14361 non-null  float64\n",
      " 21  asset_widt  14361 non-null  float64\n",
      " 22  asset_area  14361 non-null  float64\n",
      " 23  gi_feature  12090 non-null  object \n",
      " 24  tree_latin  12464 non-null  object \n",
      " 25  tree_commo  3358 non-null   object \n",
      " 26  constructi  12955 non-null  object \n",
      " 27  construc_1  12955 non-null  object \n",
      " 28  program_ar  14361 non-null  object \n",
      " 29  status_gro  14361 non-null  object \n",
      "dtypes: float64(11), object(19)\n",
      "memory usage: 3.3+ MB\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I prefer formatting my queries with the `query` parameter, as we can use SQL-style statements as an argument to the Socrata API. Note the `urllib.parse.quote_plus()` method to format the URL.\n",
    "\n",
    "As you'll see, we can also use the query parameter when working with sodapy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Preview URL: https://data.cityofnewyork.us/resource/erm2-nwe9.csv?$query=%0A++++SELECT+%2A%0A++++WHERE%0A++++++++created_date+%3E%3D+%272020-01-01%27%0A++++++++AND+descriptor+%3D+%27Street+Flooding+%28SJ%29%27%0A++++LIMIT%0A++++++++50000%0A\n",
      "(15225, 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>63301308</td>\n",
       "      <td>2024-12-05T16:20: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>10314.0</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.616099</td>\n",
       "      <td>-74.131077</td>\n",
       "      <td>\\n,  \\n(40.616098791773545, -74.13107748416888)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>63301307</td>\n",
       "      <td>2024-12-05T14:26: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>11367.0</td>\n",
       "      <td>72 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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>63305509</td>\n",
       "      <td>2024-12-05T13:41: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>11206.0</td>\n",
       "      <td>916 BROADWAY</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.697522</td>\n",
       "      <td>-73.936237</td>\n",
       "      <td>\\n,  \\n(40.697522135253905, -73.93623668626971)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>63309811</td>\n",
       "      <td>2024-12-05T12:50: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>10309.0</td>\n",
       "      <td>131 MC BAINE 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.543418</td>\n",
       "      <td>-74.210987</td>\n",
       "      <td>\\n,  \\n(40.54341808816043, -74.21098701137045)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>63302529</td>\n",
       "      <td>2024-12-05T12:29:00.000</td>\n",
       "      <td>2024-12-05T14: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>NaN</td>\n",
       "      <td>11214.0</td>\n",
       "      <td>7620 BAY PARKWAY</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.607107</td>\n",
       "      <td>-73.988450</td>\n",
       "      <td>\\n,  \\n(40.60710688908143, -73.9884502014737)</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    63301308  2024-12-05T16:20:00.000                      NaN    DEP   \n",
       "1    63301307  2024-12-05T14:26:00.000                      NaN    DEP   \n",
       "2    63305509  2024-12-05T13:41:00.000                      NaN    DEP   \n",
       "3    63309811  2024-12-05T12:50:00.000                      NaN    DEP   \n",
       "4    63302529  2024-12-05T12:29:00.000  2024-12-05T14:20:00.000    DEP   \n",
       "\n",
       "                              agency_name complaint_type  \\\n",
       "0  Department of Environmental Protection          Sewer   \n",
       "1  Department of Environmental Protection          Sewer   \n",
       "2  Department of Environmental Protection          Sewer   \n",
       "3  Department of Environmental Protection          Sewer   \n",
       "4  Department of Environmental Protection          Sewer   \n",
       "\n",
       "             descriptor  location_type  incident_zip     incident_address  \\\n",
       "0  Street Flooding (SJ)            NaN       10314.0                  NaN   \n",
       "1  Street Flooding (SJ)            NaN       11367.0              72 ROAD   \n",
       "2  Street Flooding (SJ)            NaN       11206.0         916 BROADWAY   \n",
       "3  Street Flooding (SJ)            NaN       10309.0  131 MC BAINE AVENUE   \n",
       "4  Street Flooding (SJ)            NaN       11214.0     7620 BAY PARKWAY   \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.616099 -74.131077   \n",
       "1                    NaN        NaN        NaN   \n",
       "2                    NaN  40.697522 -73.936237   \n",
       "3                    NaN  40.543418 -74.210987   \n",
       "4                    NaN  40.607107 -73.988450   \n",
       "\n",
       "                                          location  \n",
       "0  \\n,  \\n(40.616098791773545, -74.13107748416888)  \n",
       "1                                              NaN  \n",
       "2  \\n,  \\n(40.697522135253905, -73.93623668626971)  \n",
       "3   \\n,  \\n(40.54341808816043, -74.21098701137045)  \n",
       "4    \\n,  \\n(40.60710688908143, -73.9884502014737)  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# define the dataset identifier for NYC 311 Complaints\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "# define the query to filter the dataset:\n",
    "# this SQL-like query will select rows \n",
    "# where the 'created_date' is greater than or equal to 2020 and the 'descriptor' \n",
    "# is 'Street Flooding (SJ)'. Set limit to high value.\n",
    "\n",
    "query = \"\"\"\n",
    "    SELECT *\n",
    "    WHERE\n",
    "        created_date >= '2020-01-01'\n",
    "        AND descriptor = 'Street Flooding (SJ)'\n",
    "    LIMIT\n",
    "        50000\n",
    "\"\"\"\n",
    "\n",
    "# encode the query string for use in the URL. This ensures any special characters \n",
    "# in the query are properly escaped and can be safely included in the URL.\n",
    "safe_string = urllib.parse.quote_plus(query)\n",
    "\n",
    "# construct the full URL by appending the query string to the Socrata dataset URL.\n",
    "# this URL points to the NYC 311 dataset, filtered by the query above.\n",
    "url = f'https://data.cityofnewyork.us/resource/erm2-nwe9.csv?$query={safe_string}'\n",
    "\n",
    "# print the constructed URL to verify it's correct before requesting the data\n",
    "print(f'Preview URL: {url}')\n",
    "\n",
    "# load the filtered dataset into a pandas DataFrame using the URL\n",
    "df = pd.read_csv(url)\n",
    "\n",
    "# sanity checks\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5. SoQL with Sodapy\n",
    "The primary focus of this notebook is SoQL (Socrata Query Language), which is used to query data from Socrata-powered platforms. In addition, we’ll be using [Sodapy](https://github.com/xmunoz/sodapy), a Python client for interacting with the Socrata API. This is my preferred method for extracting data from NYC Open Data. However, please note that the Sodapy project is now archived on GitHub."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.1 SoQL Clauses"
   ]
  },
  {
   "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": [
      "(14361, 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>the_geom</th>\n",
       "      <th>asset_id</th>\n",
       "      <th>gi_id</th>\n",
       "      <th>dep_contra</th>\n",
       "      <th>dep_cont_1</th>\n",
       "      <th>row_onsite</th>\n",
       "      <th>project_na</th>\n",
       "      <th>asset_type</th>\n",
       "      <th>status</th>\n",
       "      <th>asset_x_co</th>\n",
       "      <th>...</th>\n",
       "      <th>asset_leng</th>\n",
       "      <th>asset_widt</th>\n",
       "      <th>asset_area</th>\n",
       "      <th>gi_feature</th>\n",
       "      <th>tree_latin</th>\n",
       "      <th>tree_commo</th>\n",
       "      <th>constructi</th>\n",
       "      <th>construc_1</th>\n",
       "      <th>program_ar</th>\n",
       "      <th>status_gro</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'type': 'Point', 'coordinates': [-73.81167623...</td>\n",
       "      <td>94002.0</td>\n",
       "      <td>1A</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWB</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1036475.27735</td>\n",
       "      <td>...</td>\n",
       "      <td>17.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>Chionanthus retusus</td>\n",
       "      <td>Chinese Fringetree</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'type': 'Point', 'coordinates': [-73.81228577...</td>\n",
       "      <td>94012.0</td>\n",
       "      <td>GS6A</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWGS</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1036305.46107</td>\n",
       "      <td>...</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>45.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No Tree</td>\n",
       "      <td>N/A</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'type': 'Point', 'coordinates': [-73.81223444...</td>\n",
       "      <td>94017.0</td>\n",
       "      <td>GS8C</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWGS</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1036319.11813</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No Tree</td>\n",
       "      <td>N/A</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'type': 'Point', 'coordinates': [-73.81205974...</td>\n",
       "      <td>94019.0</td>\n",
       "      <td>GS8E</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWGS</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1036367.52667</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No Tree</td>\n",
       "      <td>N/A</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'type': 'Point', 'coordinates': [-73.81310191...</td>\n",
       "      <td>94021.0</td>\n",
       "      <td>10A</td>\n",
       "      <td>GQJA03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>ROW</td>\n",
       "      <td>DDC JAM-003 Phase 2</td>\n",
       "      <td>ROWB</td>\n",
       "      <td>Constructed (Full Maintenance)</td>\n",
       "      <td>1036078.81888</td>\n",
       "      <td>...</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>Quercus palustris</td>\n",
       "      <td>Pin Oak</td>\n",
       "      <td>GCJA03-2A</td>\n",
       "      <td>Package-1</td>\n",
       "      <td>Right of Way (ROW)</td>\n",
       "      <td>Constructed</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            the_geom asset_id gi_id  \\\n",
       "0  {'type': 'Point', 'coordinates': [-73.81167623...  94002.0    1A   \n",
       "1  {'type': 'Point', 'coordinates': [-73.81228577...  94012.0  GS6A   \n",
       "2  {'type': 'Point', 'coordinates': [-73.81223444...  94017.0  GS8C   \n",
       "3  {'type': 'Point', 'coordinates': [-73.81205974...  94019.0  GS8E   \n",
       "4  {'type': 'Point', 'coordinates': [-73.81310191...  94021.0   10A   \n",
       "\n",
       "  dep_contra dep_cont_1 row_onsite           project_na asset_type  \\\n",
       "0  GQJA03-02          2        ROW  DDC JAM-003 Phase 2       ROWB   \n",
       "1  GQJA03-02          2        ROW  DDC JAM-003 Phase 2      ROWGS   \n",
       "2  GQJA03-02          2        ROW  DDC JAM-003 Phase 2      ROWGS   \n",
       "3  GQJA03-02          2        ROW  DDC JAM-003 Phase 2      ROWGS   \n",
       "4  GQJA03-02          2        ROW  DDC JAM-003 Phase 2       ROWB   \n",
       "\n",
       "                           status     asset_x_co  ... asset_leng asset_widt  \\\n",
       "0  Constructed (Full Maintenance)  1036475.27735  ...       17.0        5.0   \n",
       "1  Constructed (Full Maintenance)  1036305.46107  ...       13.0        3.5   \n",
       "2  Constructed (Full Maintenance)  1036319.11813  ...       20.0        3.5   \n",
       "3  Constructed (Full Maintenance)  1036367.52667  ...       20.0        3.5   \n",
       "4  Constructed (Full Maintenance)  1036078.81888  ...       13.0        4.0   \n",
       "\n",
       "  asset_area gi_feature           tree_latin          tree_commo constructi  \\\n",
       "0       85.0   Standard  Chionanthus retusus  Chinese Fringetree  GCJA03-2A   \n",
       "1       45.5        NaN              No Tree                 N/A  GCJA03-2A   \n",
       "2       70.0        NaN              No Tree                 N/A  GCJA03-2A   \n",
       "3       70.0        NaN              No Tree                 N/A  GCJA03-2A   \n",
       "4       52.0   Standard    Quercus palustris             Pin Oak  GCJA03-2A   \n",
       "\n",
       "  construc_1          program_ar   status_gro  \n",
       "0  Package-1  Right of Way (ROW)  Constructed  \n",
       "1  Package-1  Right of Way (ROW)  Constructed  \n",
       "2  Package-1  Right of Way (ROW)  Constructed  \n",
       "3  Package-1  Right of Way (ROW)  Constructed  \n",
       "4  Package-1  Right of Way (ROW)  Constructed  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# source domain for NYC Open Data on Socrata\n",
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "\n",
    "# define the dataset identifier for the DEP Green Infrastructure dataset on Socrata\n",
    "socrata_dataset_identifier = 'bs59-f3nu'\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",
    "query = \"\"\"\n",
    "    SELECT *    \n",
    "    LIMIT 50000\n",
    "\"\"\"\n",
    "\n",
    "# returned as JSON from API / converted to Python list of dictionaries by sodapy\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "# convert to pandas DataFrame\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "# sanity check\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14361 entries, 0 to 14360\n",
      "Data columns (total 30 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   the_geom    14361 non-null  object\n",
      " 1   asset_id    14361 non-null  object\n",
      " 2   gi_id       14361 non-null  object\n",
      " 3   dep_contra  14361 non-null  object\n",
      " 4   dep_cont_1  14361 non-null  object\n",
      " 5   row_onsite  14361 non-null  object\n",
      " 6   project_na  14361 non-null  object\n",
      " 7   asset_type  14361 non-null  object\n",
      " 8   status      14361 non-null  object\n",
      " 9   asset_x_co  14361 non-null  object\n",
      " 10  asset_y_co  14361 non-null  object\n",
      " 11  borough     14361 non-null  object\n",
      " 12  sewer_type  14361 non-null  object\n",
      " 13  outfall     14361 non-null  object\n",
      " 14  nyc_waters  14361 non-null  object\n",
      " 15  bbl         14361 non-null  object\n",
      " 16  secondary_  14361 non-null  object\n",
      " 17  community_  14361 non-null  object\n",
      " 18  city_counc  14361 non-null  object\n",
      " 19  assembly_d  14361 non-null  object\n",
      " 20  asset_leng  14361 non-null  object\n",
      " 21  asset_widt  14361 non-null  object\n",
      " 22  asset_area  14361 non-null  object\n",
      " 23  gi_feature  12090 non-null  object\n",
      " 24  tree_latin  12464 non-null  object\n",
      " 25  tree_commo  12464 non-null  object\n",
      " 26  constructi  12955 non-null  object\n",
      " 27  construc_1  12955 non-null  object\n",
      " 28  program_ar  14361 non-null  object\n",
      " 29  status_gro  14361 non-null  object\n",
      "dtypes: object(30)\n",
      "memory usage: 3.3+ MB\n"
     ]
    }
   ],
   "source": [
    "# examine columns and datatypes\n",
    "df.info()"
   ]
  },
  {
   "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": [
      "(40972, 32)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_key</th>\n",
       "      <th>created_date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>complaint_type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>intersection_street_1</th>\n",
       "      <th>intersection_street_2</th>\n",
       "      <th>address_type</th>\n",
       "      <th>...</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",
       "      <th>facility_type</th>\n",
       "      <th>due_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>63301308</td>\n",
       "      <td>2024-12-05T16: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>10314</td>\n",
       "      <td>JEWETT AVENUE</td>\n",
       "      <td>GOODWIN AVENUE</td>\n",
       "      <td>INTERSECTION</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>63301307</td>\n",
       "      <td>2024-12-05T14:26: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>11367</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BLOCKFACE</td>\n",
       "      <td>...</td>\n",
       "      <td>72 ROAD</td>\n",
       "      <td>72 ROAD</td>\n",
       "      <td>150 STREET</td>\n",
       "      <td>153 STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>63305509</td>\n",
       "      <td>2024-12-05T13:41:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>11206</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>916 BROADWAY</td>\n",
       "      <td>BROADWAY</td>\n",
       "      <td>STOCKTON ST</td>\n",
       "      <td>MELROSE ST</td>\n",
       "      <td>3015840020</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>3</th>\n",
       "      <td>63309811</td>\n",
       "      <td>2024-12-05T12: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>10309</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>131 MC BAINE AVENUE</td>\n",
       "      <td>MC BAINE AVENUE</td>\n",
       "      <td>ROSSVILLE AVE</td>\n",
       "      <td>MAGUIRE AVE</td>\n",
       "      <td>5070280026</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>63302529</td>\n",
       "      <td>2024-12-05T12:29: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>11214</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>7620 BAY PARKWAY</td>\n",
       "      <td>BAY PARKWAY</td>\n",
       "      <td>AVENUE P</td>\n",
       "      <td>77 ST</td>\n",
       "      <td>3062417501</td>\n",
       "      <td>2024-12-05T14:20:00.000</td>\n",
       "      <td>The Department of Environment Protection inspe...</td>\n",
       "      <td>2024-12-05T14:20:00.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_key             created_date agency  \\\n",
       "0   63301308  2024-12-05T16:20:00.000    DEP   \n",
       "1   63301307  2024-12-05T14:26:00.000    DEP   \n",
       "2   63305509  2024-12-05T13:41:00.000    DEP   \n",
       "3   63309811  2024-12-05T12:50:00.000    DEP   \n",
       "4   63302529  2024-12-05T12:29: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)        10314         JEWETT AVENUE   \n",
       "1  Street Flooding (SJ)        11367                   NaN   \n",
       "2  Street Flooding (SJ)        11206                   NaN   \n",
       "3  Street Flooding (SJ)        10309                   NaN   \n",
       "4  Street Flooding (SJ)        11214                   NaN   \n",
       "\n",
       "  intersection_street_2  address_type  ...     incident_address  \\\n",
       "0        GOODWIN AVENUE  INTERSECTION  ...                  NaN   \n",
       "1                   NaN     BLOCKFACE  ...              72 ROAD   \n",
       "2                   NaN       ADDRESS  ...         916 BROADWAY   \n",
       "3                   NaN       ADDRESS  ...  131 MC BAINE AVENUE   \n",
       "4                   NaN       ADDRESS  ...     7620 BAY PARKWAY   \n",
       "\n",
       "       street_name cross_street_1 cross_street_2         bbl  \\\n",
       "0              NaN            NaN            NaN         NaN   \n",
       "1          72 ROAD     150 STREET     153 STREET         NaN   \n",
       "2         BROADWAY    STOCKTON ST     MELROSE ST  3015840020   \n",
       "3  MC BAINE AVENUE  ROSSVILLE AVE    MAGUIRE AVE  5070280026   \n",
       "4      BAY PARKWAY       AVENUE P          77 ST  3062417501   \n",
       "\n",
       "               closed_date                             resolution_description  \\\n",
       "0                      NaN                                                NaN   \n",
       "1                      NaN                                                NaN   \n",
       "2                      NaN                                                NaN   \n",
       "3                      NaN                                                NaN   \n",
       "4  2024-12-05T14:20:00.000  The Department of Environment Protection inspe...   \n",
       "\n",
       "  resolution_action_updated_date facility_type due_date  \n",
       "0                            NaN           NaN      NaN  \n",
       "1                            NaN           NaN      NaN  \n",
       "2                            NaN           NaN      NaN  \n",
       "3                            NaN           NaN      NaN  \n",
       "4        2024-12-05T14:20:00.000           NaN      NaN  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "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:\n",
    "# select all columns, where the descriptor\n",
    "# is Street Flooding (SJ), limit our records to full dataset\n",
    "\n",
    "query = \"\"\"\n",
    "    SELECT *\n",
    "    WHERE\n",
    "        descriptor = 'Street Flooding (SJ)'\n",
    "    LIMIT\n",
    "        50000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Attention\n",
    "When querying all records, be sure to set the `limit` parameter to a value large enough to exceed the total number of records in your dataset. If the `limit` value is set to exactly the same number as the records returned, you likely haven’t retrieved all the data. To avoid this, choose a `limit` that is larger than the dataset’s total size."
   ]
  },
  {
   "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": [
      "sanity check:\n",
      "min: 2011-01-02T10:13:00.000\n",
      "max: 2011-12-31T17:03:00.000\n",
      "(2644, 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": 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:\n",
    "# select all columns, where the descriptor\n",
    "# is Street Flooding (SJ) and created_date is between 2011 and 2012 and limit our records to full dataset\n",
    "\n",
    "query = \"\"\"\n",
    "    SELECT * \n",
    "    WHERE \n",
    "        created_date BETWEEN '2011-01-01' AND '2012-01-01'\n",
    "        AND descriptor = 'Street Flooding (SJ)'\n",
    "    LIMIT 100000    \n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "# sanity checks\n",
    "print('sanity check:')\n",
    "print('min:', df.created_date.min())\n",
    "print('max:', df.created_date.max())\n",
    "\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "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": [
      "(40972, 32)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_key</th>\n",
       "      <th>created_date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>complaint_type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>intersection_street_1</th>\n",
       "      <th>intersection_street_2</th>\n",
       "      <th>address_type</th>\n",
       "      <th>...</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",
       "      <th>facility_type</th>\n",
       "      <th>due_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>63301308</td>\n",
       "      <td>2024-12-05T16: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>10314</td>\n",
       "      <td>JEWETT AVENUE</td>\n",
       "      <td>GOODWIN AVENUE</td>\n",
       "      <td>INTERSECTION</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>63301307</td>\n",
       "      <td>2024-12-05T14:26: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>11367</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BLOCKFACE</td>\n",
       "      <td>...</td>\n",
       "      <td>72 ROAD</td>\n",
       "      <td>72 ROAD</td>\n",
       "      <td>150 STREET</td>\n",
       "      <td>153 STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>63305509</td>\n",
       "      <td>2024-12-05T13:41:00.000</td>\n",
       "      <td>DEP</td>\n",
       "      <td>Department of Environmental Protection</td>\n",
       "      <td>Sewer</td>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>11206</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>916 BROADWAY</td>\n",
       "      <td>BROADWAY</td>\n",
       "      <td>STOCKTON ST</td>\n",
       "      <td>MELROSE ST</td>\n",
       "      <td>3015840020</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>3</th>\n",
       "      <td>63309811</td>\n",
       "      <td>2024-12-05T12: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>10309</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>131 MC BAINE AVENUE</td>\n",
       "      <td>MC BAINE AVENUE</td>\n",
       "      <td>ROSSVILLE AVE</td>\n",
       "      <td>MAGUIRE AVE</td>\n",
       "      <td>5070280026</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>63302529</td>\n",
       "      <td>2024-12-05T12:29: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>11214</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>7620 BAY PARKWAY</td>\n",
       "      <td>BAY PARKWAY</td>\n",
       "      <td>AVENUE P</td>\n",
       "      <td>77 ST</td>\n",
       "      <td>3062417501</td>\n",
       "      <td>2024-12-05T14:20:00.000</td>\n",
       "      <td>The Department of Environment Protection inspe...</td>\n",
       "      <td>2024-12-05T14:20:00.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_key             created_date agency  \\\n",
       "0   63301308  2024-12-05T16:20:00.000    DEP   \n",
       "1   63301307  2024-12-05T14:26:00.000    DEP   \n",
       "2   63305509  2024-12-05T13:41:00.000    DEP   \n",
       "3   63309811  2024-12-05T12:50:00.000    DEP   \n",
       "4   63302529  2024-12-05T12:29: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)        10314         JEWETT AVENUE   \n",
       "1  Street Flooding (SJ)        11367                   NaN   \n",
       "2  Street Flooding (SJ)        11206                   NaN   \n",
       "3  Street Flooding (SJ)        10309                   NaN   \n",
       "4  Street Flooding (SJ)        11214                   NaN   \n",
       "\n",
       "  intersection_street_2  address_type  ...     incident_address  \\\n",
       "0        GOODWIN AVENUE  INTERSECTION  ...                  NaN   \n",
       "1                   NaN     BLOCKFACE  ...              72 ROAD   \n",
       "2                   NaN       ADDRESS  ...         916 BROADWAY   \n",
       "3                   NaN       ADDRESS  ...  131 MC BAINE AVENUE   \n",
       "4                   NaN       ADDRESS  ...     7620 BAY PARKWAY   \n",
       "\n",
       "       street_name cross_street_1 cross_street_2         bbl  \\\n",
       "0              NaN            NaN            NaN         NaN   \n",
       "1          72 ROAD     150 STREET     153 STREET         NaN   \n",
       "2         BROADWAY    STOCKTON ST     MELROSE ST  3015840020   \n",
       "3  MC BAINE AVENUE  ROSSVILLE AVE    MAGUIRE AVE  5070280026   \n",
       "4      BAY PARKWAY       AVENUE P          77 ST  3062417501   \n",
       "\n",
       "               closed_date                             resolution_description  \\\n",
       "0                      NaN                                                NaN   \n",
       "1                      NaN                                                NaN   \n",
       "2                      NaN                                                NaN   \n",
       "3                      NaN                                                NaN   \n",
       "4  2024-12-05T14:20:00.000  The Department of Environment Protection inspe...   \n",
       "\n",
       "  resolution_action_updated_date facility_type due_date  \n",
       "0                            NaN           NaN      NaN  \n",
       "1                            NaN           NaN      NaN  \n",
       "2                            NaN           NaN      NaN  \n",
       "3                            NaN           NaN      NaN  \n",
       "4        2024-12-05T14:20:00.000           NaN      NaN  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "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=100\n",
    ")\n",
    "\n",
    "# SoQL query string below:\n",
    "# select all columns, where the descriptor\n",
    "# is Street Flooding (SJ), sort the created_date in descending order and limit our records to full dataset\n",
    "\n",
    "query = \"\"\"\n",
    "    SELECT *\n",
    "    WHERE\n",
    "        descriptor = 'Street Flooding (SJ)'\n",
    "    ORDER BY\n",
    "        created_date DESC\n",
    "    LIMIT\n",
    "        100000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "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": [
      "(7, 3)\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>borough</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>15782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>11045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>STATEN ISLAND</td>\n",
       "      <td>7259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>3538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>3294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>Unspecified</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             descriptor        borough  count\n",
       "0  Street Flooding (SJ)         QUEENS  15782\n",
       "1  Street Flooding (SJ)       BROOKLYN  11045\n",
       "2  Street Flooding (SJ)  STATEN ISLAND   7259\n",
       "3  Street Flooding (SJ)      MANHATTAN   3538\n",
       "4  Street Flooding (SJ)          BRONX   3294\n",
       "5  Street Flooding (SJ)    Unspecified     50\n",
       "6  Street Flooding (SJ)            NaN      4"
      ]
     },
     "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:\n",
    "# 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",
    "        descriptor,\n",
    "        borough, \n",
    "        count(unique_key) AS count\n",
    "    WHERE \n",
    "        descriptor = 'Street Flooding (SJ)'\n",
    "    GROUP BY \n",
    "        descriptor,\n",
    "        borough\n",
    "    ORDER BY \n",
    "        count DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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": [
      "(3, 3)\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>borough</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>15782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>11045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>STATEN ISLAND</td>\n",
       "      <td>7259</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             descriptor        borough  count\n",
       "0  Street Flooding (SJ)         QUEENS  15782\n",
       "1  Street Flooding (SJ)       BROOKLYN  11045\n",
       "2  Street Flooding (SJ)  STATEN ISLAND   7259"
      ]
     },
     "execution_count": 17,
     "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",
    "        descriptor,\n",
    "        borough, \n",
    "        count(*) AS count\n",
    "    WHERE \n",
    "        descriptor = 'Street Flooding (SJ)'\n",
    "    GROUP BY \n",
    "        descriptor,\n",
    "        borough\n",
    "    HAVING \n",
    "        count > 5000\n",
    "    ORDER BY \n",
    "        count DESC\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.2 SoQL Function and Keyword Listing"
   ]
  },
  {
   "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": [
      "(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>118577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>40972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Flood Light Lamp Out</td>\n",
       "      <td>6607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Highway Flooding (SH)</td>\n",
       "      <td>3186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Flood Light Lamp Cycling</td>\n",
       "      <td>2614</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>237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Flood Light Lamp Missing</td>\n",
       "      <td>216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Flood Light Lamp Dim</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>RAIN GARDEN FLOODING (SRGFLD)</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Flooded</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Flooding on Highway</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          descriptor   count\n",
       "0   Catch Basin Clogged/Flooding (Use Comments) (SC)  118577\n",
       "1                               Street Flooding (SJ)   40972\n",
       "2                               Flood Light Lamp Out    6607\n",
       "3                              Highway Flooding (SH)    3186\n",
       "4                           Flood Light Lamp Cycling    2614\n",
       "5                                 Flooding on Street     673\n",
       "6                                Ready NY - Flooding     271\n",
       "7                        Flood Light Lamp Dayburning     237\n",
       "8                           Flood Light Lamp Missing     216\n",
       "9                               Flood Light Lamp Dim     195\n",
       "10                     RAIN GARDEN FLOODING (SRGFLD)     152\n",
       "11                                           Flooded      57\n",
       "12                               Flooding on Highway      15"
      ]
     },
     "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=1000\n",
    ")\n",
    "\n",
    "# SoQL query string below:\n",
    "# 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(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "df"
   ]
  },
  {
   "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": [
      "(15, 3)\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>year</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2018-01-01T00:00:00.000</td>\n",
       "      <td>4140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2021-01-01T00:00:00.000</td>\n",
       "      <td>3702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2023-01-01T00:00:00.000</td>\n",
       "      <td>3484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2019-01-01T00:00:00.000</td>\n",
       "      <td>3434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2022-01-01T00:00:00.000</td>\n",
       "      <td>3078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2024-01-01T00:00:00.000</td>\n",
       "      <td>2806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2011-01-01T00:00:00.000</td>\n",
       "      <td>2644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2017-01-01T00:00:00.000</td>\n",
       "      <td>2532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2010-01-01T00:00:00.000</td>\n",
       "      <td>2531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2014-01-01T00:00:00.000</td>\n",
       "      <td>2498</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             descriptor                     year count\n",
       "0  Street Flooding (SJ)  2018-01-01T00:00:00.000  4140\n",
       "1  Street Flooding (SJ)  2021-01-01T00:00:00.000  3702\n",
       "2  Street Flooding (SJ)  2023-01-01T00:00:00.000  3484\n",
       "3  Street Flooding (SJ)  2019-01-01T00:00:00.000  3434\n",
       "4  Street Flooding (SJ)  2022-01-01T00:00:00.000  3078\n",
       "5  Street Flooding (SJ)  2024-01-01T00:00:00.000  2806\n",
       "6  Street Flooding (SJ)  2011-01-01T00:00:00.000  2644\n",
       "7  Street Flooding (SJ)  2017-01-01T00:00:00.000  2532\n",
       "8  Street Flooding (SJ)  2010-01-01T00:00:00.000  2531\n",
       "9  Street Flooding (SJ)  2014-01-01T00:00:00.000  2498"
      ]
     },
     "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:\n",
    "# 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",
    "        descriptor,\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",
    "        descriptor,\n",
    "        year\n",
    "    ORDER BY \n",
    "        count DESC    \n",
    "    LIMIT \n",
    "        1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "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": [
      "(180, 3)\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>year_month</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2021-09-01T00:00:00.000</td>\n",
       "      <td>1035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2023-09-01T00:00:00.000</td>\n",
       "      <td>932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2018-11-01T00:00:00.000</td>\n",
       "      <td>710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2021-08-01T00:00:00.000</td>\n",
       "      <td>595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2024-03-01T00:00:00.000</td>\n",
       "      <td>575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2022-12-01T00:00:00.000</td>\n",
       "      <td>530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2017-05-01T00:00:00.000</td>\n",
       "      <td>524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2021-07-01T00:00:00.000</td>\n",
       "      <td>499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2011-08-01T00:00:00.000</td>\n",
       "      <td>497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2016-02-01T00:00:00.000</td>\n",
       "      <td>490</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             descriptor               year_month count\n",
       "0  Street Flooding (SJ)  2021-09-01T00:00:00.000  1035\n",
       "1  Street Flooding (SJ)  2023-09-01T00:00:00.000   932\n",
       "2  Street Flooding (SJ)  2018-11-01T00:00:00.000   710\n",
       "3  Street Flooding (SJ)  2021-08-01T00:00:00.000   595\n",
       "4  Street Flooding (SJ)  2024-03-01T00:00:00.000   575\n",
       "5  Street Flooding (SJ)  2022-12-01T00:00:00.000   530\n",
       "6  Street Flooding (SJ)  2017-05-01T00:00:00.000   524\n",
       "7  Street Flooding (SJ)  2021-07-01T00:00:00.000   499\n",
       "8  Street Flooding (SJ)  2011-08-01T00:00:00.000   497\n",
       "9  Street Flooding (SJ)  2016-02-01T00:00:00.000   490"
      ]
     },
     "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:\n",
    "# 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",
    "        descriptor,\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",
    "        descriptor,\n",
    "        year_month\n",
    "    ORDER BY \n",
    "        count DESC    \n",
    "    LIMIT \n",
    "        1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "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": [
      "(1000, 3)\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>year_month_day</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2023-09-29T00:00:00.000</td>\n",
       "      <td>623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2021-09-02T00:00:00.000</td>\n",
       "      <td>350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2021-09-01T00:00:00.000</td>\n",
       "      <td>344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2022-12-23T00:00:00.000</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2017-05-05T00:00:00.000</td>\n",
       "      <td>247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2014-12-09T00:00:00.000</td>\n",
       "      <td>226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2014-04-30T00:00:00.000</td>\n",
       "      <td>189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2021-10-26T00:00:00.000</td>\n",
       "      <td>177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2018-04-16T00:00:00.000</td>\n",
       "      <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2013-05-08T00:00:00.000</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             descriptor           year_month_day count\n",
       "0  Street Flooding (SJ)  2023-09-29T00:00:00.000   623\n",
       "1  Street Flooding (SJ)  2021-09-02T00:00:00.000   350\n",
       "2  Street Flooding (SJ)  2021-09-01T00:00:00.000   344\n",
       "3  Street Flooding (SJ)  2022-12-23T00:00:00.000   308\n",
       "4  Street Flooding (SJ)  2017-05-05T00:00:00.000   247\n",
       "5  Street Flooding (SJ)  2014-12-09T00:00:00.000   226\n",
       "6  Street Flooding (SJ)  2014-04-30T00:00:00.000   189\n",
       "7  Street Flooding (SJ)  2021-10-26T00:00:00.000   177\n",
       "8  Street Flooding (SJ)  2018-04-16T00:00:00.000   163\n",
       "9  Street Flooding (SJ)  2013-05-08T00:00:00.000   162"
      ]
     },
     "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:\n",
    "# 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",
    "        descriptor,\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",
    "        descriptor,\n",
    "        year_month_day\n",
    "    ORDER BY \n",
    "        count DESC    \n",
    "    LIMIT \n",
    "        1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "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": [
      "(15, 3)\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>year</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2018</td>\n",
       "      <td>4140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2021</td>\n",
       "      <td>3702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2023</td>\n",
       "      <td>3484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2019</td>\n",
       "      <td>3434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2022</td>\n",
       "      <td>3078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2024</td>\n",
       "      <td>2806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2011</td>\n",
       "      <td>2644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2017</td>\n",
       "      <td>2532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2010</td>\n",
       "      <td>2531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2014</td>\n",
       "      <td>2498</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             descriptor  year count\n",
       "0  Street Flooding (SJ)  2018  4140\n",
       "1  Street Flooding (SJ)  2021  3702\n",
       "2  Street Flooding (SJ)  2023  3484\n",
       "3  Street Flooding (SJ)  2019  3434\n",
       "4  Street Flooding (SJ)  2022  3078\n",
       "5  Street Flooding (SJ)  2024  2806\n",
       "6  Street Flooding (SJ)  2011  2644\n",
       "7  Street Flooding (SJ)  2017  2532\n",
       "8  Street Flooding (SJ)  2010  2531\n",
       "9  Street Flooding (SJ)  2014  2498"
      ]
     },
     "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:\n",
    "# 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",
    "        descriptor,\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",
    "        descriptor,\n",
    "        year\n",
    "    ORDER BY \n",
    "        count DESC    \n",
    "    LIMIT \n",
    "        1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "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": [
      "(12, 3)\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>month</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>5</td>\n",
       "      <td>4331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>9</td>\n",
       "      <td>4249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>8</td>\n",
       "      <td>4020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>7</td>\n",
       "      <td>3902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>6</td>\n",
       "      <td>3450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>12</td>\n",
       "      <td>3356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>3</td>\n",
       "      <td>3202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>4</td>\n",
       "      <td>3098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>10</td>\n",
       "      <td>3059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2</td>\n",
       "      <td>2907</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             descriptor month count\n",
       "0  Street Flooding (SJ)     5  4331\n",
       "1  Street Flooding (SJ)     9  4249\n",
       "2  Street Flooding (SJ)     8  4020\n",
       "3  Street Flooding (SJ)     7  3902\n",
       "4  Street Flooding (SJ)     6  3450\n",
       "5  Street Flooding (SJ)    12  3356\n",
       "6  Street Flooding (SJ)     3  3202\n",
       "7  Street Flooding (SJ)     4  3098\n",
       "8  Street Flooding (SJ)    10  3059\n",
       "9  Street Flooding (SJ)     2  2907"
      ]
     },
     "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:\n",
    "# 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",
    "        descriptor,\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",
    "        descriptor,\n",
    "        month\n",
    "    ORDER BY \n",
    "        count DESC    \n",
    "    LIMIT \n",
    "        1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "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": [
      "(31, 3)\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>day</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>29</td>\n",
       "      <td>1972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2</td>\n",
       "      <td>1749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>23</td>\n",
       "      <td>1745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>1</td>\n",
       "      <td>1728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>30</td>\n",
       "      <td>1670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>13</td>\n",
       "      <td>1590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>9</td>\n",
       "      <td>1531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>18</td>\n",
       "      <td>1495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>8</td>\n",
       "      <td>1458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>25</td>\n",
       "      <td>1457</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             descriptor day count\n",
       "0  Street Flooding (SJ)  29  1972\n",
       "1  Street Flooding (SJ)   2  1749\n",
       "2  Street Flooding (SJ)  23  1745\n",
       "3  Street Flooding (SJ)   1  1728\n",
       "4  Street Flooding (SJ)  30  1670\n",
       "5  Street Flooding (SJ)  13  1590\n",
       "6  Street Flooding (SJ)   9  1531\n",
       "7  Street Flooding (SJ)  18  1495\n",
       "8  Street Flooding (SJ)   8  1458\n",
       "9  Street Flooding (SJ)  25  1457"
      ]
     },
     "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:\n",
    "# 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",
    "        descriptor,\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",
    "        descriptor,\n",
    "        day\n",
    "    ORDER BY \n",
    "        count DESC    \n",
    "    LIMIT \n",
    "        1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "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": [
      "(53, 3)\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>week_of_year</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>39</td>\n",
       "      <td>1440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>18</td>\n",
       "      <td>1360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>35</td>\n",
       "      <td>1214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>33</td>\n",
       "      <td>1179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>30</td>\n",
       "      <td>1104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>32</td>\n",
       "      <td>1061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>51</td>\n",
       "      <td>1052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>23</td>\n",
       "      <td>955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>29</td>\n",
       "      <td>919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>20</td>\n",
       "      <td>913</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             descriptor week_of_year count\n",
       "0  Street Flooding (SJ)           39  1440\n",
       "1  Street Flooding (SJ)           18  1360\n",
       "2  Street Flooding (SJ)           35  1214\n",
       "3  Street Flooding (SJ)           33  1179\n",
       "4  Street Flooding (SJ)           30  1104\n",
       "5  Street Flooding (SJ)           32  1061\n",
       "6  Street Flooding (SJ)           51  1052\n",
       "7  Street Flooding (SJ)           23   955\n",
       "8  Street Flooding (SJ)           29   919\n",
       "9  Street Flooding (SJ)           20   913"
      ]
     },
     "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:\n",
    "# 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",
    "        descriptor,\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",
    "        descriptor,\n",
    "        week_of_year\n",
    "    ORDER BY \n",
    "        count DESC    \n",
    "    LIMIT \n",
    "        1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "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": [
      "(7, 3)\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>day_of_week</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>5</td>\n",
       "      <td>7324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>2</td>\n",
       "      <td>6891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>1</td>\n",
       "      <td>6720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>3</td>\n",
       "      <td>6533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>4</td>\n",
       "      <td>6306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>0</td>\n",
       "      <td>3611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>6</td>\n",
       "      <td>3587</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             descriptor day_of_week count\n",
       "0  Street Flooding (SJ)           5  7324\n",
       "1  Street Flooding (SJ)           2  6891\n",
       "2  Street Flooding (SJ)           1  6720\n",
       "3  Street Flooding (SJ)           3  6533\n",
       "4  Street Flooding (SJ)           4  6306\n",
       "5  Street Flooding (SJ)           0  3611\n",
       "6  Street Flooding (SJ)           6  3587"
      ]
     },
     "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:\n",
    "# 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",
    "        descriptor,\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",
    "        descriptor,\n",
    "        day_of_week\n",
    "    ORDER BY \n",
    "        count DESC    \n",
    "    LIMIT \n",
    "        1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "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": [
      "(24, 3)\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>hour</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>11</td>\n",
       "      <td>3503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>9</td>\n",
       "      <td>3445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>10</td>\n",
       "      <td>3442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>12</td>\n",
       "      <td>3117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>15</td>\n",
       "      <td>3002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>14</td>\n",
       "      <td>2927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>16</td>\n",
       "      <td>2862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>13</td>\n",
       "      <td>2852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>8</td>\n",
       "      <td>2611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Street Flooding (SJ)</td>\n",
       "      <td>17</td>\n",
       "      <td>2266</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             descriptor hour count\n",
       "0  Street Flooding (SJ)   11  3503\n",
       "1  Street Flooding (SJ)    9  3445\n",
       "2  Street Flooding (SJ)   10  3442\n",
       "3  Street Flooding (SJ)   12  3117\n",
       "4  Street Flooding (SJ)   15  3002\n",
       "5  Street Flooding (SJ)   14  2927\n",
       "6  Street Flooding (SJ)   16  2862\n",
       "7  Street Flooding (SJ)   13  2852\n",
       "8  Street Flooding (SJ)    8  2611\n",
       "9  Street Flooding (SJ)   17  2266"
      ]
     },
     "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:\n",
    "# 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",
    "        descriptor,\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",
    "        descriptor,\n",
    "        hour\n",
    "    ORDER BY \n",
    "        count DESC    \n",
    "    LIMIT\n",
    "        1000\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "df = pd.DataFrame.from_records(results)\n",
    "\n",
    "print(df.shape)\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# close connection\n",
    "client.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}