socrata-api-basics.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Socrata API Basics \n",
    "## Socrata Open Data API (SODA) Tutorial with Python and NYC Open Data\n",
    "Author: Mark Bauer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Table of Contents\n",
    "=================\n",
    "\n",
    "   1. Introduction\n",
    "   2. Sodapy\n",
    "   3. Importing Libraries\n",
    "   4. Sodapy Socrata Class\n",
    "   5. Socrata APIs\n",
    "       - 5.1 Socrata API\n",
    "       - 5.2 Discovery API\n",
    "       - 5.3 Metadata API"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Introduction  \n",
    "This notebook demonstrates how to interact with the [Socrata Open Data API](https://dev.socrata.com/) in Python and explores different methods for retrieving data from Socrata-based open data portals. Additionally, we'll explore the library [Sodapy](https://github.com/xmunoz/sodapy), a Python client for the Socrata Open Data API.\n",
    "\n",
    "Not only will we learn how to fetch data, but we'll also explore Socrata's available metadata using the [Discovery](https://dev.socrata.com/docs/other/discovery#?route=overview) and [Metadata](https://dev.socrata.com/docs/other/metadata#?route=overview) APIs. These endpoints are often underutilized, but they offer valuable insights, such as dataset download counts, page views, and more.\n",
    "\n",
    "While the main focus is on understanding how to interact with the Socrata API, we'll use the sodapy library throughout the examples, as it provides a straightforward interface for working with the Socrata API in Python. Both the Socrata API endpoint as well as sodapy code snippets will be provided to give you flexibility in how you approach your data retrieval. For this tutorial, the code is written in Python and will use data from NYC Open Data as an example.\n",
    "\n",
    "Finally, I encourage you to read the [Socrata Documentation](https://dev.socrata.com/), as well as the [API Docs](https://dev.socrata.com/docs/endpoints) for a comprehensive understanding. This project highlights some of the most popular methods for working with the API, but it is by no means exhaustive."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Sodapy\n",
    "\n",
    "[Sodapy](https://github.com/xmunoz/sodapy) is a python client for the Socrata Open Data API. In order use sodapy, a source domain (i.e. the Socrata 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 on Socrata Open Data, 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 dataset: `erm2-nwe9`. The screenshot below displays where we retrieve this information.\n",
    "\n",
    "This is my preferred method for fetching data from NYC Open Data. However, please note that the Sodapy project is now archived on GitHub and is read-only.\n",
    "\n",
    "![nyc-311-api-docs](images/nyc-311-api-docs.png)  \n",
    "\n",
    "Source: https://dev.socrata.com/foundry/data.cityofnewyork.us/erm2-nwe9\n",
    "\n",
    "![nyc-311-api-docs](images/nyc-311-api-docs.png)  \n",
    "\n",
    "Screenshot: https://dev.socrata.com/foundry/data.cityofnewyork.us/erm2-nwe9"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will focus on three popular Sodapy methods:\n",
    "- `.get()`\n",
    "Read data from the requested resource (The Socrata API Endpoint)\n",
    "\n",
    "-  `.datasets()`\n",
    "Returns the list of datasets associated with a particular domain (Discovery API)\n",
    "\n",
    "- `.get_metadata()`\n",
    "Retrieve the metadata for a particular dataset (Metadata API)\n",
    "\n",
    "For a comprehensive list of other available APIs provided by Socrata, check out the [Other APIs](https://dev.socrata.com/docs/other/) page in the documentation."
   ]
  },
  {
   "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": "markdown",
   "metadata": {},
   "source": [
    "# 3. Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing libraries\n",
    "import pandas as pd\n",
    "import requests\n",
    "from sodapy import Socrata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Last updated: 2024-12-08 17:36:54\n",
      "\n",
      "Python implementation: CPython\n",
      "Python version       : 3.11.0\n",
      "IPython version      : 8.6.0\n",
      "\n",
      "pandas  : 1.5.1\n",
      "requests: 2.28.1\n",
      "sodapy  : 2.2.0\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# documention for installing watermark: https://github.com/rasbt/watermark, performed for reproducibility\n",
    "%reload_ext watermark\n",
    "%watermark -u -t -d -v -p pandas,requests,sodapy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Sodapy Socrata Class "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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](https://dev.socrata.com/docs/app-tokens.html) when making API requests."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:root:Requests made without an app_token will be subject to strict throttling limits.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<sodapy.socrata.Socrata object at 0x164234510>\n"
     ]
    }
   ],
   "source": [
    "# implementation in sodapy\n",
    "\n",
    "# source domain for NYC Open Data on Socrata\n",
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "\n",
    "# Socrata(): The main class that interacts with the SODA API.\n",
    "\n",
    "# the required arguments are:\n",
    "#     domain: the domain you wish you to access\n",
    "#     app_token: your Socrata application token\n",
    "# simple requests are possible without an app_token, though these\n",
    "# requests will be rate-limited.\n",
    "\n",
    "# initialize client\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# no app token is passed (refer to warning about throttling), and the timeout limit is 100 seconds\n",
    "\n",
    "# examine object\n",
    "print(client)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "type: <class 'sodapy.socrata.Socrata'>\n"
     ]
    }
   ],
   "source": [
    "# print information about the Socrata object\n",
    "print(f'type: {type(client)}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "domain: data.cityofnewyork.us\n",
      "session: <requests.sessions.Session object at 0x164234450>\n",
      "uri_prefix: https://\n",
      "timeout: 100\n"
     ]
    }
   ],
   "source": [
    "# print attributes of object\n",
    "for key, value in client.__dict__.items():\n",
    "    print(f'{key}: {value}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5. Socrata APIs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  5.1 Socrata Open Data API\n",
    "[Socrata's Open Data API](https://dev.socrata.com/docs/endpoints).\n",
    "\n",
    "From [the docs](https://dev.socrata.com/docs/endpoints):\n",
    ">The “endpoint” of a SODA API is simply a unique URL that represents an object or collection of objects. Every Socrata dataset, and even every individual data record, has its own endpoint. The endpoint is what you’ll point your HTTP client at to interact with data resources.\n",
    ">\n",
    ">All resources are accessed through a common base path of /resource/ along with their dataset identifier."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In Sodapy, we use the `.get()` method to read data from the requested resource. Options for content_type are JSON, CSV, and XML. This method performs a get request on these type of URLs: https://data.cityofnewyork.us/resource/erm2-nwe9.json?$limit=5."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Socrata API URL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dataset: DEP Green Infrastructure https://data.cityofnewyork.us/Environment/DEP-Green-Infrastructure/spjh-pz7h\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **Notice the 1,000 row limit**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (1000, 30)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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</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>{'type': 'Point', 'coordinates': [-73.81228577...</td>\n",
       "      <td>94012</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>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</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>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</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>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</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  \\\n",
       "0  {'type': 'Point', 'coordinates': [-73.81167623...     94002    1A   \n",
       "1  {'type': 'Point', 'coordinates': [-73.81228577...     94012  GS6A   \n",
       "2  {'type': 'Point', 'coordinates': [-73.81223444...     94017  GS8C   \n",
       "3  {'type': 'Point', 'coordinates': [-73.81205974...     94019  GS8E   \n",
       "4  {'type': 'Point', 'coordinates': [-73.81310191...     94021   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)  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                 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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url = 'https://data.cityofnewyork.us/resource/bs59-f3nu.json'\n",
    "df = pd.read_json(url)\n",
    "\n",
    "# preview data\n",
    "print(f\"shape of data: {df.shape}\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 30 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   the_geom    1000 non-null   object \n",
      " 1   asset_id    1000 non-null   int64  \n",
      " 2   gi_id       1000 non-null   object \n",
      " 3   dep_contra  1000 non-null   object \n",
      " 4   dep_cont_1  1000 non-null   int64  \n",
      " 5   row_onsite  1000 non-null   object \n",
      " 6   project_na  1000 non-null   object \n",
      " 7   asset_type  1000 non-null   object \n",
      " 8   status      1000 non-null   object \n",
      " 9   asset_x_co  1000 non-null   float64\n",
      " 10  asset_y_co  1000 non-null   float64\n",
      " 11  borough     1000 non-null   object \n",
      " 12  sewer_type  1000 non-null   object \n",
      " 13  outfall     1000 non-null   object \n",
      " 14  nyc_waters  1000 non-null   object \n",
      " 15  bbl         1000 non-null   int64  \n",
      " 16  secondary_  1000 non-null   int64  \n",
      " 17  community_  1000 non-null   int64  \n",
      " 18  city_counc  1000 non-null   int64  \n",
      " 19  assembly_d  1000 non-null   int64  \n",
      " 20  asset_leng  1000 non-null   float64\n",
      " 21  asset_widt  1000 non-null   float64\n",
      " 22  asset_area  1000 non-null   float64\n",
      " 23  gi_feature  900 non-null    object \n",
      " 24  tree_latin  994 non-null    object \n",
      " 25  tree_commo  994 non-null    object \n",
      " 26  constructi  994 non-null    object \n",
      " 27  construc_1  994 non-null    object \n",
      " 28  program_ar  1000 non-null   object \n",
      " 29  status_gro  1000 non-null   object \n",
      "dtypes: float64(5), int64(7), object(18)\n",
      "memory usage: 234.5+ KB\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **Use the `limit` parameter to query full dataset**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (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</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>{'type': 'Point', 'coordinates': [-73.81228577...</td>\n",
       "      <td>94012</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>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</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>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</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>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</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  \\\n",
       "0  {'type': 'Point', 'coordinates': [-73.81167623...     94002    1A   \n",
       "1  {'type': 'Point', 'coordinates': [-73.81228577...     94012  GS6A   \n",
       "2  {'type': 'Point', 'coordinates': [-73.81223444...     94017  GS8C   \n",
       "3  {'type': 'Point', 'coordinates': [-73.81205974...     94019  GS8E   \n",
       "4  {'type': 'Point', 'coordinates': [-73.81310191...     94021   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)  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                 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": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# increase limit, if number of rows equals limit, it's not the full dataset\n",
    "limit = '$limit=50000'\n",
    "\n",
    "url = f'https://data.cityofnewyork.us/resource/bs59-f3nu.json?{limit}'\n",
    "df = pd.read_json(url)\n",
    "\n",
    "# preview data\n",
    "print(f\"shape of data: {df.shape}\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can wrap this in a function to guarantee we fetch the full dataset\n",
    "\n",
    "def query_data(dataset_id, limit):\n",
    "    if not dataset_id or not limit:\n",
    "        raise ValueError(\"Both 'dataset_id' and 'limit' are required parameters.\")\n",
    "    \n",
    "    # Construct URL\n",
    "    url = f'https://data.cityofnewyork.us/resource/{dataset_id}.json?$limit={limit}'\n",
    "    \n",
    "    # Fetch the data as a pandas dataframe\n",
    "    df = pd.read_json(url)                \n",
    "    rows = df.shape[0]\n",
    "    \n",
    "    # If the number of rows equals the limit, inform the user\n",
    "    if rows == limit:\n",
    "        print(f\"Warning: The number of rows returned is equal to the specified limit ({limit}).\")\n",
    "        print(\"Please adjust the 'limit' argument and try again.\")\n",
    "        return None\n",
    "    else:\n",
    "        return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Warning: The number of rows returned is equal to the specified limit (20).\n",
      "Please adjust the 'limit' argument and try again.\n"
     ]
    }
   ],
   "source": [
    "df = query_data('bs59-f3nu', 20)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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</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>{'type': 'Point', 'coordinates': [-73.81228577...</td>\n",
       "      <td>94012</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>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</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>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</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>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</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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14356</th>\n",
       "      <td>{'type': 'Point', 'coordinates': [-73.95116666...</td>\n",
       "      <td>190910</td>\n",
       "      <td>K197-01STa</td>\n",
       "      <td>GK-S-K197</td>\n",
       "      <td>OS</td>\n",
       "      <td>Onsite</td>\n",
       "      <td>P.S. 197 - BROOKLYN</td>\n",
       "      <td>Synthetic Turf Field Storage Layer</td>\n",
       "      <td>90% Design Submitted</td>\n",
       "      <td>9.978084e+05</td>\n",
       "      <td>...</td>\n",
       "      <td>131.5</td>\n",
       "      <td>55.0</td>\n",
       "      <td>7067.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>Final Design</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14357</th>\n",
       "      <td>{'type': 'Point', 'coordinates': [-73.93711834...</td>\n",
       "      <td>190911</td>\n",
       "      <td>M096-01STa</td>\n",
       "      <td>GM-S-M096</td>\n",
       "      <td>OS</td>\n",
       "      <td>Onsite</td>\n",
       "      <td>P.S. 96M</td>\n",
       "      <td>Synthetic Turf Field Storage Layer</td>\n",
       "      <td>100% Design Submitted</td>\n",
       "      <td>1.001660e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>58.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>4101.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>Final Design</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14358</th>\n",
       "      <td>{'type': 'Point', 'coordinates': [-73.90555555...</td>\n",
       "      <td>190912</td>\n",
       "      <td>Q084-01STb</td>\n",
       "      <td>GQ-S-084</td>\n",
       "      <td>OS</td>\n",
       "      <td>Onsite</td>\n",
       "      <td>P.S. 84 Q</td>\n",
       "      <td>Synthetic Turf Field Storage Layer</td>\n",
       "      <td>Constructed</td>\n",
       "      <td>1.010410e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>102.0</td>\n",
       "      <td>32.2</td>\n",
       "      <td>3288.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>14359</th>\n",
       "      <td>{'type': 'Point', 'coordinates': [-73.95338888...</td>\n",
       "      <td>190913</td>\n",
       "      <td>K152-01STa</td>\n",
       "      <td>GK-S-152</td>\n",
       "      <td>OS</td>\n",
       "      <td>Onsite</td>\n",
       "      <td>P.S. 315 / 152 K</td>\n",
       "      <td>Synthetic Turf Field Storage Layer</td>\n",
       "      <td>Constructed</td>\n",
       "      <td>9.971874e+05</td>\n",
       "      <td>...</td>\n",
       "      <td>130.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>6435.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>14360</th>\n",
       "      <td>{'type': 'Point', 'coordinates': [-73.91524999...</td>\n",
       "      <td>190914</td>\n",
       "      <td>K377-01STb</td>\n",
       "      <td>OSNC15-TBD2</td>\n",
       "      <td>OS</td>\n",
       "      <td>Onsite</td>\n",
       "      <td>P.S. 377 K</td>\n",
       "      <td>Synthetic Turf Field Storage Layer</td>\n",
       "      <td>Constructed</td>\n",
       "      <td>1.007752e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>109.0</td>\n",
       "      <td>62.8</td>\n",
       "      <td>6841.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",
       "  </tbody>\n",
       "</table>\n",
       "<p>14361 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                the_geom  asset_id  \\\n",
       "0      {'type': 'Point', 'coordinates': [-73.81167623...     94002   \n",
       "1      {'type': 'Point', 'coordinates': [-73.81228577...     94012   \n",
       "2      {'type': 'Point', 'coordinates': [-73.81223444...     94017   \n",
       "3      {'type': 'Point', 'coordinates': [-73.81205974...     94019   \n",
       "4      {'type': 'Point', 'coordinates': [-73.81310191...     94021   \n",
       "...                                                  ...       ...   \n",
       "14356  {'type': 'Point', 'coordinates': [-73.95116666...    190910   \n",
       "14357  {'type': 'Point', 'coordinates': [-73.93711834...    190911   \n",
       "14358  {'type': 'Point', 'coordinates': [-73.90555555...    190912   \n",
       "14359  {'type': 'Point', 'coordinates': [-73.95338888...    190913   \n",
       "14360  {'type': 'Point', 'coordinates': [-73.91524999...    190914   \n",
       "\n",
       "            gi_id   dep_contra dep_cont_1 row_onsite           project_na  \\\n",
       "0              1A    GQJA03-02          2        ROW  DDC JAM-003 Phase 2   \n",
       "1            GS6A    GQJA03-02          2        ROW  DDC JAM-003 Phase 2   \n",
       "2            GS8C    GQJA03-02          2        ROW  DDC JAM-003 Phase 2   \n",
       "3            GS8E    GQJA03-02          2        ROW  DDC JAM-003 Phase 2   \n",
       "4             10A    GQJA03-02          2        ROW  DDC JAM-003 Phase 2   \n",
       "...           ...          ...        ...        ...                  ...   \n",
       "14356  K197-01STa    GK-S-K197         OS     Onsite  P.S. 197 - BROOKLYN   \n",
       "14357  M096-01STa    GM-S-M096         OS     Onsite             P.S. 96M   \n",
       "14358  Q084-01STb     GQ-S-084         OS     Onsite            P.S. 84 Q   \n",
       "14359  K152-01STa     GK-S-152         OS     Onsite     P.S. 315 / 152 K   \n",
       "14360  K377-01STb  OSNC15-TBD2         OS     Onsite           P.S. 377 K   \n",
       "\n",
       "                               asset_type                          status  \\\n",
       "0                                    ROWB  Constructed (Full Maintenance)   \n",
       "1                                   ROWGS  Constructed (Full Maintenance)   \n",
       "2                                   ROWGS  Constructed (Full Maintenance)   \n",
       "3                                   ROWGS  Constructed (Full Maintenance)   \n",
       "4                                    ROWB  Constructed (Full Maintenance)   \n",
       "...                                   ...                             ...   \n",
       "14356  Synthetic Turf Field Storage Layer            90% Design Submitted   \n",
       "14357  Synthetic Turf Field Storage Layer           100% Design Submitted   \n",
       "14358  Synthetic Turf Field Storage Layer                     Constructed   \n",
       "14359  Synthetic Turf Field Storage Layer                     Constructed   \n",
       "14360  Synthetic Turf Field Storage Layer                     Constructed   \n",
       "\n",
       "         asset_x_co  ...  asset_leng asset_widt asset_area gi_feature  \\\n",
       "0      1.036475e+06  ...        17.0        5.0       85.0   Standard   \n",
       "1      1.036305e+06  ...        13.0        3.5       45.5        NaN   \n",
       "2      1.036319e+06  ...        20.0        3.5       70.0        NaN   \n",
       "3      1.036368e+06  ...        20.0        3.5       70.0        NaN   \n",
       "4      1.036079e+06  ...        13.0        4.0       52.0   Standard   \n",
       "...             ...  ...         ...        ...        ...        ...   \n",
       "14356  9.978084e+05  ...       131.5       55.0     7067.0        NaN   \n",
       "14357  1.001660e+06  ...        58.0       66.0     4101.0        NaN   \n",
       "14358  1.010410e+06  ...       102.0       32.2     3288.0        NaN   \n",
       "14359  9.971874e+05  ...       130.0       50.0     6435.0        NaN   \n",
       "14360  1.007752e+06  ...       109.0       62.8     6841.0        NaN   \n",
       "\n",
       "                tree_latin          tree_commo  constructi  construc_1  \\\n",
       "0      Chionanthus retusus  Chinese Fringetree   GCJA03-2A   Package-1   \n",
       "1                  No Tree                 N/A   GCJA03-2A   Package-1   \n",
       "2                  No Tree                 N/A   GCJA03-2A   Package-1   \n",
       "3                  No Tree                 N/A   GCJA03-2A   Package-1   \n",
       "4        Quercus palustris             Pin Oak   GCJA03-2A   Package-1   \n",
       "...                    ...                 ...         ...         ...   \n",
       "14356                  NaN                 NaN         NaN         NaN   \n",
       "14357                  NaN                 NaN         NaN         NaN   \n",
       "14358                  NaN                 NaN         NaN         NaN   \n",
       "14359                  NaN                 NaN         NaN         NaN   \n",
       "14360                  NaN                 NaN         NaN         NaN   \n",
       "\n",
       "               program_ar    status_gro  \n",
       "0      Right of Way (ROW)   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",
       "14356       Public Onsite  Final Design  \n",
       "14357       Public Onsite  Final Design  \n",
       "14358       Public Onsite   Constructed  \n",
       "14359       Public Onsite   Constructed  \n",
       "14360       Public Onsite   Constructed  \n",
       "\n",
       "[14361 rows x 30 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = query_data('bs59-f3nu', 50_000)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (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</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>{'type': 'Point', 'coordinates': [-73.81228577...</td>\n",
       "      <td>94012</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>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</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>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</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>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</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  \\\n",
       "0  {'type': 'Point', 'coordinates': [-73.81167623...     94002    1A   \n",
       "1  {'type': 'Point', 'coordinates': [-73.81228577...     94012  GS6A   \n",
       "2  {'type': 'Point', 'coordinates': [-73.81223444...     94017  GS8C   \n",
       "3  {'type': 'Point', 'coordinates': [-73.81205974...     94019  GS8E   \n",
       "4  {'type': 'Point', 'coordinates': [-73.81310191...     94021   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)  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                 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": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview data from above query\n",
    "print(f\"shape of data: {df.shape}\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Sodapy"
   ]
  },
  {
   "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": [
      "<class 'list'>\n"
     ]
    }
   ],
   "source": [
    "# source domain for NYC Open Data on Socrata\n",
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "\n",
    "# green infrastructure dataset identifier\n",
    "socrata_dataset_identifier = 'bs59-f3nu'\n",
    "\n",
    "# initialize client\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=1000\n",
    ")\n",
    "\n",
    "# get data for the 311 dataset, limit to 50_000 rows\n",
    "# notice the limit parameter\n",
    "data = client.get(socrata_dataset_identifier, limit=50_000)\n",
    "\n",
    "# identify type of object returned\n",
    "print(type(data))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'the_geom': {'type': 'Point',\n",
       "  'coordinates': [-73.81167623024226, 40.69138622900597]},\n",
       " 'asset_id': '94002.0',\n",
       " 'gi_id': '1A',\n",
       " 'dep_contra': 'GQJA03-02',\n",
       " 'dep_cont_1': '2',\n",
       " 'row_onsite': 'ROW',\n",
       " 'project_na': 'DDC JAM-003 Phase 2',\n",
       " 'asset_type': 'ROWB',\n",
       " 'status': 'Constructed (Full Maintenance)',\n",
       " 'asset_x_co': '1036475.27735',\n",
       " 'asset_y_co': '191223.227',\n",
       " 'borough': 'Queens',\n",
       " 'sewer_type': 'Combined',\n",
       " 'outfall': 'JAM-003',\n",
       " 'nyc_waters': 'Jamaica Bay and Tributaries',\n",
       " 'bbl': '4095890001.0',\n",
       " 'secondary_': '0.0',\n",
       " 'community_': '410.0',\n",
       " 'city_counc': '28.0',\n",
       " 'assembly_d': '32.0',\n",
       " 'asset_leng': '17.0',\n",
       " 'asset_widt': '5.0',\n",
       " 'asset_area': '85.0',\n",
       " 'gi_feature': 'Standard',\n",
       " 'tree_latin': 'Chionanthus retusus',\n",
       " 'tree_commo': 'Chinese Fringetree',\n",
       " 'constructi': 'GCJA03-2A',\n",
       " 'construc_1': 'Package-1',\n",
       " 'program_ar': 'Right of Way (ROW)',\n",
       " 'status_gro': 'Constructed'}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview first element in list, this is one record\n",
    "data[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "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": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert list to a df\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# sanity check\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example with the QUERY parameter.\n",
    "\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": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of data: (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": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SoQL implementation with sodapy\n",
    "# SoQL query string below:\n",
    "# retrieve all columns and limit our records to 50000\n",
    "\n",
    "# green infrastructure dataset identifier\n",
    "socrata_dataset_identifier = 'bs59-f3nu'\n",
    "\n",
    "query = \"\"\"\n",
    "    SELECT *\n",
    "    LIMIT 50000\n",
    "\"\"\"\n",
    "\n",
    "# returned as JSON from API / converted to Python list of dictionaries by sodapy\n",
    "# notice the query parameter\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "\n",
    "# convert to pandas DataFrame\n",
    "results_df = pd.DataFrame.from_records(results)\n",
    "\n",
    "# sanity check\n",
    "print(f'shape of data: {results_df.shape}')\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.2  Discovery API\n",
    "Socrata's [Discovery API](https://dev.socrata.com/docs/other/discovery#?route=overview)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In Sodapy, we use the `.datasets()` method: Returns the list of datasets associated with a particular domain.\n",
    "WARNING: Large limits (>1000) will return megabytes of data, which can be slow on low-bandwidth networks, and is also a lot of data to hold in memory. This method performs a get request on these type of URLs: https://data.cityofnewyork.us/api/catalog/v1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Socrata API URL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['results', 'resultSetSize', 'timings', 'warnings'])"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Discovery API\n",
    "url = 'https://api.us.socrata.com/api/catalog/v1?search_context=data.ny.gov&limit=30000'\n",
    "\n",
    "# fetch the JSON data from the web\n",
    "response = requests.get(url)\n",
    "\n",
    "# parse the JSON response\n",
    "data_dict = response.json() \n",
    "\n",
    "# preview keys    \n",
    "data_dict.keys()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'resource': {'name': 'Lottery Cash 4 Life Winning Numbers: Beginning 2014',\n",
       "  'id': 'kwxv-fwze',\n",
       "  'resource_name': None,\n",
       "  'parent_fxf': [],\n",
       "  'description': 'Go to http://on.ny.gov/1xRIvPz on the New York Lottery website for past Cash 4 Life results and payouts.',\n",
       "  'attribution': 'New York State Gaming Commission',\n",
       "  'attribution_link': 'http://nylottery.ny.gov/wps/portal/Home/Lottery/home/your+lottery/drawing+results/drawingresults_cash4life',\n",
       "  'contact_email': 'opendata@its.ny.gov',\n",
       "  'type': 'dataset',\n",
       "  'updatedAt': '2024-12-08T11:01:28.000Z',\n",
       "  'createdAt': '2014-06-17T19:47:54.000Z',\n",
       "  'metadata_updated_at': '2024-12-08T11:01:27.000Z',\n",
       "  'data_updated_at': '2024-12-08T11:01:28.000Z',\n",
       "  'page_views': {'page_views_last_week': 609,\n",
       "   'page_views_last_month': 2197,\n",
       "   'page_views_total': 5933309,\n",
       "   'page_views_last_week_log': 9.25266543245025,\n",
       "   'page_views_last_month_log': 11.101975670949232,\n",
       "   'page_views_total_log': 22.500405731151073},\n",
       "  'columns_name': ['Cash Ball', 'Draw Date', 'Winning Numbers'],\n",
       "  'columns_field_name': ['cash_ball', 'draw_date', 'winning_numbers'],\n",
       "  'columns_datatype': ['Text', 'Calendar date', 'Text'],\n",
       "  'columns_description': ['Cash ball', 'Draw date', 'Winning numbers'],\n",
       "  'columns_format': [{'align': 'center'},\n",
       "   {'view': 'date', 'align': 'center'},\n",
       "   {'align': 'center'}],\n",
       "  'download_count': 231573,\n",
       "  'provenance': 'official',\n",
       "  'lens_view_type': 'tabular',\n",
       "  'lens_display_type': 'table',\n",
       "  'locked': False,\n",
       "  'blob_mime_type': None,\n",
       "  'hide_from_data_json': False,\n",
       "  'publication_date': '2021-04-27T14:13:45.000Z'},\n",
       " 'classification': {'categories': [],\n",
       "  'tags': [],\n",
       "  'domain_category': 'Government & Finance',\n",
       "  'domain_tags': ['cash 4 life', 'new york lottery', 'results', 'winning'],\n",
       "  'domain_metadata': [{'key': 'Common-Core_Publisher',\n",
       "    'value': 'State of New York'},\n",
       "   {'key': 'Common-Core_Contact-Name', 'value': 'Open Data NY'},\n",
       "   {'key': 'Common-Core_Contact-Email', 'value': 'opendata@its.ny.gov'},\n",
       "   {'key': 'Additional-Resources_See-Also',\n",
       "    'value': 'http://www.gaming.ny.gov/'},\n",
       "   {'key': 'Dataset-Summary_Dataset-Owner',\n",
       "    'value': 'New York State Gaming Commission'},\n",
       "   {'key': 'Dataset-Summary_Contact-Information',\n",
       "    'value': 'Info@gaming.ny.gov'},\n",
       "   {'key': 'Dataset-Summary_Granularity', 'value': 'By draw'},\n",
       "   {'key': 'Dataset-Summary_Coverage', 'value': 'Statewide'},\n",
       "   {'key': 'Dataset-Summary_Data-Frequency',\n",
       "    'value': 'Daily beginning 7/1/19; twice weekly previously'},\n",
       "   {'key': 'Dataset-Summary_Posting-Frequency', 'value': 'Daily'},\n",
       "   {'key': 'Dataset-Summary_Organization', 'value': 'The New York Lottery'},\n",
       "   {'key': 'Dataset-Summary_Time-Period', 'value': 'Beginning 6/16/2014'},\n",
       "   {'key': 'Dataset-Information_Agency',\n",
       "    'value': 'Gaming Commission, New York State'},\n",
       "   {'key': 'Notes_Notes',\n",
       "    'value': \"The information contained on these pages is believed to be accurate. In the event of a discrepancy between the information displayed on this Web site concerning winning numbers and payouts and the information contained in the official and certified files maintained by the New York Lottery's Drawing Unit, those maintained by the Drawing Unit shall prevail.\"}]},\n",
       " 'metadata': {'domain': 'data.ny.gov'},\n",
       " 'permalink': 'https://data.ny.gov/d/kwxv-fwze',\n",
       " 'link': 'https://data.ny.gov/Government-Finance/Lottery-Cash-4-Life-Winning-Numbers-Beginning-2014/kwxv-fwze',\n",
       " 'owner': {'id': 'xzik-pf59',\n",
       "  'user_type': 'interactive',\n",
       "  'display_name': 'NY Open Data'},\n",
       " 'creator': {'id': 'xzik-pf59',\n",
       "  'user_type': 'interactive',\n",
       "  'display_name': 'NY Open Data'}}"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview results key, first element\n",
    "data_dict['results'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(5455, 8)\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>resource</th>\n",
       "      <th>classification</th>\n",
       "      <th>metadata</th>\n",
       "      <th>permalink</th>\n",
       "      <th>link</th>\n",
       "      <th>owner</th>\n",
       "      <th>creator</th>\n",
       "      <th>preview_image_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'name': 'Lottery Cash 4 Life Winning Numbers:...</td>\n",
       "      <td>{'categories': [], 'tags': [], 'domain_categor...</td>\n",
       "      <td>{'domain': 'data.ny.gov'}</td>\n",
       "      <td>https://data.ny.gov/d/kwxv-fwze</td>\n",
       "      <td>https://data.ny.gov/Government-Finance/Lottery...</td>\n",
       "      <td>{'id': 'xzik-pf59', 'user_type': 'interactive'...</td>\n",
       "      <td>{'id': 'xzik-pf59', 'user_type': 'interactive'...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'name': 'For Hire Vehicles (FHV) - Active', '...</td>\n",
       "      <td>{'categories': [], 'tags': [], 'domain_categor...</td>\n",
       "      <td>{'domain': 'data.cityofnewyork.us'}</td>\n",
       "      <td>https://data.cityofnewyork.us/d/8wbx-tsch</td>\n",
       "      <td>https://data.cityofnewyork.us/Transportation/F...</td>\n",
       "      <td>{'id': '5fuc-pqz2', 'user_type': 'interactive'...</td>\n",
       "      <td>{'id': '5fuc-pqz2', 'user_type': 'interactive'...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'name': 'Civil Service List (Active)', 'id': ...</td>\n",
       "      <td>{'categories': [], 'tags': [], 'domain_categor...</td>\n",
       "      <td>{'domain': 'data.cityofnewyork.us'}</td>\n",
       "      <td>https://data.cityofnewyork.us/d/vx8i-nprf</td>\n",
       "      <td>https://data.cityofnewyork.us/City-Government/...</td>\n",
       "      <td>{'id': '5fuc-pqz2', 'user_type': 'interactive'...</td>\n",
       "      <td>{'id': '5fuc-pqz2', 'user_type': 'interactive'...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'name': 'DOB Job Application Filings', 'id': ...</td>\n",
       "      <td>{'categories': [], 'tags': [], 'domain_categor...</td>\n",
       "      <td>{'domain': 'data.cityofnewyork.us'}</td>\n",
       "      <td>https://data.cityofnewyork.us/d/ic3t-wcy2</td>\n",
       "      <td>https://data.cityofnewyork.us/Housing-Developm...</td>\n",
       "      <td>{'id': '5fuc-pqz2', 'user_type': 'interactive'...</td>\n",
       "      <td>{'id': '5fuc-pqz2', 'user_type': 'interactive'...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'name': 'Medicaid Enrolled Provider Listing',...</td>\n",
       "      <td>{'categories': [], 'tags': [], 'domain_categor...</td>\n",
       "      <td>{'domain': 'health.data.ny.gov'}</td>\n",
       "      <td>https://health.data.ny.gov/d/keti-qx5t</td>\n",
       "      <td>https://health.data.ny.gov/Health/Medicaid-Enr...</td>\n",
       "      <td>{'id': 's9j2-nqmr', 'user_type': 'interactive'...</td>\n",
       "      <td>{'id': 's9j2-nqmr', 'user_type': 'interactive'...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            resource  \\\n",
       "0  {'name': 'Lottery Cash 4 Life Winning Numbers:...   \n",
       "1  {'name': 'For Hire Vehicles (FHV) - Active', '...   \n",
       "2  {'name': 'Civil Service List (Active)', 'id': ...   \n",
       "3  {'name': 'DOB Job Application Filings', 'id': ...   \n",
       "4  {'name': 'Medicaid Enrolled Provider Listing',...   \n",
       "\n",
       "                                      classification  \\\n",
       "0  {'categories': [], 'tags': [], 'domain_categor...   \n",
       "1  {'categories': [], 'tags': [], 'domain_categor...   \n",
       "2  {'categories': [], 'tags': [], 'domain_categor...   \n",
       "3  {'categories': [], 'tags': [], 'domain_categor...   \n",
       "4  {'categories': [], 'tags': [], 'domain_categor...   \n",
       "\n",
       "                              metadata  \\\n",
       "0            {'domain': 'data.ny.gov'}   \n",
       "1  {'domain': 'data.cityofnewyork.us'}   \n",
       "2  {'domain': 'data.cityofnewyork.us'}   \n",
       "3  {'domain': 'data.cityofnewyork.us'}   \n",
       "4     {'domain': 'health.data.ny.gov'}   \n",
       "\n",
       "                                   permalink  \\\n",
       "0            https://data.ny.gov/d/kwxv-fwze   \n",
       "1  https://data.cityofnewyork.us/d/8wbx-tsch   \n",
       "2  https://data.cityofnewyork.us/d/vx8i-nprf   \n",
       "3  https://data.cityofnewyork.us/d/ic3t-wcy2   \n",
       "4     https://health.data.ny.gov/d/keti-qx5t   \n",
       "\n",
       "                                                link  \\\n",
       "0  https://data.ny.gov/Government-Finance/Lottery...   \n",
       "1  https://data.cityofnewyork.us/Transportation/F...   \n",
       "2  https://data.cityofnewyork.us/City-Government/...   \n",
       "3  https://data.cityofnewyork.us/Housing-Developm...   \n",
       "4  https://health.data.ny.gov/Health/Medicaid-Enr...   \n",
       "\n",
       "                                               owner  \\\n",
       "0  {'id': 'xzik-pf59', 'user_type': 'interactive'...   \n",
       "1  {'id': '5fuc-pqz2', 'user_type': 'interactive'...   \n",
       "2  {'id': '5fuc-pqz2', 'user_type': 'interactive'...   \n",
       "3  {'id': '5fuc-pqz2', 'user_type': 'interactive'...   \n",
       "4  {'id': 's9j2-nqmr', 'user_type': 'interactive'...   \n",
       "\n",
       "                                             creator preview_image_url  \n",
       "0  {'id': 'xzik-pf59', 'user_type': 'interactive'...               NaN  \n",
       "1  {'id': '5fuc-pqz2', 'user_type': 'interactive'...               NaN  \n",
       "2  {'id': '5fuc-pqz2', 'user_type': 'interactive'...               NaN  \n",
       "3  {'id': '5fuc-pqz2', 'user_type': 'interactive'...               NaN  \n",
       "4  {'id': 's9j2-nqmr', 'user_type': 'interactive'...               NaN  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert into df\n",
    "df = pd.DataFrame.from_records(data_dict['results'])\n",
    "\n",
    "# sanity check\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is not our final dataframe that contains our data in tabular format. Our information most likely is located in the `resource` column, but let's confirm."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Briefly review the other keys."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'name': 'Lottery Cash 4 Life Winning Numbers: Beginning 2014',\n",
       " 'id': 'kwxv-fwze',\n",
       " 'resource_name': None,\n",
       " 'parent_fxf': [],\n",
       " 'description': 'Go to http://on.ny.gov/1xRIvPz on the New York Lottery website for past Cash 4 Life results and payouts.',\n",
       " 'attribution': 'New York State Gaming Commission',\n",
       " 'attribution_link': 'http://nylottery.ny.gov/wps/portal/Home/Lottery/home/your+lottery/drawing+results/drawingresults_cash4life',\n",
       " 'contact_email': 'opendata@its.ny.gov',\n",
       " 'type': 'dataset',\n",
       " 'updatedAt': '2024-12-08T11:01:28.000Z',\n",
       " 'createdAt': '2014-06-17T19:47:54.000Z',\n",
       " 'metadata_updated_at': '2024-12-08T11:01:27.000Z',\n",
       " 'data_updated_at': '2024-12-08T11:01:28.000Z',\n",
       " 'page_views': {'page_views_last_week': 609,\n",
       "  'page_views_last_month': 2197,\n",
       "  'page_views_total': 5933309,\n",
       "  'page_views_last_week_log': 9.25266543245025,\n",
       "  'page_views_last_month_log': 11.101975670949232,\n",
       "  'page_views_total_log': 22.500405731151073},\n",
       " 'columns_name': ['Cash Ball', 'Draw Date', 'Winning Numbers'],\n",
       " 'columns_field_name': ['cash_ball', 'draw_date', 'winning_numbers'],\n",
       " 'columns_datatype': ['Text', 'Calendar date', 'Text'],\n",
       " 'columns_description': ['Cash ball', 'Draw date', 'Winning numbers'],\n",
       " 'columns_format': [{'align': 'center'},\n",
       "  {'view': 'date', 'align': 'center'},\n",
       "  {'align': 'center'}],\n",
       " 'download_count': 231573,\n",
       " 'provenance': 'official',\n",
       " 'lens_view_type': 'tabular',\n",
       " 'lens_display_type': 'table',\n",
       " 'locked': False,\n",
       " 'blob_mime_type': None,\n",
       " 'hide_from_data_json': False,\n",
       " 'publication_date': '2021-04-27T14:13:45.000Z'}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first element in our results list, preview resources key\n",
    "data_dict['results'][0]['resource']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'categories': [],\n",
       " 'tags': [],\n",
       " 'domain_category': 'Government & Finance',\n",
       " 'domain_tags': ['cash 4 life', 'new york lottery', 'results', 'winning'],\n",
       " 'domain_metadata': [{'key': 'Common-Core_Publisher',\n",
       "   'value': 'State of New York'},\n",
       "  {'key': 'Common-Core_Contact-Name', 'value': 'Open Data NY'},\n",
       "  {'key': 'Common-Core_Contact-Email', 'value': 'opendata@its.ny.gov'},\n",
       "  {'key': 'Additional-Resources_See-Also',\n",
       "   'value': 'http://www.gaming.ny.gov/'},\n",
       "  {'key': 'Dataset-Summary_Dataset-Owner',\n",
       "   'value': 'New York State Gaming Commission'},\n",
       "  {'key': 'Dataset-Summary_Contact-Information',\n",
       "   'value': 'Info@gaming.ny.gov'},\n",
       "  {'key': 'Dataset-Summary_Granularity', 'value': 'By draw'},\n",
       "  {'key': 'Dataset-Summary_Coverage', 'value': 'Statewide'},\n",
       "  {'key': 'Dataset-Summary_Data-Frequency',\n",
       "   'value': 'Daily beginning 7/1/19; twice weekly previously'},\n",
       "  {'key': 'Dataset-Summary_Posting-Frequency', 'value': 'Daily'},\n",
       "  {'key': 'Dataset-Summary_Organization', 'value': 'The New York Lottery'},\n",
       "  {'key': 'Dataset-Summary_Time-Period', 'value': 'Beginning 6/16/2014'},\n",
       "  {'key': 'Dataset-Information_Agency',\n",
       "   'value': 'Gaming Commission, New York State'},\n",
       "  {'key': 'Notes_Notes',\n",
       "   'value': \"The information contained on these pages is believed to be accurate. In the event of a discrepancy between the information displayed on this Web site concerning winning numbers and payouts and the information contained in the official and certified files maintained by the New York Lottery's Drawing Unit, those maintained by the Drawing Unit shall prevail.\"}]}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first element in our results list, preview classification key\n",
    "data_dict['results'][0]['classification']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'domain': 'data.ny.gov'}"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first element in our results list, preview metadata key\n",
    "data_dict['results'][0]['metadata']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://data.ny.gov/d/kwxv-fwze'"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first element in our results list, preview permalink key\n",
    "data_dict['results'][0]['permalink']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://data.ny.gov/Government-Finance/Lottery-Cash-4-Life-Winning-Numbers-Beginning-2014/kwxv-fwze'"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first element in our results list, preview link key\n",
    "data_dict['results'][0]['link']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 'xzik-pf59', 'user_type': 'interactive', 'display_name': 'NY Open Data'}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first element in our results list, preview owner key\n",
    "data_dict['results'][0]['owner']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 'xzik-pf59', 'user_type': 'interactive', 'display_name': 'NY Open Data'}"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first element in our results list, preview creator key\n",
    "data_dict['results'][0]['creator']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After confirming data is in our resource key, let's unnest these values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       {'name': 'Lottery Cash 4 Life Winning Numbers:...\n",
       "1       {'name': 'For Hire Vehicles (FHV) - Active', '...\n",
       "2       {'name': 'Civil Service List (Active)', 'id': ...\n",
       "3       {'name': 'DOB Job Application Filings', 'id': ...\n",
       "4       {'name': 'Medicaid Enrolled Provider Listing',...\n",
       "                              ...                        \n",
       "5450    {'name': 'DCM_StreetCenterLine', 'id': 'g6zj-t...\n",
       "5451    {'name': 'nyedwi', 'id': 'y4qz-3s3n', 'resourc...\n",
       "5452    {'name': 'DCM_StreetNameChanges_Areas', 'id': ...\n",
       "5453    {'name': 'nymcwi', 'id': 'qssd-iewi', 'resourc...\n",
       "5454    {'name': 'nyfb', 'id': 'xzng-ft6f', 'resource_...\n",
       "Name: resource, Length: 5455, dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# retrieve information in the resource key\n",
    "df['resource']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(5455, 27)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>id</th>\n",
       "      <th>resource_name</th>\n",
       "      <th>parent_fxf</th>\n",
       "      <th>description</th>\n",
       "      <th>attribution</th>\n",
       "      <th>attribution_link</th>\n",
       "      <th>contact_email</th>\n",
       "      <th>type</th>\n",
       "      <th>updatedAt</th>\n",
       "      <th>...</th>\n",
       "      <th>columns_description</th>\n",
       "      <th>columns_format</th>\n",
       "      <th>download_count</th>\n",
       "      <th>provenance</th>\n",
       "      <th>lens_view_type</th>\n",
       "      <th>lens_display_type</th>\n",
       "      <th>locked</th>\n",
       "      <th>blob_mime_type</th>\n",
       "      <th>hide_from_data_json</th>\n",
       "      <th>publication_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Lottery Cash 4 Life Winning Numbers: Beginning...</td>\n",
       "      <td>kwxv-fwze</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>Go to http://on.ny.gov/1xRIvPz on the New York...</td>\n",
       "      <td>New York State Gaming Commission</td>\n",
       "      <td>http://nylottery.ny.gov/wps/portal/Home/Lotter...</td>\n",
       "      <td>opendata@its.ny.gov</td>\n",
       "      <td>dataset</td>\n",
       "      <td>2024-12-08T11:01:28.000Z</td>\n",
       "      <td>...</td>\n",
       "      <td>[Cash ball, Draw date, Winning numbers]</td>\n",
       "      <td>[{'align': 'center'}, {'view': 'date', 'align'...</td>\n",
       "      <td>231573</td>\n",
       "      <td>official</td>\n",
       "      <td>tabular</td>\n",
       "      <td>table</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-04-27T14:13:45.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>For Hire Vehicles (FHV) - Active</td>\n",
       "      <td>8wbx-tsch</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>&lt;b&gt;PLEASE NOTE:&lt;/b&gt; This dataset, which includ...</td>\n",
       "      <td>Taxi and Limousine Commission (TLC)</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>dataset</td>\n",
       "      <td>2024-12-08T20:03:01.000Z</td>\n",
       "      <td>...</td>\n",
       "      <td>[Reason Code A,B,C or G\\n, Permit License Numb...</td>\n",
       "      <td>[{'displayStyle': 'plain', 'align': 'left'}, {...</td>\n",
       "      <td>0</td>\n",
       "      <td>official</td>\n",
       "      <td>tabular</td>\n",
       "      <td>table</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-04-05T13:20:47.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Civil Service List (Active)</td>\n",
       "      <td>vx8i-nprf</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>A Civil Service List consists of all candidate...</td>\n",
       "      <td>Department of Citywide Administrative Services...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>dataset</td>\n",
       "      <td>2024-12-06T14:11:47.000Z</td>\n",
       "      <td>...</td>\n",
       "      <td>[A descriptive name that identifies a civil se...</td>\n",
       "      <td>[{'displayStyle': 'plain', 'align': 'left'}, {...</td>\n",
       "      <td>0</td>\n",
       "      <td>official</td>\n",
       "      <td>tabular</td>\n",
       "      <td>table</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-12T16:15:05.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DOB Job Application Filings</td>\n",
       "      <td>ic3t-wcy2</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>This dataset contains all job applications sub...</td>\n",
       "      <td>Department of Buildings (DOB)</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>dataset</td>\n",
       "      <td>2024-12-07T21:13:15.000Z</td>\n",
       "      <td>...</td>\n",
       "      <td>[3-digit identifier: Borough code = first posi...</td>\n",
       "      <td>[{'align': 'right'}, {'align': 'right'}, {'ali...</td>\n",
       "      <td>0</td>\n",
       "      <td>official</td>\n",
       "      <td>tabular</td>\n",
       "      <td>table</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-06-22T18:23:35.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Medicaid Enrolled Provider Listing</td>\n",
       "      <td>keti-qx5t</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>&lt;b&gt;Revalidation disclaimer&lt;/b&gt;:  The next anti...</td>\n",
       "      <td>New York State Department of Health</td>\n",
       "      <td>https://www.emedny.org/info/ProviderEnrollment...</td>\n",
       "      <td>None</td>\n",
       "      <td>dataset</td>\n",
       "      <td>2024-12-02T19:12:09.000Z</td>\n",
       "      <td>...</td>\n",
       "      <td>[The name of a provider of Medicaid services a...</td>\n",
       "      <td>[{'align': 'left'}, {'view': 'date'}, {}, {}, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>official</td>\n",
       "      <td>tabular</td>\n",
       "      <td>table</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-12-28T16:03:15.000Z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                name         id resource_name  \\\n",
       "0  Lottery Cash 4 Life Winning Numbers: Beginning...  kwxv-fwze          None   \n",
       "1                   For Hire Vehicles (FHV) - Active  8wbx-tsch          None   \n",
       "2                        Civil Service List (Active)  vx8i-nprf          None   \n",
       "3                        DOB Job Application Filings  ic3t-wcy2          None   \n",
       "4                 Medicaid Enrolled Provider Listing  keti-qx5t          None   \n",
       "\n",
       "  parent_fxf                                        description  \\\n",
       "0         []  Go to http://on.ny.gov/1xRIvPz on the New York...   \n",
       "1         []  <b>PLEASE NOTE:</b> This dataset, which includ...   \n",
       "2         []  A Civil Service List consists of all candidate...   \n",
       "3         []  This dataset contains all job applications sub...   \n",
       "4         []  <b>Revalidation disclaimer</b>:  The next anti...   \n",
       "\n",
       "                                         attribution  \\\n",
       "0                   New York State Gaming Commission   \n",
       "1                Taxi and Limousine Commission (TLC)   \n",
       "2  Department of Citywide Administrative Services...   \n",
       "3                      Department of Buildings (DOB)   \n",
       "4                New York State Department of Health   \n",
       "\n",
       "                                    attribution_link        contact_email  \\\n",
       "0  http://nylottery.ny.gov/wps/portal/Home/Lotter...  opendata@its.ny.gov   \n",
       "1                                               None                 None   \n",
       "2                                               None                 None   \n",
       "3                                               None                 None   \n",
       "4  https://www.emedny.org/info/ProviderEnrollment...                 None   \n",
       "\n",
       "      type                 updatedAt  ...  \\\n",
       "0  dataset  2024-12-08T11:01:28.000Z  ...   \n",
       "1  dataset  2024-12-08T20:03:01.000Z  ...   \n",
       "2  dataset  2024-12-06T14:11:47.000Z  ...   \n",
       "3  dataset  2024-12-07T21:13:15.000Z  ...   \n",
       "4  dataset  2024-12-02T19:12:09.000Z  ...   \n",
       "\n",
       "                                 columns_description  \\\n",
       "0            [Cash ball, Draw date, Winning numbers]   \n",
       "1  [Reason Code A,B,C or G\\n, Permit License Numb...   \n",
       "2  [A descriptive name that identifies a civil se...   \n",
       "3  [3-digit identifier: Borough code = first posi...   \n",
       "4  [The name of a provider of Medicaid services a...   \n",
       "\n",
       "                                      columns_format download_count  \\\n",
       "0  [{'align': 'center'}, {'view': 'date', 'align'...         231573   \n",
       "1  [{'displayStyle': 'plain', 'align': 'left'}, {...              0   \n",
       "2  [{'displayStyle': 'plain', 'align': 'left'}, {...              0   \n",
       "3  [{'align': 'right'}, {'align': 'right'}, {'ali...              0   \n",
       "4  [{'align': 'left'}, {'view': 'date'}, {}, {}, ...              0   \n",
       "\n",
       "  provenance lens_view_type lens_display_type locked blob_mime_type  \\\n",
       "0   official        tabular             table  False           None   \n",
       "1   official        tabular             table  False           None   \n",
       "2   official        tabular             table  False           None   \n",
       "3   official        tabular             table  False           None   \n",
       "4   official        tabular             table  False           None   \n",
       "\n",
       "  hide_from_data_json          publication_date  \n",
       "0               False  2021-04-27T14:13:45.000Z  \n",
       "1               False  2021-04-05T13:20:47.000Z  \n",
       "2               False  2024-01-12T16:15:05.000Z  \n",
       "3               False  2020-06-22T18:23:35.000Z  \n",
       "4               False  2020-12-28T16:03:15.000Z  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert to a dataframe\n",
    "df = pd.DataFrame.from_records(df['resource'])\n",
    "\n",
    "# sanity check\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5455 entries, 0 to 5454\n",
      "Data columns (total 27 columns):\n",
      " #   Column               Non-Null Count  Dtype \n",
      "---  ------               --------------  ----- \n",
      " 0   name                 5455 non-null   object\n",
      " 1   id                   5455 non-null   object\n",
      " 2   resource_name        4 non-null      object\n",
      " 3   parent_fxf           5455 non-null   object\n",
      " 4   description          5455 non-null   object\n",
      " 5   attribution          5260 non-null   object\n",
      " 6   attribution_link     2222 non-null   object\n",
      " 7   contact_email        644 non-null    object\n",
      " 8   type                 5455 non-null   object\n",
      " 9   updatedAt            5455 non-null   object\n",
      " 10  createdAt            5455 non-null   object\n",
      " 11  metadata_updated_at  5455 non-null   object\n",
      " 12  data_updated_at      5190 non-null   object\n",
      " 13  page_views           5455 non-null   object\n",
      " 14  columns_name         5455 non-null   object\n",
      " 15  columns_field_name   5455 non-null   object\n",
      " 16  columns_datatype     5455 non-null   object\n",
      " 17  columns_description  5455 non-null   object\n",
      " 18  columns_format       5455 non-null   object\n",
      " 19  download_count       5455 non-null   int64 \n",
      " 20  provenance           5455 non-null   object\n",
      " 21  lens_view_type       5455 non-null   object\n",
      " 22  lens_display_type    5455 non-null   object\n",
      " 23  locked               5455 non-null   bool  \n",
      " 24  blob_mime_type       367 non-null    object\n",
      " 25  hide_from_data_json  5455 non-null   bool  \n",
      " 26  publication_date     5423 non-null   object\n",
      "dtypes: bool(2), int64(1), object(24)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <td>Lottery Cash 4 Life Winning Numbers: Beginning...</td>\n",
       "      <td>For Hire Vehicles (FHV) - Active</td>\n",
       "      <td>Civil Service List (Active)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>kwxv-fwze</td>\n",
       "      <td>8wbx-tsch</td>\n",
       "      <td>vx8i-nprf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>resource_name</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>parent_fxf</th>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>description</th>\n",
       "      <td>Go to http://on.ny.gov/1xRIvPz on the New York...</td>\n",
       "      <td>&lt;b&gt;PLEASE NOTE:&lt;/b&gt; This dataset, which includ...</td>\n",
       "      <td>A Civil Service List consists of all candidate...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>attribution</th>\n",
       "      <td>New York State Gaming Commission</td>\n",
       "      <td>Taxi and Limousine Commission (TLC)</td>\n",
       "      <td>Department of Citywide Administrative Services...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>attribution_link</th>\n",
       "      <td>http://nylottery.ny.gov/wps/portal/Home/Lotter...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>contact_email</th>\n",
       "      <td>opendata@its.ny.gov</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <td>dataset</td>\n",
       "      <td>dataset</td>\n",
       "      <td>dataset</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>updatedAt</th>\n",
       "      <td>2024-12-08T11:01:28.000Z</td>\n",
       "      <td>2024-12-08T20:03:01.000Z</td>\n",
       "      <td>2024-12-06T14:11:47.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>createdAt</th>\n",
       "      <td>2014-06-17T19:47:54.000Z</td>\n",
       "      <td>2015-07-16T17:33:32.000Z</td>\n",
       "      <td>2016-06-14T21:12:15.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>metadata_updated_at</th>\n",
       "      <td>2024-12-08T11:01:27.000Z</td>\n",
       "      <td>2022-09-06T21:05:32.000Z</td>\n",
       "      <td>2024-05-14T14:26:52.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>data_updated_at</th>\n",
       "      <td>2024-12-08T11:01:28.000Z</td>\n",
       "      <td>2024-12-08T20:03:01.000Z</td>\n",
       "      <td>2024-12-06T14:11:47.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>page_views</th>\n",
       "      <td>{'page_views_last_week': 609, 'page_views_last...</td>\n",
       "      <td>{'page_views_last_week': 7, 'page_views_last_m...</td>\n",
       "      <td>{'page_views_last_week': 0, 'page_views_last_m...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>columns_name</th>\n",
       "      <td>[Cash Ball, Draw Date, Winning Numbers]</td>\n",
       "      <td>[Reason, Permit License Number, Active, Last D...</td>\n",
       "      <td>[List Title Desc, Extension Date, Established ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>columns_field_name</th>\n",
       "      <td>[cash_ball, draw_date, winning_numbers]</td>\n",
       "      <td>[reason, permit_license_number, active, last_d...</td>\n",
       "      <td>[list_title_desc, extension_date, established_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>columns_datatype</th>\n",
       "      <td>[Text, Calendar date, Text]</td>\n",
       "      <td>[Text, Text, Text, Calendar date, Calendar dat...</td>\n",
       "      <td>[Text, Calendar date, Calendar date, Calendar ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>columns_description</th>\n",
       "      <td>[Cash ball, Draw date, Winning numbers]</td>\n",
       "      <td>[Reason Code A,B,C or G\\n, Permit License Numb...</td>\n",
       "      <td>[A descriptive name that identifies a civil se...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>columns_format</th>\n",
       "      <td>[{'align': 'center'}, {'view': 'date', 'align'...</td>\n",
       "      <td>[{'displayStyle': 'plain', 'align': 'left'}, {...</td>\n",
       "      <td>[{'displayStyle': 'plain', 'align': 'left'}, {...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>download_count</th>\n",
       "      <td>231573</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>provenance</th>\n",
       "      <td>official</td>\n",
       "      <td>official</td>\n",
       "      <td>official</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lens_view_type</th>\n",
       "      <td>tabular</td>\n",
       "      <td>tabular</td>\n",
       "      <td>tabular</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lens_display_type</th>\n",
       "      <td>table</td>\n",
       "      <td>table</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>locked</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>blob_mime_type</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hide_from_data_json</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>publication_date</th>\n",
       "      <td>2021-04-27T14:13:45.000Z</td>\n",
       "      <td>2021-04-05T13:20:47.000Z</td>\n",
       "      <td>2024-01-12T16:15:05.000Z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                     0  \\\n",
       "name                 Lottery Cash 4 Life Winning Numbers: Beginning...   \n",
       "id                                                           kwxv-fwze   \n",
       "resource_name                                                     None   \n",
       "parent_fxf                                                          []   \n",
       "description          Go to http://on.ny.gov/1xRIvPz on the New York...   \n",
       "attribution                           New York State Gaming Commission   \n",
       "attribution_link     http://nylottery.ny.gov/wps/portal/Home/Lotter...   \n",
       "contact_email                                      opendata@its.ny.gov   \n",
       "type                                                           dataset   \n",
       "updatedAt                                     2024-12-08T11:01:28.000Z   \n",
       "createdAt                                     2014-06-17T19:47:54.000Z   \n",
       "metadata_updated_at                           2024-12-08T11:01:27.000Z   \n",
       "data_updated_at                               2024-12-08T11:01:28.000Z   \n",
       "page_views           {'page_views_last_week': 609, 'page_views_last...   \n",
       "columns_name                   [Cash Ball, Draw Date, Winning Numbers]   \n",
       "columns_field_name             [cash_ball, draw_date, winning_numbers]   \n",
       "columns_datatype                           [Text, Calendar date, Text]   \n",
       "columns_description            [Cash ball, Draw date, Winning numbers]   \n",
       "columns_format       [{'align': 'center'}, {'view': 'date', 'align'...   \n",
       "download_count                                                  231573   \n",
       "provenance                                                    official   \n",
       "lens_view_type                                                 tabular   \n",
       "lens_display_type                                                table   \n",
       "locked                                                           False   \n",
       "blob_mime_type                                                    None   \n",
       "hide_from_data_json                                              False   \n",
       "publication_date                              2021-04-27T14:13:45.000Z   \n",
       "\n",
       "                                                                     1  \\\n",
       "name                                  For Hire Vehicles (FHV) - Active   \n",
       "id                                                           8wbx-tsch   \n",
       "resource_name                                                     None   \n",
       "parent_fxf                                                          []   \n",
       "description          <b>PLEASE NOTE:</b> This dataset, which includ...   \n",
       "attribution                        Taxi and Limousine Commission (TLC)   \n",
       "attribution_link                                                  None   \n",
       "contact_email                                                     None   \n",
       "type                                                           dataset   \n",
       "updatedAt                                     2024-12-08T20:03:01.000Z   \n",
       "createdAt                                     2015-07-16T17:33:32.000Z   \n",
       "metadata_updated_at                           2022-09-06T21:05:32.000Z   \n",
       "data_updated_at                               2024-12-08T20:03:01.000Z   \n",
       "page_views           {'page_views_last_week': 7, 'page_views_last_m...   \n",
       "columns_name         [Reason, Permit License Number, Active, Last D...   \n",
       "columns_field_name   [reason, permit_license_number, active, last_d...   \n",
       "columns_datatype     [Text, Text, Text, Calendar date, Calendar dat...   \n",
       "columns_description  [Reason Code A,B,C or G\\n, Permit License Numb...   \n",
       "columns_format       [{'displayStyle': 'plain', 'align': 'left'}, {...   \n",
       "download_count                                                       0   \n",
       "provenance                                                    official   \n",
       "lens_view_type                                                 tabular   \n",
       "lens_display_type                                                table   \n",
       "locked                                                           False   \n",
       "blob_mime_type                                                    None   \n",
       "hide_from_data_json                                              False   \n",
       "publication_date                              2021-04-05T13:20:47.000Z   \n",
       "\n",
       "                                                                     2  \n",
       "name                                       Civil Service List (Active)  \n",
       "id                                                           vx8i-nprf  \n",
       "resource_name                                                     None  \n",
       "parent_fxf                                                          []  \n",
       "description          A Civil Service List consists of all candidate...  \n",
       "attribution          Department of Citywide Administrative Services...  \n",
       "attribution_link                                                  None  \n",
       "contact_email                                                     None  \n",
       "type                                                           dataset  \n",
       "updatedAt                                     2024-12-06T14:11:47.000Z  \n",
       "createdAt                                     2016-06-14T21:12:15.000Z  \n",
       "metadata_updated_at                           2024-05-14T14:26:52.000Z  \n",
       "data_updated_at                               2024-12-06T14:11:47.000Z  \n",
       "page_views           {'page_views_last_week': 0, 'page_views_last_m...  \n",
       "columns_name         [List Title Desc, Extension Date, Established ...  \n",
       "columns_field_name   [list_title_desc, extension_date, established_...  \n",
       "columns_datatype     [Text, Calendar date, Calendar date, Calendar ...  \n",
       "columns_description  [A descriptive name that identifies a civil se...  \n",
       "columns_format       [{'displayStyle': 'plain', 'align': 'left'}, {...  \n",
       "download_count                                                       0  \n",
       "provenance                                                    official  \n",
       "lens_view_type                                                 tabular  \n",
       "lens_display_type                                                table  \n",
       "locked                                                           False  \n",
       "blob_mime_type                                                    None  \n",
       "hide_from_data_json                                              False  \n",
       "publication_date                              2024-01-12T16:15:05.000Z  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview values\n",
    "df.head(3).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Sodapy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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": [
      "object type: <class 'list'>\n",
      "Number of datasets on NYC Open Data: 3,231.\n"
     ]
    }
   ],
   "source": [
    "# source domain for NYC Open Data on Socrata\n",
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "\n",
    "# initialize client\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# call sodapy's datasets method\n",
    "datasets = client.datasets()\n",
    "\n",
    "# sanity checks\n",
    "print(f'object type: {type(datasets)}')\n",
    "print(f'Number of datasets on NYC Open Data: {len(datasets):,}.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'dict'>\n"
     ]
    }
   ],
   "source": [
    "# review type about first dataset\n",
    "print(type(datasets[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['resource', 'classification', 'metadata', 'permalink', 'link', 'owner', 'creator'])"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# review information about keys\n",
    "datasets[0].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: For Hire Vehicles (FHV) - Active\n",
      "\n",
      "id: 8wbx-tsch\n",
      "\n",
      "resource_name: None\n",
      "\n",
      "parent_fxf: []\n",
      "\n",
      "description: <b>PLEASE NOTE:</b> This dataset, which includes all TLC licensed for-hire vehicles which are in good standing and able to drive, is updated every day in the evening between 4-7pm. Please check the 'Last Update Date' field to make sure the list has updated successfully. 'Last Update Date'  should show either today or yesterday's date, depending on the time of day. If the list is outdated, please download the most recent list from the link below. \r\n",
      "http://www1.nyc.gov/assets/tlc/downloads/datasets/tlc_for_hire_vehicle_active_and_inactive.csv\r\n",
      "\r\n",
      "TLC authorized For-Hire vehicles that are active. This list is accurate to the date and time represented in the Last Date Updated and Last Time Updated fields. For inquiries about the contents of this dataset, please email licensinginquiries@tlc.nyc.gov.\n",
      "\n",
      "attribution: Taxi and Limousine Commission (TLC)\n",
      "\n",
      "attribution_link: None\n",
      "\n",
      "contact_email: None\n",
      "\n",
      "type: dataset\n",
      "\n",
      "updatedAt: 2024-12-08T20:03:01.000Z\n",
      "\n",
      "createdAt: 2015-07-16T17:33:32.000Z\n",
      "\n",
      "metadata_updated_at: 2022-09-06T21:05:32.000Z\n",
      "\n",
      "data_updated_at: 2024-12-08T20:03:01.000Z\n",
      "\n",
      "page_views: {'page_views_last_week': 15718, 'page_views_last_month': 38056, 'page_views_total': 2934458, 'page_views_last_week_log': 13.940221819733113, 'page_views_last_month_log': 15.215874219256294, 'page_views_total_log': 21.484663120373497}\n",
      "\n",
      "columns_name: ['Reason', 'Permit License Number', 'Active', 'Last Date Updated', 'Hack Up Date', 'Base Address', 'Base Type', 'Vehicle VIN Number', 'Expiration Date', 'Wheelchair Accessible', 'Name', 'Vehicle Year', 'Last Time Updated', 'Vehicle License Number', 'Certification Date', 'License Type', 'VEH', 'Order Date', 'Base Telephone Number', 'Website', 'Base Name', 'DMV License Plate Number', 'Base Number']\n",
      "\n",
      "columns_field_name: ['reason', 'permit_license_number', 'active', 'last_date_updated', 'hack_up_date', 'base_address', 'base_type', 'vehicle_vin_number', 'expiration_date', 'wheelchair_accessible', 'name', 'vehicle_year', 'last_time_updated', 'vehicle_license_number', 'certification_date', 'license_type', 'veh', 'order_date', 'base_telephone_number', 'website', 'base_name', 'dmv_license_plate_number', 'base_number']\n",
      "\n",
      "columns_datatype: ['Text', 'Text', 'Text', 'Calendar date', 'Calendar date', 'Text', 'Text', 'Text', 'Calendar date', 'Text', 'Text', 'Number', 'Text', 'Text', 'Calendar date', 'Text', 'Text', 'Calendar date', 'Text', 'Text', 'Text', 'Text', 'Text']\n",
      "\n",
      "columns_description: ['Reason Code A,B,C or G\\n', 'Permit License Number', 'Permit active or not\\n', 'Last Date Updated', 'Hack Up Date', 'Base Address', 'Base Type', 'Vehicle VIN Number', 'Expiration Date', 'Wheelchair Accessible Indicator\\n', 'Owner Name\\n', 'Vehicle Year', 'Last Time Updated', 'FHV Vehicle License Number', 'Certification Date', 'TLC License Type\\n', 'Hybrid vehicle indicator\\n', 'Date Suspension ordered\\n', 'Base Telephone NumberBase Telephone Number', 'Base Website', 'Base Name', 'DMV License Plate Number', 'Base Number']\n",
      "\n",
      "columns_format: [{'displayStyle': 'plain', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'view': 'date', 'align': 'left'}, {'view': 'date', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'view': 'date', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'precisionStyle': 'standard', 'noCommas': 'true', 'align': 'right'}, {'displayStyle': 'plain', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'view': 'date', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'view': 'date_time', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}, {'displayStyle': 'plain', 'align': 'left'}]\n",
      "\n",
      "download_count: 532497\n",
      "\n",
      "provenance: official\n",
      "\n",
      "lens_view_type: tabular\n",
      "\n",
      "lens_display_type: table\n",
      "\n",
      "locked: False\n",
      "\n",
      "blob_mime_type: None\n",
      "\n",
      "hide_from_data_json: False\n",
      "\n",
      "publication_date: 2021-04-05T13:20:47.000Z\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# review information about resource key\n",
    "resources = datasets[0]['resource'].items()\n",
    "\n",
    "for key, value in resources:\n",
    "    print(f'{key}: {value}\\n')      "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "We found the Green Infrastructure dataset!\n",
      "Index is: 195\n"
     ]
    }
   ],
   "source": [
    "# green infrastructure id on website URL\n",
    "id_on_website = 'spjh-pz7h'\n",
    "\n",
    "# get the list of datasets once\n",
    "datasets = client.datasets()\n",
    "\n",
    "# loop through the datasets to find the one with the matching identifier\n",
    "for idx, dataset in enumerate(datasets):\n",
    "    if dataset['resource']['id'] == id_on_website:\n",
    "        print('We found the Green Infrastructure dataset!')\n",
    "        print(f'Index is: {idx}')\n",
    "        \n",
    "        dataset_index = idx\n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>id</th>\n",
       "      <th>resource_name</th>\n",
       "      <th>parent_fxf</th>\n",
       "      <th>description</th>\n",
       "      <th>attribution</th>\n",
       "      <th>attribution_link</th>\n",
       "      <th>contact_email</th>\n",
       "      <th>type</th>\n",
       "      <th>updatedAt</th>\n",
       "      <th>...</th>\n",
       "      <th>columns_description</th>\n",
       "      <th>columns_format</th>\n",
       "      <th>download_count</th>\n",
       "      <th>provenance</th>\n",
       "      <th>lens_view_type</th>\n",
       "      <th>lens_display_type</th>\n",
       "      <th>locked</th>\n",
       "      <th>blob_mime_type</th>\n",
       "      <th>hide_from_data_json</th>\n",
       "      <th>publication_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DEP Green Infrastructure</td>\n",
       "      <td>spjh-pz7h</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>NYC Green Infrastructure Program initiatives. ...</td>\n",
       "      <td>Department of Environmental Protection (DEP)</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>map</td>\n",
       "      <td>2024-11-06T14:58:19.000Z</td>\n",
       "      <td>...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>27796</td>\n",
       "      <td>official</td>\n",
       "      <td>geo</td>\n",
       "      <td>map</td>\n",
       "      <td>False</td>\n",
       "      <td>application/zip</td>\n",
       "      <td>False</td>\n",
       "      <td>2017-08-31T20:33:51.000Z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       name         id resource_name parent_fxf  \\\n",
       "0  DEP Green Infrastructure  spjh-pz7h          None         []   \n",
       "\n",
       "                                         description  \\\n",
       "0  NYC Green Infrastructure Program initiatives. ...   \n",
       "\n",
       "                                    attribution attribution_link  \\\n",
       "0  Department of Environmental Protection (DEP)             None   \n",
       "\n",
       "  contact_email type                 updatedAt  ... columns_description  \\\n",
       "0          None  map  2024-11-06T14:58:19.000Z  ...                  []   \n",
       "\n",
       "  columns_format download_count provenance lens_view_type lens_display_type  \\\n",
       "0             []          27796   official            geo               map   \n",
       "\n",
       "  locked   blob_mime_type hide_from_data_json          publication_date  \n",
       "0  False  application/zip               False  2017-08-31T20:33:51.000Z  \n",
       "\n",
       "[1 rows x 27 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = datasets[dataset_index]['resource']\n",
    "df = pd.DataFrame([data])\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1 entries, 0 to 0\n",
      "Data columns (total 27 columns):\n",
      " #   Column               Non-Null Count  Dtype \n",
      "---  ------               --------------  ----- \n",
      " 0   name                 1 non-null      object\n",
      " 1   id                   1 non-null      object\n",
      " 2   resource_name        0 non-null      object\n",
      " 3   parent_fxf           1 non-null      object\n",
      " 4   description          1 non-null      object\n",
      " 5   attribution          1 non-null      object\n",
      " 6   attribution_link     0 non-null      object\n",
      " 7   contact_email        0 non-null      object\n",
      " 8   type                 1 non-null      object\n",
      " 9   updatedAt            1 non-null      object\n",
      " 10  createdAt            1 non-null      object\n",
      " 11  metadata_updated_at  1 non-null      object\n",
      " 12  data_updated_at      1 non-null      object\n",
      " 13  page_views           1 non-null      object\n",
      " 14  columns_name         1 non-null      object\n",
      " 15  columns_field_name   1 non-null      object\n",
      " 16  columns_datatype     1 non-null      object\n",
      " 17  columns_description  1 non-null      object\n",
      " 18  columns_format       1 non-null      object\n",
      " 19  download_count       1 non-null      int64 \n",
      " 20  provenance           1 non-null      object\n",
      " 21  lens_view_type       1 non-null      object\n",
      " 22  lens_display_type    1 non-null      object\n",
      " 23  locked               1 non-null      bool  \n",
      " 24  blob_mime_type       1 non-null      object\n",
      " 25  hide_from_data_json  1 non-null      bool  \n",
      " 26  publication_date     1 non-null      object\n",
      "dtypes: bool(2), int64(1), object(24)\n",
      "memory usage: 330.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 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>name</th>\n",
       "      <th>id</th>\n",
       "      <th>resource_name</th>\n",
       "      <th>parent_fxf</th>\n",
       "      <th>description</th>\n",
       "      <th>attribution</th>\n",
       "      <th>attribution_link</th>\n",
       "      <th>contact_email</th>\n",
       "      <th>type</th>\n",
       "      <th>updatedAt</th>\n",
       "      <th>...</th>\n",
       "      <th>locked</th>\n",
       "      <th>blob_mime_type</th>\n",
       "      <th>hide_from_data_json</th>\n",
       "      <th>publication_date</th>\n",
       "      <th>page_views.page_views_last_week</th>\n",
       "      <th>page_views.page_views_last_month</th>\n",
       "      <th>page_views.page_views_total</th>\n",
       "      <th>page_views.page_views_last_week_log</th>\n",
       "      <th>page_views.page_views_last_month_log</th>\n",
       "      <th>page_views.page_views_total_log</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DEP Green Infrastructure</td>\n",
       "      <td>spjh-pz7h</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>NYC Green Infrastructure Program initiatives. ...</td>\n",
       "      <td>Department of Environmental Protection (DEP)</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>map</td>\n",
       "      <td>2024-11-06T14:58:19.000Z</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>application/zip</td>\n",
       "      <td>False</td>\n",
       "      <td>2017-08-31T20:33:51.000Z</td>\n",
       "      <td>176</td>\n",
       "      <td>557</td>\n",
       "      <td>19391</td>\n",
       "      <td>7.467606</td>\n",
       "      <td>9.124121</td>\n",
       "      <td>14.243174</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       name         id resource_name parent_fxf  \\\n",
       "0  DEP Green Infrastructure  spjh-pz7h          None         []   \n",
       "\n",
       "                                         description  \\\n",
       "0  NYC Green Infrastructure Program initiatives. ...   \n",
       "\n",
       "                                    attribution attribution_link  \\\n",
       "0  Department of Environmental Protection (DEP)             None   \n",
       "\n",
       "  contact_email type                 updatedAt  ... locked   blob_mime_type  \\\n",
       "0          None  map  2024-11-06T14:58:19.000Z  ...  False  application/zip   \n",
       "\n",
       "  hide_from_data_json          publication_date  \\\n",
       "0               False  2017-08-31T20:33:51.000Z   \n",
       "\n",
       "  page_views.page_views_last_week page_views.page_views_last_month  \\\n",
       "0                             176                              557   \n",
       "\n",
       "  page_views.page_views_total page_views.page_views_last_week_log  \\\n",
       "0                       19391                            7.467606   \n",
       "\n",
       "   page_views.page_views_last_month_log page_views.page_views_total_log  \n",
       "0                              9.124121                       14.243174  \n",
       "\n",
       "[1 rows x 32 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# alternatively: normalize JSON, unnest values\n",
    "df = pd.json_normalize(datasets[dataset_index]['resource'])\n",
    "\n",
    "# sanity check\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1 entries, 0 to 0\n",
      "Data columns (total 32 columns):\n",
      " #   Column                                Non-Null Count  Dtype  \n",
      "---  ------                                --------------  -----  \n",
      " 0   name                                  1 non-null      object \n",
      " 1   id                                    1 non-null      object \n",
      " 2   resource_name                         0 non-null      object \n",
      " 3   parent_fxf                            1 non-null      object \n",
      " 4   description                           1 non-null      object \n",
      " 5   attribution                           1 non-null      object \n",
      " 6   attribution_link                      0 non-null      object \n",
      " 7   contact_email                         0 non-null      object \n",
      " 8   type                                  1 non-null      object \n",
      " 9   updatedAt                             1 non-null      object \n",
      " 10  createdAt                             1 non-null      object \n",
      " 11  metadata_updated_at                   1 non-null      object \n",
      " 12  data_updated_at                       1 non-null      object \n",
      " 13  columns_name                          1 non-null      object \n",
      " 14  columns_field_name                    1 non-null      object \n",
      " 15  columns_datatype                      1 non-null      object \n",
      " 16  columns_description                   1 non-null      object \n",
      " 17  columns_format                        1 non-null      object \n",
      " 18  download_count                        1 non-null      int64  \n",
      " 19  provenance                            1 non-null      object \n",
      " 20  lens_view_type                        1 non-null      object \n",
      " 21  lens_display_type                     1 non-null      object \n",
      " 22  locked                                1 non-null      bool   \n",
      " 23  blob_mime_type                        1 non-null      object \n",
      " 24  hide_from_data_json                   1 non-null      bool   \n",
      " 25  publication_date                      1 non-null      object \n",
      " 26  page_views.page_views_last_week       1 non-null      int64  \n",
      " 27  page_views.page_views_last_month      1 non-null      int64  \n",
      " 28  page_views.page_views_total           1 non-null      int64  \n",
      " 29  page_views.page_views_last_week_log   1 non-null      float64\n",
      " 30  page_views.page_views_last_month_log  1 non-null      float64\n",
      " 31  page_views.page_views_total_log       1 non-null      float64\n",
      "dtypes: bool(2), float64(3), int64(4), object(23)\n",
      "memory usage: 370.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <td>DEP Green Infrastructure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>spjh-pz7h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>resource_name</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>parent_fxf</th>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>description</th>\n",
       "      <td>NYC Green Infrastructure Program initiatives. ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>attribution</th>\n",
       "      <td>Department of Environmental Protection (DEP)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>attribution_link</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>contact_email</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <td>map</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>updatedAt</th>\n",
       "      <td>2024-11-06T14:58:19.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>createdAt</th>\n",
       "      <td>2017-08-31T20:33:51.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>metadata_updated_at</th>\n",
       "      <td>2024-11-06T14:58:19.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>data_updated_at</th>\n",
       "      <td>2024-11-06T14:58:02.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>columns_name</th>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>columns_field_name</th>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>columns_datatype</th>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>columns_description</th>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>columns_format</th>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>download_count</th>\n",
       "      <td>27796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>provenance</th>\n",
       "      <td>official</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lens_view_type</th>\n",
       "      <td>geo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lens_display_type</th>\n",
       "      <td>map</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>locked</th>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>blob_mime_type</th>\n",
       "      <td>application/zip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hide_from_data_json</th>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>publication_date</th>\n",
       "      <td>2017-08-31T20:33:51.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>page_views.page_views_last_week</th>\n",
       "      <td>176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>page_views.page_views_last_month</th>\n",
       "      <td>557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>page_views.page_views_total</th>\n",
       "      <td>19391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>page_views.page_views_last_week_log</th>\n",
       "      <td>7.467606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>page_views.page_views_last_month_log</th>\n",
       "      <td>9.124121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>page_views.page_views_total_log</th>\n",
       "      <td>14.243174</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                      0\n",
       "name                                                           DEP Green Infrastructure\n",
       "id                                                                            spjh-pz7h\n",
       "resource_name                                                                      None\n",
       "parent_fxf                                                                           []\n",
       "description                           NYC Green Infrastructure Program initiatives. ...\n",
       "attribution                                Department of Environmental Protection (DEP)\n",
       "attribution_link                                                                   None\n",
       "contact_email                                                                      None\n",
       "type                                                                                map\n",
       "updatedAt                                                      2024-11-06T14:58:19.000Z\n",
       "createdAt                                                      2017-08-31T20:33:51.000Z\n",
       "metadata_updated_at                                            2024-11-06T14:58:19.000Z\n",
       "data_updated_at                                                2024-11-06T14:58:02.000Z\n",
       "columns_name                                                                         []\n",
       "columns_field_name                                                                   []\n",
       "columns_datatype                                                                     []\n",
       "columns_description                                                                  []\n",
       "columns_format                                                                       []\n",
       "download_count                                                                    27796\n",
       "provenance                                                                     official\n",
       "lens_view_type                                                                      geo\n",
       "lens_display_type                                                                   map\n",
       "locked                                                                            False\n",
       "blob_mime_type                                                          application/zip\n",
       "hide_from_data_json                                                               False\n",
       "publication_date                                               2017-08-31T20:33:51.000Z\n",
       "page_views.page_views_last_week                                                     176\n",
       "page_views.page_views_last_month                                                    557\n",
       "page_views.page_views_total                                                       19391\n",
       "page_views.page_views_last_week_log                                            7.467606\n",
       "page_views.page_views_last_month_log                                           9.124121\n",
       "page_views.page_views_total_log                                               14.243174"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview values\n",
    "df.T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.3  Metadata API\n",
    "Socrata's [Metadata API](https://dev.socrata.com/docs/other/metadata#?route=overview)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In Sodapy, we use the `.get_metadata()` method: Retrieve the metadata for a particular dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Socrata API URL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>assetType</th>\n",
       "      <th>averageRating</th>\n",
       "      <th>category</th>\n",
       "      <th>createdAt</th>\n",
       "      <th>description</th>\n",
       "      <th>displayType</th>\n",
       "      <th>downloadCount</th>\n",
       "      <th>hideFromCatalog</th>\n",
       "      <th>...</th>\n",
       "      <th>blobId</th>\n",
       "      <th>blobMimeType</th>\n",
       "      <th>rowIdentifierColumnId</th>\n",
       "      <th>queryString</th>\n",
       "      <th>ratings</th>\n",
       "      <th>indexUpdatedAt</th>\n",
       "      <th>childViews</th>\n",
       "      <th>iconUrl</th>\n",
       "      <th>previewImageId</th>\n",
       "      <th>disabledFeatureFlags</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6xyb-j5pk</td>\n",
       "      <td>NYC Address Points (Map)</td>\n",
       "      <td>map</td>\n",
       "      <td>0</td>\n",
       "      <td>City Government</td>\n",
       "      <td>1732652029</td>\n",
       "      <td>Address points were developed to supplement th...</td>\n",
       "      <td>visualization_canvas_map</td>\n",
       "      <td>0</td>\n",
       "      <td>False</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>uf93-f8nk</td>\n",
       "      <td>NYC Address Points</td>\n",
       "      <td>dataset</td>\n",
       "      <td>0</td>\n",
       "      <td>City Government</td>\n",
       "      <td>1732641922</td>\n",
       "      <td>Address points were developed to supplement th...</td>\n",
       "      <td>table</td>\n",
       "      <td>73</td>\n",
       "      <td>False</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>b7aj-ck5a</td>\n",
       "      <td>NYC Greenhouse Gas Emissions Municipal Inventory</td>\n",
       "      <td>dataset</td>\n",
       "      <td>0</td>\n",
       "      <td>Environment</td>\n",
       "      <td>1732116009</td>\n",
       "      <td>The Inventory of New York City Greenhouse Gas ...</td>\n",
       "      <td>table</td>\n",
       "      <td>52</td>\n",
       "      <td>False</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>3</th>\n",
       "      <td>3g6p-4u5s</td>\n",
       "      <td>Building Footprints (Map)</td>\n",
       "      <td>map</td>\n",
       "      <td>0</td>\n",
       "      <td>City Government</td>\n",
       "      <td>1731516162</td>\n",
       "      <td>Shapefile of footprint outlines of buildings i...</td>\n",
       "      <td>visualization_canvas_map</td>\n",
       "      <td>1</td>\n",
       "      <td>False</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>4</th>\n",
       "      <td>u9wf-3gbt</td>\n",
       "      <td>Building Footprints (P Layer)</td>\n",
       "      <td>dataset</td>\n",
       "      <td>0</td>\n",
       "      <td>City Government</td>\n",
       "      <td>1731513726</td>\n",
       "      <td>Shapefile of footprint outlines of buildings i...</td>\n",
       "      <td>table</td>\n",
       "      <td>22</td>\n",
       "      <td>False</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                              name assetType  \\\n",
       "0  6xyb-j5pk                          NYC Address Points (Map)       map   \n",
       "1  uf93-f8nk                                NYC Address Points   dataset   \n",
       "2  b7aj-ck5a  NYC Greenhouse Gas Emissions Municipal Inventory   dataset   \n",
       "3  3g6p-4u5s                         Building Footprints (Map)       map   \n",
       "4  u9wf-3gbt                     Building Footprints (P Layer)   dataset   \n",
       "\n",
       "   averageRating         category   createdAt  \\\n",
       "0              0  City Government  1732652029   \n",
       "1              0  City Government  1732641922   \n",
       "2              0      Environment  1732116009   \n",
       "3              0  City Government  1731516162   \n",
       "4              0  City Government  1731513726   \n",
       "\n",
       "                                         description  \\\n",
       "0  Address points were developed to supplement th...   \n",
       "1  Address points were developed to supplement th...   \n",
       "2  The Inventory of New York City Greenhouse Gas ...   \n",
       "3  Shapefile of footprint outlines of buildings i...   \n",
       "4  Shapefile of footprint outlines of buildings i...   \n",
       "\n",
       "                displayType  downloadCount  hideFromCatalog  ...  blobId  \\\n",
       "0  visualization_canvas_map              0            False  ...     NaN   \n",
       "1                     table             73            False  ...     NaN   \n",
       "2                     table             52            False  ...     NaN   \n",
       "3  visualization_canvas_map              1            False  ...     NaN   \n",
       "4                     table             22            False  ...     NaN   \n",
       "\n",
       "   blobMimeType rowIdentifierColumnId  queryString  ratings  indexUpdatedAt  \\\n",
       "0           NaN                   NaN          NaN      NaN             NaN   \n",
       "1           NaN                   NaN          NaN      NaN             NaN   \n",
       "2           NaN                   NaN          NaN      NaN             NaN   \n",
       "3           NaN                   NaN          NaN      NaN             NaN   \n",
       "4           NaN                   NaN          NaN      NaN             NaN   \n",
       "\n",
       "  childViews  iconUrl  previewImageId  disabledFeatureFlags  \n",
       "0        NaN      NaN             NaN                   NaN  \n",
       "1        NaN      NaN             NaN                   NaN  \n",
       "2        NaN      NaN             NaN                   NaN  \n",
       "3        NaN      NaN             NaN                   NaN  \n",
       "4        NaN      NaN             NaN                   NaN  \n",
       "\n",
       "[5 rows x 51 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Metadata API with 311 dataset\n",
    "# https://dev.socrata.com/docs/other/metadata#?route=overview\n",
    "\n",
    "# all datasets on NYC Open Data, and expand nested values\n",
    "url = 'https://data.cityofnewyork.us/api/views/'\n",
    "df = pd.read_json(url)\n",
    "\n",
    "# preview data\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3231 entries, 0 to 3230\n",
      "Data columns (total 51 columns):\n",
      " #   Column                    Non-Null Count  Dtype  \n",
      "---  ------                    --------------  -----  \n",
      " 0   id                        3231 non-null   object \n",
      " 1   name                      3231 non-null   object \n",
      " 2   assetType                 3231 non-null   object \n",
      " 3   averageRating             3231 non-null   int64  \n",
      " 4   category                  3131 non-null   object \n",
      " 5   createdAt                 3231 non-null   int64  \n",
      " 6   description               3166 non-null   object \n",
      " 7   displayType               3231 non-null   object \n",
      " 8   downloadCount             3231 non-null   int64  \n",
      " 9   hideFromCatalog           3231 non-null   bool   \n",
      " 10  hideFromDataJson          3231 non-null   bool   \n",
      " 11  locked                    3231 non-null   bool   \n",
      " 12  modifyingViewUid          271 non-null    object \n",
      " 13  newBackend                3231 non-null   bool   \n",
      " 14  numberOfComments          3231 non-null   int64  \n",
      " 15  oid                       3231 non-null   int64  \n",
      " 16  provenance                3231 non-null   object \n",
      " 17  publicationAppendEnabled  3231 non-null   bool   \n",
      " 18  publicationDate           3206 non-null   float64\n",
      " 19  publicationGroup          3231 non-null   int64  \n",
      " 20  publicationStage          3231 non-null   object \n",
      " 21  rowsUpdatedAt             3054 non-null   float64\n",
      " 22  rowsUpdatedBy             2792 non-null   object \n",
      " 23  tableId                   3231 non-null   int64  \n",
      " 24  totalTimesRated           3231 non-null   int64  \n",
      " 25  viewCount                 3231 non-null   int64  \n",
      " 26  viewLastModified          3231 non-null   int64  \n",
      " 27  viewType                  3231 non-null   object \n",
      " 28  approvals                 3231 non-null   object \n",
      " 29  clientContext             3231 non-null   object \n",
      " 30  grants                    3231 non-null   object \n",
      " 31  metadata                  3231 non-null   object \n",
      " 32  owner                     3231 non-null   object \n",
      " 33  rights                    3231 non-null   object \n",
      " 34  tableAuthor               3231 non-null   object \n",
      " 35  flags                     3231 non-null   object \n",
      " 36  tags                      2447 non-null   object \n",
      " 37  locale                    383 non-null    object \n",
      " 38  rowClass                  1656 non-null   object \n",
      " 39  blobFilename              202 non-null    object \n",
      " 40  blobFileSize              202 non-null    float64\n",
      " 41  blobId                    202 non-null    object \n",
      " 42  blobMimeType              202 non-null    object \n",
      " 43  rowIdentifierColumnId     57 non-null     float64\n",
      " 44  queryString               1 non-null      object \n",
      " 45  ratings                   24 non-null     object \n",
      " 46  indexUpdatedAt            1772 non-null   float64\n",
      " 47  childViews                35 non-null     object \n",
      " 48  iconUrl                   41 non-null     object \n",
      " 49  previewImageId            109 non-null    object \n",
      " 50  disabledFeatureFlags      16 non-null     object \n",
      "dtypes: bool(5), float64(5), int64(10), object(31)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['id', 'name', 'assetType', 'attribution', 'averageRating', 'category', 'createdAt', 'description', 'displayType', 'downloadCount', 'hideFromCatalog', 'hideFromDataJson', 'locked', 'newBackend', 'numberOfComments', 'oid', 'provenance', 'publicationAppendEnabled', 'publicationDate', 'publicationGroup', 'publicationStage', 'rowClass', 'rowIdentifierColumnId', 'rowsUpdatedAt', 'rowsUpdatedBy', 'tableId', 'totalTimesRated', 'viewCount', 'viewLastModified', 'viewType', 'approvals', 'clientContext', 'columns', 'grants', 'metadata', 'owner', 'query', 'rights', 'tableAuthor', 'tags', 'flags'])"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# example of Discovery API with the 311 dataset\n",
    "# instead of expanding nested columns, let's keep only top-level columns\n",
    "url = 'https://data.cityofnewyork.us/api/views/erm2-nwe9'\n",
    "\n",
    "# fetch the JSON data from the web\n",
    "response = requests.get(url)\n",
    "\n",
    "# parse the JSON response\n",
    "data_dict = response.json()  \n",
    "\n",
    "# preview keys    \n",
    "data_dict.keys()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>assetType</th>\n",
       "      <th>attribution</th>\n",
       "      <th>averageRating</th>\n",
       "      <th>category</th>\n",
       "      <th>createdAt</th>\n",
       "      <th>description</th>\n",
       "      <th>displayType</th>\n",
       "      <th>downloadCount</th>\n",
       "      <th>...</th>\n",
       "      <th>clientContext</th>\n",
       "      <th>columns</th>\n",
       "      <th>grants</th>\n",
       "      <th>metadata</th>\n",
       "      <th>owner</th>\n",
       "      <th>query</th>\n",
       "      <th>rights</th>\n",
       "      <th>tableAuthor</th>\n",
       "      <th>tags</th>\n",
       "      <th>flags</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>erm2-nwe9</td>\n",
       "      <td>311 Service Requests from 2010 to Present</td>\n",
       "      <td>dataset</td>\n",
       "      <td>311</td>\n",
       "      <td>0</td>\n",
       "      <td>Social Services</td>\n",
       "      <td>1318225937</td>\n",
       "      <td>&lt;b&gt;NOTE:&lt;/b&gt; The 311 dataset is currently show...</td>\n",
       "      <td>table</td>\n",
       "      <td>444681</td>\n",
       "      <td>...</td>\n",
       "      <td>{'clientContextVariables': [], 'inheritedVaria...</td>\n",
       "      <td>[{'id': 585605889, 'name': 'Unique Key', 'data...</td>\n",
       "      <td>[{'inherited': False, 'type': 'viewer', 'flags...</td>\n",
       "      <td>{'rdfSubject': '0', 'rdfClass': '', 'jsonQuery...</td>\n",
       "      <td>{'id': '5fuc-pqz2', 'displayName': 'NYC OpenDa...</td>\n",
       "      <td>{'orderBys': [{'ascending': False, 'expression...</td>\n",
       "      <td>[read]</td>\n",
       "      <td>{'id': '5fuc-pqz2', 'displayName': 'NYC OpenDa...</td>\n",
       "      <td>[311, 311 service requests, city government, s...</td>\n",
       "      <td>[default, ownerMayBeContacted, restorable, res...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                       name assetType attribution  \\\n",
       "0  erm2-nwe9  311 Service Requests from 2010 to Present   dataset         311   \n",
       "\n",
       "   averageRating         category   createdAt  \\\n",
       "0              0  Social Services  1318225937   \n",
       "\n",
       "                                         description displayType  \\\n",
       "0  <b>NOTE:</b> The 311 dataset is currently show...       table   \n",
       "\n",
       "   downloadCount  ...                                      clientContext  \\\n",
       "0         444681  ...  {'clientContextVariables': [], 'inheritedVaria...   \n",
       "\n",
       "                                             columns  \\\n",
       "0  [{'id': 585605889, 'name': 'Unique Key', 'data...   \n",
       "\n",
       "                                              grants  \\\n",
       "0  [{'inherited': False, 'type': 'viewer', 'flags...   \n",
       "\n",
       "                                            metadata  \\\n",
       "0  {'rdfSubject': '0', 'rdfClass': '', 'jsonQuery...   \n",
       "\n",
       "                                               owner  \\\n",
       "0  {'id': '5fuc-pqz2', 'displayName': 'NYC OpenDa...   \n",
       "\n",
       "                                               query  rights  \\\n",
       "0  {'orderBys': [{'ascending': False, 'expression...  [read]   \n",
       "\n",
       "                                         tableAuthor  \\\n",
       "0  {'id': '5fuc-pqz2', 'displayName': 'NYC OpenDa...   \n",
       "\n",
       "                                                tags  \\\n",
       "0  [311, 311 service requests, city government, s...   \n",
       "\n",
       "                                               flags  \n",
       "0  [default, ownerMayBeContacted, restorable, res...  \n",
       "\n",
       "[1 rows x 41 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert to df\n",
    "df = pd.DataFrame([data_dict])\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1 entries, 0 to 0\n",
      "Data columns (total 41 columns):\n",
      " #   Column                    Non-Null Count  Dtype \n",
      "---  ------                    --------------  ----- \n",
      " 0   id                        1 non-null      object\n",
      " 1   name                      1 non-null      object\n",
      " 2   assetType                 1 non-null      object\n",
      " 3   attribution               1 non-null      object\n",
      " 4   averageRating             1 non-null      int64 \n",
      " 5   category                  1 non-null      object\n",
      " 6   createdAt                 1 non-null      int64 \n",
      " 7   description               1 non-null      object\n",
      " 8   displayType               1 non-null      object\n",
      " 9   downloadCount             1 non-null      int64 \n",
      " 10  hideFromCatalog           1 non-null      bool  \n",
      " 11  hideFromDataJson          1 non-null      bool  \n",
      " 12  locked                    1 non-null      bool  \n",
      " 13  newBackend                1 non-null      bool  \n",
      " 14  numberOfComments          1 non-null      int64 \n",
      " 15  oid                       1 non-null      int64 \n",
      " 16  provenance                1 non-null      object\n",
      " 17  publicationAppendEnabled  1 non-null      bool  \n",
      " 18  publicationDate           1 non-null      int64 \n",
      " 19  publicationGroup          1 non-null      int64 \n",
      " 20  publicationStage          1 non-null      object\n",
      " 21  rowClass                  1 non-null      object\n",
      " 22  rowIdentifierColumnId     1 non-null      int64 \n",
      " 23  rowsUpdatedAt             1 non-null      int64 \n",
      " 24  rowsUpdatedBy             1 non-null      object\n",
      " 25  tableId                   1 non-null      int64 \n",
      " 26  totalTimesRated           1 non-null      int64 \n",
      " 27  viewCount                 1 non-null      int64 \n",
      " 28  viewLastModified          1 non-null      int64 \n",
      " 29  viewType                  1 non-null      object\n",
      " 30  approvals                 1 non-null      object\n",
      " 31  clientContext             1 non-null      object\n",
      " 32  columns                   1 non-null      object\n",
      " 33  grants                    1 non-null      object\n",
      " 34  metadata                  1 non-null      object\n",
      " 35  owner                     1 non-null      object\n",
      " 36  query                     1 non-null      object\n",
      " 37  rights                    1 non-null      object\n",
      " 38  tableAuthor               1 non-null      object\n",
      " 39  tags                      1 non-null      object\n",
      " 40  flags                     1 non-null      object\n",
      "dtypes: bool(5), int64(13), object(23)\n",
      "memory usage: 421.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "# view columns, notice the decrease in number of columns compared to the previous example\n",
    "# some values are still nested\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Sodapy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:root:Requests made without an app_token will be subject to strict throttling limits.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "dict_keys(['id', 'name', 'assetType', 'attribution', 'averageRating', 'category', 'createdAt', 'description', 'displayType', 'downloadCount', 'hideFromCatalog', 'hideFromDataJson', 'locked', 'newBackend', 'numberOfComments', 'oid', 'provenance', 'publicationAppendEnabled', 'publicationDate', 'publicationGroup', 'publicationStage', 'rowClass', 'rowIdentifierColumnId', 'rowsUpdatedAt', 'rowsUpdatedBy', 'tableId', 'totalTimesRated', 'viewCount', 'viewLastModified', 'viewType', 'approvals', 'clientContext', 'columns', 'grants', 'metadata', 'owner', 'query', 'rights', 'tableAuthor', 'tags', 'flags'])"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# source domain for NYC Open Data on Socrata\n",
    "socrata_domain = 'data.cityofnewyork.us'\n",
    "\n",
    "# dataset id for NYC 311 on NYC Open Data on Socrata\n",
    "socrata_dataset_identifier = 'erm2-nwe9'\n",
    "\n",
    "# initialize client\n",
    "client = Socrata(\n",
    "    socrata_domain,\n",
    "    app_token=None,\n",
    "    timeout=100\n",
    ")\n",
    "\n",
    "# similar to: 'https://data.cityofnewyork.us/api/views/metadata/v1/'\n",
    "metadata = client.get_metadata(socrata_dataset_identifier)\n",
    "\n",
    "metadata.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>assetType</th>\n",
       "      <th>attribution</th>\n",
       "      <th>averageRating</th>\n",
       "      <th>category</th>\n",
       "      <th>createdAt</th>\n",
       "      <th>description</th>\n",
       "      <th>displayType</th>\n",
       "      <th>downloadCount</th>\n",
       "      <th>...</th>\n",
       "      <th>clientContext</th>\n",
       "      <th>columns</th>\n",
       "      <th>grants</th>\n",
       "      <th>metadata</th>\n",
       "      <th>owner</th>\n",
       "      <th>query</th>\n",
       "      <th>rights</th>\n",
       "      <th>tableAuthor</th>\n",
       "      <th>tags</th>\n",
       "      <th>flags</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>erm2-nwe9</td>\n",
       "      <td>311 Service Requests from 2010 to Present</td>\n",
       "      <td>dataset</td>\n",
       "      <td>311</td>\n",
       "      <td>0</td>\n",
       "      <td>Social Services</td>\n",
       "      <td>1318225937</td>\n",
       "      <td>&lt;b&gt;NOTE:&lt;/b&gt; The 311 dataset is currently show...</td>\n",
       "      <td>table</td>\n",
       "      <td>444681</td>\n",
       "      <td>...</td>\n",
       "      <td>{'clientContextVariables': [], 'inheritedVaria...</td>\n",
       "      <td>[{'id': 585605889, 'name': 'Unique Key', 'data...</td>\n",
       "      <td>[{'inherited': False, 'type': 'viewer', 'flags...</td>\n",
       "      <td>{'rdfSubject': '0', 'rdfClass': '', 'jsonQuery...</td>\n",
       "      <td>{'id': '5fuc-pqz2', 'displayName': 'NYC OpenDa...</td>\n",
       "      <td>{'orderBys': [{'ascending': False, 'expression...</td>\n",
       "      <td>[read]</td>\n",
       "      <td>{'id': '5fuc-pqz2', 'displayName': 'NYC OpenDa...</td>\n",
       "      <td>[311, 311 service requests, city government, s...</td>\n",
       "      <td>[default, ownerMayBeContacted, restorable, res...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                       name assetType attribution  \\\n",
       "0  erm2-nwe9  311 Service Requests from 2010 to Present   dataset         311   \n",
       "\n",
       "   averageRating         category   createdAt  \\\n",
       "0              0  Social Services  1318225937   \n",
       "\n",
       "                                         description displayType  \\\n",
       "0  <b>NOTE:</b> The 311 dataset is currently show...       table   \n",
       "\n",
       "   downloadCount  ...                                      clientContext  \\\n",
       "0         444681  ...  {'clientContextVariables': [], 'inheritedVaria...   \n",
       "\n",
       "                                             columns  \\\n",
       "0  [{'id': 585605889, 'name': 'Unique Key', 'data...   \n",
       "\n",
       "                                              grants  \\\n",
       "0  [{'inherited': False, 'type': 'viewer', 'flags...   \n",
       "\n",
       "                                            metadata  \\\n",
       "0  {'rdfSubject': '0', 'rdfClass': '', 'jsonQuery...   \n",
       "\n",
       "                                               owner  \\\n",
       "0  {'id': '5fuc-pqz2', 'displayName': 'NYC OpenDa...   \n",
       "\n",
       "                                               query  rights  \\\n",
       "0  {'orderBys': [{'ascending': False, 'expression...  [read]   \n",
       "\n",
       "                                         tableAuthor  \\\n",
       "0  {'id': '5fuc-pqz2', 'displayName': 'NYC OpenDa...   \n",
       "\n",
       "                                                tags  \\\n",
       "0  [311, 311 service requests, city government, s...   \n",
       "\n",
       "                                               flags  \n",
       "0  [default, ownerMayBeContacted, restorable, res...  \n",
       "\n",
       "[1 rows x 41 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert to df\n",
    "df = pd.DataFrame([metadata])\n",
    "\n",
    "# preview data\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1 entries, 0 to 0\n",
      "Data columns (total 41 columns):\n",
      " #   Column                    Non-Null Count  Dtype \n",
      "---  ------                    --------------  ----- \n",
      " 0   id                        1 non-null      object\n",
      " 1   name                      1 non-null      object\n",
      " 2   assetType                 1 non-null      object\n",
      " 3   attribution               1 non-null      object\n",
      " 4   averageRating             1 non-null      int64 \n",
      " 5   category                  1 non-null      object\n",
      " 6   createdAt                 1 non-null      int64 \n",
      " 7   description               1 non-null      object\n",
      " 8   displayType               1 non-null      object\n",
      " 9   downloadCount             1 non-null      int64 \n",
      " 10  hideFromCatalog           1 non-null      bool  \n",
      " 11  hideFromDataJson          1 non-null      bool  \n",
      " 12  locked                    1 non-null      bool  \n",
      " 13  newBackend                1 non-null      bool  \n",
      " 14  numberOfComments          1 non-null      int64 \n",
      " 15  oid                       1 non-null      int64 \n",
      " 16  provenance                1 non-null      object\n",
      " 17  publicationAppendEnabled  1 non-null      bool  \n",
      " 18  publicationDate           1 non-null      int64 \n",
      " 19  publicationGroup          1 non-null      int64 \n",
      " 20  publicationStage          1 non-null      object\n",
      " 21  rowClass                  1 non-null      object\n",
      " 22  rowIdentifierColumnId     1 non-null      int64 \n",
      " 23  rowsUpdatedAt             1 non-null      int64 \n",
      " 24  rowsUpdatedBy             1 non-null      object\n",
      " 25  tableId                   1 non-null      int64 \n",
      " 26  totalTimesRated           1 non-null      int64 \n",
      " 27  viewCount                 1 non-null      int64 \n",
      " 28  viewLastModified          1 non-null      int64 \n",
      " 29  viewType                  1 non-null      object\n",
      " 30  approvals                 1 non-null      object\n",
      " 31  clientContext             1 non-null      object\n",
      " 32  columns                   1 non-null      object\n",
      " 33  grants                    1 non-null      object\n",
      " 34  metadata                  1 non-null      object\n",
      " 35  owner                     1 non-null      object\n",
      " 36  query                     1 non-null      object\n",
      " 37  rights                    1 non-null      object\n",
      " 38  tableAuthor               1 non-null      object\n",
      " 39  tags                      1 non-null      object\n",
      " 40  flags                     1 non-null      object\n",
      "dtypes: bool(5), int64(13), object(23)\n",
      "memory usage: 421.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>attribution</th>\n",
       "      <th>attributionLink</th>\n",
       "      <th>category</th>\n",
       "      <th>createdAt</th>\n",
       "      <th>dataUpdatedAt</th>\n",
       "      <th>dataUri</th>\n",
       "      <th>description</th>\n",
       "      <th>domain</th>\n",
       "      <th>...</th>\n",
       "      <th>hideFromCatalog</th>\n",
       "      <th>hideFromDataJson</th>\n",
       "      <th>license</th>\n",
       "      <th>metadataUpdatedAt</th>\n",
       "      <th>provenance</th>\n",
       "      <th>updatedAt</th>\n",
       "      <th>webUri</th>\n",
       "      <th>approvals</th>\n",
       "      <th>customFields</th>\n",
       "      <th>tags</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6xyb-j5pk</td>\n",
       "      <td>NYC Address Points (Map)</td>\n",
       "      <td>Office of Technology and Innovation (OTI)</td>\n",
       "      <td>None</td>\n",
       "      <td>City Government</td>\n",
       "      <td>2024-11-26T20:13:49+0000</td>\n",
       "      <td>2024-12-04T18:57:52+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/resource/6xyb-j5pk</td>\n",
       "      <td>Address points were developed to supplement th...</td>\n",
       "      <td>data.cityofnewyork.us</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-11-26T20:14:56+0000</td>\n",
       "      <td>OFFICIAL</td>\n",
       "      <td>2024-11-26T20:14:56+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/d/6xyb-j5pk</td>\n",
       "      <td>[{'reviewedAt': 1732652072, 'reviewedAutomatic...</td>\n",
       "      <td>{'Update': {'Automation': 'No', 'Date Made Pub...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>uf93-f8nk</td>\n",
       "      <td>NYC Address Points</td>\n",
       "      <td>Office of Technology and Innovation (OTI)</td>\n",
       "      <td>None</td>\n",
       "      <td>City Government</td>\n",
       "      <td>2024-11-26T17:25:22+0000</td>\n",
       "      <td>2024-12-04T18:57:52+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/resource/uf93-f8nk</td>\n",
       "      <td>Address points were developed to supplement th...</td>\n",
       "      <td>data.cityofnewyork.us</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-12-04T18:50:48+0000</td>\n",
       "      <td>OFFICIAL</td>\n",
       "      <td>2024-12-04T18:50:48+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/d/uf93-f8nk</td>\n",
       "      <td>[{'reviewedAt': 1732651869, 'reviewedAutomatic...</td>\n",
       "      <td>{'Update': {'Automation': 'No', 'Date Made Pub...</td>\n",
       "      <td>[address point]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b7aj-ck5a</td>\n",
       "      <td>NYC Greenhouse Gas Emissions Municipal Inventory</td>\n",
       "      <td>Mayor's Office of Climate and Environmental Ju...</td>\n",
       "      <td>https://climate.cityofnewyork.us/initiatives/n...</td>\n",
       "      <td>Environment</td>\n",
       "      <td>2024-11-20T15:20:09+0000</td>\n",
       "      <td>2024-11-20T15:48:44+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/resource/b7aj-ck5a</td>\n",
       "      <td>The Inventory of New York City Greenhouse Gas ...</td>\n",
       "      <td>data.cityofnewyork.us</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-11-20T15:49:27+0000</td>\n",
       "      <td>OFFICIAL</td>\n",
       "      <td>2024-11-20T15:49:49+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/d/b7aj-ck5a</td>\n",
       "      <td>[{'reviewedAt': 1732117789, 'reviewedAutomatic...</td>\n",
       "      <td>{'Update': {'Automation': 'No', 'Date Made Pub...</td>\n",
       "      <td>[greenhouse gas emissions, greenhouse, fuel, e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3g6p-4u5s</td>\n",
       "      <td>Building Footprints (Map)</td>\n",
       "      <td>Office of Technology and Innovation (OTI)</td>\n",
       "      <td>None</td>\n",
       "      <td>City Government</td>\n",
       "      <td>2024-11-13T16:42:42+0000</td>\n",
       "      <td>2024-12-04T19:26:21+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/resource/3g6p-4u5s</td>\n",
       "      <td>Shapefile of footprint outlines of buildings i...</td>\n",
       "      <td>data.cityofnewyork.us</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-11-13T16:47:48+0000</td>\n",
       "      <td>OFFICIAL</td>\n",
       "      <td>2024-11-13T16:47:48+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/d/3g6p-4u5s</td>\n",
       "      <td>[{'reviewedAt': 1731516188, 'reviewedAutomatic...</td>\n",
       "      <td>{'Update': {'Automation': 'No', 'Date Made Pub...</td>\n",
       "      <td>[footprints, buildings]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>u9wf-3gbt</td>\n",
       "      <td>Building Footprints (P Layer)</td>\n",
       "      <td>Office of Technology and Innovation (OTI)</td>\n",
       "      <td>None</td>\n",
       "      <td>City Government</td>\n",
       "      <td>2024-11-13T16:02:06+0000</td>\n",
       "      <td>2024-12-04T19:04:52+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/resource/u9wf-3gbt</td>\n",
       "      <td>Shapefile of footprint outlines of buildings i...</td>\n",
       "      <td>data.cityofnewyork.us</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-12-04T19:02:59+0000</td>\n",
       "      <td>OFFICIAL</td>\n",
       "      <td>2024-12-04T19:02:59+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/d/u9wf-3gbt</td>\n",
       "      <td>[{'reviewedAt': 1731516092, 'reviewedAutomatic...</td>\n",
       "      <td>{'Update': {'Automation': 'No', 'Date Made Pub...</td>\n",
       "      <td>[building, footprint, footprints]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                              name  \\\n",
       "0  6xyb-j5pk                          NYC Address Points (Map)   \n",
       "1  uf93-f8nk                                NYC Address Points   \n",
       "2  b7aj-ck5a  NYC Greenhouse Gas Emissions Municipal Inventory   \n",
       "3  3g6p-4u5s                         Building Footprints (Map)   \n",
       "4  u9wf-3gbt                     Building Footprints (P Layer)   \n",
       "\n",
       "                                         attribution  \\\n",
       "0          Office of Technology and Innovation (OTI)   \n",
       "1          Office of Technology and Innovation (OTI)   \n",
       "2  Mayor's Office of Climate and Environmental Ju...   \n",
       "3          Office of Technology and Innovation (OTI)   \n",
       "4          Office of Technology and Innovation (OTI)   \n",
       "\n",
       "                                     attributionLink         category  \\\n",
       "0                                               None  City Government   \n",
       "1                                               None  City Government   \n",
       "2  https://climate.cityofnewyork.us/initiatives/n...      Environment   \n",
       "3                                               None  City Government   \n",
       "4                                               None  City Government   \n",
       "\n",
       "                  createdAt             dataUpdatedAt  \\\n",
       "0  2024-11-26T20:13:49+0000  2024-12-04T18:57:52+0000   \n",
       "1  2024-11-26T17:25:22+0000  2024-12-04T18:57:52+0000   \n",
       "2  2024-11-20T15:20:09+0000  2024-11-20T15:48:44+0000   \n",
       "3  2024-11-13T16:42:42+0000  2024-12-04T19:26:21+0000   \n",
       "4  2024-11-13T16:02:06+0000  2024-12-04T19:04:52+0000   \n",
       "\n",
       "                                            dataUri  \\\n",
       "0  https://data.cityofnewyork.us/resource/6xyb-j5pk   \n",
       "1  https://data.cityofnewyork.us/resource/uf93-f8nk   \n",
       "2  https://data.cityofnewyork.us/resource/b7aj-ck5a   \n",
       "3  https://data.cityofnewyork.us/resource/3g6p-4u5s   \n",
       "4  https://data.cityofnewyork.us/resource/u9wf-3gbt   \n",
       "\n",
       "                                         description                 domain  \\\n",
       "0  Address points were developed to supplement th...  data.cityofnewyork.us   \n",
       "1  Address points were developed to supplement th...  data.cityofnewyork.us   \n",
       "2  The Inventory of New York City Greenhouse Gas ...  data.cityofnewyork.us   \n",
       "3  Shapefile of footprint outlines of buildings i...  data.cityofnewyork.us   \n",
       "4  Shapefile of footprint outlines of buildings i...  data.cityofnewyork.us   \n",
       "\n",
       "   ...  hideFromCatalog  hideFromDataJson  license         metadataUpdatedAt  \\\n",
       "0  ...            False             False     None  2024-11-26T20:14:56+0000   \n",
       "1  ...            False             False     None  2024-12-04T18:50:48+0000   \n",
       "2  ...            False             False     None  2024-11-20T15:49:27+0000   \n",
       "3  ...            False             False     None  2024-11-13T16:47:48+0000   \n",
       "4  ...            False             False     None  2024-12-04T19:02:59+0000   \n",
       "\n",
       "  provenance                 updatedAt  \\\n",
       "0   OFFICIAL  2024-11-26T20:14:56+0000   \n",
       "1   OFFICIAL  2024-12-04T18:50:48+0000   \n",
       "2   OFFICIAL  2024-11-20T15:49:49+0000   \n",
       "3   OFFICIAL  2024-11-13T16:47:48+0000   \n",
       "4   OFFICIAL  2024-12-04T19:02:59+0000   \n",
       "\n",
       "                                      webUri  \\\n",
       "0  https://data.cityofnewyork.us/d/6xyb-j5pk   \n",
       "1  https://data.cityofnewyork.us/d/uf93-f8nk   \n",
       "2  https://data.cityofnewyork.us/d/b7aj-ck5a   \n",
       "3  https://data.cityofnewyork.us/d/3g6p-4u5s   \n",
       "4  https://data.cityofnewyork.us/d/u9wf-3gbt   \n",
       "\n",
       "                                           approvals  \\\n",
       "0  [{'reviewedAt': 1732652072, 'reviewedAutomatic...   \n",
       "1  [{'reviewedAt': 1732651869, 'reviewedAutomatic...   \n",
       "2  [{'reviewedAt': 1732117789, 'reviewedAutomatic...   \n",
       "3  [{'reviewedAt': 1731516188, 'reviewedAutomatic...   \n",
       "4  [{'reviewedAt': 1731516092, 'reviewedAutomatic...   \n",
       "\n",
       "                                        customFields  \\\n",
       "0  {'Update': {'Automation': 'No', 'Date Made Pub...   \n",
       "1  {'Update': {'Automation': 'No', 'Date Made Pub...   \n",
       "2  {'Update': {'Automation': 'No', 'Date Made Pub...   \n",
       "3  {'Update': {'Automation': 'No', 'Date Made Pub...   \n",
       "4  {'Update': {'Automation': 'No', 'Date Made Pub...   \n",
       "\n",
       "                                                tags  \n",
       "0                                               None  \n",
       "1                                    [address point]  \n",
       "2  [greenhouse gas emissions, greenhouse, fuel, e...  \n",
       "3                            [footprints, buildings]  \n",
       "4                  [building, footprint, footprints]  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# example of the Metadata API\n",
    "# https://dev.socrata.com/docs/other/metadata#?route=overview\n",
    "\n",
    "# all datasets on NYC Open Data, notice the metadata in the URL\n",
    "url = 'https://data.cityofnewyork.us/api/views/metadata/v1/'\n",
    "df = pd.read_json(url)\n",
    "\n",
    "# preview data\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3231 entries, 0 to 3230\n",
      "Data columns (total 21 columns):\n",
      " #   Column             Non-Null Count  Dtype  \n",
      "---  ------             --------------  -----  \n",
      " 0   id                 3231 non-null   object \n",
      " 1   name               3231 non-null   object \n",
      " 2   attribution        3164 non-null   object \n",
      " 3   attributionLink    449 non-null    object \n",
      " 4   category           3131 non-null   object \n",
      " 5   createdAt          3231 non-null   object \n",
      " 6   dataUpdatedAt      3054 non-null   object \n",
      " 7   dataUri            3231 non-null   object \n",
      " 8   description        3166 non-null   object \n",
      " 9   domain             3231 non-null   object \n",
      " 10  externalId         0 non-null      float64\n",
      " 11  hideFromCatalog    3231 non-null   bool   \n",
      " 12  hideFromDataJson   3231 non-null   bool   \n",
      " 13  license            79 non-null     object \n",
      " 14  metadataUpdatedAt  3231 non-null   object \n",
      " 15  provenance         3231 non-null   object \n",
      " 16  updatedAt          3231 non-null   object \n",
      " 17  webUri             3231 non-null   object \n",
      " 18  approvals          3231 non-null   object \n",
      " 19  customFields       3229 non-null   object \n",
      " 20  tags               2447 non-null   object \n",
      "dtypes: bool(2), float64(1), object(18)\n",
      "memory usage: 486.0+ KB\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>attribution</th>\n",
       "      <th>attributionLink</th>\n",
       "      <th>category</th>\n",
       "      <th>createdAt</th>\n",
       "      <th>dataUpdatedAt</th>\n",
       "      <th>dataUri</th>\n",
       "      <th>description</th>\n",
       "      <th>domain</th>\n",
       "      <th>...</th>\n",
       "      <th>hideFromCatalog</th>\n",
       "      <th>hideFromDataJson</th>\n",
       "      <th>license</th>\n",
       "      <th>metadataUpdatedAt</th>\n",
       "      <th>provenance</th>\n",
       "      <th>updatedAt</th>\n",
       "      <th>webUri</th>\n",
       "      <th>approvals</th>\n",
       "      <th>customFields</th>\n",
       "      <th>tags</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>erm2-nwe9</td>\n",
       "      <td>311 Service Requests from 2010 to Present</td>\n",
       "      <td>311</td>\n",
       "      <td>None</td>\n",
       "      <td>Social Services</td>\n",
       "      <td>2011-10-10T05:52:17+0000</td>\n",
       "      <td>2024-12-08T02:36:08+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/resource/erm2-nwe9</td>\n",
       "      <td>&lt;b&gt;NOTE:&lt;/b&gt; The 311 dataset is currently show...</td>\n",
       "      <td>data.cityofnewyork.us</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-05-28T20:32:16+0000</td>\n",
       "      <td>OFFICIAL</td>\n",
       "      <td>2024-05-28T20:32:16+0000</td>\n",
       "      <td>https://data.cityofnewyork.us/d/erm2-nwe9</td>\n",
       "      <td>[{'reviewedAt': 1524193398, 'reviewedAutomatic...</td>\n",
       "      <td>{'Update': {'Automation': 'Yes', 'Date Made Pu...</td>\n",
       "      <td>[311, 311 service requests, city government, s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                       name attribution  \\\n",
       "0  erm2-nwe9  311 Service Requests from 2010 to Present         311   \n",
       "\n",
       "  attributionLink         category                 createdAt  \\\n",
       "0            None  Social Services  2011-10-10T05:52:17+0000   \n",
       "\n",
       "              dataUpdatedAt                                           dataUri  \\\n",
       "0  2024-12-08T02:36:08+0000  https://data.cityofnewyork.us/resource/erm2-nwe9   \n",
       "\n",
       "                                         description                 domain  \\\n",
       "0  <b>NOTE:</b> The 311 dataset is currently show...  data.cityofnewyork.us   \n",
       "\n",
       "   ... hideFromCatalog  hideFromDataJson  license         metadataUpdatedAt  \\\n",
       "0  ...           False             False     None  2024-05-28T20:32:16+0000   \n",
       "\n",
       "  provenance                 updatedAt  \\\n",
       "0   OFFICIAL  2024-05-28T20:32:16+0000   \n",
       "\n",
       "                                      webUri  \\\n",
       "0  https://data.cityofnewyork.us/d/erm2-nwe9   \n",
       "\n",
       "                                           approvals  \\\n",
       "0  [{'reviewedAt': 1524193398, 'reviewedAutomatic...   \n",
       "\n",
       "                                        customFields  \\\n",
       "0  {'Update': {'Automation': 'Yes', 'Date Made Pu...   \n",
       "\n",
       "                                                tags  \n",
       "0  [311, 311 service requests, city government, s...  \n",
       "\n",
       "[1 rows x 21 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# example of Metadata API for 311 dataset\n",
    "url = 'https://data.cityofnewyork.us/api/views/metadata/v1/erm2-nwe9/'\n",
    "\n",
    "# fetch the JSON data from the web\n",
    "response = requests.get(url)\n",
    "\n",
    "# parse the JSON response\n",
    "data_dict = response.json()\n",
    "\n",
    "# convert to a df\n",
    "df = pd.DataFrame([data_dict])\n",
    "\n",
    "# preview data\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1 entries, 0 to 0\n",
      "Data columns (total 21 columns):\n",
      " #   Column             Non-Null Count  Dtype \n",
      "---  ------             --------------  ----- \n",
      " 0   id                 1 non-null      object\n",
      " 1   name               1 non-null      object\n",
      " 2   attribution        1 non-null      object\n",
      " 3   attributionLink    0 non-null      object\n",
      " 4   category           1 non-null      object\n",
      " 5   createdAt          1 non-null      object\n",
      " 6   dataUpdatedAt      1 non-null      object\n",
      " 7   dataUri            1 non-null      object\n",
      " 8   description        1 non-null      object\n",
      " 9   domain             1 non-null      object\n",
      " 10  externalId         0 non-null      object\n",
      " 11  hideFromCatalog    1 non-null      bool  \n",
      " 12  hideFromDataJson   1 non-null      bool  \n",
      " 13  license            0 non-null      object\n",
      " 14  metadataUpdatedAt  1 non-null      object\n",
      " 15  provenance         1 non-null      object\n",
      " 16  updatedAt          1 non-null      object\n",
      " 17  webUri             1 non-null      object\n",
      " 18  approvals          1 non-null      object\n",
      " 19  customFields       1 non-null      object\n",
      " 20  tags               1 non-null      object\n",
      "dtypes: bool(2), object(19)\n",
      "memory usage: 282.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "# preview columns\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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
}