New_data_etl_3.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "29681777",
   "metadata": {},
   "outputs": [],
   "source": [
    "from path import Path\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Import password variable\n",
    "from config import db_password "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e6a8ce05",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connection string to local server\n",
    "db_string = f\"postgresql://group5:{db_password}@house-prices-instance.cwmcyi4k8dli.us-east-2.rds.amazonaws.com:5432/postgres\"\n",
    "\n",
    "# Database engine for SQLAlchemy to connect Pandas to SQL\n",
    "engine = create_engine(db_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "bb259861",
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'Resources/kc_house_data.csv'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-10-414f5f353782>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Read raw data from csv and load into dataframe\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mhouse_data_raw_df\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"Resources/kc_house_data.csv\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m#house_data_raw_df.to_sql(name='house_data_raw', con=engine, if_exists = \"replace\")\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\envs\\PythonData\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36mread_csv\u001b[1;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, dialect, error_bad_lines, warn_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options)\u001b[0m\n\u001b[0;32m    608\u001b[0m     \u001b[0mkwds\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mupdate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkwds_defaults\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    609\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 610\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0m_read\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    611\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    612\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\envs\\PythonData\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m_read\u001b[1;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[0;32m    460\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    461\u001b[0m     \u001b[1;31m# Create the parser.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 462\u001b[1;33m     \u001b[0mparser\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mTextFileReader\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    463\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    464\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mchunksize\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0miterator\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\envs\\PythonData\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, f, engine, **kwds)\u001b[0m\n\u001b[0;32m    817\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"has_index_names\"\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"has_index_names\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    818\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 819\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_make_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    820\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    821\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\envs\\PythonData\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m_make_engine\u001b[1;34m(self, engine)\u001b[0m\n\u001b[0;32m   1048\u001b[0m             )\n\u001b[0;32m   1049\u001b[0m         \u001b[1;31m# error: Too many arguments for \"ParserBase\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1050\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mmapping\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m)\u001b[0m  \u001b[1;31m# type: ignore[call-arg]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1051\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1052\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_failover_to_python\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\envs\\PythonData\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, src, **kwds)\u001b[0m\n\u001b[0;32m   1865\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1866\u001b[0m         \u001b[1;31m# open handles\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1867\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_open_handles\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msrc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1868\u001b[0m         \u001b[1;32massert\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhandles\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1869\u001b[0m         \u001b[1;32mfor\u001b[0m \u001b[0mkey\u001b[0m \u001b[1;32min\u001b[0m \u001b[1;33m(\u001b[0m\u001b[1;34m\"storage_options\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"encoding\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"memory_map\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"compression\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\envs\\PythonData\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m_open_handles\u001b[1;34m(self, src, kwds)\u001b[0m\n\u001b[0;32m   1366\u001b[0m             \u001b[0mcompression\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"compression\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1367\u001b[0m             \u001b[0mmemory_map\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"memory_map\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1368\u001b[1;33m             \u001b[0mstorage_options\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"storage_options\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1369\u001b[0m         )\n\u001b[0;32m   1370\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\envs\\PythonData\\lib\\site-packages\\pandas\\io\\common.py\u001b[0m in \u001b[0;36mget_handle\u001b[1;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)\u001b[0m\n\u001b[0;32m    645\u001b[0m                 \u001b[0mencoding\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mioargs\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mencoding\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    646\u001b[0m                 \u001b[0merrors\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0merrors\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 647\u001b[1;33m                 \u001b[0mnewline\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"\"\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    648\u001b[0m             )\n\u001b[0;32m    649\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'Resources/kc_house_data.csv'"
     ]
    }
   ],
   "source": [
    "# Read raw data from csv and load into dataframe\n",
    "house_data_raw_df = pd.read_csv(\"Resources\\kc_house_data.csv\")\n",
    "\n",
    "#house_data_raw_df.to_sql(name='house_data_raw', con=engine, if_exists = \"replace\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4b095a4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e6b91948",
   "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>date</th>\n",
       "      <th>price</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>sqft_living</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>sqft_above</th>\n",
       "      <th>sqft_basement</th>\n",
       "      <th>yr_built</th>\n",
       "      <th>yr_renovated</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>sqft_living15</th>\n",
       "      <th>sqft_lot15</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7129300520</td>\n",
       "      <td>20141013T000000</td>\n",
       "      <td>221900.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1180</td>\n",
       "      <td>5650</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1180</td>\n",
       "      <td>0</td>\n",
       "      <td>1955</td>\n",
       "      <td>0</td>\n",
       "      <td>98178</td>\n",
       "      <td>47.5112</td>\n",
       "      <td>-122.257</td>\n",
       "      <td>1340</td>\n",
       "      <td>5650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6414100192</td>\n",
       "      <td>20141209T000000</td>\n",
       "      <td>538000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2570</td>\n",
       "      <td>7242</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>2170</td>\n",
       "      <td>400</td>\n",
       "      <td>1951</td>\n",
       "      <td>1991</td>\n",
       "      <td>98125</td>\n",
       "      <td>47.7210</td>\n",
       "      <td>-122.319</td>\n",
       "      <td>1690</td>\n",
       "      <td>7639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5631500400</td>\n",
       "      <td>20150225T000000</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>770</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>770</td>\n",
       "      <td>0</td>\n",
       "      <td>1933</td>\n",
       "      <td>0</td>\n",
       "      <td>98028</td>\n",
       "      <td>47.7379</td>\n",
       "      <td>-122.233</td>\n",
       "      <td>2720</td>\n",
       "      <td>8062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2487200875</td>\n",
       "      <td>20141209T000000</td>\n",
       "      <td>604000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1960</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1050</td>\n",
       "      <td>910</td>\n",
       "      <td>1965</td>\n",
       "      <td>0</td>\n",
       "      <td>98136</td>\n",
       "      <td>47.5208</td>\n",
       "      <td>-122.393</td>\n",
       "      <td>1360</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1954400510</td>\n",
       "      <td>20150218T000000</td>\n",
       "      <td>510000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1680</td>\n",
       "      <td>8080</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>1680</td>\n",
       "      <td>0</td>\n",
       "      <td>1987</td>\n",
       "      <td>0</td>\n",
       "      <td>98074</td>\n",
       "      <td>47.6168</td>\n",
       "      <td>-122.045</td>\n",
       "      <td>1800</td>\n",
       "      <td>7503</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>21608</th>\n",
       "      <td>263000018</td>\n",
       "      <td>20140521T000000</td>\n",
       "      <td>360000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1530</td>\n",
       "      <td>1131</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>1530</td>\n",
       "      <td>0</td>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>98103</td>\n",
       "      <td>47.6993</td>\n",
       "      <td>-122.346</td>\n",
       "      <td>1530</td>\n",
       "      <td>1509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21609</th>\n",
       "      <td>6600060120</td>\n",
       "      <td>20150223T000000</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2310</td>\n",
       "      <td>5813</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>2310</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "      <td>0</td>\n",
       "      <td>98146</td>\n",
       "      <td>47.5107</td>\n",
       "      <td>-122.362</td>\n",
       "      <td>1830</td>\n",
       "      <td>7200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21610</th>\n",
       "      <td>1523300141</td>\n",
       "      <td>20140623T000000</td>\n",
       "      <td>402101.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>1350</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1020</td>\n",
       "      <td>0</td>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>98144</td>\n",
       "      <td>47.5944</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21611</th>\n",
       "      <td>291310100</td>\n",
       "      <td>20150116T000000</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1600</td>\n",
       "      <td>2388</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>1600</td>\n",
       "      <td>0</td>\n",
       "      <td>2004</td>\n",
       "      <td>0</td>\n",
       "      <td>98027</td>\n",
       "      <td>47.5345</td>\n",
       "      <td>-122.069</td>\n",
       "      <td>1410</td>\n",
       "      <td>1287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21612</th>\n",
       "      <td>1523300157</td>\n",
       "      <td>20141015T000000</td>\n",
       "      <td>325000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>1076</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1020</td>\n",
       "      <td>0</td>\n",
       "      <td>2008</td>\n",
       "      <td>0</td>\n",
       "      <td>98144</td>\n",
       "      <td>47.5941</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>1357</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21613 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               id             date     price  bedrooms  bathrooms  \\\n",
       "0      7129300520  20141013T000000  221900.0         3       1.00   \n",
       "1      6414100192  20141209T000000  538000.0         3       2.25   \n",
       "2      5631500400  20150225T000000  180000.0         2       1.00   \n",
       "3      2487200875  20141209T000000  604000.0         4       3.00   \n",
       "4      1954400510  20150218T000000  510000.0         3       2.00   \n",
       "...           ...              ...       ...       ...        ...   \n",
       "21608   263000018  20140521T000000  360000.0         3       2.50   \n",
       "21609  6600060120  20150223T000000  400000.0         4       2.50   \n",
       "21610  1523300141  20140623T000000  402101.0         2       0.75   \n",
       "21611   291310100  20150116T000000  400000.0         3       2.50   \n",
       "21612  1523300157  20141015T000000  325000.0         2       0.75   \n",
       "\n",
       "       sqft_living  sqft_lot  floors  waterfront  view  ...  grade  \\\n",
       "0             1180      5650     1.0           0     0  ...      7   \n",
       "1             2570      7242     2.0           0     0  ...      7   \n",
       "2              770     10000     1.0           0     0  ...      6   \n",
       "3             1960      5000     1.0           0     0  ...      7   \n",
       "4             1680      8080     1.0           0     0  ...      8   \n",
       "...            ...       ...     ...         ...   ...  ...    ...   \n",
       "21608         1530      1131     3.0           0     0  ...      8   \n",
       "21609         2310      5813     2.0           0     0  ...      8   \n",
       "21610         1020      1350     2.0           0     0  ...      7   \n",
       "21611         1600      2388     2.0           0     0  ...      8   \n",
       "21612         1020      1076     2.0           0     0  ...      7   \n",
       "\n",
       "       sqft_above  sqft_basement  yr_built  yr_renovated  zipcode      lat  \\\n",
       "0            1180              0      1955             0    98178  47.5112   \n",
       "1            2170            400      1951          1991    98125  47.7210   \n",
       "2             770              0      1933             0    98028  47.7379   \n",
       "3            1050            910      1965             0    98136  47.5208   \n",
       "4            1680              0      1987             0    98074  47.6168   \n",
       "...           ...            ...       ...           ...      ...      ...   \n",
       "21608        1530              0      2009             0    98103  47.6993   \n",
       "21609        2310              0      2014             0    98146  47.5107   \n",
       "21610        1020              0      2009             0    98144  47.5944   \n",
       "21611        1600              0      2004             0    98027  47.5345   \n",
       "21612        1020              0      2008             0    98144  47.5941   \n",
       "\n",
       "          long  sqft_living15  sqft_lot15  \n",
       "0     -122.257           1340        5650  \n",
       "1     -122.319           1690        7639  \n",
       "2     -122.233           2720        8062  \n",
       "3     -122.393           1360        5000  \n",
       "4     -122.045           1800        7503  \n",
       "...        ...            ...         ...  \n",
       "21608 -122.346           1530        1509  \n",
       "21609 -122.362           1830        7200  \n",
       "21610 -122.299           1020        2007  \n",
       "21611 -122.069           1410        1287  \n",
       "21612 -122.299           1020        1357  \n",
       "\n",
       "[21613 rows x 21 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = Path('Resources/kc_house_data.csv')\n",
    "df = pd.read_csv(data)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4af1d0d0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 21613 entries, 0 to 21612\n",
      "Data columns (total 21 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   id             21613 non-null  int64  \n",
      " 1   date           21613 non-null  object \n",
      " 2   price          21613 non-null  float64\n",
      " 3   bedrooms       21613 non-null  int64  \n",
      " 4   bathrooms      21613 non-null  float64\n",
      " 5   sqft_living    21613 non-null  int64  \n",
      " 6   sqft_lot       21613 non-null  int64  \n",
      " 7   floors         21613 non-null  float64\n",
      " 8   waterfront     21613 non-null  int64  \n",
      " 9   view           21613 non-null  int64  \n",
      " 10  condition      21613 non-null  int64  \n",
      " 11  grade          21613 non-null  int64  \n",
      " 12  sqft_above     21613 non-null  int64  \n",
      " 13  sqft_basement  21613 non-null  int64  \n",
      " 14  yr_built       21613 non-null  int64  \n",
      " 15  yr_renovated   21613 non-null  int64  \n",
      " 16  zipcode        21613 non-null  int64  \n",
      " 17  lat            21613 non-null  float64\n",
      " 18  long           21613 non-null  float64\n",
      " 19  sqft_living15  21613 non-null  int64  \n",
      " 20  sqft_lot15     21613 non-null  int64  \n",
      "dtypes: float64(5), int64(15), object(1)\n",
      "memory usage: 3.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "300dd837",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21613, 21)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "be06a586",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id               21613\n",
       "date             21613\n",
       "price            21613\n",
       "bedrooms         21613\n",
       "bathrooms        21613\n",
       "sqft_living      21613\n",
       "sqft_lot         21613\n",
       "floors           21613\n",
       "waterfront       21613\n",
       "view             21613\n",
       "condition        21613\n",
       "grade            21613\n",
       "sqft_above       21613\n",
       "sqft_basement    21613\n",
       "yr_built         21613\n",
       "yr_renovated     21613\n",
       "zipcode          21613\n",
       "lat              21613\n",
       "long             21613\n",
       "sqft_living15    21613\n",
       "sqft_lot15       21613\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d9d0eb3d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column id has 0 null values\n",
      "Column date has 0 null values\n",
      "Column price has 0 null values\n",
      "Column bedrooms has 0 null values\n",
      "Column bathrooms has 0 null values\n",
      "Column sqft_living has 0 null values\n",
      "Column sqft_lot has 0 null values\n",
      "Column floors has 0 null values\n",
      "Column waterfront has 0 null values\n",
      "Column view has 0 null values\n",
      "Column condition has 0 null values\n",
      "Column grade has 0 null values\n",
      "Column sqft_above has 0 null values\n",
      "Column sqft_basement has 0 null values\n",
      "Column yr_built has 0 null values\n",
      "Column yr_renovated has 0 null values\n",
      "Column zipcode has 0 null values\n",
      "Column lat has 0 null values\n",
      "Column long has 0 null values\n",
      "Column sqft_living15 has 0 null values\n",
      "Column sqft_lot15 has 0 null values\n"
     ]
    }
   ],
   "source": [
    "#Checking for null values\n",
    "for column in df.columns:\n",
    "    print(f\"Column {column} has {df[column].isnull().sum()} null values\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a0719031",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df['date'] = pd.to_datetime(df['date']) #Dropping date column bcz won't gonna day and month and creating new year column\n",
    "#df['year'] = df['date'].dt.year\n",
    "#df = df.drop([\"date\", \"sqft_lot15\"],axis=1)\n",
    "#df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "21afdd70",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "177"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Checking for duplicates in id column\n",
    "df.id.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a2429bd9",
   "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>date</th>\n",
       "      <th>price</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>sqft_living</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>sqft_above</th>\n",
       "      <th>sqft_basement</th>\n",
       "      <th>yr_built</th>\n",
       "      <th>yr_renovated</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>sqft_living15</th>\n",
       "      <th>sqft_lot15</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7129300520</td>\n",
       "      <td>20141013T000000</td>\n",
       "      <td>221900.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1180</td>\n",
       "      <td>5650</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1180</td>\n",
       "      <td>0</td>\n",
       "      <td>1955</td>\n",
       "      <td>0</td>\n",
       "      <td>98178</td>\n",
       "      <td>47.5112</td>\n",
       "      <td>-122.257</td>\n",
       "      <td>1340</td>\n",
       "      <td>5650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6414100192</td>\n",
       "      <td>20141209T000000</td>\n",
       "      <td>538000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2570</td>\n",
       "      <td>7242</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>2170</td>\n",
       "      <td>400</td>\n",
       "      <td>1951</td>\n",
       "      <td>1991</td>\n",
       "      <td>98125</td>\n",
       "      <td>47.7210</td>\n",
       "      <td>-122.319</td>\n",
       "      <td>1690</td>\n",
       "      <td>7639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5631500400</td>\n",
       "      <td>20150225T000000</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>770</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>770</td>\n",
       "      <td>0</td>\n",
       "      <td>1933</td>\n",
       "      <td>0</td>\n",
       "      <td>98028</td>\n",
       "      <td>47.7379</td>\n",
       "      <td>-122.233</td>\n",
       "      <td>2720</td>\n",
       "      <td>8062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2487200875</td>\n",
       "      <td>20141209T000000</td>\n",
       "      <td>604000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1960</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1050</td>\n",
       "      <td>910</td>\n",
       "      <td>1965</td>\n",
       "      <td>0</td>\n",
       "      <td>98136</td>\n",
       "      <td>47.5208</td>\n",
       "      <td>-122.393</td>\n",
       "      <td>1360</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1954400510</td>\n",
       "      <td>20150218T000000</td>\n",
       "      <td>510000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1680</td>\n",
       "      <td>8080</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>1680</td>\n",
       "      <td>0</td>\n",
       "      <td>1987</td>\n",
       "      <td>0</td>\n",
       "      <td>98074</td>\n",
       "      <td>47.6168</td>\n",
       "      <td>-122.045</td>\n",
       "      <td>1800</td>\n",
       "      <td>7503</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>21608</th>\n",
       "      <td>263000018</td>\n",
       "      <td>20140521T000000</td>\n",
       "      <td>360000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1530</td>\n",
       "      <td>1131</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>1530</td>\n",
       "      <td>0</td>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>98103</td>\n",
       "      <td>47.6993</td>\n",
       "      <td>-122.346</td>\n",
       "      <td>1530</td>\n",
       "      <td>1509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21609</th>\n",
       "      <td>6600060120</td>\n",
       "      <td>20150223T000000</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2310</td>\n",
       "      <td>5813</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>2310</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "      <td>0</td>\n",
       "      <td>98146</td>\n",
       "      <td>47.5107</td>\n",
       "      <td>-122.362</td>\n",
       "      <td>1830</td>\n",
       "      <td>7200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21610</th>\n",
       "      <td>1523300141</td>\n",
       "      <td>20140623T000000</td>\n",
       "      <td>402101.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>1350</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1020</td>\n",
       "      <td>0</td>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>98144</td>\n",
       "      <td>47.5944</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21611</th>\n",
       "      <td>291310100</td>\n",
       "      <td>20150116T000000</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1600</td>\n",
       "      <td>2388</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>1600</td>\n",
       "      <td>0</td>\n",
       "      <td>2004</td>\n",
       "      <td>0</td>\n",
       "      <td>98027</td>\n",
       "      <td>47.5345</td>\n",
       "      <td>-122.069</td>\n",
       "      <td>1410</td>\n",
       "      <td>1287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21612</th>\n",
       "      <td>1523300157</td>\n",
       "      <td>20141015T000000</td>\n",
       "      <td>325000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>1076</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1020</td>\n",
       "      <td>0</td>\n",
       "      <td>2008</td>\n",
       "      <td>0</td>\n",
       "      <td>98144</td>\n",
       "      <td>47.5941</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>1357</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21436 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               id             date     price  bedrooms  bathrooms  \\\n",
       "0      7129300520  20141013T000000  221900.0         3       1.00   \n",
       "1      6414100192  20141209T000000  538000.0         3       2.25   \n",
       "2      5631500400  20150225T000000  180000.0         2       1.00   \n",
       "3      2487200875  20141209T000000  604000.0         4       3.00   \n",
       "4      1954400510  20150218T000000  510000.0         3       2.00   \n",
       "...           ...              ...       ...       ...        ...   \n",
       "21608   263000018  20140521T000000  360000.0         3       2.50   \n",
       "21609  6600060120  20150223T000000  400000.0         4       2.50   \n",
       "21610  1523300141  20140623T000000  402101.0         2       0.75   \n",
       "21611   291310100  20150116T000000  400000.0         3       2.50   \n",
       "21612  1523300157  20141015T000000  325000.0         2       0.75   \n",
       "\n",
       "       sqft_living  sqft_lot  floors  waterfront  view  ...  grade  \\\n",
       "0             1180      5650     1.0           0     0  ...      7   \n",
       "1             2570      7242     2.0           0     0  ...      7   \n",
       "2              770     10000     1.0           0     0  ...      6   \n",
       "3             1960      5000     1.0           0     0  ...      7   \n",
       "4             1680      8080     1.0           0     0  ...      8   \n",
       "...            ...       ...     ...         ...   ...  ...    ...   \n",
       "21608         1530      1131     3.0           0     0  ...      8   \n",
       "21609         2310      5813     2.0           0     0  ...      8   \n",
       "21610         1020      1350     2.0           0     0  ...      7   \n",
       "21611         1600      2388     2.0           0     0  ...      8   \n",
       "21612         1020      1076     2.0           0     0  ...      7   \n",
       "\n",
       "       sqft_above  sqft_basement  yr_built  yr_renovated  zipcode      lat  \\\n",
       "0            1180              0      1955             0    98178  47.5112   \n",
       "1            2170            400      1951          1991    98125  47.7210   \n",
       "2             770              0      1933             0    98028  47.7379   \n",
       "3            1050            910      1965             0    98136  47.5208   \n",
       "4            1680              0      1987             0    98074  47.6168   \n",
       "...           ...            ...       ...           ...      ...      ...   \n",
       "21608        1530              0      2009             0    98103  47.6993   \n",
       "21609        2310              0      2014             0    98146  47.5107   \n",
       "21610        1020              0      2009             0    98144  47.5944   \n",
       "21611        1600              0      2004             0    98027  47.5345   \n",
       "21612        1020              0      2008             0    98144  47.5941   \n",
       "\n",
       "          long  sqft_living15  sqft_lot15  \n",
       "0     -122.257           1340        5650  \n",
       "1     -122.319           1690        7639  \n",
       "2     -122.233           2720        8062  \n",
       "3     -122.393           1360        5000  \n",
       "4     -122.045           1800        7503  \n",
       "...        ...            ...         ...  \n",
       "21608 -122.346           1530        1509  \n",
       "21609 -122.362           1830        7200  \n",
       "21610 -122.299           1020        2007  \n",
       "21611 -122.069           1410        1287  \n",
       "21612 -122.299           1020        1357  \n",
       "\n",
       "[21436 rows x 21 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Dropping duplicates in 'id' columns and checking shape\n",
    "new_df = df.drop_duplicates(subset='id', keep='last', inplace=False)\n",
    "new_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1d290f3e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\chayd\\AppData\\Local\\Temp/ipykernel_75108/2967657252.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  new_df['date'] = pd.to_datetime(df['date'])\n"
     ]
    }
   ],
   "source": [
    "new_df['date'] = pd.to_datetime(df['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f45ce6e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        14/10/13\n",
       "1        14/12/09\n",
       "2        15/02/25\n",
       "3        14/12/09\n",
       "4        15/02/18\n",
       "           ...   \n",
       "21608    14/05/21\n",
       "21609    15/02/23\n",
       "21610    14/06/23\n",
       "21611    15/01/16\n",
       "21612    14/10/15\n",
       "Name: date, Length: 21436, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df['date'].dt.strftime('%y/%m/%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f8a55f58",
   "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>close_date</th>\n",
       "      <th>price</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>home_size</th>\n",
       "      <th>lot_size</th>\n",
       "      <th>floors</th>\n",
       "      <th>waterfront</th>\n",
       "      <th>views</th>\n",
       "      <th>...</th>\n",
       "      <th>grade</th>\n",
       "      <th>above_size</th>\n",
       "      <th>basement_size</th>\n",
       "      <th>yr_built</th>\n",
       "      <th>yr_renovated</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>homereno_2015</th>\n",
       "      <th>lotreno_2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7129300520</td>\n",
       "      <td>2014-10-13</td>\n",
       "      <td>221900.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1180</td>\n",
       "      <td>5650</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1180</td>\n",
       "      <td>0</td>\n",
       "      <td>1955</td>\n",
       "      <td>0</td>\n",
       "      <td>98178</td>\n",
       "      <td>47.5112</td>\n",
       "      <td>-122.257</td>\n",
       "      <td>1340</td>\n",
       "      <td>5650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6414100192</td>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>538000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2570</td>\n",
       "      <td>7242</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>2170</td>\n",
       "      <td>400</td>\n",
       "      <td>1951</td>\n",
       "      <td>1991</td>\n",
       "      <td>98125</td>\n",
       "      <td>47.7210</td>\n",
       "      <td>-122.319</td>\n",
       "      <td>1690</td>\n",
       "      <td>7639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5631500400</td>\n",
       "      <td>2015-02-25</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>770</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>770</td>\n",
       "      <td>0</td>\n",
       "      <td>1933</td>\n",
       "      <td>0</td>\n",
       "      <td>98028</td>\n",
       "      <td>47.7379</td>\n",
       "      <td>-122.233</td>\n",
       "      <td>2720</td>\n",
       "      <td>8062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2487200875</td>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>604000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1960</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1050</td>\n",
       "      <td>910</td>\n",
       "      <td>1965</td>\n",
       "      <td>0</td>\n",
       "      <td>98136</td>\n",
       "      <td>47.5208</td>\n",
       "      <td>-122.393</td>\n",
       "      <td>1360</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1954400510</td>\n",
       "      <td>2015-02-18</td>\n",
       "      <td>510000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1680</td>\n",
       "      <td>8080</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>1680</td>\n",
       "      <td>0</td>\n",
       "      <td>1987</td>\n",
       "      <td>0</td>\n",
       "      <td>98074</td>\n",
       "      <td>47.6168</td>\n",
       "      <td>-122.045</td>\n",
       "      <td>1800</td>\n",
       "      <td>7503</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id close_date     price  bedrooms  bathrooms  home_size  lot_size  \\\n",
       "0  7129300520 2014-10-13  221900.0         3       1.00       1180      5650   \n",
       "1  6414100192 2014-12-09  538000.0         3       2.25       2570      7242   \n",
       "2  5631500400 2015-02-25  180000.0         2       1.00        770     10000   \n",
       "3  2487200875 2014-12-09  604000.0         4       3.00       1960      5000   \n",
       "4  1954400510 2015-02-18  510000.0         3       2.00       1680      8080   \n",
       "\n",
       "   floors  waterfront  views  ...  grade  above_size  basement_size  yr_built  \\\n",
       "0     1.0           0      0  ...      7        1180              0      1955   \n",
       "1     2.0           0      0  ...      7        2170            400      1951   \n",
       "2     1.0           0      0  ...      6         770              0      1933   \n",
       "3     1.0           0      0  ...      7        1050            910      1965   \n",
       "4     1.0           0      0  ...      8        1680              0      1987   \n",
       "\n",
       "   yr_renovated  zipcode      lat     long  homereno_2015  lotreno_2015  \n",
       "0             0    98178  47.5112 -122.257           1340          5650  \n",
       "1          1991    98125  47.7210 -122.319           1690          7639  \n",
       "2             0    98028  47.7379 -122.233           2720          8062  \n",
       "3             0    98136  47.5208 -122.393           1360          5000  \n",
       "4             0    98074  47.6168 -122.045           1800          7503  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newcolumns_df = new_df.rename(columns={'sqft_living': 'home_size', 'sqft_lot': 'lot_size', 'view' : 'views', 'sqft_basement': 'basement_size','date': 'close_date',\n",
    "                              'sqft_living15': 'homereno_2015', 'sqft_above': 'above_size',\n",
    "                              'sqft_lot15': 'lotreno_2015'})\n",
    "newcolumns_df.head()                          "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "fa93884c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2.143600e+04\n",
       "mean     5.416500e+05\n",
       "std      3.673149e+05\n",
       "min      7.500000e+04\n",
       "25%      3.248660e+05\n",
       "50%      4.500000e+05\n",
       "75%      6.450000e+05\n",
       "max      7.700000e+06\n",
       "Name: price, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newcolumns_df[\"price\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "eb6ac3b9",
   "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>close_date</th>\n",
       "      <th>price</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>home_size</th>\n",
       "      <th>lot_size</th>\n",
       "      <th>floors</th>\n",
       "      <th>waterfront</th>\n",
       "      <th>views</th>\n",
       "      <th>...</th>\n",
       "      <th>grade</th>\n",
       "      <th>above_size</th>\n",
       "      <th>basement_size</th>\n",
       "      <th>yr_built</th>\n",
       "      <th>yr_renovated</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>homereno_2015</th>\n",
       "      <th>lotreno_2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7129300520</td>\n",
       "      <td>2014-10-13</td>\n",
       "      <td>221900.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1180</td>\n",
       "      <td>5650</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1180</td>\n",
       "      <td>0</td>\n",
       "      <td>1955</td>\n",
       "      <td>0</td>\n",
       "      <td>98178</td>\n",
       "      <td>47.5112</td>\n",
       "      <td>-122.257</td>\n",
       "      <td>1340</td>\n",
       "      <td>5650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6414100192</td>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>538000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2570</td>\n",
       "      <td>7242</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>2170</td>\n",
       "      <td>400</td>\n",
       "      <td>1951</td>\n",
       "      <td>1991</td>\n",
       "      <td>98125</td>\n",
       "      <td>47.7210</td>\n",
       "      <td>-122.319</td>\n",
       "      <td>1690</td>\n",
       "      <td>7639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5631500400</td>\n",
       "      <td>2015-02-25</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>770</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>770</td>\n",
       "      <td>0</td>\n",
       "      <td>1933</td>\n",
       "      <td>0</td>\n",
       "      <td>98028</td>\n",
       "      <td>47.7379</td>\n",
       "      <td>-122.233</td>\n",
       "      <td>2720</td>\n",
       "      <td>8062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2487200875</td>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>604000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1960</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1050</td>\n",
       "      <td>910</td>\n",
       "      <td>1965</td>\n",
       "      <td>0</td>\n",
       "      <td>98136</td>\n",
       "      <td>47.5208</td>\n",
       "      <td>-122.393</td>\n",
       "      <td>1360</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1954400510</td>\n",
       "      <td>2015-02-18</td>\n",
       "      <td>510000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1680</td>\n",
       "      <td>8080</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>1680</td>\n",
       "      <td>0</td>\n",
       "      <td>1987</td>\n",
       "      <td>0</td>\n",
       "      <td>98074</td>\n",
       "      <td>47.6168</td>\n",
       "      <td>-122.045</td>\n",
       "      <td>1800</td>\n",
       "      <td>7503</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>21608</th>\n",
       "      <td>263000018</td>\n",
       "      <td>2014-05-21</td>\n",
       "      <td>360000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1530</td>\n",
       "      <td>1131</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>1530</td>\n",
       "      <td>0</td>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>98103</td>\n",
       "      <td>47.6993</td>\n",
       "      <td>-122.346</td>\n",
       "      <td>1530</td>\n",
       "      <td>1509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21609</th>\n",
       "      <td>6600060120</td>\n",
       "      <td>2015-02-23</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2310</td>\n",
       "      <td>5813</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>2310</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "      <td>0</td>\n",
       "      <td>98146</td>\n",
       "      <td>47.5107</td>\n",
       "      <td>-122.362</td>\n",
       "      <td>1830</td>\n",
       "      <td>7200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21610</th>\n",
       "      <td>1523300141</td>\n",
       "      <td>2014-06-23</td>\n",
       "      <td>402101.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>1350</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1020</td>\n",
       "      <td>0</td>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>98144</td>\n",
       "      <td>47.5944</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21611</th>\n",
       "      <td>291310100</td>\n",
       "      <td>2015-01-16</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1600</td>\n",
       "      <td>2388</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>1600</td>\n",
       "      <td>0</td>\n",
       "      <td>2004</td>\n",
       "      <td>0</td>\n",
       "      <td>98027</td>\n",
       "      <td>47.5345</td>\n",
       "      <td>-122.069</td>\n",
       "      <td>1410</td>\n",
       "      <td>1287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21612</th>\n",
       "      <td>1523300157</td>\n",
       "      <td>2014-10-15</td>\n",
       "      <td>325000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>1076</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>1020</td>\n",
       "      <td>0</td>\n",
       "      <td>2008</td>\n",
       "      <td>0</td>\n",
       "      <td>98144</td>\n",
       "      <td>47.5941</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>1357</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21436 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               id close_date     price  bedrooms  bathrooms  home_size  \\\n",
       "0      7129300520 2014-10-13  221900.0         3       1.00       1180   \n",
       "1      6414100192 2014-12-09  538000.0         3       2.25       2570   \n",
       "2      5631500400 2015-02-25  180000.0         2       1.00        770   \n",
       "3      2487200875 2014-12-09  604000.0         4       3.00       1960   \n",
       "4      1954400510 2015-02-18  510000.0         3       2.00       1680   \n",
       "...           ...        ...       ...       ...        ...        ...   \n",
       "21608   263000018 2014-05-21  360000.0         3       2.50       1530   \n",
       "21609  6600060120 2015-02-23  400000.0         4       2.50       2310   \n",
       "21610  1523300141 2014-06-23  402101.0         2       0.75       1020   \n",
       "21611   291310100 2015-01-16  400000.0         3       2.50       1600   \n",
       "21612  1523300157 2014-10-15  325000.0         2       0.75       1020   \n",
       "\n",
       "       lot_size  floors  waterfront  views  ...  grade  above_size  \\\n",
       "0          5650     1.0           0      0  ...      7        1180   \n",
       "1          7242     2.0           0      0  ...      7        2170   \n",
       "2         10000     1.0           0      0  ...      6         770   \n",
       "3          5000     1.0           0      0  ...      7        1050   \n",
       "4          8080     1.0           0      0  ...      8        1680   \n",
       "...         ...     ...         ...    ...  ...    ...         ...   \n",
       "21608      1131     3.0           0      0  ...      8        1530   \n",
       "21609      5813     2.0           0      0  ...      8        2310   \n",
       "21610      1350     2.0           0      0  ...      7        1020   \n",
       "21611      2388     2.0           0      0  ...      8        1600   \n",
       "21612      1076     2.0           0      0  ...      7        1020   \n",
       "\n",
       "       basement_size  yr_built  yr_renovated  zipcode      lat     long  \\\n",
       "0                  0      1955             0    98178  47.5112 -122.257   \n",
       "1                400      1951          1991    98125  47.7210 -122.319   \n",
       "2                  0      1933             0    98028  47.7379 -122.233   \n",
       "3                910      1965             0    98136  47.5208 -122.393   \n",
       "4                  0      1987             0    98074  47.6168 -122.045   \n",
       "...              ...       ...           ...      ...      ...      ...   \n",
       "21608              0      2009             0    98103  47.6993 -122.346   \n",
       "21609              0      2014             0    98146  47.5107 -122.362   \n",
       "21610              0      2009             0    98144  47.5944 -122.299   \n",
       "21611              0      2004             0    98027  47.5345 -122.069   \n",
       "21612              0      2008             0    98144  47.5941 -122.299   \n",
       "\n",
       "       homereno_2015  lotreno_2015  \n",
       "0               1340          5650  \n",
       "1               1690          7639  \n",
       "2               2720          8062  \n",
       "3               1360          5000  \n",
       "4               1800          7503  \n",
       "...              ...           ...  \n",
       "21608           1530          1509  \n",
       "21609           1830          7200  \n",
       "21610           1020          2007  \n",
       "21611           1410          1287  \n",
       "21612           1020          1357  \n",
       "\n",
       "[21436 rows x 21 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newcolumns_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8985f0f8",
   "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>close_date</th>\n",
       "      <th>price</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>home_size</th>\n",
       "      <th>lot_size</th>\n",
       "      <th>floors</th>\n",
       "      <th>waterfront</th>\n",
       "      <th>views</th>\n",
       "      <th>condition</th>\n",
       "      <th>grade</th>\n",
       "      <th>above_size</th>\n",
       "      <th>basement_size</th>\n",
       "      <th>yr_built</th>\n",
       "      <th>yr_renovated</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>homereno_2015</th>\n",
       "      <th>lotreno_2015</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7129300520</th>\n",
       "      <td>2014-10-13</td>\n",
       "      <td>221900.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1180</td>\n",
       "      <td>5650</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1180</td>\n",
       "      <td>0</td>\n",
       "      <td>1955</td>\n",
       "      <td>0</td>\n",
       "      <td>98178</td>\n",
       "      <td>47.5112</td>\n",
       "      <td>-122.257</td>\n",
       "      <td>1340</td>\n",
       "      <td>5650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6414100192</th>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>538000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2570</td>\n",
       "      <td>7242</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>2170</td>\n",
       "      <td>400</td>\n",
       "      <td>1951</td>\n",
       "      <td>1991</td>\n",
       "      <td>98125</td>\n",
       "      <td>47.7210</td>\n",
       "      <td>-122.319</td>\n",
       "      <td>1690</td>\n",
       "      <td>7639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5631500400</th>\n",
       "      <td>2015-02-25</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>770</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>770</td>\n",
       "      <td>0</td>\n",
       "      <td>1933</td>\n",
       "      <td>0</td>\n",
       "      <td>98028</td>\n",
       "      <td>47.7379</td>\n",
       "      <td>-122.233</td>\n",
       "      <td>2720</td>\n",
       "      <td>8062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2487200875</th>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>604000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1960</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>1050</td>\n",
       "      <td>910</td>\n",
       "      <td>1965</td>\n",
       "      <td>0</td>\n",
       "      <td>98136</td>\n",
       "      <td>47.5208</td>\n",
       "      <td>-122.393</td>\n",
       "      <td>1360</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1954400510</th>\n",
       "      <td>2015-02-18</td>\n",
       "      <td>510000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1680</td>\n",
       "      <td>8080</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>1680</td>\n",
       "      <td>0</td>\n",
       "      <td>1987</td>\n",
       "      <td>0</td>\n",
       "      <td>98074</td>\n",
       "      <td>47.6168</td>\n",
       "      <td>-122.045</td>\n",
       "      <td>1800</td>\n",
       "      <td>7503</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263000018</th>\n",
       "      <td>2014-05-21</td>\n",
       "      <td>360000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1530</td>\n",
       "      <td>1131</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>1530</td>\n",
       "      <td>0</td>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>98103</td>\n",
       "      <td>47.6993</td>\n",
       "      <td>-122.346</td>\n",
       "      <td>1530</td>\n",
       "      <td>1509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6600060120</th>\n",
       "      <td>2015-02-23</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2310</td>\n",
       "      <td>5813</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>2310</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "      <td>0</td>\n",
       "      <td>98146</td>\n",
       "      <td>47.5107</td>\n",
       "      <td>-122.362</td>\n",
       "      <td>1830</td>\n",
       "      <td>7200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1523300141</th>\n",
       "      <td>2014-06-23</td>\n",
       "      <td>402101.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>1350</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1020</td>\n",
       "      <td>0</td>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>98144</td>\n",
       "      <td>47.5944</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291310100</th>\n",
       "      <td>2015-01-16</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1600</td>\n",
       "      <td>2388</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>1600</td>\n",
       "      <td>0</td>\n",
       "      <td>2004</td>\n",
       "      <td>0</td>\n",
       "      <td>98027</td>\n",
       "      <td>47.5345</td>\n",
       "      <td>-122.069</td>\n",
       "      <td>1410</td>\n",
       "      <td>1287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1523300157</th>\n",
       "      <td>2014-10-15</td>\n",
       "      <td>325000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>1076</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1020</td>\n",
       "      <td>0</td>\n",
       "      <td>2008</td>\n",
       "      <td>0</td>\n",
       "      <td>98144</td>\n",
       "      <td>47.5941</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>1357</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21436 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           close_date     price  bedrooms  bathrooms  home_size  lot_size  \\\n",
       "id                                                                          \n",
       "7129300520 2014-10-13  221900.0         3       1.00       1180      5650   \n",
       "6414100192 2014-12-09  538000.0         3       2.25       2570      7242   \n",
       "5631500400 2015-02-25  180000.0         2       1.00        770     10000   \n",
       "2487200875 2014-12-09  604000.0         4       3.00       1960      5000   \n",
       "1954400510 2015-02-18  510000.0         3       2.00       1680      8080   \n",
       "...               ...       ...       ...        ...        ...       ...   \n",
       "263000018  2014-05-21  360000.0         3       2.50       1530      1131   \n",
       "6600060120 2015-02-23  400000.0         4       2.50       2310      5813   \n",
       "1523300141 2014-06-23  402101.0         2       0.75       1020      1350   \n",
       "291310100  2015-01-16  400000.0         3       2.50       1600      2388   \n",
       "1523300157 2014-10-15  325000.0         2       0.75       1020      1076   \n",
       "\n",
       "            floors  waterfront  views  condition  grade  above_size  \\\n",
       "id                                                                    \n",
       "7129300520     1.0           0      0          3      7        1180   \n",
       "6414100192     2.0           0      0          3      7        2170   \n",
       "5631500400     1.0           0      0          3      6         770   \n",
       "2487200875     1.0           0      0          5      7        1050   \n",
       "1954400510     1.0           0      0          3      8        1680   \n",
       "...            ...         ...    ...        ...    ...         ...   \n",
       "263000018      3.0           0      0          3      8        1530   \n",
       "6600060120     2.0           0      0          3      8        2310   \n",
       "1523300141     2.0           0      0          3      7        1020   \n",
       "291310100      2.0           0      0          3      8        1600   \n",
       "1523300157     2.0           0      0          3      7        1020   \n",
       "\n",
       "            basement_size  yr_built  yr_renovated  zipcode      lat     long  \\\n",
       "id                                                                             \n",
       "7129300520              0      1955             0    98178  47.5112 -122.257   \n",
       "6414100192            400      1951          1991    98125  47.7210 -122.319   \n",
       "5631500400              0      1933             0    98028  47.7379 -122.233   \n",
       "2487200875            910      1965             0    98136  47.5208 -122.393   \n",
       "1954400510              0      1987             0    98074  47.6168 -122.045   \n",
       "...                   ...       ...           ...      ...      ...      ...   \n",
       "263000018               0      2009             0    98103  47.6993 -122.346   \n",
       "6600060120              0      2014             0    98146  47.5107 -122.362   \n",
       "1523300141              0      2009             0    98144  47.5944 -122.299   \n",
       "291310100               0      2004             0    98027  47.5345 -122.069   \n",
       "1523300157              0      2008             0    98144  47.5941 -122.299   \n",
       "\n",
       "            homereno_2015  lotreno_2015  \n",
       "id                                       \n",
       "7129300520           1340          5650  \n",
       "6414100192           1690          7639  \n",
       "5631500400           2720          8062  \n",
       "2487200875           1360          5000  \n",
       "1954400510           1800          7503  \n",
       "...                   ...           ...  \n",
       "263000018            1530          1509  \n",
       "6600060120           1830          7200  \n",
       "1523300141           1020          2007  \n",
       "291310100            1410          1287  \n",
       "1523300157           1020          1357  \n",
       "\n",
       "[21436 rows x 20 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "house_data_clean_df = newcolumns_df.set_index('id')\n",
    "house_data_clean_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c89f5f1f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id\n",
       "7129300520    394.0\n",
       "6414100192    724.0\n",
       "5631500400    385.0\n",
       "2487200875    263.0\n",
       "1954400510    560.0\n",
       "              ...  \n",
       "263000018     510.0\n",
       "6600060120    578.0\n",
       "1523300141    510.0\n",
       "291310100     534.0\n",
       "1523300157    510.0\n",
       "Name: above_size by bedrooms, Length: 21436, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "house_data_clean_df[\"above_size by bedrooms\"] = house_data_clean_df[\"above_size\"]/house_data_clean_df[\"bedrooms\"]\n",
    "house_data_clean_df[\"above_size by bedrooms\"].apply(np.ceil)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6827b68c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['close_date', 'price', 'bedrooms', 'bathrooms', 'home_size', 'lot_size',\n",
       "       'floors', 'waterfront', 'views', 'condition', 'grade', 'above_size',\n",
       "       'basement_size', 'yr_built', 'yr_renovated', 'zipcode', 'lat', 'long',\n",
       "       'homereno_2015', 'lotreno_2015', 'above_size by bedrooms'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "house_data_clean_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c1021fcd",
   "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>close_date</th>\n",
       "      <th>price</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>home_size</th>\n",
       "      <th>lot_size</th>\n",
       "      <th>floors</th>\n",
       "      <th>waterfront</th>\n",
       "      <th>views</th>\n",
       "      <th>condition</th>\n",
       "      <th>...</th>\n",
       "      <th>above_size</th>\n",
       "      <th>basement_size</th>\n",
       "      <th>yr_built</th>\n",
       "      <th>yr_renovated</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>homereno_2015</th>\n",
       "      <th>lotreno_2015</th>\n",
       "      <th>above_size by bedrooms</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7129300520</th>\n",
       "      <td>2014-10-13</td>\n",
       "      <td>221900.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1180</td>\n",
       "      <td>5650</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1180</td>\n",
       "      <td>0</td>\n",
       "      <td>1955</td>\n",
       "      <td>0</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>393.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6414100192</th>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>538000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2570</td>\n",
       "      <td>7242</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>2170</td>\n",
       "      <td>400</td>\n",
       "      <td>1951</td>\n",
       "      <td>1991</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>723.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5631500400</th>\n",
       "      <td>2015-02-25</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>770</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>770</td>\n",
       "      <td>0</td>\n",
       "      <td>1933</td>\n",
       "      <td>0</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>385.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2487200875</th>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>604000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1960</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>1050</td>\n",
       "      <td>910</td>\n",
       "      <td>1965</td>\n",
       "      <td>0</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>262.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1954400510</th>\n",
       "      <td>2015-02-18</td>\n",
       "      <td>510000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1680</td>\n",
       "      <td>8080</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1680</td>\n",
       "      <td>0</td>\n",
       "      <td>1987</td>\n",
       "      <td>0</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>560.000000</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>263000018</th>\n",
       "      <td>2014-05-21</td>\n",
       "      <td>360000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1530</td>\n",
       "      <td>1131</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1530</td>\n",
       "      <td>0</td>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>98103</td>\n",
       "      <td>47.6993</td>\n",
       "      <td>-122.346</td>\n",
       "      <td>1530</td>\n",
       "      <td>1509</td>\n",
       "      <td>510.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6600060120</th>\n",
       "      <td>2015-02-23</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2310</td>\n",
       "      <td>5813</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>2310</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "      <td>0</td>\n",
       "      <td>98146</td>\n",
       "      <td>47.5107</td>\n",
       "      <td>-122.362</td>\n",
       "      <td>1830</td>\n",
       "      <td>7200</td>\n",
       "      <td>577.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1523300141</th>\n",
       "      <td>2014-06-23</td>\n",
       "      <td>402101.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>1350</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1020</td>\n",
       "      <td>0</td>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>98144</td>\n",
       "      <td>47.5944</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>2007</td>\n",
       "      <td>510.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291310100</th>\n",
       "      <td>2015-01-16</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1600</td>\n",
       "      <td>2388</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1600</td>\n",
       "      <td>0</td>\n",
       "      <td>2004</td>\n",
       "      <td>0</td>\n",
       "      <td>98027</td>\n",
       "      <td>47.5345</td>\n",
       "      <td>-122.069</td>\n",
       "      <td>1410</td>\n",
       "      <td>1287</td>\n",
       "      <td>533.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1523300157</th>\n",
       "      <td>2014-10-15</td>\n",
       "      <td>325000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1020</td>\n",
       "      <td>1076</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1020</td>\n",
       "      <td>0</td>\n",
       "      <td>2008</td>\n",
       "      <td>0</td>\n",
       "      <td>98144</td>\n",
       "      <td>47.5941</td>\n",
       "      <td>-122.299</td>\n",
       "      <td>1020</td>\n",
       "      <td>1357</td>\n",
       "      <td>510.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21436 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           close_date     price  bedrooms  bathrooms  home_size  lot_size  \\\n",
       "id                                                                          \n",
       "7129300520 2014-10-13  221900.0         3       1.00       1180      5650   \n",
       "6414100192 2014-12-09  538000.0         3       2.25       2570      7242   \n",
       "5631500400 2015-02-25  180000.0         2       1.00        770     10000   \n",
       "2487200875 2014-12-09  604000.0         4       3.00       1960      5000   \n",
       "1954400510 2015-02-18  510000.0         3       2.00       1680      8080   \n",
       "...               ...       ...       ...        ...        ...       ...   \n",
       "263000018  2014-05-21  360000.0         3       2.50       1530      1131   \n",
       "6600060120 2015-02-23  400000.0         4       2.50       2310      5813   \n",
       "1523300141 2014-06-23  402101.0         2       0.75       1020      1350   \n",
       "291310100  2015-01-16  400000.0         3       2.50       1600      2388   \n",
       "1523300157 2014-10-15  325000.0         2       0.75       1020      1076   \n",
       "\n",
       "            floors  waterfront  views  condition  ...  above_size  \\\n",
       "id                                                ...               \n",
       "7129300520     1.0           0      0          3  ...        1180   \n",
       "6414100192     2.0           0      0          3  ...        2170   \n",
       "5631500400     1.0           0      0          3  ...         770   \n",
       "2487200875     1.0           0      0          5  ...        1050   \n",
       "1954400510     1.0           0      0          3  ...        1680   \n",
       "...            ...         ...    ...        ...  ...         ...   \n",
       "263000018      3.0           0      0          3  ...        1530   \n",
       "6600060120     2.0           0      0          3  ...        2310   \n",
       "1523300141     2.0           0      0          3  ...        1020   \n",
       "291310100      2.0           0      0          3  ...        1600   \n",
       "1523300157     2.0           0      0          3  ...        1020   \n",
       "\n",
       "            basement_size  yr_built  yr_renovated  zipcode      lat     long  \\\n",
       "id                                                                             \n",
       "7129300520              0      1955             0    98178  47.5112 -122.257   \n",
       "6414100192            400      1951          1991    98125  47.7210 -122.319   \n",
       "5631500400              0      1933             0    98028  47.7379 -122.233   \n",
       "2487200875            910      1965             0    98136  47.5208 -122.393   \n",
       "1954400510              0      1987             0    98074  47.6168 -122.045   \n",
       "...                   ...       ...           ...      ...      ...      ...   \n",
       "263000018               0      2009             0    98103  47.6993 -122.346   \n",
       "6600060120              0      2014             0    98146  47.5107 -122.362   \n",
       "1523300141              0      2009             0    98144  47.5944 -122.299   \n",
       "291310100               0      2004             0    98027  47.5345 -122.069   \n",
       "1523300157              0      2008             0    98144  47.5941 -122.299   \n",
       "\n",
       "            homereno_2015  lotreno_2015  above_size by bedrooms  \n",
       "id                                                               \n",
       "7129300520           1340          5650              393.333333  \n",
       "6414100192           1690          7639              723.333333  \n",
       "5631500400           2720          8062              385.000000  \n",
       "2487200875           1360          5000              262.500000  \n",
       "1954400510           1800          7503              560.000000  \n",
       "...                   ...           ...                     ...  \n",
       "263000018            1530          1509              510.000000  \n",
       "6600060120           1830          7200              577.500000  \n",
       "1523300141           1020          2007              510.000000  \n",
       "291310100            1410          1287              533.333333  \n",
       "1523300157           1020          1357              510.000000  \n",
       "\n",
       "[21436 rows x 21 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "house_data_clean_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "7236a1e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "house_data_clean_df.to_csv('Resources/kc_house_clean.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "73281ae4",
   "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>condition</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>needs repair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>poor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>good</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>very good</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>excellent</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  condition   description\n",
       "0         1  needs repair\n",
       "1         2          poor\n",
       "2         3          good\n",
       "3         4     very good\n",
       "4         5     excellent"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "condition_df = pd.DataFrame(columns = ['condition', 'description'])\n",
    "condition_df.loc[0] = [1, 'needs repair']\n",
    "condition_df.loc[1] = [2, 'poor']\n",
    "condition_df.loc[2] = [3, 'good']\n",
    "condition_df.loc[3] = [4, 'very good']\n",
    "condition_df.loc[4] = [5, 'excellent']\n",
    "condition_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "12e1ccb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "condition_df.to_csv('Resources/condition.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b313c426",
   "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.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}