03_load/03_load.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# SQL Alchemy\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Import password authentication for the postgresql database\n",
    "from config import password"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to local database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create Engine and connection\n",
    "engine = create_engine(f\"postgresql://postgres:{password}@localhost:5432/fast_food_census_db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['zip', 'zip_zcta', 'census', 'restaurant', 'restaurant_address']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for tables\n",
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use pandas to load csv-converted dataframes into database"
   ]
  },
  {
   "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>zcta</th>\n",
       "      <th>population</th>\n",
       "      <th>median_age</th>\n",
       "      <th>median_household_income</th>\n",
       "      <th>per_capita_income</th>\n",
       "      <th>poverty_count</th>\n",
       "      <th>unemployment_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00601</td>\n",
       "      <td>17242.0</td>\n",
       "      <td>40.5</td>\n",
       "      <td>13092.0</td>\n",
       "      <td>6999.0</td>\n",
       "      <td>10772.0</td>\n",
       "      <td>2316.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00602</td>\n",
       "      <td>38442.0</td>\n",
       "      <td>42.3</td>\n",
       "      <td>16358.0</td>\n",
       "      <td>9277.0</td>\n",
       "      <td>19611.0</td>\n",
       "      <td>1927.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00603</td>\n",
       "      <td>48814.0</td>\n",
       "      <td>41.1</td>\n",
       "      <td>16603.0</td>\n",
       "      <td>11307.0</td>\n",
       "      <td>24337.0</td>\n",
       "      <td>3124.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00606</td>\n",
       "      <td>6437.0</td>\n",
       "      <td>43.3</td>\n",
       "      <td>12832.0</td>\n",
       "      <td>5943.0</td>\n",
       "      <td>4163.0</td>\n",
       "      <td>230.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00610</td>\n",
       "      <td>27073.0</td>\n",
       "      <td>42.1</td>\n",
       "      <td>19309.0</td>\n",
       "      <td>10220.0</td>\n",
       "      <td>11724.0</td>\n",
       "      <td>1290.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    zcta  population  median_age  median_household_income  per_capita_income  \\\n",
       "0  00601     17242.0        40.5                  13092.0             6999.0   \n",
       "1  00602     38442.0        42.3                  16358.0             9277.0   \n",
       "2  00603     48814.0        41.1                  16603.0            11307.0   \n",
       "3  00606      6437.0        43.3                  12832.0             5943.0   \n",
       "4  00610     27073.0        42.1                  19309.0            10220.0   \n",
       "\n",
       "   poverty_count  unemployment_count  \n",
       "0        10772.0              2316.0  \n",
       "1        19611.0              1927.0  \n",
       "2        24337.0              3124.0  \n",
       "3         4163.0               230.0  \n",
       "4        11724.0              1290.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_df = pd.read_csv(\"../02_transform_census/clean_census.csv\", dtype={'zcta': 'str'})\n",
    "census_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_df.to_sql(name='census', con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>city</th>\n",
       "      <th>state</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00544</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00601</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00602</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00603</td>\n",
       "      <td>Aguadilla</td>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  zip_code        city state\n",
       "0    00501  Holtsville    NY\n",
       "1    00544  Holtsville    NY\n",
       "2    00601    Adjuntas    PR\n",
       "3    00602      Aguada    PR\n",
       "4    00603   Aguadilla    PR"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_code_df = pd.read_csv(\"../02_transform_restaurant/final_zip_code.csv\", dtype='str')\n",
    "zip_code_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_code_df.to_sql(name='zip', con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "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>zip_code</th>\n",
       "      <th>zcta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00501</td>\n",
       "      <td>11742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00544</td>\n",
       "      <td>11742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00601</td>\n",
       "      <td>00601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00602</td>\n",
       "      <td>00602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00603</td>\n",
       "      <td>00603</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  zip_code   zcta\n",
       "0    00501  11742\n",
       "1    00544  11742\n",
       "2    00601  00601\n",
       "3    00602  00602\n",
       "4    00603  00603"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zcta_df = pd.read_csv(\"../02_transform_zip_zcta/zcta.csv\", dtype='str')\n",
    "zcta_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "zcta_df.to_sql(name=\"zip_zcta\", con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>restaurant_id</th>\n",
       "      <th>restaurant_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>SONIC Drive In</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Taco Bell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Arby's</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Steak 'n Shake</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Wendy's</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   restaurant_id restaurant_name\n",
       "0              1  SONIC Drive In\n",
       "1              2       Taco Bell\n",
       "2              3          Arby's\n",
       "3              4  Steak 'n Shake\n",
       "4              5         Wendy's"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurant_df = pd.read_csv(\"../02_transform_restaurant/restaurant_id.csv\")\n",
    "restaurant_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "restaurant_df.to_sql(name='restaurant', con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>restaurant_id</th>\n",
       "      <th>street_no</th>\n",
       "      <th>street_name</th>\n",
       "      <th>zip_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>N Canal Blvd</td>\n",
       "      <td>70301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>John R Rd</td>\n",
       "      <td>48083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>909</td>\n",
       "      <td>N Wood</td>\n",
       "      <td>75644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>97</td>\n",
       "      <td>Gateway Blvd</td>\n",
       "      <td>82901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>6557</td>\n",
       "      <td>S Staples St</td>\n",
       "      <td>78413</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   restaurant_id street_no   street_name zip_code\n",
       "0              1       800  N Canal Blvd    70301\n",
       "1              1       124     John R Rd    48083\n",
       "2              1       909        N Wood    75644\n",
       "3              1        97  Gateway Blvd    82901\n",
       "4              1      6557  S Staples St    78413"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurant_address_df = pd.read_csv(\"../02_transform_restaurant/restaurant_address.csv\", dtype={'zip_code': 'str'})\n",
    "restaurant_address_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "restaurant_address_df.to_sql(name='restaurant_address', con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Confirm data has been added by querying the tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>zcta</th>\n",
       "      <th>population</th>\n",
       "      <th>median_age</th>\n",
       "      <th>median_household_income</th>\n",
       "      <th>per_capita_income</th>\n",
       "      <th>poverty_count</th>\n",
       "      <th>unemployment_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00601</td>\n",
       "      <td>17242.0</td>\n",
       "      <td>40.5</td>\n",
       "      <td>13092.0</td>\n",
       "      <td>6999.0</td>\n",
       "      <td>10772.0</td>\n",
       "      <td>2316.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00602</td>\n",
       "      <td>38442.0</td>\n",
       "      <td>42.3</td>\n",
       "      <td>16358.0</td>\n",
       "      <td>9277.0</td>\n",
       "      <td>19611.0</td>\n",
       "      <td>1927.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00603</td>\n",
       "      <td>48814.0</td>\n",
       "      <td>41.1</td>\n",
       "      <td>16603.0</td>\n",
       "      <td>11307.0</td>\n",
       "      <td>24337.0</td>\n",
       "      <td>3124.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00606</td>\n",
       "      <td>6437.0</td>\n",
       "      <td>43.3</td>\n",
       "      <td>12832.0</td>\n",
       "      <td>5943.0</td>\n",
       "      <td>4163.0</td>\n",
       "      <td>230.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00610</td>\n",
       "      <td>27073.0</td>\n",
       "      <td>42.1</td>\n",
       "      <td>19309.0</td>\n",
       "      <td>10220.0</td>\n",
       "      <td>11724.0</td>\n",
       "      <td>1290.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    zcta  population  median_age  median_household_income  per_capita_income  \\\n",
       "0  00601     17242.0        40.5                  13092.0             6999.0   \n",
       "1  00602     38442.0        42.3                  16358.0             9277.0   \n",
       "2  00603     48814.0        41.1                  16603.0            11307.0   \n",
       "3  00606      6437.0        43.3                  12832.0             5943.0   \n",
       "4  00610     27073.0        42.1                  19309.0            10220.0   \n",
       "\n",
       "   poverty_count  unemployment_count  \n",
       "0        10772.0              2316.0  \n",
       "1        19611.0              1927.0  \n",
       "2        24337.0              3124.0  \n",
       "3         4163.0               230.0  \n",
       "4        11724.0              1290.0  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM census', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>zcta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00501</td>\n",
       "      <td>11742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00544</td>\n",
       "      <td>11742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00601</td>\n",
       "      <td>00601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00602</td>\n",
       "      <td>00602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00603</td>\n",
       "      <td>00603</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  zip_code   zcta\n",
       "0    00501  11742\n",
       "1    00544  11742\n",
       "2    00601  00601\n",
       "3    00602  00602\n",
       "4    00603  00603"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM zip_zcta', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>city</th>\n",
       "      <th>state</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00544</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00601</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00602</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00603</td>\n",
       "      <td>Aguadilla</td>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  zip_code        city state\n",
       "0    00501  Holtsville    NY\n",
       "1    00544  Holtsville    NY\n",
       "2    00601    Adjuntas    PR\n",
       "3    00602      Aguada    PR\n",
       "4    00603   Aguadilla    PR"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM zip', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>restaurant_address_id</th>\n",
       "      <th>restaurant_id</th>\n",
       "      <th>street_no</th>\n",
       "      <th>street_name</th>\n",
       "      <th>zip_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>N Canal Blvd</td>\n",
       "      <td>70301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>124</td>\n",
       "      <td>John R Rd</td>\n",
       "      <td>48083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>909</td>\n",
       "      <td>N Wood</td>\n",
       "      <td>75644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>97</td>\n",
       "      <td>Gateway Blvd</td>\n",
       "      <td>82901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>6557</td>\n",
       "      <td>S Staples St</td>\n",
       "      <td>78413</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   restaurant_address_id  restaurant_id street_no   street_name zip_code\n",
       "0                      1              1       800  N Canal Blvd    70301\n",
       "1                      2              1       124     John R Rd    48083\n",
       "2                      3              1       909        N Wood    75644\n",
       "3                      4              1        97  Gateway Blvd    82901\n",
       "4                      5              1      6557  S Staples St    78413"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM restaurant_address', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>restaurant_id</th>\n",
       "      <th>restaurant_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>SONIC Drive In</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Taco Bell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Arby's</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Steak 'n Shake</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Wendy's</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   restaurant_id restaurant_name\n",
       "0              1  SONIC Drive In\n",
       "1              2       Taco Bell\n",
       "2              3          Arby's\n",
       "3              4  Steak 'n Shake\n",
       "4              5         Wendy's"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM restaurant', con=engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Top zip_code with the most number of fast food restaurants and related census"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "most_restaurants = pd.read_sql_query(\\\n",
    "                       'SELECT\\\n",
    "                            COUNT(restaurant_id) AS num_restaurants,\\\n",
    "                            z.zip_code, z.city, z.state,\\\n",
    "                            cs.zcta, cs.population, cs.median_age, cs.median_household_income,\\\n",
    "                            cs.per_capita_income, cs.poverty_count, cs.unemployment_count\\\n",
    "                        FROM restaurant_address AS ra\\\n",
    "                        INNER JOIN zip AS z\\\n",
    "                            ON z.zip_code = ra.zip_code\\\n",
    "                        INNER JOIN zip_zcta AS zz\\\n",
    "                            ON zz.zip_code = z.zip_code\\\n",
    "                        INNER JOIN census AS cs\\\n",
    "                            ON cs.zcta = zz.zcta\\\n",
    "                        GROUP BY z.zip_code, z.city, z.state,\\\n",
    "                            cs.zcta, cs.population, cs.median_age,\\\n",
    "                            cs.median_household_income, cs.per_capita_income,\\\n",
    "                            cs.poverty_count, cs.unemployment_count\\\n",
    "                        ORDER BY num_restaurants DESC\\\n",
    "                        LIMIT 10',\\\n",
    "                       con=engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Top zip_code with the least number of fast food restaurants and related census"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "least_restaurant = pd.read_sql_query(\\\n",
    "                       'SELECT\\\n",
    "                            COUNT(restaurant_id) AS num_restaurants,\\\n",
    "                            z.zip_code, z.city, z.state,\\\n",
    "                            cs.zcta, cs.population, cs.median_age, cs.median_household_income,\\\n",
    "                            cs.per_capita_income, cs.poverty_count, cs.unemployment_count\\\n",
    "                        FROM restaurant_address AS ra\\\n",
    "                        INNER JOIN zip AS z\\\n",
    "                            ON z.zip_code = ra.zip_code\\\n",
    "                        INNER JOIN zip_zcta AS zz\\\n",
    "                            ON zz.zip_code = z.zip_code\\\n",
    "                        INNER JOIN census AS cs\\\n",
    "                            ON cs.zcta = zz.zcta\\\n",
    "                        GROUP BY z.zip_code, z.city, z.state,\\\n",
    "                            cs.zcta, cs.population, cs.median_age,\\\n",
    "                            cs.median_household_income, cs.per_capita_income,\\\n",
    "                            cs.poverty_count, cs.unemployment_count\\\n",
    "                        ORDER BY num_restaurants ASC\\\n",
    "                        LIMIT 10',\\\n",
    "                    con=engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Comparing some census data at the zcta with the most and least number fast food restaurants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>num_restaurants</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zcta</th>\n",
       "      <th>population</th>\n",
       "      <th>median_age</th>\n",
       "      <th>median_household_income</th>\n",
       "      <th>per_capita_income</th>\n",
       "      <th>poverty_count</th>\n",
       "      <th>unemployment_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>32809</td>\n",
       "      <td>Orlando</td>\n",
       "      <td>FL</td>\n",
       "      <td>32809</td>\n",
       "      <td>28258.0</td>\n",
       "      <td>35.4</td>\n",
       "      <td>43919.0</td>\n",
       "      <td>21304.0</td>\n",
       "      <td>6416.0</td>\n",
       "      <td>1126.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>32810</td>\n",
       "      <td>Orlando</td>\n",
       "      <td>FL</td>\n",
       "      <td>32810</td>\n",
       "      <td>39384.0</td>\n",
       "      <td>32.8</td>\n",
       "      <td>48727.0</td>\n",
       "      <td>21728.0</td>\n",
       "      <td>7671.0</td>\n",
       "      <td>1576.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>75150</td>\n",
       "      <td>Mesquite</td>\n",
       "      <td>TX</td>\n",
       "      <td>75150</td>\n",
       "      <td>62452.0</td>\n",
       "      <td>32.2</td>\n",
       "      <td>52439.0</td>\n",
       "      <td>23139.0</td>\n",
       "      <td>8351.0</td>\n",
       "      <td>1793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>43026</td>\n",
       "      <td>Hilliard</td>\n",
       "      <td>OH</td>\n",
       "      <td>43026</td>\n",
       "      <td>62169.0</td>\n",
       "      <td>35.3</td>\n",
       "      <td>86032.0</td>\n",
       "      <td>39083.0</td>\n",
       "      <td>4483.0</td>\n",
       "      <td>982.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>92335</td>\n",
       "      <td>Fontana</td>\n",
       "      <td>CA</td>\n",
       "      <td>92335</td>\n",
       "      <td>99284.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>50730.0</td>\n",
       "      <td>16138.0</td>\n",
       "      <td>19875.0</td>\n",
       "      <td>4565.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8</td>\n",
       "      <td>30606</td>\n",
       "      <td>Athens</td>\n",
       "      <td>GA</td>\n",
       "      <td>30606</td>\n",
       "      <td>43716.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>48890.0</td>\n",
       "      <td>32344.0</td>\n",
       "      <td>10405.0</td>\n",
       "      <td>1572.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8</td>\n",
       "      <td>89103</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>89103</td>\n",
       "      <td>52149.0</td>\n",
       "      <td>37.9</td>\n",
       "      <td>40261.0</td>\n",
       "      <td>23287.0</td>\n",
       "      <td>8294.0</td>\n",
       "      <td>1920.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>54701</td>\n",
       "      <td>Eau Claire</td>\n",
       "      <td>WI</td>\n",
       "      <td>54701</td>\n",
       "      <td>40130.0</td>\n",
       "      <td>34.6</td>\n",
       "      <td>56810.0</td>\n",
       "      <td>31100.0</td>\n",
       "      <td>4965.0</td>\n",
       "      <td>802.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7</td>\n",
       "      <td>29621</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>SC</td>\n",
       "      <td>29621</td>\n",
       "      <td>40931.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>56887.0</td>\n",
       "      <td>31854.0</td>\n",
       "      <td>4061.0</td>\n",
       "      <td>1115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7</td>\n",
       "      <td>44256</td>\n",
       "      <td>Medina</td>\n",
       "      <td>OH</td>\n",
       "      <td>44256</td>\n",
       "      <td>62916.0</td>\n",
       "      <td>42.2</td>\n",
       "      <td>78924.0</td>\n",
       "      <td>38406.0</td>\n",
       "      <td>4009.0</td>\n",
       "      <td>1006.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_restaurants zip_code        city state   zcta  population  median_age  \\\n",
       "0                9    32809     Orlando    FL  32809     28258.0        35.4   \n",
       "1                8    32810     Orlando    FL  32810     39384.0        32.8   \n",
       "2                8    75150    Mesquite    TX  75150     62452.0        32.2   \n",
       "3                8    43026    Hilliard    OH  43026     62169.0        35.3   \n",
       "4                8    92335     Fontana    CA  92335     99284.0        29.0   \n",
       "5                8    30606      Athens    GA  30606     43716.0        33.0   \n",
       "6                8    89103   Las Vegas    NV  89103     52149.0        37.9   \n",
       "7                8    54701  Eau Claire    WI  54701     40130.0        34.6   \n",
       "8                7    29621    Anderson    SC  29621     40931.0        44.0   \n",
       "9                7    44256      Medina    OH  44256     62916.0        42.2   \n",
       "\n",
       "   median_household_income  per_capita_income  poverty_count  \\\n",
       "0                  43919.0            21304.0         6416.0   \n",
       "1                  48727.0            21728.0         7671.0   \n",
       "2                  52439.0            23139.0         8351.0   \n",
       "3                  86032.0            39083.0         4483.0   \n",
       "4                  50730.0            16138.0        19875.0   \n",
       "5                  48890.0            32344.0        10405.0   \n",
       "6                  40261.0            23287.0         8294.0   \n",
       "7                  56810.0            31100.0         4965.0   \n",
       "8                  56887.0            31854.0         4061.0   \n",
       "9                  78924.0            38406.0         4009.0   \n",
       "\n",
       "   unemployment_count  \n",
       "0              1126.0  \n",
       "1              1576.0  \n",
       "2              1793.0  \n",
       "3               982.0  \n",
       "4              4565.0  \n",
       "5              1572.0  \n",
       "6              1920.0  \n",
       "7               802.0  \n",
       "8              1115.0  \n",
       "9              1006.0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "most_restaurants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>num_restaurants</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zcta</th>\n",
       "      <th>population</th>\n",
       "      <th>median_age</th>\n",
       "      <th>median_household_income</th>\n",
       "      <th>per_capita_income</th>\n",
       "      <th>poverty_count</th>\n",
       "      <th>unemployment_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>01105</td>\n",
       "      <td>Springfield</td>\n",
       "      <td>MA</td>\n",
       "      <td>01105</td>\n",
       "      <td>11957.0</td>\n",
       "      <td>31.1</td>\n",
       "      <td>19921.0</td>\n",
       "      <td>13698.0</td>\n",
       "      <td>5996.0</td>\n",
       "      <td>645.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>01001</td>\n",
       "      <td>Agawam</td>\n",
       "      <td>MA</td>\n",
       "      <td>01001</td>\n",
       "      <td>17621.0</td>\n",
       "      <td>46.1</td>\n",
       "      <td>62144.0</td>\n",
       "      <td>35135.0</td>\n",
       "      <td>1513.0</td>\n",
       "      <td>314.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>01027</td>\n",
       "      <td>Easthampton</td>\n",
       "      <td>MA</td>\n",
       "      <td>01027</td>\n",
       "      <td>17848.0</td>\n",
       "      <td>45.3</td>\n",
       "      <td>62772.0</td>\n",
       "      <td>37036.0</td>\n",
       "      <td>1597.0</td>\n",
       "      <td>590.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>01013</td>\n",
       "      <td>Chicopee</td>\n",
       "      <td>MA</td>\n",
       "      <td>01013</td>\n",
       "      <td>23065.0</td>\n",
       "      <td>35.5</td>\n",
       "      <td>40594.0</td>\n",
       "      <td>23656.0</td>\n",
       "      <td>4603.0</td>\n",
       "      <td>825.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>01060</td>\n",
       "      <td>Northampton</td>\n",
       "      <td>MA</td>\n",
       "      <td>01060</td>\n",
       "      <td>15779.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>60020.0</td>\n",
       "      <td>36203.0</td>\n",
       "      <td>2280.0</td>\n",
       "      <td>498.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>01073</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>MA</td>\n",
       "      <td>01073</td>\n",
       "      <td>6140.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>93854.0</td>\n",
       "      <td>46378.0</td>\n",
       "      <td>212.0</td>\n",
       "      <td>174.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>01085</td>\n",
       "      <td>Westfield</td>\n",
       "      <td>MA</td>\n",
       "      <td>01085</td>\n",
       "      <td>41942.0</td>\n",
       "      <td>39.5</td>\n",
       "      <td>64026.0</td>\n",
       "      <td>31563.0</td>\n",
       "      <td>2806.0</td>\n",
       "      <td>1223.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>01089</td>\n",
       "      <td>West Springfield</td>\n",
       "      <td>MA</td>\n",
       "      <td>01089</td>\n",
       "      <td>28666.0</td>\n",
       "      <td>38.9</td>\n",
       "      <td>51472.0</td>\n",
       "      <td>31135.0</td>\n",
       "      <td>3329.0</td>\n",
       "      <td>984.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>01040</td>\n",
       "      <td>Holyoke</td>\n",
       "      <td>MA</td>\n",
       "      <td>01040</td>\n",
       "      <td>40376.0</td>\n",
       "      <td>34.9</td>\n",
       "      <td>40656.0</td>\n",
       "      <td>23385.0</td>\n",
       "      <td>11630.0</td>\n",
       "      <td>1549.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>01107</td>\n",
       "      <td>Springfield</td>\n",
       "      <td>MA</td>\n",
       "      <td>01107</td>\n",
       "      <td>11582.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>27131.0</td>\n",
       "      <td>14816.0</td>\n",
       "      <td>5309.0</td>\n",
       "      <td>1046.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_restaurants zip_code              city state   zcta  population  \\\n",
       "0                1    01105       Springfield    MA  01105     11957.0   \n",
       "1                1    01001            Agawam    MA  01001     17621.0   \n",
       "2                1    01027       Easthampton    MA  01027     17848.0   \n",
       "3                1    01013          Chicopee    MA  01013     23065.0   \n",
       "4                1    01060       Northampton    MA  01060     15779.0   \n",
       "5                1    01073       Southampton    MA  01073      6140.0   \n",
       "6                1    01085         Westfield    MA  01085     41942.0   \n",
       "7                1    01089  West Springfield    MA  01089     28666.0   \n",
       "8                1    01040           Holyoke    MA  01040     40376.0   \n",
       "9                1    01107       Springfield    MA  01107     11582.0   \n",
       "\n",
       "   median_age  median_household_income  per_capita_income  poverty_count  \\\n",
       "0        31.1                  19921.0            13698.0         5996.0   \n",
       "1        46.1                  62144.0            35135.0         1513.0   \n",
       "2        45.3                  62772.0            37036.0         1597.0   \n",
       "3        35.5                  40594.0            23656.0         4603.0   \n",
       "4        35.0                  60020.0            36203.0         2280.0   \n",
       "5        45.7                  93854.0            46378.0          212.0   \n",
       "6        39.5                  64026.0            31563.0         2806.0   \n",
       "7        38.9                  51472.0            31135.0         3329.0   \n",
       "8        34.9                  40656.0            23385.0        11630.0   \n",
       "9        27.0                  27131.0            14816.0         5309.0   \n",
       "\n",
       "   unemployment_count  \n",
       "0               645.0  \n",
       "1               314.0  \n",
       "2               590.0  \n",
       "3               825.0  \n",
       "4               498.0  \n",
       "5               174.0  \n",
       "6              1223.0  \n",
       "7               984.0  \n",
       "8              1549.0  \n",
       "9              1046.0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "least_restaurant"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Restaurant brands with the most number of stores in the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>restaurant_name</th>\n",
       "      <th>num_restaurants</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>McDonald's</td>\n",
       "      <td>1729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Taco Bell</td>\n",
       "      <td>933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Burger King</td>\n",
       "      <td>768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Subway</td>\n",
       "      <td>766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arby's</td>\n",
       "      <td>585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Wendy's</td>\n",
       "      <td>580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Jack in the Box</td>\n",
       "      <td>315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Pizza Hut</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Domino's Pizza</td>\n",
       "      <td>213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Dairy Queen</td>\n",
       "      <td>207</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   restaurant_name  num_restaurants\n",
       "0       McDonald's             1729\n",
       "1        Taco Bell              933\n",
       "2      Burger King              768\n",
       "3           Subway              766\n",
       "4           Arby's              585\n",
       "5          Wendy's              580\n",
       "6  Jack in the Box              315\n",
       "7        Pizza Hut              228\n",
       "8   Domino's Pizza              213\n",
       "9      Dairy Queen              207"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\\\n",
    "                'SELECT \\\n",
    "                  r.restaurant_name,\\\n",
    "                  COUNT(ra.restaurant_id) AS num_restaurants\\\n",
    "                 FROM restaurant_address AS ra\\\n",
    "                 INNER JOIN restaurant AS r\\\n",
    "                 ON ra.restaurant_id = r.restaurant_id\\\n",
    "                 GROUP BY r.restaurant_name\\\n",
    "                 ORDER BY num_restaurants DESC\\\n",
    "                 LIMIT 10',\\\n",
    "                 con=engine)"
   ]
  }
 ],
 "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.6"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}