DrunkenZipCodes.SQLAlchemy.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "from sqlalchemy import *\n",
    "from sqlalchemy.sql import select\n",
    "from sqlalchemy.orm import Session, sessionmaker, relationship\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy_utils import database_exists, create_database, drop_database\n",
    "from config import username, password"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "###EXTRACT\n",
    "\n",
    "# Read .csv data into DataFrames\n",
    "dfPop2000 = pd.DataFrame # Population by zipcode, 2000\n",
    "csvFile = 'Resources/population_by_zip_2000.csv'\n",
    "dfPop2000 = pd.read_csv(csvFile,delimiter=',',encoding='utf-8',low_memory=False,dtype=str)\n",
    "dfPop2010 = pd.DataFrame # Population by zipcode, 2010\n",
    "csvFile = 'Resources/population_by_zip_2010.csv'\n",
    "dfPop2010 = pd.read_csv(csvFile,delimiter=',',encoding='utf-8',low_memory=False,dtype=str)\n",
    "dfBars = pd.DataFrame # Bars dataset\n",
    "csvFile = 'Resources/8260_1.csv'\n",
    "dfBars = pd.read_csv(csvFile,delimiter=',',encoding='utf-8',low_memory=False,dtype=str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>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                     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": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "###TRANSFORM\n",
    "\n",
    "# Bars DataFrame - dirty data clean-up! (Need to be able to merge on 'zipcode') \n",
    "dfBars.dropna(subset=['postalCode'],inplace=True) # Drop rows with no zipcode listed\n",
    "dfBars['postalCode'] = dfBars['postalCode'].str.zfill(5) # Prepend '0' if zipcode less than 5 digits long\n",
    "dfBars['postalCode'] = dfBars['postalCode'].str.slice(start=0,stop=5) # Keep only first 5 digits of zipcode\n",
    "bars_columns = [u'postalCode',u'name'] # the 'u' before column name -should- make sure all data is utf-8 compliant\n",
    "bars_clean = dfBars[bars_columns].copy()\n",
    "dfBars=bars_clean.rename(columns={'postalCode':'zipcode'})\n",
    "dfBars.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>bar_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zipcode</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>04412</th>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93446</th>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94558</th>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95448</th>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43215</th>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         bar_count\n",
       "zipcode           \n",
       "04412           57\n",
       "93446           51\n",
       "94558           49\n",
       "95448           44\n",
       "43215           42"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create new DataFrame to hold count of bars in each zipcode\n",
    "ZipBars = dfBars.groupby(['zipcode']).count()\n",
    "ZipBars.sort_values('name',ascending=False,inplace=True)\n",
    "ZipBars.rename(columns={'name':'bar_count'},inplace=True)\n",
    "ZipBars.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>minimum_age</th>\n",
       "      <th>maximum_age</th>\n",
       "      <th>gender</th>\n",
       "      <th>population</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>geo_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>14</td>\n",
       "      <td>female</td>\n",
       "      <td>75</td>\n",
       "      <td>39769</td>\n",
       "      <td>8600000US39769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>85</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>130</td>\n",
       "      <td>44047</td>\n",
       "      <td>8600000US44047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30</td>\n",
       "      <td>34</td>\n",
       "      <td>female</td>\n",
       "      <td>11</td>\n",
       "      <td>42436</td>\n",
       "      <td>8600000US42436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22</td>\n",
       "      <td>24</td>\n",
       "      <td>male</td>\n",
       "      <td>22</td>\n",
       "      <td>99685</td>\n",
       "      <td>8600000US99685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>67</td>\n",
       "      <td>69</td>\n",
       "      <td>male</td>\n",
       "      <td>69</td>\n",
       "      <td>04239</td>\n",
       "      <td>8600000US04239</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  minimum_age maximum_age  gender population zipcode          geo_id\n",
       "0          10          14  female         75   39769  8600000US39769\n",
       "1          85         NaN  female        130   44047  8600000US44047\n",
       "2          30          34  female         11   42436  8600000US42436\n",
       "3          22          24    male         22   99685  8600000US99685\n",
       "4          67          69    male         69   04239  8600000US04239"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Population DataFrames - cleanup\n",
    "dfPop2000.dropna(subset=['zipcode'],inplace=True) # Drop rows with no zipcode listed\n",
    "dfPop2010.dropna(subset=['zipcode'],inplace=True) \n",
    "dfPop2000['zipcode'] = dfPop2000['zipcode'].str.zfill(5) # Prepend '0' if zip < 5 digits long\n",
    "dfPop2010['zipcode'] = dfPop2010['zipcode'].str.zfill(5)\n",
    "dfPop2000['zipcode'] = dfPop2000['zipcode'].str.slice(start=0,stop=5) # Keep only first 5 digits\n",
    "dfPop2010['zipcode'] = dfPop2010['zipcode'].str.slice(start=0,stop=5)\n",
    "dfPop2000.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert population data to int to enable aggregation\n",
    "dfPop2000['population'] = dfPop2010['population'].astype(int)\n",
    "dfPop2010['population'] = dfPop2010['population'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "zipcode\n",
       "00601     9157\n",
       "00602    12868\n",
       "00603    29139\n",
       "00604    18476\n",
       "00606    39821\n",
       "Name: population, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create DataFrames to hold total population by zipcode\n",
    "ZipPop2000 = dfPop2000.groupby(['zipcode'])['population'].sum()\n",
    "ZipPop2010 = dfPop2010.groupby(['zipcode'])['population'].sum()\n",
    "ZipPop2000.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "zipcode  gender\n",
       "00601    female     1469\n",
       "         male       7665\n",
       "00602    female     5000\n",
       "         male       7620\n",
       "00603    female    14015\n",
       "Name: population, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create DataFrames to show population grouped by zipcode and gender\n",
    "GenderPop2000 = dfPop2000.groupby(['zipcode','gender'])['population'].sum()\n",
    "GenderPop2010 = dfPop2010.groupby(['zipcode','gender'])['population'].sum()\n",
    "GenderPop2000.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate dictionaries from DataFrames\n",
    "bar_names = {'zipcode','name'}\n",
    "bars_by_zip = {'zipcode','bar_count'}\n",
    "pop_by_zip2000 = {'zipcode','population'}\n",
    "pop_by_zip2010 = {'zipcode','population'}\n",
    "gender2000 = {'zipcode','gender','population'}\n",
    "gender2010 = {'zipcode','gender','population'}\n",
    "pop_female2000 = {'zipcode':'population'}\n",
    "pop_male2000 = {'zipcode':'population'}\n",
    "pop_female2010 = {'zipcode':'population'}\n",
    "pop_male2010 = {'zipcode':'population'}\n",
    "\n",
    "bar_names = dfBars.to_dict()\n",
    "bars_by_zip = ZipBars.to_dict()\n",
    "pop_by_zip2000 = ZipPop2000.to_dict()\n",
    "pop_by_zip2010 = ZipPop2010.to_dict()\n",
    "gender2000 = GenderPop2000.to_dict()\n",
    "gender2010 = GenderPop2010.to_dict()\n",
    "for key, value in gender2000.items():\n",
    "    if key[1] == 'female': pop_female2000.update({key[0]:value})\n",
    "    if key[1] == 'male': pop_male2000.update({key[0]:value})\n",
    "for key, value in gender2010.items():\n",
    "    if key[1] == 'female': pop_female2010.update({key[0]:value})\n",
    "    if key[1] == 'male': pop_male2010.update({key[0]:value})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### LOAD\n",
    "\n",
    "# Create connection to Bars_db\n",
    "rds_connection_string = username + ':' + password + '@127.0.0.1/Bars_db' #username and password variables stored locally in config.py\n",
    "engine = create_engine(f'mysql://{rds_connection_string}',echo=False)\n",
    "if database_exists(engine.url): #check to see if Bars_db exists...\n",
    "    drop_database(engine.url) #if it does, delete it...\n",
    "if not database_exists(engine.url): #check to see if Bars_db exists...\n",
    "    create_database(engine.url) #if it doesn't, create it\n",
    "conn = engine.connect()\n",
    "Base = declarative_base()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine.table_names()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define classes to create table schemas\n",
    "\n",
    "class BarName(Base):\n",
    "    __tablename__ = 'bar_name'\n",
    "    id = Column(Integer, primary_key=True,nullable=False)\n",
    "    zipcode = Column(String(5))\n",
    "    name = Column(String(255))\n",
    "\n",
    "class BarCount(Base):\n",
    "    __tablename__ = 'bar_count'\n",
    "    id = Column(Integer, primary_key=True,nullable=False)\n",
    "    zipcode = Column(String(5))\n",
    "    bar_count = Column(Integer)\n",
    "\n",
    "class Population2000(Base):\n",
    "    __tablename__ = 'population_by_zipcode_2000'\n",
    "    id = Column(Integer, primary_key=True,nullable=False)\n",
    "    zipcode = Column(String(5))\n",
    "    population = Column(Integer)\n",
    "\n",
    "class Population2010(Base):\n",
    "    __tablename__ = 'population_by_zipcode_2010'\n",
    "    id = Column(Integer, primary_key=True,nullable=False)\n",
    "    zipcode = Column(String(5))\n",
    "    population = Column(Integer)\n",
    "\n",
    "class MalePopulation2000(Base):\n",
    "    __tablename__ = 'male_population_by_zipcode_2000'\n",
    "    id = Column(Integer, primary_key=True,nullable=False)\n",
    "    zipcode = Column(String(5))\n",
    "    population = Column(Integer)\n",
    "\n",
    "class MalePopulation2010(Base):\n",
    "    __tablename__ = 'male_population_by_zipcode_2010'\n",
    "    id = Column(Integer, primary_key=True,nullable=False)\n",
    "    zipcode = Column(String(5))\n",
    "    population = Column(Integer)\n",
    "    \n",
    "class FemalePopulation2000(Base):\n",
    "    __tablename__ = 'female_population_by_zipcode_2000'\n",
    "    id = Column(Integer, primary_key=True,nullable=False)\n",
    "    zipcode = Column(String(5))\n",
    "    population = Column(Integer)\n",
    "\n",
    "class FemalePopulation2010(Base):\n",
    "    __tablename__ = 'female_population_by_zipcode_2010'\n",
    "    id = Column(Integer, primary_key=True,nullable=False)\n",
    "    zipcode = Column(String(5))\n",
    "    population = Column(Integer)\n",
    "    \n",
    "Base.metadata.create_all(conn)\n",
    "tables = []\n",
    "tables = engine.table_names()\n",
    "tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get it to work ONCE\n",
    "\n",
    "session = Session(bind=engine)\n",
    "bar = BarName(zipcode='53212',name='The Waterfront Cafe')\n",
    "session.add(bar)\n",
    "session.commit()\n",
    "result = conn.execute('SELECT * FROM bar_name;')\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create series of loops to make it work a whole bunch of times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "file_extension": ".py",
  "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.6.6"
  },
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pygments_lexer": "ipython3",
  "version": 3
 },
 "nbformat": 4,
 "nbformat_minor": 2
}