1_Data acquisitions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "complete-burning",
   "metadata": {},
   "source": [
    "Since some of the data files acquired are too large to import in excel/google sheets, I used Python and pandas to select smaller amount of data that we're interested in. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "acoustic-bonus",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "abandoned-nicaragua",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(\"sqlite:///data/restaurants.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "applicable-merchandise",
   "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>Vehicle Type</th>\n",
       "      <th>Taxi Company Borough</th>\n",
       "      <th>Taxi Pick Up Location</th>\n",
       "      <th>Bridge Highway Name</th>\n",
       "      <th>Bridge Highway Direction</th>\n",
       "      <th>Road Ramp</th>\n",
       "      <th>Bridge Highway Segment</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39981109</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>08/15/2018 12:00:00 AM</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>Rat Sighting</td>\n",
       "      <td>Other (Explain Below)</td>\n",
       "      <td>11209</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>40.63372397873045</td>\n",
       "      <td>-74.02388342767199</td>\n",
       "      <td>(40.63372397873045, -74.02388342767199)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39981156</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>08/10/2018 12:00:00 AM</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>Rat Sighting</td>\n",
       "      <td>3+ Family Apt. Building</td>\n",
       "      <td>11372</td>\n",
       "      <td>33-33 82ND ST</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>40.754279636875594</td>\n",
       "      <td>-73.88499034099145</td>\n",
       "      <td>(40.754279636875594, -73.88499034099145)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39981569</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>08/11/2018 12:00:00 AM</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>Signs of Rodents</td>\n",
       "      <td>3+ Family Apt. Building</td>\n",
       "      <td>10032</td>\n",
       "      <td>478 WEST  158 STREET</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>40.83300208979604</td>\n",
       "      <td>-73.94044685663981</td>\n",
       "      <td>(40.83300208979604, -73.94044685663981)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39981599</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>08/23/2018 12:00:00 AM</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>Rat Sighting</td>\n",
       "      <td>Other (Explain Below)</td>\n",
       "      <td>11206</td>\n",
       "      <td>167 HEYWARD STREET</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>40.7025703051472</td>\n",
       "      <td>-73.95477695269145</td>\n",
       "      <td>(40.7025703051472, -73.95477695269145)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39981657</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>08/17/2018 03:40:41 PM</td>\n",
       "      <td>DOHMH</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>Rat Sighting</td>\n",
       "      <td>Other (Explain Below)</td>\n",
       "      <td>11221</td>\n",
       "      <td>600 QUINCY STREET</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>40.68854430730697</td>\n",
       "      <td>-73.9368041756644</td>\n",
       "      <td>(40.68854430730697, -73.9368041756644)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unique Key Created Date             Closed Date Agency  \\\n",
       "0   39981109   2018-08-10  08/15/2018 12:00:00 AM  DOHMH   \n",
       "1   39981156   2018-08-10  08/10/2018 12:00:00 AM  DOHMH   \n",
       "2   39981569   2018-08-11  08/11/2018 12:00:00 AM  DOHMH   \n",
       "3   39981599   2018-08-12  08/23/2018 12:00:00 AM  DOHMH   \n",
       "4   39981657   2018-08-11  08/17/2018 03:40:41 PM  DOHMH   \n",
       "\n",
       "                               Agency Name Complaint Type        Descriptor  \\\n",
       "0  Department of Health and Mental Hygiene         Rodent      Rat Sighting   \n",
       "1  Department of Health and Mental Hygiene         Rodent      Rat Sighting   \n",
       "2  Department of Health and Mental Hygiene         Rodent  Signs of Rodents   \n",
       "3  Department of Health and Mental Hygiene         Rodent      Rat Sighting   \n",
       "4  Department of Health and Mental Hygiene         Rodent      Rat Sighting   \n",
       "\n",
       "             Location Type Incident Zip      Incident Address  ...  \\\n",
       "0    Other (Explain Below)        11209                        ...   \n",
       "1  3+ Family Apt. Building        11372         33-33 82ND ST  ...   \n",
       "2  3+ Family Apt. Building        10032  478 WEST  158 STREET  ...   \n",
       "3    Other (Explain Below)        11206    167 HEYWARD STREET  ...   \n",
       "4    Other (Explain Below)        11221     600 QUINCY STREET  ...   \n",
       "\n",
       "  Vehicle Type Taxi Company Borough Taxi Pick Up Location Bridge Highway Name  \\\n",
       "0                                                                               \n",
       "1                                                                               \n",
       "2                                                                               \n",
       "3                                                                               \n",
       "4                                                                               \n",
       "\n",
       "  Bridge Highway Direction Road Ramp Bridge Highway Segment  \\\n",
       "0                                                             \n",
       "1                                                             \n",
       "2                                                             \n",
       "3                                                             \n",
       "4                                                             \n",
       "\n",
       "             Latitude           Longitude  \\\n",
       "0   40.63372397873045  -74.02388342767199   \n",
       "1  40.754279636875594  -73.88499034099145   \n",
       "2   40.83300208979604  -73.94044685663981   \n",
       "3    40.7025703051472  -73.95477695269145   \n",
       "4   40.68854430730697   -73.9368041756644   \n",
       "\n",
       "                                   Location  \n",
       "0   (40.63372397873045, -74.02388342767199)  \n",
       "1  (40.754279636875594, -73.88499034099145)  \n",
       "2   (40.83300208979604, -73.94044685663981)  \n",
       "3    (40.7025703051472, -73.95477695269145)  \n",
       "4    (40.68854430730697, -73.9368041756644)  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_sql(\"\"\"SELECT * FROM calls_data WHERE \"Complaint Type\"=\"Rodent\";\"\"\", engine, parse_dates=[\"Created Date\"])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "concrete-subsection",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(67709, 41)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "calls_df = df[df['Created Date']> \"1/1/2019\"]\n",
    "calls_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "norwegian-fabric",
   "metadata": {},
   "outputs": [],
   "source": [
    "calls_df.to_csv(\"./data/rodent_calls.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "oriental-subscriber",
   "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>CAMIS</th>\n",
       "      <th>DBA</th>\n",
       "      <th>BORO</th>\n",
       "      <th>BUILDING</th>\n",
       "      <th>STREET</th>\n",
       "      <th>ZIPCODE</th>\n",
       "      <th>PHONE</th>\n",
       "      <th>CUISINE DESCRIPTION</th>\n",
       "      <th>INSPECTION DATE</th>\n",
       "      <th>ACTION</th>\n",
       "      <th>...</th>\n",
       "      <th>RECORD DATE</th>\n",
       "      <th>INSPECTION TYPE</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Community Board</th>\n",
       "      <th>Council District</th>\n",
       "      <th>Census Tract</th>\n",
       "      <th>BIN</th>\n",
       "      <th>BBL</th>\n",
       "      <th>NTA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41347625</td>\n",
       "      <td>EAT &amp; GO ISTANBUL, NEW YORK</td>\n",
       "      <td>Queens</td>\n",
       "      <td></td>\n",
       "      <td>JFK AIRPORT</td>\n",
       "      <td>11430</td>\n",
       "      <td>7187512826</td>\n",
       "      <td></td>\n",
       "      <td>2020-02-27</td>\n",
       "      <td>U</td>\n",
       "      <td>...</td>\n",
       "      <td>04/20/2021</td>\n",
       "      <td>Cycle Inspection / Initial Inspection</td>\n",
       "      <td>40.648312826068</td>\n",
       "      <td>-73.788281497594</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>1</th>\n",
       "      <td>50001914</td>\n",
       "      <td>ATLAS SOCIAL CLUB</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>753</td>\n",
       "      <td>9 AVENUE</td>\n",
       "      <td>10019</td>\n",
       "      <td>2122628527</td>\n",
       "      <td></td>\n",
       "      <td>2019-10-29</td>\n",
       "      <td>U</td>\n",
       "      <td>...</td>\n",
       "      <td>04/20/2021</td>\n",
       "      <td>Cycle Inspection / Initial Inspection</td>\n",
       "      <td>40.763903510982</td>\n",
       "      <td>-73.988653981842</td>\n",
       "      <td>104</td>\n",
       "      <td>03</td>\n",
       "      <td>013300</td>\n",
       "      <td>1026689</td>\n",
       "      <td>1010600035</td>\n",
       "      <td>MN15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50050805</td>\n",
       "      <td>#1 Chinese Restaurant</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>825</td>\n",
       "      <td>WEST  181 STREET</td>\n",
       "      <td>10033</td>\n",
       "      <td>2127403188</td>\n",
       "      <td></td>\n",
       "      <td>2018-04-11</td>\n",
       "      <td>D</td>\n",
       "      <td>...</td>\n",
       "      <td>04/20/2021</td>\n",
       "      <td>Cycle Inspection / Re-inspection</td>\n",
       "      <td>40.851256157186</td>\n",
       "      <td>-73.93951897372</td>\n",
       "      <td>112</td>\n",
       "      <td>10</td>\n",
       "      <td>027300</td>\n",
       "      <td>1064342</td>\n",
       "      <td>1021797502</td>\n",
       "      <td>MN35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50080293</td>\n",
       "      <td>HUNGRY BURRITO</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>510</td>\n",
       "      <td>MORGAN AVENUE</td>\n",
       "      <td>11222</td>\n",
       "      <td>7183871006</td>\n",
       "      <td></td>\n",
       "      <td>2018-12-13</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>04/20/2021</td>\n",
       "      <td>Cycle Inspection / Initial Inspection</td>\n",
       "      <td>40.72160175308</td>\n",
       "      <td>-73.938464608393</td>\n",
       "      <td>301</td>\n",
       "      <td>34</td>\n",
       "      <td>044900</td>\n",
       "      <td>3069872</td>\n",
       "      <td>3028350001</td>\n",
       "      <td>BK90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>41319055</td>\n",
       "      <td>GRAND SICHUAN EASTERN</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>1049</td>\n",
       "      <td>2 AVENUE</td>\n",
       "      <td>10022</td>\n",
       "      <td>2123555855</td>\n",
       "      <td></td>\n",
       "      <td>2019-01-10</td>\n",
       "      <td>D</td>\n",
       "      <td>...</td>\n",
       "      <td>04/20/2021</td>\n",
       "      <td>Cycle Inspection / Initial Inspection</td>\n",
       "      <td>40.758206436667</td>\n",
       "      <td>-73.966102032937</td>\n",
       "      <td>106</td>\n",
       "      <td>04</td>\n",
       "      <td>010800</td>\n",
       "      <td>1038582</td>\n",
       "      <td>1013290023</td>\n",
       "      <td>MN19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      CAMIS                          DBA       BORO BUILDING  \\\n",
       "0  41347625  EAT & GO ISTANBUL, NEW YORK     Queens            \n",
       "1  50001914            ATLAS SOCIAL CLUB  Manhattan      753   \n",
       "2  50050805        #1 Chinese Restaurant  Manhattan      825   \n",
       "3  50080293               HUNGRY BURRITO   Brooklyn      510   \n",
       "4  41319055        GRAND SICHUAN EASTERN  Manhattan     1049   \n",
       "\n",
       "             STREET ZIPCODE       PHONE CUISINE DESCRIPTION INSPECTION DATE  \\\n",
       "0       JFK AIRPORT   11430  7187512826                          2020-02-27   \n",
       "1          9 AVENUE   10019  2122628527                          2019-10-29   \n",
       "2  WEST  181 STREET   10033  2127403188                          2018-04-11   \n",
       "3     MORGAN AVENUE   11222  7183871006                          2018-12-13   \n",
       "4          2 AVENUE   10022  2123555855                          2019-01-10   \n",
       "\n",
       "  ACTION  ... RECORD DATE                        INSPECTION TYPE  \\\n",
       "0      U  ...  04/20/2021  Cycle Inspection / Initial Inspection   \n",
       "1      U  ...  04/20/2021  Cycle Inspection / Initial Inspection   \n",
       "2      D  ...  04/20/2021       Cycle Inspection / Re-inspection   \n",
       "3      F  ...  04/20/2021  Cycle Inspection / Initial Inspection   \n",
       "4      D  ...  04/20/2021  Cycle Inspection / Initial Inspection   \n",
       "\n",
       "          Latitude         Longitude Community Board Council District  \\\n",
       "0  40.648312826068  -73.788281497594                                    \n",
       "1  40.763903510982  -73.988653981842             104               03   \n",
       "2  40.851256157186   -73.93951897372             112               10   \n",
       "3   40.72160175308  -73.938464608393             301               34   \n",
       "4  40.758206436667  -73.966102032937             106               04   \n",
       "\n",
       "  Census Tract      BIN         BBL   NTA  \n",
       "0                                          \n",
       "1       013300  1026689  1010600035  MN15  \n",
       "2       027300  1064342  1021797502  MN35  \n",
       "3       044900  3069872  3028350001  BK90  \n",
       "4       010800  1038582  1013290023  MN19  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_sql(\"\"\"SELECT * FROM Inspection_data WHERE \"VIOLATION CODE\"=\"04K\";\"\"\", engine, parse_dates=[\"INSPECTION DATE\"])\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "unique-interstate",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.to_csv(\"./data/inspections.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "appointed-lloyd",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:metis] *",
   "language": "python",
   "name": "conda-env-metis-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}