01. Download Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import sys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def download_inspections(outfile, start_date=None, end_date=None, limit=None):\n",
    "    if not outfile:\n",
    "        raise ValueError(\"No outfile specified.\")\n",
    "\n",
    "\n",
    "    DATA_URL = \"https://data.cityofnewyork.us/resource/43nn-pn8j.json\"\n",
    "\n",
    "    query = \"\"\"\n",
    "        SELECT\n",
    "            camis,\n",
    "            dba,\n",
    "            boro,\n",
    "            zipcode,\n",
    "            cuisine_description,\n",
    "            inspection_date,\n",
    "            action,\n",
    "            violation_code,\n",
    "            violation_description,\n",
    "            critical_flag,\n",
    "            score,\n",
    "            grade,\n",
    "            inspection_type,\n",
    "            latitude,\n",
    "            longitude\n",
    "    \"\"\"\n",
    "    \n",
    "    if not start_date and end_date:\n",
    "        where = f\"\"\"\n",
    "            WHERE\n",
    "                inspection_date <= \"{end_date}\"\n",
    "    \n",
    "        \"\"\"\n",
    "    elif start_date and not end_date:\n",
    "        where = f\"\"\"\n",
    "            WHERE\n",
    "                inspection_date > \"{start_date}\"\n",
    "    \n",
    "        \"\"\"\n",
    "    elif not start_date and not end_date:    \n",
    "        where = f\"\"\"\"\"\"\n",
    "\n",
    "    elif start_date and end_date:\n",
    "        where = f\"\"\"\n",
    "            WHERE\n",
    "                inspection_date BETWEEN \"{start_date}\" AND \"{end_date}\"\n",
    "        \"\"\"\n",
    "    \n",
    "    if limit:\n",
    "        limit = f\"LIMIT {int(limit)}\"\n",
    "    else:\n",
    "        limit = \"\"\n",
    "        \n",
    "\n",
    "    # put it all together\n",
    "    query += where + limit\n",
    "    \n",
    "    r = requests.get(DATA_URL, params={\"$query\":query})\n",
    "    rows = r.json()\n",
    "    if not r.ok:\n",
    "        print(rows)\n",
    "    data = pd.DataFrame(rows)\n",
    "    print(f'{data.shape[0]} rows downloaded')\n",
    "    data.to_csv(outfile, index=False)\n",
    "    \n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def download_311(outfile, start_date=None, end_date=None, limit=None):\n",
    "\n",
    "    if not outfile:\n",
    "        raise ValueError(\"No outfile specified.\")\n",
    "\n",
    "    DATA_URL = \"https://data.cityofnewyork.us/resource/erm2-nwe9.json\"\n",
    "    \n",
    "    query = f\"\"\"\n",
    "        SELECT\n",
    "            created_date,\n",
    "            agency,\n",
    "            complaint_type,\n",
    "            descriptor,\n",
    "            location_type,\n",
    "            incident_zip,\n",
    "            address_type,\n",
    "            city,\n",
    "            facility_type,\n",
    "            borough,\n",
    "            latitude,\n",
    "            longitude\n",
    "    \"\"\"\n",
    "    \n",
    "    if not start_date and end_date:\n",
    "        where = f\"\"\"\n",
    "            WHERE\n",
    "                agency in('DEP', 'DOHMH', 'DSNY')\n",
    "                AND created_date <= \"{end_date}\"\n",
    "    \n",
    "        \"\"\"\n",
    "    elif start_date and not end_date:\n",
    "        where = f\"\"\"\n",
    "            WHERE\n",
    "                agency in('DEP', 'DOHMH', 'DSNY')\n",
    "                AND created_date > \"{start_date}\"\n",
    "    \n",
    "        \"\"\"\n",
    "    elif not start_date and not end_date:    \n",
    "        where = f\"\"\"\n",
    "            WHERE\n",
    "                agency in('DEP', 'DOHMH', 'DSNY')\n",
    "        \"\"\"\n",
    "\n",
    "    elif start_date and end_date:\n",
    "        where = f\"\"\"\n",
    "            WHERE\n",
    "                agency in('DEP', 'DOHMH', 'DSNY')\n",
    "                AND created_date BETWEEN \"{start_date}\" AND \"{end_date}\"\n",
    "        \"\"\"\n",
    "    \n",
    "    if limit:\n",
    "        limit = f\"LIMIT {int(limit)}\"\n",
    "    else:\n",
    "        limit = \"\"\n",
    "        \n",
    "\n",
    "    # put it all together\n",
    "    query += where + limit\n",
    "    \n",
    "    r = requests.get(DATA_URL, params={\"$query\":query})        \n",
    "    rows = r.json()\n",
    "    if not r.ok:\n",
    "        print(query)\n",
    "        print(rows)\n",
    "    data = pd.DataFrame(rows)\n",
    "    print(f'{data.shape[0]} rows downloaded')\n",
    "    data.to_csv(outfile, index=False)\n",
    "    \n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def download_business(outfile, start_date='2016-01-01', end_date='2018-12-31', limit=20):\n",
    "\n",
    "    if not outfile:\n",
    "        raise ValueError(\"No outfile specified.\")\n",
    "\n",
    "    DATA_URL = \"https://data.cityofnewyork.us/resource/w7w3-xahh.json\"\n",
    "    \n",
    "    query = f\"\"\"\n",
    "        SELECT\n",
    "            license_nbr as lic_id,\n",
    "            license_status as status,\n",
    "            lic_expir_dd as lic_expire,\n",
    "            license_creation_date as lic_create,\n",
    "            industry,\n",
    "            business_name as name,\n",
    "            address_zip,\n",
    "            address_borough,\n",
    "            latitude,\n",
    "            longitude\n",
    "\n",
    "        LIMIT {int(limit)}\n",
    "    \"\"\"\n",
    "    \n",
    "    r = requests.get(DATA_URL, params={\"$query\":query})        \n",
    "    rows = r.json()\n",
    "    if not r.ok:\n",
    "        print(rows)\n",
    "    data = pd.DataFrame(rows)\n",
    "    data.to_csv(outfile, index=False)\n",
    "    \n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def download_nypd_historic(outfile, start_date=None, end_date=None, limit=None):\n",
    "\n",
    "    if not outfile:\n",
    "        raise ValueError(\"No outfile specified.\")\n",
    "\n",
    "    DATA_URL = \"https://data.cityofnewyork.us/resource/qgea-i56i.json\"\n",
    "    \n",
    "    query = f\"\"\"\n",
    "        SELECT\n",
    "            cmplnt_fr_dt as complaint_date,\n",
    "            ky_cd as complaint_code,\n",
    "            ofns_desc as complaint_description,\n",
    "            law_cat_cd as offense_lvl,\n",
    "            boro_nm as boro,\n",
    "            prem_typ_desc as premisis_type,\n",
    "            latitude,\n",
    "            longitude\n",
    "    \"\"\"\n",
    "    \n",
    "    if not start_date and end_date:\n",
    "        where = f\"\"\"\n",
    "            WHERE\n",
    "                complaint_date <= \"{end_date}\"\n",
    "        \"\"\"\n",
    "    elif start_date and not end_date:\n",
    "        where = f\"\"\"\n",
    "            WHERE\n",
    "                complaint_date > \"{start_date}\"\n",
    "        \"\"\"\n",
    "    elif not start_date and not end_date:    \n",
    "        where = f\"\"\"\"\"\"\n",
    "\n",
    "    elif start_date and end_date:\n",
    "        where = f\"\"\"\n",
    "            WHERE\n",
    "                complaint_date BETWEEN \"{start_date}\" AND \"{end_date}\"\n",
    "        \"\"\"\n",
    "    if limit:\n",
    "        limit = f\"LIMIT {int(limit)}\"\n",
    "    else:\n",
    "        limit = \"\"\n",
    "        \n",
    "    # put it all together\n",
    "    query += where + limit\n",
    "    \n",
    "    r = requests.get(DATA_URL, params={\"$query\":query})        \n",
    "    rows = r.json()\n",
    "    if not r.ok:\n",
    "        print(rows)\n",
    "    data = pd.DataFrame(rows)\n",
    "    print(f'{data.shape[0]} rows downloaded')\n",
    "    data.to_csv(outfile, index=False)\n",
    "    \n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df = download_inspections('./data/inspections.csv', start_date='2016-01-01', limit=1e6)\n",
    "df = download_311('./data/311.csv', start_date='2016-01-01', limit=2e6)\n",
    "# df = download_business('./data/business.csv', limit=3e5)\n",
    "# df = download_nypd_historic('./data/nypd.csv', start_date='2016-01-01', limit=2e6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Weather\n",
    "\n",
    "For the historical weather, you need to download the data from NOAA. Use this website.\n",
    "\n",
    "https://www.ncdc.noaa.gov/cdo-web/datasets/GHCND/stations/GHCND:USW00094728/detail\n",
    "\n",
    "As far as I can tell there isn't an automatic way to get the weather data from this website. Although, I didn't look super hard.\n",
    "\n",
    "Documentaion: https://www1.ncdc.noaa.gov/pub/data/cdo/documentation/GHCND_documentation.pdf\n",
    "\n",
    "Here are the columns if you download all the data.\n",
    "\n",
    " Precipitation (PRCP)\n",
    " Snow depth (SNWD)\n",
    " Snowfall (SNOW)\n",
    " Maximum temperature (TMAX)\n",
    " Minimum temperature (TMIN)\n",
    " Average wind speed (AWND)\n",
    " Direction of fastest 2-minute wind (WDF2)\n",
    " Direction of fastest 5-second wind (WDF5)\n",
    " Fastest 2-minute wind speed (WSF2)\n",
    " Fastest 5-second wind speed (WSF5)\n",
    " Peak gust time (PGTM)\n",
    " Weather types (WT**)\n",
    "\n",
    "Here are the weather types -- example WT01\n",
    "\n",
    "01 = Fog, ice fog, or freezing fog (may include heavy fog)\n",
    "02 = Heavy fog or heaving freezing fog (not always distinguished from fog)\n",
    "03 = Thunder\n",
    "04 = Ice pellets, sleet, snow pellets, or small hail\n",
    "05 = Hail (may include small hail)\n",
    "06 = Glaze or rime\n",
    "07 = Dust, volcanic ash, blowing dust, blowing sand, or blowing obstruction\n",
    "08 = Smoke or haze\n",
    "09 = Blowing or drifting snow\n",
    "10 = Tornado, waterspout, or funnel cloud\n",
    "11 = High or damaging winds\n",
    "12 = Blowing spray\n",
    "13 = Mist\n",
    "14 = Drizzle\n",
    "15 = Freezing drizzle\n",
    "16 = Rain (may include freezing rain, drizzle, and freezing drizzle)\n",
    "17 = Freezing rain\n",
    "18 = Snow, snow pellets, snow grains, or ice crystals\n",
    "19 = Unknown source of precipitation\n",
    "21 = Ground fog\n",
    "22 = Ice fog or freezing fog\n",
    "\n"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}