SQL_data_cleaning_project.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "db658f64",
   "metadata": {},
   "source": [
    "### 1. Import libraries, connect to SQL database, and convert dataframe to SQL table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7366332a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.simplefilter(action='ignore', category=UserWarning)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8ffdc9a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "fea1f6fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import xlsx file\n",
    "file_name = \"nashville_housing_data.xlsx\"\n",
    "my_sheet_name = \"Sheet1\"\n",
    "\n",
    "df = pd.read_excel(file_name, sheet_name = my_sheet_name, index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2eee5bb6",
   "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>ParcelID</th>\n",
       "      <th>LandUse</th>\n",
       "      <th>PropertyAddress</th>\n",
       "      <th>SaleDate</th>\n",
       "      <th>SalePrice</th>\n",
       "      <th>LegalReference</th>\n",
       "      <th>SoldAsVacant</th>\n",
       "      <th>OwnerName</th>\n",
       "      <th>OwnerAddress</th>\n",
       "      <th>Acreage</th>\n",
       "      <th>TaxDistrict</th>\n",
       "      <th>LandValue</th>\n",
       "      <th>BuildingValue</th>\n",
       "      <th>TotalValue</th>\n",
       "      <th>YearBuilt</th>\n",
       "      <th>Bedrooms</th>\n",
       "      <th>FullBath</th>\n",
       "      <th>HalfBath</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UniqueID</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>2045</th>\n",
       "      <td>007 00 0 125.00</td>\n",
       "      <td>SINGLE FAMILY</td>\n",
       "      <td>1808  FOX CHASE DR, GOODLETTSVILLE</td>\n",
       "      <td>2013-04-09</td>\n",
       "      <td>240000</td>\n",
       "      <td>20130412-0036474</td>\n",
       "      <td>No</td>\n",
       "      <td>FRAZIER, CYRENTHA LYNETTE</td>\n",
       "      <td>1808  FOX CHASE DR, GOODLETTSVILLE, TN</td>\n",
       "      <td>2.3</td>\n",
       "      <td>GENERAL SERVICES DISTRICT</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>168200.0</td>\n",
       "      <td>235700.0</td>\n",
       "      <td>1986.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16918</th>\n",
       "      <td>007 00 0 130.00</td>\n",
       "      <td>SINGLE FAMILY</td>\n",
       "      <td>1832  FOX CHASE DR, GOODLETTSVILLE</td>\n",
       "      <td>2014-06-10</td>\n",
       "      <td>366000</td>\n",
       "      <td>20140619-0053768</td>\n",
       "      <td>No</td>\n",
       "      <td>BONER, CHARLES &amp; LESLIE</td>\n",
       "      <td>1832  FOX CHASE DR, GOODLETTSVILLE, TN</td>\n",
       "      <td>3.5</td>\n",
       "      <td>GENERAL SERVICES DISTRICT</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>264100.0</td>\n",
       "      <td>319000.0</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54582</th>\n",
       "      <td>007 00 0 138.00</td>\n",
       "      <td>SINGLE FAMILY</td>\n",
       "      <td>1864 FOX CHASE  DR, GOODLETTSVILLE</td>\n",
       "      <td>2016-09-26</td>\n",
       "      <td>435000</td>\n",
       "      <td>20160927-0101718</td>\n",
       "      <td>No</td>\n",
       "      <td>WILSON, JAMES E. &amp; JOANNE</td>\n",
       "      <td>1864  FOX CHASE DR, GOODLETTSVILLE, TN</td>\n",
       "      <td>2.9</td>\n",
       "      <td>GENERAL SERVICES DISTRICT</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>216200.0</td>\n",
       "      <td>298000.0</td>\n",
       "      <td>1987.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43070</th>\n",
       "      <td>007 00 0 143.00</td>\n",
       "      <td>SINGLE FAMILY</td>\n",
       "      <td>1853  FOX CHASE DR, GOODLETTSVILLE</td>\n",
       "      <td>2016-01-29</td>\n",
       "      <td>255000</td>\n",
       "      <td>20160129-0008913</td>\n",
       "      <td>No</td>\n",
       "      <td>BAKER, JAY K. &amp; SUSAN E.</td>\n",
       "      <td>1853  FOX CHASE DR, GOODLETTSVILLE, TN</td>\n",
       "      <td>2.6</td>\n",
       "      <td>GENERAL SERVICES DISTRICT</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>147300.0</td>\n",
       "      <td>197300.0</td>\n",
       "      <td>1985.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22714</th>\n",
       "      <td>007 00 0 149.00</td>\n",
       "      <td>SINGLE FAMILY</td>\n",
       "      <td>1829  FOX CHASE DR, GOODLETTSVILLE</td>\n",
       "      <td>2014-10-10</td>\n",
       "      <td>278000</td>\n",
       "      <td>20141015-0095255</td>\n",
       "      <td>No</td>\n",
       "      <td>POST, CHRISTOPHER M. &amp; SAMANTHA C.</td>\n",
       "      <td>1829  FOX CHASE DR, GOODLETTSVILLE, TN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>GENERAL SERVICES DISTRICT</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>152300.0</td>\n",
       "      <td>202300.0</td>\n",
       "      <td>1984.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ParcelID        LandUse                     PropertyAddress  \\\n",
       "UniqueID                                                                        \n",
       "2045       007 00 0 125.00  SINGLE FAMILY  1808  FOX CHASE DR, GOODLETTSVILLE   \n",
       "16918      007 00 0 130.00  SINGLE FAMILY  1832  FOX CHASE DR, GOODLETTSVILLE   \n",
       "54582      007 00 0 138.00  SINGLE FAMILY  1864 FOX CHASE  DR, GOODLETTSVILLE   \n",
       "43070      007 00 0 143.00  SINGLE FAMILY  1853  FOX CHASE DR, GOODLETTSVILLE   \n",
       "22714      007 00 0 149.00  SINGLE FAMILY  1829  FOX CHASE DR, GOODLETTSVILLE   \n",
       "\n",
       "            SaleDate  SalePrice    LegalReference SoldAsVacant  \\\n",
       "UniqueID                                                         \n",
       "2045      2013-04-09     240000  20130412-0036474           No   \n",
       "16918     2014-06-10     366000  20140619-0053768           No   \n",
       "54582     2016-09-26     435000  20160927-0101718           No   \n",
       "43070     2016-01-29     255000  20160129-0008913           No   \n",
       "22714     2014-10-10     278000  20141015-0095255           No   \n",
       "\n",
       "                                    OwnerName  \\\n",
       "UniqueID                                        \n",
       "2045                FRAZIER, CYRENTHA LYNETTE   \n",
       "16918                 BONER, CHARLES & LESLIE   \n",
       "54582               WILSON, JAMES E. & JOANNE   \n",
       "43070                BAKER, JAY K. & SUSAN E.   \n",
       "22714      POST, CHRISTOPHER M. & SAMANTHA C.   \n",
       "\n",
       "                                     OwnerAddress  Acreage  \\\n",
       "UniqueID                                                     \n",
       "2045       1808  FOX CHASE DR, GOODLETTSVILLE, TN      2.3   \n",
       "16918      1832  FOX CHASE DR, GOODLETTSVILLE, TN      3.5   \n",
       "54582      1864  FOX CHASE DR, GOODLETTSVILLE, TN      2.9   \n",
       "43070      1853  FOX CHASE DR, GOODLETTSVILLE, TN      2.6   \n",
       "22714      1829  FOX CHASE DR, GOODLETTSVILLE, TN      2.0   \n",
       "\n",
       "                         TaxDistrict  LandValue  BuildingValue  TotalValue  \\\n",
       "UniqueID                                                                     \n",
       "2045       GENERAL SERVICES DISTRICT    50000.0       168200.0    235700.0   \n",
       "16918      GENERAL SERVICES DISTRICT    50000.0       264100.0    319000.0   \n",
       "54582      GENERAL SERVICES DISTRICT    50000.0       216200.0    298000.0   \n",
       "43070      GENERAL SERVICES DISTRICT    50000.0       147300.0    197300.0   \n",
       "22714      GENERAL SERVICES DISTRICT    50000.0       152300.0    202300.0   \n",
       "\n",
       "           YearBuilt  Bedrooms  FullBath  HalfBath  \n",
       "UniqueID                                            \n",
       "2045          1986.0       3.0       3.0       0.0  \n",
       "16918         1998.0       3.0       3.0       2.0  \n",
       "54582         1987.0       4.0       3.0       0.0  \n",
       "43070         1985.0       3.0       3.0       0.0  \n",
       "22714         1984.0       4.0       3.0       0.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "60219cc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to SQL database\n",
    "conn = sqlite3.connect('my_projects.db')\n",
    "\n",
    "df.to_sql(\"nashville_housing\", conn, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f2ed19d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "\n",
    "%sql sqlite:///my_projects.db"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e04ab1e7",
   "metadata": {},
   "source": [
    "### 2. Populate null values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b2ceb91",
   "metadata": {},
   "source": [
    "#### 2.1 check null values in \"PropertyAddress\" column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5ea206fd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>UniqueID </th>\n",
       "        <th>ParcelID</th>\n",
       "        <th>LandUse</th>\n",
       "        <th>PropertyAddress</th>\n",
       "        <th>SaleDate</th>\n",
       "        <th>SalePrice</th>\n",
       "        <th>LegalReference</th>\n",
       "        <th>SoldAsVacant</th>\n",
       "        <th>OwnerName</th>\n",
       "        <th>OwnerAddress</th>\n",
       "        <th>Acreage</th>\n",
       "        <th>TaxDistrict</th>\n",
       "        <th>LandValue</th>\n",
       "        <th>BuildingValue</th>\n",
       "        <th>TotalValue</th>\n",
       "        <th>YearBuilt</th>\n",
       "        <th>Bedrooms</th>\n",
       "        <th>FullBath</th>\n",
       "        <th>HalfBath</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43076</td>\n",
       "        <td>025 07 0 031.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-01-15 00:00:00</td>\n",
       "        <td>179900</td>\n",
       "        <td>20160120-0005776</td>\n",
       "        <td>No</td>\n",
       "        <td>COSTNER, FRED &amp; CAROLYN</td>\n",
       "        <td>410  ROSEHILL CT, GOODLETTSVILLE, TN</td>\n",
       "        <td>0.96</td>\n",
       "        <td>CITY OF GOODLETTSVILLE</td>\n",
       "        <td>30000.0</td>\n",
       "        <td>70000.0</td>\n",
       "        <td>100000.0</td>\n",
       "        <td>1964.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>1.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39432</td>\n",
       "        <td>026 01 0 069.00</td>\n",
       "        <td>VACANT RESIDENTIAL LAND</td>\n",
       "        <td>None</td>\n",
       "        <td>2015-10-23 00:00:00</td>\n",
       "        <td>153000</td>\n",
       "        <td>20151028-0109602</td>\n",
       "        <td>No</td>\n",
       "        <td>SHACKLEFORD, MICHAEL C., JR.</td>\n",
       "        <td>141  TWO MILE PIKE, GOODLETTSVILLE, TN</td>\n",
       "        <td>0.17</td>\n",
       "        <td>CITY OF GOODLETTSVILLE</td>\n",
       "        <td>21100.0</td>\n",
       "        <td>121600.0</td>\n",
       "        <td>142700.0</td>\n",
       "        <td>2015.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45290</td>\n",
       "        <td>026 05 0 017.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-03-29 00:00:00</td>\n",
       "        <td>155000</td>\n",
       "        <td>20160330-0029941</td>\n",
       "        <td>No</td>\n",
       "        <td>TRIPP, MARVIN S. &amp; DEBORAH YOUNG</td>\n",
       "        <td>208  EAST AVE, GOODLETTSVILLE, TN</td>\n",
       "        <td>0.2</td>\n",
       "        <td>CITY OF GOODLETTSVILLE</td>\n",
       "        <td>21100.0</td>\n",
       "        <td>130200.0</td>\n",
       "        <td>151300.0</td>\n",
       "        <td>2008.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53147</td>\n",
       "        <td>026 06 0A 038.00</td>\n",
       "        <td>RESIDENTIAL CONDO</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-08-25 00:00:00</td>\n",
       "        <td>144900</td>\n",
       "        <td>20160831-0091567</td>\n",
       "        <td>No</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43080</td>\n",
       "        <td>033 06 0 041.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-01-04 00:00:00</td>\n",
       "        <td>170000</td>\n",
       "        <td>20160107-0001526</td>\n",
       "        <td>No</td>\n",
       "        <td>FRANK, ZACHARY &amp; NIKI</td>\n",
       "        <td>1129  CAMPBELL RD, GOODLETTSVILLE, TN</td>\n",
       "        <td>0.24</td>\n",
       "        <td>GENERAL SERVICES DISTRICT</td>\n",
       "        <td>35000.0</td>\n",
       "        <td>110500.0</td>\n",
       "        <td>145500.0</td>\n",
       "        <td>2000.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45295</td>\n",
       "        <td>033 06 0A 002.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-03-29 00:00:00</td>\n",
       "        <td>210000</td>\n",
       "        <td>20160331-0030709</td>\n",
       "        <td>No</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48731</td>\n",
       "        <td>033 15 0 123.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-05-05 00:00:00</td>\n",
       "        <td>199900</td>\n",
       "        <td>20160506-0045368</td>\n",
       "        <td>No</td>\n",
       "        <td>COLEMAN, AARON A. &amp; CECIL, CORRIE J.</td>\n",
       "        <td>438  W CAMPBELL RD, GOODLETTSVILLE, TN</td>\n",
       "        <td>1.39</td>\n",
       "        <td>GENERAL SERVICES DISTRICT</td>\n",
       "        <td>45000.0</td>\n",
       "        <td>90300.0</td>\n",
       "        <td>135300.0</td>\n",
       "        <td>1954.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>1.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36531</td>\n",
       "        <td>034 03 0 059.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2015-08-13 00:00:00</td>\n",
       "        <td>245000</td>\n",
       "        <td>20150819-0083759</td>\n",
       "        <td>No</td>\n",
       "        <td>DILICK, JOHN MARK &amp; ANNETTE A.</td>\n",
       "        <td>2117  PAULA DR, MADISON, TN</td>\n",
       "        <td>1.01</td>\n",
       "        <td>GENERAL SERVICES DISTRICT</td>\n",
       "        <td>32000.0</td>\n",
       "        <td>170000.0</td>\n",
       "        <td>228300.0</td>\n",
       "        <td>1964.0</td>\n",
       "        <td>4.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46919</td>\n",
       "        <td>034 07 0B 015.00</td>\n",
       "        <td>VACANT RESIDENTIAL LAND</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-04-27 00:00:00</td>\n",
       "        <td>40000</td>\n",
       "        <td>20160304-0020905</td>\n",
       "        <td>Yes</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44264</td>\n",
       "        <td>034 16 0A 004.00</td>\n",
       "        <td>VACANT RESIDENTIAL LAND</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-02-04 00:00:00</td>\n",
       "        <td>130000</td>\n",
       "        <td>20160205-0011327</td>\n",
       "        <td>Yes</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45298</td>\n",
       "        <td>041 03 0A 100.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-03-18 00:00:00</td>\n",
       "        <td>170000</td>\n",
       "        <td>20160322-0027048</td>\n",
       "        <td>No</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40678</td>\n",
       "        <td>042 13 0 075.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2015-11-30 00:00:00</td>\n",
       "        <td>208000</td>\n",
       "        <td>20151209-0123831</td>\n",
       "        <td>No</td>\n",
       "        <td>SADOWSKY, ERIC C. &amp; LISA D.</td>\n",
       "        <td>222  FOXBORO DR, MADISON, TN</td>\n",
       "        <td>1.01</td>\n",
       "        <td>GENERAL SERVICES DISTRICT</td>\n",
       "        <td>30000.0</td>\n",
       "        <td>100000.0</td>\n",
       "        <td>130000.0</td>\n",
       "        <td>1967.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>1.0</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47293</td>\n",
       "        <td>043 04 0 014.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-04-29 00:00:00</td>\n",
       "        <td>190000</td>\n",
       "        <td>20160503-0043576</td>\n",
       "        <td>No</td>\n",
       "        <td>CRIPPS, WILLIE S. JR. &amp; GINEA M.</td>\n",
       "        <td>112  HILLER DR, OLD HICKORY, TN</td>\n",
       "        <td>0.29</td>\n",
       "        <td>GENERAL SERVICES DISTRICT</td>\n",
       "        <td>17000.0</td>\n",
       "        <td>143700.0</td>\n",
       "        <td>173600.0</td>\n",
       "        <td>1956.0</td>\n",
       "        <td>4.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22775</td>\n",
       "        <td>043 09 0 074.00</td>\n",
       "        <td>VACANT RESIDENTIAL LAND</td>\n",
       "        <td>None</td>\n",
       "        <td>2014-10-27 00:00:00</td>\n",
       "        <td>151000</td>\n",
       "        <td>20141028-0099094</td>\n",
       "        <td>Yes</td>\n",
       "        <td>BREWER HOLDINGS, LLC</td>\n",
       "        <td>213 B LOVELL ST, MADISON, TN</td>\n",
       "        <td>0.15</td>\n",
       "        <td>GENERAL SERVICES DISTRICT</td>\n",
       "        <td>18000.0</td>\n",
       "        <td>115600.0</td>\n",
       "        <td>133600.0</td>\n",
       "        <td>2015.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45349</td>\n",
       "        <td>043 13 0 308.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-03-31 00:00:00</td>\n",
       "        <td>155000</td>\n",
       "        <td>20160405-0032445</td>\n",
       "        <td>No</td>\n",
       "        <td>JACKSON, ADAM L. &amp; MOORE, MARY A.</td>\n",
       "        <td>224  HICKORY ST, MADISON, TN</td>\n",
       "        <td>0.16</td>\n",
       "        <td>GENERAL SERVICES DISTRICT</td>\n",
       "        <td>16000.0</td>\n",
       "        <td>65900.0</td>\n",
       "        <td>81900.0</td>\n",
       "        <td>1940.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>1.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50927</td>\n",
       "        <td>044 05 0 135.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-06-15 00:00:00</td>\n",
       "        <td>160000</td>\n",
       "        <td>20160617-0061987</td>\n",
       "        <td>No</td>\n",
       "        <td>SMITH, WARREN &amp; MARTHA</td>\n",
       "        <td>202  KEETON AVE, OLD HICKORY, TN</td>\n",
       "        <td>0.12</td>\n",
       "        <td>GENERAL SERVICES DISTRICT</td>\n",
       "        <td>19000.0</td>\n",
       "        <td>102700.0</td>\n",
       "        <td>121700.0</td>\n",
       "        <td>2015.0</td>\n",
       "        <td>4.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3299</td>\n",
       "        <td>052 01 0 296.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2013-05-31 00:00:00</td>\n",
       "        <td>79370</td>\n",
       "        <td>20130620-0063114</td>\n",
       "        <td>No</td>\n",
       "        <td>KIRBY, WILLIAM E. JR</td>\n",
       "        <td>726  IDLEWILD DR, MADISON, TN</td>\n",
       "        <td>0.22</td>\n",
       "        <td>GENERAL SERVICES DISTRICT</td>\n",
       "        <td>16000.0</td>\n",
       "        <td>60800.0</td>\n",
       "        <td>76800.0</td>\n",
       "        <td>1957.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>1.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43151</td>\n",
       "        <td>052 08 0A 320.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-01-15 00:00:00</td>\n",
       "        <td>150000</td>\n",
       "        <td>20160119-0004918</td>\n",
       "        <td>No</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49886</td>\n",
       "        <td>092 06 0 273.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-06-21 00:00:00</td>\n",
       "        <td>186000</td>\n",
       "        <td>20160705-0068281</td>\n",
       "        <td>No</td>\n",
       "        <td>SOLAVA, RACHEL LYNN</td>\n",
       "        <td>2721  HERMAN ST, NASHVILLE, TN</td>\n",
       "        <td>0.17</td>\n",
       "        <td>URBAN SERVICES DISTRICT</td>\n",
       "        <td>9000.0</td>\n",
       "        <td>63200.0</td>\n",
       "        <td>72200.0</td>\n",
       "        <td>1987.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>1.0</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27140</td>\n",
       "        <td>092 06 0 282.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2015-02-20 00:00:00</td>\n",
       "        <td>41500</td>\n",
       "        <td>20150224-0015900</td>\n",
       "        <td>No</td>\n",
       "        <td>PROSPERITAS PARTNERS LLC</td>\n",
       "        <td>815  31ST AVE N, NASHVILLE, TN</td>\n",
       "        <td>0.17</td>\n",
       "        <td>URBAN SERVICES DISTRICT</td>\n",
       "        <td>13000.0</td>\n",
       "        <td>49400.0</td>\n",
       "        <td>62400.0</td>\n",
       "        <td>1960.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>1.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11478</td>\n",
       "        <td>092 13 0 322.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2014-01-17 00:00:00</td>\n",
       "        <td>269500</td>\n",
       "        <td>20140122-0006168</td>\n",
       "        <td>No</td>\n",
       "        <td>WIGGINS, MATHEW L. &amp; GOOD, KENDRA A.</td>\n",
       "        <td>237  37TH AVE N, NASHVILLE, TN</td>\n",
       "        <td>0.34</td>\n",
       "        <td>URBAN SERVICES DISTRICT</td>\n",
       "        <td>91000.0</td>\n",
       "        <td>107100.0</td>\n",
       "        <td>198100.0</td>\n",
       "        <td>1945.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>1.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32385</td>\n",
       "        <td>092 13 0 339.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2015-06-05 00:00:00</td>\n",
       "        <td>450000</td>\n",
       "        <td>20150618-0058311</td>\n",
       "        <td>No</td>\n",
       "        <td>JUMPER, NICOLE</td>\n",
       "        <td>311  35TH AVE N, NASHVILLE, TN</td>\n",
       "        <td>0.16</td>\n",
       "        <td>URBAN SERVICES DISTRICT</td>\n",
       "        <td>65000.0</td>\n",
       "        <td>365300.0</td>\n",
       "        <td>430300.0</td>\n",
       "        <td>2015.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8126</td>\n",
       "        <td>093 08 0 054.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2013-09-20 00:00:00</td>\n",
       "        <td>25000</td>\n",
       "        <td>20130923-0099666</td>\n",
       "        <td>N</td>\n",
       "        <td>CRAWFORD, CHRISTOPHER C. &amp; BOBBIE LEANN</td>\n",
       "        <td>700  GLENVIEW DR, NASHVILLE, TN</td>\n",
       "        <td>0.21</td>\n",
       "        <td>URBAN SERVICES DISTRICT</td>\n",
       "        <td>25000.0</td>\n",
       "        <td>236800.0</td>\n",
       "        <td>261800.0</td>\n",
       "        <td>2015.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45774</td>\n",
       "        <td>107 13 0 107.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-03-28 00:00:00</td>\n",
       "        <td>169900</td>\n",
       "        <td>20160330-0030078</td>\n",
       "        <td>No</td>\n",
       "        <td>GROOMS, MICAH &amp; CASEY</td>\n",
       "        <td>1205  THOMPSON PL, NASHVILLE, TN</td>\n",
       "        <td>0.39</td>\n",
       "        <td>URBAN SERVICES DISTRICT</td>\n",
       "        <td>17000.0</td>\n",
       "        <td>75600.0</td>\n",
       "        <td>96200.0</td>\n",
       "        <td>1949.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>1.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14753</td>\n",
       "        <td>108 07 0A 026.00</td>\n",
       "        <td>RESIDENTIAL CONDO</td>\n",
       "        <td>None</td>\n",
       "        <td>2014-04-15 00:00:00</td>\n",
       "        <td>79900</td>\n",
       "        <td>20140416-0031777</td>\n",
       "        <td>No</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15886</td>\n",
       "        <td>109 04 0A 080.00</td>\n",
       "        <td>VACANT RES LAND</td>\n",
       "        <td>None</td>\n",
       "        <td>2014-05-13 00:00:00</td>\n",
       "        <td>255590</td>\n",
       "        <td>20140514-0041276</td>\n",
       "        <td>No</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24197</td>\n",
       "        <td>110 03 0A 061.00</td>\n",
       "        <td>SINGLE FAMILY</td>\n",
       "        <td>None</td>\n",
       "        <td>2014-11-19 00:00:00</td>\n",
       "        <td>269750</td>\n",
       "        <td>20141120-0106962</td>\n",
       "        <td>No</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51930</td>\n",
       "        <td>113 14 0A 002.00</td>\n",
       "        <td>VACANT RESIDENTIAL LAND</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-07-05 00:00:00</td>\n",
       "        <td>133000</td>\n",
       "        <td>20160707-0069375</td>\n",
       "        <td>Yes</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51703</td>\n",
       "        <td>114 15 0A 030.00</td>\n",
       "        <td>RESIDENTIAL CONDO</td>\n",
       "        <td>None</td>\n",
       "        <td>2016-07-08 00:00:00</td>\n",
       "        <td>343235</td>\n",
       "        <td>20160713-0071757</td>\n",
       "        <td>No</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(43076, '025 07 0 031.00', 'SINGLE FAMILY', None, '2016-01-15 00:00:00', 179900, '20160120-0005776', 'No', 'COSTNER, FRED & CAROLYN', '410  ROSEHILL CT, GOODLETTSVILLE, TN', 0.96, 'CITY OF GOODLETTSVILLE', 30000.0, 70000.0, 100000.0, 1964.0, 3.0, 1.0, 0.0),\n",
       " (39432, '026 01 0 069.00', 'VACANT RESIDENTIAL LAND', None, '2015-10-23 00:00:00', 153000, '20151028-0109602', 'No', 'SHACKLEFORD, MICHAEL C., JR.', '141  TWO MILE PIKE, GOODLETTSVILLE, TN', 0.17, 'CITY OF GOODLETTSVILLE', 21100.0, 121600.0, 142700.0, 2015.0, 3.0, 2.0, 0.0),\n",
       " (45290, '026 05 0 017.00', 'SINGLE FAMILY', None, '2016-03-29 00:00:00', 155000, '20160330-0029941', 'No', 'TRIPP, MARVIN S. & DEBORAH YOUNG', '208  EAST AVE, GOODLETTSVILLE, TN', 0.2, 'CITY OF GOODLETTSVILLE', 21100.0, 130200.0, 151300.0, 2008.0, 3.0, 2.0, 0.0),\n",
       " (53147, '026 06 0A 038.00', 'RESIDENTIAL CONDO', None, '2016-08-25 00:00:00', 144900, '20160831-0091567', 'No', None, None, None, None, None, None, None, None, None, None, None),\n",
       " (43080, '033 06 0 041.00', 'SINGLE FAMILY', None, '2016-01-04 00:00:00', 170000, '20160107-0001526', 'No', 'FRANK, ZACHARY & NIKI', '1129  CAMPBELL RD, GOODLETTSVILLE, TN', 0.24, 'GENERAL SERVICES DISTRICT', 35000.0, 110500.0, 145500.0, 2000.0, 3.0, 2.0, 0.0),\n",
       " (45295, '033 06 0A 002.00', 'SINGLE FAMILY', None, '2016-03-29 00:00:00', 210000, '20160331-0030709', 'No', None, None, None, None, None, None, None, None, None, None, None),\n",
       " (48731, '033 15 0 123.00', 'SINGLE FAMILY', None, '2016-05-05 00:00:00', 199900, '20160506-0045368', 'No', 'COLEMAN, AARON A. & CECIL, CORRIE J.', '438  W CAMPBELL RD, GOODLETTSVILLE, TN', 1.39, 'GENERAL SERVICES DISTRICT', 45000.0, 90300.0, 135300.0, 1954.0, 2.0, 1.0, 0.0),\n",
       " (36531, '034 03 0 059.00', 'SINGLE FAMILY', None, '2015-08-13 00:00:00', 245000, '20150819-0083759', 'No', 'DILICK, JOHN MARK & ANNETTE A.', '2117  PAULA DR, MADISON, TN', 1.01, 'GENERAL SERVICES DISTRICT', 32000.0, 170000.0, 228300.0, 1964.0, 4.0, 3.0, 0.0),\n",
       " (46919, '034 07 0B 015.00', 'VACANT RESIDENTIAL LAND', None, '2016-04-27 00:00:00', 40000, '20160304-0020905', 'Yes', None, None, None, None, None, None, None, None, None, None, None),\n",
       " (44264, '034 16 0A 004.00', 'VACANT RESIDENTIAL LAND', None, '2016-02-04 00:00:00', 130000, '20160205-0011327', 'Yes', None, None, None, None, None, None, None, None, None, None, None),\n",
       " (45298, '041 03 0A 100.00', 'SINGLE FAMILY', None, '2016-03-18 00:00:00', 170000, '20160322-0027048', 'No', None, None, None, None, None, None, None, None, None, None, None),\n",
       " (40678, '042 13 0 075.00', 'SINGLE FAMILY', None, '2015-11-30 00:00:00', 208000, '20151209-0123831', 'No', 'SADOWSKY, ERIC C. & LISA D.', '222  FOXBORO DR, MADISON, TN', 1.01, 'GENERAL SERVICES DISTRICT', 30000.0, 100000.0, 130000.0, 1967.0, 3.0, 1.0, 1.0),\n",
       " (47293, '043 04 0 014.00', 'SINGLE FAMILY', None, '2016-04-29 00:00:00', 190000, '20160503-0043576', 'No', 'CRIPPS, WILLIE S. JR. & GINEA M.', '112  HILLER DR, OLD HICKORY, TN', 0.29, 'GENERAL SERVICES DISTRICT', 17000.0, 143700.0, 173600.0, 1956.0, 4.0, 2.0, 1.0),\n",
       " (22775, '043 09 0 074.00', 'VACANT RESIDENTIAL LAND', None, '2014-10-27 00:00:00', 151000, '20141028-0099094', 'Yes', 'BREWER HOLDINGS, LLC', '213 B LOVELL ST, MADISON, TN', 0.15, 'GENERAL SERVICES DISTRICT', 18000.0, 115600.0, 133600.0, 2015.0, 3.0, 2.0, 0.0),\n",
       " (45349, '043 13 0 308.00', 'SINGLE FAMILY', None, '2016-03-31 00:00:00', 155000, '20160405-0032445', 'No', 'JACKSON, ADAM L. & MOORE, MARY A.', '224  HICKORY ST, MADISON, TN', 0.16, 'GENERAL SERVICES DISTRICT', 16000.0, 65900.0, 81900.0, 1940.0, 2.0, 1.0, 0.0),\n",
       " (50927, '044 05 0 135.00', 'SINGLE FAMILY', None, '2016-06-15 00:00:00', 160000, '20160617-0061987', 'No', 'SMITH, WARREN & MARTHA', '202  KEETON AVE, OLD HICKORY, TN', 0.12, 'GENERAL SERVICES DISTRICT', 19000.0, 102700.0, 121700.0, 2015.0, 4.0, 2.0, 0.0),\n",
       " (3299, '052 01 0 296.00', 'SINGLE FAMILY', None, '2013-05-31 00:00:00', 79370, '20130620-0063114', 'No', 'KIRBY, WILLIAM E. JR', '726  IDLEWILD DR, MADISON, TN', 0.22, 'GENERAL SERVICES DISTRICT', 16000.0, 60800.0, 76800.0, 1957.0, 2.0, 1.0, 0.0),\n",
       " (43151, '052 08 0A 320.00', 'SINGLE FAMILY', None, '2016-01-15 00:00:00', 150000, '20160119-0004918', 'No', None, None, None, None, None, None, None, None, None, None, None),\n",
       " (49886, '092 06 0 273.00', 'SINGLE FAMILY', None, '2016-06-21 00:00:00', 186000, '20160705-0068281', 'No', 'SOLAVA, RACHEL LYNN', '2721  HERMAN ST, NASHVILLE, TN', 0.17, 'URBAN SERVICES DISTRICT', 9000.0, 63200.0, 72200.0, 1987.0, 2.0, 1.0, 1.0),\n",
       " (27140, '092 06 0 282.00', 'SINGLE FAMILY', None, '2015-02-20 00:00:00', 41500, '20150224-0015900', 'No', 'PROSPERITAS PARTNERS LLC', '815  31ST AVE N, NASHVILLE, TN', 0.17, 'URBAN SERVICES DISTRICT', 13000.0, 49400.0, 62400.0, 1960.0, 2.0, 1.0, 0.0),\n",
       " (11478, '092 13 0 322.00', 'SINGLE FAMILY', None, '2014-01-17 00:00:00', 269500, '20140122-0006168', 'No', 'WIGGINS, MATHEW L. & GOOD, KENDRA A.', '237  37TH AVE N, NASHVILLE, TN', 0.34, 'URBAN SERVICES DISTRICT', 91000.0, 107100.0, 198100.0, 1945.0, 2.0, 1.0, 0.0),\n",
       " (32385, '092 13 0 339.00', 'SINGLE FAMILY', None, '2015-06-05 00:00:00', 450000, '20150618-0058311', 'No', 'JUMPER, NICOLE', '311  35TH AVE N, NASHVILLE, TN', 0.16, 'URBAN SERVICES DISTRICT', 65000.0, 365300.0, 430300.0, 2015.0, 3.0, 3.0, 1.0),\n",
       " (8126, '093 08 0 054.00', 'SINGLE FAMILY', None, '2013-09-20 00:00:00', 25000, '20130923-0099666', 'N', 'CRAWFORD, CHRISTOPHER C. & BOBBIE LEANN', '700  GLENVIEW DR, NASHVILLE, TN', 0.21, 'URBAN SERVICES DISTRICT', 25000.0, 236800.0, 261800.0, 2015.0, 3.0, 2.0, 1.0),\n",
       " (45774, '107 13 0 107.00', 'SINGLE FAMILY', None, '2016-03-28 00:00:00', 169900, '20160330-0030078', 'No', 'GROOMS, MICAH & CASEY', '1205  THOMPSON PL, NASHVILLE, TN', 0.39, 'URBAN SERVICES DISTRICT', 17000.0, 75600.0, 96200.0, 1949.0, 3.0, 1.0, 0.0),\n",
       " (14753, '108 07 0A 026.00', 'RESIDENTIAL CONDO', None, '2014-04-15 00:00:00', 79900, '20140416-0031777', 'No', None, None, None, None, None, None, None, None, None, None, None),\n",
       " (15886, '109 04 0A 080.00', 'VACANT RES LAND', None, '2014-05-13 00:00:00', 255590, '20140514-0041276', 'No', None, None, None, None, None, None, None, None, None, None, None),\n",
       " (24197, '110 03 0A 061.00', 'SINGLE FAMILY', None, '2014-11-19 00:00:00', 269750, '20141120-0106962', 'No', None, None, None, None, None, None, None, None, None, None, None),\n",
       " (51930, '113 14 0A 002.00', 'VACANT RESIDENTIAL LAND', None, '2016-07-05 00:00:00', 133000, '20160707-0069375', 'Yes', None, None, None, None, None, None, None, None, None, None, None),\n",
       " (51703, '114 15 0A 030.00', 'RESIDENTIAL CONDO', None, '2016-07-08 00:00:00', 343235, '20160713-0071757', 'No', None, None, None, None, None, None, None, None, None, None, None)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    *\n",
    "From\n",
    "    nashville_housing\n",
    "WHERE\n",
    "    PropertyAddress IS NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d81caea",
   "metadata": {},
   "source": [
    "#### 2.2 Use ***ParcelID*** to reference property addresses in self-join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "aef4ab8c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ParcelID</th>\n",
       "        <th>PropertyAddress</th>\n",
       "        <th>ParcelID_1</th>\n",
       "        <th>PropertyAddress_1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>025 07 0 031.00</td>\n",
       "        <td>None</td>\n",
       "        <td>025 07 0 031.00</td>\n",
       "        <td>410  ROSEHILL CT, GOODLETTSVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>026 01 0 069.00</td>\n",
       "        <td>None</td>\n",
       "        <td>026 01 0 069.00</td>\n",
       "        <td>141  TWO MILE PIKE, GOODLETTSVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>026 05 0 017.00</td>\n",
       "        <td>None</td>\n",
       "        <td>026 05 0 017.00</td>\n",
       "        <td>208  EAST AVE, GOODLETTSVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>026 06 0A 038.00</td>\n",
       "        <td>None</td>\n",
       "        <td>026 06 0A 038.00</td>\n",
       "        <td>109  CANTON CT, GOODLETTSVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>033 06 0 041.00</td>\n",
       "        <td>None</td>\n",
       "        <td>033 06 0 041.00</td>\n",
       "        <td>1129  CAMPBELL RD, GOODLETTSVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>033 06 0A 002.00</td>\n",
       "        <td>None</td>\n",
       "        <td>033 06 0A 002.00</td>\n",
       "        <td>1116  CAMPBELL RD, GOODLETTSVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>033 15 0 123.00</td>\n",
       "        <td>None</td>\n",
       "        <td>033 15 0 123.00</td>\n",
       "        <td>438  W CAMPBELL RD, GOODLETTSVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>034 03 0 059.00</td>\n",
       "        <td>None</td>\n",
       "        <td>034 03 0 059.00</td>\n",
       "        <td>2117  PAULA DR, MADISON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>034 03 0 059.00</td>\n",
       "        <td>None</td>\n",
       "        <td>034 03 0 059.00</td>\n",
       "        <td>2117  PAULA DR, MADISON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>034 07 0B 015.00</td>\n",
       "        <td>None</td>\n",
       "        <td>034 07 0B 015.00</td>\n",
       "        <td>2524  VAL MARIE DR, MADISON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>034 07 0B 015.00</td>\n",
       "        <td>None</td>\n",
       "        <td>034 07 0B 015.00</td>\n",
       "        <td>2524  VAL MARIE DR, MADISON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>034 07 0B 015.00</td>\n",
       "        <td>None</td>\n",
       "        <td>034 07 0B 015.00</td>\n",
       "        <td>2524 VAL MARIE  DR, MADISON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>034 16 0A 004.00</td>\n",
       "        <td>None</td>\n",
       "        <td>034 16 0A 004.00</td>\n",
       "        <td>213 WARREN  CT, OLD HICKORY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>041 03 0A 100.00</td>\n",
       "        <td>None</td>\n",
       "        <td>041 03 0A 100.00</td>\n",
       "        <td>1289  GOODMORNING DR, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>042 13 0 075.00</td>\n",
       "        <td>None</td>\n",
       "        <td>042 13 0 075.00</td>\n",
       "        <td>222  FOXBORO DR, MADISON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>043 04 0 014.00</td>\n",
       "        <td>None</td>\n",
       "        <td>043 04 0 014.00</td>\n",
       "        <td>112  HILLER DR, OLD HICKORY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>043 09 0 074.00</td>\n",
       "        <td>None</td>\n",
       "        <td>043 09 0 074.00</td>\n",
       "        <td>213 B  LOVELL ST, MADISON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>043 13 0 308.00</td>\n",
       "        <td>None</td>\n",
       "        <td>043 13 0 308.00</td>\n",
       "        <td>224  HICKORY ST, MADISON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>044 05 0 135.00</td>\n",
       "        <td>None</td>\n",
       "        <td>044 05 0 135.00</td>\n",
       "        <td>202  KEETON AVE, OLD HICKORY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>052 01 0 296.00</td>\n",
       "        <td>None</td>\n",
       "        <td>052 01 0 296.00</td>\n",
       "        <td>726  IDLEWILD DR, MADISON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>052 08 0A 320.00</td>\n",
       "        <td>None</td>\n",
       "        <td>052 08 0A 320.00</td>\n",
       "        <td>608  SANDY SPRING TRL, MADISON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>092 06 0 273.00</td>\n",
       "        <td>None</td>\n",
       "        <td>092 06 0 273.00</td>\n",
       "        <td>2721  HERMAN ST, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>092 06 0 282.00</td>\n",
       "        <td>None</td>\n",
       "        <td>092 06 0 282.00</td>\n",
       "        <td>815  31ST AVE N, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>092 13 0 322.00</td>\n",
       "        <td>None</td>\n",
       "        <td>092 13 0 322.00</td>\n",
       "        <td>237  37TH AVE N, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>092 13 0 322.00</td>\n",
       "        <td>None</td>\n",
       "        <td>092 13 0 322.00</td>\n",
       "        <td>237  37TH AVE N, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>092 13 0 339.00</td>\n",
       "        <td>None</td>\n",
       "        <td>092 13 0 339.00</td>\n",
       "        <td>311  35TH AVE N, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>093 08 0 054.00</td>\n",
       "        <td>None</td>\n",
       "        <td>093 08 0 054.00</td>\n",
       "        <td>700  GLENVIEW DR, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>093 08 0 054.00</td>\n",
       "        <td>None</td>\n",
       "        <td>093 08 0 054.00</td>\n",
       "        <td>700  GLENVIEW DR, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>107 13 0 107.00</td>\n",
       "        <td>None</td>\n",
       "        <td>107 13 0 107.00</td>\n",
       "        <td>1205  THOMPSON PL, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>108 07 0A 026.00</td>\n",
       "        <td>None</td>\n",
       "        <td>108 07 0A 026.00</td>\n",
       "        <td>908  PATIO DR, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>108 07 0A 026.00</td>\n",
       "        <td>None</td>\n",
       "        <td>108 07 0A 026.00</td>\n",
       "        <td>908  PATIO DR, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>109 04 0A 080.00</td>\n",
       "        <td>None</td>\n",
       "        <td>109 04 0A 080.00</td>\n",
       "        <td>2537  JANALYN TRCE, HERMITAGE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>110 03 0A 061.00</td>\n",
       "        <td>None</td>\n",
       "        <td>110 03 0A 061.00</td>\n",
       "        <td>2704  ALVIN SPERRY PASS, MOUNT JULIET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>113 14 0A 002.00</td>\n",
       "        <td>None</td>\n",
       "        <td>113 14 0A 002.00</td>\n",
       "        <td>7601  CHIPMUNK LN, NASHVILLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>114 15 0A 030.00</td>\n",
       "        <td>None</td>\n",
       "        <td>114 15 0A 030.00</td>\n",
       "        <td>109  CEDAR PLACE BND, NASHVILLE</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('025 07 0 031.00', None, '025 07 0 031.00', '410  ROSEHILL CT, GOODLETTSVILLE'),\n",
       " ('026 01 0 069.00', None, '026 01 0 069.00', '141  TWO MILE PIKE, GOODLETTSVILLE'),\n",
       " ('026 05 0 017.00', None, '026 05 0 017.00', '208  EAST AVE, GOODLETTSVILLE'),\n",
       " ('026 06 0A 038.00', None, '026 06 0A 038.00', '109  CANTON CT, GOODLETTSVILLE'),\n",
       " ('033 06 0 041.00', None, '033 06 0 041.00', '1129  CAMPBELL RD, GOODLETTSVILLE'),\n",
       " ('033 06 0A 002.00', None, '033 06 0A 002.00', '1116  CAMPBELL RD, GOODLETTSVILLE'),\n",
       " ('033 15 0 123.00', None, '033 15 0 123.00', '438  W CAMPBELL RD, GOODLETTSVILLE'),\n",
       " ('034 03 0 059.00', None, '034 03 0 059.00', '2117  PAULA DR, MADISON'),\n",
       " ('034 03 0 059.00', None, '034 03 0 059.00', '2117  PAULA DR, MADISON'),\n",
       " ('034 07 0B 015.00', None, '034 07 0B 015.00', '2524  VAL MARIE DR, MADISON'),\n",
       " ('034 07 0B 015.00', None, '034 07 0B 015.00', '2524  VAL MARIE DR, MADISON'),\n",
       " ('034 07 0B 015.00', None, '034 07 0B 015.00', '2524 VAL MARIE  DR, MADISON'),\n",
       " ('034 16 0A 004.00', None, '034 16 0A 004.00', '213 WARREN  CT, OLD HICKORY'),\n",
       " ('041 03 0A 100.00', None, '041 03 0A 100.00', '1289  GOODMORNING DR, NASHVILLE'),\n",
       " ('042 13 0 075.00', None, '042 13 0 075.00', '222  FOXBORO DR, MADISON'),\n",
       " ('043 04 0 014.00', None, '043 04 0 014.00', '112  HILLER DR, OLD HICKORY'),\n",
       " ('043 09 0 074.00', None, '043 09 0 074.00', '213 B  LOVELL ST, MADISON'),\n",
       " ('043 13 0 308.00', None, '043 13 0 308.00', '224  HICKORY ST, MADISON'),\n",
       " ('044 05 0 135.00', None, '044 05 0 135.00', '202  KEETON AVE, OLD HICKORY'),\n",
       " ('052 01 0 296.00', None, '052 01 0 296.00', '726  IDLEWILD DR, MADISON'),\n",
       " ('052 08 0A 320.00', None, '052 08 0A 320.00', '608  SANDY SPRING TRL, MADISON'),\n",
       " ('092 06 0 273.00', None, '092 06 0 273.00', '2721  HERMAN ST, NASHVILLE'),\n",
       " ('092 06 0 282.00', None, '092 06 0 282.00', '815  31ST AVE N, NASHVILLE'),\n",
       " ('092 13 0 322.00', None, '092 13 0 322.00', '237  37TH AVE N, NASHVILLE'),\n",
       " ('092 13 0 322.00', None, '092 13 0 322.00', '237  37TH AVE N, NASHVILLE'),\n",
       " ('092 13 0 339.00', None, '092 13 0 339.00', '311  35TH AVE N, NASHVILLE'),\n",
       " ('093 08 0 054.00', None, '093 08 0 054.00', '700  GLENVIEW DR, NASHVILLE'),\n",
       " ('093 08 0 054.00', None, '093 08 0 054.00', '700  GLENVIEW DR, NASHVILLE'),\n",
       " ('107 13 0 107.00', None, '107 13 0 107.00', '1205  THOMPSON PL, NASHVILLE'),\n",
       " ('108 07 0A 026.00', None, '108 07 0A 026.00', '908  PATIO DR, NASHVILLE'),\n",
       " ('108 07 0A 026.00', None, '108 07 0A 026.00', '908  PATIO DR, NASHVILLE'),\n",
       " ('109 04 0A 080.00', None, '109 04 0A 080.00', '2537  JANALYN TRCE, HERMITAGE'),\n",
       " ('110 03 0A 061.00', None, '110 03 0A 061.00', '2704  ALVIN SPERRY PASS, MOUNT JULIET'),\n",
       " ('113 14 0A 002.00', None, '113 14 0A 002.00', '7601  CHIPMUNK LN, NASHVILLE'),\n",
       " ('114 15 0A 030.00', None, '114 15 0A 030.00', '109  CEDAR PLACE BND, NASHVILLE')]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    t1.ParcelID,\n",
    "    t1.PropertyAddress,\n",
    "    t2.ParcelID,\n",
    "    t2.PropertyAddress\n",
    "FROM\n",
    "    nashville_housing AS t1\n",
    "JOIN\n",
    "    nashville_housing AS t2\n",
    "ON\n",
    "    t1.ParcelID = t2.ParcelID AND t1.[UniqueID ] <> t2.[UniqueID ]\n",
    "WHERE\n",
    "    t1.PropertyAddress IS NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9392307c",
   "metadata": {},
   "source": [
    "#### 2.3 Populate null values in \"PropertyAddress\" column referenced to according ***ParcelID***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "07554ffa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "29 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "UPDATE\n",
    "    nashville_housing\n",
    "SET\n",
    "    PropertyAddress = (SELECT \n",
    "                           nh.PropertyAddress\n",
    "                       FROM \n",
    "                           nashville_housing AS nh\n",
    "                       WHERE \n",
    "                           nashville_housing.ParcelID = nh.ParcelID)\n",
    "WHERE EXISTS (SELECT \n",
    "                  nh.PropertyAddress\n",
    "              FROM \n",
    "                  nashville_housing AS nh\n",
    "              WHERE \n",
    "                  nashville_housing.ParcelID = nh.ParcelID\n",
    "                  AND nashville_housing.[UniqueID ] <> nh.[UniqueID ]\n",
    "                  AND nashville_housing.PropertyAddress IS NULL)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "744e6665",
   "metadata": {},
   "source": [
    "### 3. Standardize data formats"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21eddb14",
   "metadata": {},
   "source": [
    "#### 3.1 convert \"SaleDate\" column from datetime to date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4ee74b9d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "56477 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "UPDATE\n",
    "    nashville_housing\n",
    "SET\n",
    "    SaleDate = DATE(SaleDate);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b87051f",
   "metadata": {},
   "source": [
    "#### 3.2 Split \"PropertyAddress\" column into individual columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd0180da",
   "metadata": {},
   "source": [
    "- First, parse property address out of \"PropertyAddress\" column and set values into a new column \"SplitPropertyAddress\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "51b090d3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n",
      "56477 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "ALTER TABLE\n",
    "    nashville_housing\n",
    "ADD COLUMN\n",
    "    SplitPropertyAddress TEXT(255);\n",
    "\n",
    "UPDATE\n",
    "    nashville_housing\n",
    "SET\n",
    "    SplitPropertyAddress = SUBSTR(PropertyAddress, 1, INSTR(PropertyAddress, \", \")-1);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edcdddd0",
   "metadata": {},
   "source": [
    "- Then, parse city information out of \"PropertyAddress\" column and set values into a new column \"SplitPropertyCity\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "cf285d3b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n",
      "56477 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "ALTER TABLE\n",
    "    nashville_housing\n",
    "ADD COLUMN\n",
    "    SplitPropertyCity TEXT(255);\n",
    "\n",
    "UPDATE\n",
    "    nashville_housing\n",
    "SET\n",
    "    SplitPropertyCity = SUBSTR(PropertyAddress, INSTR(PropertyAddress, \", \")+2);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8d0885f",
   "metadata": {},
   "source": [
    "#### 3.3 Split \"OwnerAddress\" column into individual columns for address, city, and state"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1610faa4",
   "metadata": {},
   "source": [
    "- First, parse only property address out of \"OwnerAddress\" column and set values into a new column \"SplitOwnerAddress\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ecb80307",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n",
      "56477 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "ALTER TABLE\n",
    "    nashville_housing\n",
    "ADD COLUMN\n",
    "    SplitOwnerAddress TEXT(255);\n",
    "\n",
    "UPDATE\n",
    "    nashville_housing\n",
    "SET\n",
    "    SplitOwnerAddress = SUBSTR(OwnerAddress, 1, INSTR(OwnerAddress, \", \")-1);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2dc8f936",
   "metadata": {},
   "source": [
    "- Second, reset \"OwnerAddress\" column to only owners' city and state information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b7cebad6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "56477 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "UPDATE\n",
    "    nashville_housing\n",
    "SET\n",
    "    OwnerAddress = SUBSTR(OwnerAddress, INSTR(OwnerAddress, \", \")+2);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43fe35e7",
   "metadata": {},
   "source": [
    "- Third, parse owners' city information out of \"OwnerAddress\" and set values into a new column \"SplitOwnerCity\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2b1be516",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n",
      "56477 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "ALTER TABLE\n",
    "    nashville_housing\n",
    "ADD COLUMN\n",
    "    SplitOwnerCity TEXT(255);\n",
    "\n",
    "UPDATE\n",
    "    nashville_housing\n",
    "SET\n",
    "    SplitOwnerCity = SUBSTR(OwnerAddress, 1, INSTR(OwnerAddress, \", \")-1);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b6f3aaf",
   "metadata": {},
   "source": [
    "- Finally, parse owners' state information out of \"OwnerAddress\" and set values into a new column \"SplitOwnerState\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "bdd6cec7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n",
      "56477 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "ALTER TABLE\n",
    "    nashville_housing\n",
    "ADD COLUMN\n",
    "    SplitOwnerState TEXT(255);\n",
    "\n",
    "UPDATE\n",
    "    nashville_housing\n",
    "SET\n",
    "    SplitOwnerState = SUBSTR(OwnerAddress, INSTR(OwnerAddress, \", \")+2);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "768d18d1",
   "metadata": {},
   "source": [
    "#### 3.4 Standardize \"Sold as Vacant\" column to solely \"Yes\" or \"No\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7d390fd0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>SoldAsVacant</th>\n",
       "        <th>COUNT(SoldAsVacant)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>N</td>\n",
       "        <td>399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>No</td>\n",
       "        <td>51403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Y</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Yes</td>\n",
       "        <td>4623</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('N', 399), ('No', 51403), ('Y', 52), ('Yes', 4623)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    DISTINCT SoldAsVacant,\n",
    "    COUNT(SoldAsVacant)\n",
    "FROM\n",
    "    nashville_housing\n",
    "GROUP BY\n",
    "    SoldAsVacant;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "06feea52",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "56477 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "UPDATE\n",
    "    nashville_housing\n",
    "SET\n",
    "    SoldAsVacant = (CASE WHEN \n",
    "                        SoldAsVacant = \"N\" THEN \"No\"\n",
    "                    WHEN \n",
    "                        SoldAsVacant = \"Y\" THEN \"Yes\"\n",
    "                    ELSE \n",
    "                        SoldAsVacant\n",
    "                    END);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "773e299b",
   "metadata": {},
   "source": [
    "#### 3.5 Convert \"YearBuilt\" column from float to integer"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2ae54f7",
   "metadata": {},
   "source": [
    "- First, rename \"YearBuilt\" column to \"old_YearBuilt\" column for next step"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "7539b1dd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "ALTER TABLE\n",
    "    nashville_housing\n",
    "RENAME COLUMN\n",
    "    YearBuilt TO old_YearBuilt;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47dc8ffd",
   "metadata": {},
   "source": [
    "- Then, add a new column \"YearBuilt\" and set values which cast \"old_YearBuilt\" column from float to integer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "28624595",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n",
      "56477 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "ALTER TABLE\n",
    "    nashville_housing\n",
    "ADD COLUMN\n",
    "    YearBuilt INTEGER;\n",
    "\n",
    "UPDATE\n",
    "    nashville_housing\n",
    "SET\n",
    "    YearBuilt = CAST(old_YearBuilt AS INTEGER);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2b0f209",
   "metadata": {},
   "source": [
    "### 4. Delete unused columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "b5093c31",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sql</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CREATE TABLE &quot;nashville_housing&quot; (<br>&quot;UniqueID &quot; INTEGER,<br>  &quot;ParcelID&quot; TEXT,<br>  &quot;LandUse&quot; TEXT,<br>  &quot;PropertyAddress&quot; TEXT,<br>  &quot;SaleDate&quot; TIMESTAMP,<br>  &quot;SalePrice&quot; INTEGER,<br>  &quot;LegalReference&quot; TEXT,<br>  &quot;SoldAsVacant&quot; TEXT,<br>  &quot;OwnerName&quot; TEXT,<br>  &quot;OwnerAddress&quot; TEXT,<br>  &quot;Acreage&quot; REAL,<br>  &quot;TaxDistrict&quot; TEXT,<br>  &quot;LandValue&quot; REAL,<br>  &quot;BuildingValue&quot; REAL,<br>  &quot;TotalValue&quot; REAL,<br>  &quot;old_YearBuilt&quot; REAL,<br>  &quot;Bedrooms&quot; REAL,<br>  &quot;FullBath&quot; REAL,<br>  &quot;HalfBath&quot; REAL<br>, SplitPropertyAddress TEXT(255), SplitPropertyCity TEXT(255), SplitOwnerAddress TEXT(255), SplitOwnerCity TEXT(255), SplitOwnerState TEXT(255), YearBuilt INTEGER)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CREATE INDEX &quot;ix_nashville_housing_UniqueID &quot;ON &quot;nashville_housing&quot; (&quot;UniqueID &quot;)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('CREATE TABLE \"nashville_housing\" (\\n\"UniqueID \" INTEGER,\\n  \"ParcelID\" TEXT,\\n  \"LandUse\" TEXT,\\n  \"PropertyAddress\" TEXT,\\n  \"SaleDate\" TIMESTAMP,\\n ... (327 characters truncated) ... yAddress TEXT(255), SplitPropertyCity TEXT(255), SplitOwnerAddress TEXT(255), SplitOwnerCity TEXT(255), SplitOwnerState TEXT(255), YearBuilt INTEGER)',),\n",
       " ('CREATE INDEX \"ix_nashville_housing_UniqueID \"ON \"nashville_housing\" (\"UniqueID \")',)]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT sql\n",
    "FROM sqlite_master\n",
    "WHERE tbl_name = \"nashville_housing\";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "79fa58aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_projects.db\n",
      "Done.\n",
      "Done.\n",
      "56477 rows affected.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "PRAGMA foreign_keys=OFF;\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS nashville_housing_cleaned(\n",
    "    ParcelID TEXT,\n",
    "    LandUse TEXT,\n",
    "    SplitPropertyAddress TEXT,\n",
    "    SplitPropertyCity TEXT,\n",
    "    SaleDate TIMESTAMP,\n",
    "    SalePrice INTEGER,\n",
    "    LegalReference TEXT,\n",
    "    SoldAsVacant TEXT,\n",
    "    OwnerName TEXT,\n",
    "    SplitOwnerAddress TEXT,\n",
    "    SplitOwnerCity TEXT,\n",
    "    SplitOwnerState TEXT,\n",
    "    Acreage REAL,\n",
    "    LandValue REAL,\n",
    "    BuildingValue REAL,\n",
    "    TotalValue REAL,\n",
    "    YearBuilt INTEGER,\n",
    "    Bedrooms REAL,\n",
    "    FullBath REAL,\n",
    "    HalfBath REAL\n",
    ");\n",
    "\n",
    "INSERT INTO nashville_housing_cleaned\n",
    "\n",
    "SELECT\n",
    "    ParcelID,\n",
    "    LandUse,\n",
    "    SplitPropertyAddress,\n",
    "    SplitPropertyCity,\n",
    "    SaleDate,\n",
    "    SalePrice,\n",
    "    LegalReference,\n",
    "    SoldAsVacant,\n",
    "    OwnerName,\n",
    "    SplitOwnerAddress,\n",
    "    SplitOwnerCity,\n",
    "    SplitOwnerState,\n",
    "    Acreage,\n",
    "    LandValue,\n",
    "    BuildingValue,\n",
    "    TotalValue,\n",
    "    YearBuilt,\n",
    "    Bedrooms,\n",
    "    FullBath,\n",
    "    HalfBath\n",
    "FROM\n",
    "    nashville_housing;\n",
    "\n",
    "DROP TABLE \n",
    "    nashville_housing;\n",
    "\n",
    "ALTER TABLE \n",
    "    nashville_housing_cleaned\n",
    "RENAME TO\n",
    "    nashville_housing;\n",
    "\n",
    "PRAGMA foreign_keys=ON;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e7088ddc",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}