Master_file_v11.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL Project - Team "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Objective\n",
    "\n",
    "Obtain resources from Multiple Listing Service and State of Texas websites to extract, transform, and load csv files to conduct an informative analysis in the future to help our clients to find the perfect house with the perfect school."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Type DataBase\n",
    "•\tSQL Postgres"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## HAR Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "import numpy as np\n",
    "from secrets import username, password"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mls</th>\n",
       "      <th>street_number</th>\n",
       "      <th>street_name</th>\n",
       "      <th>city</th>\n",
       "      <th>zip</th>\n",
       "      <th>county</th>\n",
       "      <th>subdivision</th>\n",
       "      <th>home_type</th>\n",
       "      <th>year_built</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>...</th>\n",
       "      <th>style</th>\n",
       "      <th>list_price</th>\n",
       "      <th>market_area</th>\n",
       "      <th>area</th>\n",
       "      <th>dom</th>\n",
       "      <th>cdom</th>\n",
       "      <th>list_date</th>\n",
       "      <th>school_district</th>\n",
       "      <th>elementary</th>\n",
       "      <th>high_school</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70161219</td>\n",
       "      <td>1747</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1952</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>255000</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2/1/2021</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17580127</td>\n",
       "      <td>1911</td>\n",
       "      <td>Pasadena</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1949</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>324900</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>74</td>\n",
       "      <td>74</td>\n",
       "      <td>11/24/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35404452</td>\n",
       "      <td>1931</td>\n",
       "      <td>Santa Rosa</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1949</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Contemporary/Modern, Traditional</td>\n",
       "      <td>319000</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>12/11/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12503971</td>\n",
       "      <td>2022</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1938</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>399000</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2/3/2021</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32263872</td>\n",
       "      <td>6707</td>\n",
       "      <td>Avenue I</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77011</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Central Park</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>2020</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>280990</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>134</td>\n",
       "      <td>251</td>\n",
       "      <td>9/25/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        mls  street_number  street_name     city    zip  county   subdivision  \\\n",
       "0  70161219           1747  Forest Hill  Houston  77023  Harris   Forest Hill   \n",
       "1  17580127           1911     Pasadena  Houston  77023  Harris   Forest Hill   \n",
       "2  35404452           1931   Santa Rosa  Houston  77023  Harris   Forest Hill   \n",
       "3  12503971           2022  Forest Hill  Houston  77023  Harris   Forest Hill   \n",
       "4  32263872           6707     Avenue I  Houston  77011  Harris  Central Park   \n",
       "\n",
       "       home_type  year_built  bedrooms  ...                             style  \\\n",
       "0  Single-Family        1952         3  ...                       Traditional   \n",
       "1  Single-Family        1949         2  ...                       Traditional   \n",
       "2  Single-Family        1949         3  ...  Contemporary/Modern, Traditional   \n",
       "3  Single-Family        1938         4  ...                       Traditional   \n",
       "4  Single-Family        2020         3  ...                       Traditional   \n",
       "\n",
       "   list_price  market_area                  area  dom  cdom   list_date  \\\n",
       "0      255000            4  East End Revitalized    5     5    2/1/2021   \n",
       "1      324900            4  East End Revitalized   74    74  11/24/2020   \n",
       "2      319000            4  East End Revitalized   53    53  12/11/2020   \n",
       "3      399000            4  East End Revitalized    3     3    2/3/2021   \n",
       "4      280990            4       University Area  134   251   9/25/2020   \n",
       "\n",
       "   school_district          elementary         high_school  \n",
       "0      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "1      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "2      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "3      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "4      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Store csv into df\n",
    "csv_file= \"./Resources/har.csv\"\n",
    "customer_data_df= pd.read_csv(csv_file)\n",
    "customer_data_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3241, 28)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total info\n",
    "customer_data_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['mls', 'street_number', 'street_name', 'city', 'zip', 'county',\n",
       "       'subdivision', 'home_type', 'year_built', 'bedrooms', 'full_baths',\n",
       "       'half_baths', 'total_baths', 'room_count', 'fireplaces', 'stories',\n",
       "       'pool_private', 'garages', 'style', 'list_price', 'market_area', 'area',\n",
       "       'dom', 'cdom', 'list_date', 'school_district', 'elementary',\n",
       "       'high_school'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Columns list\n",
    "customer_data_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       0\n",
       "1       1\n",
       "2       0\n",
       "3       1\n",
       "4       0\n",
       "       ..\n",
       "3236    0\n",
       "3237    0\n",
       "3238    0\n",
       "3239    0\n",
       "3240    1\n",
       "Name: fireplaces, Length: 3241, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Navigate a single column\n",
    "customer_data_df['fireplaces']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace null values with \"0\"\n",
    "customer_data_df['fireplaces']= customer_data_df['fireplaces'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       0\n",
       "1       1\n",
       "2       0\n",
       "3       1\n",
       "4       0\n",
       "       ..\n",
       "3236    0\n",
       "3237    0\n",
       "3238    0\n",
       "3239    0\n",
       "3240    1\n",
       "Name: fireplaces, Length: 3241, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check replacement\n",
    "customer_data_df['fireplaces']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace null values with \"0\"\n",
    "customer_data_df['garages']= customer_data_df['garages'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "mls                3241\n",
       "street_number      2151\n",
       "street_name        1841\n",
       "city                 10\n",
       "zip                  67\n",
       "county                3\n",
       "subdivision        1699\n",
       "home_type             1\n",
       "year_built          112\n",
       "bedrooms             11\n",
       "full_baths           10\n",
       "half_baths            7\n",
       "total_baths          38\n",
       "room_count           26\n",
       "fireplaces            8\n",
       "stories               5\n",
       "pool_private          2\n",
       "garages              16\n",
       "style                79\n",
       "list_price         1228\n",
       "market_area          18\n",
       "area                 58\n",
       "dom                 355\n",
       "cdom                572\n",
       "list_date           359\n",
       "school_district       6\n",
       "elementary          167\n",
       "high_school          36\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking unique values\n",
    "customer_data_df.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking unique values\n",
    "customer_data_df.nunique().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "mls                0\n",
       "street_number      0\n",
       "street_name        0\n",
       "city               0\n",
       "zip                0\n",
       "county             0\n",
       "subdivision        1\n",
       "home_type          0\n",
       "year_built         0\n",
       "bedrooms           0\n",
       "full_baths         0\n",
       "half_baths         0\n",
       "total_baths        0\n",
       "room_count         0\n",
       "fireplaces         0\n",
       "stories            0\n",
       "pool_private       0\n",
       "garages            0\n",
       "style              0\n",
       "list_price         0\n",
       "market_area        0\n",
       "area               0\n",
       "dom                0\n",
       "cdom               0\n",
       "list_date          0\n",
       "school_district    0\n",
       "elementary         0\n",
       "high_school        0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# isna/isnull\n",
    "customer_data_df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "mls                  int64\n",
       "street_number        int64\n",
       "street_name         object\n",
       "city                object\n",
       "zip                  int64\n",
       "county              object\n",
       "subdivision         object\n",
       "home_type           object\n",
       "year_built           int64\n",
       "bedrooms             int64\n",
       "full_baths           int64\n",
       "half_baths           int64\n",
       "total_baths        float64\n",
       "room_count           int64\n",
       "fireplaces           int64\n",
       "stories              int64\n",
       "pool_private          bool\n",
       "garages              int64\n",
       "style               object\n",
       "list_price           int64\n",
       "market_area          int64\n",
       "area                object\n",
       "dom                  int64\n",
       "cdom                 int64\n",
       "list_date           object\n",
       "school_district     object\n",
       "elementary          object\n",
       "high_school         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Data types\n",
    "customer_data_df.dtypes"
   ]
  },
  {
   "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>mls</th>\n",
       "      <th>street_number</th>\n",
       "      <th>street_name</th>\n",
       "      <th>city</th>\n",
       "      <th>zip</th>\n",
       "      <th>county</th>\n",
       "      <th>subdivision</th>\n",
       "      <th>home_type</th>\n",
       "      <th>year_built</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>...</th>\n",
       "      <th>style</th>\n",
       "      <th>list_price</th>\n",
       "      <th>market_area</th>\n",
       "      <th>area</th>\n",
       "      <th>dom</th>\n",
       "      <th>cdom</th>\n",
       "      <th>list_date</th>\n",
       "      <th>school_district</th>\n",
       "      <th>elementary</th>\n",
       "      <th>high_school</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70161219</td>\n",
       "      <td>1747</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1952</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>255000</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2/1/2021</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17580127</td>\n",
       "      <td>1911</td>\n",
       "      <td>Pasadena</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1949</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>324900</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>74</td>\n",
       "      <td>74</td>\n",
       "      <td>11/24/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35404452</td>\n",
       "      <td>1931</td>\n",
       "      <td>Santa Rosa</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1949</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Contemporary/Modern, Traditional</td>\n",
       "      <td>319000</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>12/11/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12503971</td>\n",
       "      <td>2022</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1938</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>399000</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2/3/2021</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32263872</td>\n",
       "      <td>6707</td>\n",
       "      <td>Avenue I</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77011</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Central Park</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>2020</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>280990</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>134</td>\n",
       "      <td>251</td>\n",
       "      <td>9/25/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        mls  street_number  street_name     city    zip  county   subdivision  \\\n",
       "0  70161219           1747  Forest Hill  Houston  77023  Harris   Forest Hill   \n",
       "1  17580127           1911     Pasadena  Houston  77023  Harris   Forest Hill   \n",
       "2  35404452           1931   Santa Rosa  Houston  77023  Harris   Forest Hill   \n",
       "3  12503971           2022  Forest Hill  Houston  77023  Harris   Forest Hill   \n",
       "4  32263872           6707     Avenue I  Houston  77011  Harris  Central Park   \n",
       "\n",
       "       home_type  year_built  bedrooms  ...                             style  \\\n",
       "0  Single-Family        1952         3  ...                       Traditional   \n",
       "1  Single-Family        1949         2  ...                       Traditional   \n",
       "2  Single-Family        1949         3  ...  Contemporary/Modern, Traditional   \n",
       "3  Single-Family        1938         4  ...                       Traditional   \n",
       "4  Single-Family        2020         3  ...                       Traditional   \n",
       "\n",
       "   list_price  market_area                  area  dom  cdom   list_date  \\\n",
       "0      255000            4  East End Revitalized    5     5    2/1/2021   \n",
       "1      324900            4  East End Revitalized   74    74  11/24/2020   \n",
       "2      319000            4  East End Revitalized   53    53  12/11/2020   \n",
       "3      399000            4  East End Revitalized    3     3    2/3/2021   \n",
       "4      280990            4       University Area  134   251   9/25/2020   \n",
       "\n",
       "   school_district          elementary         high_school  \n",
       "0      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "1      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "2      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "3      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "4      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create new df\n",
    "HAR_df= customer_data_df\n",
    "HAR_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Writing to clean csv\n",
    "HAR_df.to_csv(\"./Resources/clean_har.csv\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TEA Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extraction"
   ]
  },
  {
   "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>campus_Number</th>\n",
       "      <th>school</th>\n",
       "      <th>district</th>\n",
       "      <th>yrs_unacceptable</th>\n",
       "      <th>rating</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101902001</td>\n",
       "      <td>ALDINE HIGH SCHOOL</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101902099</td>\n",
       "      <td>ALDINE J J A E P</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101902041</td>\n",
       "      <td>ALDINE MIDDLE</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101902101</td>\n",
       "      <td>ANDERSON ACADEMY</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101902015</td>\n",
       "      <td>AVALOS P-TECH SCHOOL</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   campus_Number                school    district  yrs_unacceptable  \\\n",
       "0      101902001    ALDINE HIGH SCHOOL  ALDINE ISD                 0   \n",
       "1      101902099      ALDINE J J A E P  ALDINE ISD                 0   \n",
       "2      101902041         ALDINE MIDDLE  ALDINE ISD                 0   \n",
       "3      101902101      ANDERSON ACADEMY  ALDINE ISD                 1   \n",
       "4      101902015  AVALOS P-TECH SCHOOL  ALDINE ISD                 0   \n",
       "\n",
       "      rating  year  \n",
       "0          C   NaN  \n",
       "1  Not Rated   NaN  \n",
       "2          D   NaN  \n",
       "3          F   NaN  \n",
       "4  Not Rated   NaN  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Store csv into df\n",
    "csv_file= \"./Resources/school_rating.csv\"\n",
    "old_school_rating_df= pd.read_csv(csv_file)\n",
    "old_school_rating_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "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>campus_Number</th>\n",
       "      <th>school</th>\n",
       "      <th>district</th>\n",
       "      <th>yrs_unacceptable</th>\n",
       "      <th>rating</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101902001</td>\n",
       "      <td>ALDINE HIGH SCHOOL</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101902099</td>\n",
       "      <td>ALDINE J J A E P</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101902041</td>\n",
       "      <td>ALDINE MIDDLE</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101902101</td>\n",
       "      <td>ANDERSON ACADEMY</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101902015</td>\n",
       "      <td>AVALOS P-TECH SCHOOL</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>NaN</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650</th>\n",
       "      <td>101919051</td>\n",
       "      <td>SPRINGWOODS VILLAGE MIDDLE</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651</th>\n",
       "      <td>101919047</td>\n",
       "      <td>STELLE CLAUGHTON MIDDLE</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652</th>\n",
       "      <td>101919045</td>\n",
       "      <td>TWIN CREEKS MIDDLE</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>653</th>\n",
       "      <td>101919002</td>\n",
       "      <td>WESTFIELD HIGH SCHOOL</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>888888882</td>\n",
       "      <td>MCELWAIN ELEMENTARY</td>\n",
       "      <td>KATY ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>655 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     campus_Number                      school    district  yrs_unacceptable  \\\n",
       "0        101902001          ALDINE HIGH SCHOOL  ALDINE ISD                 0   \n",
       "1        101902099            ALDINE J J A E P  ALDINE ISD                 0   \n",
       "2        101902041               ALDINE MIDDLE  ALDINE ISD                 0   \n",
       "3        101902101            ANDERSON ACADEMY  ALDINE ISD                 1   \n",
       "4        101902015        AVALOS P-TECH SCHOOL  ALDINE ISD                 0   \n",
       "..             ...                         ...         ...               ...   \n",
       "650      101919051  SPRINGWOODS VILLAGE MIDDLE  SPRING ISD                 0   \n",
       "651      101919047     STELLE CLAUGHTON MIDDLE  SPRING ISD                 1   \n",
       "652      101919045          TWIN CREEKS MIDDLE  SPRING ISD                 0   \n",
       "653      101919002       WESTFIELD HIGH SCHOOL  SPRING ISD                 0   \n",
       "654      888888882         MCELWAIN ELEMENTARY    KATY ISD                 0   \n",
       "\n",
       "        rating  year  \n",
       "0            C   NaN  \n",
       "1    Not Rated   NaN  \n",
       "2            D   NaN  \n",
       "3            F   NaN  \n",
       "4    Not Rated   NaN  \n",
       "..         ...   ...  \n",
       "650  Not Rated   NaN  \n",
       "651          F   NaN  \n",
       "652          C   NaN  \n",
       "653          D   NaN  \n",
       "654  Not Rated   NaN  \n",
       "\n",
       "[655 rows x 6 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Renaming column\n",
    "a_school_rating_df= old_school_rating_df.rename(columns= {\"rating_2019\": \"rating\"})\n",
    "a_school_rating_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>campus_Number</th>\n",
       "      <th>school</th>\n",
       "      <th>district</th>\n",
       "      <th>yrs_unacceptable</th>\n",
       "      <th>rating</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101902001</td>\n",
       "      <td>ALDINE HIGH SCHOOL</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101902099</td>\n",
       "      <td>ALDINE J J A E P</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101902041</td>\n",
       "      <td>ALDINE MIDDLE</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101902101</td>\n",
       "      <td>ANDERSON ACADEMY</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101902015</td>\n",
       "      <td>AVALOS P-TECH SCHOOL</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>2019</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650</th>\n",
       "      <td>101919051</td>\n",
       "      <td>SPRINGWOODS VILLAGE MIDDLE</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651</th>\n",
       "      <td>101919047</td>\n",
       "      <td>STELLE CLAUGHTON MIDDLE</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652</th>\n",
       "      <td>101919045</td>\n",
       "      <td>TWIN CREEKS MIDDLE</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>653</th>\n",
       "      <td>101919002</td>\n",
       "      <td>WESTFIELD HIGH SCHOOL</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>888888882</td>\n",
       "      <td>MCELWAIN ELEMENTARY</td>\n",
       "      <td>KATY ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>655 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     campus_Number                      school    district  yrs_unacceptable  \\\n",
       "0        101902001          ALDINE HIGH SCHOOL  ALDINE ISD                 0   \n",
       "1        101902099            ALDINE J J A E P  ALDINE ISD                 0   \n",
       "2        101902041               ALDINE MIDDLE  ALDINE ISD                 0   \n",
       "3        101902101            ANDERSON ACADEMY  ALDINE ISD                 1   \n",
       "4        101902015        AVALOS P-TECH SCHOOL  ALDINE ISD                 0   \n",
       "..             ...                         ...         ...               ...   \n",
       "650      101919051  SPRINGWOODS VILLAGE MIDDLE  SPRING ISD                 0   \n",
       "651      101919047     STELLE CLAUGHTON MIDDLE  SPRING ISD                 1   \n",
       "652      101919045          TWIN CREEKS MIDDLE  SPRING ISD                 0   \n",
       "653      101919002       WESTFIELD HIGH SCHOOL  SPRING ISD                 0   \n",
       "654      888888882         MCELWAIN ELEMENTARY    KATY ISD                 0   \n",
       "\n",
       "        rating  year  \n",
       "0            C  2019  \n",
       "1    Not Rated  2019  \n",
       "2            D  2019  \n",
       "3            F  2019  \n",
       "4    Not Rated  2019  \n",
       "..         ...   ...  \n",
       "650  Not Rated  2019  \n",
       "651          F  2019  \n",
       "652          C  2019  \n",
       "653          D  2019  \n",
       "654  Not Rated  2019  \n",
       "\n",
       "[655 rows x 6 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adding in new column and associated value\n",
    "a_school_rating_df['year']= 2019\n",
    "a_school_rating_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>campus_Number</th>\n",
       "      <th>school</th>\n",
       "      <th>district</th>\n",
       "      <th>yrs_unacceptable</th>\n",
       "      <th>rating</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101902001</td>\n",
       "      <td>ALDINE HIGH SCHOOL</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101902099</td>\n",
       "      <td>ALDINE J J A E P</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101902041</td>\n",
       "      <td>ALDINE MIDDLE</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101902101</td>\n",
       "      <td>ANDERSON ACADEMY</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101902015</td>\n",
       "      <td>AVALOS P-TECH SCHOOL</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>2019</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650</th>\n",
       "      <td>101919051</td>\n",
       "      <td>SPRINGWOODS VILLAGE MIDDLE</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651</th>\n",
       "      <td>101919047</td>\n",
       "      <td>STELLE CLAUGHTON MIDDLE</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652</th>\n",
       "      <td>101919045</td>\n",
       "      <td>TWIN CREEKS MIDDLE</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>653</th>\n",
       "      <td>101919002</td>\n",
       "      <td>WESTFIELD HIGH SCHOOL</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>888888882</td>\n",
       "      <td>MCELWAIN ELEMENTARY</td>\n",
       "      <td>KATY ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>655 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     campus_Number                      school    district  yrs_unacceptable  \\\n",
       "0        101902001          ALDINE HIGH SCHOOL  ALDINE ISD                 0   \n",
       "1        101902099            ALDINE J J A E P  ALDINE ISD                 0   \n",
       "2        101902041               ALDINE MIDDLE  ALDINE ISD                 0   \n",
       "3        101902101            ANDERSON ACADEMY  ALDINE ISD                 1   \n",
       "4        101902015        AVALOS P-TECH SCHOOL  ALDINE ISD                 0   \n",
       "..             ...                         ...         ...               ...   \n",
       "650      101919051  SPRINGWOODS VILLAGE MIDDLE  SPRING ISD                 0   \n",
       "651      101919047     STELLE CLAUGHTON MIDDLE  SPRING ISD                 1   \n",
       "652      101919045          TWIN CREEKS MIDDLE  SPRING ISD                 0   \n",
       "653      101919002       WESTFIELD HIGH SCHOOL  SPRING ISD                 0   \n",
       "654      888888882         MCELWAIN ELEMENTARY    KATY ISD                 0   \n",
       "\n",
       "        rating  year  \n",
       "0            C  2019  \n",
       "1    Not Rated  2019  \n",
       "2            D  2019  \n",
       "3            F  2019  \n",
       "4    Not Rated  2019  \n",
       "..         ...   ...  \n",
       "650  Not Rated  2019  \n",
       "651          F  2019  \n",
       "652          C  2019  \n",
       "653          D  2019  \n",
       "654  Not Rated  2019  \n",
       "\n",
       "[655 rows x 6 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating duplicate df to prevent corruption of original df\n",
    "school_rating_df= a_school_rating_df\n",
    "school_rating_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Writing to clean csv\n",
    "school_rating_df.to_csv('./Resources/clean_ratings.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "school\n",
      "ACADEMY OF CHOICE                     1\n",
      "ADAM ELEMENTARY                       1\n",
      "ADAMS J H                             1\n",
      "ALAMO ELEMENTARY                      1\n",
      "ALCOTT ELEMENTARY                     1\n",
      "                                     ..\n",
      "YOUNG ELEMENTARY                      1\n",
      "YOUNG LEARNERS                        1\n",
      "YOUNG SCHOLARS ACADEMY FOR EXCELLE    1\n",
      "YOUNG WOMEN'S COLLEGE PREP ACADEMY    1\n",
      "ZELMA HUTSELL ELEMENTARY              1\n",
      "Length: 655, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Identify duplicate schools\n",
    "dupl_school_rating= school_rating_df.pivot_table(index= ['school'], aggfunc= 'size')\n",
    "print(dupl_school_rating)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "school\n",
       "ZELMA HUTSELL ELEMENTARY         1\n",
       "GARCIA MIDDLE                    1\n",
       "GREGG ELEMENTARY                 1\n",
       "GREENSPOINT ELEMENTARY           1\n",
       "GREEN VALLEY ELEMENTARY          1\n",
       "                                ..\n",
       "OAK FOREST ELEMENTARY            1\n",
       "NOTTINGHAM ELEMENTARY            1\n",
       "NOTTINGHAM COUNTRY ELEMENTARY    1\n",
       "NORTHSIDE HIGH SCHOOL            1\n",
       "ACADEMY OF CHOICE                1\n",
       "Length: 655, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Duplicate school sorting descending\n",
    "dupl_school_rating.sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Junction Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mls</th>\n",
       "      <th>campus_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>242718</td>\n",
       "      <td>101912116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>242718</td>\n",
       "      <td>101912009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>296833</td>\n",
       "      <td>101912211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>296833</td>\n",
       "      <td>101912015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>468622</td>\n",
       "      <td>101912174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6477</th>\n",
       "      <td>98783237</td>\n",
       "      <td>101912002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6478</th>\n",
       "      <td>98915588</td>\n",
       "      <td>101912389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6479</th>\n",
       "      <td>98915588</td>\n",
       "      <td>101912003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6480</th>\n",
       "      <td>98990945</td>\n",
       "      <td>101912275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6481</th>\n",
       "      <td>98990945</td>\n",
       "      <td>101912036</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6482 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls  campus_number\n",
       "0       242718      101912116\n",
       "1       242718      101912009\n",
       "2       296833      101912211\n",
       "3       296833      101912015\n",
       "4       468622      101912174\n",
       "...        ...            ...\n",
       "6477  98783237      101912002\n",
       "6478  98915588      101912389\n",
       "6479  98915588      101912003\n",
       "6480  98990945      101912275\n",
       "6481  98990945      101912036\n",
       "\n",
       "[6482 rows x 2 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Store csv into df\n",
    "csv_file= \"./Resources/HAR2.csv\"\n",
    "junction_df= pd.read_csv(csv_file)\n",
    "junction_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load to SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to local database\n",
    "rds_connection_string = f\"{username}:{password}@localhost:5432/etl_team5\"\n",
    "engine = create_engine(f'postgresql://{rds_connection_string}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use pandas to load transformed df into table school_rating_df\n",
    "school_rating_df.to_sql(name='school_rating', con=engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use pandas to load transformed df into table mls\n",
    "HAR_df.to_sql(name='mls', con=engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use pandas to load transformed df into table junction_table\n",
    "junction_df.to_sql(name='junction_table', con=engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL Query of tables "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>campus_Number</th>\n",
       "      <th>school</th>\n",
       "      <th>district</th>\n",
       "      <th>yrs_unacceptable</th>\n",
       "      <th>rating</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101902001</td>\n",
       "      <td>ALDINE HIGH SCHOOL</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101902099</td>\n",
       "      <td>ALDINE J J A E P</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101902041</td>\n",
       "      <td>ALDINE MIDDLE</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101902101</td>\n",
       "      <td>ANDERSON ACADEMY</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101902015</td>\n",
       "      <td>AVALOS P-TECH SCHOOL</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   campus_Number                school    district  yrs_unacceptable  \\\n",
       "0      101902001    ALDINE HIGH SCHOOL  ALDINE ISD                 0   \n",
       "1      101902099      ALDINE J J A E P  ALDINE ISD                 0   \n",
       "2      101902041         ALDINE MIDDLE  ALDINE ISD                 0   \n",
       "3      101902101      ANDERSON ACADEMY  ALDINE ISD                 1   \n",
       "4      101902015  AVALOS P-TECH SCHOOL  ALDINE ISD                 0   \n",
       "\n",
       "      rating  year  \n",
       "0          C  2019  \n",
       "1  Not Rated  2019  \n",
       "2          D  2019  \n",
       "3          F  2019  \n",
       "4  Not Rated  2019  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Confirm data has been added by querying the school_rating table\n",
    "pd.read_sql_query('select * from school_rating', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mls</th>\n",
       "      <th>street_number</th>\n",
       "      <th>street_name</th>\n",
       "      <th>city</th>\n",
       "      <th>zip</th>\n",
       "      <th>county</th>\n",
       "      <th>subdivision</th>\n",
       "      <th>home_type</th>\n",
       "      <th>year_built</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>...</th>\n",
       "      <th>style</th>\n",
       "      <th>list_price</th>\n",
       "      <th>market_area</th>\n",
       "      <th>area</th>\n",
       "      <th>dom</th>\n",
       "      <th>cdom</th>\n",
       "      <th>list_date</th>\n",
       "      <th>school_district</th>\n",
       "      <th>elementary</th>\n",
       "      <th>high_school</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70161219</td>\n",
       "      <td>1747</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1952</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>255000</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2/1/2021</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17580127</td>\n",
       "      <td>1911</td>\n",
       "      <td>Pasadena</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1949</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>324900</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>74</td>\n",
       "      <td>74</td>\n",
       "      <td>11/24/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35404452</td>\n",
       "      <td>1931</td>\n",
       "      <td>Santa Rosa</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1949</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Contemporary/Modern, Traditional</td>\n",
       "      <td>319000</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>12/11/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12503971</td>\n",
       "      <td>2022</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77023</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Forest Hill</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1938</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>399000</td>\n",
       "      <td>4</td>\n",
       "      <td>East End Revitalized</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2/3/2021</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32263872</td>\n",
       "      <td>6707</td>\n",
       "      <td>Avenue I</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77011</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Central Park</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>2020</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>280990</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>134</td>\n",
       "      <td>251</td>\n",
       "      <td>9/25/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>BRISCOE ELEMENTARY</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        mls  street_number  street_name     city    zip  county   subdivision  \\\n",
       "0  70161219           1747  Forest Hill  Houston  77023  Harris   Forest Hill   \n",
       "1  17580127           1911     Pasadena  Houston  77023  Harris   Forest Hill   \n",
       "2  35404452           1931   Santa Rosa  Houston  77023  Harris   Forest Hill   \n",
       "3  12503971           2022  Forest Hill  Houston  77023  Harris   Forest Hill   \n",
       "4  32263872           6707     Avenue I  Houston  77011  Harris  Central Park   \n",
       "\n",
       "       home_type  year_built  bedrooms  ...                             style  \\\n",
       "0  Single-Family        1952         3  ...                       Traditional   \n",
       "1  Single-Family        1949         2  ...                       Traditional   \n",
       "2  Single-Family        1949         3  ...  Contemporary/Modern, Traditional   \n",
       "3  Single-Family        1938         4  ...                       Traditional   \n",
       "4  Single-Family        2020         3  ...                       Traditional   \n",
       "\n",
       "   list_price  market_area                  area  dom  cdom   list_date  \\\n",
       "0      255000            4  East End Revitalized    5     5    2/1/2021   \n",
       "1      324900            4  East End Revitalized   74    74  11/24/2020   \n",
       "2      319000            4  East End Revitalized   53    53  12/11/2020   \n",
       "3      399000            4  East End Revitalized    3     3    2/3/2021   \n",
       "4      280990            4       University Area  134   251   9/25/2020   \n",
       "\n",
       "   school_district          elementary         high_school  \n",
       "0      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "1      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "2      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "3      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "4      HOUSTON ISD  BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Confirm data has been added by querying the mls table\n",
    "pd.read_sql_query('select * from mls', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mls</th>\n",
       "      <th>campus_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>242718</td>\n",
       "      <td>101912116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>242718</td>\n",
       "      <td>101912009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>296833</td>\n",
       "      <td>101912211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>296833</td>\n",
       "      <td>101912015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>468622</td>\n",
       "      <td>101912174</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      mls  campus_number\n",
       "0  242718      101912116\n",
       "1  242718      101912009\n",
       "2  296833      101912211\n",
       "3  296833      101912015\n",
       "4  468622      101912174"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Confirm data has been added by querying the junction_table table\n",
    "pd.read_sql_query('select * from junction_table', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['junction_table', 'school_rating', 'mls']"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for tables\n",
    "engine.table_names()"
   ]
  }
 ],
 "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": 5
}