Alt_Master_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",
       "    <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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3236</th>\n",
       "      <td>52192821</td>\n",
       "      <td>3722</td>\n",
       "      <td>Goodhope</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1958</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>160000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2/3/2021</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY ELEMENTARY</td>\n",
       "      <td>YATES HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3237</th>\n",
       "      <td>58992773</td>\n",
       "      <td>3722</td>\n",
       "      <td>Mount Pleasant #B</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>2021</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Contemporary/Modern</td>\n",
       "      <td>369998</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>129</td>\n",
       "      <td>129</td>\n",
       "      <td>9/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY ELEMENTARY</td>\n",
       "      <td>YATES HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3238</th>\n",
       "      <td>63298093</td>\n",
       "      <td>3734</td>\n",
       "      <td>Seabrook</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1955</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Other Style</td>\n",
       "      <td>149900</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>87</td>\n",
       "      <td>87</td>\n",
       "      <td>10/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY ELEMENTARY</td>\n",
       "      <td>YATES HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3239</th>\n",
       "      <td>9871530</td>\n",
       "      <td>3814</td>\n",
       "      <td>Seabrook St</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1968</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>179000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>12/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY ELEMENTARY</td>\n",
       "      <td>YATES HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3240</th>\n",
       "      <td>9871043</td>\n",
       "      <td>3853</td>\n",
       "      <td>Goodhope</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1950</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>225000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>87</td>\n",
       "      <td>87</td>\n",
       "      <td>11/11/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY ELEMENTARY</td>\n",
       "      <td>YATES HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3241 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls  street_number        street_name     city    zip  county  \\\n",
       "0     70161219           1747        Forest Hill  Houston  77023  Harris   \n",
       "1     17580127           1911           Pasadena  Houston  77023  Harris   \n",
       "2     35404452           1931         Santa Rosa  Houston  77023  Harris   \n",
       "3     12503971           2022        Forest Hill  Houston  77023  Harris   \n",
       "4     32263872           6707           Avenue I  Houston  77011  Harris   \n",
       "...        ...            ...                ...      ...    ...     ...   \n",
       "3236  52192821           3722           Goodhope  Houston  77021  Harris   \n",
       "3237  58992773           3722  Mount Pleasant #B  Houston  77021  Harris   \n",
       "3238  63298093           3734           Seabrook  Houston  77021  Harris   \n",
       "3239   9871530           3814        Seabrook St  Houston  77021  Harris   \n",
       "3240   9871043           3853           Goodhope  Houston  77021  Harris   \n",
       "\n",
       "                  subdivision      home_type  year_built  bedrooms  ...  \\\n",
       "0                 Forest Hill  Single-Family        1952         3  ...   \n",
       "1                 Forest Hill  Single-Family        1949         2  ...   \n",
       "2                 Forest Hill  Single-Family        1949         3  ...   \n",
       "3                 Forest Hill  Single-Family        1938         4  ...   \n",
       "4                Central Park  Single-Family        2020         3  ...   \n",
       "...                       ...            ...         ...       ...  ...   \n",
       "3236  South End Sunnyside U/R  Single-Family        1958         3  ...   \n",
       "3237  South End Sunnyside U/R  Single-Family        2021         3  ...   \n",
       "3238  South End Sunnyside U/R  Single-Family        1955         3  ...   \n",
       "3239  South End Sunnyside U/R  Single-Family        1968         4  ...   \n",
       "3240  South End Sunnyside U/R  Single-Family        1950         4  ...   \n",
       "\n",
       "                                 style  list_price  market_area  \\\n",
       "0                          Traditional      255000            4   \n",
       "1                          Traditional      324900            4   \n",
       "2     Contemporary/Modern, Traditional      319000            4   \n",
       "3                          Traditional      399000            4   \n",
       "4                          Traditional      280990            4   \n",
       "...                                ...         ...          ...   \n",
       "3236                       Traditional      160000            4   \n",
       "3237               Contemporary/Modern      369998            4   \n",
       "3238                       Other Style      149900            4   \n",
       "3239                       Traditional      179000            4   \n",
       "3240                       Traditional      225000            4   \n",
       "\n",
       "                      area  dom  cdom   list_date  school_district  \\\n",
       "0     East End Revitalized    5     5    2/1/2021      HOUSTON ISD   \n",
       "1     East End Revitalized   74    74  11/24/2020      HOUSTON ISD   \n",
       "2     East End Revitalized   53    53  12/11/2020      HOUSTON ISD   \n",
       "3     East End Revitalized    3     3    2/3/2021      HOUSTON ISD   \n",
       "4          University Area  134   251   9/25/2020      HOUSTON ISD   \n",
       "...                    ...  ...   ...         ...              ...   \n",
       "3236       University Area    3     3    2/3/2021      HOUSTON ISD   \n",
       "3237       University Area  129   129   9/30/2020      HOUSTON ISD   \n",
       "3238       University Area   87    87  10/30/2020      HOUSTON ISD   \n",
       "3239       University Area   34    34  12/30/2020      HOUSTON ISD   \n",
       "3240       University Area   87    87  11/11/2020      HOUSTON ISD   \n",
       "\n",
       "              elementary         high_school  \n",
       "0     BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "1     BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "2     BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "3     BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "4     BRISCOE ELEMENTARY  AUSTIN HIGH SCHOOL  \n",
       "...                  ...                 ...  \n",
       "3236   WHIDBY ELEMENTARY   YATES HIGH SCHOOL  \n",
       "3237   WHIDBY ELEMENTARY   YATES HIGH SCHOOL  \n",
       "3238   WHIDBY ELEMENTARY   YATES HIGH SCHOOL  \n",
       "3239   WHIDBY ELEMENTARY   YATES HIGH SCHOOL  \n",
       "3240   WHIDBY ELEMENTARY   YATES HIGH SCHOOL  \n",
       "\n",
       "[3241 rows x 28 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Store csv into df\n",
    "csv_file= \"./Resources/alt_HAR.csv\"\n",
    "raw_har_df= pd.read_csv(csv_file)\n",
    "raw_har_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "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>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 EL</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 EL</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 EL</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 EL</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 EL</td>\n",
       "      <td>AUSTIN HIGH SCHOOL</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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3236</th>\n",
       "      <td>52192821</td>\n",
       "      <td>3722</td>\n",
       "      <td>Goodhope</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1958</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>160000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2/3/2021</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3237</th>\n",
       "      <td>58992773</td>\n",
       "      <td>3722</td>\n",
       "      <td>Mount Pleasant #B</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>2021</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Contemporary/Modern</td>\n",
       "      <td>369998</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>129</td>\n",
       "      <td>129</td>\n",
       "      <td>9/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3238</th>\n",
       "      <td>63298093</td>\n",
       "      <td>3734</td>\n",
       "      <td>Seabrook</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1955</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Other Style</td>\n",
       "      <td>149900</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>87</td>\n",
       "      <td>87</td>\n",
       "      <td>10/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3239</th>\n",
       "      <td>9871530</td>\n",
       "      <td>3814</td>\n",
       "      <td>Seabrook St</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1968</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>179000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>12/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3240</th>\n",
       "      <td>9871043</td>\n",
       "      <td>3853</td>\n",
       "      <td>Goodhope</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1950</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>225000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>87</td>\n",
       "      <td>87</td>\n",
       "      <td>11/11/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES HIGH SCHOOL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3241 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls  street_number        street_name     city    zip  county  \\\n",
       "0     70161219           1747        Forest Hill  Houston  77023  Harris   \n",
       "1     17580127           1911           Pasadena  Houston  77023  Harris   \n",
       "2     35404452           1931         Santa Rosa  Houston  77023  Harris   \n",
       "3     12503971           2022        Forest Hill  Houston  77023  Harris   \n",
       "4     32263872           6707           Avenue I  Houston  77011  Harris   \n",
       "...        ...            ...                ...      ...    ...     ...   \n",
       "3236  52192821           3722           Goodhope  Houston  77021  Harris   \n",
       "3237  58992773           3722  Mount Pleasant #B  Houston  77021  Harris   \n",
       "3238  63298093           3734           Seabrook  Houston  77021  Harris   \n",
       "3239   9871530           3814        Seabrook St  Houston  77021  Harris   \n",
       "3240   9871043           3853           Goodhope  Houston  77021  Harris   \n",
       "\n",
       "                  subdivision      home_type  year_built  bedrooms  ...  \\\n",
       "0                 Forest Hill  Single-Family        1952         3  ...   \n",
       "1                 Forest Hill  Single-Family        1949         2  ...   \n",
       "2                 Forest Hill  Single-Family        1949         3  ...   \n",
       "3                 Forest Hill  Single-Family        1938         4  ...   \n",
       "4                Central Park  Single-Family        2020         3  ...   \n",
       "...                       ...            ...         ...       ...  ...   \n",
       "3236  South End Sunnyside U/R  Single-Family        1958         3  ...   \n",
       "3237  South End Sunnyside U/R  Single-Family        2021         3  ...   \n",
       "3238  South End Sunnyside U/R  Single-Family        1955         3  ...   \n",
       "3239  South End Sunnyside U/R  Single-Family        1968         4  ...   \n",
       "3240  South End Sunnyside U/R  Single-Family        1950         4  ...   \n",
       "\n",
       "                                 style  list_price  market_area  \\\n",
       "0                          Traditional      255000            4   \n",
       "1                          Traditional      324900            4   \n",
       "2     Contemporary/Modern, Traditional      319000            4   \n",
       "3                          Traditional      399000            4   \n",
       "4                          Traditional      280990            4   \n",
       "...                                ...         ...          ...   \n",
       "3236                       Traditional      160000            4   \n",
       "3237               Contemporary/Modern      369998            4   \n",
       "3238                       Other Style      149900            4   \n",
       "3239                       Traditional      179000            4   \n",
       "3240                       Traditional      225000            4   \n",
       "\n",
       "                      area  dom  cdom   list_date  school_district  \\\n",
       "0     East End Revitalized    5     5    2/1/2021      HOUSTON ISD   \n",
       "1     East End Revitalized   74    74  11/24/2020      HOUSTON ISD   \n",
       "2     East End Revitalized   53    53  12/11/2020      HOUSTON ISD   \n",
       "3     East End Revitalized    3     3    2/3/2021      HOUSTON ISD   \n",
       "4          University Area  134   251   9/25/2020      HOUSTON ISD   \n",
       "...                    ...  ...   ...         ...              ...   \n",
       "3236       University Area    3     3    2/3/2021      HOUSTON ISD   \n",
       "3237       University Area  129   129   9/30/2020      HOUSTON ISD   \n",
       "3238       University Area   87    87  10/30/2020      HOUSTON ISD   \n",
       "3239       University Area   34    34  12/30/2020      HOUSTON ISD   \n",
       "3240       University Area   87    87  11/11/2020      HOUSTON ISD   \n",
       "\n",
       "      elementary         high_school  \n",
       "0     BRISCOE EL  AUSTIN HIGH SCHOOL  \n",
       "1     BRISCOE EL  AUSTIN HIGH SCHOOL  \n",
       "2     BRISCOE EL  AUSTIN HIGH SCHOOL  \n",
       "3     BRISCOE EL  AUSTIN HIGH SCHOOL  \n",
       "4     BRISCOE EL  AUSTIN HIGH SCHOOL  \n",
       "...          ...                 ...  \n",
       "3236   WHIDBY EL   YATES HIGH SCHOOL  \n",
       "3237   WHIDBY EL   YATES HIGH SCHOOL  \n",
       "3238   WHIDBY EL   YATES HIGH SCHOOL  \n",
       "3239   WHIDBY EL   YATES HIGH SCHOOL  \n",
       "3240   WHIDBY EL   YATES HIGH SCHOOL  \n",
       "\n",
       "[3241 rows x 28 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# replace elementary with el to match TEA table\n",
    "raw_har1_df= raw_har_df.replace({\"elementary\" :\"ELEMENTARY\"}, \"EL\", regex = True)\n",
    "raw_har1_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3236</th>\n",
       "      <td>52192821</td>\n",
       "      <td>3722</td>\n",
       "      <td>Goodhope</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1958</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>160000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2/3/2021</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3237</th>\n",
       "      <td>58992773</td>\n",
       "      <td>3722</td>\n",
       "      <td>Mount Pleasant #B</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>2021</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Contemporary/Modern</td>\n",
       "      <td>369998</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>129</td>\n",
       "      <td>129</td>\n",
       "      <td>9/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3238</th>\n",
       "      <td>63298093</td>\n",
       "      <td>3734</td>\n",
       "      <td>Seabrook</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1955</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Other Style</td>\n",
       "      <td>149900</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>87</td>\n",
       "      <td>87</td>\n",
       "      <td>10/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3239</th>\n",
       "      <td>9871530</td>\n",
       "      <td>3814</td>\n",
       "      <td>Seabrook St</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1968</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>179000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>12/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3240</th>\n",
       "      <td>9871043</td>\n",
       "      <td>3853</td>\n",
       "      <td>Goodhope</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1950</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>225000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>87</td>\n",
       "      <td>87</td>\n",
       "      <td>11/11/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3241 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls  street_number        street_name     city    zip  county  \\\n",
       "0     70161219           1747        Forest Hill  Houston  77023  Harris   \n",
       "1     17580127           1911           Pasadena  Houston  77023  Harris   \n",
       "2     35404452           1931         Santa Rosa  Houston  77023  Harris   \n",
       "3     12503971           2022        Forest Hill  Houston  77023  Harris   \n",
       "4     32263872           6707           Avenue I  Houston  77011  Harris   \n",
       "...        ...            ...                ...      ...    ...     ...   \n",
       "3236  52192821           3722           Goodhope  Houston  77021  Harris   \n",
       "3237  58992773           3722  Mount Pleasant #B  Houston  77021  Harris   \n",
       "3238  63298093           3734           Seabrook  Houston  77021  Harris   \n",
       "3239   9871530           3814        Seabrook St  Houston  77021  Harris   \n",
       "3240   9871043           3853           Goodhope  Houston  77021  Harris   \n",
       "\n",
       "                  subdivision      home_type  year_built  bedrooms  ...  \\\n",
       "0                 Forest Hill  Single-Family        1952         3  ...   \n",
       "1                 Forest Hill  Single-Family        1949         2  ...   \n",
       "2                 Forest Hill  Single-Family        1949         3  ...   \n",
       "3                 Forest Hill  Single-Family        1938         4  ...   \n",
       "4                Central Park  Single-Family        2020         3  ...   \n",
       "...                       ...            ...         ...       ...  ...   \n",
       "3236  South End Sunnyside U/R  Single-Family        1958         3  ...   \n",
       "3237  South End Sunnyside U/R  Single-Family        2021         3  ...   \n",
       "3238  South End Sunnyside U/R  Single-Family        1955         3  ...   \n",
       "3239  South End Sunnyside U/R  Single-Family        1968         4  ...   \n",
       "3240  South End Sunnyside U/R  Single-Family        1950         4  ...   \n",
       "\n",
       "                                 style  list_price  market_area  \\\n",
       "0                          Traditional      255000            4   \n",
       "1                          Traditional      324900            4   \n",
       "2     Contemporary/Modern, Traditional      319000            4   \n",
       "3                          Traditional      399000            4   \n",
       "4                          Traditional      280990            4   \n",
       "...                                ...         ...          ...   \n",
       "3236                       Traditional      160000            4   \n",
       "3237               Contemporary/Modern      369998            4   \n",
       "3238                       Other Style      149900            4   \n",
       "3239                       Traditional      179000            4   \n",
       "3240                       Traditional      225000            4   \n",
       "\n",
       "                      area  dom  cdom   list_date  school_district  \\\n",
       "0     East End Revitalized    5     5    2/1/2021      HOUSTON ISD   \n",
       "1     East End Revitalized   74    74  11/24/2020      HOUSTON ISD   \n",
       "2     East End Revitalized   53    53  12/11/2020      HOUSTON ISD   \n",
       "3     East End Revitalized    3     3    2/3/2021      HOUSTON ISD   \n",
       "4          University Area  134   251   9/25/2020      HOUSTON ISD   \n",
       "...                    ...  ...   ...         ...              ...   \n",
       "3236       University Area    3     3    2/3/2021      HOUSTON ISD   \n",
       "3237       University Area  129   129   9/30/2020      HOUSTON ISD   \n",
       "3238       University Area   87    87  10/30/2020      HOUSTON ISD   \n",
       "3239       University Area   34    34  12/30/2020      HOUSTON ISD   \n",
       "3240       University Area   87    87  11/11/2020      HOUSTON ISD   \n",
       "\n",
       "      elementary  high_school  \n",
       "0     BRISCOE EL   AUSTIN H S  \n",
       "1     BRISCOE EL   AUSTIN H S  \n",
       "2     BRISCOE EL   AUSTIN H S  \n",
       "3     BRISCOE EL   AUSTIN H S  \n",
       "4     BRISCOE EL   AUSTIN H S  \n",
       "...          ...          ...  \n",
       "3236   WHIDBY EL    YATES H S  \n",
       "3237   WHIDBY EL    YATES H S  \n",
       "3238   WHIDBY EL    YATES H S  \n",
       "3239   WHIDBY EL    YATES H S  \n",
       "3240   WHIDBY EL    YATES H S  \n",
       "\n",
       "[3241 rows x 28 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# replace high school with h s to match TEA table\n",
    "customer_data_df = raw_har1_df.replace({\"high_school\" :\"HIGH SCHOOL\"}, \"H S\", regex = True)\n",
    "customer_data_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3241, 28)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total info\n",
    "customer_data_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Columns list\n",
    "customer_data_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Navigate a single column\n",
    "# customer_data_df['fireplaces']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace null values with \"0\"\n",
    "# customer_data_df['fireplaces']= customer_data_df['fireplaces'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check replacement\n",
    "# customer_data_df['fireplaces']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace null values with \"0\"\n",
    "# customer_data_df['garages']= customer_data_df['garages'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Checking unique values\n",
    "# customer_data_df.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking unique values\n",
    "customer_data_df.nunique().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# isna/isnull\n",
    "# customer_data_df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data types\n",
    "# customer_data_df.dtypes"
   ]
  },
  {
   "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>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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3236</th>\n",
       "      <td>52192821</td>\n",
       "      <td>3722</td>\n",
       "      <td>Goodhope</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1958</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>160000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2/3/2021</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3237</th>\n",
       "      <td>58992773</td>\n",
       "      <td>3722</td>\n",
       "      <td>Mount Pleasant #B</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>2021</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Contemporary/Modern</td>\n",
       "      <td>369998</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>129</td>\n",
       "      <td>129</td>\n",
       "      <td>9/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3238</th>\n",
       "      <td>63298093</td>\n",
       "      <td>3734</td>\n",
       "      <td>Seabrook</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1955</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Other Style</td>\n",
       "      <td>149900</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>87</td>\n",
       "      <td>87</td>\n",
       "      <td>10/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3239</th>\n",
       "      <td>9871530</td>\n",
       "      <td>3814</td>\n",
       "      <td>Seabrook St</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1968</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>179000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>12/30/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3240</th>\n",
       "      <td>9871043</td>\n",
       "      <td>3853</td>\n",
       "      <td>Goodhope</td>\n",
       "      <td>Houston</td>\n",
       "      <td>77021</td>\n",
       "      <td>Harris</td>\n",
       "      <td>South End Sunnyside U/R</td>\n",
       "      <td>Single-Family</td>\n",
       "      <td>1950</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>225000</td>\n",
       "      <td>4</td>\n",
       "      <td>University Area</td>\n",
       "      <td>87</td>\n",
       "      <td>87</td>\n",
       "      <td>11/11/2020</td>\n",
       "      <td>HOUSTON ISD</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3241 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls  street_number        street_name     city    zip  county  \\\n",
       "0     70161219           1747        Forest Hill  Houston  77023  Harris   \n",
       "1     17580127           1911           Pasadena  Houston  77023  Harris   \n",
       "2     35404452           1931         Santa Rosa  Houston  77023  Harris   \n",
       "3     12503971           2022        Forest Hill  Houston  77023  Harris   \n",
       "4     32263872           6707           Avenue I  Houston  77011  Harris   \n",
       "...        ...            ...                ...      ...    ...     ...   \n",
       "3236  52192821           3722           Goodhope  Houston  77021  Harris   \n",
       "3237  58992773           3722  Mount Pleasant #B  Houston  77021  Harris   \n",
       "3238  63298093           3734           Seabrook  Houston  77021  Harris   \n",
       "3239   9871530           3814        Seabrook St  Houston  77021  Harris   \n",
       "3240   9871043           3853           Goodhope  Houston  77021  Harris   \n",
       "\n",
       "                  subdivision      home_type  year_built  bedrooms  ...  \\\n",
       "0                 Forest Hill  Single-Family        1952         3  ...   \n",
       "1                 Forest Hill  Single-Family        1949         2  ...   \n",
       "2                 Forest Hill  Single-Family        1949         3  ...   \n",
       "3                 Forest Hill  Single-Family        1938         4  ...   \n",
       "4                Central Park  Single-Family        2020         3  ...   \n",
       "...                       ...            ...         ...       ...  ...   \n",
       "3236  South End Sunnyside U/R  Single-Family        1958         3  ...   \n",
       "3237  South End Sunnyside U/R  Single-Family        2021         3  ...   \n",
       "3238  South End Sunnyside U/R  Single-Family        1955         3  ...   \n",
       "3239  South End Sunnyside U/R  Single-Family        1968         4  ...   \n",
       "3240  South End Sunnyside U/R  Single-Family        1950         4  ...   \n",
       "\n",
       "                                 style  list_price  market_area  \\\n",
       "0                          Traditional      255000            4   \n",
       "1                          Traditional      324900            4   \n",
       "2     Contemporary/Modern, Traditional      319000            4   \n",
       "3                          Traditional      399000            4   \n",
       "4                          Traditional      280990            4   \n",
       "...                                ...         ...          ...   \n",
       "3236                       Traditional      160000            4   \n",
       "3237               Contemporary/Modern      369998            4   \n",
       "3238                       Other Style      149900            4   \n",
       "3239                       Traditional      179000            4   \n",
       "3240                       Traditional      225000            4   \n",
       "\n",
       "                      area  dom  cdom   list_date  school_district  \\\n",
       "0     East End Revitalized    5     5    2/1/2021      HOUSTON ISD   \n",
       "1     East End Revitalized   74    74  11/24/2020      HOUSTON ISD   \n",
       "2     East End Revitalized   53    53  12/11/2020      HOUSTON ISD   \n",
       "3     East End Revitalized    3     3    2/3/2021      HOUSTON ISD   \n",
       "4          University Area  134   251   9/25/2020      HOUSTON ISD   \n",
       "...                    ...  ...   ...         ...              ...   \n",
       "3236       University Area    3     3    2/3/2021      HOUSTON ISD   \n",
       "3237       University Area  129   129   9/30/2020      HOUSTON ISD   \n",
       "3238       University Area   87    87  10/30/2020      HOUSTON ISD   \n",
       "3239       University Area   34    34  12/30/2020      HOUSTON ISD   \n",
       "3240       University Area   87    87  11/11/2020      HOUSTON ISD   \n",
       "\n",
       "      elementary  high_school  \n",
       "0     BRISCOE EL   AUSTIN H S  \n",
       "1     BRISCOE EL   AUSTIN H S  \n",
       "2     BRISCOE EL   AUSTIN H S  \n",
       "3     BRISCOE EL   AUSTIN H S  \n",
       "4     BRISCOE EL   AUSTIN H S  \n",
       "...          ...          ...  \n",
       "3236   WHIDBY EL    YATES H S  \n",
       "3237   WHIDBY EL    YATES H S  \n",
       "3238   WHIDBY EL    YATES H S  \n",
       "3239   WHIDBY EL    YATES H S  \n",
       "3240   WHIDBY EL    YATES H S  \n",
       "\n",
       "[3241 rows x 28 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create new df\n",
    "HAR_df= customer_data_df\n",
    "HAR_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Writing to clean csv\n",
    "HAR_df.to_csv(\"./Resources/alt_clean_har.csv\", index = False)"
   ]
  },
  {
   "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>mls</th>\n",
       "      <th>school</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70161219</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17580127</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35404452</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12503971</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32263872</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3236</th>\n",
       "      <td>52192821</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3237</th>\n",
       "      <td>58992773</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3238</th>\n",
       "      <td>63298093</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3239</th>\n",
       "      <td>9871530</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3240</th>\n",
       "      <td>9871043</td>\n",
       "      <td>WHIDBY EL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3241 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls      school\n",
       "0     70161219  BRISCOE EL\n",
       "1     17580127  BRISCOE EL\n",
       "2     35404452  BRISCOE EL\n",
       "3     12503971  BRISCOE EL\n",
       "4     32263872  BRISCOE EL\n",
       "...        ...         ...\n",
       "3236  52192821   WHIDBY EL\n",
       "3237  58992773   WHIDBY EL\n",
       "3238  63298093   WHIDBY EL\n",
       "3239   9871530   WHIDBY EL\n",
       "3240   9871043   WHIDBY EL\n",
       "\n",
       "[3241 rows x 2 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating elementary school potion of junction table\n",
    "raw_mls_el_df = HAR_df[['mls','elementary']]\n",
    "mls_el_df= raw_mls_el_df.rename(columns= {'elementary': 'school'})\n",
    "mls_el_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>mls</th>\n",
       "      <th>school</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70161219</td>\n",
       "      <td>AUSTIN H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17580127</td>\n",
       "      <td>AUSTIN H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35404452</td>\n",
       "      <td>AUSTIN H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12503971</td>\n",
       "      <td>AUSTIN H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32263872</td>\n",
       "      <td>AUSTIN H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3236</th>\n",
       "      <td>52192821</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3237</th>\n",
       "      <td>58992773</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3238</th>\n",
       "      <td>63298093</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3239</th>\n",
       "      <td>9871530</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3240</th>\n",
       "      <td>9871043</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3241 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls      school\n",
       "0     70161219  AUSTIN H S\n",
       "1     17580127  AUSTIN H S\n",
       "2     35404452  AUSTIN H S\n",
       "3     12503971  AUSTIN H S\n",
       "4     32263872  AUSTIN H S\n",
       "...        ...         ...\n",
       "3236  52192821   YATES H S\n",
       "3237  58992773   YATES H S\n",
       "3238  63298093   YATES H S\n",
       "3239   9871530   YATES H S\n",
       "3240   9871043   YATES H S\n",
       "\n",
       "[3241 rows x 2 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating high school potion of junction table\n",
    "raw_mls_hs_df = HAR_df[['mls', 'high_school']]\n",
    "mls_hs_df= raw_mls_hs_df.rename(columns= {'high_school': 'school'})\n",
    "mls_hs_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>school</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70161219</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17580127</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35404452</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12503971</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32263872</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3236</th>\n",
       "      <td>52192821</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3237</th>\n",
       "      <td>58992773</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3238</th>\n",
       "      <td>63298093</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3239</th>\n",
       "      <td>9871530</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3240</th>\n",
       "      <td>9871043</td>\n",
       "      <td>YATES H S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6482 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls      school\n",
       "0     70161219  BRISCOE EL\n",
       "1     17580127  BRISCOE EL\n",
       "2     35404452  BRISCOE EL\n",
       "3     12503971  BRISCOE EL\n",
       "4     32263872  BRISCOE EL\n",
       "...        ...         ...\n",
       "3236  52192821   YATES H S\n",
       "3237  58992773   YATES H S\n",
       "3238  63298093   YATES H S\n",
       "3239   9871530   YATES H S\n",
       "3240   9871043   YATES H S\n",
       "\n",
       "[6482 rows x 2 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# concating HAR potion of junction table\n",
    "frames = [mls_el_df,mls_hs_df]\n",
    "all_mls_schools_df = pd.concat(frames)\n",
    "all_mls_schools_df.to_csv('./Resources/all_mls_schools.csv', index = False)\n",
    "all_mls_schools_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TEA Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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\\nNumber</th>\n",
       "      <th>Campus</th>\n",
       "      <th>District</th>\n",
       "      <th>ESC\\nRegion</th>\n",
       "      <th>Region</th>\n",
       "      <th>SBOE\\nDistrict</th>\n",
       "      <th># of Consecutive Years Campus is Academically Unacceptable</th>\n",
       "      <th>Campus 2020 Rating</th>\n",
       "      <th>Campus 2019 Rating</th>\n",
       "      <th>Campus 2018 Rating</th>\n",
       "      <th>Campus 2017 Rating</th>\n",
       "      <th>Campus 2016 Rating</th>\n",
       "      <th>Campus 2015 Rating</th>\n",
       "      <th>Campus 2014 Rating</th>\n",
       "      <th>Campus 2013 Rating</th>\n",
       "      <th>Campus 2012 Rating</th>\n",
       "      <th>Campus 2011 Rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>024901001</td>\n",
       "      <td>FALFURRIAS H S</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>REGION 01: EDINBURG</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Not Rated: Declared State of Disaster</td>\n",
       "      <td>B</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Academically Unacceptable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>024901041</td>\n",
       "      <td>FALFURRIAS J H</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>REGION 01: EDINBURG</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Not Rated: Declared State of Disaster</td>\n",
       "      <td>F</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Improvement Required</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Academically Unacceptable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>024901101</td>\n",
       "      <td>FALFURRIAS EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>REGION 01: EDINBURG</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Not Rated: Declared State of Disaster</td>\n",
       "      <td>C</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Academically Acceptable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>024901103</td>\n",
       "      <td>LASATER EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>REGION 01: EDINBURG</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Not Rated: Declared State of Disaster</td>\n",
       "      <td>C</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>Met Standard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Academically Acceptable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>024901105</td>\n",
       "      <td>FALFURRIAS INNOVATION ACADEMY</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>REGION 01: EDINBURG</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Not Rated: Declared State of Disaster</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>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Campus\\nNumber                         Campus           District  \\\n",
       "0      024901001                 FALFURRIAS H S  BROOKS COUNTY ISD   \n",
       "1      024901041                 FALFURRIAS J H  BROOKS COUNTY ISD   \n",
       "2      024901101                  FALFURRIAS EL  BROOKS COUNTY ISD   \n",
       "3      024901103                     LASATER EL  BROOKS COUNTY ISD   \n",
       "4      024901105  FALFURRIAS INNOVATION ACADEMY  BROOKS COUNTY ISD   \n",
       "\n",
       "   ESC\\nRegion               Region  SBOE\\nDistrict  \\\n",
       "0          1.0  REGION 01: EDINBURG             3.0   \n",
       "1          1.0  REGION 01: EDINBURG             3.0   \n",
       "2          1.0  REGION 01: EDINBURG             3.0   \n",
       "3          1.0  REGION 01: EDINBURG             3.0   \n",
       "4          1.0  REGION 01: EDINBURG             3.0   \n",
       "\n",
       "   # of Consecutive Years Campus is Academically Unacceptable  \\\n",
       "0                                                0.0            \n",
       "1                                                1.0            \n",
       "2                                                0.0            \n",
       "3                                                0.0            \n",
       "4                                                0.0            \n",
       "\n",
       "                      Campus 2020 Rating Campus 2019 Rating  \\\n",
       "0  Not Rated: Declared State of Disaster                  B   \n",
       "1  Not Rated: Declared State of Disaster                  F   \n",
       "2  Not Rated: Declared State of Disaster                  C   \n",
       "3  Not Rated: Declared State of Disaster                  C   \n",
       "4  Not Rated: Declared State of Disaster                NaN   \n",
       "\n",
       "  Campus 2018 Rating Campus 2017 Rating Campus 2016 Rating  \\\n",
       "0       Met Standard       Met Standard       Met Standard   \n",
       "1       Met Standard       Met Standard       Met Standard   \n",
       "2       Met Standard       Met Standard       Met Standard   \n",
       "3       Met Standard       Met Standard       Met Standard   \n",
       "4                NaN                NaN                NaN   \n",
       "\n",
       "     Campus 2015 Rating Campus 2014 Rating Campus 2013 Rating  \\\n",
       "0          Met Standard       Met Standard       Met Standard   \n",
       "1  Improvement Required       Met Standard       Met Standard   \n",
       "2          Met Standard       Met Standard       Met Standard   \n",
       "3          Met Standard       Met Standard       Met Standard   \n",
       "4                   NaN                NaN                NaN   \n",
       "\n",
       "   Campus 2012 Rating         Campus 2011 Rating  \n",
       "0                 NaN  Academically Unacceptable  \n",
       "1                 NaN  Academically Unacceptable  \n",
       "2                 NaN    Academically Acceptable  \n",
       "3                 NaN    Academically Acceptable  \n",
       "4                 NaN                        NaN  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Store csv into df\n",
    "csv_file= \"./Resources/alt_school_rating_list_tea.csv\"\n",
    "raw_school_rating_df= pd.read_csv(csv_file)\n",
    "raw_school_rating_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Campus\\nNumber</th>\n",
       "      <th>Campus</th>\n",
       "      <th>District</th>\n",
       "      <th># of Consecutive Years Campus is Academically Unacceptable</th>\n",
       "      <th>Campus 2019 Rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>024901001</td>\n",
       "      <td>FALFURRIAS H S</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>024901041</td>\n",
       "      <td>FALFURRIAS J H</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>024901101</td>\n",
       "      <td>FALFURRIAS EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>024901103</td>\n",
       "      <td>LASATER EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>024901105</td>\n",
       "      <td>FALFURRIAS INNOVATION ACADEMY</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8866</th>\n",
       "      <td>232903110</td>\n",
       "      <td>FLORES EL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8867</th>\n",
       "      <td>* n/a:  Rating is not available.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8868</th>\n",
       "      <td>* 2011 is the last year for the accountability...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8869</th>\n",
       "      <td>* No state accountability ratings were assigne...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8870</th>\n",
       "      <td>* State accountability ratings were changed to...</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>8871 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Campus\\nNumber  \\\n",
       "0                                             024901001   \n",
       "1                                             024901041   \n",
       "2                                             024901101   \n",
       "3                                             024901103   \n",
       "4                                             024901105   \n",
       "...                                                 ...   \n",
       "8866                                          232903110   \n",
       "8867                   * n/a:  Rating is not available.   \n",
       "8868  * 2011 is the last year for the accountability...   \n",
       "8869  * No state accountability ratings were assigne...   \n",
       "8870  * State accountability ratings were changed to...   \n",
       "\n",
       "                             Campus           District  \\\n",
       "0                    FALFURRIAS H S  BROOKS COUNTY ISD   \n",
       "1                    FALFURRIAS J H  BROOKS COUNTY ISD   \n",
       "2                     FALFURRIAS EL  BROOKS COUNTY ISD   \n",
       "3                        LASATER EL  BROOKS COUNTY ISD   \n",
       "4     FALFURRIAS INNOVATION ACADEMY  BROOKS COUNTY ISD   \n",
       "...                             ...                ...   \n",
       "8866                      FLORES EL        UVALDE CISD   \n",
       "8867                            NaN                NaN   \n",
       "8868                            NaN                NaN   \n",
       "8869                            NaN                NaN   \n",
       "8870                            NaN                NaN   \n",
       "\n",
       "      # of Consecutive Years Campus is Academically Unacceptable  \\\n",
       "0                                                   0.0            \n",
       "1                                                   1.0            \n",
       "2                                                   0.0            \n",
       "3                                                   0.0            \n",
       "4                                                   0.0            \n",
       "...                                                 ...            \n",
       "8866                                                0.0            \n",
       "8867                                                NaN            \n",
       "8868                                                NaN            \n",
       "8869                                                NaN            \n",
       "8870                                                NaN            \n",
       "\n",
       "     Campus 2019 Rating  \n",
       "0                     B  \n",
       "1                     F  \n",
       "2                     C  \n",
       "3                     C  \n",
       "4                   NaN  \n",
       "...                 ...  \n",
       "8866                  C  \n",
       "8867                NaN  \n",
       "8868                NaN  \n",
       "8869                NaN  \n",
       "8870                NaN  \n",
       "\n",
       "[8871 rows x 5 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# removing columns not needed\n",
    "raw_school_rating1_df = raw_school_rating_df.drop(['ESC\\nRegion', 'Region', 'SBOE\\nDistrict','Campus 2020 Rating',\n",
    "                                                  'Campus 2018 Rating', 'Campus 2017 Rating','Campus 2016 Rating',\n",
    "                                                  'Campus 2015 Rating', 'Campus 2014 Rating','Campus 2013 Rating',\n",
    "                                                  'Campus 2012 Rating','Campus 2011 Rating'], axis =1)\n",
    "raw_school_rating1_df"
   ]
  },
  {
   "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>campus_number</th>\n",
       "      <th>school</th>\n",
       "      <th>district</th>\n",
       "      <th>yrs_unacceptable</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>024901001</td>\n",
       "      <td>FALFURRIAS H S</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>024901041</td>\n",
       "      <td>FALFURRIAS J H</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>024901101</td>\n",
       "      <td>FALFURRIAS EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>024901103</td>\n",
       "      <td>LASATER EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>024901105</td>\n",
       "      <td>FALFURRIAS INNOVATION ACADEMY</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8866</th>\n",
       "      <td>232903110</td>\n",
       "      <td>FLORES EL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8867</th>\n",
       "      <td>* n/a:  Rating is not available.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8868</th>\n",
       "      <td>* 2011 is the last year for the accountability...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8869</th>\n",
       "      <td>* No state accountability ratings were assigne...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8870</th>\n",
       "      <td>* State accountability ratings were changed to...</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>8871 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          campus_number  \\\n",
       "0                                             024901001   \n",
       "1                                             024901041   \n",
       "2                                             024901101   \n",
       "3                                             024901103   \n",
       "4                                             024901105   \n",
       "...                                                 ...   \n",
       "8866                                          232903110   \n",
       "8867                   * n/a:  Rating is not available.   \n",
       "8868  * 2011 is the last year for the accountability...   \n",
       "8869  * No state accountability ratings were assigne...   \n",
       "8870  * State accountability ratings were changed to...   \n",
       "\n",
       "                             school           district  yrs_unacceptable  \\\n",
       "0                    FALFURRIAS H S  BROOKS COUNTY ISD               0.0   \n",
       "1                    FALFURRIAS J H  BROOKS COUNTY ISD               1.0   \n",
       "2                     FALFURRIAS EL  BROOKS COUNTY ISD               0.0   \n",
       "3                        LASATER EL  BROOKS COUNTY ISD               0.0   \n",
       "4     FALFURRIAS INNOVATION ACADEMY  BROOKS COUNTY ISD               0.0   \n",
       "...                             ...                ...               ...   \n",
       "8866                      FLORES EL        UVALDE CISD               0.0   \n",
       "8867                            NaN                NaN               NaN   \n",
       "8868                            NaN                NaN               NaN   \n",
       "8869                            NaN                NaN               NaN   \n",
       "8870                            NaN                NaN               NaN   \n",
       "\n",
       "     rating  \n",
       "0         B  \n",
       "1         F  \n",
       "2         C  \n",
       "3         C  \n",
       "4       NaN  \n",
       "...     ...  \n",
       "8866      C  \n",
       "8867    NaN  \n",
       "8868    NaN  \n",
       "8869    NaN  \n",
       "8870    NaN  \n",
       "\n",
       "[8871 rows x 5 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Renaming columns - int is for intermediate (in process) table\n",
    "int_school_rating_df= raw_school_rating1_df.rename(columns= {'Campus\\nNumber': 'campus_number',\n",
    "                                                            'Campus' : 'school',\n",
    "                                                            'District' : 'district',\n",
    "                                                          '# of Consecutive Years Campus is Academically Unacceptable' : 'yrs_unacceptable',\n",
    "                                                          'Campus 2019 Rating' : 'rating'})\n",
    "int_school_rating_df"
   ]
  },
  {
   "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>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>024901001</td>\n",
       "      <td>FALFURRIAS H S</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>024901041</td>\n",
       "      <td>FALFURRIAS J H</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>024901101</td>\n",
       "      <td>FALFURRIAS EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>024901103</td>\n",
       "      <td>LASATER EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>024901105</td>\n",
       "      <td>FALFURRIAS INNOVATION ACADEMY</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>8866</th>\n",
       "      <td>232903110</td>\n",
       "      <td>FLORES EL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8867</th>\n",
       "      <td>* n/a:  Rating is not available.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8868</th>\n",
       "      <td>* 2011 is the last year for the accountability...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8869</th>\n",
       "      <td>* No state accountability ratings were assigne...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8870</th>\n",
       "      <td>* State accountability ratings were changed to...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8871 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          campus_number  \\\n",
       "0                                             024901001   \n",
       "1                                             024901041   \n",
       "2                                             024901101   \n",
       "3                                             024901103   \n",
       "4                                             024901105   \n",
       "...                                                 ...   \n",
       "8866                                          232903110   \n",
       "8867                   * n/a:  Rating is not available.   \n",
       "8868  * 2011 is the last year for the accountability...   \n",
       "8869  * No state accountability ratings were assigne...   \n",
       "8870  * State accountability ratings were changed to...   \n",
       "\n",
       "                             school           district  yrs_unacceptable  \\\n",
       "0                    FALFURRIAS H S  BROOKS COUNTY ISD               0.0   \n",
       "1                    FALFURRIAS J H  BROOKS COUNTY ISD               1.0   \n",
       "2                     FALFURRIAS EL  BROOKS COUNTY ISD               0.0   \n",
       "3                        LASATER EL  BROOKS COUNTY ISD               0.0   \n",
       "4     FALFURRIAS INNOVATION ACADEMY  BROOKS COUNTY ISD               0.0   \n",
       "...                             ...                ...               ...   \n",
       "8866                      FLORES EL        UVALDE CISD               0.0   \n",
       "8867                            NaN                NaN               NaN   \n",
       "8868                            NaN                NaN               NaN   \n",
       "8869                            NaN                NaN               NaN   \n",
       "8870                            NaN                NaN               NaN   \n",
       "\n",
       "     rating  year  \n",
       "0         B  2019  \n",
       "1         F  2019  \n",
       "2         C  2019  \n",
       "3         C  2019  \n",
       "4       NaN  2019  \n",
       "...     ...   ...  \n",
       "8866      C  2019  \n",
       "8867    NaN  2019  \n",
       "8868    NaN  2019  \n",
       "8869    NaN  2019  \n",
       "8870    NaN  2019  \n",
       "\n",
       "[8871 rows x 6 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adding in new column and associated value\n",
    "int_school_rating_df['year']= 2019\n",
    "int_school_rating_df"
   ]
  },
  {
   "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>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>024901001</td>\n",
       "      <td>FALFURRIAS H S</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>024901041</td>\n",
       "      <td>FALFURRIAS J H</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>024901101</td>\n",
       "      <td>FALFURRIAS EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>024901103</td>\n",
       "      <td>LASATER EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>024901105</td>\n",
       "      <td>FALFURRIAS INNOVATION ACADEMY</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>8862</th>\n",
       "      <td>232903102</td>\n",
       "      <td>DALTON EL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8863</th>\n",
       "      <td>232903103</td>\n",
       "      <td>ROBB EL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>2.0</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8864</th>\n",
       "      <td>232903104</td>\n",
       "      <td>ANTHON EL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8865</th>\n",
       "      <td>232903108</td>\n",
       "      <td>BATESVILLE SCHOOL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8866</th>\n",
       "      <td>232903110</td>\n",
       "      <td>FLORES EL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8867 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     campus_number                         school           district  \\\n",
       "0        024901001                 FALFURRIAS H S  BROOKS COUNTY ISD   \n",
       "1        024901041                 FALFURRIAS J H  BROOKS COUNTY ISD   \n",
       "2        024901101                  FALFURRIAS EL  BROOKS COUNTY ISD   \n",
       "3        024901103                     LASATER EL  BROOKS COUNTY ISD   \n",
       "4        024901105  FALFURRIAS INNOVATION ACADEMY  BROOKS COUNTY ISD   \n",
       "...            ...                            ...                ...   \n",
       "8862     232903102                      DALTON EL        UVALDE CISD   \n",
       "8863     232903103                        ROBB EL        UVALDE CISD   \n",
       "8864     232903104                      ANTHON EL        UVALDE CISD   \n",
       "8865     232903108              BATESVILLE SCHOOL        UVALDE CISD   \n",
       "8866     232903110                      FLORES EL        UVALDE CISD   \n",
       "\n",
       "      yrs_unacceptable rating  year  \n",
       "0                  0.0      B  2019  \n",
       "1                  1.0      F  2019  \n",
       "2                  0.0      C  2019  \n",
       "3                  0.0      C  2019  \n",
       "4                  0.0    NaN  2019  \n",
       "...                ...    ...   ...  \n",
       "8862               0.0      C  2019  \n",
       "8863               2.0      F  2019  \n",
       "8864               0.0      C  2019  \n",
       "8865               1.0      F  2019  \n",
       "8866               0.0      C  2019  \n",
       "\n",
       "[8867 rows x 6 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dropping rows that contain notes, i.e. start with *\n",
    "int_school_ratinga_df = int_school_rating_df.drop(int_school_rating_df.loc[int_school_rating_df.campus_number.str.startswith(\"*\")].index)\n",
    "int_school_ratinga_df"
   ]
  },
  {
   "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>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>024901001</td>\n",
       "      <td>FALFURRIAS H S</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>024901041</td>\n",
       "      <td>FALFURRIAS J H</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>024901101</td>\n",
       "      <td>FALFURRIAS EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>024901103</td>\n",
       "      <td>LASATER EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>024901105</td>\n",
       "      <td>FALFURRIAS INNOVATION ACADEMY</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>8862</th>\n",
       "      <td>232903102</td>\n",
       "      <td>DALTON EL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8863</th>\n",
       "      <td>232903103</td>\n",
       "      <td>ROBB EL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>2.0</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8864</th>\n",
       "      <td>232903104</td>\n",
       "      <td>ANTHON EL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8865</th>\n",
       "      <td>232903108</td>\n",
       "      <td>BATESVILLE SCHOOL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8866</th>\n",
       "      <td>232903110</td>\n",
       "      <td>FLORES EL</td>\n",
       "      <td>UVALDE CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7796 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     campus_number                         school           district  \\\n",
       "0        024901001                 FALFURRIAS H S  BROOKS COUNTY ISD   \n",
       "1        024901041                 FALFURRIAS J H  BROOKS COUNTY ISD   \n",
       "2        024901101                  FALFURRIAS EL  BROOKS COUNTY ISD   \n",
       "3        024901103                     LASATER EL  BROOKS COUNTY ISD   \n",
       "4        024901105  FALFURRIAS INNOVATION ACADEMY  BROOKS COUNTY ISD   \n",
       "...            ...                            ...                ...   \n",
       "8862     232903102                      DALTON EL        UVALDE CISD   \n",
       "8863     232903103                        ROBB EL        UVALDE CISD   \n",
       "8864     232903104                      ANTHON EL        UVALDE CISD   \n",
       "8865     232903108              BATESVILLE SCHOOL        UVALDE CISD   \n",
       "8866     232903110                      FLORES EL        UVALDE CISD   \n",
       "\n",
       "      yrs_unacceptable rating  year  \n",
       "0                  0.0      B  2019  \n",
       "1                  1.0      F  2019  \n",
       "2                  0.0      C  2019  \n",
       "3                  0.0      C  2019  \n",
       "4                  0.0    NaN  2019  \n",
       "...                ...    ...   ...  \n",
       "8862               0.0      C  2019  \n",
       "8863               2.0      F  2019  \n",
       "8864               0.0      C  2019  \n",
       "8865               1.0      F  2019  \n",
       "8866               0.0      C  2019  \n",
       "\n",
       "[7796 rows x 6 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dropping rows that contain middle school to match HAR data\n",
    "int_school_rating1_df = int_school_ratinga_df.drop(int_school_ratinga_df.loc[int_school_ratinga_df.school.str.contains(\"MIDDLE\")].index)\n",
    "int_school_rating1_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "campus_number         0\n",
       "school                0\n",
       "district              0\n",
       "yrs_unacceptable      0\n",
       "rating              187\n",
       "year                  0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking for NANs\n",
    "int_school_rating1_df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filling NaNs\n",
    "int_school_rating1_df = int_school_rating1_df.fillna(\"Not Rated\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "campus_number       0\n",
       "school              0\n",
       "district            0\n",
       "yrs_unacceptable    0\n",
       "rating              0\n",
       "year                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking for NANs\n",
    "int_school_rating1_df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['B', 'F', 'C', 'Not Rated', 'A', 'D',\n",
       "       'Not Rated: Minimum Size rules', 'Not rated: Data Integrity Issue'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# looking for unique items in column\n",
    "int_school_rating1_df.rating.unique()"
   ]
  },
  {
   "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>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>024901001</td>\n",
       "      <td>FALFURRIAS H S</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>024901041</td>\n",
       "      <td>FALFURRIAS J H</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>F</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>024901101</td>\n",
       "      <td>FALFURRIAS EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>024901103</td>\n",
       "      <td>LASATER EL</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>024901105</td>\n",
       "      <td>FALFURRIAS INNOVATION ACADEMY</td>\n",
       "      <td>BROOKS COUNTY ISD</td>\n",
       "      <td>0.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  \\\n",
       "0     024901001                 FALFURRIAS H S  BROOKS COUNTY ISD   \n",
       "1     024901041                 FALFURRIAS J H  BROOKS COUNTY ISD   \n",
       "2     024901101                  FALFURRIAS EL  BROOKS COUNTY ISD   \n",
       "3     024901103                     LASATER EL  BROOKS COUNTY ISD   \n",
       "4     024901105  FALFURRIAS INNOVATION ACADEMY  BROOKS COUNTY ISD   \n",
       "\n",
       "   yrs_unacceptable     rating  year  \n",
       "0               0.0          B  2019  \n",
       "1               1.0          F  2019  \n",
       "2               0.0          C  2019  \n",
       "3               0.0          C  2019  \n",
       "4               0.0  Not Rated  2019  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# replacing to get like wording \n",
    "int_school_rating2_df = int_school_rating1_df.replace({\"rating\" :\"Not Rated: Minimum Size rules\"},\"Not Rated\", regex = True)\n",
    "int_school_rating3_df = int_school_rating2_df.replace({\"rating\" :\"Not rated: Data Integrity Issue\"},\"Not Rated\", regex = True)\n",
    "int_school_rating3_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['B', 'F', 'C', 'Not Rated', 'A', 'D'], dtype=object)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# verifying like wording\n",
    "int_school_rating3_df.rating.unique()"
   ]
  },
  {
   "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>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>1318</th>\n",
       "      <td>101907002</td>\n",
       "      <td>CY-FAIR H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1319</th>\n",
       "      <td>101907003</td>\n",
       "      <td>JERSEY VILLAGE H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1320</th>\n",
       "      <td>101907004</td>\n",
       "      <td>CYPRESS CREEK H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1321</th>\n",
       "      <td>101907005</td>\n",
       "      <td>LANGHAM CREEK H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1322</th>\n",
       "      <td>101907007</td>\n",
       "      <td>CYPRESS FALLS H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</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>2389</th>\n",
       "      <td>101920128</td>\n",
       "      <td>THE WILDCAT WAY SCHOOL</td>\n",
       "      <td>SPRING BRANCH ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2390</th>\n",
       "      <td>101920129</td>\n",
       "      <td>THE PANDA PATH SCHOOL</td>\n",
       "      <td>SPRING BRANCH ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2391</th>\n",
       "      <td>101920130</td>\n",
       "      <td>THE LION LANE SCHOOL</td>\n",
       "      <td>SPRING BRANCH ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2392</th>\n",
       "      <td>101920131</td>\n",
       "      <td>THE BEAR BLVD SCHOOL</td>\n",
       "      <td>SPRING BRANCH ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2393</th>\n",
       "      <td>101920132</td>\n",
       "      <td>THE TIGER TRAIL SCHOOL</td>\n",
       "      <td>SPRING BRANCH ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>454 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     campus_number                  school               district  \\\n",
       "1318     101907002             CY-FAIR H S  CYPRESS-FAIRBANKS ISD   \n",
       "1319     101907003      JERSEY VILLAGE H S  CYPRESS-FAIRBANKS ISD   \n",
       "1320     101907004       CYPRESS CREEK H S  CYPRESS-FAIRBANKS ISD   \n",
       "1321     101907005       LANGHAM CREEK H S  CYPRESS-FAIRBANKS ISD   \n",
       "1322     101907007       CYPRESS FALLS H S  CYPRESS-FAIRBANKS ISD   \n",
       "...            ...                     ...                    ...   \n",
       "2389     101920128  THE WILDCAT WAY SCHOOL      SPRING BRANCH ISD   \n",
       "2390     101920129   THE PANDA PATH SCHOOL      SPRING BRANCH ISD   \n",
       "2391     101920130    THE LION LANE SCHOOL      SPRING BRANCH ISD   \n",
       "2392     101920131    THE BEAR BLVD SCHOOL      SPRING BRANCH ISD   \n",
       "2393     101920132  THE TIGER TRAIL SCHOOL      SPRING BRANCH ISD   \n",
       "\n",
       "      yrs_unacceptable rating  year  \n",
       "1318               0.0      A  2019  \n",
       "1319               0.0      B  2019  \n",
       "1320               0.0      B  2019  \n",
       "1321               0.0      A  2019  \n",
       "1322               0.0      B  2019  \n",
       "...                ...    ...   ...  \n",
       "2389               0.0      A  2019  \n",
       "2390               0.0      B  2019  \n",
       "2391               0.0      B  2019  \n",
       "2392               0.0      A  2019  \n",
       "2393               0.0      C  2019  \n",
       "\n",
       "[454 rows x 6 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# building dataframe for customer target area\n",
    "int_school_rating4_df = int_school_rating3_df.loc[(int_school_rating_df['district'] == \"HOUSTON ISD\")|\n",
    "                                               (int_school_rating_df['district'] == \"KATY ISD\") |\n",
    "                                               (int_school_rating_df['district'] == \"SHELDON ISD\") |\n",
    "                                               (int_school_rating_df['district'] == \"SPRING BRANCH ISD\") |\n",
    "                                               (int_school_rating_df['district'] == \"GALENA PARK ISD\") |\n",
    "                                               (int_school_rating_df['district'] == \"CYPRESS-FAIRBANKS ISD\") \n",
    "                                               ]\n",
    "int_school_rating4_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# adding high schools that are on edge of target area (outside of ISD) but still gets kids because of zoning\n",
    "new_df = int_school_rating3_df.loc[(int_school_rating3_df['campus_number'] =='101911002')]\n",
    "new1_df = int_school_rating3_df.loc[(int_school_rating3_df['campus_number'] =='101902003')]\n",
    "new2_df = int_school_rating3_df.loc[(int_school_rating3_df['campus_number'] =='101919001')]                               "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>101907002</td>\n",
       "      <td>CY-FAIR H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101907003</td>\n",
       "      <td>JERSEY VILLAGE H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101907004</td>\n",
       "      <td>CYPRESS CREEK H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101907005</td>\n",
       "      <td>LANGHAM CREEK H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101907007</td>\n",
       "      <td>CYPRESS FALLS H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</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>452</th>\n",
       "      <td>101920131</td>\n",
       "      <td>THE BEAR BLVD SCHOOL</td>\n",
       "      <td>SPRING BRANCH ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>101920132</td>\n",
       "      <td>THE TIGER TRAIL SCHOOL</td>\n",
       "      <td>SPRING BRANCH ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454</th>\n",
       "      <td>101911002</td>\n",
       "      <td>LEE H S</td>\n",
       "      <td>GOOSE CREEK CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>101902003</td>\n",
       "      <td>MACARTHUR H S</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>101919001</td>\n",
       "      <td>SPRING H S</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>457 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    campus_number                  school               district  \\\n",
       "0       101907002             CY-FAIR H S  CYPRESS-FAIRBANKS ISD   \n",
       "1       101907003      JERSEY VILLAGE H S  CYPRESS-FAIRBANKS ISD   \n",
       "2       101907004       CYPRESS CREEK H S  CYPRESS-FAIRBANKS ISD   \n",
       "3       101907005       LANGHAM CREEK H S  CYPRESS-FAIRBANKS ISD   \n",
       "4       101907007       CYPRESS FALLS H S  CYPRESS-FAIRBANKS ISD   \n",
       "..            ...                     ...                    ...   \n",
       "452     101920131    THE BEAR BLVD SCHOOL      SPRING BRANCH ISD   \n",
       "453     101920132  THE TIGER TRAIL SCHOOL      SPRING BRANCH ISD   \n",
       "454     101911002                 LEE H S       GOOSE CREEK CISD   \n",
       "455     101902003           MACARTHUR H S             ALDINE ISD   \n",
       "456     101919001              SPRING H S             SPRING ISD   \n",
       "\n",
       "     yrs_unacceptable rating  year  \n",
       "0                 0.0      A  2019  \n",
       "1                 0.0      B  2019  \n",
       "2                 0.0      B  2019  \n",
       "3                 0.0      A  2019  \n",
       "4                 0.0      B  2019  \n",
       "..                ...    ...   ...  \n",
       "452               0.0      A  2019  \n",
       "453               0.0      C  2019  \n",
       "454               0.0      B  2019  \n",
       "455               0.0      C  2019  \n",
       "456               0.0      C  2019  \n",
       "\n",
       "[457 rows x 6 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# adding schools to df\n",
    "int_school_rating5_df = int_school_rating4_df.append(new_df, ignore_index=True)\n",
    "int_school_rating6_df = int_school_rating5_df.append(new1_df, ignore_index=True)\n",
    "int_school_rating7_df = int_school_rating6_df.append(new2_df, ignore_index=True)\n",
    "int_school_rating7_df"
   ]
  },
  {
   "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>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>101907002</td>\n",
       "      <td>CY-FAIR H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101907003</td>\n",
       "      <td>JERSEY VILLAGE H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101907004</td>\n",
       "      <td>CYPRESS CREEK H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101907005</td>\n",
       "      <td>LANGHAM CREEK H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101907007</td>\n",
       "      <td>CYPRESS FALLS H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</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>452</th>\n",
       "      <td>101920131</td>\n",
       "      <td>THE BEAR BLVD SCHOOL</td>\n",
       "      <td>SPRING BRANCH ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>101920132</td>\n",
       "      <td>THE TIGER TRAIL SCHOOL</td>\n",
       "      <td>SPRING BRANCH ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454</th>\n",
       "      <td>101911002</td>\n",
       "      <td>LEE H S</td>\n",
       "      <td>GOOSE CREEK CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>101902003</td>\n",
       "      <td>MACARTHUR H S</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>101919001</td>\n",
       "      <td>SPRING H S</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>456 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    campus_number                  school               district  \\\n",
       "0       101907002             CY-FAIR H S  CYPRESS-FAIRBANKS ISD   \n",
       "1       101907003      JERSEY VILLAGE H S  CYPRESS-FAIRBANKS ISD   \n",
       "2       101907004       CYPRESS CREEK H S  CYPRESS-FAIRBANKS ISD   \n",
       "3       101907005       LANGHAM CREEK H S  CYPRESS-FAIRBANKS ISD   \n",
       "4       101907007       CYPRESS FALLS H S  CYPRESS-FAIRBANKS ISD   \n",
       "..            ...                     ...                    ...   \n",
       "452     101920131    THE BEAR BLVD SCHOOL      SPRING BRANCH ISD   \n",
       "453     101920132  THE TIGER TRAIL SCHOOL      SPRING BRANCH ISD   \n",
       "454     101911002                 LEE H S       GOOSE CREEK CISD   \n",
       "455     101902003           MACARTHUR H S             ALDINE ISD   \n",
       "456     101919001              SPRING H S             SPRING ISD   \n",
       "\n",
       "     yrs_unacceptable rating  year  \n",
       "0                 0.0      A  2019  \n",
       "1                 0.0      B  2019  \n",
       "2                 0.0      B  2019  \n",
       "3                 0.0      A  2019  \n",
       "4                 0.0      B  2019  \n",
       "..                ...    ...   ...  \n",
       "452               0.0      A  2019  \n",
       "453               0.0      C  2019  \n",
       "454               0.0      B  2019  \n",
       "455               0.0      C  2019  \n",
       "456               0.0      C  2019  \n",
       "\n",
       "[456 rows x 6 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# removing elementary school that is not in the target area ISD - it was causing conflict with primary key\n",
    "int_school_rating8_df = int_school_rating7_df[int_school_rating7_df.campus_number != '101907152']\n",
    "int_school_rating8_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>101907002</td>\n",
       "      <td>CY-FAIR H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101907003</td>\n",
       "      <td>JERSEY VILLAGE H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101907004</td>\n",
       "      <td>CYPRESS CREEK H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101907005</td>\n",
       "      <td>LANGHAM CREEK H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101907007</td>\n",
       "      <td>CYPRESS FALLS H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</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>452</th>\n",
       "      <td>101920131</td>\n",
       "      <td>THE BEAR BLVD SCHOOL</td>\n",
       "      <td>SPRING BRANCH ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>101920132</td>\n",
       "      <td>THE TIGER TRAIL SCHOOL</td>\n",
       "      <td>SPRING BRANCH ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454</th>\n",
       "      <td>101911002</td>\n",
       "      <td>LEE H S</td>\n",
       "      <td>GOOSE CREEK CISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>101902003</td>\n",
       "      <td>MACARTHUR H S</td>\n",
       "      <td>ALDINE ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>101919001</td>\n",
       "      <td>SPRING H S</td>\n",
       "      <td>SPRING ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>456 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    campus_number                  school               district  \\\n",
       "0       101907002             CY-FAIR H S  CYPRESS-FAIRBANKS ISD   \n",
       "1       101907003      JERSEY VILLAGE H S  CYPRESS-FAIRBANKS ISD   \n",
       "2       101907004       CYPRESS CREEK H S  CYPRESS-FAIRBANKS ISD   \n",
       "3       101907005       LANGHAM CREEK H S  CYPRESS-FAIRBANKS ISD   \n",
       "4       101907007       CYPRESS FALLS H S  CYPRESS-FAIRBANKS ISD   \n",
       "..            ...                     ...                    ...   \n",
       "452     101920131    THE BEAR BLVD SCHOOL      SPRING BRANCH ISD   \n",
       "453     101920132  THE TIGER TRAIL SCHOOL      SPRING BRANCH ISD   \n",
       "454     101911002                 LEE H S       GOOSE CREEK CISD   \n",
       "455     101902003           MACARTHUR H S             ALDINE ISD   \n",
       "456     101919001              SPRING H S             SPRING ISD   \n",
       "\n",
       "     yrs_unacceptable rating  year  \n",
       "0                 0.0      A  2019  \n",
       "1                 0.0      B  2019  \n",
       "2                 0.0      B  2019  \n",
       "3                 0.0      A  2019  \n",
       "4                 0.0      B  2019  \n",
       "..                ...    ...   ...  \n",
       "452               0.0      A  2019  \n",
       "453               0.0      C  2019  \n",
       "454               0.0      B  2019  \n",
       "455               0.0      C  2019  \n",
       "456               0.0      C  2019  \n",
       "\n",
       "[456 rows x 6 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating duplicate df to prevent corruption of original df\n",
    "school_rating_df= int_school_rating8_df\n",
    "school_rating_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Writing to clean csv\n",
    "school_rating_df.to_csv('./Resources/alt_clean_ratings.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101907002</td>\n",
       "      <td>CY-FAIR H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101907003</td>\n",
       "      <td>JERSEY VILLAGE H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101907004</td>\n",
       "      <td>CYPRESS CREEK H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101907005</td>\n",
       "      <td>LANGHAM CREEK H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101907007</td>\n",
       "      <td>CYPRESS FALLS H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>101920131</td>\n",
       "      <td>THE BEAR BLVD SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>101920132</td>\n",
       "      <td>THE TIGER TRAIL SCHOOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454</th>\n",
       "      <td>101911002</td>\n",
       "      <td>LEE H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>101902003</td>\n",
       "      <td>MACARTHUR H S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>101919001</td>\n",
       "      <td>SPRING H S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>456 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    campus_number                  school\n",
       "0       101907002             CY-FAIR H S\n",
       "1       101907003      JERSEY VILLAGE H S\n",
       "2       101907004       CYPRESS CREEK H S\n",
       "3       101907005       LANGHAM CREEK H S\n",
       "4       101907007       CYPRESS FALLS H S\n",
       "..            ...                     ...\n",
       "452     101920131    THE BEAR BLVD SCHOOL\n",
       "453     101920132  THE TIGER TRAIL SCHOOL\n",
       "454     101911002                 LEE H S\n",
       "455     101902003           MACARTHUR H S\n",
       "456     101919001              SPRING H S\n",
       "\n",
       "[456 rows x 2 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_cnum_school_df = school_rating_df[['campus_number','school']]\n",
    "raw_cnum_school_df .to_csv(\"./Resources/raw_cnum_school.csv\", index = False)\n",
    "raw_cnum_school_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>school</th>\n",
       "      <th>campus_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70161219</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "      <td>101912117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17580127</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "      <td>101912117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35404452</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "      <td>101912117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12503971</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "      <td>101912117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32263872</td>\n",
       "      <td>BRISCOE EL</td>\n",
       "      <td>101912117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6477</th>\n",
       "      <td>52192821</td>\n",
       "      <td>YATES H S</td>\n",
       "      <td>101912020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6478</th>\n",
       "      <td>58992773</td>\n",
       "      <td>YATES H S</td>\n",
       "      <td>101912020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6479</th>\n",
       "      <td>63298093</td>\n",
       "      <td>YATES H S</td>\n",
       "      <td>101912020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6480</th>\n",
       "      <td>9871530</td>\n",
       "      <td>YATES H S</td>\n",
       "      <td>101912020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6481</th>\n",
       "      <td>9871043</td>\n",
       "      <td>YATES H S</td>\n",
       "      <td>101912020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6482 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls      school campus_number\n",
       "0     70161219  BRISCOE EL     101912117\n",
       "1     17580127  BRISCOE EL     101912117\n",
       "2     35404452  BRISCOE EL     101912117\n",
       "3     12503971  BRISCOE EL     101912117\n",
       "4     32263872  BRISCOE EL     101912117\n",
       "...        ...         ...           ...\n",
       "6477  52192821   YATES H S     101912020\n",
       "6478  58992773   YATES H S     101912020\n",
       "6479  63298093   YATES H S     101912020\n",
       "6480   9871530   YATES H S     101912020\n",
       "6481   9871043   YATES H S     101912020\n",
       "\n",
       "[6482 rows x 3 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_junction = pd.merge_ordered(all_mls_schools_df, raw_cnum_school_df, fill_method='ffill', left_by='school')\n",
    "raw_junction.to_csv(\"./Resources/raw_junction.csv\", index = False)\n",
    "raw_junction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "mls\n",
       "98990945    2\n",
       "34482854    2\n",
       "34830405    2\n",
       "34817296    2\n",
       "34799457    2\n",
       "           ..\n",
       "66270166    2\n",
       "66254848    2\n",
       "66232995    2\n",
       "66200111    2\n",
       "242718      2\n",
       "Length: 3241, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# triple mls check - if dup school names in target area - will cause 3 mls nubers to show\n",
    "trip_school_rating = raw_junction.pivot_table(index= ['mls'], aggfunc= 'size')\n",
    "trip_school_rating.sort_values(ascending=False)"
   ]
  },
  {
   "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>mls</th>\n",
       "      <th>school</th>\n",
       "      <th>campus_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6113</th>\n",
       "      <td>242718</td>\n",
       "      <td>WISDOM H S</td>\n",
       "      <td>101912009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2936</th>\n",
       "      <td>242718</td>\n",
       "      <td>BRIARGROVE EL</td>\n",
       "      <td>101912116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2253</th>\n",
       "      <td>296833</td>\n",
       "      <td>OAK FOREST EL</td>\n",
       "      <td>101912211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5226</th>\n",
       "      <td>296833</td>\n",
       "      <td>WALTRIP H S</td>\n",
       "      <td>101912015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1855</th>\n",
       "      <td>468622</td>\n",
       "      <td>HIGHLAND HTS EL</td>\n",
       "      <td>101912174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3489</th>\n",
       "      <td>98783237</td>\n",
       "      <td>BELLAIRE H S</td>\n",
       "      <td>101912002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4772</th>\n",
       "      <td>98915588</td>\n",
       "      <td>NORTHSIDE H S</td>\n",
       "      <td>101912003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1737</th>\n",
       "      <td>98915588</td>\n",
       "      <td>KETELSEN EL</td>\n",
       "      <td>101912389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5789</th>\n",
       "      <td>98990945</td>\n",
       "      <td>WESTSIDE H S</td>\n",
       "      <td>101912036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2665</th>\n",
       "      <td>98990945</td>\n",
       "      <td>BUSH EL</td>\n",
       "      <td>101912275</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6482 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls           school campus_number\n",
       "6113    242718       WISDOM H S     101912009\n",
       "2936    242718    BRIARGROVE EL     101912116\n",
       "2253    296833    OAK FOREST EL     101912211\n",
       "5226    296833      WALTRIP H S     101912015\n",
       "1855    468622  HIGHLAND HTS EL     101912174\n",
       "...        ...              ...           ...\n",
       "3489  98783237     BELLAIRE H S     101912002\n",
       "4772  98915588    NORTHSIDE H S     101912003\n",
       "1737  98915588      KETELSEN EL     101912389\n",
       "5789  98990945     WESTSIDE H S     101912036\n",
       "2665  98990945          BUSH EL     101912275\n",
       "\n",
       "[6482 rows x 3 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted_raw_junction = raw_junction.sort_values(by=['mls'])\n",
    "sorted_raw_junction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>6113</th>\n",
       "      <td>242718</td>\n",
       "      <td>101912009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2936</th>\n",
       "      <td>242718</td>\n",
       "      <td>101912116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2253</th>\n",
       "      <td>296833</td>\n",
       "      <td>101912211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5226</th>\n",
       "      <td>296833</td>\n",
       "      <td>101912015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1855</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>3489</th>\n",
       "      <td>98783237</td>\n",
       "      <td>101912002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4772</th>\n",
       "      <td>98915588</td>\n",
       "      <td>101912003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1737</th>\n",
       "      <td>98915588</td>\n",
       "      <td>101912389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5789</th>\n",
       "      <td>98990945</td>\n",
       "      <td>101912036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2665</th>\n",
       "      <td>98990945</td>\n",
       "      <td>101912275</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6482 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls campus_number\n",
       "6113    242718     101912009\n",
       "2936    242718     101912116\n",
       "2253    296833     101912211\n",
       "5226    296833     101912015\n",
       "1855    468622     101912174\n",
       "...        ...           ...\n",
       "3489  98783237     101912002\n",
       "4772  98915588     101912003\n",
       "1737  98915588     101912389\n",
       "5789  98990945     101912036\n",
       "2665  98990945     101912275\n",
       "\n",
       "[6482 rows x 2 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_har2_df = sorted_raw_junction.drop(['school'], axis =1)\n",
    "raw_har2_df "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Writing to clean csv\n",
    "raw_har2_df .to_csv(\"./Resources/raw_har2.csv\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Junction Table"
   ]
  },
  {
   "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>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>101912009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>242718</td>\n",
       "      <td>101912116</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>101912003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6479</th>\n",
       "      <td>98915588</td>\n",
       "      <td>101912389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6480</th>\n",
       "      <td>98990945</td>\n",
       "      <td>101912036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6481</th>\n",
       "      <td>98990945</td>\n",
       "      <td>101912275</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6482 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           mls  campus_number\n",
       "0       242718      101912009\n",
       "1       242718      101912116\n",
       "2       296833      101912211\n",
       "3       296833      101912015\n",
       "4       468622      101912174\n",
       "...        ...            ...\n",
       "6477  98783237      101912002\n",
       "6478  98915588      101912003\n",
       "6479  98915588      101912389\n",
       "6480  98990945      101912036\n",
       "6481  98990945      101912275\n",
       "\n",
       "[6482 rows x 2 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Store csv into df\n",
    "csv_file= \"./Resources/raw_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": 45,
   "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": 46,
   "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": 47,
   "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": 48,
   "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": 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>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>101907002</td>\n",
       "      <td>CY-FAIR H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101907003</td>\n",
       "      <td>JERSEY VILLAGE H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101907004</td>\n",
       "      <td>CYPRESS CREEK H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101907005</td>\n",
       "      <td>LANGHAM CREEK H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>A</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101907007</td>\n",
       "      <td>CYPRESS FALLS H S</td>\n",
       "      <td>CYPRESS-FAIRBANKS ISD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  campus_number              school               district  yrs_unacceptable  \\\n",
       "0     101907002         CY-FAIR H S  CYPRESS-FAIRBANKS ISD               0.0   \n",
       "1     101907003  JERSEY VILLAGE H S  CYPRESS-FAIRBANKS ISD               0.0   \n",
       "2     101907004   CYPRESS CREEK H S  CYPRESS-FAIRBANKS ISD               0.0   \n",
       "3     101907005   LANGHAM CREEK H S  CYPRESS-FAIRBANKS ISD               0.0   \n",
       "4     101907007   CYPRESS FALLS H S  CYPRESS-FAIRBANKS ISD               0.0   \n",
       "\n",
       "  rating  year  \n",
       "0      A  2019  \n",
       "1      B  2019  \n",
       "2      B  2019  \n",
       "3      A  2019  \n",
       "4      B  2019  "
      ]
     },
     "execution_count": 49,
     "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": 50,
   "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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL</td>\n",
       "      <td>AUSTIN H S</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 EL   AUSTIN H S  \n",
       "1      HOUSTON ISD  BRISCOE EL   AUSTIN H S  \n",
       "2      HOUSTON ISD  BRISCOE EL   AUSTIN H S  \n",
       "3      HOUSTON ISD  BRISCOE EL   AUSTIN H S  \n",
       "4      HOUSTON ISD  BRISCOE EL   AUSTIN H S  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 50,
     "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": 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>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>101912009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>242718</td>\n",
       "      <td>101912116</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      101912009\n",
       "1  242718      101912116\n",
       "2  296833      101912211\n",
       "3  296833      101912015\n",
       "4  468622      101912174"
      ]
     },
     "execution_count": 51,
     "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": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['school_rating', 'mls', 'junction_table']"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for tables\n",
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}