Notebooks/Create_Master_Table_and_Split_Train_Test.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/jamesfay/opt/anaconda3/envs/learn-env/lib/python3.6/site-packages/statsmodels/tools/_testing.py:19: FutureWarning: pandas.util.testing is deprecated. Use the functions in the public API at pandas.testing instead.\n",
      "  import pandas.util.testing as tm\n"
     ]
    }
   ],
   "source": [
    "#import libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('kc_house_data.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deal with Null Values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Replace Null Values with Median WF value for the zipcode**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['waterfront'] = df['waterfront'].fillna(df.groupby('zipcode')['waterfront'].transform('median'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0    21451\n",
       "1.0      146\n",
       "Name: waterfront, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['waterfront'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Merge Year Built & Year Renovated into 1 Column**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['yr_of_last_update'] = df['yr_renovated']\n",
    "df['yr_of_last_update'].fillna(value=df['yr_built'], inplace=True)\n",
    "df['yr_of_last_update'].replace(to_replace=0, value=df['yr_built'], inplace=True)\n",
    "df['yr_of_last_update'] = df['yr_of_last_update'].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Replace sqft_basement ?s with median by zipcode**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#replace sqft_basement '?' with nas\n",
    "df['sqft_basement'] = df['sqft_basement'].replace('?', np.nan)\n",
    "\n",
    "#Convert to int\n",
    "df['sqft_basement'] = df['sqft_basement'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replace Nas with median values by zipcode\n",
    "df['sqft_basement'] = df['sqft_basement'].fillna(df.groupby('zipcode')['sqft_basement'].transform('median'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace null view values\n",
    "df['view'] = df['view'].fillna(df.groupby('zipcode')['view'].transform('median'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Season Bins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Using Northern Meterological Seasons\n",
    "#https://www.timeanddate.com/calendar/aboutseasons.html\n",
    "df['date'] = pd.to_datetime(df['date'])\n",
    "df['season_sold'] = df['date'].apply(lambda x: 'Spring' if (3 <= x.month <= 5) else 'Summer' if (6 <= x.month <= 8) else 'Fall' if (9 <= x.month <= 11) else 'Winter')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Above/Below Ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['below_ground_ratio'] =  df['sqft_basement'] / df['sqft_above']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Log of Lot Size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "log_lot_col = np.log(df['sqft_lot'])\n",
    "df.insert(loc=6, column='log_lot', value=log_lot_col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove Date, Above, Basement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(['date', 'sqft_above', 'sqft_basement', 'yr_built', 'yr_renovated'], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove Bedrooms Outlier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21596, 20)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "to_drop = df.loc[(df['bedrooms'] == 33)]\n",
    "df.drop(to_drop.index, axis = 0, inplace = True)\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove Houses Over 1 Million or Below 100 Thousand"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21374, 20)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "to_drop = df.loc[(df['price'] < 100000)]\n",
    "df.drop(to_drop.index, axis = 0, inplace = True)\n",
    "to_drop = df.loc[(df['price'] > 2000000)]\n",
    "df.drop(to_drop.index, axis = 0, inplace = True)\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Change Price Units to Hundreds of Thousands"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['price_100k'] = df['price'] / 100000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create is_top_school_district"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>price</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>sqft_living</th>\n",
       "      <th>log_lot</th>\n",
       "      <th>sqft_lot</th>\n",
       "      <th>floors</th>\n",
       "      <th>waterfront</th>\n",
       "      <th>view</th>\n",
       "      <th>...</th>\n",
       "      <th>grade</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>sqft_living15</th>\n",
       "      <th>sqft_lot15</th>\n",
       "      <th>yr_of_last_update</th>\n",
       "      <th>season_sold</th>\n",
       "      <th>below_ground_ratio</th>\n",
       "      <th>price_100k</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7129300520</td>\n",
       "      <td>221900.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1180</td>\n",
       "      <td>8.639411</td>\n",
       "      <td>5650</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>98178</td>\n",
       "      <td>47.5112</td>\n",
       "      <td>-122.257</td>\n",
       "      <td>1340</td>\n",
       "      <td>5650</td>\n",
       "      <td>1955</td>\n",
       "      <td>Fall</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6414100192</td>\n",
       "      <td>538000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2570</td>\n",
       "      <td>8.887653</td>\n",
       "      <td>7242</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>98125</td>\n",
       "      <td>47.7210</td>\n",
       "      <td>-122.319</td>\n",
       "      <td>1690</td>\n",
       "      <td>7639</td>\n",
       "      <td>1991</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.184332</td>\n",
       "      <td>5.380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5631500400</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>770</td>\n",
       "      <td>9.210340</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>98028</td>\n",
       "      <td>47.7379</td>\n",
       "      <td>-122.233</td>\n",
       "      <td>2720</td>\n",
       "      <td>8062</td>\n",
       "      <td>1933</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2487200875</td>\n",
       "      <td>604000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1960</td>\n",
       "      <td>8.517193</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>98136</td>\n",
       "      <td>47.5208</td>\n",
       "      <td>-122.393</td>\n",
       "      <td>1360</td>\n",
       "      <td>5000</td>\n",
       "      <td>1965</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>6.040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1954400510</td>\n",
       "      <td>510000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1680</td>\n",
       "      <td>8.997147</td>\n",
       "      <td>8080</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>98074</td>\n",
       "      <td>47.6168</td>\n",
       "      <td>-122.045</td>\n",
       "      <td>1800</td>\n",
       "      <td>7503</td>\n",
       "      <td>1987</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id     price  bedrooms  bathrooms  sqft_living   log_lot  sqft_lot  \\\n",
       "0  7129300520  221900.0         3       1.00         1180  8.639411      5650   \n",
       "1  6414100192  538000.0         3       2.25         2570  8.887653      7242   \n",
       "2  5631500400  180000.0         2       1.00          770  9.210340     10000   \n",
       "3  2487200875  604000.0         4       3.00         1960  8.517193      5000   \n",
       "4  1954400510  510000.0         3       2.00         1680  8.997147      8080   \n",
       "\n",
       "   floors  waterfront  view  ...  grade  zipcode      lat     long  \\\n",
       "0     1.0         0.0   0.0  ...      7    98178  47.5112 -122.257   \n",
       "1     2.0         0.0   0.0  ...      7    98125  47.7210 -122.319   \n",
       "2     1.0         0.0   0.0  ...      6    98028  47.7379 -122.233   \n",
       "3     1.0         0.0   0.0  ...      7    98136  47.5208 -122.393   \n",
       "4     1.0         0.0   0.0  ...      8    98074  47.6168 -122.045   \n",
       "\n",
       "   sqft_living15  sqft_lot15  yr_of_last_update  season_sold  \\\n",
       "0           1340        5650               1955         Fall   \n",
       "1           1690        7639               1991       Winter   \n",
       "2           2720        8062               1933       Winter   \n",
       "3           1360        5000               1965       Winter   \n",
       "4           1800        7503               1987       Winter   \n",
       "\n",
       "  below_ground_ratio  price_100k  \n",
       "0           0.000000       2.219  \n",
       "1           0.184332       5.380  \n",
       "2           0.000000       1.800  \n",
       "3           0.866667       6.040  \n",
       "4           0.000000       5.100  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['long_lat'] = list(zip(df['long'], df['lat']))"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>price</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>sqft_living</th>\n",
       "      <th>log_lot</th>\n",
       "      <th>sqft_lot</th>\n",
       "      <th>floors</th>\n",
       "      <th>waterfront</th>\n",
       "      <th>view</th>\n",
       "      <th>...</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>sqft_living15</th>\n",
       "      <th>sqft_lot15</th>\n",
       "      <th>yr_of_last_update</th>\n",
       "      <th>season_sold</th>\n",
       "      <th>below_ground_ratio</th>\n",
       "      <th>price_100k</th>\n",
       "      <th>long_lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7129300520</td>\n",
       "      <td>221900.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1180</td>\n",
       "      <td>8.639411</td>\n",
       "      <td>5650</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>98178</td>\n",
       "      <td>47.5112</td>\n",
       "      <td>-122.257</td>\n",
       "      <td>1340</td>\n",
       "      <td>5650</td>\n",
       "      <td>1955</td>\n",
       "      <td>Fall</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.219</td>\n",
       "      <td>(-122.257, 47.5112)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6414100192</td>\n",
       "      <td>538000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2570</td>\n",
       "      <td>8.887653</td>\n",
       "      <td>7242</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>98125</td>\n",
       "      <td>47.7210</td>\n",
       "      <td>-122.319</td>\n",
       "      <td>1690</td>\n",
       "      <td>7639</td>\n",
       "      <td>1991</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.184332</td>\n",
       "      <td>5.380</td>\n",
       "      <td>(-122.319, 47.721000000000004)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5631500400</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>770</td>\n",
       "      <td>9.210340</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>98028</td>\n",
       "      <td>47.7379</td>\n",
       "      <td>-122.233</td>\n",
       "      <td>2720</td>\n",
       "      <td>8062</td>\n",
       "      <td>1933</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.800</td>\n",
       "      <td>(-122.23299999999999, 47.7379)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2487200875</td>\n",
       "      <td>604000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1960</td>\n",
       "      <td>8.517193</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>98136</td>\n",
       "      <td>47.5208</td>\n",
       "      <td>-122.393</td>\n",
       "      <td>1360</td>\n",
       "      <td>5000</td>\n",
       "      <td>1965</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>6.040</td>\n",
       "      <td>(-122.39299999999999, 47.5208)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1954400510</td>\n",
       "      <td>510000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1680</td>\n",
       "      <td>8.997147</td>\n",
       "      <td>8080</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>98074</td>\n",
       "      <td>47.6168</td>\n",
       "      <td>-122.045</td>\n",
       "      <td>1800</td>\n",
       "      <td>7503</td>\n",
       "      <td>1987</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.100</td>\n",
       "      <td>(-122.045, 47.6168)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id     price  bedrooms  bathrooms  sqft_living   log_lot  sqft_lot  \\\n",
       "0  7129300520  221900.0         3       1.00         1180  8.639411      5650   \n",
       "1  6414100192  538000.0         3       2.25         2570  8.887653      7242   \n",
       "2  5631500400  180000.0         2       1.00          770  9.210340     10000   \n",
       "3  2487200875  604000.0         4       3.00         1960  8.517193      5000   \n",
       "4  1954400510  510000.0         3       2.00         1680  8.997147      8080   \n",
       "\n",
       "   floors  waterfront  view  ...  zipcode      lat     long  sqft_living15  \\\n",
       "0     1.0         0.0   0.0  ...    98178  47.5112 -122.257           1340   \n",
       "1     2.0         0.0   0.0  ...    98125  47.7210 -122.319           1690   \n",
       "2     1.0         0.0   0.0  ...    98028  47.7379 -122.233           2720   \n",
       "3     1.0         0.0   0.0  ...    98136  47.5208 -122.393           1360   \n",
       "4     1.0         0.0   0.0  ...    98074  47.6168 -122.045           1800   \n",
       "\n",
       "   sqft_lot15  yr_of_last_update  season_sold  below_ground_ratio price_100k  \\\n",
       "0        5650               1955         Fall            0.000000      2.219   \n",
       "1        7639               1991       Winter            0.184332      5.380   \n",
       "2        8062               1933       Winter            0.000000      1.800   \n",
       "3        5000               1965       Winter            0.866667      6.040   \n",
       "4        7503               1987       Winter            0.000000      5.100   \n",
       "\n",
       "                         long_lat  \n",
       "0             (-122.257, 47.5112)  \n",
       "1  (-122.319, 47.721000000000004)  \n",
       "2  (-122.23299999999999, 47.7379)  \n",
       "3  (-122.39299999999999, 47.5208)  \n",
       "4             (-122.045, 47.6168)  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import shapefile\n",
    "from shapely.geometry import Point # Point class\n",
    "from shapely.geometry import shape # shape() is a function to convert geo objects through the interface\n",
    "\n",
    "\n",
    "shp = shapefile.Reader('School_Districts_in_King_County___schdst_area.shp') #open the shapefile\n",
    "all_shapes = shp.shapes() # get all the polygons\n",
    "all_records = shp.records()\n",
    "\n",
    "#Create Numpy array to hold district names\n",
    "# district_array = np.empty([df.shape[0], ], dtype=\"S20\")\n",
    "district_df = pd.DataFrame(index=np.arange(df.shape[0]), columns=['district'])\n",
    "\n",
    "\n",
    "for x in range(df.shape[0]):\n",
    "    point = df.iloc[x, 21] # an x,y tuple\n",
    "    for i in range(len(all_shapes)):\n",
    "        boundary = all_shapes[i] # get a boundary polygon\n",
    "        if Point(point).within(shape(boundary)): # make a point and see if it's in the polygon\n",
    "           name = all_records[i][2] # get the second field of the corresponding record\n",
    "           district_df.iloc[x, 0] = name\n",
    "        else:\n",
    "            district_df.iloc[x, 0] = 'blank'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21374, 22)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21374, 1)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "district_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 21374 entries, 0 to 21373\n",
      "Data columns (total 22 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   id                  21374 non-null  int64  \n",
      " 1   price               21374 non-null  float64\n",
      " 2   bedrooms            21374 non-null  int64  \n",
      " 3   bathrooms           21374 non-null  float64\n",
      " 4   sqft_living         21374 non-null  int64  \n",
      " 5   log_lot             21374 non-null  float64\n",
      " 6   sqft_lot            21374 non-null  int64  \n",
      " 7   floors              21374 non-null  float64\n",
      " 8   waterfront          21374 non-null  float64\n",
      " 9   view                21374 non-null  float64\n",
      " 10  condition           21374 non-null  int64  \n",
      " 11  grade               21374 non-null  int64  \n",
      " 12  zipcode             21374 non-null  int64  \n",
      " 13  lat                 21374 non-null  float64\n",
      " 14  long                21374 non-null  float64\n",
      " 15  sqft_living15       21374 non-null  int64  \n",
      " 16  sqft_lot15          21374 non-null  int64  \n",
      " 17  yr_of_last_update   21374 non-null  int64  \n",
      " 18  season_sold         21374 non-null  object \n",
      " 19  below_ground_ratio  21374 non-null  float64\n",
      " 20  price_100k          21374 non-null  float64\n",
      " 21  long_lat            21374 non-null  object \n",
      "dtypes: float64(10), int64(10), object(2)\n",
      "memory usage: 3.6+ MB\n"
     ]
    }
   ],
   "source": [
    "new_df = df.reset_index(drop=True)\n",
    "new_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge in List of Districts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 21374 entries, 0 to 21373\n",
      "Data columns (total 23 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   id                  21374 non-null  int64  \n",
      " 1   price               21374 non-null  float64\n",
      " 2   bedrooms            21374 non-null  int64  \n",
      " 3   bathrooms           21374 non-null  float64\n",
      " 4   sqft_living         21374 non-null  int64  \n",
      " 5   log_lot             21374 non-null  float64\n",
      " 6   sqft_lot            21374 non-null  int64  \n",
      " 7   floors              21374 non-null  float64\n",
      " 8   waterfront          21374 non-null  float64\n",
      " 9   view                21374 non-null  float64\n",
      " 10  condition           21374 non-null  int64  \n",
      " 11  grade               21374 non-null  int64  \n",
      " 12  zipcode             21374 non-null  int64  \n",
      " 13  lat                 21374 non-null  float64\n",
      " 14  long                21374 non-null  float64\n",
      " 15  sqft_living15       21374 non-null  int64  \n",
      " 16  sqft_lot15          21374 non-null  int64  \n",
      " 17  yr_of_last_update   21374 non-null  int64  \n",
      " 18  season_sold         21374 non-null  object \n",
      " 19  below_ground_ratio  21374 non-null  float64\n",
      " 20  price_100k          21374 non-null  float64\n",
      " 21  long_lat            21374 non-null  object \n",
      " 22  district            21368 non-null  object \n",
      "dtypes: float64(10), int64(10), object(3)\n",
      "memory usage: 3.8+ MB\n"
     ]
    }
   ],
   "source": [
    "master_df = pd.concat([new_df, district_df], axis=1)\n",
    "master_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Couldn't find district for 6 houses.  Remove them.\n",
    "to_drop = master_df.loc[(master_df['district'].isna())]\n",
    "master_df.drop(to_drop.index, axis = 0, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "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>id</th>\n",
       "      <th>price</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>sqft_living</th>\n",
       "      <th>log_lot</th>\n",
       "      <th>sqft_lot</th>\n",
       "      <th>floors</th>\n",
       "      <th>waterfront</th>\n",
       "      <th>view</th>\n",
       "      <th>...</th>\n",
       "      <th>long</th>\n",
       "      <th>sqft_living15</th>\n",
       "      <th>sqft_lot15</th>\n",
       "      <th>yr_of_last_update</th>\n",
       "      <th>season_sold</th>\n",
       "      <th>below_ground_ratio</th>\n",
       "      <th>price_100k</th>\n",
       "      <th>long_lat</th>\n",
       "      <th>district</th>\n",
       "      <th>is_seattle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7129300520</td>\n",
       "      <td>221900.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1180</td>\n",
       "      <td>8.639411</td>\n",
       "      <td>5650</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-122.257</td>\n",
       "      <td>1340</td>\n",
       "      <td>5650</td>\n",
       "      <td>1955</td>\n",
       "      <td>Fall</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.21900</td>\n",
       "      <td>(-122.257, 47.5112)</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6414100192</td>\n",
       "      <td>538000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2570</td>\n",
       "      <td>8.887653</td>\n",
       "      <td>7242</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-122.319</td>\n",
       "      <td>1690</td>\n",
       "      <td>7639</td>\n",
       "      <td>1991</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.184332</td>\n",
       "      <td>5.38000</td>\n",
       "      <td>(-122.319, 47.721000000000004)</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5631500400</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>770</td>\n",
       "      <td>9.210340</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-122.233</td>\n",
       "      <td>2720</td>\n",
       "      <td>8062</td>\n",
       "      <td>1933</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.80000</td>\n",
       "      <td>(-122.23299999999999, 47.7379)</td>\n",
       "      <td>Northshore</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2487200875</td>\n",
       "      <td>604000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1960</td>\n",
       "      <td>8.517193</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-122.393</td>\n",
       "      <td>1360</td>\n",
       "      <td>5000</td>\n",
       "      <td>1965</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>6.04000</td>\n",
       "      <td>(-122.39299999999999, 47.5208)</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1954400510</td>\n",
       "      <td>510000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1680</td>\n",
       "      <td>8.997147</td>\n",
       "      <td>8080</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-122.045</td>\n",
       "      <td>1800</td>\n",
       "      <td>7503</td>\n",
       "      <td>1987</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.10000</td>\n",
       "      <td>(-122.045, 47.6168)</td>\n",
       "      <td>Lake Washington</td>\n",
       "      <td>0.0</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>21369</th>\n",
       "      <td>263000018</td>\n",
       "      <td>360000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1530</td>\n",
       "      <td>7.030857</td>\n",
       "      <td>1131</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-122.346</td>\n",
       "      <td>1530</td>\n",
       "      <td>1509</td>\n",
       "      <td>2009</td>\n",
       "      <td>Spring</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.60000</td>\n",
       "      <td>(-122.346, 47.6993)</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21370</th>\n",
       "      <td>6600060120</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2310</td>\n",
       "      <td>8.667852</td>\n",
       "      <td>5813</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-122.362</td>\n",
       "      <td>1830</td>\n",
       "      <td>7200</td>\n",
       "      <td>2014</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>(-122.36200000000001, 47.5107)</td>\n",
       "      <td>Highline</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21371</th>\n",
       "      <td>1523300141</td>\n",
       "      <td>402101.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>7.207860</td>\n",
       "      <td>1350</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>2007</td>\n",
       "      <td>2009</td>\n",
       "      <td>Summer</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.02101</td>\n",
       "      <td>(-122.29899999999999, 47.5944)</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21372</th>\n",
       "      <td>291310100</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1600</td>\n",
       "      <td>7.778211</td>\n",
       "      <td>2388</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-122.069</td>\n",
       "      <td>1410</td>\n",
       "      <td>1287</td>\n",
       "      <td>2004</td>\n",
       "      <td>Winter</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>(-122.069, 47.5345)</td>\n",
       "      <td>Issaquah</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21373</th>\n",
       "      <td>1523300157</td>\n",
       "      <td>325000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>6.981006</td>\n",
       "      <td>1076</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>1357</td>\n",
       "      <td>2008</td>\n",
       "      <td>Fall</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.25000</td>\n",
       "      <td>(-122.29899999999999, 47.5941)</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21368 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               id     price  bedrooms  bathrooms  sqft_living   log_lot  \\\n",
       "0      7129300520  221900.0         3       1.00         1180  8.639411   \n",
       "1      6414100192  538000.0         3       2.25         2570  8.887653   \n",
       "2      5631500400  180000.0         2       1.00          770  9.210340   \n",
       "3      2487200875  604000.0         4       3.00         1960  8.517193   \n",
       "4      1954400510  510000.0         3       2.00         1680  8.997147   \n",
       "...           ...       ...       ...        ...          ...       ...   \n",
       "21369   263000018  360000.0         3       2.50         1530  7.030857   \n",
       "21370  6600060120  400000.0         4       2.50         2310  8.667852   \n",
       "21371  1523300141  402101.0         2       0.75         1020  7.207860   \n",
       "21372   291310100  400000.0         3       2.50         1600  7.778211   \n",
       "21373  1523300157  325000.0         2       0.75         1020  6.981006   \n",
       "\n",
       "       sqft_lot  floors  waterfront  view  ...     long  sqft_living15  \\\n",
       "0          5650     1.0         0.0   0.0  ... -122.257           1340   \n",
       "1          7242     2.0         0.0   0.0  ... -122.319           1690   \n",
       "2         10000     1.0         0.0   0.0  ... -122.233           2720   \n",
       "3          5000     1.0         0.0   0.0  ... -122.393           1360   \n",
       "4          8080     1.0         0.0   0.0  ... -122.045           1800   \n",
       "...         ...     ...         ...   ...  ...      ...            ...   \n",
       "21369      1131     3.0         0.0   0.0  ... -122.346           1530   \n",
       "21370      5813     2.0         0.0   0.0  ... -122.362           1830   \n",
       "21371      1350     2.0         0.0   0.0  ... -122.299           1020   \n",
       "21372      2388     2.0         0.0   0.0  ... -122.069           1410   \n",
       "21373      1076     2.0         0.0   0.0  ... -122.299           1020   \n",
       "\n",
       "      sqft_lot15  yr_of_last_update  season_sold  below_ground_ratio  \\\n",
       "0           5650               1955         Fall            0.000000   \n",
       "1           7639               1991       Winter            0.184332   \n",
       "2           8062               1933       Winter            0.000000   \n",
       "3           5000               1965       Winter            0.866667   \n",
       "4           7503               1987       Winter            0.000000   \n",
       "...          ...                ...          ...                 ...   \n",
       "21369       1509               2009       Spring            0.000000   \n",
       "21370       7200               2014       Winter            0.000000   \n",
       "21371       2007               2009       Summer            0.000000   \n",
       "21372       1287               2004       Winter            0.000000   \n",
       "21373       1357               2008         Fall            0.000000   \n",
       "\n",
       "       price_100k                        long_lat         district  is_seattle  \n",
       "0         2.21900             (-122.257, 47.5112)          Seattle         1.0  \n",
       "1         5.38000  (-122.319, 47.721000000000004)          Seattle         1.0  \n",
       "2         1.80000  (-122.23299999999999, 47.7379)       Northshore         0.0  \n",
       "3         6.04000  (-122.39299999999999, 47.5208)          Seattle         1.0  \n",
       "4         5.10000             (-122.045, 47.6168)  Lake Washington         0.0  \n",
       "...           ...                             ...              ...         ...  \n",
       "21369     3.60000             (-122.346, 47.6993)          Seattle         1.0  \n",
       "21370     4.00000  (-122.36200000000001, 47.5107)         Highline         0.0  \n",
       "21371     4.02101  (-122.29899999999999, 47.5944)          Seattle         1.0  \n",
       "21372     4.00000             (-122.069, 47.5345)         Issaquah         0.0  \n",
       "21373     3.25000  (-122.29899999999999, 47.5941)          Seattle         1.0  \n",
       "\n",
       "[21368 rows x 24 columns]"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create is_seattle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below Shows One Method That Uses Zipcodes.  This is not efficient b/c zipcodes are not neatly contained in the Seattle city borders.  Instead will use the Seattle school district data, which is more exact"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'98101|98102|98103|98104|98105|98106|98107|98108|98109|98111|98112|98113|98114|98115|98116|98117|98118|98119|98121|98122|98124|98125|98126|98127|98129|98131|98133|98134|98136|98138|98139|98141|98144|98145|98146|98148|98154|98155|98158|98160|98161|98164|98165|98166|98168|98170|98174|98175|98177|98178|98181|98185|98188|98190|98191|98194|98195|98198|98199'"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# seattle_zips = [98101, 98102, 98103, 98104, 98105, 98106, 98107, 98108, 98109, 98111, 98112, 98113, 98114, 98115, 98116, 98117, 98118, 98119, 98121, 98122, 98124, 98125, 98126, 98127, 98129, 98131, 98133, 98134, 98136, 98138, 98139, 98141, 98144, 98145, 98146, 98148, 98154, 98155, 98158, 98160, 98161, 98164, 98165, 98166, 98168, 98170, 98174, 98175, 98177, 98178, 98181, 98185, 98188, 98190, 98191, 98194, 98195, 98198, 98199]\n",
    "# seattle_zips = [str(i) for i in seattle_zips]\n",
    "# seattle_zips_str = '98101, 98102, 98103, 98104, 98105, 98106, 98107, 98108, 98109, 98111, 98112, 98113, 98114, 98115, 98116, 98117, 98118, 98119, 98121, 98122, 98124, 98125, 98126, 98127, 98129, 98131, 98133, 98134, 98136, 98138, 98139, 98141, 98144, 98145, 98146, 98148, 98154, 98155, 98158, 98160, 98161, 98164, 98165, 98166, 98168, 98170, 98174, 98175, 98177, 98178, 98181, 98185, 98188, 98190, 98191, 98194, 98195, 98198, 98199'\n",
    "# seattle_zips_str = seattle_zips_str.replace(', ', '|')\n",
    "# seattle_zips_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "# master_df['zipcode'] = master_df['zipcode'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "# master_df.drop('is_seattle', axis=1, inplace=True)\n",
    "# master_df.loc[master_df['zipcode'].str.contains('98101|98102|98103|98104|98105|98106|98107|98108|98109|98111|98112|98113|98114|98115|98116|98117|98118|98119|98121|98122|98124|98125|98126|98127|98129|98131|98133|98134|98136|98138|98139|98141|98144|98145|98146|98148|98154|98155|98158|98160|98161|98164|98165|98166|98168|98170|98174|98175|98177|98178|98181|98185|98188|98190|98191|98194|98195|98198|98199'),'is_seattle'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "# master_df['is_seattle'].fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Method Using School District Data - Assumes we know user location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "master_df.loc[master_df['district'] == 'Seattle', 'is_seattle'] = 1\n",
    "master_df['is_seattle'].fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0    14693\n",
       "1.0     6675\n",
       "Name: is_seattle, dtype: int64"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_df['is_seattle'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop vars and save file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "master_df = master_df.drop(['price', 'sqft_living15', 'sqft_lot15', 'view'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 21368 entries, 0 to 21373\n",
      "Data columns (total 20 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   id                  21368 non-null  int64  \n",
      " 1   bedrooms            21368 non-null  int64  \n",
      " 2   bathrooms           21368 non-null  float64\n",
      " 3   sqft_living         21368 non-null  int64  \n",
      " 4   log_lot             21368 non-null  float64\n",
      " 5   sqft_lot            21368 non-null  int64  \n",
      " 6   floors              21368 non-null  float64\n",
      " 7   waterfront          21368 non-null  float64\n",
      " 8   condition           21368 non-null  int64  \n",
      " 9   grade               21368 non-null  int64  \n",
      " 10  zipcode             21368 non-null  object \n",
      " 11  lat                 21368 non-null  float64\n",
      " 12  long                21368 non-null  float64\n",
      " 13  yr_of_last_update   21368 non-null  int64  \n",
      " 14  season_sold         21368 non-null  object \n",
      " 15  below_ground_ratio  21368 non-null  float64\n",
      " 16  price_100k          21368 non-null  float64\n",
      " 17  long_lat            21368 non-null  object \n",
      " 18  district            21368 non-null  object \n",
      " 19  is_seattle          21368 non-null  float64\n",
      "dtypes: float64(9), int64(7), object(4)\n",
      "memory usage: 4.0+ MB\n"
     ]
    }
   ],
   "source": [
    "master_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "master_df.to_csv(path_or_buf='Master_Data_Table_v2.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Split Train and Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "## import\n",
    "from sklearn.model_selection import train_test_split\n",
    "# Split Master DF\n",
    "train_set, test_set = train_test_split(master_df, test_size=0.2, random_state=13)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save Train vs Test csv Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_set.to_csv(path_or_buf='Train_Table.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_set.to_csv(path_or_buf='Test_Table.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "model."
   ]
  }
 ],
 "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.1"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}