1 - Downloading data from NYC Open Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NYC Open Data\n",
    "https://opendata.cityofnewyork.us/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "NYC Open Data is a polatform to access open data produced and used by NY City government. \n",
    "\n",
    "One can download the data in two ways:\n",
    "    + Using the website\n",
    "    + Using the Socrata Open Data API\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using the Website\n",
    "\n",
    "From now on, let's focus on the 311 Service Requests from 2010 to Present: \n",
    "\n",
    "https://data.cityofnewyork.us/Social-Services/311-Service-Requests-from-2010-to-Present/erm2-nwe9\n",
    "\n",
    "One can download the CSV file with all the historical data from 2010 to Present. \n",
    "\n",
    "**Pro's**: You get all the data at once\n",
    "\n",
    "**Cons**: The file is heavy (>20 GB) and might be hard to open in a laptop without enough RAM."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Drag Racing](figs/311_download_screenshot.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using the Socrata Open Data API\n",
    "\n",
    "I fully recommend this approach since it allows you to access the part of information you need. You also save time since you can download the information in different runs."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For this to work, you need to obtain an app token. First, create a free account at:\n",
    "\n",
    "https://data.cityofnewyork.us/login\n",
    "\n",
    "Please remember the email and password used, because you will need them later.\n",
    "\n",
    "Once you log in, you click on 'Developer Settings' -> 'Create New App Token': "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![AppToken1](figs/appToken1.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we are ready to download the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Client Connection\n",
    "\n",
    "We can create a client connection using using the sodapy library. You will need: \n",
    "    + token\n",
    "    + username (the email address)\n",
    "    + password\n",
    "\n",
    "For more details about sodapy library check: https://pypi.org/project/sodapy/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:root:Requests made without an app_token will be subject to strict throttling limits.\n"
     ]
    }
   ],
   "source": [
    "# !pip install sodapy\n",
    "import pandas as pd\n",
    "from sodapy import Socrata\n",
    "\n",
    "# Unauthenticated client only works with public data sets. Note 'None'\n",
    "# in place of application token, and no username or password:\n",
    "client = Socrata(\"data.cityofnewyork.us\", None)\n",
    "\n",
    "# Example authenticated client (needed for non-public datasets):\n",
    "MyAppToken = 'YOUR_TOKEN'\n",
    "client = Socrata(\"data.cityofnewyork.us\",\n",
    "                  MyAppToken,\n",
    "                  username=\"YOUR_USERNAME\",\n",
    "                  password=\"YOUR_PASSWORD\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One convenient feature of using the Socrate Open Data API, is that you can do queries specifying the condition that you need for your data, just like a 'WHERE (CONDITION)' statement within a SQL query. This is implemented via the Socrates Query Language 'SoQL'.\n",
    "\n",
    "For more details regarding possible queries, please check: https://dev.socrata.com/docs/queries/\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the first 100 observation in January-2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>unique_key</th>\n",
       "      <th>created_date</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>complaint_type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>location_type</th>\n",
       "      <th>incident_zip</th>\n",
       "      <th>incident_address</th>\n",
       "      <th>...</th>\n",
       "      <th>x_coordinate_state_plane</th>\n",
       "      <th>y_coordinate_state_plane</th>\n",
       "      <th>open_data_channel_type</th>\n",
       "      <th>park_facility_name</th>\n",
       "      <th>park_borough</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "      <th>taxi_pick_up_location</th>\n",
       "      <th>facility_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>45289558</td>\n",
       "      <td>2020-01-01T00:00:00.000</td>\n",
       "      <td>2020-01-15T00:00:01.000</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>Food Poisoning</td>\n",
       "      <td>3 or More</td>\n",
       "      <td>Other (Explain Below)</td>\n",
       "      <td>11215</td>\n",
       "      <td>625 UNION STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>988567</td>\n",
       "      <td>186277</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>Unspecified</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>40.677963041857886</td>\n",
       "      <td>-73.98443609121443</td>\n",
       "      <td>{'latitude': '40.677963041857886', 'longitude'...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>45288728</td>\n",
       "      <td>2020-01-01T00:00:00.000</td>\n",
       "      <td>2020-01-02T00:00:01.000</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>Food Poisoning</td>\n",
       "      <td>1 or 2</td>\n",
       "      <td>Restaurant/Bar/Deli/Bakery</td>\n",
       "      <td>11225</td>\n",
       "      <td>985 NOSTRAND AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>997849</td>\n",
       "      <td>181347</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>Unspecified</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>40.66442190467239</td>\n",
       "      <td>-73.95098201556382</td>\n",
       "      <td>{'latitude': '40.66442190467239', 'longitude':...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>45288240</td>\n",
       "      <td>2020-01-01T00:00:00.000</td>\n",
       "      <td>2020-01-02T00:00:01.000</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>Food Poisoning</td>\n",
       "      <td>1 or 2</td>\n",
       "      <td>Restaurant/Bar/Deli/Bakery</td>\n",
       "      <td>11385</td>\n",
       "      <td>1717 CORNELIA STREET</td>\n",
       "      <td>...</td>\n",
       "      <td>1010470</td>\n",
       "      <td>194453</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>Unspecified</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>40.700366489799876</td>\n",
       "      <td>-73.90543829006366</td>\n",
       "      <td>{'latitude': '40.700366489799876', 'longitude'...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45287907</td>\n",
       "      <td>2020-01-01T00:00:00.000</td>\n",
       "      <td>2020-01-02T00:00:01.000</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>Food Poisoning</td>\n",
       "      <td>3 or More</td>\n",
       "      <td>Restaurant/Bar/Deli/Bakery</td>\n",
       "      <td>11214</td>\n",
       "      <td>1602 SHORE PARKWAY</td>\n",
       "      <td>...</td>\n",
       "      <td>984202</td>\n",
       "      <td>156289</td>\n",
       "      <td>ONLINE</td>\n",
       "      <td>Unspecified</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>40.59565343138651</td>\n",
       "      <td>-74.00017283917487</td>\n",
       "      <td>{'latitude': '40.59565343138651', 'longitude':...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>45285651</td>\n",
       "      <td>2020-01-01T00:00:00.000</td>\n",
       "      <td>2020-01-02T00:00:01.000</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>Food Poisoning</td>\n",
       "      <td>1 or 2</td>\n",
       "      <td>Restaurant/Bar/Deli/Bakery</td>\n",
       "      <td>10458</td>\n",
       "      <td>2701 DECATUR AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>1015012</td>\n",
       "      <td>254391</td>\n",
       "      <td>PHONE</td>\n",
       "      <td>Unspecified</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>40.86486556770799</td>\n",
       "      <td>-73.88878325729915</td>\n",
       "      <td>{'latitude': '40.86486556770799', 'longitude':...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_key             created_date              closed_date agency  \\\n",
       "0   45289558  2020-01-01T00:00:00.000  2020-01-15T00:00:01.000  DOHMH   \n",
       "1   45288728  2020-01-01T00:00:00.000  2020-01-02T00:00:01.000  DOHMH   \n",
       "2   45288240  2020-01-01T00:00:00.000  2020-01-02T00:00:01.000  DOHMH   \n",
       "3   45287907  2020-01-01T00:00:00.000  2020-01-02T00:00:01.000  DOHMH   \n",
       "4   45285651  2020-01-01T00:00:00.000  2020-01-02T00:00:01.000  DOHMH   \n",
       "\n",
       "                               agency_name  complaint_type descriptor  \\\n",
       "0  Department of Health and Mental Hygiene  Food Poisoning  3 or More   \n",
       "1  Department of Health and Mental Hygiene  Food Poisoning     1 or 2   \n",
       "2  Department of Health and Mental Hygiene  Food Poisoning     1 or 2   \n",
       "3  Department of Health and Mental Hygiene  Food Poisoning  3 or More   \n",
       "4  Department of Health and Mental Hygiene  Food Poisoning     1 or 2   \n",
       "\n",
       "                location_type incident_zip      incident_address  ...  \\\n",
       "0       Other (Explain Below)        11215      625 UNION STREET  ...   \n",
       "1  Restaurant/Bar/Deli/Bakery        11225   985 NOSTRAND AVENUE  ...   \n",
       "2  Restaurant/Bar/Deli/Bakery        11385  1717 CORNELIA STREET  ...   \n",
       "3  Restaurant/Bar/Deli/Bakery        11214    1602 SHORE PARKWAY  ...   \n",
       "4  Restaurant/Bar/Deli/Bakery        10458   2701 DECATUR AVENUE  ...   \n",
       "\n",
       "  x_coordinate_state_plane y_coordinate_state_plane open_data_channel_type  \\\n",
       "0                   988567                   186277                 ONLINE   \n",
       "1                   997849                   181347                 ONLINE   \n",
       "2                  1010470                   194453                 ONLINE   \n",
       "3                   984202                   156289                 ONLINE   \n",
       "4                  1015012                   254391                  PHONE   \n",
       "\n",
       "  park_facility_name park_borough            latitude           longitude  \\\n",
       "0        Unspecified     BROOKLYN  40.677963041857886  -73.98443609121443   \n",
       "1        Unspecified     BROOKLYN   40.66442190467239  -73.95098201556382   \n",
       "2        Unspecified       QUEENS  40.700366489799876  -73.90543829006366   \n",
       "3        Unspecified     BROOKLYN   40.59565343138651  -74.00017283917487   \n",
       "4        Unspecified        BRONX   40.86486556770799  -73.88878325729915   \n",
       "\n",
       "                                            location taxi_pick_up_location  \\\n",
       "0  {'latitude': '40.677963041857886', 'longitude'...                   NaN   \n",
       "1  {'latitude': '40.66442190467239', 'longitude':...                   NaN   \n",
       "2  {'latitude': '40.700366489799876', 'longitude'...                   NaN   \n",
       "3  {'latitude': '40.59565343138651', 'longitude':...                   NaN   \n",
       "4  {'latitude': '40.86486556770799', 'longitude':...                   NaN   \n",
       "\n",
       "  facility_type  \n",
       "0           NaN  \n",
       "1           NaN  \n",
       "2           NaN  \n",
       "3           NaN  \n",
       "4           NaN  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_condition = \"created_date between '2020-01-01' and '2020-01-31'\"\n",
    "results = client.get(\"erm2-nwe9\", where=query_condition, limit=100)\n",
    "df = pd.DataFrame.from_records(results) \n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get the first and last days of every month in a given year, we can use the function `first_and_last_date`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defaultdict(list,\n",
       "            {1: ['2020-01-01', '2020-01-31'],\n",
       "             2: ['2020-02-01', '2020-02-29'],\n",
       "             3: ['2020-03-01', '2020-03-31'],\n",
       "             4: ['2020-04-01', '2020-04-30'],\n",
       "             5: ['2020-05-01', '2020-05-31'],\n",
       "             6: ['2020-06-01', '2020-06-30'],\n",
       "             7: ['2020-07-01', '2020-07-31'],\n",
       "             8: ['2020-08-01', '2020-08-31'],\n",
       "             9: ['2020-09-01', '2020-09-30'],\n",
       "             10: ['2020-10-01', '2020-10-31'],\n",
       "             11: ['2020-11-01', '2020-11-30'],\n",
       "             12: ['2020-12-01', '2020-12-31']})"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import calendar\n",
    "import datetime\n",
    "import collections\n",
    "def first_and_last_date(year):\n",
    "    # year:= integer value\n",
    "    # returns: dictionary with first and last dates for a given year\n",
    "    first_and_last_days = collections.defaultdict(list)\n",
    "    for i in range(1,13):\n",
    "        _, num_days = calendar.monthrange(year, i)\n",
    "        first_day = datetime.date(year, i, 1).strftime('%Y-%m-%d')\n",
    "        last_day = datetime.date(year, i, num_days).strftime('%Y-%m-%d')\n",
    "        first_and_last_days[i] = [first_day, last_day ]\n",
    "    return(first_and_last_days)\n",
    "\n",
    "months_days = first_and_last_date(year = 2020)\n",
    "months_days"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Download single file for a month**: In this example we download the data for January 2020. Notice that the function queries the data in batches of 3000 observations. We store all the queries into a list of dataframes and then we merge them all."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "from os import path\n",
    "\n",
    "# Input\n",
    "year = 2020\n",
    "month = 1\n",
    "\n",
    "# Code\n",
    "df_list = []\n",
    "lim = 3000\n",
    "i = 0\n",
    "offset = 0\n",
    "months_days = first_and_last_date(year = year)\n",
    "filename = ''.join(['%i-' % year, str(month).zfill(2),'.csv'])\n",
    "while(True):\n",
    "    if path.exists(filename): break\n",
    "    where_cond = ''.join([\"created_date between '\", months_days[month][0], \"' and '\",months_days[month][1], \"'\"])\n",
    "    results = client.get(\"erm2-nwe9\", \n",
    "                         where = where_cond, \n",
    "                         limit = lim, offset = lim*i)\n",
    "    if not results: break\n",
    "    df = pd.DataFrame.from_records(results)\n",
    "    df_list.append(df)\n",
    "    i += 1\n",
    "df = pd.concat(df_list, axis=0, sort=True)\n",
    "df.to_csv(filename)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(162301, 41)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>address_type</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>bbl</th>\n",
       "      <th>borough</th>\n",
       "      <th>bridge_highway_direction</th>\n",
       "      <th>bridge_highway_name</th>\n",
       "      <th>bridge_highway_segment</th>\n",
       "      <th>city</th>\n",
       "      <th>closed_date</th>\n",
       "      <th>...</th>\n",
       "      <th>resolution_description</th>\n",
       "      <th>road_ramp</th>\n",
       "      <th>status</th>\n",
       "      <th>street_name</th>\n",
       "      <th>taxi_company_borough</th>\n",
       "      <th>taxi_pick_up_location</th>\n",
       "      <th>unique_key</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>x_coordinate_state_plane</th>\n",
       "      <th>y_coordinate_state_plane</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>3004340016</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>2020-01-15T00:00:01.000</td>\n",
       "      <td>...</td>\n",
       "      <td>The Health Departmentâs Office of Environmen...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Closed</td>\n",
       "      <td>UNION STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45289558</td>\n",
       "      <td>NaN</td>\n",
       "      <td>988567</td>\n",
       "      <td>186277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>3013090001</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>2020-01-02T00:00:01.000</td>\n",
       "      <td>...</td>\n",
       "      <td>The Department of Health and Mental Hygiene ha...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Closed</td>\n",
       "      <td>NOSTRAND AVENUE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45288728</td>\n",
       "      <td>NaN</td>\n",
       "      <td>997849</td>\n",
       "      <td>181347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>4034610052</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ridgewood</td>\n",
       "      <td>2020-01-02T00:00:01.000</td>\n",
       "      <td>...</td>\n",
       "      <td>The Department of Health and Mental Hygiene ha...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Closed</td>\n",
       "      <td>CORNELIA STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45288240</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1010470</td>\n",
       "      <td>194453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>3064910050</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>2020-01-02T00:00:01.000</td>\n",
       "      <td>...</td>\n",
       "      <td>The Department of Health and Mental Hygiene ha...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Closed</td>\n",
       "      <td>SHORE PARKWAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45287907</td>\n",
       "      <td>NaN</td>\n",
       "      <td>984202</td>\n",
       "      <td>156289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>2032830055</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>2020-01-02T00:00:01.000</td>\n",
       "      <td>...</td>\n",
       "      <td>The Department of Health and Mental Hygiene ha...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Closed</td>\n",
       "      <td>DECATUR AVENUE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45285651</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1015012</td>\n",
       "      <td>254391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>3073620008</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>2020-01-10T00:00:01.000</td>\n",
       "      <td>...</td>\n",
       "      <td>The Health Departmentâs Office of Environmen...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Closed</td>\n",
       "      <td>NOSTRAND AVENUE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45285347</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000400</td>\n",
       "      <td>157925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>4089330077</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Woodhaven</td>\n",
       "      <td>2020-01-02T09:39:33.000</td>\n",
       "      <td>...</td>\n",
       "      <td>The Department of Health and Mental Hygiene ha...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Closed</td>\n",
       "      <td>JAMAICA AVENUE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45289555</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1025018</td>\n",
       "      <td>191916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>2020-01-02T09:51:29.000</td>\n",
       "      <td>...</td>\n",
       "      <td>The Department of Health and Mental Hygiene ha...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Closed</td>\n",
       "      <td>EAST  149 STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45288120</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1009724</td>\n",
       "      <td>235487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ADDRESS</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>2020-01-02T09:50:09.000</td>\n",
       "      <td>...</td>\n",
       "      <td>The Health Departmentâs Office of Environmen...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Closed</td>\n",
       "      <td>CHURCH AVENUE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45285821</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1005466</td>\n",
       "      <td>177022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NYPD</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>3052300069</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>2020-01-01T03:07:10.000</td>\n",
       "      <td>...</td>\n",
       "      <td>The Police Department issued a summons in resp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Closed</td>\n",
       "      <td>EAST   28 STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45282225</td>\n",
       "      <td>NaN</td>\n",
       "      <td>998097</td>\n",
       "      <td>171717</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  address_type agency                              agency_name         bbl  \\\n",
       "0      ADDRESS  DOHMH  Department of Health and Mental Hygiene  3004340016   \n",
       "1      ADDRESS  DOHMH  Department of Health and Mental Hygiene  3013090001   \n",
       "2      ADDRESS  DOHMH  Department of Health and Mental Hygiene  4034610052   \n",
       "3      ADDRESS  DOHMH  Department of Health and Mental Hygiene  3064910050   \n",
       "4      ADDRESS  DOHMH  Department of Health and Mental Hygiene  2032830055   \n",
       "5      ADDRESS  DOHMH  Department of Health and Mental Hygiene  3073620008   \n",
       "6      ADDRESS  DOHMH  Department of Health and Mental Hygiene  4089330077   \n",
       "7      ADDRESS  DOHMH  Department of Health and Mental Hygiene         NaN   \n",
       "8      ADDRESS  DOHMH  Department of Health and Mental Hygiene         NaN   \n",
       "9          NaN   NYPD          New York City Police Department  3052300069   \n",
       "\n",
       "    borough bridge_highway_direction bridge_highway_name  \\\n",
       "0  BROOKLYN                      NaN                 NaN   \n",
       "1  BROOKLYN                      NaN                 NaN   \n",
       "2    QUEENS                      NaN                 NaN   \n",
       "3  BROOKLYN                      NaN                 NaN   \n",
       "4     BRONX                      NaN                 NaN   \n",
       "5  BROOKLYN                      NaN                 NaN   \n",
       "6    QUEENS                      NaN                 NaN   \n",
       "7     BRONX                      NaN                 NaN   \n",
       "8  BROOKLYN                      NaN                 NaN   \n",
       "9  BROOKLYN                      NaN                 NaN   \n",
       "\n",
       "  bridge_highway_segment       city              closed_date  ...  \\\n",
       "0                    NaN   BROOKLYN  2020-01-15T00:00:01.000  ...   \n",
       "1                    NaN   BROOKLYN  2020-01-02T00:00:01.000  ...   \n",
       "2                    NaN  Ridgewood  2020-01-02T00:00:01.000  ...   \n",
       "3                    NaN   BROOKLYN  2020-01-02T00:00:01.000  ...   \n",
       "4                    NaN      BRONX  2020-01-02T00:00:01.000  ...   \n",
       "5                    NaN   BROOKLYN  2020-01-10T00:00:01.000  ...   \n",
       "6                    NaN  Woodhaven  2020-01-02T09:39:33.000  ...   \n",
       "7                    NaN      BRONX  2020-01-02T09:51:29.000  ...   \n",
       "8                    NaN   BROOKLYN  2020-01-02T09:50:09.000  ...   \n",
       "9                    NaN   BROOKLYN  2020-01-01T03:07:10.000  ...   \n",
       "\n",
       "                              resolution_description road_ramp  status  \\\n",
       "0  The Health Departmentâs Office of Environmen...       NaN  Closed   \n",
       "1  The Department of Health and Mental Hygiene ha...       NaN  Closed   \n",
       "2  The Department of Health and Mental Hygiene ha...       NaN  Closed   \n",
       "3  The Department of Health and Mental Hygiene ha...       NaN  Closed   \n",
       "4  The Department of Health and Mental Hygiene ha...       NaN  Closed   \n",
       "5  The Health Departmentâs Office of Environmen...       NaN  Closed   \n",
       "6  The Department of Health and Mental Hygiene ha...       NaN  Closed   \n",
       "7  The Department of Health and Mental Hygiene ha...       NaN  Closed   \n",
       "8  The Health Departmentâs Office of Environmen...       NaN  Closed   \n",
       "9  The Police Department issued a summons in resp...       NaN  Closed   \n",
       "\n",
       "        street_name taxi_company_borough taxi_pick_up_location unique_key  \\\n",
       "0      UNION STREET                  NaN                   NaN   45289558   \n",
       "1   NOSTRAND AVENUE                  NaN                   NaN   45288728   \n",
       "2   CORNELIA STREET                  NaN                   NaN   45288240   \n",
       "3     SHORE PARKWAY                  NaN                   NaN   45287907   \n",
       "4    DECATUR AVENUE                  NaN                   NaN   45285651   \n",
       "5   NOSTRAND AVENUE                  NaN                   NaN   45285347   \n",
       "6    JAMAICA AVENUE                  NaN                   NaN   45289555   \n",
       "7  EAST  149 STREET                  NaN                   NaN   45288120   \n",
       "8     CHURCH AVENUE                  NaN                   NaN   45285821   \n",
       "9  EAST   28 STREET                  NaN                   NaN   45282225   \n",
       "\n",
       "  vehicle_type x_coordinate_state_plane y_coordinate_state_plane  \n",
       "0          NaN                   988567                   186277  \n",
       "1          NaN                   997849                   181347  \n",
       "2          NaN                  1010470                   194453  \n",
       "3          NaN                   984202                   156289  \n",
       "4          NaN                  1015012                   254391  \n",
       "5          NaN                  1000400                   157925  \n",
       "6          NaN                  1025018                   191916  \n",
       "7          NaN                  1009724                   235487  \n",
       "8          NaN                  1005466                   177022  \n",
       "9          NaN                   998097                   171717  \n",
       "\n",
       "[10 rows x 41 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}