src/01_Neighborhoods.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NYC Neighborhoods (ETL): Extract"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Borough</th>\n",
       "      <th>Neighborhood</th>\n",
       "      <th>ZIP Codes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>Central Bronx</td>\n",
       "      <td>10453, 10457, 10460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx Park and Fordham</td>\n",
       "      <td>10458, 10467, 10468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>High Bridge and Morrisania</td>\n",
       "      <td>10451, 10452, 10456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>Hunts Point and Mott Haven</td>\n",
       "      <td>10454, 10455, 10459, 10474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>Kingsbridge and Riverdale</td>\n",
       "      <td>10463, 10471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>Northeast Bronx</td>\n",
       "      <td>10466, 10469, 10470, 10475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>Southeast Bronx</td>\n",
       "      <td>10461, 10462,10464, 10465, 10472, 10473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Central Brooklyn</td>\n",
       "      <td>11212, 11213, 11216, 11233, 11238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Southwest Brooklyn</td>\n",
       "      <td>11209, 11214, 11228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Borough Park</td>\n",
       "      <td>11204, 11218, 11219, 11230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Canarsie and Flatlands</td>\n",
       "      <td>11234, 11236, 11239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Southern Brooklyn</td>\n",
       "      <td>11223, 11224, 11229, 11235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Northwest Brooklyn</td>\n",
       "      <td>11201, 11205, 11215, 11217, 11231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Flatbush</td>\n",
       "      <td>11203, 11210, 11225, 11226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>East New York and New Lots</td>\n",
       "      <td>11207, 11208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Greenpoint</td>\n",
       "      <td>11211, 11222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Sunset Park</td>\n",
       "      <td>11220, 11232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Bushwick and Williamsburg</td>\n",
       "      <td>11206, 11221, 11237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Central Harlem</td>\n",
       "      <td>10026, 10027, 10030, 10037, 10039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Chelsea and Clinton</td>\n",
       "      <td>10001, 10011, 10018, 10019, 10020, 10036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>East Harlem</td>\n",
       "      <td>10029, 10035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Gramercy Park and Murray Hill</td>\n",
       "      <td>10010, 10016, 10017, 10022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Greenwich Village and Soho</td>\n",
       "      <td>10012, 10013, 10014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lower Manhattan</td>\n",
       "      <td>10004, 10005, 10006, 10007, 10038, 10280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lower East Side</td>\n",
       "      <td>10002, 10003, 10009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Upper East Side</td>\n",
       "      <td>10021, 10028, 10044, 10065, 10075, 10128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Upper West Side</td>\n",
       "      <td>10023, 10024, 10025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Inwood and Washington Heights</td>\n",
       "      <td>10031, 10032, 10033, 10034, 10040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Queens</td>\n",
       "      <td>Northeast Queens</td>\n",
       "      <td>11361, 11362, 11363, 11364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Queens</td>\n",
       "      <td>North Queens</td>\n",
       "      <td>11354, 11355, 11356, 11357, 11358, 11359, 11360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Queens</td>\n",
       "      <td>Central Queens</td>\n",
       "      <td>11365, 11366, 11367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Queens</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>11412, 11423, 11432, 11433, 11434, 11435, 11436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Queens</td>\n",
       "      <td>Northwest Queens</td>\n",
       "      <td>11101, 11102, 11103, 11104, 11105, 11106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Queens</td>\n",
       "      <td>West Central Queens</td>\n",
       "      <td>11374, 11375, 11379, 11385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Queens</td>\n",
       "      <td>Rockaways</td>\n",
       "      <td>11691, 11692, 11693, 11694, 11695, 11697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Queens</td>\n",
       "      <td>Southeast Queens</td>\n",
       "      <td>11004, 11005, 11411, 11413, 11422, 11426, 1142...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Queens</td>\n",
       "      <td>Southwest Queens</td>\n",
       "      <td>11414, 11415, 11416, 11417, 11418, 11419, 1142...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Queens</td>\n",
       "      <td>West Queens</td>\n",
       "      <td>11368, 11369, 11370, 11372, 11373, 11377, 11378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Staten Island</td>\n",
       "      <td>Port Richmond</td>\n",
       "      <td>10302, 10303, 10310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Staten Island</td>\n",
       "      <td>South Shore</td>\n",
       "      <td>10306, 10307, 10308, 10309, 10312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Staten Island</td>\n",
       "      <td>Stapleton and St. George</td>\n",
       "      <td>10301, 10304, 10305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Staten Island</td>\n",
       "      <td>Mid-Island</td>\n",
       "      <td>10314</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Borough                   Neighborhood  \\\n",
       "0           Bronx                  Central Bronx   \n",
       "1           Bronx         Bronx Park and Fordham   \n",
       "2           Bronx     High Bridge and Morrisania   \n",
       "3           Bronx     Hunts Point and Mott Haven   \n",
       "4           Bronx      Kingsbridge and Riverdale   \n",
       "5           Bronx                Northeast Bronx   \n",
       "6           Bronx                Southeast Bronx   \n",
       "7        Brooklyn               Central Brooklyn   \n",
       "8        Brooklyn             Southwest Brooklyn   \n",
       "9        Brooklyn                   Borough Park   \n",
       "10       Brooklyn         Canarsie and Flatlands   \n",
       "11       Brooklyn              Southern Brooklyn   \n",
       "12       Brooklyn             Northwest Brooklyn   \n",
       "13       Brooklyn                       Flatbush   \n",
       "14       Brooklyn     East New York and New Lots   \n",
       "15       Brooklyn                     Greenpoint   \n",
       "16       Brooklyn                    Sunset Park   \n",
       "17       Brooklyn      Bushwick and Williamsburg   \n",
       "18      Manhattan                 Central Harlem   \n",
       "19      Manhattan            Chelsea and Clinton   \n",
       "20      Manhattan                    East Harlem   \n",
       "21      Manhattan  Gramercy Park and Murray Hill   \n",
       "22      Manhattan     Greenwich Village and Soho   \n",
       "23      Manhattan                Lower Manhattan   \n",
       "24      Manhattan                Lower East Side   \n",
       "25      Manhattan                Upper East Side   \n",
       "26      Manhattan                Upper West Side   \n",
       "27      Manhattan  Inwood and Washington Heights   \n",
       "28         Queens               Northeast Queens   \n",
       "29         Queens                   North Queens   \n",
       "30         Queens                 Central Queens   \n",
       "31         Queens                        Jamaica   \n",
       "32         Queens               Northwest Queens   \n",
       "33         Queens            West Central Queens   \n",
       "34         Queens                      Rockaways   \n",
       "35         Queens               Southeast Queens   \n",
       "36         Queens               Southwest Queens   \n",
       "37         Queens                    West Queens   \n",
       "38  Staten Island                  Port Richmond   \n",
       "39  Staten Island                    South Shore   \n",
       "40  Staten Island       Stapleton and St. George   \n",
       "41  Staten Island                     Mid-Island   \n",
       "\n",
       "                                            ZIP Codes  \n",
       "0                                 10453, 10457, 10460  \n",
       "1                                 10458, 10467, 10468  \n",
       "2                                 10451, 10452, 10456  \n",
       "3                          10454, 10455, 10459, 10474  \n",
       "4                                        10463, 10471  \n",
       "5                          10466, 10469, 10470, 10475  \n",
       "6             10461, 10462,10464, 10465, 10472, 10473  \n",
       "7                   11212, 11213, 11216, 11233, 11238  \n",
       "8                                 11209, 11214, 11228  \n",
       "9                          11204, 11218, 11219, 11230  \n",
       "10                                11234, 11236, 11239  \n",
       "11                         11223, 11224, 11229, 11235  \n",
       "12                  11201, 11205, 11215, 11217, 11231  \n",
       "13                         11203, 11210, 11225, 11226  \n",
       "14                                       11207, 11208  \n",
       "15                                       11211, 11222  \n",
       "16                                       11220, 11232  \n",
       "17                                11206, 11221, 11237  \n",
       "18                  10026, 10027, 10030, 10037, 10039  \n",
       "19           10001, 10011, 10018, 10019, 10020, 10036  \n",
       "20                                       10029, 10035  \n",
       "21                         10010, 10016, 10017, 10022  \n",
       "22                                10012, 10013, 10014  \n",
       "23           10004, 10005, 10006, 10007, 10038, 10280  \n",
       "24                                10002, 10003, 10009  \n",
       "25           10021, 10028, 10044, 10065, 10075, 10128  \n",
       "26                                10023, 10024, 10025  \n",
       "27                  10031, 10032, 10033, 10034, 10040  \n",
       "28                         11361, 11362, 11363, 11364  \n",
       "29    11354, 11355, 11356, 11357, 11358, 11359, 11360  \n",
       "30                                11365, 11366, 11367  \n",
       "31    11412, 11423, 11432, 11433, 11434, 11435, 11436  \n",
       "32           11101, 11102, 11103, 11104, 11105, 11106  \n",
       "33                         11374, 11375, 11379, 11385  \n",
       "34           11691, 11692, 11693, 11694, 11695, 11697  \n",
       "35  11004, 11005, 11411, 11413, 11422, 11426, 1142...  \n",
       "36  11414, 11415, 11416, 11417, 11418, 11419, 1142...  \n",
       "37    11368, 11369, 11370, 11372, 11373, 11377, 11378  \n",
       "38                                10302, 10303, 10310  \n",
       "39                  10306, 10307, 10308, 10309, 10312  \n",
       "40                                10301, 10304, 10305  \n",
       "41                                              10314  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get NYC neighborhoods from NYS Department of Health\n",
    "# https://www.health.ny.gov/statistics/cancer/registry/appendix/neighborhoods.htm\n",
    "df = pd.read_csv('../Data/neighborhoods.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NYC Neighborhoods (ETL): Transform"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reorganize dataframe by zip code\n",
    "nyc_zip_codes = []\n",
    "nyc_neighborhoods = []\n",
    "nyc_boroughs = []\n",
    "\n",
    "# create record for each zip code\n",
    "for row in range(df.shape[0]):\n",
    "    codes = df['ZIP Codes'][row].split(',')\n",
    "    for code in codes:\n",
    "        nyc_zip_codes.append(int(code))\n",
    "        nyc_neighborhoods.append(df['Neighborhood'][row])\n",
    "        nyc_boroughs.append(df['Borough'][row])\n",
    "\n",
    "# create dataframe indexed by zip code\n",
    "nyc = pd.DataFrame({'ZIP Code': nyc_zip_codes,\n",
    "                    'Neighborhood': nyc_neighborhoods,\n",
    "                    'Borough': nyc_boroughs})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>ZIP Code</th>\n",
       "      <th>Neighborhood</th>\n",
       "      <th>Borough</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10453</td>\n",
       "      <td>Central Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10457</td>\n",
       "      <td>Central Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10460</td>\n",
       "      <td>Central Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10458</td>\n",
       "      <td>Bronx Park and Fordham</td>\n",
       "      <td>Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10467</td>\n",
       "      <td>Bronx Park and Fordham</td>\n",
       "      <td>Bronx</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ZIP Code            Neighborhood Borough\n",
       "0     10453           Central Bronx   Bronx\n",
       "1     10457           Central Bronx   Bronx\n",
       "2     10460           Central Bronx   Bronx\n",
       "3     10458  Bronx Park and Fordham   Bronx\n",
       "4     10467  Bronx Park and Fordham   Bronx"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#review cleaned dataset\n",
    "nyc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NYC Neighborhoods (ETL): Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating SQL connection\n",
    "conn = sqlite3.connect('../Data/pet_care_industry.db')\n",
    "c = conn.cursor()\n",
    "\n",
    "#function to create table\n",
    "def create_table(query):\n",
    "    c.execute(query)\n",
    "\n",
    "#function to close connection\n",
    "def close_c_conn():\n",
    "    c.close()\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create neighborhoods table\n",
    "create_query = \"\"\"CREATE TABLE neighborhoods\n",
    "                (ZipCode INTEGER PRIMARY KEY,\n",
    "                 Neighborhood TEXT,\n",
    "                 Borough TEXT);\"\"\"\n",
    "\n",
    "c.execute('DROP TABLE IF EXISTS neighborhoods')\n",
    "create_table(create_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#function to insert neighborhoods into table\n",
    "def insert_neighborhoods(neighborhoods):\n",
    "    for i in range(len(neighborhoods.index)):\n",
    "        c.execute(\"\"\"INSERT INTO neighborhoods\n",
    "                  (ZipCode,\n",
    "                   Neighborhood,\n",
    "                   Borough)\n",
    "                   VALUES\n",
    "                   (?,?,?)\"\"\",\n",
    "                   (int(neighborhoods.iloc[i]['ZIP Code']),\n",
    "                    neighborhoods.iloc[i]['Neighborhood'],\n",
    "                    neighborhoods.iloc[i]['Borough']))\n",
    "        \n",
    "    conn.commit()\n",
    "    \n",
    "#insert neighborhoods into table\n",
    "insert_neighborhoods(nyc)"
   ]
  },
  {
   "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>ZipCode</th>\n",
       "      <th>Neighborhood</th>\n",
       "      <th>Borough</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001</td>\n",
       "      <td>Chelsea and Clinton</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10002</td>\n",
       "      <td>Lower East Side</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10003</td>\n",
       "      <td>Lower East Side</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10004</td>\n",
       "      <td>Lower Manhattan</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10005</td>\n",
       "      <td>Lower Manhattan</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10006</td>\n",
       "      <td>Lower Manhattan</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10007</td>\n",
       "      <td>Lower Manhattan</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10009</td>\n",
       "      <td>Lower East Side</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10010</td>\n",
       "      <td>Gramercy Park and Murray Hill</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10011</td>\n",
       "      <td>Chelsea and Clinton</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10012</td>\n",
       "      <td>Greenwich Village and Soho</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10013</td>\n",
       "      <td>Greenwich Village and Soho</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10014</td>\n",
       "      <td>Greenwich Village and Soho</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10016</td>\n",
       "      <td>Gramercy Park and Murray Hill</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10017</td>\n",
       "      <td>Gramercy Park and Murray Hill</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10018</td>\n",
       "      <td>Chelsea and Clinton</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10019</td>\n",
       "      <td>Chelsea and Clinton</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>10020</td>\n",
       "      <td>Chelsea and Clinton</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10021</td>\n",
       "      <td>Upper East Side</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10022</td>\n",
       "      <td>Gramercy Park and Murray Hill</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10023</td>\n",
       "      <td>Upper West Side</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10024</td>\n",
       "      <td>Upper West Side</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>10025</td>\n",
       "      <td>Upper West Side</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10026</td>\n",
       "      <td>Central Harlem</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10027</td>\n",
       "      <td>Central Harlem</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10028</td>\n",
       "      <td>Upper East Side</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>10029</td>\n",
       "      <td>East Harlem</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>10030</td>\n",
       "      <td>Central Harlem</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>10031</td>\n",
       "      <td>Inwood and Washington Heights</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10032</td>\n",
       "      <td>Inwood and Washington Heights</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>11379</td>\n",
       "      <td>West Central Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>11385</td>\n",
       "      <td>West Central Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>11411</td>\n",
       "      <td>Southeast Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>11412</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>11413</td>\n",
       "      <td>Southeast Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>11414</td>\n",
       "      <td>Southwest Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>11415</td>\n",
       "      <td>Southwest Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>11416</td>\n",
       "      <td>Southwest Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>11417</td>\n",
       "      <td>Southwest Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>11418</td>\n",
       "      <td>Southwest Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>11419</td>\n",
       "      <td>Southwest Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>11420</td>\n",
       "      <td>Southwest Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>11421</td>\n",
       "      <td>Southwest Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>11422</td>\n",
       "      <td>Southeast Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>11423</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>11426</td>\n",
       "      <td>Southeast Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>11427</td>\n",
       "      <td>Southeast Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>11428</td>\n",
       "      <td>Southeast Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>11429</td>\n",
       "      <td>Southeast Queens</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>11432</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>11433</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>11434</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>11435</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>11436</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>11691</td>\n",
       "      <td>Rockaways</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>11692</td>\n",
       "      <td>Rockaways</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>11693</td>\n",
       "      <td>Rockaways</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>11694</td>\n",
       "      <td>Rockaways</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>11695</td>\n",
       "      <td>Rockaways</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>11697</td>\n",
       "      <td>Rockaways</td>\n",
       "      <td>Queens</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>178 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ZipCode                   Neighborhood    Borough\n",
       "0      10001            Chelsea and Clinton  Manhattan\n",
       "1      10002                Lower East Side  Manhattan\n",
       "2      10003                Lower East Side  Manhattan\n",
       "3      10004                Lower Manhattan  Manhattan\n",
       "4      10005                Lower Manhattan  Manhattan\n",
       "5      10006                Lower Manhattan  Manhattan\n",
       "6      10007                Lower Manhattan  Manhattan\n",
       "7      10009                Lower East Side  Manhattan\n",
       "8      10010  Gramercy Park and Murray Hill  Manhattan\n",
       "9      10011            Chelsea and Clinton  Manhattan\n",
       "10     10012     Greenwich Village and Soho  Manhattan\n",
       "11     10013     Greenwich Village and Soho  Manhattan\n",
       "12     10014     Greenwich Village and Soho  Manhattan\n",
       "13     10016  Gramercy Park and Murray Hill  Manhattan\n",
       "14     10017  Gramercy Park and Murray Hill  Manhattan\n",
       "15     10018            Chelsea and Clinton  Manhattan\n",
       "16     10019            Chelsea and Clinton  Manhattan\n",
       "17     10020            Chelsea and Clinton  Manhattan\n",
       "18     10021                Upper East Side  Manhattan\n",
       "19     10022  Gramercy Park and Murray Hill  Manhattan\n",
       "20     10023                Upper West Side  Manhattan\n",
       "21     10024                Upper West Side  Manhattan\n",
       "22     10025                Upper West Side  Manhattan\n",
       "23     10026                 Central Harlem  Manhattan\n",
       "24     10027                 Central Harlem  Manhattan\n",
       "25     10028                Upper East Side  Manhattan\n",
       "26     10029                    East Harlem  Manhattan\n",
       "27     10030                 Central Harlem  Manhattan\n",
       "28     10031  Inwood and Washington Heights  Manhattan\n",
       "29     10032  Inwood and Washington Heights  Manhattan\n",
       "..       ...                            ...        ...\n",
       "148    11379            West Central Queens     Queens\n",
       "149    11385            West Central Queens     Queens\n",
       "150    11411               Southeast Queens     Queens\n",
       "151    11412                        Jamaica     Queens\n",
       "152    11413               Southeast Queens     Queens\n",
       "153    11414               Southwest Queens     Queens\n",
       "154    11415               Southwest Queens     Queens\n",
       "155    11416               Southwest Queens     Queens\n",
       "156    11417               Southwest Queens     Queens\n",
       "157    11418               Southwest Queens     Queens\n",
       "158    11419               Southwest Queens     Queens\n",
       "159    11420               Southwest Queens     Queens\n",
       "160    11421               Southwest Queens     Queens\n",
       "161    11422               Southeast Queens     Queens\n",
       "162    11423                        Jamaica     Queens\n",
       "163    11426               Southeast Queens     Queens\n",
       "164    11427               Southeast Queens     Queens\n",
       "165    11428               Southeast Queens     Queens\n",
       "166    11429               Southeast Queens     Queens\n",
       "167    11432                        Jamaica     Queens\n",
       "168    11433                        Jamaica     Queens\n",
       "169    11434                        Jamaica     Queens\n",
       "170    11435                        Jamaica     Queens\n",
       "171    11436                        Jamaica     Queens\n",
       "172    11691                      Rockaways     Queens\n",
       "173    11692                      Rockaways     Queens\n",
       "174    11693                      Rockaways     Queens\n",
       "175    11694                      Rockaways     Queens\n",
       "176    11695                      Rockaways     Queens\n",
       "177    11697                      Rockaways     Queens\n",
       "\n",
       "[178 rows x 3 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check SQL neighborhoods table\n",
    "neighborhoods = pd.read_sql_query(\"SELECT * FROM neighborhoods;\", conn)\n",
    "neighborhoods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#close connection\n",
    "close_c_conn()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}