DrunkenBars and ZipCodes ETL Project 2019.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import mysql.connector\n",
    "from sqlalchemy import create_engine\n",
    "import sqlalchemy\n",
    "from sqlalchemy import *\n",
    "from census import Census\n",
    "from MySQLconfig import username, password\n",
    "\n",
    "# Census API Key\n",
    "from config import api_key\n",
    "c = Census(api_key, year=2017)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pull various datasources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Dataset of bars\n",
    "#df = pd.read_csv('https://query.data.world/s/7c4vc4flpfmphqsmsjhbd55fsj3w32')\n",
    "df = pd.read_csv('barsinfo.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>address</th>\n",
       "      <th>categories</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>hours</th>\n",
       "      <th>keys</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>menus</th>\n",
       "      <th>name</th>\n",
       "      <th>postalCode</th>\n",
       "      <th>province</th>\n",
       "      <th>twitter</th>\n",
       "      <th>websites</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AVweN1T_ByjofQCxwSPL</td>\n",
       "      <td>Syr Airport</td>\n",
       "      <td>Brewery</td>\n",
       "      <td>Syracuse</td>\n",
       "      <td>US</td>\n",
       "      <td>NaN</td>\n",
       "      <td>us/ny/syracuse/syrairport/2098450797</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Adirondack Pub</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AVwdkF9ZIN2L1WUfxDfw</td>\n",
       "      <td>1320 N Doctor Martin Luther King Dr</td>\n",
       "      <td>Office and Cafeteria Brewer's Hill</td>\n",
       "      <td>Milwaukee</td>\n",
       "      <td>US</td>\n",
       "      <td>NaN</td>\n",
       "      <td>us/wi/milwaukee/1320ndoctormartinlutherkingdr/...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Waterfront Cafe</td>\n",
       "      <td>53212</td>\n",
       "      <td>WI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AVwdlPkf_7pvs4fz7TMr</td>\n",
       "      <td>608 4th St</td>\n",
       "      <td>Brewery Downtown Clovis</td>\n",
       "      <td>Clovis</td>\n",
       "      <td>US</td>\n",
       "      <td>[{\"day\":\"Wed - Sat\",\"hour\":\"5:00 PM - 8:00 PM\"}]</td>\n",
       "      <td>us/ca/clovis/6084thst/-932885010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>559 Local Brewing</td>\n",
       "      <td>93612</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://fresnobrewing.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AVwcucY0kufWRAb5yWbU</td>\n",
       "      <td>1103 Mills St</td>\n",
       "      <td>breweries &amp; brew pubs</td>\n",
       "      <td>Black Earth</td>\n",
       "      <td>US</td>\n",
       "      <td>NaN</td>\n",
       "      <td>us/wi/blackearth/1103millsst/-757413411</td>\n",
       "      <td>43.138995</td>\n",
       "      <td>-89.747904</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Shack The</td>\n",
       "      <td>53515-9454</td>\n",
       "      <td>WI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AVweCkd5kufWRAb5_eaI</td>\n",
       "      <td>130 W Riverside Dr</td>\n",
       "      <td>Brewery and Gastropub Downtown Jeffersonville</td>\n",
       "      <td>Jeffersonville</td>\n",
       "      <td>US</td>\n",
       "      <td>[{\"day\":\"Wed - Thu\",\"hour\":\"3:00 PM - 9:00 PM\"...</td>\n",
       "      <td>us/utica/jeffersonville/130wriversidedr/787280564</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Flat12 Bierworks</td>\n",
       "      <td>47130</td>\n",
       "      <td>Utica</td>\n",
       "      <td>flat12bierwerks</td>\n",
       "      <td>http://flat12.me/brewery</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id                              address  \\\n",
       "0  AVweN1T_ByjofQCxwSPL                          Syr Airport   \n",
       "1  AVwdkF9ZIN2L1WUfxDfw  1320 N Doctor Martin Luther King Dr   \n",
       "2  AVwdlPkf_7pvs4fz7TMr                           608 4th St   \n",
       "3  AVwcucY0kufWRAb5yWbU                        1103 Mills St   \n",
       "4  AVweCkd5kufWRAb5_eaI                   130 W Riverside Dr   \n",
       "\n",
       "                                      categories            city country  \\\n",
       "0                                        Brewery        Syracuse      US   \n",
       "1             Office and Cafeteria Brewer's Hill       Milwaukee      US   \n",
       "2                        Brewery Downtown Clovis          Clovis      US   \n",
       "3                          breweries & brew pubs     Black Earth      US   \n",
       "4  Brewery and Gastropub Downtown Jeffersonville  Jeffersonville      US   \n",
       "\n",
       "                                               hours  \\\n",
       "0                                                NaN   \n",
       "1                                                NaN   \n",
       "2   [{\"day\":\"Wed - Sat\",\"hour\":\"5:00 PM - 8:00 PM\"}]   \n",
       "3                                                NaN   \n",
       "4  [{\"day\":\"Wed - Thu\",\"hour\":\"3:00 PM - 9:00 PM\"...   \n",
       "\n",
       "                                                keys   latitude  longitude  \\\n",
       "0               us/ny/syracuse/syrairport/2098450797        NaN        NaN   \n",
       "1  us/wi/milwaukee/1320ndoctormartinlutherkingdr/...        NaN        NaN   \n",
       "2                   us/ca/clovis/6084thst/-932885010        NaN        NaN   \n",
       "3            us/wi/blackearth/1103millsst/-757413411  43.138995 -89.747904   \n",
       "4  us/utica/jeffersonville/130wriversidedr/787280564        NaN        NaN   \n",
       "\n",
       "  menus                 name  postalCode province          twitter  \\\n",
       "0   NaN       Adirondack Pub         NaN       NY              NaN   \n",
       "1   NaN  The Waterfront Cafe       53212       WI              NaN   \n",
       "2   NaN    559 Local Brewing       93612       CA              NaN   \n",
       "3   NaN            Shack The  53515-9454       WI              NaN   \n",
       "4   NaN     Flat12 Bierworks       47130    Utica  flat12bierwerks   \n",
       "\n",
       "                   websites  \n",
       "0                       NaN  \n",
       "1                       NaN  \n",
       "2  http://fresnobrewing.com  \n",
       "3                       NaN  \n",
       "4  http://flat12.me/brewery  "
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "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>bar_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Adirondack Pub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53212</td>\n",
       "      <td>The Waterfront Cafe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>93612</td>\n",
       "      <td>559 Local Brewing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>53515-9454</td>\n",
       "      <td>Shack The</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>47130</td>\n",
       "      <td>Flat12 Bierworks</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      zipcode             bar_name\n",
       "0         NaN       Adirondack Pub\n",
       "1       53212  The Waterfront Cafe\n",
       "2       93612    559 Local Brewing\n",
       "3  53515-9454            Shack The\n",
       "4       47130     Flat12 Bierworks"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Capture columns needed and rename\n",
    "bars_columns = ['postalCode','name'] #picks the column \n",
    "dfBars = df[bars_columns].copy()\n",
    "dfBars=dfBars.rename(columns={'postalCode':'zipcode', 'name':'bar_name'}) #rename columns\n",
    "\n",
    "dfBars.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "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>bar_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53212</td>\n",
       "      <td>The Waterfront Cafe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>93612</td>\n",
       "      <td>559 Local Brewing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>53515</td>\n",
       "      <td>Shack The</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>47130</td>\n",
       "      <td>Flat12 Bierworks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>98072</td>\n",
       "      <td>Redhook Ale Brewery Inc</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  zipcode                 bar_name\n",
       "1   53212      The Waterfront Cafe\n",
       "2   93612        559 Local Brewing\n",
       "3   53515                Shack The\n",
       "4   47130         Flat12 Bierworks\n",
       "5   98072  Redhook Ale Brewery Inc"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Clean data and reformat zipcode\n",
    "dfBars.dropna(subset=['zipcode'],inplace=True) # Drop rows with no zip code listed\n",
    "dfBars['zipcode'] = dfBars['zipcode'].str.zfill(5) # Prepend '0' if zip code less than 5 digits long\n",
    "dfBars['zipcode'] = dfBars['zipcode'].str.slice(start=0,stop=5) # Keep only first 5 digits of zip code\n",
    "dfBars.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "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>bar_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>04412</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5362</th>\n",
       "      <td>93446</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5479</th>\n",
       "      <td>94558</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5604</th>\n",
       "      <td>95448</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2659</th>\n",
       "      <td>43215</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     zipcode  bar_count\n",
       "263    04412         57\n",
       "5362   93446         51\n",
       "5479   94558         49\n",
       "5604   95448         44\n",
       "2659   43215         42"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Group bars by zipcode to get count\n",
    "groupedbars = dfBars.groupby(['zipcode'], as_index=False).count()\n",
    "groupedbars.sort_values('bar_name', ascending=False, inplace=True)\n",
    "groupedbars=groupedbars.rename(columns={'bar_name':'bar_count'}) #rename columns\n",
    "\n",
    "\n",
    "groupedbars.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "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</th>\n",
       "      <th>type</th>\n",
       "      <th>decommissioned</th>\n",
       "      <th>primary_city</th>\n",
       "      <th>acceptable_cities</th>\n",
       "      <th>unacceptable_cities</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>timezone</th>\n",
       "      <th>area_codes</th>\n",
       "      <th>world_region</th>\n",
       "      <th>country</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>irs_estimated_population_2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>501</td>\n",
       "      <td>UNIQUE</td>\n",
       "      <td>0</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I R S Service Center</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk County</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>631</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>40.81</td>\n",
       "      <td>-73.04</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>544</td>\n",
       "      <td>UNIQUE</td>\n",
       "      <td>0</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Irs Service Center</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk County</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>631</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>40.81</td>\n",
       "      <td>-73.04</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>601</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Colinas Del Gigante, Jard De Adjuntas, Urb San...</td>\n",
       "      <td>PR</td>\n",
       "      <td>Adjuntas Municipio</td>\n",
       "      <td>America/Puerto_Rico</td>\n",
       "      <td>787,939</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>18.16</td>\n",
       "      <td>-66.72</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>602</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alts De Aguada, Bo Guaniquilla, Comunidad Las ...</td>\n",
       "      <td>PR</td>\n",
       "      <td>Aguada Municipio</td>\n",
       "      <td>America/Puerto_Rico</td>\n",
       "      <td>787,939</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>18.38</td>\n",
       "      <td>-67.18</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>603</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0</td>\n",
       "      <td>Aguadilla</td>\n",
       "      <td>Ramey</td>\n",
       "      <td>Bda Caban, Bda Esteves, Bo Borinquen, Bo Ceiba...</td>\n",
       "      <td>PR</td>\n",
       "      <td>Aguadilla Municipio</td>\n",
       "      <td>America/Puerto_Rico</td>\n",
       "      <td>787</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>18.43</td>\n",
       "      <td>-67.15</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   zip      type  decommissioned primary_city acceptable_cities  \\\n",
       "0  501    UNIQUE               0   Holtsville               NaN   \n",
       "1  544    UNIQUE               0   Holtsville               NaN   \n",
       "2  601  STANDARD               0     Adjuntas               NaN   \n",
       "3  602  STANDARD               0       Aguada               NaN   \n",
       "4  603  STANDARD               0    Aguadilla             Ramey   \n",
       "\n",
       "                                 unacceptable_cities state  \\\n",
       "0                               I R S Service Center    NY   \n",
       "1                                 Irs Service Center    NY   \n",
       "2  Colinas Del Gigante, Jard De Adjuntas, Urb San...    PR   \n",
       "3  Alts De Aguada, Bo Guaniquilla, Comunidad Las ...    PR   \n",
       "4  Bda Caban, Bda Esteves, Bo Borinquen, Bo Ceiba...    PR   \n",
       "\n",
       "                county             timezone area_codes world_region country  \\\n",
       "0       Suffolk County     America/New_York        631          NaN      US   \n",
       "1       Suffolk County     America/New_York        631          NaN      US   \n",
       "2   Adjuntas Municipio  America/Puerto_Rico    787,939          NaN      US   \n",
       "3     Aguada Municipio  America/Puerto_Rico    787,939          NaN      US   \n",
       "4  Aguadilla Municipio  America/Puerto_Rico        787          NaN      US   \n",
       "\n",
       "   latitude  longitude  irs_estimated_population_2015  \n",
       "0     40.81     -73.04                            562  \n",
       "1     40.81     -73.04                              0  \n",
       "2     18.16     -66.72                              0  \n",
       "3     18.38     -67.18                              0  \n",
       "4     18.43     -67.15                              0  "
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Import zipcode information\n",
    "zipdf = pd.read_csv('zip_code_database.csv')\n",
    "zipdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "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</th>\n",
       "      <th>type</th>\n",
       "      <th>decommissioned</th>\n",
       "      <th>primary_city</th>\n",
       "      <th>acceptable_cities</th>\n",
       "      <th>unacceptable_cities</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>timezone</th>\n",
       "      <th>area_codes</th>\n",
       "      <th>world_region</th>\n",
       "      <th>country</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>irs_estimated_population_2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00501</td>\n",
       "      <td>UNIQUE</td>\n",
       "      <td>0</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I R S Service Center</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk County</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>631</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>40.81</td>\n",
       "      <td>-73.04</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00544</td>\n",
       "      <td>UNIQUE</td>\n",
       "      <td>0</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Irs Service Center</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk County</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>631</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>40.81</td>\n",
       "      <td>-73.04</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00601</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Colinas Del Gigante, Jard De Adjuntas, Urb San...</td>\n",
       "      <td>PR</td>\n",
       "      <td>Adjuntas Municipio</td>\n",
       "      <td>America/Puerto_Rico</td>\n",
       "      <td>787,939</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>18.16</td>\n",
       "      <td>-66.72</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00602</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alts De Aguada, Bo Guaniquilla, Comunidad Las ...</td>\n",
       "      <td>PR</td>\n",
       "      <td>Aguada Municipio</td>\n",
       "      <td>America/Puerto_Rico</td>\n",
       "      <td>787,939</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>18.38</td>\n",
       "      <td>-67.18</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00603</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0</td>\n",
       "      <td>Aguadilla</td>\n",
       "      <td>Ramey</td>\n",
       "      <td>Bda Caban, Bda Esteves, Bo Borinquen, Bo Ceiba...</td>\n",
       "      <td>PR</td>\n",
       "      <td>Aguadilla Municipio</td>\n",
       "      <td>America/Puerto_Rico</td>\n",
       "      <td>787</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>18.43</td>\n",
       "      <td>-67.15</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     zip      type  decommissioned primary_city acceptable_cities  \\\n",
       "0  00501    UNIQUE               0   Holtsville               NaN   \n",
       "1  00544    UNIQUE               0   Holtsville               NaN   \n",
       "2  00601  STANDARD               0     Adjuntas               NaN   \n",
       "3  00602  STANDARD               0       Aguada               NaN   \n",
       "4  00603  STANDARD               0    Aguadilla             Ramey   \n",
       "\n",
       "                                 unacceptable_cities state  \\\n",
       "0                               I R S Service Center    NY   \n",
       "1                                 Irs Service Center    NY   \n",
       "2  Colinas Del Gigante, Jard De Adjuntas, Urb San...    PR   \n",
       "3  Alts De Aguada, Bo Guaniquilla, Comunidad Las ...    PR   \n",
       "4  Bda Caban, Bda Esteves, Bo Borinquen, Bo Ceiba...    PR   \n",
       "\n",
       "                county             timezone area_codes world_region country  \\\n",
       "0       Suffolk County     America/New_York        631          NaN      US   \n",
       "1       Suffolk County     America/New_York        631          NaN      US   \n",
       "2   Adjuntas Municipio  America/Puerto_Rico    787,939          NaN      US   \n",
       "3     Aguada Municipio  America/Puerto_Rico    787,939          NaN      US   \n",
       "4  Aguadilla Municipio  America/Puerto_Rico        787          NaN      US   \n",
       "\n",
       "   latitude  longitude  irs_estimated_population_2015  \n",
       "0     40.81     -73.04                            562  \n",
       "1     40.81     -73.04                              0  \n",
       "2     18.16     -66.72                              0  \n",
       "3     18.38     -67.18                              0  \n",
       "4     18.43     -67.15                              0  "
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Reformat zip column\n",
    "zipdf['zip'].apply(str)\n",
    "zipdf['zip']=zipdf['zip'].apply(str)\n",
    "zipdf.dropna(subset=['zip'],inplace=True) # Drop rows with no zip code listed\n",
    "zipdf['zip'] = zipdf['zip'].str.zfill(5) # Prepend '0' if zip code less than 5 digits long\n",
    "\n",
    "zipdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "#zipdf.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "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>primary_city</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>irs_est_population_2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00501</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk County</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00544</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk County</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00601</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>PR</td>\n",
       "      <td>Adjuntas Municipio</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00602</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>PR</td>\n",
       "      <td>Aguada Municipio</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00603</td>\n",
       "      <td>Aguadilla</td>\n",
       "      <td>PR</td>\n",
       "      <td>Aguadilla Municipio</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  zipcode primary_city state               county  irs_est_population_2015\n",
       "0   00501   Holtsville    NY       Suffolk County                      562\n",
       "1   00544   Holtsville    NY       Suffolk County                        0\n",
       "2   00601     Adjuntas    PR   Adjuntas Municipio                        0\n",
       "3   00602       Aguada    PR     Aguada Municipio                        0\n",
       "4   00603    Aguadilla    PR  Aguadilla Municipio                        0"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Pick columns from zipdf\n",
    "zip_columns = ['zip','primary_city','state','county','irs_estimated_population_2015'] #picks the column \n",
    "zip_info = zipdf[zip_columns].copy()\n",
    "zip_info=zip_info.rename(columns={'zip':'zipcode','irs_estimated_population_2015':'irs_est_population_2015'}) #rename columns\n",
    "\n",
    "zip_info.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Census Data pull"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run Census Search to retrieve data on all zip codes (2017 ACS5 Census)\n",
    "# See: https://github.com/CommerceDataService/census-wrapper for library documentation\n",
    "# See: https://gist.github.com/afhaque/60558290d6efd892351c4b64e5c01e9b for labels\n",
    "census_data = c.acs5.get((\"NAME\", \"B19013_001E\", \"B01003_001E\",\"B23025_004E\",\"B01002_001E\",\n",
    "                          \"B19301_001E\"), {'for': 'zip code tabulation area:*'})\n",
    "\n",
    "# Convert to DataFrame\n",
    "census_pd = pd.DataFrame(census_data)\n",
    "\n",
    "# Column Reordering\n",
    "census_pd = census_pd.rename(columns={\"B01003_001E\": \"Population\",\n",
    "                                      \"B01002_001E\": \"Median Age\",\n",
    "                                      \"B19013_001E\": \"Household Income\",\n",
    "                                      \"B19301_001E\": \"Per Capita Income\",\n",
    "                                      \"NAME\":\"Name\", \"zip code tabulation area\": \"Zipcode\"})\n",
    "\n",
    "# Final DataFrame\n",
    "census2017 = census_pd[[\"Zipcode\", \"Population\", \"Median Age\", \"Household Income\",\n",
    "                       \"Per Capita Income\"]]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "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>Population</th>\n",
       "      <th>MedianAge</th>\n",
       "      <th>HouseholdIncome</th>\n",
       "      <th>PerCapitaIncome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00601</td>\n",
       "      <td>17599.0</td>\n",
       "      <td>38.9</td>\n",
       "      <td>11757.0</td>\n",
       "      <td>7041.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00602</td>\n",
       "      <td>39209.0</td>\n",
       "      <td>40.9</td>\n",
       "      <td>16190.0</td>\n",
       "      <td>8978.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00603</td>\n",
       "      <td>50135.0</td>\n",
       "      <td>40.4</td>\n",
       "      <td>16645.0</td>\n",
       "      <td>10897.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00606</td>\n",
       "      <td>6304.0</td>\n",
       "      <td>42.8</td>\n",
       "      <td>13387.0</td>\n",
       "      <td>5960.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00610</td>\n",
       "      <td>27590.0</td>\n",
       "      <td>41.4</td>\n",
       "      <td>18741.0</td>\n",
       "      <td>9266.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  zipcode  Population  MedianAge  HouseholdIncome  PerCapitaIncome\n",
       "0   00601     17599.0       38.9          11757.0           7041.0\n",
       "1   00602     39209.0       40.9          16190.0           8978.0\n",
       "2   00603     50135.0       40.4          16645.0          10897.0\n",
       "3   00606      6304.0       42.8          13387.0           5960.0\n",
       "4   00610     27590.0       41.4          18741.0           9266.0"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Display census data and rename columns\n",
    "census2017=census2017.rename(columns={'Zipcode':'zipcode','Median Age':'MedianAge','Household Income':'HouseholdIncome','Per Capita Income':'PerCapitaIncome'}) #rename columns\n",
    "census2017.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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>Population</th>\n",
       "      <th>MedianAge</th>\n",
       "      <th>HouseholdIncome</th>\n",
       "      <th>PerCapitaIncome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00601</td>\n",
       "      <td>17599.0</td>\n",
       "      <td>38.9</td>\n",
       "      <td>11757.0</td>\n",
       "      <td>7041.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00602</td>\n",
       "      <td>39209.0</td>\n",
       "      <td>40.9</td>\n",
       "      <td>16190.0</td>\n",
       "      <td>8978.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00603</td>\n",
       "      <td>50135.0</td>\n",
       "      <td>40.4</td>\n",
       "      <td>16645.0</td>\n",
       "      <td>10897.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00606</td>\n",
       "      <td>6304.0</td>\n",
       "      <td>42.8</td>\n",
       "      <td>13387.0</td>\n",
       "      <td>5960.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00610</td>\n",
       "      <td>27590.0</td>\n",
       "      <td>41.4</td>\n",
       "      <td>18741.0</td>\n",
       "      <td>9266.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>00612</td>\n",
       "      <td>62566.0</td>\n",
       "      <td>41.4</td>\n",
       "      <td>17744.0</td>\n",
       "      <td>10283.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>00616</td>\n",
       "      <td>10687.0</td>\n",
       "      <td>43.7</td>\n",
       "      <td>14918.0</td>\n",
       "      <td>9002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>00617</td>\n",
       "      <td>24508.0</td>\n",
       "      <td>38.7</td>\n",
       "      <td>17157.0</td>\n",
       "      <td>8762.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>00622</td>\n",
       "      <td>7405.0</td>\n",
       "      <td>48.4</td>\n",
       "      <td>16727.0</td>\n",
       "      <td>11935.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>00623</td>\n",
       "      <td>42321.0</td>\n",
       "      <td>41.3</td>\n",
       "      <td>16401.0</td>\n",
       "      <td>9101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>00624</td>\n",
       "      <td>23238.0</td>\n",
       "      <td>37.2</td>\n",
       "      <td>16832.0</td>\n",
       "      <td>8072.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>00627</td>\n",
       "      <td>32936.0</td>\n",
       "      <td>40.1</td>\n",
       "      <td>17579.0</td>\n",
       "      <td>9738.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>00631</td>\n",
       "      <td>1555.0</td>\n",
       "      <td>48.2</td>\n",
       "      <td>9632.0</td>\n",
       "      <td>7690.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>00637</td>\n",
       "      <td>23610.0</td>\n",
       "      <td>40.9</td>\n",
       "      <td>15736.0</td>\n",
       "      <td>9158.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>00638</td>\n",
       "      <td>17202.0</td>\n",
       "      <td>40.3</td>\n",
       "      <td>14448.0</td>\n",
       "      <td>8108.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>00641</td>\n",
       "      <td>28332.0</td>\n",
       "      <td>41.9</td>\n",
       "      <td>16292.0</td>\n",
       "      <td>8305.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>00646</td>\n",
       "      <td>38008.0</td>\n",
       "      <td>39.4</td>\n",
       "      <td>29160.0</td>\n",
       "      <td>15397.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>00647</td>\n",
       "      <td>5610.0</td>\n",
       "      <td>47.1</td>\n",
       "      <td>13071.0</td>\n",
       "      <td>7058.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>00650</td>\n",
       "      <td>14664.0</td>\n",
       "      <td>38.6</td>\n",
       "      <td>16095.0</td>\n",
       "      <td>7698.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>00652</td>\n",
       "      <td>3681.0</td>\n",
       "      <td>38.8</td>\n",
       "      <td>20564.0</td>\n",
       "      <td>11439.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>00653</td>\n",
       "      <td>11717.0</td>\n",
       "      <td>38.7</td>\n",
       "      <td>12236.0</td>\n",
       "      <td>7279.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>00656</td>\n",
       "      <td>19546.0</td>\n",
       "      <td>40.3</td>\n",
       "      <td>14966.0</td>\n",
       "      <td>8327.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>00659</td>\n",
       "      <td>40978.0</td>\n",
       "      <td>40.7</td>\n",
       "      <td>17770.0</td>\n",
       "      <td>10360.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>00660</td>\n",
       "      <td>16474.0</td>\n",
       "      <td>45.1</td>\n",
       "      <td>19674.0</td>\n",
       "      <td>11588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>00662</td>\n",
       "      <td>41341.0</td>\n",
       "      <td>40.6</td>\n",
       "      <td>16771.0</td>\n",
       "      <td>8883.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>00664</td>\n",
       "      <td>15396.0</td>\n",
       "      <td>36.3</td>\n",
       "      <td>15401.0</td>\n",
       "      <td>7134.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>00667</td>\n",
       "      <td>24089.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>13843.0</td>\n",
       "      <td>7256.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>00669</td>\n",
       "      <td>26510.0</td>\n",
       "      <td>40.9</td>\n",
       "      <td>12996.0</td>\n",
       "      <td>8236.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>00670</td>\n",
       "      <td>8797.0</td>\n",
       "      <td>40.1</td>\n",
       "      <td>14456.0</td>\n",
       "      <td>7293.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>00674</td>\n",
       "      <td>40826.0</td>\n",
       "      <td>41.1</td>\n",
       "      <td>18032.0</td>\n",
       "      <td>10520.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33088</th>\n",
       "      <td>99784</td>\n",
       "      <td>173.0</td>\n",
       "      <td>30.3</td>\n",
       "      <td>38125.0</td>\n",
       "      <td>18612.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33089</th>\n",
       "      <td>99785</td>\n",
       "      <td>421.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>33750.0</td>\n",
       "      <td>8425.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33090</th>\n",
       "      <td>99786</td>\n",
       "      <td>299.0</td>\n",
       "      <td>26.9</td>\n",
       "      <td>44500.0</td>\n",
       "      <td>15937.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33091</th>\n",
       "      <td>99788</td>\n",
       "      <td>119.0</td>\n",
       "      <td>34.4</td>\n",
       "      <td>28750.0</td>\n",
       "      <td>19081.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33092</th>\n",
       "      <td>99789</td>\n",
       "      <td>395.0</td>\n",
       "      <td>25.4</td>\n",
       "      <td>82813.0</td>\n",
       "      <td>22481.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33094</th>\n",
       "      <td>99791</td>\n",
       "      <td>172.0</td>\n",
       "      <td>24.5</td>\n",
       "      <td>61250.0</td>\n",
       "      <td>20597.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33095</th>\n",
       "      <td>99801</td>\n",
       "      <td>30179.0</td>\n",
       "      <td>38.2</td>\n",
       "      <td>91743.0</td>\n",
       "      <td>41968.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33096</th>\n",
       "      <td>99820</td>\n",
       "      <td>540.0</td>\n",
       "      <td>42.7</td>\n",
       "      <td>36000.0</td>\n",
       "      <td>34954.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33097</th>\n",
       "      <td>99824</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>35.5</td>\n",
       "      <td>75577.0</td>\n",
       "      <td>41057.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33098</th>\n",
       "      <td>99825</td>\n",
       "      <td>89.0</td>\n",
       "      <td>58.5</td>\n",
       "      <td>88500.0</td>\n",
       "      <td>50983.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33099</th>\n",
       "      <td>99826</td>\n",
       "      <td>386.0</td>\n",
       "      <td>53.3</td>\n",
       "      <td>61875.0</td>\n",
       "      <td>39582.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33100</th>\n",
       "      <td>99827</td>\n",
       "      <td>2599.0</td>\n",
       "      <td>45.5</td>\n",
       "      <td>70091.0</td>\n",
       "      <td>35619.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33101</th>\n",
       "      <td>99829</td>\n",
       "      <td>828.0</td>\n",
       "      <td>46.1</td>\n",
       "      <td>60625.0</td>\n",
       "      <td>28857.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33102</th>\n",
       "      <td>99830</td>\n",
       "      <td>635.0</td>\n",
       "      <td>37.2</td>\n",
       "      <td>51250.0</td>\n",
       "      <td>18955.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33103</th>\n",
       "      <td>99832</td>\n",
       "      <td>94.0</td>\n",
       "      <td>55.5</td>\n",
       "      <td>67917.0</td>\n",
       "      <td>33720.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33104</th>\n",
       "      <td>99833</td>\n",
       "      <td>3275.0</td>\n",
       "      <td>41.7</td>\n",
       "      <td>63490.0</td>\n",
       "      <td>35044.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33105</th>\n",
       "      <td>99835</td>\n",
       "      <td>8810.0</td>\n",
       "      <td>38.9</td>\n",
       "      <td>70765.0</td>\n",
       "      <td>36617.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33106</th>\n",
       "      <td>99836</td>\n",
       "      <td>60.0</td>\n",
       "      <td>40.7</td>\n",
       "      <td>66875.0</td>\n",
       "      <td>30355.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33107</th>\n",
       "      <td>99840</td>\n",
       "      <td>1038.0</td>\n",
       "      <td>42.9</td>\n",
       "      <td>70673.0</td>\n",
       "      <td>39376.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33108</th>\n",
       "      <td>99841</td>\n",
       "      <td>147.0</td>\n",
       "      <td>61.3</td>\n",
       "      <td>59688.0</td>\n",
       "      <td>34670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33109</th>\n",
       "      <td>99901</td>\n",
       "      <td>13768.0</td>\n",
       "      <td>39.5</td>\n",
       "      <td>67321.0</td>\n",
       "      <td>33755.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33111</th>\n",
       "      <td>99918</td>\n",
       "      <td>174.0</td>\n",
       "      <td>57.8</td>\n",
       "      <td>63125.0</td>\n",
       "      <td>34149.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33112</th>\n",
       "      <td>99919</td>\n",
       "      <td>660.0</td>\n",
       "      <td>47.8</td>\n",
       "      <td>50956.0</td>\n",
       "      <td>31413.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33113</th>\n",
       "      <td>99921</td>\n",
       "      <td>1971.0</td>\n",
       "      <td>41.1</td>\n",
       "      <td>58047.0</td>\n",
       "      <td>33002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33114</th>\n",
       "      <td>99922</td>\n",
       "      <td>306.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>31250.0</td>\n",
       "      <td>16408.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33115</th>\n",
       "      <td>99923</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-666666666.0</td>\n",
       "      <td>-666666666.0</td>\n",
       "      <td>-666666666.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33116</th>\n",
       "      <td>99925</td>\n",
       "      <td>901.0</td>\n",
       "      <td>45.1</td>\n",
       "      <td>48646.0</td>\n",
       "      <td>24783.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33117</th>\n",
       "      <td>99926</td>\n",
       "      <td>1684.0</td>\n",
       "      <td>32.8</td>\n",
       "      <td>57969.0</td>\n",
       "      <td>22927.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33118</th>\n",
       "      <td>99927</td>\n",
       "      <td>59.0</td>\n",
       "      <td>49.8</td>\n",
       "      <td>17981.0</td>\n",
       "      <td>11959.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33119</th>\n",
       "      <td>99929</td>\n",
       "      <td>2455.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>55742.0</td>\n",
       "      <td>29861.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32795 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      zipcode  Population    MedianAge  HouseholdIncome  PerCapitaIncome\n",
       "0       00601     17599.0         38.9          11757.0           7041.0\n",
       "1       00602     39209.0         40.9          16190.0           8978.0\n",
       "2       00603     50135.0         40.4          16645.0          10897.0\n",
       "3       00606      6304.0         42.8          13387.0           5960.0\n",
       "4       00610     27590.0         41.4          18741.0           9266.0\n",
       "5       00612     62566.0         41.4          17744.0          10283.0\n",
       "6       00616     10687.0         43.7          14918.0           9002.0\n",
       "7       00617     24508.0         38.7          17157.0           8762.0\n",
       "8       00622      7405.0         48.4          16727.0          11935.0\n",
       "9       00623     42321.0         41.3          16401.0           9101.0\n",
       "10      00624     23238.0         37.2          16832.0           8072.0\n",
       "11      00627     32936.0         40.1          17579.0           9738.0\n",
       "12      00631      1555.0         48.2           9632.0           7690.0\n",
       "13      00637     23610.0         40.9          15736.0           9158.0\n",
       "14      00638     17202.0         40.3          14448.0           8108.0\n",
       "15      00641     28332.0         41.9          16292.0           8305.0\n",
       "16      00646     38008.0         39.4          29160.0          15397.0\n",
       "17      00647      5610.0         47.1          13071.0           7058.0\n",
       "18      00650     14664.0         38.6          16095.0           7698.0\n",
       "19      00652      3681.0         38.8          20564.0          11439.0\n",
       "20      00653     11717.0         38.7          12236.0           7279.0\n",
       "21      00656     19546.0         40.3          14966.0           8327.0\n",
       "22      00659     40978.0         40.7          17770.0          10360.0\n",
       "23      00660     16474.0         45.1          19674.0          11588.0\n",
       "24      00662     41341.0         40.6          16771.0           8883.0\n",
       "25      00664     15396.0         36.3          15401.0           7134.0\n",
       "26      00667     24089.0         43.6          13843.0           7256.0\n",
       "27      00669     26510.0         40.9          12996.0           8236.0\n",
       "28      00670      8797.0         40.1          14456.0           7293.0\n",
       "29      00674     40826.0         41.1          18032.0          10520.0\n",
       "...       ...         ...          ...              ...              ...\n",
       "33088   99784       173.0         30.3          38125.0          18612.0\n",
       "33089   99785       421.0         22.0          33750.0           8425.0\n",
       "33090   99786       299.0         26.9          44500.0          15937.0\n",
       "33091   99788       119.0         34.4          28750.0          19081.0\n",
       "33092   99789       395.0         25.4          82813.0          22481.0\n",
       "33094   99791       172.0         24.5          61250.0          20597.0\n",
       "33095   99801     30179.0         38.2          91743.0          41968.0\n",
       "33096   99820       540.0         42.7          36000.0          34954.0\n",
       "33097   99824      2255.0         35.5          75577.0          41057.0\n",
       "33098   99825        89.0         58.5          88500.0          50983.0\n",
       "33099   99826       386.0         53.3          61875.0          39582.0\n",
       "33100   99827      2599.0         45.5          70091.0          35619.0\n",
       "33101   99829       828.0         46.1          60625.0          28857.0\n",
       "33102   99830       635.0         37.2          51250.0          18955.0\n",
       "33103   99832        94.0         55.5          67917.0          33720.0\n",
       "33104   99833      3275.0         41.7          63490.0          35044.0\n",
       "33105   99835      8810.0         38.9          70765.0          36617.0\n",
       "33106   99836        60.0         40.7          66875.0          30355.0\n",
       "33107   99840      1038.0         42.9          70673.0          39376.0\n",
       "33108   99841       147.0         61.3          59688.0          34670.0\n",
       "33109   99901     13768.0         39.5          67321.0          33755.0\n",
       "33111   99918       174.0         57.8          63125.0          34149.0\n",
       "33112   99919       660.0         47.8          50956.0          31413.0\n",
       "33113   99921      1971.0         41.1          58047.0          33002.0\n",
       "33114   99922       306.0         45.0          31250.0          16408.0\n",
       "33115   99923         0.0 -666666666.0     -666666666.0     -666666666.0\n",
       "33116   99925       901.0         45.1          48646.0          24783.0\n",
       "33117   99926      1684.0         32.8          57969.0          22927.0\n",
       "33118   99927        59.0         49.8          17981.0          11959.0\n",
       "33119   99929      2455.0         49.0          55742.0          29861.0\n",
       "\n",
       "[32795 rows x 5 columns]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Remove Nans\n",
    "census2017.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove bad values\n",
    "census2017_1=census2017[census2017.MedianAge !=-666666666.0]\n",
    "#census2017[census2017.HouseholdIncome !=-666666666.0]\n",
    "#census2017[census2017.PerCapitaIncome !=-666666666.0]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove bad values\n",
    "census2017_2=census2017_1[census2017_1.HouseholdIncome !=-666666666.0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove bad values\n",
    "census2017_cleaned=census2017_2[census2017_2.PerCapitaIncome !=-666666666.0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Export cleaned file for reference\n",
    "#census2017_cleaned.to_csv(\"census_data_2017_cleaned.csv\", encoding=\"utf-8\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Merge bars and census. \n",
    "bars_census = pd.merge(census2017_cleaned, groupedbars, on='zipcode', how='left')\n",
    "\n",
    "#Clean and perform calculations\n",
    "bars_census.dropna(subset=['bar_count'],inplace=True) # Drop rows with no bars\n",
    "bars_census['Bars_per_Capita']=bars_census['bar_count']/bars_census['Population']\n",
    "bars_census['Bars_per_Income']=bars_census['bar_count']/bars_census['HouseholdIncome']\n",
    "#bars_census.info()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Export cleaned file for reference\n",
    "#bars_cleaned.to_csv(\"bars_cleaned.csv\", encoding=\"utf-8\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove bad values\n",
    "#bars_clean=bars_census[bars_census.MedianAge !=-666666666.0]\n",
    "#bars_census[bars_census.HouseholdIncome !=-666666666.0]\n",
    "#bars_census[bars_census.PerCapitaIncome !=-666666666.0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove bad values\n",
    "#bars_clean2=bars_clean[bars_clean.HouseholdIncome !=-666666666.0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove bad values\n",
    "#bars_cleaned=bars_clean2[bars_clean2.PerCapitaIncome !=-666666666.0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "#bars_cleaned.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MySQL - loading information to MySQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "#MySQL Connection\n",
    "\n",
    "rds_connection_string = username + \":\" + password +\"@127.0.0.1/bars_db\"\n",
    "\n",
    "engine = create_engine(f'mysql://{rds_connection_string}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['bars_count', 'bars_values', 'census', 'zip_info']"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Show available tables\n",
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x26052c61b00>"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Clear existing data in tables before loading information\n",
    "engine.execute(\"truncate table bars_count\")\n",
    "engine.execute(\"truncate table bars_values\")\n",
    "engine.execute(\"truncate table census\")\n",
    "engine.execute(\"truncate table zip_info\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Loads Count of Bars by zip code\n",
    "\n",
    "groupedbars.to_sql(name='bars_count', con=engine, if_exists='append', index=False)\n",
    "#pd.read_sql_query('select * from bars_count', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Loads Bars data with census\n",
    "bars_census.to_sql(name='bars_values', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Loads Census data\n",
    "census2017_cleaned.to_sql(name='census', con=engine, if_exists='append', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Loads Zip Info\n",
    "zip_info.to_sql(name='zip_info', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "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
}