ETL_Notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL Project\n",
    "\n",
    "Team Members\n",
    "- Harry Feldman\n",
    "- Jessica Pardo\n",
    "- Andrey Tokarev\n",
    "- Raven Washington\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import Dependencies\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import Session\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "import psycopg2\n",
    "\n",
    "# Get the password from config file\n",
    "from config import password"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## U.S. State Data\n",
    "\n",
    "- U.S. State Tables (CSV file). Data source: http://www.whypad.com/posts/excel-spreadsheet-of-us-states/583/\n",
    "- Quality Of Life by State (CSV file). Data source: https://worldpopulationreview.com/state-rankings/quality-of-life-by-state"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# File path\n",
    "states_csv = 'Resources/Raw_data/us_states.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>STATE</th>\n",
       "      <th>state_name</th>\n",
       "      <th>state_abr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALASKA</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARIZONA</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ARKANSAS</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CALIFORNIA</td>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        STATE  state_name state_abr\n",
       "0     ALABAMA     Alabama        AL\n",
       "1      ALASKA      Alaska        AK\n",
       "2     ARIZONA     Arizona        AZ\n",
       "3    ARKANSAS    Arkansas        AR\n",
       "4  CALIFORNIA  California        CA"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read csv and store in to Pandas Dataframe\n",
    "col_Names=[\"STATE\", \"state_name\", \"state_abr\"]\n",
    "states_df= pd.read_csv(states_csv,names=col_Names)\n",
    "states_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# File path\n",
    "ranking_csv = 'Resources/Raw_data/state_ranking.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>State</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Washington</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Minnesota</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Utah</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Vermont</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           State  lifeQualityRank  healthCareRank  educationRank  economyRank\n",
       "0     Washington                1               4              4            3\n",
       "1  New Hampshire                2              16              5           13\n",
       "2      Minnesota                3              10             17           18\n",
       "3           Utah                4               9             10            2\n",
       "4        Vermont                5              11              8           29"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read csv and store in to Pandas Dataframe\n",
    "ranking_df = pd.read_csv(ranking_csv)\n",
    "ranking_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "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>state_name</th>\n",
       "      <th>state_abr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_name state_abr\n",
       "0     Alabama        AL\n",
       "1      Alaska        AK\n",
       "2     Arizona        AZ\n",
       "3    Arkansas        AR\n",
       "4  California        CA"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states_df = states_df.drop(columns=['STATE'])\n",
    "states_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>state_name</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Washington</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Minnesota</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Utah</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Vermont</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      state_name  lifeQualityRank  healthCareRank  educationRank  economyRank\n",
       "0     Washington                1               4              4            3\n",
       "1  New Hampshire                2              16              5           13\n",
       "2      Minnesota                3              10             17           18\n",
       "3           Utah                4               9             10            2\n",
       "4        Vermont                5              11              8           29"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ranking_df = ranking_df.rename(columns={'State':'state_name'})\n",
    "ranking_df.head()"
   ]
  },
  {
   "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>state_name</th>\n",
       "      <th>state_abr</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>44</td>\n",
       "      <td>25</td>\n",
       "      <td>47</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>34</td>\n",
       "      <td>23</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>45</td>\n",
       "      <td>49</td>\n",
       "      <td>42</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "      <td>19</td>\n",
       "      <td>7</td>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_name state_abr  lifeQualityRank  healthCareRank  educationRank  \\\n",
       "0     Alabama        AL               49              46             50   \n",
       "1      Alaska        AK               44              25             47   \n",
       "2     Arizona        AZ               34              23             40   \n",
       "3    Arkansas        AR               45              49             42   \n",
       "4  California        CA               19               7             21   \n",
       "\n",
       "   economyRank  \n",
       "0           45  \n",
       "1           46  \n",
       "2           10  \n",
       "3           43  \n",
       "4            4  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state = pd.merge(left=states_df, right=ranking_df, how='left', left_on='state_name', right_on='state_name')\n",
    "state.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>state_name</th>\n",
       "      <th>state_abr</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>44</td>\n",
       "      <td>25</td>\n",
       "      <td>47</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>34</td>\n",
       "      <td>23</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>45</td>\n",
       "      <td>49</td>\n",
       "      <td>42</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "      <td>19</td>\n",
       "      <td>7</td>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          state_name state_abr  lifeQualityRank  healthCareRank  \\\n",
       "state_id                                                          \n",
       "0            Alabama        AL               49              46   \n",
       "1             Alaska        AK               44              25   \n",
       "2            Arizona        AZ               34              23   \n",
       "3           Arkansas        AR               45              49   \n",
       "4         California        CA               19               7   \n",
       "\n",
       "          educationRank  economyRank  \n",
       "state_id                              \n",
       "0                    50           45  \n",
       "1                    47           46  \n",
       "2                    40           10  \n",
       "3                    42           43  \n",
       "4                    21            4  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state.index.names = ['state_id']\n",
    "state.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export Data\n",
    "state.to_csv(r'Resources/Clean_data/state.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Housing Data\n",
    "\n",
    "- Zillow Economics Data(CSV file). Data source: https://www.kaggle.com/zillow/zecon"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>state_id</th>\n",
       "      <th>state_name</th>\n",
       "      <th>state_abr</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>44</td>\n",
       "      <td>25</td>\n",
       "      <td>47</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>34</td>\n",
       "      <td>23</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_id state_name state_abr  lifeQualityRank  healthCareRank  \\\n",
       "0         0    Alabama        AL               49              46   \n",
       "1         1     Alaska        AK               44              25   \n",
       "2         2    Arizona        AZ               34              23   \n",
       "\n",
       "   educationRank  economyRank  \n",
       "0             50           45  \n",
       "1             47           46  \n",
       "2             40           10  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_id_df = pd.read_csv(\"./Resources/Clean_data/state.csv\")\n",
    "state_id_df.head(3)"
   ]
  },
  {
   "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>Date</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>DaysOnZillow_AllHomes</th>\n",
       "      <th>InventorySeasonallyAdjusted_AllHomes</th>\n",
       "      <th>InventoryRaw_AllHomes</th>\n",
       "      <th>MedianListingPricePerSqft_1Bedroom</th>\n",
       "      <th>MedianListingPricePerSqft_2Bedroom</th>\n",
       "      <th>MedianListingPricePerSqft_3Bedroom</th>\n",
       "      <th>MedianListingPricePerSqft_4Bedroom</th>\n",
       "      <th>MedianListingPricePerSqft_5BedroomOrMore</th>\n",
       "      <th>...</th>\n",
       "      <th>ZHVI_BottomTier</th>\n",
       "      <th>ZHVI_CondoCoop</th>\n",
       "      <th>ZHVI_MiddleTier</th>\n",
       "      <th>ZHVI_SingleFamilyResidence</th>\n",
       "      <th>ZHVI_TopTier</th>\n",
       "      <th>ZRI_AllHomes</th>\n",
       "      <th>ZRI_AllHomesPlusMultifamily</th>\n",
       "      <th>ZriPerSqft_AllHomes</th>\n",
       "      <th>Zri_MultiFamilyResidenceRental</th>\n",
       "      <th>Zri_SingleFamilyResidenceRental</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1996-04-30</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>45600.0</td>\n",
       "      <td>99500.0</td>\n",
       "      <td>79500.0</td>\n",
       "      <td>79000.0</td>\n",
       "      <td>140200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1996-04-30</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>67100.0</td>\n",
       "      <td>78900.0</td>\n",
       "      <td>103600.0</td>\n",
       "      <td>107500.0</td>\n",
       "      <td>168700.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1996-04-30</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>38400.0</td>\n",
       "      <td>70300.0</td>\n",
       "      <td>64400.0</td>\n",
       "      <td>64500.0</td>\n",
       "      <td>115200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 82 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date RegionName  DaysOnZillow_AllHomes  \\\n",
       "0  1996-04-30    Alabama                    NaN   \n",
       "1  1996-04-30    Arizona                    NaN   \n",
       "2  1996-04-30   Arkansas                    NaN   \n",
       "\n",
       "   InventorySeasonallyAdjusted_AllHomes  InventoryRaw_AllHomes  \\\n",
       "0                                   NaN                    NaN   \n",
       "1                                   NaN                    NaN   \n",
       "2                                   NaN                    NaN   \n",
       "\n",
       "   MedianListingPricePerSqft_1Bedroom  MedianListingPricePerSqft_2Bedroom  \\\n",
       "0                                 NaN                                 NaN   \n",
       "1                                 NaN                                 NaN   \n",
       "2                                 NaN                                 NaN   \n",
       "\n",
       "   MedianListingPricePerSqft_3Bedroom  MedianListingPricePerSqft_4Bedroom  \\\n",
       "0                                 NaN                                 NaN   \n",
       "1                                 NaN                                 NaN   \n",
       "2                                 NaN                                 NaN   \n",
       "\n",
       "   MedianListingPricePerSqft_5BedroomOrMore  ...  ZHVI_BottomTier  \\\n",
       "0                                       NaN  ...          45600.0   \n",
       "1                                       NaN  ...          67100.0   \n",
       "2                                       NaN  ...          38400.0   \n",
       "\n",
       "   ZHVI_CondoCoop  ZHVI_MiddleTier  ZHVI_SingleFamilyResidence  ZHVI_TopTier  \\\n",
       "0         99500.0          79500.0                     79000.0      140200.0   \n",
       "1         78900.0         103600.0                    107500.0      168700.0   \n",
       "2         70300.0          64400.0                     64500.0      115200.0   \n",
       "\n",
       "   ZRI_AllHomes  ZRI_AllHomesPlusMultifamily  ZriPerSqft_AllHomes  \\\n",
       "0           NaN                          NaN                  NaN   \n",
       "1           NaN                          NaN                  NaN   \n",
       "2           NaN                          NaN                  NaN   \n",
       "\n",
       "   Zri_MultiFamilyResidenceRental  Zri_SingleFamilyResidenceRental  \n",
       "0                             NaN                              NaN  \n",
       "1                             NaN                              NaN  \n",
       "2                             NaN                              NaN  \n",
       "\n",
       "[3 rows x 82 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "housing_df = pd.read_csv(\"./Resources/Raw_data/State_time_series.csv\")\n",
    "housing_df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Date</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>MedianListingPricePerSqft_AllHomes</th>\n",
       "      <th>MedianRentalPricePerSqft_AllHomes</th>\n",
       "      <th>PctOfHomesDecreasingInValues_AllHomes</th>\n",
       "      <th>PctOfHomesIncreasingInValues_AllHomes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1996-04-30</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1996-04-30</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1996-04-30</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date RegionName  MedianListingPricePerSqft_AllHomes  \\\n",
       "0  1996-04-30    Alabama                                 NaN   \n",
       "1  1996-04-30    Arizona                                 NaN   \n",
       "2  1996-04-30   Arkansas                                 NaN   \n",
       "\n",
       "   MedianRentalPricePerSqft_AllHomes  PctOfHomesDecreasingInValues_AllHomes  \\\n",
       "0                                NaN                                    NaN   \n",
       "1                                NaN                                    NaN   \n",
       "2                                NaN                                    NaN   \n",
       "\n",
       "   PctOfHomesIncreasingInValues_AllHomes  \n",
       "0                                    NaN  \n",
       "1                                    NaN  \n",
       "2                                    NaN  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_housing_df = housing_df[['Date','RegionName', 'MedianListingPricePerSqft_AllHomes', 'MedianRentalPricePerSqft_AllHomes', \n",
    "                               'PctOfHomesDecreasingInValues_AllHomes', 'PctOfHomesIncreasingInValues_AllHomes']]\n",
    "clean_housing_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_housing_df = clean_housing_df.dropna()"
   ]
  },
  {
   "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>date</th>\n",
       "      <th>state_name</th>\n",
       "      <th>MedianListingPricePerSqft_AllHomes</th>\n",
       "      <th>MedianRentalPricePerSqft_AllHomes</th>\n",
       "      <th>PctOfHomesDecreasingInValues_AllHomes</th>\n",
       "      <th>PctOfHomesIncreasingInValues_AllHomes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8231</th>\n",
       "      <td>2010-01-31</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>94.303797</td>\n",
       "      <td>0.715309</td>\n",
       "      <td>67.15</td>\n",
       "      <td>25.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8269</th>\n",
       "      <td>2010-01-31</td>\n",
       "      <td>Washington</td>\n",
       "      <td>169.014085</td>\n",
       "      <td>0.961538</td>\n",
       "      <td>78.82</td>\n",
       "      <td>14.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8276</th>\n",
       "      <td>2010-02-28</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>88.310580</td>\n",
       "      <td>0.606187</td>\n",
       "      <td>40.91</td>\n",
       "      <td>48.71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            date  state_name  MedianListingPricePerSqft_AllHomes  \\\n",
       "8231  2010-01-31     Georgia                           94.303797   \n",
       "8269  2010-01-31  Washington                          169.014085   \n",
       "8276  2010-02-28    Arkansas                           88.310580   \n",
       "\n",
       "      MedianRentalPricePerSqft_AllHomes  \\\n",
       "8231                           0.715309   \n",
       "8269                           0.961538   \n",
       "8276                           0.606187   \n",
       "\n",
       "      PctOfHomesDecreasingInValues_AllHomes  \\\n",
       "8231                                  67.15   \n",
       "8269                                  78.82   \n",
       "8276                                  40.91   \n",
       "\n",
       "      PctOfHomesIncreasingInValues_AllHomes  \n",
       "8231                                  25.88  \n",
       "8269                                  14.58  \n",
       "8276                                  48.71  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# renaming columns according to the ERD\n",
    "\n",
    "# state_id integer FK >- state.state_id\n",
    "# date date\n",
    "# MedianListingPricePerSqft_AllHomes integer\n",
    "# MedianRentalPricePerSqft_AllHomes integer\n",
    "# PctOfHomesDecreasingInValues_AllHomes float\n",
    "# PctOfHomesIncreasingInValues_AllHomes float\n",
    "\n",
    "clean_housing_df = clean_housing_df.rename(columns={\"RegionName\": \"state_name\", \"Date\": \"date\"})\n",
    "clean_housing_df.head(3)"
   ]
  },
  {
   "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>date</th>\n",
       "      <th>state_name</th>\n",
       "      <th>MedianListingPricePerSqft_AllHomes</th>\n",
       "      <th>MedianRentalPricePerSqft_AllHomes</th>\n",
       "      <th>PctOfHomesDecreasingInValues_AllHomes</th>\n",
       "      <th>PctOfHomesIncreasingInValues_AllHomes</th>\n",
       "      <th>state_id</th>\n",
       "      <th>state_abr</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-01-31</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>94.303797</td>\n",
       "      <td>0.715309</td>\n",
       "      <td>67.15</td>\n",
       "      <td>25.88</td>\n",
       "      <td>9</td>\n",
       "      <td>GA</td>\n",
       "      <td>17</td>\n",
       "      <td>39</td>\n",
       "      <td>30</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010-02-28</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>94.234234</td>\n",
       "      <td>0.706880</td>\n",
       "      <td>68.87</td>\n",
       "      <td>23.88</td>\n",
       "      <td>9</td>\n",
       "      <td>GA</td>\n",
       "      <td>17</td>\n",
       "      <td>39</td>\n",
       "      <td>30</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010-03-31</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>94.186424</td>\n",
       "      <td>0.708333</td>\n",
       "      <td>69.38</td>\n",
       "      <td>23.21</td>\n",
       "      <td>9</td>\n",
       "      <td>GA</td>\n",
       "      <td>17</td>\n",
       "      <td>39</td>\n",
       "      <td>30</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date state_name  MedianListingPricePerSqft_AllHomes  \\\n",
       "0  2010-01-31    Georgia                           94.303797   \n",
       "1  2010-02-28    Georgia                           94.234234   \n",
       "2  2010-03-31    Georgia                           94.186424   \n",
       "\n",
       "   MedianRentalPricePerSqft_AllHomes  PctOfHomesDecreasingInValues_AllHomes  \\\n",
       "0                           0.715309                                  67.15   \n",
       "1                           0.706880                                  68.87   \n",
       "2                           0.708333                                  69.38   \n",
       "\n",
       "   PctOfHomesIncreasingInValues_AllHomes  state_id state_abr  lifeQualityRank  \\\n",
       "0                                  25.88         9        GA               17   \n",
       "1                                  23.88         9        GA               17   \n",
       "2                                  23.21         9        GA               17   \n",
       "\n",
       "   healthCareRank  educationRank  economyRank  \n",
       "0              39             30           11  \n",
       "1              39             30           11  \n",
       "2              39             30           11  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_housing_df_merged = pd.merge(clean_housing_df, state_id_df, how='inner', on='state_name')\n",
    "clean_housing_df_merged.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'state_name', 'MedianListingPricePerSqft_AllHomes',\n",
       "       'MedianRentalPricePerSqft_AllHomes',\n",
       "       'PctOfHomesDecreasingInValues_AllHomes',\n",
       "       'PctOfHomesIncreasingInValues_AllHomes', 'state_id', 'state_abr',\n",
       "       'lifeQualityRank', 'healthCareRank', 'educationRank', 'economyRank'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_housing_df_merged.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "housing_df_final = clean_housing_df_merged.drop(labels=['state_abr', 'state_name', \n",
    "                                                        'lifeQualityRank', 'healthCareRank',\n",
    "                                                        'educationRank', 'economyRank'\n",
    "                                                       ], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'MedianListingPricePerSqft_AllHomes',\n",
       "       'MedianRentalPricePerSqft_AllHomes',\n",
       "       'PctOfHomesDecreasingInValues_AllHomes',\n",
       "       'PctOfHomesIncreasingInValues_AllHomes', 'state_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "housing_df_final.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "housing_df_final.to_csv(\"./Resources/Clean_data/housing.csv\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Income Data\n",
    "- USA Income Levels by United States Census Bureau https://data.world/uscensusbureau/acs-2015-5-e-income/workspace/file?filename=USA_ZCTA.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "income_df = pd.read_csv(\"./Resources/Raw_Data/USA_All_States.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_income_df = income_df[['AreaName','B19001_001']]\n",
    "clean_income_df = clean_income_df.dropna()"
   ]
  },
  {
   "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>state_name</th>\n",
       "      <th>household_income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>1848325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>250969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>2412212</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state_name  household_income\n",
       "0    Alabama           1848325\n",
       "1     Alaska            250969\n",
       "2    Arizona           2412212"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Renaming columns according to ERD\n",
    "# state_id integer FK >- state.state_id\n",
    "# household_income float\n",
    "# year integer\n",
    "\n",
    "clean_income_df = clean_income_df.rename(columns={\"AreaName\": \"state_name\", \"B19001_001\": \"household_income\"})\n",
    "clean_income_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>state_name</th>\n",
       "      <th>household_income</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>1848325</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>250969</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>2412212</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state_name  household_income  year\n",
       "0    Alabama           1848325  2015\n",
       "1     Alaska            250969  2015\n",
       "2    Arizona           2412212  2015"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# adding \"year\" column\n",
    "clean_income_df['year'] = 2015\n",
    "clean_income_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>state_name</th>\n",
       "      <th>household_income</th>\n",
       "      <th>year</th>\n",
       "      <th>state_id</th>\n",
       "      <th>state_abr</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>1848325</td>\n",
       "      <td>2015</td>\n",
       "      <td>0</td>\n",
       "      <td>AL</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>250969</td>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>AK</td>\n",
       "      <td>44</td>\n",
       "      <td>25</td>\n",
       "      <td>47</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>2412212</td>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>AZ</td>\n",
       "      <td>34</td>\n",
       "      <td>23</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state_name  household_income  year  state_id state_abr  lifeQualityRank  \\\n",
       "0    Alabama           1848325  2015         0        AL               49   \n",
       "1     Alaska            250969  2015         1        AK               44   \n",
       "2    Arizona           2412212  2015         2        AZ               34   \n",
       "\n",
       "   healthCareRank  educationRank  economyRank  \n",
       "0              46             50           45  \n",
       "1              25             47           46  \n",
       "2              23             40           10  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# need to replace state_name with state_id column \n",
    "clean_income_df_merged = pd.merge(clean_income_df, state_id_df, how='inner', on='state_name')\n",
    "clean_income_df_merged.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "income_df_final = clean_income_df_merged.drop(labels=['state_abr', 'state_name', \n",
    "                                                        'lifeQualityRank', 'healthCareRank',\n",
    "                                                        'educationRank', 'economyRank'\n",
    "                                                       ], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['household_income', 'year', 'state_id'], dtype='object')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income_df_final.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "income_df_final.to_csv(\"./Resources/Clean_data/income.csv\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Crime Data\n",
    "- US Crime rates by County\n",
    "https://www.kaggle.com/mikejohnsonjr/united-states-crime-rates-by-county"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extraction"
   ]
  },
  {
   "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>county_name</th>\n",
       "      <th>crime_rate_per_100000</th>\n",
       "      <th>index</th>\n",
       "      <th>EDITION</th>\n",
       "      <th>PART</th>\n",
       "      <th>IDNO</th>\n",
       "      <th>CPOPARST</th>\n",
       "      <th>CPOPCRIM</th>\n",
       "      <th>AG_ARRST</th>\n",
       "      <th>AG_OFF</th>\n",
       "      <th>...</th>\n",
       "      <th>RAPE</th>\n",
       "      <th>ROBBERY</th>\n",
       "      <th>AGASSLT</th>\n",
       "      <th>BURGLRY</th>\n",
       "      <th>LARCENY</th>\n",
       "      <th>MVTHEFT</th>\n",
       "      <th>ARSON</th>\n",
       "      <th>population</th>\n",
       "      <th>FIPS_ST</th>\n",
       "      <th>FIPS_CTY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>St. Louis city, MO</td>\n",
       "      <td>1791.995377</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1612</td>\n",
       "      <td>318667</td>\n",
       "      <td>318667</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>200</td>\n",
       "      <td>1778</td>\n",
       "      <td>3609</td>\n",
       "      <td>4995</td>\n",
       "      <td>13791</td>\n",
       "      <td>3543</td>\n",
       "      <td>464</td>\n",
       "      <td>318416</td>\n",
       "      <td>29</td>\n",
       "      <td>510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Crittenden County, AR</td>\n",
       "      <td>1754.914968</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>130</td>\n",
       "      <td>50717</td>\n",
       "      <td>50717</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>38</td>\n",
       "      <td>165</td>\n",
       "      <td>662</td>\n",
       "      <td>1482</td>\n",
       "      <td>1753</td>\n",
       "      <td>189</td>\n",
       "      <td>28</td>\n",
       "      <td>49746</td>\n",
       "      <td>5</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alexander County, IL</td>\n",
       "      <td>1664.700485</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>604</td>\n",
       "      <td>8040</td>\n",
       "      <td>8040</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>119</td>\n",
       "      <td>82</td>\n",
       "      <td>184</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>7629</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             county_name  crime_rate_per_100000  index  EDITION  PART  IDNO  \\\n",
       "0     St. Louis city, MO            1791.995377      1        1     4  1612   \n",
       "1  Crittenden County, AR            1754.914968      2        1     4   130   \n",
       "2   Alexander County, IL            1664.700485      3        1     4   604   \n",
       "\n",
       "   CPOPARST  CPOPCRIM  AG_ARRST  AG_OFF  ...  RAPE  ROBBERY  AGASSLT  BURGLRY  \\\n",
       "0    318667    318667        15      15  ...   200     1778     3609     4995   \n",
       "1     50717     50717         4       4  ...    38      165      662     1482   \n",
       "2      8040      8040         2       2  ...     2        5      119       82   \n",
       "\n",
       "   LARCENY  MVTHEFT  ARSON  population  FIPS_ST  FIPS_CTY  \n",
       "0    13791     3543    464      318416       29       510  \n",
       "1     1753      189     28       49746        5        35  \n",
       "2      184       12      2        7629       17         3  \n",
       "\n",
       "[3 rows x 24 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df = pd.read_csv(\"./Resources/Raw_data/crime_data_w_population_and_crime_rate.csv\")\n",
    "crime_df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>county_name</th>\n",
       "      <th>crime_rate_per_100000</th>\n",
       "      <th>index</th>\n",
       "      <th>EDITION</th>\n",
       "      <th>PART</th>\n",
       "      <th>IDNO</th>\n",
       "      <th>CPOPARST</th>\n",
       "      <th>CPOPCRIM</th>\n",
       "      <th>AG_ARRST</th>\n",
       "      <th>AG_OFF</th>\n",
       "      <th>...</th>\n",
       "      <th>ROBBERY</th>\n",
       "      <th>AGASSLT</th>\n",
       "      <th>BURGLRY</th>\n",
       "      <th>LARCENY</th>\n",
       "      <th>MVTHEFT</th>\n",
       "      <th>ARSON</th>\n",
       "      <th>population</th>\n",
       "      <th>FIPS_ST</th>\n",
       "      <th>FIPS_CTY</th>\n",
       "      <th>state_abr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>St. Louis city</td>\n",
       "      <td>1791.995377</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1612</td>\n",
       "      <td>318667</td>\n",
       "      <td>318667</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>1778</td>\n",
       "      <td>3609</td>\n",
       "      <td>4995</td>\n",
       "      <td>13791</td>\n",
       "      <td>3543</td>\n",
       "      <td>464</td>\n",
       "      <td>318416</td>\n",
       "      <td>29</td>\n",
       "      <td>510</td>\n",
       "      <td>MO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Crittenden County</td>\n",
       "      <td>1754.914968</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>130</td>\n",
       "      <td>50717</td>\n",
       "      <td>50717</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>165</td>\n",
       "      <td>662</td>\n",
       "      <td>1482</td>\n",
       "      <td>1753</td>\n",
       "      <td>189</td>\n",
       "      <td>28</td>\n",
       "      <td>49746</td>\n",
       "      <td>5</td>\n",
       "      <td>35</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alexander County</td>\n",
       "      <td>1664.700485</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>604</td>\n",
       "      <td>8040</td>\n",
       "      <td>8040</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>119</td>\n",
       "      <td>82</td>\n",
       "      <td>184</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>7629</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         county_name  crime_rate_per_100000  index  EDITION  PART  IDNO  \\\n",
       "0     St. Louis city            1791.995377      1        1     4  1612   \n",
       "1  Crittenden County            1754.914968      2        1     4   130   \n",
       "2   Alexander County            1664.700485      3        1     4   604   \n",
       "\n",
       "   CPOPARST  CPOPCRIM  AG_ARRST  AG_OFF  ...  ROBBERY  AGASSLT  BURGLRY  \\\n",
       "0    318667    318667        15      15  ...     1778     3609     4995   \n",
       "1     50717     50717         4       4  ...      165      662     1482   \n",
       "2      8040      8040         2       2  ...        5      119       82   \n",
       "\n",
       "   LARCENY  MVTHEFT  ARSON  population  FIPS_ST  FIPS_CTY  state_abr  \n",
       "0    13791     3543    464      318416       29       510         MO  \n",
       "1     1753      189     28       49746        5        35         AR  \n",
       "2      184       12      2        7629       17         3         IL  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df[['county_name','state_abr']] = crime_df['county_name'].str.split(',',expand=True)\n",
    "crime_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>county_name</th>\n",
       "      <th>crime_rate_per_100000</th>\n",
       "      <th>CPOPARST</th>\n",
       "      <th>CPOPCRIM</th>\n",
       "      <th>AG_ARRST</th>\n",
       "      <th>AG_OFF</th>\n",
       "      <th>COVIND</th>\n",
       "      <th>INDEX</th>\n",
       "      <th>MODINDX</th>\n",
       "      <th>MURDER</th>\n",
       "      <th>...</th>\n",
       "      <th>ROBBERY</th>\n",
       "      <th>AGASSLT</th>\n",
       "      <th>BURGLRY</th>\n",
       "      <th>LARCENY</th>\n",
       "      <th>MVTHEFT</th>\n",
       "      <th>ARSON</th>\n",
       "      <th>population</th>\n",
       "      <th>FIPS_ST</th>\n",
       "      <th>FIPS_CTY</th>\n",
       "      <th>state_abr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>St. Louis city</td>\n",
       "      <td>1791.995377</td>\n",
       "      <td>318667</td>\n",
       "      <td>318667</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>100.0</td>\n",
       "      <td>5706</td>\n",
       "      <td>22329</td>\n",
       "      <td>119</td>\n",
       "      <td>...</td>\n",
       "      <td>1778</td>\n",
       "      <td>3609</td>\n",
       "      <td>4995</td>\n",
       "      <td>13791</td>\n",
       "      <td>3543</td>\n",
       "      <td>464</td>\n",
       "      <td>318416</td>\n",
       "      <td>29</td>\n",
       "      <td>510</td>\n",
       "      <td>MO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Crittenden County</td>\n",
       "      <td>1754.914968</td>\n",
       "      <td>50717</td>\n",
       "      <td>50717</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>100.0</td>\n",
       "      <td>873</td>\n",
       "      <td>3424</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>165</td>\n",
       "      <td>662</td>\n",
       "      <td>1482</td>\n",
       "      <td>1753</td>\n",
       "      <td>189</td>\n",
       "      <td>28</td>\n",
       "      <td>49746</td>\n",
       "      <td>5</td>\n",
       "      <td>35</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alexander County</td>\n",
       "      <td>1664.700485</td>\n",
       "      <td>8040</td>\n",
       "      <td>8040</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>100.0</td>\n",
       "      <td>127</td>\n",
       "      <td>278</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>119</td>\n",
       "      <td>82</td>\n",
       "      <td>184</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>7629</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         county_name  crime_rate_per_100000  CPOPARST  CPOPCRIM  AG_ARRST  \\\n",
       "0     St. Louis city            1791.995377    318667    318667        15   \n",
       "1  Crittenden County            1754.914968     50717     50717         4   \n",
       "2   Alexander County            1664.700485      8040      8040         2   \n",
       "\n",
       "   AG_OFF  COVIND  INDEX  MODINDX  MURDER  ...  ROBBERY  AGASSLT  BURGLRY  \\\n",
       "0      15   100.0   5706    22329     119  ...     1778     3609     4995   \n",
       "1       4   100.0    873     3424       8  ...      165      662     1482   \n",
       "2       2   100.0    127      278       1  ...        5      119       82   \n",
       "\n",
       "   LARCENY  MVTHEFT  ARSON  population  FIPS_ST  FIPS_CTY  state_abr  \n",
       "0    13791     3543    464      318416       29       510         MO  \n",
       "1     1753      189     28       49746        5        35         AR  \n",
       "2      184       12      2        7629       17         3         IL  \n",
       "\n",
       "[3 rows x 21 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df1 = crime_df.drop(labels=['index', 'EDITION', 'PART', 'IDNO'], axis=1)\n",
    "crime_df1.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>county_name</th>\n",
       "      <th>crime_rate_per_100000</th>\n",
       "      <th>CPOPARST</th>\n",
       "      <th>CPOPCRIM</th>\n",
       "      <th>AG_ARRST</th>\n",
       "      <th>AG_OFF</th>\n",
       "      <th>COVIND</th>\n",
       "      <th>INDEX</th>\n",
       "      <th>MODINDX</th>\n",
       "      <th>MURDER</th>\n",
       "      <th>...</th>\n",
       "      <th>AGASSLT</th>\n",
       "      <th>BURGLRY</th>\n",
       "      <th>LARCENY</th>\n",
       "      <th>MVTHEFT</th>\n",
       "      <th>ARSON</th>\n",
       "      <th>population</th>\n",
       "      <th>FIPS_ST</th>\n",
       "      <th>FIPS_CTY</th>\n",
       "      <th>state_abr</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>St. Louis city</td>\n",
       "      <td>1791.995377</td>\n",
       "      <td>318667</td>\n",
       "      <td>318667</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>100.0</td>\n",
       "      <td>5706</td>\n",
       "      <td>22329</td>\n",
       "      <td>119</td>\n",
       "      <td>...</td>\n",
       "      <td>3609</td>\n",
       "      <td>4995</td>\n",
       "      <td>13791</td>\n",
       "      <td>3543</td>\n",
       "      <td>464</td>\n",
       "      <td>318416</td>\n",
       "      <td>29</td>\n",
       "      <td>510</td>\n",
       "      <td>MO</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Crittenden County</td>\n",
       "      <td>1754.914968</td>\n",
       "      <td>50717</td>\n",
       "      <td>50717</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>100.0</td>\n",
       "      <td>873</td>\n",
       "      <td>3424</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>662</td>\n",
       "      <td>1482</td>\n",
       "      <td>1753</td>\n",
       "      <td>189</td>\n",
       "      <td>28</td>\n",
       "      <td>49746</td>\n",
       "      <td>5</td>\n",
       "      <td>35</td>\n",
       "      <td>AR</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alexander County</td>\n",
       "      <td>1664.700485</td>\n",
       "      <td>8040</td>\n",
       "      <td>8040</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>100.0</td>\n",
       "      <td>127</td>\n",
       "      <td>278</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>119</td>\n",
       "      <td>82</td>\n",
       "      <td>184</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>7629</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "      <td>IL</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         county_name  crime_rate_per_100000  CPOPARST  CPOPCRIM  AG_ARRST  \\\n",
       "0     St. Louis city            1791.995377    318667    318667        15   \n",
       "1  Crittenden County            1754.914968     50717     50717         4   \n",
       "2   Alexander County            1664.700485      8040      8040         2   \n",
       "\n",
       "   AG_OFF  COVIND  INDEX  MODINDX  MURDER  ...  AGASSLT  BURGLRY  LARCENY  \\\n",
       "0      15   100.0   5706    22329     119  ...     3609     4995    13791   \n",
       "1       4   100.0    873     3424       8  ...      662     1482     1753   \n",
       "2       2   100.0    127      278       1  ...      119       82      184   \n",
       "\n",
       "   MVTHEFT  ARSON  population  FIPS_ST  FIPS_CTY  state_abr  year  \n",
       "0     3543    464      318416       29       510         MO  2016  \n",
       "1      189     28       49746        5        35         AR  2016  \n",
       "2       12      2        7629       17         3         IL  2016  \n",
       "\n",
       "[3 rows x 22 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df1['year'] = 2016\n",
    "crime_df1.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['county_name', 'crime_rate_per_100000', 'CPOPARST', 'CPOPCRIM',\n",
       "       'AG_ARRST', 'AG_OFF', 'COVIND', 'INDEX', 'MODINDX', 'MURDER', 'RAPE',\n",
       "       'ROBBERY', 'AGASSLT', 'BURGLRY', 'LARCENY', 'MVTHEFT', 'ARSON',\n",
       "       'population', 'FIPS_ST', 'FIPS_CTY', 'state_abr', 'year'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df1.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_df2 = crime_df1.groupby(['state_abr'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>state_abr</th>\n",
       "      <th>crime_rate_per_100000</th>\n",
       "      <th>CPOPARST</th>\n",
       "      <th>CPOPCRIM</th>\n",
       "      <th>AG_ARRST</th>\n",
       "      <th>AG_OFF</th>\n",
       "      <th>COVIND</th>\n",
       "      <th>INDEX</th>\n",
       "      <th>MODINDX</th>\n",
       "      <th>MURDER</th>\n",
       "      <th>...</th>\n",
       "      <th>ROBBERY</th>\n",
       "      <th>AGASSLT</th>\n",
       "      <th>BURGLRY</th>\n",
       "      <th>LARCENY</th>\n",
       "      <th>MVTHEFT</th>\n",
       "      <th>ARSON</th>\n",
       "      <th>population</th>\n",
       "      <th>FIPS_ST</th>\n",
       "      <th>FIPS_CTY</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK</td>\n",
       "      <td>289.320957</td>\n",
       "      <td>20258.956522</td>\n",
       "      <td>20157.260870</td>\n",
       "      <td>1.347826</td>\n",
       "      <td>1.434783</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>147.913043</td>\n",
       "      <td>710.869565</td>\n",
       "      <td>0.913043</td>\n",
       "      <td>...</td>\n",
       "      <td>25.695652</td>\n",
       "      <td>102.217391</td>\n",
       "      <td>77.434783</td>\n",
       "      <td>580.260870</td>\n",
       "      <td>53.173913</td>\n",
       "      <td>5.913043</td>\n",
       "      <td>31224.565217</td>\n",
       "      <td>2</td>\n",
       "      <td>147.0</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>385.645691</td>\n",
       "      <td>71970.492537</td>\n",
       "      <td>71316.417910</td>\n",
       "      <td>6.492537</td>\n",
       "      <td>6.253731</td>\n",
       "      <td>95.955690</td>\n",
       "      <td>320.492537</td>\n",
       "      <td>2487.134328</td>\n",
       "      <td>5.089552</td>\n",
       "      <td>...</td>\n",
       "      <td>74.447761</td>\n",
       "      <td>221.746269</td>\n",
       "      <td>700.417910</td>\n",
       "      <td>1641.029851</td>\n",
       "      <td>145.626866</td>\n",
       "      <td>15.298507</td>\n",
       "      <td>72145.104478</td>\n",
       "      <td>1</td>\n",
       "      <td>67.0</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AR</td>\n",
       "      <td>334.750727</td>\n",
       "      <td>39321.746667</td>\n",
       "      <td>38579.933333</td>\n",
       "      <td>3.720000</td>\n",
       "      <td>3.720000</td>\n",
       "      <td>97.768183</td>\n",
       "      <td>183.066667</td>\n",
       "      <td>1443.693333</td>\n",
       "      <td>2.306667</td>\n",
       "      <td>...</td>\n",
       "      <td>30.693333</td>\n",
       "      <td>133.760000</td>\n",
       "      <td>430.666667</td>\n",
       "      <td>937.693333</td>\n",
       "      <td>75.360000</td>\n",
       "      <td>7.520000</td>\n",
       "      <td>39458.306667</td>\n",
       "      <td>5</td>\n",
       "      <td>75.0</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  state_abr  crime_rate_per_100000      CPOPARST      CPOPCRIM  AG_ARRST  \\\n",
       "0        AK             289.320957  20258.956522  20157.260870  1.347826   \n",
       "1        AL             385.645691  71970.492537  71316.417910  6.492537   \n",
       "2        AR             334.750727  39321.746667  38579.933333  3.720000   \n",
       "\n",
       "     AG_OFF      COVIND       INDEX      MODINDX    MURDER  ...    ROBBERY  \\\n",
       "0  1.434783  100.000000  147.913043   710.869565  0.913043  ...  25.695652   \n",
       "1  6.253731   95.955690  320.492537  2487.134328  5.089552  ...  74.447761   \n",
       "2  3.720000   97.768183  183.066667  1443.693333  2.306667  ...  30.693333   \n",
       "\n",
       "      AGASSLT     BURGLRY      LARCENY     MVTHEFT      ARSON    population  \\\n",
       "0  102.217391   77.434783   580.260870   53.173913   5.913043  31224.565217   \n",
       "1  221.746269  700.417910  1641.029851  145.626866  15.298507  72145.104478   \n",
       "2  133.760000  430.666667   937.693333   75.360000   7.520000  39458.306667   \n",
       "\n",
       "   FIPS_ST  FIPS_CTY  year  \n",
       "0        2     147.0  2016  \n",
       "1        1      67.0  2016  \n",
       "2        5      75.0  2016  \n",
       "\n",
       "[3 rows x 21 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df_final = crime_df2.agg({\n",
    "    'crime_rate_per_100000': 'mean',\n",
    "    'CPOPARST': 'mean',\n",
    "    'CPOPCRIM': 'mean',\n",
    "    'AG_ARRST': 'mean',\n",
    "    'AG_OFF': 'mean',\n",
    "    'COVIND': 'mean',\n",
    "    'INDEX': 'mean',\n",
    "    'MODINDX': 'mean',\n",
    "    'MURDER': 'mean',\n",
    "    'RAPE': 'mean',\n",
    "    'ROBBERY': 'mean',\n",
    "    'AGASSLT': 'mean',\n",
    "    'BURGLRY': 'mean',\n",
    "    'LARCENY': 'mean',\n",
    "    'MVTHEFT': 'mean',\n",
    "    'ARSON': 'mean',\n",
    "    'population': 'mean',\n",
    "    'FIPS_ST': 'mean',\n",
    "    'FIPS_CTY': 'mean',\n",
    "    'year': 'mean'\n",
    "}).reset_index()\n",
    "crime_df_final.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# need to replace state_name with state_id column \n",
    "crime_df_final_merged = pd.merge(crime_df_final, state_id_df, how='inner', on='state_abr')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_df_final_v2 = crime_df_final_merged.drop(labels=['state_abr', 'state_name', \n",
    "                                                        'lifeQualityRank', 'healthCareRank',\n",
    "                                                        'educationRank', 'economyRank'\n",
    "                                                       ], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['crime_rate_per_100000', 'CPOPARST', 'CPOPCRIM', 'AG_ARRST', 'AG_OFF',\n",
       "       'COVIND', 'INDEX', 'MODINDX', 'MURDER', 'RAPE', 'ROBBERY', 'AGASSLT',\n",
       "       'BURGLRY', 'LARCENY', 'MVTHEFT', 'ARSON', 'population', 'FIPS_ST',\n",
       "       'FIPS_CTY', 'year', 'state_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df_final_v2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_df_final_v2.to_csv(\"./Resources/Clean_data/crime.csv\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Public school data\n",
    "- Public School Data (CSV file) Data Source: https://www.kaggle.com/carlosaguayo/usa-public-schools"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# File path\n",
    "school_csv = 'Resources/Raw_data/public_schools.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>NCESID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>ZIP</th>\n",
       "      <th>ZIP4</th>\n",
       "      <th>...</th>\n",
       "      <th>VAL_METHOD</th>\n",
       "      <th>VAL_DATE</th>\n",
       "      <th>WEBSITE</th>\n",
       "      <th>LEVEL_</th>\n",
       "      <th>ENROLLMENT</th>\n",
       "      <th>ST_GRADE</th>\n",
       "      <th>END_GRADE</th>\n",
       "      <th>DISTRICTID</th>\n",
       "      <th>FT_TEACHER</th>\n",
       "      <th>SHELTER_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-81.050895</td>\n",
       "      <td>29.022271</td>\n",
       "      <td>2002</td>\n",
       "      <td>120192008041</td>\n",
       "      <td>SAMSULA ACADEMY</td>\n",
       "      <td>248 N SAMSULA DR</td>\n",
       "      <td>NEW SMYRNA</td>\n",
       "      <td>FL</td>\n",
       "      <td>32168</td>\n",
       "      <td>8762</td>\n",
       "      <td>...</td>\n",
       "      <td>IMAGERY</td>\n",
       "      <td>2014-05-20T00:00:00.000Z</td>\n",
       "      <td>http://nces.ed.gov/GLOBALLOCATOR/sch_info_popu...</td>\n",
       "      <td>1</td>\n",
       "      <td>222</td>\n",
       "      <td>KG</td>\n",
       "      <td>05</td>\n",
       "      <td>1201920</td>\n",
       "      <td>13</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-92.507288</td>\n",
       "      <td>31.180659</td>\n",
       "      <td>2003</td>\n",
       "      <td>220129002344</td>\n",
       "      <td>CAROLINE DORMON JUNIOR HIGH SCHOOL</td>\n",
       "      <td>8906 HWY 165 SOUTH</td>\n",
       "      <td>WOODWORTH</td>\n",
       "      <td>LA</td>\n",
       "      <td>71485</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>...</td>\n",
       "      <td>IMAGERY/OTHER</td>\n",
       "      <td>2015-06-19T00:00:00.000Z</td>\n",
       "      <td>http://nces.ed.gov/GLOBALLOCATOR/sch_info_popu...</td>\n",
       "      <td>1</td>\n",
       "      <td>312</td>\n",
       "      <td>PK</td>\n",
       "      <td>08</td>\n",
       "      <td>2201290</td>\n",
       "      <td>21</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-69.971880</td>\n",
       "      <td>43.908147</td>\n",
       "      <td>2004</td>\n",
       "      <td>230378023129</td>\n",
       "      <td>HARRIET BEECHER STOWE ELEMENTARY</td>\n",
       "      <td>44 MCKEEN STREET</td>\n",
       "      <td>BRUNSWICK</td>\n",
       "      <td>ME</td>\n",
       "      <td>4011</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>...</td>\n",
       "      <td>IMAGERY</td>\n",
       "      <td>2014-05-07T00:00:00.000Z</td>\n",
       "      <td>http://nces.ed.gov/GLOBALLOCATOR/sch_info_popu...</td>\n",
       "      <td>1</td>\n",
       "      <td>739</td>\n",
       "      <td>02</td>\n",
       "      <td>05</td>\n",
       "      <td>2303780</td>\n",
       "      <td>52</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-89.542799</td>\n",
       "      <td>32.728496</td>\n",
       "      <td>2005</td>\n",
       "      <td>280252001118</td>\n",
       "      <td>LEAKE CENTRAL ELEMENTARY SCHOOL</td>\n",
       "      <td>603 HWY. 16 WEST</td>\n",
       "      <td>CARTHAGE</td>\n",
       "      <td>MS</td>\n",
       "      <td>39051</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>...</td>\n",
       "      <td>IMAGERY/OTHER</td>\n",
       "      <td>2010-07-06T00:00:00.000Z</td>\n",
       "      <td>http://nces.ed.gov/GLOBALLOCATOR/sch_info_popu...</td>\n",
       "      <td>1</td>\n",
       "      <td>1159</td>\n",
       "      <td>PK</td>\n",
       "      <td>05</td>\n",
       "      <td>2802520</td>\n",
       "      <td>63</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-94.361775</td>\n",
       "      <td>39.364359</td>\n",
       "      <td>2006</td>\n",
       "      <td>291645000891</td>\n",
       "      <td>KEARNEY ELEM.</td>\n",
       "      <td>902 S JEFFERSON</td>\n",
       "      <td>KEARNEY</td>\n",
       "      <td>MO</td>\n",
       "      <td>64060</td>\n",
       "      <td>8518</td>\n",
       "      <td>...</td>\n",
       "      <td>IMAGERY/OTHER</td>\n",
       "      <td>2016-07-18T00:00:00.000Z</td>\n",
       "      <td>http://nces.ed.gov/GLOBALLOCATOR/sch_info_popu...</td>\n",
       "      <td>1</td>\n",
       "      <td>274</td>\n",
       "      <td>KG</td>\n",
       "      <td>05</td>\n",
       "      <td>2916450</td>\n",
       "      <td>22</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           X          Y  OBJECTID        NCESID  \\\n",
       "0 -81.050895  29.022271      2002  120192008041   \n",
       "1 -92.507288  31.180659      2003  220129002344   \n",
       "2 -69.971880  43.908147      2004  230378023129   \n",
       "3 -89.542799  32.728496      2005  280252001118   \n",
       "4 -94.361775  39.364359      2006  291645000891   \n",
       "\n",
       "                                 NAME             ADDRESS        CITY STATE  \\\n",
       "0                     SAMSULA ACADEMY    248 N SAMSULA DR  NEW SMYRNA    FL   \n",
       "1  CAROLINE DORMON JUNIOR HIGH SCHOOL  8906 HWY 165 SOUTH   WOODWORTH    LA   \n",
       "2    HARRIET BEECHER STOWE ELEMENTARY    44 MCKEEN STREET   BRUNSWICK    ME   \n",
       "3     LEAKE CENTRAL ELEMENTARY SCHOOL    603 HWY. 16 WEST    CARTHAGE    MS   \n",
       "4                       KEARNEY ELEM.     902 S JEFFERSON     KEARNEY    MO   \n",
       "\n",
       "     ZIP           ZIP4  ...     VAL_METHOD                  VAL_DATE  \\\n",
       "0  32168           8762  ...        IMAGERY  2014-05-20T00:00:00.000Z   \n",
       "1  71485  NOT AVAILABLE  ...  IMAGERY/OTHER  2015-06-19T00:00:00.000Z   \n",
       "2   4011  NOT AVAILABLE  ...        IMAGERY  2014-05-07T00:00:00.000Z   \n",
       "3  39051  NOT AVAILABLE  ...  IMAGERY/OTHER  2010-07-06T00:00:00.000Z   \n",
       "4  64060           8518  ...  IMAGERY/OTHER  2016-07-18T00:00:00.000Z   \n",
       "\n",
       "                                             WEBSITE  LEVEL_ ENROLLMENT  \\\n",
       "0  http://nces.ed.gov/GLOBALLOCATOR/sch_info_popu...       1        222   \n",
       "1  http://nces.ed.gov/GLOBALLOCATOR/sch_info_popu...       1        312   \n",
       "2  http://nces.ed.gov/GLOBALLOCATOR/sch_info_popu...       1        739   \n",
       "3  http://nces.ed.gov/GLOBALLOCATOR/sch_info_popu...       1       1159   \n",
       "4  http://nces.ed.gov/GLOBALLOCATOR/sch_info_popu...       1        274   \n",
       "\n",
       "   ST_GRADE END_GRADE  DISTRICTID  FT_TEACHER     SHELTER_ID  \n",
       "0        KG        05     1201920          13  NOT AVAILABLE  \n",
       "1        PK        08     2201290          21  NOT AVAILABLE  \n",
       "2        02        05     2303780          52  NOT AVAILABLE  \n",
       "3        PK        05     2802520          63  NOT AVAILABLE  \n",
       "4        KG        05     2916450          22  NOT AVAILABLE  \n",
       "\n",
       "[5 rows x 33 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read csv and store in to Pandas DataFrame\n",
    "schools_df = pd.read_csv(school_csv)\n",
    "schools_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# File path\n",
    "state_csv = 'Resources/Clean_data/state.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>state_id</th>\n",
       "      <th>state_name</th>\n",
       "      <th>state_abr</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>44</td>\n",
       "      <td>25</td>\n",
       "      <td>47</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>34</td>\n",
       "      <td>23</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>45</td>\n",
       "      <td>49</td>\n",
       "      <td>42</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "      <td>19</td>\n",
       "      <td>7</td>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_id  state_name state_abr  lifeQualityRank  healthCareRank  \\\n",
       "0         0     Alabama        AL               49              46   \n",
       "1         1      Alaska        AK               44              25   \n",
       "2         2     Arizona        AZ               34              23   \n",
       "3         3    Arkansas        AR               45              49   \n",
       "4         4  California        CA               19               7   \n",
       "\n",
       "   educationRank  economyRank  \n",
       "0             50           45  \n",
       "1             47           46  \n",
       "2             40           10  \n",
       "3             42           43  \n",
       "4             21            4  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read csv and store in to Pandas DataFrame\n",
    "state_df = pd.read_csv(state_csv)\n",
    "state_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>STATE</th>\n",
       "      <th>CITY</th>\n",
       "      <th>NAME</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>ZIP</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>DISTRICTID</th>\n",
       "      <th>ST_GRADE</th>\n",
       "      <th>END_GRADE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FL</td>\n",
       "      <td>NEW SMYRNA</td>\n",
       "      <td>SAMSULA ACADEMY</td>\n",
       "      <td>248 N SAMSULA DR</td>\n",
       "      <td>32168</td>\n",
       "      <td>-81.050895</td>\n",
       "      <td>29.022271</td>\n",
       "      <td>1201920</td>\n",
       "      <td>KG</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LA</td>\n",
       "      <td>WOODWORTH</td>\n",
       "      <td>CAROLINE DORMON JUNIOR HIGH SCHOOL</td>\n",
       "      <td>8906 HWY 165 SOUTH</td>\n",
       "      <td>71485</td>\n",
       "      <td>-92.507288</td>\n",
       "      <td>31.180659</td>\n",
       "      <td>2201290</td>\n",
       "      <td>PK</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ME</td>\n",
       "      <td>BRUNSWICK</td>\n",
       "      <td>HARRIET BEECHER STOWE ELEMENTARY</td>\n",
       "      <td>44 MCKEEN STREET</td>\n",
       "      <td>4011</td>\n",
       "      <td>-69.971880</td>\n",
       "      <td>43.908147</td>\n",
       "      <td>2303780</td>\n",
       "      <td>02</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MS</td>\n",
       "      <td>CARTHAGE</td>\n",
       "      <td>LEAKE CENTRAL ELEMENTARY SCHOOL</td>\n",
       "      <td>603 HWY. 16 WEST</td>\n",
       "      <td>39051</td>\n",
       "      <td>-89.542799</td>\n",
       "      <td>32.728496</td>\n",
       "      <td>2802520</td>\n",
       "      <td>PK</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MO</td>\n",
       "      <td>KEARNEY</td>\n",
       "      <td>KEARNEY ELEM.</td>\n",
       "      <td>902 S JEFFERSON</td>\n",
       "      <td>64060</td>\n",
       "      <td>-94.361775</td>\n",
       "      <td>39.364359</td>\n",
       "      <td>2916450</td>\n",
       "      <td>KG</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102365</th>\n",
       "      <td>MI</td>\n",
       "      <td>DETROIT</td>\n",
       "      <td>COVENANT HOUSE ACADEMY DETROIT - SOUTHWEST SITE</td>\n",
       "      <td>1450 25TH ST</td>\n",
       "      <td>48216</td>\n",
       "      <td>-83.085229</td>\n",
       "      <td>42.320632</td>\n",
       "      <td>2600322</td>\n",
       "      <td>09</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102366</th>\n",
       "      <td>MI</td>\n",
       "      <td>SOUTH ROCKWOOD</td>\n",
       "      <td>FRED W. RITTER ELEMENTARY SCHOOL</td>\n",
       "      <td>5650 CARLETON ROCKWOOD RD</td>\n",
       "      <td>48179</td>\n",
       "      <td>-83.272599</td>\n",
       "      <td>42.062038</td>\n",
       "      <td>2601980</td>\n",
       "      <td>KG</td>\n",
       "      <td>04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102367</th>\n",
       "      <td>MS</td>\n",
       "      <td>DIBERVILLE</td>\n",
       "      <td>DIBERVILLE ELEM</td>\n",
       "      <td>4540 BRODIE ROAD</td>\n",
       "      <td>39540</td>\n",
       "      <td>-88.914089</td>\n",
       "      <td>30.436478</td>\n",
       "      <td>2801770</td>\n",
       "      <td>KG</td>\n",
       "      <td>03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102368</th>\n",
       "      <td>MO</td>\n",
       "      <td>KANSAS CITY</td>\n",
       "      <td>DAVIDSON ELEM.</td>\n",
       "      <td>5100 N HIGHLAND</td>\n",
       "      <td>64118</td>\n",
       "      <td>-94.558365</td>\n",
       "      <td>39.187941</td>\n",
       "      <td>2922800</td>\n",
       "      <td>PK</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102369</th>\n",
       "      <td>MO</td>\n",
       "      <td>SPRINGFIELD</td>\n",
       "      <td>JUVENILE JUSTICE CTR.</td>\n",
       "      <td>1111 N ROBBERSON</td>\n",
       "      <td>65802</td>\n",
       "      <td>-93.291370</td>\n",
       "      <td>37.220353</td>\n",
       "      <td>2928860</td>\n",
       "      <td>07</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102370 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       STATE            CITY                                             NAME  \\\n",
       "0         FL      NEW SMYRNA                                  SAMSULA ACADEMY   \n",
       "1         LA       WOODWORTH               CAROLINE DORMON JUNIOR HIGH SCHOOL   \n",
       "2         ME       BRUNSWICK                 HARRIET BEECHER STOWE ELEMENTARY   \n",
       "3         MS        CARTHAGE                  LEAKE CENTRAL ELEMENTARY SCHOOL   \n",
       "4         MO         KEARNEY                                    KEARNEY ELEM.   \n",
       "...      ...             ...                                              ...   \n",
       "102365    MI         DETROIT  COVENANT HOUSE ACADEMY DETROIT - SOUTHWEST SITE   \n",
       "102366    MI  SOUTH ROCKWOOD                 FRED W. RITTER ELEMENTARY SCHOOL   \n",
       "102367    MS      DIBERVILLE                                  DIBERVILLE ELEM   \n",
       "102368    MO     KANSAS CITY                                   DAVIDSON ELEM.   \n",
       "102369    MO     SPRINGFIELD                            JUVENILE JUSTICE CTR.   \n",
       "\n",
       "                          ADDRESS    ZIP          X          Y  DISTRICTID  \\\n",
       "0                248 N SAMSULA DR  32168 -81.050895  29.022271     1201920   \n",
       "1              8906 HWY 165 SOUTH  71485 -92.507288  31.180659     2201290   \n",
       "2                44 MCKEEN STREET   4011 -69.971880  43.908147     2303780   \n",
       "3                603 HWY. 16 WEST  39051 -89.542799  32.728496     2802520   \n",
       "4                 902 S JEFFERSON  64060 -94.361775  39.364359     2916450   \n",
       "...                           ...    ...        ...        ...         ...   \n",
       "102365               1450 25TH ST  48216 -83.085229  42.320632     2600322   \n",
       "102366  5650 CARLETON ROCKWOOD RD  48179 -83.272599  42.062038     2601980   \n",
       "102367           4540 BRODIE ROAD  39540 -88.914089  30.436478     2801770   \n",
       "102368            5100 N HIGHLAND  64118 -94.558365  39.187941     2922800   \n",
       "102369           1111 N ROBBERSON  65802 -93.291370  37.220353     2928860   \n",
       "\n",
       "       ST_GRADE END_GRADE  \n",
       "0            KG        05  \n",
       "1            PK        08  \n",
       "2            02        05  \n",
       "3            PK        05  \n",
       "4            KG        05  \n",
       "...         ...       ...  \n",
       "102365       09        12  \n",
       "102366       KG        04  \n",
       "102367       KG        03  \n",
       "102368       PK        05  \n",
       "102369       07        12  \n",
       "\n",
       "[102370 rows x 10 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Cleaning up the DataFrame\n",
    "# Selecting the columns to be displayed\n",
    "clean_schools_df = schools_df[['STATE', 'CITY', 'NAME', 'ADDRESS', 'ZIP', 'X', 'Y', 'DISTRICTID', 'ST_GRADE', 'END_GRADE']]\n",
    "clean_schools_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>STATE</th>\n",
       "      <th>CITY</th>\n",
       "      <th>NAME</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>ZIP</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>DISTRICTID</th>\n",
       "      <th>ST_GRADE</th>\n",
       "      <th>END_GRADE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FL</td>\n",
       "      <td>NEW SMYRNA</td>\n",
       "      <td>SAMSULA ACADEMY</td>\n",
       "      <td>248 N SAMSULA DR</td>\n",
       "      <td>32168</td>\n",
       "      <td>-81.050895</td>\n",
       "      <td>29.022271</td>\n",
       "      <td>1201920</td>\n",
       "      <td>KG</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LA</td>\n",
       "      <td>WOODWORTH</td>\n",
       "      <td>CAROLINE DORMON JUNIOR HIGH SCHOOL</td>\n",
       "      <td>8906 HWY 165 SOUTH</td>\n",
       "      <td>71485</td>\n",
       "      <td>-92.507288</td>\n",
       "      <td>31.180659</td>\n",
       "      <td>2201290</td>\n",
       "      <td>PK</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ME</td>\n",
       "      <td>BRUNSWICK</td>\n",
       "      <td>HARRIET BEECHER STOWE ELEMENTARY</td>\n",
       "      <td>44 MCKEEN STREET</td>\n",
       "      <td>4011</td>\n",
       "      <td>-69.971880</td>\n",
       "      <td>43.908147</td>\n",
       "      <td>2303780</td>\n",
       "      <td>02</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MS</td>\n",
       "      <td>CARTHAGE</td>\n",
       "      <td>LEAKE CENTRAL ELEMENTARY SCHOOL</td>\n",
       "      <td>603 HWY. 16 WEST</td>\n",
       "      <td>39051</td>\n",
       "      <td>-89.542799</td>\n",
       "      <td>32.728496</td>\n",
       "      <td>2802520</td>\n",
       "      <td>PK</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MO</td>\n",
       "      <td>KEARNEY</td>\n",
       "      <td>KEARNEY ELEM.</td>\n",
       "      <td>902 S JEFFERSON</td>\n",
       "      <td>64060</td>\n",
       "      <td>-94.361775</td>\n",
       "      <td>39.364359</td>\n",
       "      <td>2916450</td>\n",
       "      <td>KG</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102365</th>\n",
       "      <td>MI</td>\n",
       "      <td>DETROIT</td>\n",
       "      <td>COVENANT HOUSE ACADEMY DETROIT - SOUTHWEST SITE</td>\n",
       "      <td>1450 25TH ST</td>\n",
       "      <td>48216</td>\n",
       "      <td>-83.085229</td>\n",
       "      <td>42.320632</td>\n",
       "      <td>2600322</td>\n",
       "      <td>09</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102366</th>\n",
       "      <td>MI</td>\n",
       "      <td>SOUTH ROCKWOOD</td>\n",
       "      <td>FRED W. RITTER ELEMENTARY SCHOOL</td>\n",
       "      <td>5650 CARLETON ROCKWOOD RD</td>\n",
       "      <td>48179</td>\n",
       "      <td>-83.272599</td>\n",
       "      <td>42.062038</td>\n",
       "      <td>2601980</td>\n",
       "      <td>KG</td>\n",
       "      <td>04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102367</th>\n",
       "      <td>MS</td>\n",
       "      <td>DIBERVILLE</td>\n",
       "      <td>DIBERVILLE ELEM</td>\n",
       "      <td>4540 BRODIE ROAD</td>\n",
       "      <td>39540</td>\n",
       "      <td>-88.914089</td>\n",
       "      <td>30.436478</td>\n",
       "      <td>2801770</td>\n",
       "      <td>KG</td>\n",
       "      <td>03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102368</th>\n",
       "      <td>MO</td>\n",
       "      <td>KANSAS CITY</td>\n",
       "      <td>DAVIDSON ELEM.</td>\n",
       "      <td>5100 N HIGHLAND</td>\n",
       "      <td>64118</td>\n",
       "      <td>-94.558365</td>\n",
       "      <td>39.187941</td>\n",
       "      <td>2922800</td>\n",
       "      <td>PK</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102369</th>\n",
       "      <td>MO</td>\n",
       "      <td>SPRINGFIELD</td>\n",
       "      <td>JUVENILE JUSTICE CTR.</td>\n",
       "      <td>1111 N ROBBERSON</td>\n",
       "      <td>65802</td>\n",
       "      <td>-93.291370</td>\n",
       "      <td>37.220353</td>\n",
       "      <td>2928860</td>\n",
       "      <td>07</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102368 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       STATE            CITY                                             NAME  \\\n",
       "0         FL      NEW SMYRNA                                  SAMSULA ACADEMY   \n",
       "1         LA       WOODWORTH               CAROLINE DORMON JUNIOR HIGH SCHOOL   \n",
       "2         ME       BRUNSWICK                 HARRIET BEECHER STOWE ELEMENTARY   \n",
       "3         MS        CARTHAGE                  LEAKE CENTRAL ELEMENTARY SCHOOL   \n",
       "4         MO         KEARNEY                                    KEARNEY ELEM.   \n",
       "...      ...             ...                                              ...   \n",
       "102365    MI         DETROIT  COVENANT HOUSE ACADEMY DETROIT - SOUTHWEST SITE   \n",
       "102366    MI  SOUTH ROCKWOOD                 FRED W. RITTER ELEMENTARY SCHOOL   \n",
       "102367    MS      DIBERVILLE                                  DIBERVILLE ELEM   \n",
       "102368    MO     KANSAS CITY                                   DAVIDSON ELEM.   \n",
       "102369    MO     SPRINGFIELD                            JUVENILE JUSTICE CTR.   \n",
       "\n",
       "                          ADDRESS    ZIP          X          Y  DISTRICTID  \\\n",
       "0                248 N SAMSULA DR  32168 -81.050895  29.022271     1201920   \n",
       "1              8906 HWY 165 SOUTH  71485 -92.507288  31.180659     2201290   \n",
       "2                44 MCKEEN STREET   4011 -69.971880  43.908147     2303780   \n",
       "3                603 HWY. 16 WEST  39051 -89.542799  32.728496     2802520   \n",
       "4                 902 S JEFFERSON  64060 -94.361775  39.364359     2916450   \n",
       "...                           ...    ...        ...        ...         ...   \n",
       "102365               1450 25TH ST  48216 -83.085229  42.320632     2600322   \n",
       "102366  5650 CARLETON ROCKWOOD RD  48179 -83.272599  42.062038     2601980   \n",
       "102367           4540 BRODIE ROAD  39540 -88.914089  30.436478     2801770   \n",
       "102368            5100 N HIGHLAND  64118 -94.558365  39.187941     2922800   \n",
       "102369           1111 N ROBBERSON  65802 -93.291370  37.220353     2928860   \n",
       "\n",
       "       ST_GRADE END_GRADE  \n",
       "0            KG        05  \n",
       "1            PK        08  \n",
       "2            02        05  \n",
       "3            PK        05  \n",
       "4            KG        05  \n",
       "...         ...       ...  \n",
       "102365       09        12  \n",
       "102366       KG        04  \n",
       "102367       KG        03  \n",
       "102368       PK        05  \n",
       "102369       07        12  \n",
       "\n",
       "[102368 rows x 10 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dropping null values\n",
    "clean_schools_df = clean_schools_df.dropna()\n",
    "clean_schools_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>state_abr</th>\n",
       "      <th>City</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>Address</th>\n",
       "      <th>Zip</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>DistrictID</th>\n",
       "      <th>StartGrade</th>\n",
       "      <th>EndGrade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FL</td>\n",
       "      <td>NEW SMYRNA</td>\n",
       "      <td>SAMSULA ACADEMY</td>\n",
       "      <td>248 N SAMSULA DR</td>\n",
       "      <td>32168</td>\n",
       "      <td>-81.050895</td>\n",
       "      <td>29.022271</td>\n",
       "      <td>1201920</td>\n",
       "      <td>KG</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LA</td>\n",
       "      <td>WOODWORTH</td>\n",
       "      <td>CAROLINE DORMON JUNIOR HIGH SCHOOL</td>\n",
       "      <td>8906 HWY 165 SOUTH</td>\n",
       "      <td>71485</td>\n",
       "      <td>-92.507288</td>\n",
       "      <td>31.180659</td>\n",
       "      <td>2201290</td>\n",
       "      <td>PK</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ME</td>\n",
       "      <td>BRUNSWICK</td>\n",
       "      <td>HARRIET BEECHER STOWE ELEMENTARY</td>\n",
       "      <td>44 MCKEEN STREET</td>\n",
       "      <td>4011</td>\n",
       "      <td>-69.971880</td>\n",
       "      <td>43.908147</td>\n",
       "      <td>2303780</td>\n",
       "      <td>02</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MS</td>\n",
       "      <td>CARTHAGE</td>\n",
       "      <td>LEAKE CENTRAL ELEMENTARY SCHOOL</td>\n",
       "      <td>603 HWY. 16 WEST</td>\n",
       "      <td>39051</td>\n",
       "      <td>-89.542799</td>\n",
       "      <td>32.728496</td>\n",
       "      <td>2802520</td>\n",
       "      <td>PK</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MO</td>\n",
       "      <td>KEARNEY</td>\n",
       "      <td>KEARNEY ELEM.</td>\n",
       "      <td>902 S JEFFERSON</td>\n",
       "      <td>64060</td>\n",
       "      <td>-94.361775</td>\n",
       "      <td>39.364359</td>\n",
       "      <td>2916450</td>\n",
       "      <td>KG</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state_abr        City                          SchoolName  \\\n",
       "0        FL  NEW SMYRNA                     SAMSULA ACADEMY   \n",
       "1        LA   WOODWORTH  CAROLINE DORMON JUNIOR HIGH SCHOOL   \n",
       "2        ME   BRUNSWICK    HARRIET BEECHER STOWE ELEMENTARY   \n",
       "3        MS    CARTHAGE     LEAKE CENTRAL ELEMENTARY SCHOOL   \n",
       "4        MO     KEARNEY                       KEARNEY ELEM.   \n",
       "\n",
       "              Address    Zip          X          Y  DistrictID StartGrade  \\\n",
       "0    248 N SAMSULA DR  32168 -81.050895  29.022271     1201920         KG   \n",
       "1  8906 HWY 165 SOUTH  71485 -92.507288  31.180659     2201290         PK   \n",
       "2    44 MCKEEN STREET   4011 -69.971880  43.908147     2303780         02   \n",
       "3    603 HWY. 16 WEST  39051 -89.542799  32.728496     2802520         PK   \n",
       "4     902 S JEFFERSON  64060 -94.361775  39.364359     2916450         KG   \n",
       "\n",
       "  EndGrade  \n",
       "0       05  \n",
       "1       08  \n",
       "2       05  \n",
       "3       05  \n",
       "4       05  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Renaiming Columns\n",
    "clean_schools_df = clean_schools_df.rename(columns={'STATE':'state_abr', 'CITY':'City', 'NAME':'SchoolName', 'ADDRESS':'Address',\n",
    "                                                    'ZIP':'Zip', 'DISTRICTID':'DistrictID', 'ST_GRADE':'StartGrade', 'END_GRADE':'EndGrade'})\n",
    "clean_schools_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>state_id</th>\n",
       "      <th>state_name</th>\n",
       "      <th>state_abr</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "      <th>City</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>Address</th>\n",
       "      <th>Zip</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>DistrictID</th>\n",
       "      <th>StartGrade</th>\n",
       "      <th>EndGrade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>LEEDS</td>\n",
       "      <td>LEEDS HIGH SCH</td>\n",
       "      <td>1500 GREENWAVE DRIVE</td>\n",
       "      <td>35094</td>\n",
       "      <td>-86.535935</td>\n",
       "      <td>33.548162</td>\n",
       "      <td>100011</td>\n",
       "      <td>09</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>CULLMAN</td>\n",
       "      <td>GOOD HOPE MIDDLE SCH</td>\n",
       "      <td>216 GOOD HOPE SCHOOL RD.</td>\n",
       "      <td>35057</td>\n",
       "      <td>-86.881057</td>\n",
       "      <td>34.102738</td>\n",
       "      <td>101020</td>\n",
       "      <td>06</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>GUNTERSVILLE</td>\n",
       "      <td>BRINDLEE MT MIDDLE SCH</td>\n",
       "      <td>1050 SCANT CITY ROAD</td>\n",
       "      <td>35976</td>\n",
       "      <td>-86.422337</td>\n",
       "      <td>34.377158</td>\n",
       "      <td>100006</td>\n",
       "      <td>06</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>SMITHS STATION</td>\n",
       "      <td>SMITH STATION FRESHMAN CTR</td>\n",
       "      <td>1150 LEE ROAD 298</td>\n",
       "      <td>36877</td>\n",
       "      <td>-85.099268</td>\n",
       "      <td>32.536590</td>\n",
       "      <td>102070</td>\n",
       "      <td>09</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>CLEVELAND ELEM SCH</td>\n",
       "      <td>115 STADIUM DR</td>\n",
       "      <td>35049</td>\n",
       "      <td>-86.572919</td>\n",
       "      <td>33.993417</td>\n",
       "      <td>100420</td>\n",
       "      <td>KG</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_id state_name state_abr  lifeQualityRank  healthCareRank  \\\n",
       "0       0.0    Alabama        AL             49.0            46.0   \n",
       "1       0.0    Alabama        AL             49.0            46.0   \n",
       "2       0.0    Alabama        AL             49.0            46.0   \n",
       "3       0.0    Alabama        AL             49.0            46.0   \n",
       "4       0.0    Alabama        AL             49.0            46.0   \n",
       "\n",
       "   educationRank  economyRank            City                  SchoolName  \\\n",
       "0           50.0         45.0           LEEDS              LEEDS HIGH SCH   \n",
       "1           50.0         45.0         CULLMAN        GOOD HOPE MIDDLE SCH   \n",
       "2           50.0         45.0    GUNTERSVILLE      BRINDLEE MT MIDDLE SCH   \n",
       "3           50.0         45.0  SMITHS STATION  SMITH STATION FRESHMAN CTR   \n",
       "4           50.0         45.0       CLEVELAND          CLEVELAND ELEM SCH   \n",
       "\n",
       "                    Address    Zip          X          Y  DistrictID  \\\n",
       "0      1500 GREENWAVE DRIVE  35094 -86.535935  33.548162      100011   \n",
       "1  216 GOOD HOPE SCHOOL RD.  35057 -86.881057  34.102738      101020   \n",
       "2      1050 SCANT CITY ROAD  35976 -86.422337  34.377158      100006   \n",
       "3         1150 LEE ROAD 298  36877 -85.099268  32.536590      102070   \n",
       "4            115 STADIUM DR  35049 -86.572919  33.993417      100420   \n",
       "\n",
       "  StartGrade EndGrade  \n",
       "0         09       12  \n",
       "1         06       08  \n",
       "2         06       08  \n",
       "3         09       09  \n",
       "4         KG       06  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge table for state_id\n",
    "\n",
    "public_schools_merge = pd.merge(left=state_df, right=clean_schools_df, how='right', left_on='state_abr', right_on='state_abr')\n",
    "public_schools_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>state_id</th>\n",
       "      <th>City</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>Address</th>\n",
       "      <th>Zip</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>DistrictID</th>\n",
       "      <th>StartGrade</th>\n",
       "      <th>EndGrade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>LEEDS</td>\n",
       "      <td>LEEDS HIGH SCH</td>\n",
       "      <td>1500 GREENWAVE DRIVE</td>\n",
       "      <td>35094</td>\n",
       "      <td>-86.535935</td>\n",
       "      <td>33.548162</td>\n",
       "      <td>100011</td>\n",
       "      <td>09</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>CULLMAN</td>\n",
       "      <td>GOOD HOPE MIDDLE SCH</td>\n",
       "      <td>216 GOOD HOPE SCHOOL RD.</td>\n",
       "      <td>35057</td>\n",
       "      <td>-86.881057</td>\n",
       "      <td>34.102738</td>\n",
       "      <td>101020</td>\n",
       "      <td>06</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>GUNTERSVILLE</td>\n",
       "      <td>BRINDLEE MT MIDDLE SCH</td>\n",
       "      <td>1050 SCANT CITY ROAD</td>\n",
       "      <td>35976</td>\n",
       "      <td>-86.422337</td>\n",
       "      <td>34.377158</td>\n",
       "      <td>100006</td>\n",
       "      <td>06</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>SMITHS STATION</td>\n",
       "      <td>SMITH STATION FRESHMAN CTR</td>\n",
       "      <td>1150 LEE ROAD 298</td>\n",
       "      <td>36877</td>\n",
       "      <td>-85.099268</td>\n",
       "      <td>32.536590</td>\n",
       "      <td>102070</td>\n",
       "      <td>09</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>CLEVELAND ELEM SCH</td>\n",
       "      <td>115 STADIUM DR</td>\n",
       "      <td>35049</td>\n",
       "      <td>-86.572919</td>\n",
       "      <td>33.993417</td>\n",
       "      <td>100420</td>\n",
       "      <td>KG</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102363</th>\n",
       "      <td>NaN</td>\n",
       "      <td>SAINT THOMAS</td>\n",
       "      <td>JOSEPH GOMEZ ELEMENTARY SCHOOL</td>\n",
       "      <td>142 ANNAS RETREAT</td>\n",
       "      <td>802</td>\n",
       "      <td>-64.919936</td>\n",
       "      <td>18.338171</td>\n",
       "      <td>7800030</td>\n",
       "      <td>KG</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102364</th>\n",
       "      <td>NaN</td>\n",
       "      <td>SAINT JOHN</td>\n",
       "      <td>JULIUS E SPRAUVE</td>\n",
       "      <td>14 18 ESTATE ENIGHED</td>\n",
       "      <td>831</td>\n",
       "      <td>-64.793916</td>\n",
       "      <td>18.330464</td>\n",
       "      <td>7800030</td>\n",
       "      <td>KG</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102365</th>\n",
       "      <td>NaN</td>\n",
       "      <td>SAINT THOMAS</td>\n",
       "      <td>LEONARD DOBER ELEMENTARY SCHOOL</td>\n",
       "      <td>9A 10B KRONPRINDSENS GADE</td>\n",
       "      <td>802</td>\n",
       "      <td>-64.925850</td>\n",
       "      <td>18.342520</td>\n",
       "      <td>7800030</td>\n",
       "      <td>04</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102366</th>\n",
       "      <td>NaN</td>\n",
       "      <td>SAINT CROIX</td>\n",
       "      <td>RICARDO RICHARDS ELEMENTARY SCCHOOL</td>\n",
       "      <td>491 BARREN SPOT</td>\n",
       "      <td>850</td>\n",
       "      <td>-64.760782</td>\n",
       "      <td>17.725168</td>\n",
       "      <td>7800002</td>\n",
       "      <td>KG</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102367</th>\n",
       "      <td>NaN</td>\n",
       "      <td>SAINT THOMAS</td>\n",
       "      <td>LOCKHART ELEMENTARY SCHOOL</td>\n",
       "      <td>41 ESTATE THOMAS</td>\n",
       "      <td>802</td>\n",
       "      <td>-64.917602</td>\n",
       "      <td>18.341950</td>\n",
       "      <td>7800030</td>\n",
       "      <td>KG</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102368 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        state_id            City                           SchoolName  \\\n",
       "0            0.0           LEEDS                       LEEDS HIGH SCH   \n",
       "1            0.0         CULLMAN                 GOOD HOPE MIDDLE SCH   \n",
       "2            0.0    GUNTERSVILLE               BRINDLEE MT MIDDLE SCH   \n",
       "3            0.0  SMITHS STATION           SMITH STATION FRESHMAN CTR   \n",
       "4            0.0       CLEVELAND                   CLEVELAND ELEM SCH   \n",
       "...          ...             ...                                  ...   \n",
       "102363       NaN    SAINT THOMAS       JOSEPH GOMEZ ELEMENTARY SCHOOL   \n",
       "102364       NaN      SAINT JOHN                     JULIUS E SPRAUVE   \n",
       "102365       NaN    SAINT THOMAS      LEONARD DOBER ELEMENTARY SCHOOL   \n",
       "102366       NaN     SAINT CROIX  RICARDO RICHARDS ELEMENTARY SCCHOOL   \n",
       "102367       NaN    SAINT THOMAS           LOCKHART ELEMENTARY SCHOOL   \n",
       "\n",
       "                          Address    Zip          X          Y  DistrictID  \\\n",
       "0            1500 GREENWAVE DRIVE  35094 -86.535935  33.548162      100011   \n",
       "1        216 GOOD HOPE SCHOOL RD.  35057 -86.881057  34.102738      101020   \n",
       "2            1050 SCANT CITY ROAD  35976 -86.422337  34.377158      100006   \n",
       "3               1150 LEE ROAD 298  36877 -85.099268  32.536590      102070   \n",
       "4                  115 STADIUM DR  35049 -86.572919  33.993417      100420   \n",
       "...                           ...    ...        ...        ...         ...   \n",
       "102363          142 ANNAS RETREAT    802 -64.919936  18.338171     7800030   \n",
       "102364       14 18 ESTATE ENIGHED    831 -64.793916  18.330464     7800030   \n",
       "102365  9A 10B KRONPRINDSENS GADE    802 -64.925850  18.342520     7800030   \n",
       "102366            491 BARREN SPOT    850 -64.760782  17.725168     7800002   \n",
       "102367           41 ESTATE THOMAS    802 -64.917602  18.341950     7800030   \n",
       "\n",
       "       StartGrade EndGrade  \n",
       "0              09       12  \n",
       "1              06       08  \n",
       "2              06       08  \n",
       "3              09       09  \n",
       "4              KG       06  \n",
       "...           ...      ...  \n",
       "102363         KG       06  \n",
       "102364         KG       08  \n",
       "102365         04       06  \n",
       "102366         KG       06  \n",
       "102367         KG       06  \n",
       "\n",
       "[102368 rows x 10 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Selecting the columns to be displayed\n",
    "public_schools = public_schools_merge[['state_id', 'City', 'SchoolName', 'Address', 'Zip', 'X', 'Y', 'DistrictID', 'StartGrade', 'EndGrade']]\n",
    "public_schools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>state_id</th>\n",
       "      <th>City</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>Address</th>\n",
       "      <th>Zip</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>DistrictID</th>\n",
       "      <th>StartGrade</th>\n",
       "      <th>EndGrade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>LEEDS</td>\n",
       "      <td>LEEDS HIGH SCH</td>\n",
       "      <td>1500 GREENWAVE DRIVE</td>\n",
       "      <td>35094</td>\n",
       "      <td>-86.535935</td>\n",
       "      <td>33.548162</td>\n",
       "      <td>100011</td>\n",
       "      <td>09</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>CULLMAN</td>\n",
       "      <td>GOOD HOPE MIDDLE SCH</td>\n",
       "      <td>216 GOOD HOPE SCHOOL RD.</td>\n",
       "      <td>35057</td>\n",
       "      <td>-86.881057</td>\n",
       "      <td>34.102738</td>\n",
       "      <td>101020</td>\n",
       "      <td>06</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>GUNTERSVILLE</td>\n",
       "      <td>BRINDLEE MT MIDDLE SCH</td>\n",
       "      <td>1050 SCANT CITY ROAD</td>\n",
       "      <td>35976</td>\n",
       "      <td>-86.422337</td>\n",
       "      <td>34.377158</td>\n",
       "      <td>100006</td>\n",
       "      <td>06</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>SMITHS STATION</td>\n",
       "      <td>SMITH STATION FRESHMAN CTR</td>\n",
       "      <td>1150 LEE ROAD 298</td>\n",
       "      <td>36877</td>\n",
       "      <td>-85.099268</td>\n",
       "      <td>32.536590</td>\n",
       "      <td>102070</td>\n",
       "      <td>09</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>CLEVELAND ELEM SCH</td>\n",
       "      <td>115 STADIUM DR</td>\n",
       "      <td>35049</td>\n",
       "      <td>-86.572919</td>\n",
       "      <td>33.993417</td>\n",
       "      <td>100420</td>\n",
       "      <td>KG</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100614</th>\n",
       "      <td>49.0</td>\n",
       "      <td>EVANSTON</td>\n",
       "      <td>HORIZON ALTERNATIVE SCHOOL</td>\n",
       "      <td>164 YELLOWCREEK RD</td>\n",
       "      <td>82931</td>\n",
       "      <td>-110.980300</td>\n",
       "      <td>41.259840</td>\n",
       "      <td>5602760</td>\n",
       "      <td>07</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100615</th>\n",
       "      <td>49.0</td>\n",
       "      <td>CHEYENNE</td>\n",
       "      <td>ANDERSON ELEMENTARY</td>\n",
       "      <td>2204 PLAIN VIEW RD</td>\n",
       "      <td>82009</td>\n",
       "      <td>-104.786858</td>\n",
       "      <td>41.170001</td>\n",
       "      <td>5601980</td>\n",
       "      <td>KG</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100616</th>\n",
       "      <td>49.0</td>\n",
       "      <td>EVANSTON</td>\n",
       "      <td>DAVIS MIDDLE SCHOOL</td>\n",
       "      <td>837 NO NAME ST</td>\n",
       "      <td>82931</td>\n",
       "      <td>-110.972237</td>\n",
       "      <td>41.260919</td>\n",
       "      <td>5602760</td>\n",
       "      <td>06</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100617</th>\n",
       "      <td>49.0</td>\n",
       "      <td>SHERIDAN</td>\n",
       "      <td>FT. MACKENZIE</td>\n",
       "      <td>620 LEWIS ST</td>\n",
       "      <td>82801</td>\n",
       "      <td>-106.965030</td>\n",
       "      <td>44.803526</td>\n",
       "      <td>5605695</td>\n",
       "      <td>09</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100618</th>\n",
       "      <td>49.0</td>\n",
       "      <td>POWDER RIVER</td>\n",
       "      <td>POWDER RIVER ELEMENTARY</td>\n",
       "      <td>35462 NOTCHES RD</td>\n",
       "      <td>82648</td>\n",
       "      <td>-106.988950</td>\n",
       "      <td>43.036013</td>\n",
       "      <td>5604510</td>\n",
       "      <td>KG</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100619 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        state_id            City                  SchoolName  \\\n",
       "0            0.0           LEEDS              LEEDS HIGH SCH   \n",
       "1            0.0         CULLMAN        GOOD HOPE MIDDLE SCH   \n",
       "2            0.0    GUNTERSVILLE      BRINDLEE MT MIDDLE SCH   \n",
       "3            0.0  SMITHS STATION  SMITH STATION FRESHMAN CTR   \n",
       "4            0.0       CLEVELAND          CLEVELAND ELEM SCH   \n",
       "...          ...             ...                         ...   \n",
       "100614      49.0        EVANSTON  HORIZON ALTERNATIVE SCHOOL   \n",
       "100615      49.0        CHEYENNE         ANDERSON ELEMENTARY   \n",
       "100616      49.0        EVANSTON         DAVIS MIDDLE SCHOOL   \n",
       "100617      49.0        SHERIDAN               FT. MACKENZIE   \n",
       "100618      49.0    POWDER RIVER     POWDER RIVER ELEMENTARY   \n",
       "\n",
       "                         Address    Zip           X          Y  DistrictID  \\\n",
       "0           1500 GREENWAVE DRIVE  35094  -86.535935  33.548162      100011   \n",
       "1       216 GOOD HOPE SCHOOL RD.  35057  -86.881057  34.102738      101020   \n",
       "2           1050 SCANT CITY ROAD  35976  -86.422337  34.377158      100006   \n",
       "3              1150 LEE ROAD 298  36877  -85.099268  32.536590      102070   \n",
       "4                 115 STADIUM DR  35049  -86.572919  33.993417      100420   \n",
       "...                          ...    ...         ...        ...         ...   \n",
       "100614        164 YELLOWCREEK RD  82931 -110.980300  41.259840     5602760   \n",
       "100615        2204 PLAIN VIEW RD  82009 -104.786858  41.170001     5601980   \n",
       "100616            837 NO NAME ST  82931 -110.972237  41.260919     5602760   \n",
       "100617              620 LEWIS ST  82801 -106.965030  44.803526     5605695   \n",
       "100618          35462 NOTCHES RD  82648 -106.988950  43.036013     5604510   \n",
       "\n",
       "       StartGrade EndGrade  \n",
       "0              09       12  \n",
       "1              06       08  \n",
       "2              06       08  \n",
       "3              09       09  \n",
       "4              KG       06  \n",
       "...           ...      ...  \n",
       "100614         07       12  \n",
       "100615         KG       06  \n",
       "100616         06       08  \n",
       "100617         09       12  \n",
       "100618         KG       06  \n",
       "\n",
       "[100619 rows x 10 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dropping null values\n",
    "public_schools = public_schools.dropna()\n",
    "public_schools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export Data\n",
    "public_schools.to_csv(r'Resources/Clean_data/public_schools.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hospital Data\n",
    "- Web scraping: Refer to scrapeRatings.ipynb notebook\n",
    "- Hospital Dataset (CSV file) Data Source: https://www.kaggle.com/carlosaguayo/usa-hospitals/version/1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>NAME</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>ZIP</th>\n",
       "      <th>OWNER</th>\n",
       "      <th>BEDS</th>\n",
       "      <th>RATING</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HOUSTON METHODIST SAN JACINTO HOSPITAL ALEXAND...</td>\n",
       "      <td>1700 JAMES BOWIE DRIVE</td>\n",
       "      <td>BAYTOWN</td>\n",
       "      <td>TX</td>\n",
       "      <td>77520</td>\n",
       "      <td>NON-PROFIT</td>\n",
       "      <td>182.0</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WOODS AT PARKSIDE,THE</td>\n",
       "      <td>349 OLDE RIDENOUR ROAD</td>\n",
       "      <td>COLUMBUS</td>\n",
       "      <td>OH</td>\n",
       "      <td>43230</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>50.0</td>\n",
       "      <td>4.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DAYTON CHILDREN'S HOSPITAL</td>\n",
       "      <td>ONE CHILDRENS PLAZA</td>\n",
       "      <td>DAYTON</td>\n",
       "      <td>OH</td>\n",
       "      <td>45404</td>\n",
       "      <td>NON-PROFIT</td>\n",
       "      <td>155.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>VIBRA HOSPITAL OF MAHONING VALLEY</td>\n",
       "      <td>8049 SOUTH AVENUE</td>\n",
       "      <td>BOARDMAN</td>\n",
       "      <td>OH</td>\n",
       "      <td>44512</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>45.0</td>\n",
       "      <td>4.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HAVEN BEHAVIORAL SENIOR CARE OF DAYTON</td>\n",
       "      <td>ONE ELIZABETH PLACE,E3 SUITE A</td>\n",
       "      <td>DAYTON</td>\n",
       "      <td>OH</td>\n",
       "      <td>45417</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>32.0</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                NAME  \\\n",
       "0  HOUSTON METHODIST SAN JACINTO HOSPITAL ALEXAND...   \n",
       "1                              WOODS AT PARKSIDE,THE   \n",
       "2                         DAYTON CHILDREN'S HOSPITAL   \n",
       "3                  VIBRA HOSPITAL OF MAHONING VALLEY   \n",
       "4             HAVEN BEHAVIORAL SENIOR CARE OF DAYTON   \n",
       "\n",
       "                          ADDRESS      CITY STATE    ZIP        OWNER   BEDS  \\\n",
       "0          1700 JAMES BOWIE DRIVE   BAYTOWN    TX  77520   NON-PROFIT  182.0   \n",
       "1          349 OLDE RIDENOUR ROAD  COLUMBUS    OH  43230  PROPRIETARY   50.0   \n",
       "2             ONE CHILDRENS PLAZA    DAYTON    OH  45404   NON-PROFIT  155.0   \n",
       "3               8049 SOUTH AVENUE  BOARDMAN    OH  44512  PROPRIETARY   45.0   \n",
       "4  ONE ELIZABETH PLACE,E3 SUITE A    DAYTON    OH  45417  PROPRIETARY   32.0   \n",
       "\n",
       "   RATING  \n",
       "0     4.5  \n",
       "1     4.1  \n",
       "2     4.0  \n",
       "3     4.6  \n",
       "4     2.5  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('Resources/Clean_data/hospitalRatings.csv')\n",
    "del df['Unnamed: 0']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>ID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>ZIP</th>\n",
       "      <th>ZIP4</th>\n",
       "      <th>...</th>\n",
       "      <th>VAL_DATE</th>\n",
       "      <th>WEBSITE</th>\n",
       "      <th>STATE_ID</th>\n",
       "      <th>ALT_NAME</th>\n",
       "      <th>ST_FIPS</th>\n",
       "      <th>OWNER</th>\n",
       "      <th>TTL_STAFF</th>\n",
       "      <th>BEDS</th>\n",
       "      <th>TRAUMA</th>\n",
       "      <th>HELIPAD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-94.945477</td>\n",
       "      <td>29.747620</td>\n",
       "      <td>8497</td>\n",
       "      <td>76777520</td>\n",
       "      <td>HOUSTON METHODIST SAN JACINTO HOSPITAL ALEXAND...</td>\n",
       "      <td>1700 JAMES BOWIE DRIVE</td>\n",
       "      <td>BAYTOWN</td>\n",
       "      <td>TX</td>\n",
       "      <td>77520</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-12-18T00:00:00.000Z</td>\n",
       "      <td>http://www.houstonmethodist.org/locations/san-...</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>48.0</td>\n",
       "      <td>NON-PROFIT</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>182.0</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-82.881843</td>\n",
       "      <td>40.027143</td>\n",
       "      <td>8498</td>\n",
       "      <td>129043230</td>\n",
       "      <td>WOODS AT PARKSIDE,THE</td>\n",
       "      <td>349 OLDE RIDENOUR ROAD</td>\n",
       "      <td>COLUMBUS</td>\n",
       "      <td>OH</td>\n",
       "      <td>43230</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-04-26T00:00:00.000Z</td>\n",
       "      <td>http://www.thewoodsatparkside.com/</td>\n",
       "      <td>1815</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>39.0</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-84.168027</td>\n",
       "      <td>39.774242</td>\n",
       "      <td>8499</td>\n",
       "      <td>130045404</td>\n",
       "      <td>DAYTON CHILDREN'S HOSPITAL</td>\n",
       "      <td>ONE CHILDRENS PLAZA</td>\n",
       "      <td>DAYTON</td>\n",
       "      <td>OH</td>\n",
       "      <td>45404</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-04-26T00:00:00.000Z</td>\n",
       "      <td>http://www.childrensdayton.org/cms/home/index....</td>\n",
       "      <td>1411</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>39.0</td>\n",
       "      <td>NON-PROFIT</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>PEDIATRIC LEVEL II</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-80.632972</td>\n",
       "      <td>41.005169</td>\n",
       "      <td>8500</td>\n",
       "      <td>128844512</td>\n",
       "      <td>VIBRA HOSPITAL OF MAHONING VALLEY</td>\n",
       "      <td>8049 SOUTH AVENUE</td>\n",
       "      <td>BOARDMAN</td>\n",
       "      <td>OH</td>\n",
       "      <td>44512</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-04-26T00:00:00.000Z</td>\n",
       "      <td>http://www.mahoningvalleyhospital.com/</td>\n",
       "      <td>1428</td>\n",
       "      <td>MAHONING VALLEY HOSPITAL BOARDMAN CAMPUS</td>\n",
       "      <td>39.0</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-84.199398</td>\n",
       "      <td>39.747740</td>\n",
       "      <td>8501</td>\n",
       "      <td>129845417</td>\n",
       "      <td>HAVEN BEHAVIORAL SENIOR CARE OF DAYTON</td>\n",
       "      <td>ONE ELIZABETH PLACE,E3 SUITE A</td>\n",
       "      <td>DAYTON</td>\n",
       "      <td>OH</td>\n",
       "      <td>45417</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-04-26T00:00:00.000Z</td>\n",
       "      <td>https://dayton.havenbehavioral.com/</td>\n",
       "      <td>1506</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>39.0</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "      <td>NOT AVAILABLE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           X          Y  OBJECTID         ID  \\\n",
       "0 -94.945477  29.747620      8497   76777520   \n",
       "1 -82.881843  40.027143      8498  129043230   \n",
       "2 -84.168027  39.774242      8499  130045404   \n",
       "3 -80.632972  41.005169      8500  128844512   \n",
       "4 -84.199398  39.747740      8501  129845417   \n",
       "\n",
       "                                                NAME  \\\n",
       "0  HOUSTON METHODIST SAN JACINTO HOSPITAL ALEXAND...   \n",
       "1                              WOODS AT PARKSIDE,THE   \n",
       "2                         DAYTON CHILDREN'S HOSPITAL   \n",
       "3                  VIBRA HOSPITAL OF MAHONING VALLEY   \n",
       "4             HAVEN BEHAVIORAL SENIOR CARE OF DAYTON   \n",
       "\n",
       "                          ADDRESS      CITY STATE    ZIP           ZIP4  ...  \\\n",
       "0          1700 JAMES BOWIE DRIVE   BAYTOWN    TX  77520  NOT AVAILABLE  ...   \n",
       "1          349 OLDE RIDENOUR ROAD  COLUMBUS    OH  43230  NOT AVAILABLE  ...   \n",
       "2             ONE CHILDRENS PLAZA    DAYTON    OH  45404  NOT AVAILABLE  ...   \n",
       "3               8049 SOUTH AVENUE  BOARDMAN    OH  44512  NOT AVAILABLE  ...   \n",
       "4  ONE ELIZABETH PLACE,E3 SUITE A    DAYTON    OH  45417  NOT AVAILABLE  ...   \n",
       "\n",
       "                   VAL_DATE  \\\n",
       "0  2017-12-18T00:00:00.000Z   \n",
       "1  2018-04-26T00:00:00.000Z   \n",
       "2  2018-04-26T00:00:00.000Z   \n",
       "3  2018-04-26T00:00:00.000Z   \n",
       "4  2018-04-26T00:00:00.000Z   \n",
       "\n",
       "                                             WEBSITE       STATE_ID  \\\n",
       "0  http://www.houstonmethodist.org/locations/san-...  NOT AVAILABLE   \n",
       "1                 http://www.thewoodsatparkside.com/           1815   \n",
       "2  http://www.childrensdayton.org/cms/home/index....           1411   \n",
       "3             http://www.mahoningvalleyhospital.com/           1428   \n",
       "4                https://dayton.havenbehavioral.com/           1506   \n",
       "\n",
       "                                   ALT_NAME ST_FIPS        OWNER TTL_STAFF  \\\n",
       "0                             NOT AVAILABLE    48.0   NON-PROFIT    -999.0   \n",
       "1                             NOT AVAILABLE    39.0  PROPRIETARY    -999.0   \n",
       "2                             NOT AVAILABLE    39.0   NON-PROFIT    -999.0   \n",
       "3  MAHONING VALLEY HOSPITAL BOARDMAN CAMPUS    39.0  PROPRIETARY    -999.0   \n",
       "4                             NOT AVAILABLE    39.0  PROPRIETARY    -999.0   \n",
       "\n",
       "    BEDS              TRAUMA        HELIPAD  \n",
       "0  182.0       NOT AVAILABLE              Y  \n",
       "1   50.0       NOT AVAILABLE  NOT AVAILABLE  \n",
       "2  155.0  PEDIATRIC LEVEL II              Y  \n",
       "3   45.0       NOT AVAILABLE  NOT AVAILABLE  \n",
       "4   32.0       NOT AVAILABLE  NOT AVAILABLE  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Looks like we're missing the website column. I must have deleted it by mistake in the original cleanup\n",
    "# Let's grab it from the original data\n",
    "hosp = pd.read_csv('Resources/Raw_data/Hospitals.csv')\n",
    "hosp.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>NAME</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>ZIP</th>\n",
       "      <th>OWNER</th>\n",
       "      <th>BEDS</th>\n",
       "      <th>RATING</th>\n",
       "      <th>WEBSITE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HOUSTON METHODIST SAN JACINTO HOSPITAL ALEXAND...</td>\n",
       "      <td>1700 JAMES BOWIE DRIVE</td>\n",
       "      <td>BAYTOWN</td>\n",
       "      <td>TX</td>\n",
       "      <td>77520</td>\n",
       "      <td>NON-PROFIT</td>\n",
       "      <td>182.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>http://www.houstonmethodist.org/locations/san-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WOODS AT PARKSIDE,THE</td>\n",
       "      <td>349 OLDE RIDENOUR ROAD</td>\n",
       "      <td>COLUMBUS</td>\n",
       "      <td>OH</td>\n",
       "      <td>43230</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>50.0</td>\n",
       "      <td>4.1</td>\n",
       "      <td>http://www.thewoodsatparkside.com/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DAYTON CHILDREN'S HOSPITAL</td>\n",
       "      <td>ONE CHILDRENS PLAZA</td>\n",
       "      <td>DAYTON</td>\n",
       "      <td>OH</td>\n",
       "      <td>45404</td>\n",
       "      <td>NON-PROFIT</td>\n",
       "      <td>155.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>http://www.childrensdayton.org/cms/home/index....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>VIBRA HOSPITAL OF MAHONING VALLEY</td>\n",
       "      <td>8049 SOUTH AVENUE</td>\n",
       "      <td>BOARDMAN</td>\n",
       "      <td>OH</td>\n",
       "      <td>44512</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>45.0</td>\n",
       "      <td>4.6</td>\n",
       "      <td>http://www.mahoningvalleyhospital.com/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HAVEN BEHAVIORAL SENIOR CARE OF DAYTON</td>\n",
       "      <td>ONE ELIZABETH PLACE,E3 SUITE A</td>\n",
       "      <td>DAYTON</td>\n",
       "      <td>OH</td>\n",
       "      <td>45417</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>32.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>https://dayton.havenbehavioral.com/</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                NAME  \\\n",
       "0  HOUSTON METHODIST SAN JACINTO HOSPITAL ALEXAND...   \n",
       "1                              WOODS AT PARKSIDE,THE   \n",
       "2                         DAYTON CHILDREN'S HOSPITAL   \n",
       "3                  VIBRA HOSPITAL OF MAHONING VALLEY   \n",
       "4             HAVEN BEHAVIORAL SENIOR CARE OF DAYTON   \n",
       "\n",
       "                          ADDRESS      CITY STATE    ZIP        OWNER   BEDS  \\\n",
       "0          1700 JAMES BOWIE DRIVE   BAYTOWN    TX  77520   NON-PROFIT  182.0   \n",
       "1          349 OLDE RIDENOUR ROAD  COLUMBUS    OH  43230  PROPRIETARY   50.0   \n",
       "2             ONE CHILDRENS PLAZA    DAYTON    OH  45404   NON-PROFIT  155.0   \n",
       "3               8049 SOUTH AVENUE  BOARDMAN    OH  44512  PROPRIETARY   45.0   \n",
       "4  ONE ELIZABETH PLACE,E3 SUITE A    DAYTON    OH  45417  PROPRIETARY   32.0   \n",
       "\n",
       "   RATING                                            WEBSITE  \n",
       "0     4.5  http://www.houstonmethodist.org/locations/san-...  \n",
       "1     4.1                 http://www.thewoodsatparkside.com/  \n",
       "2     4.0  http://www.childrensdayton.org/cms/home/index....  \n",
       "3     4.6             http://www.mahoningvalleyhospital.com/  \n",
       "4     2.5                https://dayton.havenbehavioral.com/  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.merge(df, hosp[['NAME', 'WEBSITE']], on='NAME')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>state_id</th>\n",
       "      <th>NAME</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>CITY</th>\n",
       "      <th>ZIP</th>\n",
       "      <th>OWNER</th>\n",
       "      <th>BEDS</th>\n",
       "      <th>RATING</th>\n",
       "      <th>WEBSITE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>42</td>\n",
       "      <td>HOUSTON METHODIST SAN JACINTO HOSPITAL ALEXAND...</td>\n",
       "      <td>1700 JAMES BOWIE DRIVE</td>\n",
       "      <td>BAYTOWN</td>\n",
       "      <td>77520</td>\n",
       "      <td>NON-PROFIT</td>\n",
       "      <td>182.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>http://www.houstonmethodist.org/locations/san-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>42</td>\n",
       "      <td>PROVIDENCE HOSPITAL OF NORTH HOUSTON LLC</td>\n",
       "      <td>16750 RED OAK DR</td>\n",
       "      <td>HOUSTON</td>\n",
       "      <td>77090</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>16.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>http://phnh.net/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>42</td>\n",
       "      <td>MCCALLEN MEDICAL CENTER</td>\n",
       "      <td>301 WEST EXPRESSWAY 83</td>\n",
       "      <td>MCALLEN</td>\n",
       "      <td>78503</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>441.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>http://www.mcallenmedicalcenter.com/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>42</td>\n",
       "      <td>FAITH COMMUNITY HOSPITAL</td>\n",
       "      <td>215 CHISHOLM TRAIL</td>\n",
       "      <td>JACKSBORO</td>\n",
       "      <td>76458</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>17.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>https://www.fchtexas.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>42</td>\n",
       "      <td>FAITH COMMUNITY HOSPITAL</td>\n",
       "      <td>215 CHISHOLM TRAIL</td>\n",
       "      <td>JACKSBORO</td>\n",
       "      <td>76458</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>17.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>http://www.faithcommunityhospital.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8403</th>\n",
       "      <td>7</td>\n",
       "      <td>ROCKFORD CENTER</td>\n",
       "      <td>100 ROCKFORD DR</td>\n",
       "      <td>NEWARK</td>\n",
       "      <td>19713</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>92.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>http://www.rockfordcenter.com/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8404</th>\n",
       "      <td>7</td>\n",
       "      <td>SELECT SPECIALTY HOSPITAL OF WILMINGTON</td>\n",
       "      <td>701 N CLAYTON ST</td>\n",
       "      <td>WILMINGTON</td>\n",
       "      <td>19805</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>35.0</td>\n",
       "      <td>4.2</td>\n",
       "      <td>http://wilmington.selectspecialtyhospitals.com/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8405</th>\n",
       "      <td>7</td>\n",
       "      <td>BEEBE MEDICAL CENTER</td>\n",
       "      <td>424 SAVANNAH RD</td>\n",
       "      <td>LEWES</td>\n",
       "      <td>19958</td>\n",
       "      <td>NON-PROFIT</td>\n",
       "      <td>210.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>http://www.beebemed.org/index.cfm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8406</th>\n",
       "      <td>7</td>\n",
       "      <td>BAYHEALTH KENT GENERAL HOSPITAL</td>\n",
       "      <td>640 S STATE ST</td>\n",
       "      <td>DOVER</td>\n",
       "      <td>19901</td>\n",
       "      <td>NON-PROFIT</td>\n",
       "      <td>236.0</td>\n",
       "      <td>2.6</td>\n",
       "      <td>http://www.bayhealth.org/Home/Facilities/tabid...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8407</th>\n",
       "      <td>7</td>\n",
       "      <td>DOVER BEHAVIORAL HEALTH SYSTEM</td>\n",
       "      <td>725 HORSEPOND RD</td>\n",
       "      <td>DOVER</td>\n",
       "      <td>19901</td>\n",
       "      <td>PROPRIETARY</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>http://www.doverbehavioral.com/</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8408 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      state_id                                               NAME  \\\n",
       "0           42  HOUSTON METHODIST SAN JACINTO HOSPITAL ALEXAND...   \n",
       "1           42           PROVIDENCE HOSPITAL OF NORTH HOUSTON LLC   \n",
       "2           42                            MCCALLEN MEDICAL CENTER   \n",
       "3           42                           FAITH COMMUNITY HOSPITAL   \n",
       "4           42                           FAITH COMMUNITY HOSPITAL   \n",
       "...        ...                                                ...   \n",
       "8403         7                                    ROCKFORD CENTER   \n",
       "8404         7            SELECT SPECIALTY HOSPITAL OF WILMINGTON   \n",
       "8405         7                               BEEBE MEDICAL CENTER   \n",
       "8406         7                    BAYHEALTH KENT GENERAL HOSPITAL   \n",
       "8407         7                     DOVER BEHAVIORAL HEALTH SYSTEM   \n",
       "\n",
       "                     ADDRESS        CITY    ZIP        OWNER   BEDS  RATING  \\\n",
       "0     1700 JAMES BOWIE DRIVE     BAYTOWN  77520   NON-PROFIT  182.0     4.5   \n",
       "1           16750 RED OAK DR     HOUSTON  77090  PROPRIETARY   16.0     4.0   \n",
       "2     301 WEST EXPRESSWAY 83     MCALLEN  78503  PROPRIETARY  441.0     4.0   \n",
       "3         215 CHISHOLM TRAIL   JACKSBORO  76458  PROPRIETARY   17.0     3.6   \n",
       "4         215 CHISHOLM TRAIL   JACKSBORO  76458  PROPRIETARY   17.0     3.6   \n",
       "...                      ...         ...    ...          ...    ...     ...   \n",
       "8403         100 ROCKFORD DR      NEWARK  19713  PROPRIETARY   92.0     2.0   \n",
       "8404        701 N CLAYTON ST  WILMINGTON  19805  PROPRIETARY   35.0     4.2   \n",
       "8405         424 SAVANNAH RD       LEWES  19958   NON-PROFIT  210.0     2.8   \n",
       "8406          640 S STATE ST       DOVER  19901   NON-PROFIT  236.0     2.6   \n",
       "8407        725 HORSEPOND RD       DOVER  19901  PROPRIETARY   52.0     1.8   \n",
       "\n",
       "                                                WEBSITE  \n",
       "0     http://www.houstonmethodist.org/locations/san-...  \n",
       "1                                      http://phnh.net/  \n",
       "2                  http://www.mcallenmedicalcenter.com/  \n",
       "3                              https://www.fchtexas.com  \n",
       "4                 http://www.faithcommunityhospital.com  \n",
       "...                                                 ...  \n",
       "8403                     http://www.rockfordcenter.com/  \n",
       "8404    http://wilmington.selectspecialtyhospitals.com/  \n",
       "8405                  http://www.beebemed.org/index.cfm  \n",
       "8406  http://www.bayhealth.org/Home/Facilities/tabid...  \n",
       "8407                    http://www.doverbehavioral.com/  \n",
       "\n",
       "[8408 rows x 9 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join with state_id table\n",
    "states = pd.read_csv('Resources/Clean_data/state.csv')\n",
    "df = pd.merge(df, states[['state_id', 'state_abr']], left_on='STATE', right_on = 'state_abr')[['state_id', 'NAME', 'ADDRESS', 'CITY', 'ZIP', 'OWNER', 'BEDS', 'RATING', 'WEBSITE']]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save data\n",
    "df.to_csv('Resources/Clean_data/hospitals.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unemployment Data\n",
    "- USA Unemployment rate Dataset (CSV file) Data Source: https://www.kaggle.com/carlosaguayo/2018-unemployment-rate-by-county/version/1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>Series ID</th>\n",
       "      <th>Region Name</th>\n",
       "      <th>Region Code</th>\n",
       "      <th>2000</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>...</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SCABBE1URN</td>\n",
       "      <td>Abbeville County, SC</td>\n",
       "      <td>45001</td>\n",
       "      <td>3.8</td>\n",
       "      <td>6.6</td>\n",
       "      <td>7.7</td>\n",
       "      <td>9.1</td>\n",
       "      <td>8.2</td>\n",
       "      <td>7.7</td>\n",
       "      <td>8.8</td>\n",
       "      <td>...</td>\n",
       "      <td>14.2</td>\n",
       "      <td>13.6</td>\n",
       "      <td>12.5</td>\n",
       "      <td>10.6</td>\n",
       "      <td>9.5</td>\n",
       "      <td>7.7</td>\n",
       "      <td>6.9</td>\n",
       "      <td>5.5</td>\n",
       "      <td>4.6</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LAACAD0URN</td>\n",
       "      <td>Acadia Parish, LA</td>\n",
       "      <td>22001</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.3</td>\n",
       "      <td>6.8</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.1</td>\n",
       "      <td>3.6</td>\n",
       "      <td>...</td>\n",
       "      <td>6.5</td>\n",
       "      <td>7.3</td>\n",
       "      <td>6.7</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.9</td>\n",
       "      <td>7.4</td>\n",
       "      <td>5.9</td>\n",
       "      <td>5.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>VAACCO1URN</td>\n",
       "      <td>Accomack County, VA</td>\n",
       "      <td>51001</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.4</td>\n",
       "      <td>4.2</td>\n",
       "      <td>4.4</td>\n",
       "      <td>4.7</td>\n",
       "      <td>4.7</td>\n",
       "      <td>4.3</td>\n",
       "      <td>...</td>\n",
       "      <td>6.5</td>\n",
       "      <td>7.9</td>\n",
       "      <td>8.4</td>\n",
       "      <td>7.9</td>\n",
       "      <td>7.2</td>\n",
       "      <td>6.7</td>\n",
       "      <td>5.5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IDADAC1URN</td>\n",
       "      <td>Ada County, ID</td>\n",
       "      <td>16001</td>\n",
       "      <td>3.4</td>\n",
       "      <td>3.9</td>\n",
       "      <td>4.7</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.1</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.7</td>\n",
       "      <td>...</td>\n",
       "      <td>8.9</td>\n",
       "      <td>8.3</td>\n",
       "      <td>7.3</td>\n",
       "      <td>6.2</td>\n",
       "      <td>5.2</td>\n",
       "      <td>4.2</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.8</td>\n",
       "      <td>2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>OKADAI1URN</td>\n",
       "      <td>Adair County, OK</td>\n",
       "      <td>40001</td>\n",
       "      <td>3.5</td>\n",
       "      <td>4.1</td>\n",
       "      <td>4.8</td>\n",
       "      <td>7.8</td>\n",
       "      <td>6.2</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.4</td>\n",
       "      <td>...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.5</td>\n",
       "      <td>9.2</td>\n",
       "      <td>9.1</td>\n",
       "      <td>9.8</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.1</td>\n",
       "      <td>5.9</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Series ID           Region Name  Region Code  2000  2001  2002  2003  \\\n",
       "0  SCABBE1URN  Abbeville County, SC        45001   3.8   6.6   7.7   9.1   \n",
       "1  LAACAD0URN     Acadia Parish, LA        22001   5.8   6.0   6.3   6.8   \n",
       "2  VAACCO1URN   Accomack County, VA        51001   3.0   3.4   4.2   4.4   \n",
       "3  IDADAC1URN        Ada County, ID        16001   3.4   3.9   4.7   4.9   \n",
       "4  OKADAI1URN      Adair County, OK        40001   3.5   4.1   4.8   7.8   \n",
       "\n",
       "   2004  2005  2006  ...  2009  2010  2011  2012  2013  2014  2015  2016  \\\n",
       "0   8.2   7.7   8.8  ...  14.2  13.6  12.5  10.6   9.5   7.7   6.9   5.5   \n",
       "1   5.8   6.1   3.6  ...   6.5   7.3   6.7   6.0   6.0   5.8   6.9   7.4   \n",
       "2   4.7   4.7   4.3  ...   6.5   7.9   8.4   7.9   7.2   6.7   5.5   5.0   \n",
       "3   4.1   3.3   2.7  ...   8.9   8.3   7.3   6.2   5.2   4.2   3.7   3.3   \n",
       "4   6.2   4.8   4.4  ...   8.0  10.5   9.2   9.1   9.8   8.0   6.1   5.9   \n",
       "\n",
       "   2017  2018  \n",
       "0   4.6   4.2  \n",
       "1   5.9   5.4  \n",
       "2   4.9   4.1  \n",
       "3   2.8   2.4  \n",
       "4   5.0   4.4  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Grab unemployment csv\n",
    "unemployment_df = pd.read_csv(\"./Resources/Raw_data/GeoFRED_Unemployment_Rate_by_County_Percent.csv\")\n",
    "unemployment_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>Series ID</th>\n",
       "      <th>Region Name</th>\n",
       "      <th>Region Code</th>\n",
       "      <th>2000</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>...</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SCABBE1URN</td>\n",
       "      <td>Abbeville County</td>\n",
       "      <td>45001</td>\n",
       "      <td>3.8</td>\n",
       "      <td>6.6</td>\n",
       "      <td>7.7</td>\n",
       "      <td>9.1</td>\n",
       "      <td>8.2</td>\n",
       "      <td>7.7</td>\n",
       "      <td>8.8</td>\n",
       "      <td>...</td>\n",
       "      <td>13.6</td>\n",
       "      <td>12.5</td>\n",
       "      <td>10.6</td>\n",
       "      <td>9.5</td>\n",
       "      <td>7.7</td>\n",
       "      <td>6.9</td>\n",
       "      <td>5.5</td>\n",
       "      <td>4.6</td>\n",
       "      <td>4.2</td>\n",
       "      <td>SC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LAACAD0URN</td>\n",
       "      <td>Acadia Parish</td>\n",
       "      <td>22001</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.3</td>\n",
       "      <td>6.8</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.1</td>\n",
       "      <td>3.6</td>\n",
       "      <td>...</td>\n",
       "      <td>7.3</td>\n",
       "      <td>6.7</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.9</td>\n",
       "      <td>7.4</td>\n",
       "      <td>5.9</td>\n",
       "      <td>5.4</td>\n",
       "      <td>LA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>VAACCO1URN</td>\n",
       "      <td>Accomack County</td>\n",
       "      <td>51001</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.4</td>\n",
       "      <td>4.2</td>\n",
       "      <td>4.4</td>\n",
       "      <td>4.7</td>\n",
       "      <td>4.7</td>\n",
       "      <td>4.3</td>\n",
       "      <td>...</td>\n",
       "      <td>7.9</td>\n",
       "      <td>8.4</td>\n",
       "      <td>7.9</td>\n",
       "      <td>7.2</td>\n",
       "      <td>6.7</td>\n",
       "      <td>5.5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.1</td>\n",
       "      <td>VA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IDADAC1URN</td>\n",
       "      <td>Ada County</td>\n",
       "      <td>16001</td>\n",
       "      <td>3.4</td>\n",
       "      <td>3.9</td>\n",
       "      <td>4.7</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.1</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.7</td>\n",
       "      <td>...</td>\n",
       "      <td>8.3</td>\n",
       "      <td>7.3</td>\n",
       "      <td>6.2</td>\n",
       "      <td>5.2</td>\n",
       "      <td>4.2</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.8</td>\n",
       "      <td>2.4</td>\n",
       "      <td>ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>OKADAI1URN</td>\n",
       "      <td>Adair County</td>\n",
       "      <td>40001</td>\n",
       "      <td>3.5</td>\n",
       "      <td>4.1</td>\n",
       "      <td>4.8</td>\n",
       "      <td>7.8</td>\n",
       "      <td>6.2</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.4</td>\n",
       "      <td>...</td>\n",
       "      <td>10.5</td>\n",
       "      <td>9.2</td>\n",
       "      <td>9.1</td>\n",
       "      <td>9.8</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.1</td>\n",
       "      <td>5.9</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.4</td>\n",
       "      <td>OK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Series ID       Region Name  Region Code  2000  2001  2002  2003  2004  \\\n",
       "0  SCABBE1URN  Abbeville County        45001   3.8   6.6   7.7   9.1   8.2   \n",
       "1  LAACAD0URN     Acadia Parish        22001   5.8   6.0   6.3   6.8   5.8   \n",
       "2  VAACCO1URN   Accomack County        51001   3.0   3.4   4.2   4.4   4.7   \n",
       "3  IDADAC1URN        Ada County        16001   3.4   3.9   4.7   4.9   4.1   \n",
       "4  OKADAI1URN      Adair County        40001   3.5   4.1   4.8   7.8   6.2   \n",
       "\n",
       "   2005  2006  ...  2010  2011  2012  2013  2014  2015  2016  2017  2018  \\\n",
       "0   7.7   8.8  ...  13.6  12.5  10.6   9.5   7.7   6.9   5.5   4.6   4.2   \n",
       "1   6.1   3.6  ...   7.3   6.7   6.0   6.0   5.8   6.9   7.4   5.9   5.4   \n",
       "2   4.7   4.3  ...   7.9   8.4   7.9   7.2   6.7   5.5   5.0   4.9   4.1   \n",
       "3   3.3   2.7  ...   8.3   7.3   6.2   5.2   4.2   3.7   3.3   2.8   2.4   \n",
       "4   4.8   4.4  ...  10.5   9.2   9.1   9.8   8.0   6.1   5.9   5.0   4.4   \n",
       "\n",
       "   State  \n",
       "0     SC  \n",
       "1     LA  \n",
       "2     VA  \n",
       "3     ID  \n",
       "4     OK  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract State into its own column\n",
    "unemployment_df[['Region Name','State']] = unemployment_df['Region Name'].str.split(',',expand=True)\n",
    "unemployment_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>State</th>\n",
       "      <th>Region Name</th>\n",
       "      <th>Region Code</th>\n",
       "      <th>Year</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SC</td>\n",
       "      <td>Abbeville County</td>\n",
       "      <td>45001</td>\n",
       "      <td>2000</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LA</td>\n",
       "      <td>Acadia Parish</td>\n",
       "      <td>22001</td>\n",
       "      <td>2000</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>VA</td>\n",
       "      <td>Accomack County</td>\n",
       "      <td>51001</td>\n",
       "      <td>2000</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ID</td>\n",
       "      <td>Ada County</td>\n",
       "      <td>16001</td>\n",
       "      <td>2000</td>\n",
       "      <td>3.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>OK</td>\n",
       "      <td>Adair County</td>\n",
       "      <td>40001</td>\n",
       "      <td>2000</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State       Region Name  Region Code  Year  Unemployment\n",
       "0    SC  Abbeville County        45001  2000           3.8\n",
       "1    LA     Acadia Parish        22001  2000           5.8\n",
       "2    VA   Accomack County        51001  2000           3.0\n",
       "3    ID        Ada County        16001  2000           3.4\n",
       "4    OK      Adair County        40001  2000           3.5"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Clean up and reorganize\n",
    "\n",
    "# Extract desired data only\n",
    "unemployment_df1 = unemployment_df.drop(labels=['Series ID'], axis=1)\n",
    "unemployment_df = unemployment_df[['State', 'Region Name', 'Region Code'] + [str(x) for x in range(2000, 2019)]]\n",
    "\n",
    "# Reorganize years\n",
    "unemployment_df = unemployment_df.melt(('State', 'Region Name', 'Region Code'), var_name='Year', value_name='Unemployment')\n",
    "unemployment_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>state_id</th>\n",
       "      <th>Region Code</th>\n",
       "      <th>Region Name</th>\n",
       "      <th>Year</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39</td>\n",
       "      <td>45001</td>\n",
       "      <td>Abbeville County</td>\n",
       "      <td>2000</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39</td>\n",
       "      <td>45003</td>\n",
       "      <td>Aiken County</td>\n",
       "      <td>2000</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39</td>\n",
       "      <td>45005</td>\n",
       "      <td>Allendale County</td>\n",
       "      <td>2000</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39</td>\n",
       "      <td>45007</td>\n",
       "      <td>Anderson County</td>\n",
       "      <td>2000</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39</td>\n",
       "      <td>45009</td>\n",
       "      <td>Bamberg County</td>\n",
       "      <td>2000</td>\n",
       "      <td>5.2</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>59560</th>\n",
       "      <td>7</td>\n",
       "      <td>10003</td>\n",
       "      <td>New Castle County</td>\n",
       "      <td>2017</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59561</th>\n",
       "      <td>7</td>\n",
       "      <td>10005</td>\n",
       "      <td>Sussex County</td>\n",
       "      <td>2017</td>\n",
       "      <td>4.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59562</th>\n",
       "      <td>7</td>\n",
       "      <td>10001</td>\n",
       "      <td>Kent County</td>\n",
       "      <td>2018</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59563</th>\n",
       "      <td>7</td>\n",
       "      <td>10003</td>\n",
       "      <td>New Castle County</td>\n",
       "      <td>2018</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59564</th>\n",
       "      <td>7</td>\n",
       "      <td>10005</td>\n",
       "      <td>Sussex County</td>\n",
       "      <td>2018</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59565 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       state_id  Region Code        Region Name  Year  Unemployment\n",
       "0            39        45001   Abbeville County  2000           3.8\n",
       "1            39        45003       Aiken County  2000           3.9\n",
       "2            39        45005   Allendale County  2000           5.3\n",
       "3            39        45007    Anderson County  2000           3.2\n",
       "4            39        45009     Bamberg County  2000           5.2\n",
       "...         ...          ...                ...   ...           ...\n",
       "59560         7        10003  New Castle County  2017           4.5\n",
       "59561         7        10005      Sussex County  2017           4.6\n",
       "59562         7        10001        Kent County  2018           4.3\n",
       "59563         7        10003  New Castle County  2018           3.8\n",
       "59564         7        10005      Sussex County  2018           4.0\n",
       "\n",
       "[59565 rows x 5 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace state with state_id\n",
    "states = pd.read_csv('Resources/Clean_data/state.csv')\n",
    "unemployment_df['State'] = unemployment_df['State'].apply(lambda x: x[1:])\n",
    "unemployment_df = pd.merge(unemployment_df, states[['state_id', 'state_abr']], left_on='State', right_on='state_abr')[['state_id', 'Region Code', 'Region Name', 'Year', 'Unemployment']]\n",
    "                                                                                                            \n",
    "unemployment_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>Code</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>45001</td>\n",
       "      <td>Abbeville County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>45003</td>\n",
       "      <td>Aiken County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>45005</td>\n",
       "      <td>Allendale County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45007</td>\n",
       "      <td>Anderson County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>45009</td>\n",
       "      <td>Bamberg County</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Code              Name\n",
       "0  45001  Abbeville County\n",
       "1  45003      Aiken County\n",
       "2  45005  Allendale County\n",
       "3  45007   Anderson County\n",
       "4  45009    Bamberg County"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Further normalize data. There should be a Region name-Region code table\n",
    "regions = {'Code': [], 'Name': []}\n",
    "for index, row in unemployment_df.iterrows():\n",
    "    code = row['Region Code']\n",
    "    if code in regions['Code']:\n",
    "        continue\n",
    "    regions['Code'].append(code)\n",
    "    regions['Name'].append(row['Region Name'])\n",
    "code_df = pd.DataFrame(regions)\n",
    "code_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete Region Name column from the unemployment to avoid redunancy\n",
    "del unemployment_df['Region Name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>state_id</th>\n",
       "      <th>Region Code</th>\n",
       "      <th>Year</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39</td>\n",
       "      <td>45001</td>\n",
       "      <td>2000</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39</td>\n",
       "      <td>45003</td>\n",
       "      <td>2000</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39</td>\n",
       "      <td>45005</td>\n",
       "      <td>2000</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39</td>\n",
       "      <td>45007</td>\n",
       "      <td>2000</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39</td>\n",
       "      <td>45009</td>\n",
       "      <td>2000</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_id  Region Code  Year  Unemployment\n",
       "0        39        45001  2000           3.8\n",
       "1        39        45003  2000           3.9\n",
       "2        39        45005  2000           5.3\n",
       "3        39        45007  2000           3.2\n",
       "4        39        45009  2000           5.2"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unemployment_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>Code</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>45001</td>\n",
       "      <td>Abbeville County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>45003</td>\n",
       "      <td>Aiken County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>45005</td>\n",
       "      <td>Allendale County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45007</td>\n",
       "      <td>Anderson County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>45009</td>\n",
       "      <td>Bamberg County</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Code              Name\n",
       "0  45001  Abbeville County\n",
       "1  45003      Aiken County\n",
       "2  45005  Allendale County\n",
       "3  45007   Anderson County\n",
       "4  45009    Bamberg County"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "code_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save data\n",
    "unemployment_df.to_csv('Resources/Clean_data/unemployment.csv')\n",
    "code_df.to_csv('Resources/Clean_data/region_codes.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Minimun Wage Data\n",
    "- Data Source:https://www.kaggle.com/lislejoem/us-minimum-wage-by-state-from-1968-to-2017"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Path\n",
    "min_wage_df = \"Resources/Raw_data/min_wage.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>state_id</th>\n",
       "      <th>Year</th>\n",
       "      <th>state_name</th>\n",
       "      <th>min_wage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1968</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>8.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1968</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>15.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1968</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>8.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1968</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>8.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1968</td>\n",
       "      <td>California</td>\n",
       "      <td>12.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2857</th>\n",
       "      <td>2857</td>\n",
       "      <td>2020</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>7.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2858</th>\n",
       "      <td>2858</td>\n",
       "      <td>2020</td>\n",
       "      <td>Washington</td>\n",
       "      <td>13.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2859</th>\n",
       "      <td>2859</td>\n",
       "      <td>2020</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>8.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2860</th>\n",
       "      <td>2860</td>\n",
       "      <td>2020</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>7.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2861</th>\n",
       "      <td>2861</td>\n",
       "      <td>2020</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>7.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2862 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      state_id  Year     state_name  min_wage\n",
       "0            0  1968        Alabama      8.55\n",
       "1            1  1968         Alaska     15.61\n",
       "2            2  1968        Arizona      8.55\n",
       "3            3  1968       Arkansas      8.55\n",
       "4            4  1968     California     12.26\n",
       "...        ...   ...            ...       ...\n",
       "2857      2857  2020       Virginia      7.25\n",
       "2858      2858  2020     Washington     13.50\n",
       "2859      2859  2020  West Virginia      8.75\n",
       "2860      2860  2020      Wisconsin      7.25\n",
       "2861      2861  2020        Wyoming      7.25\n",
       "\n",
       "[2862 rows x 4 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Read csv\n",
    "min_wage_df = pd.read_csv(min_wage_df)\n",
    "min_wage_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "del min_wage_df['state_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>Year</th>\n",
       "      <th>state_name</th>\n",
       "      <th>min_wage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1968</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>8.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1968</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>15.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1968</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>8.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1968</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>8.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1968</td>\n",
       "      <td>California</td>\n",
       "      <td>12.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2857</th>\n",
       "      <td>2020</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>7.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2858</th>\n",
       "      <td>2020</td>\n",
       "      <td>Washington</td>\n",
       "      <td>13.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2859</th>\n",
       "      <td>2020</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>8.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2860</th>\n",
       "      <td>2020</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>7.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2861</th>\n",
       "      <td>2020</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>7.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2862 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year     state_name  min_wage\n",
       "0     1968        Alabama      8.55\n",
       "1     1968         Alaska     15.61\n",
       "2     1968        Arizona      8.55\n",
       "3     1968       Arkansas      8.55\n",
       "4     1968     California     12.26\n",
       "...    ...            ...       ...\n",
       "2857  2020       Virginia      7.25\n",
       "2858  2020     Washington     13.50\n",
       "2859  2020  West Virginia      8.75\n",
       "2860  2020      Wisconsin      7.25\n",
       "2861  2020        Wyoming      7.25\n",
       "\n",
       "[2862 rows x 3 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "min_wage_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>state_id</th>\n",
       "      <th>state_name</th>\n",
       "      <th>state_abr</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>44</td>\n",
       "      <td>25</td>\n",
       "      <td>47</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>34</td>\n",
       "      <td>23</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>45</td>\n",
       "      <td>49</td>\n",
       "      <td>42</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "      <td>19</td>\n",
       "      <td>7</td>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_id  state_name state_abr  lifeQualityRank  healthCareRank  \\\n",
       "0         0     Alabama        AL               49              46   \n",
       "1         1      Alaska        AK               44              25   \n",
       "2         2     Arizona        AZ               34              23   \n",
       "3         3    Arkansas        AR               45              49   \n",
       "4         4  California        CA               19               7   \n",
       "\n",
       "   educationRank  economyRank  \n",
       "0             50           45  \n",
       "1             47           46  \n",
       "2             40           10  \n",
       "3             42           43  \n",
       "4             21            4  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Read state csv\n",
    "statesdf = pd.read_csv('Resources/Clean_data/state.csv')\n",
    "statesdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>state_id</th>\n",
       "      <th>state_name</th>\n",
       "      <th>state_abr</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "      <th>Year</th>\n",
       "      <th>min_wage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "      <td>1968</td>\n",
       "      <td>8.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "      <td>1969</td>\n",
       "      <td>8.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "      <td>1970</td>\n",
       "      <td>8.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "      <td>1971</td>\n",
       "      <td>8.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "      <td>1972</td>\n",
       "      <td>9.90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_id state_name state_abr  lifeQualityRank  healthCareRank  \\\n",
       "0         0    Alabama        AL               49              46   \n",
       "1         0    Alabama        AL               49              46   \n",
       "2         0    Alabama        AL               49              46   \n",
       "3         0    Alabama        AL               49              46   \n",
       "4         0    Alabama        AL               49              46   \n",
       "\n",
       "   educationRank  economyRank  Year  min_wage  \n",
       "0             50           45  1968      8.55  \n",
       "1             50           45  1969      8.11  \n",
       "2             50           45  1970      8.67  \n",
       "3             50           45  1971      8.30  \n",
       "4             50           45  1972      9.90  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_states_df = pd.merge(statesdf, min_wage_df, on='state_name', how='inner')\n",
    "merged_states_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>state_id</th>\n",
       "      <th>Year</th>\n",
       "      <th>min_wage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1968</td>\n",
       "      <td>8.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1969</td>\n",
       "      <td>8.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>1970</td>\n",
       "      <td>8.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>1971</td>\n",
       "      <td>8.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>1972</td>\n",
       "      <td>9.90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_id  Year  min_wage\n",
       "0         0  1968      8.55\n",
       "1         0  1969      8.11\n",
       "2         0  1970      8.67\n",
       "3         0  1971      8.30\n",
       "4         0  1972      9.90"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "min_wage_df= merged_states_df[['state_id', 'Year', 'min_wage']]\n",
    "min_wage_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "min_wage_df.to_csv('Resources/Clean_data/min_wage.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create engine and connection to  db.\n",
    "\n",
    "engine = create_engine(f'postgresql://postgres:{password}@localhost:5432/US_quality_db')\n",
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Postgresql Database state table "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_df.to_sql(name='state', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Postgresql Database public_school table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "public_schools.to_sql(name='public_schools', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Postgresql Database housing table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "housing_df_final.to_sql(name='housing', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Postgresql Database income table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "income_df_final.to_sql(name='income', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Postgresql Database crime table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_df_final_v2.to_sql(name='crime', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Postgresql Database min_wage table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "min_wage_df.to_sql(name=\"min_wage\", con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Postgresql Database unemployment table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to database\n",
    "Base = automap_base()\n",
    "engine = create_engine(f'postgresql://postgres:{password}@localhost:5432/US_quality_db')\n",
    "connection = engine.connect()\n",
    "sesh = Session(engine)\n",
    "Base.prepare(engine, reflect= True)\n",
    "econRow = Base.classes.unemployment\n",
    "codeRow = Base.classes.regionCode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Region codes\n",
    "codeObjects = []\n",
    "for index, row in code_df.iterrows():\n",
    "    Row = codeRow(code = row['Code'],\n",
    "                  name = row['Name'])\n",
    "    codeObjects.append(Row)\n",
    "\n",
    "# Unemployment \n",
    "econObjects = []\n",
    "for index, row in unemployment_df.iterrows():\n",
    "    Row = econRow(state_id = row['state_id'],\n",
    "                  regionCode = row['Region Code'],\n",
    "                  year = row['Year'],\n",
    "                  unemployment = row['Unemployment']\n",
    "                  )\n",
    "    econObjects.append(Row)\n",
    "\n",
    "# Add, close, commit\n",
    "sesh = Session(engine)\n",
    "sesh.add_all(codeObjects)\n",
    "sesh.add_all(econObjects)\n",
    "sesh.commit()\n",
    "sesh.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Postgresql Database hospitals table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to database\n",
    "Base = automap_base()\n",
    "engine = create_engine(f'postgresql://postgres:{password}@localhost:5432/US_quality_db')\n",
    "connection = engine.connect()\n",
    "Base.prepare(engine, reflect= True)\n",
    "Row = Base.classes.hospitals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert into database\n",
    "rows = []\n",
    "columns = df.columns\n",
    "for index, row in df.iterrows():\n",
    "    object = Row(\n",
    "    state_id= row['state_id'],\n",
    "    name= row['NAME'],\n",
    "    address= row['ADDRESS'],\n",
    "    city= row['CITY'],\n",
    "    zip= row['ZIP'],\n",
    "    owner= row['OWNER'],\n",
    "    rating= row['RATING'],\n",
    "    website= row['WEBSITE']\n",
    "    )\n",
    "    rows.append(object)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Commit changes\n",
    "sesh = Session(engine)\n",
    "sesh.add_all(rows)\n",
    "sesh.commit()\n",
    "sesh.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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>state_id</th>\n",
       "      <th>state_abr</th>\n",
       "      <th>state_name</th>\n",
       "      <th>lifeQualityRank</th>\n",
       "      <th>healthCareRank</th>\n",
       "      <th>educationRank</th>\n",
       "      <th>economyRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>49</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>44</td>\n",
       "      <td>25</td>\n",
       "      <td>47</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>AZ</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>34</td>\n",
       "      <td>23</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>AR</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>45</td>\n",
       "      <td>49</td>\n",
       "      <td>42</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>CA</td>\n",
       "      <td>California</td>\n",
       "      <td>19</td>\n",
       "      <td>7</td>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_id state_abr  state_name  lifeQualityRank  healthCareRank  \\\n",
       "0         0        AL     Alabama               49              46   \n",
       "1         1        AK      Alaska               44              25   \n",
       "2         2        AZ     Arizona               34              23   \n",
       "3         3        AR    Arkansas               45              49   \n",
       "4         4        CA  California               19               7   \n",
       "\n",
       "   educationRank  economyRank  \n",
       "0             50           45  \n",
       "1             47           46  \n",
       "2             40           10  \n",
       "3             42           43  \n",
       "4             21            4  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query the states from the state table into a Pandas dataframe\n",
    "states_sql = pd.read_sql(\"SELECT * FROM state\", connection)\n",
    "\n",
    "# Displaying DF\n",
    "states_sql.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}