3.CleanData.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "experienced-advisory",
   "metadata": {},
   "source": [
    "Import necessary libraries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "referenced-badge",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\robin\\anaconda3\\lib\\site-packages\\fuzzywuzzy\\fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning\n",
      "  warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import string\n",
    "from difflib import get_close_matches, SequenceMatcher\n",
    "#!pip install python-Levenshtein\n",
    "from fuzzywuzzy import fuzz\n",
    "\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "accessible-reducing",
   "metadata": {},
   "source": [
    "Import data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "institutional-small",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\robin\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3146: DtypeWarning: Columns (9) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(252718, 91)\n"
     ]
    },
    {
     "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>RE</th>\n",
       "      <th>section</th>\n",
       "      <th>township</th>\n",
       "      <th>range</th>\n",
       "      <th>tile</th>\n",
       "      <th>mailing_address_1</th>\n",
       "      <th>mailing_address_2</th>\n",
       "      <th>mail_city</th>\n",
       "      <th>mail_state</th>\n",
       "      <th>mail_zipcode</th>\n",
       "      <th>...</th>\n",
       "      <th>sub_Unfin Open Porch</th>\n",
       "      <th>sub_Unfinished Garage</th>\n",
       "      <th>sub_Unfinished Storage</th>\n",
       "      <th>street_num</th>\n",
       "      <th>direction</th>\n",
       "      <th>street_name</th>\n",
       "      <th>street_type</th>\n",
       "      <th>unit</th>\n",
       "      <th>city</th>\n",
       "      <th>zipcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000060030R</td>\n",
       "      <td>1</td>\n",
       "      <td>2S</td>\n",
       "      <td>23E</td>\n",
       "      <td>3401.0</td>\n",
       "      <td>2503 SUMMERFIELD LN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>32234</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2503.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SUMMERFIELD</td>\n",
       "      <td>LN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>32234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000070010R</td>\n",
       "      <td>1</td>\n",
       "      <td>2S</td>\n",
       "      <td>23E</td>\n",
       "      <td>3401.0</td>\n",
       "      <td>C/O TAX DEPARTMENT</td>\n",
       "      <td>9540 SAN JOSE BLVD</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>32257</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2610.0</td>\n",
       "      <td>N</td>\n",
       "      <td>US 301</td>\n",
       "      <td>HWY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>32234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000090100R</td>\n",
       "      <td>12</td>\n",
       "      <td>2S</td>\n",
       "      <td>23E</td>\n",
       "      <td>3412.0</td>\n",
       "      <td>2495 U S 301 HWY N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BALDWIN</td>\n",
       "      <td>FL</td>\n",
       "      <td>32234</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2495.0</td>\n",
       "      <td>N</td>\n",
       "      <td>US 301</td>\n",
       "      <td>HWY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>32234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000110000R</td>\n",
       "      <td>12</td>\n",
       "      <td>2S</td>\n",
       "      <td>23E</td>\n",
       "      <td>3412.0</td>\n",
       "      <td>2425 US HWY 301 N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BALDWIN</td>\n",
       "      <td>FL</td>\n",
       "      <td>32234</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2435.0</td>\n",
       "      <td>N</td>\n",
       "      <td>US 301</td>\n",
       "      <td>HWY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>32234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000120010R</td>\n",
       "      <td>12</td>\n",
       "      <td>2S</td>\n",
       "      <td>23E</td>\n",
       "      <td>3412.0</td>\n",
       "      <td>2204 US HIGHWAY 301 N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>32234</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2204.0</td>\n",
       "      <td>N</td>\n",
       "      <td>US 301</td>\n",
       "      <td>HWY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>32234</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 91 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            RE  section township range    tile      mailing_address_1  \\\n",
       "0  0000060030R        1       2S   23E  3401.0    2503 SUMMERFIELD LN   \n",
       "1  0000070010R        1       2S   23E  3401.0     C/O TAX DEPARTMENT   \n",
       "2  0000090100R       12       2S   23E  3412.0     2495 U S 301 HWY N   \n",
       "3  0000110000R       12       2S   23E  3412.0      2425 US HWY 301 N   \n",
       "4  0000120010R       12       2S   23E  3412.0  2204 US HIGHWAY 301 N   \n",
       "\n",
       "    mailing_address_2     mail_city mail_state mail_zipcode  ...  \\\n",
       "0                 NaN  JACKSONVILLE         FL        32234  ...   \n",
       "1  9540 SAN JOSE BLVD  JACKSONVILLE         FL        32257  ...   \n",
       "2                 NaN       BALDWIN         FL        32234  ...   \n",
       "3                 NaN       BALDWIN         FL        32234  ...   \n",
       "4                 NaN  JACKSONVILLE         FL        32234  ...   \n",
       "\n",
       "  sub_Unfin Open Porch  sub_Unfinished Garage  sub_Unfinished Storage  \\\n",
       "0                  0.0                    0.0                     0.0   \n",
       "1                  0.0                    0.0                     0.0   \n",
       "2                  1.0                    0.0                     0.0   \n",
       "3                  0.0                    0.0                     0.0   \n",
       "4                  0.0                    0.0                     0.0   \n",
       "\n",
       "   street_num  direction  street_name street_type  unit          city zipcode  \n",
       "0      2503.0        NaN  SUMMERFIELD          LN   NaN  JACKSONVILLE   32234  \n",
       "1      2610.0          N       US 301         HWY   NaN  JACKSONVILLE   32234  \n",
       "2      2495.0          N       US 301         HWY   NaN  JACKSONVILLE   32234  \n",
       "3      2435.0          N       US 301         HWY   NaN  JACKSONVILLE   32234  \n",
       "4      2204.0          N       US 301         HWY   NaN  JACKSONVILLE   32234  \n",
       "\n",
       "[5 rows x 91 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "houses = pd.read_csv('D:/Data/JacksonvilleHouses.csv')\n",
    "print(houses.shape)\n",
    "houses.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fleet-thumbnail",
   "metadata": {},
   "source": [
    "Remove spaces in column names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "electric-connecticut",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_cols = houses.columns.to_list()\n",
    "new_cols = [x.replace(' ','_') for x in new_cols]\n",
    "houses.rename(columns=dict(zip(houses.columns.to_list(),new_cols)), inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "offensive-aruba",
   "metadata": {},
   "source": [
    "Convert parcel IDs to same format as shapefiles for future visualizations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "thick-scoop",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['RE'] = houses['RE'].str.replace('R','')\n",
    "houses['RE'] = [x[:6] + ' ' + x[-4:] for x in houses['RE']]\n",
    "houses['RE_b'] = houses['RE'] + ' ' + houses['building'].map(str)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "convinced-female",
   "metadata": {},
   "source": [
    "Combine section, township and range columns into a single column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "dressed-newspaper",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['section'] = houses['section'].astype('str')\n",
    "houses['sec_township_range'] = houses['section'] + ' ' + houses['township'].map(str) + ' ' + \\\n",
    "        houses['range'].map(str)\n",
    "houses.drop(columns=['section', 'township', 'range'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "young-transportation",
   "metadata": {},
   "source": [
    "Remove unneeded columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "gentle-amino",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses.drop(columns=['tile','style'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "inner-giant",
   "metadata": {},
   "source": [
    "Standardize addresses of most common home owners and add investment group column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "accepted-stupid",
   "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>mailing_address_1</th>\n",
       "      <th>fixed</th>\n",
       "      <th>mail_2</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>inv_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>P O BOX 4090</td>\n",
       "      <td>PO BOX 4090</td>\n",
       "      <td></td>\n",
       "      <td>SCOTTSDALE</td>\n",
       "      <td>AZ</td>\n",
       "      <td>Progress Residential</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>P O BOX 4090</td>\n",
       "      <td>PO BOX 4090</td>\n",
       "      <td></td>\n",
       "      <td>SCOTTSDALE</td>\n",
       "      <td>AZ</td>\n",
       "      <td>Progress Residential</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PO BOX 4090</td>\n",
       "      <td>PO BOX 4090</td>\n",
       "      <td></td>\n",
       "      <td>SCOTTSDALE</td>\n",
       "      <td>AZ</td>\n",
       "      <td>Progress Residential</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>P O Box 4090</td>\n",
       "      <td>PO BOX 4090</td>\n",
       "      <td></td>\n",
       "      <td>SCOTTSDALE</td>\n",
       "      <td>AZ</td>\n",
       "      <td>Progress Residential</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>P O OX 4090</td>\n",
       "      <td>PO BOX 4090</td>\n",
       "      <td></td>\n",
       "      <td>SCOTTSDALE</td>\n",
       "      <td>AZ</td>\n",
       "      <td>Progress Residential</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>C/O WM A WATSON JR TRUSTEE</td>\n",
       "      <td>7821 DEERCREEK CLUB RD 200</td>\n",
       "      <td></td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>Watson Realty Corp.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>7821 DEERCREEK CLUB RD</td>\n",
       "      <td>7821 DEERCREEK CLUB RD 200</td>\n",
       "      <td></td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>Watson Realty Corp.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>7821 DEERCREEK CLUB RD #200</td>\n",
       "      <td>7821 DEERCREEK CLUB RD 200</td>\n",
       "      <td></td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>Watson Realty Corp.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>398</th>\n",
       "      <td>7821 DEERCREEK CLUB RD</td>\n",
       "      <td>7821 DEERCREEK CLUB RD 200</td>\n",
       "      <td></td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>Watson Realty Corp.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>221 N HOGAN ST UNIT 405</td>\n",
       "      <td>2 BATES TRL</td>\n",
       "      <td></td>\n",
       "      <td>EAST GREENWHICH</td>\n",
       "      <td>RI</td>\n",
       "      <td>China Equity Inc.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>400 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               mailing_address_1                       fixed mail_2  \\\n",
       "0                   P O BOX 4090                 PO BOX 4090          \n",
       "1                  P O BOX 4090                  PO BOX 4090          \n",
       "2                    PO BOX 4090                 PO BOX 4090          \n",
       "3                   P O Box 4090                 PO BOX 4090          \n",
       "4                    P O OX 4090                 PO BOX 4090          \n",
       "..                           ...                         ...    ...   \n",
       "395   C/O WM A WATSON JR TRUSTEE  7821 DEERCREEK CLUB RD 200          \n",
       "396       7821 DEERCREEK CLUB RD  7821 DEERCREEK CLUB RD 200          \n",
       "397  7821 DEERCREEK CLUB RD #200  7821 DEERCREEK CLUB RD 200          \n",
       "398      7821 DEERCREEK CLUB RD   7821 DEERCREEK CLUB RD 200          \n",
       "399      221 N HOGAN ST UNIT 405                 2 BATES TRL          \n",
       "\n",
       "                city state             inv_group  \n",
       "0         SCOTTSDALE    AZ  Progress Residential  \n",
       "1         SCOTTSDALE    AZ  Progress Residential  \n",
       "2         SCOTTSDALE    AZ  Progress Residential  \n",
       "3         SCOTTSDALE    AZ  Progress Residential  \n",
       "4         SCOTTSDALE    AZ  Progress Residential  \n",
       "..               ...   ...                   ...  \n",
       "395     JACKSONVILLE    FL   Watson Realty Corp.  \n",
       "396     JACKSONVILLE    FL   Watson Realty Corp.  \n",
       "397     JACKSONVILLE    FL   Watson Realty Corp.  \n",
       "398     JACKSONVILLE    FL   Watson Realty Corp.  \n",
       "399  EAST GREENWHICH    RI     China Equity Inc.  \n",
       "\n",
       "[400 rows x 6 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine = create_engine('sqlite:///D:\\\\Data\\\\DuvalProperties.sqlite')\n",
    "con = engine.connect()\n",
    "invs1 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), 'PO BOX 4090' AS fixed, '' AS mail_2, 'SCOTTSDALE' AS city, \n",
    "    'AZ' AS state, 'Progress Residential' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE mailing_address_1 LIKE 'P%O%4090%' AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs1 = pd.DataFrame(invs1.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs2 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '30601 AGOURA RD 200' AS fixed, '' AS mail_2, \n",
    "    'AGOURA HILLS' AS city, 'CA' AS state, 'American Homes 4 Rent' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE TRIM(mailing_address_1) LIKE '30%AG%R%D%' AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs2 = pd.DataFrame(invs2.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs3 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '1717 MAIN ST 2000' AS fixed, '' AS mail_2, 'DALLAS' AS city, \n",
    "    'TX' AS state, 'Invitation Homes' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE (TRIM(mailing_address_1) LIKE '1717%MAIN%2000%' OR TRIM(mailing_address_2) \n",
    "    LIKE '1717%MAIN%2000%') AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs3 = pd.DataFrame(invs3.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs4 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '3721 DUPONT STATION CT S' AS fixed, '' AS mail_2, \n",
    "    'JACKSONVILLE' AS city, 'FL' AS state, 'Heritage Florida Property Holdings' AS inv_group\n",
    "    FROM Parcel\n",
    "    LEFT JOIN Owner USING (RE)\n",
    "    WHERE TRIM(mailing_address_1) LIKE '3721%DUPONT%ST%CT%' AND mailing_address_1 NOT LIKE '%1' \n",
    "        AND owner NOT LIKE 'RIBER%' AND owner NOT LIKE 'EIKNER%' AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs4 = pd.DataFrame(invs4.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs5 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '113361 ATLANTIC BLVD' AS fixed, '' AS mail_2, \n",
    "    'JACKSONVILLE' AS city, 'FL' AS state, 'Heritage Florida Property Holdings' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE (TRIM(mailing_address_1) LIKE '13%61%AT%C%B%L%V%' OR TRIM(mailing_address_2) \n",
    "    LIKE '13%61%AT%C%B%L%V%') AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs5 = pd.DataFrame(invs5.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs6 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '5001 PLZ ON THE LAKE 200' AS fixed, '' AS mail_2, \n",
    "    'AUSTIN' AS city, 'TX' AS state, 'Amherst Residential' AS inv_group\n",
    "    FROM Parcel\n",
    "    LEFT JOIN Owner USING (RE)\n",
    "    WHERE TRIM(mailing_address_1) LIKE '5001%PL%Z%ON%%LAKE%' AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs6 = pd.DataFrame(invs6.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs7 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '7563 PHILIPS HWY 208' AS fixed, '' AS mail_2, \n",
    "    'JACKSONVILLE' AS city, 'FL' AS state, 'JWB Real Estate Capital' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE TRIM(mailing_address_1) LIKE '7563%PHILIPS%' AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs7 = pd.DataFrame(invs7.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs8 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '1508 BROOKHOLLOW DR' AS fixed, '' AS mail_2, 'SANTA ANA' AS city,\n",
    "    'CA' AS state, 'Tricon American Homes' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE (TRIM(mailing_address_1) LIKE '%TRICON%' OR TRIM(mailing_address_1) LIKE '1508%BROOK%HOLLOW')\n",
    "        AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs8 = pd.DataFrame(invs8.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs9 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT mailing_address_1, '6836 MORRISON BLVD 320' AS fixed,'' AS mail_2,'CHARLOTTE' AS city,\n",
    "    'NC' AS state, 'Morrocroft Neighborhood Stabilization Fund' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE TRIM(mailing_address_1) LIKE '6836%MOR%N%B%L%V%' AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs9 = pd.DataFrame(invs9.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs10 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '1850 PARKWAY PL 900' AS fixed, '' AS mail_2, 'MARIETTA' AS city, \n",
    "    'GA' AS state, 'First Key Homes' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE (TRIM(mailing_address_1) LIKE '1850%P%Y%PL%' OR TRIM(mailing_address_2) LIKE '1850%P%Y%PL%')\n",
    "    AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs10 = pd.DataFrame(invs10.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs11 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), 'PO BOX 1459' AS fixed, '' AS mail_2, 'LA JOLLA' AS city, \n",
    "    'CA' AS state, 'Reven Housing' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE (TRIM(mailing_address_1) LIKE 'P%O%B%X%1459%' OR TRIM(mailing_address_2) LIKE 'P%O%B%X%1459%')\n",
    "    AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs11 = pd.DataFrame(invs11.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs12 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '3500 S DUPONT HWY' AS fixed, '' AS mail_2, 'NEW YORK' AS city, \n",
    "    'NY' AS state, 'Lafayette Real Estate' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE (TRIM(mailing_address_1) LIKE '3500%DUPONT%H%W%Y' OR TRIM(mailing_address_2) \n",
    "    LIKE '3500%DUPONT%H%W%Y') AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs12 = pd.DataFrame(invs12.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs13 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1),'411 NE 2ND AVE' AS fixed,'' AS mail_2, 'HALLANDALE BEACH' AS city,\n",
    "    'FL' AS state, 'Avalon Group USA' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE (TRIM(mailing_address_1) LIKE '411%ND%AVE%' OR TRIM(mailing_address_2) LIKE '411%ND%AVE%') \n",
    "    AND mailing_address_1 NOT LIKE '%GLENDALE%'\n",
    "    AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs13 = pd.DataFrame(invs13.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs14 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '3505 KOGER BLVD 400' AS fixed, '' AS mail_2, 'DULUTH' AS city, \n",
    "    'GA' AS state, 'Havenbrook Homes' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE mailing_address_1 LIKE 'C/O HAVENBROOK%' AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs14 = pd.DataFrame(invs14.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs15 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '7821 DEERCREEK CLUB RD 200' AS fixed,'' AS mail_2, \n",
    "    'JACKSONVILLE' AS city, 'FL' AS state, 'Watson Realty Corp.' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE (TRIM(mailing_address_1) LIKE '7821%DEER%CLUB%RD%' OR TRIM(mailing_address_2) LIKE '7821%DEER%CLUB%RD%')\n",
    "    AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs15 = pd.DataFrame(invs15.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "invs16 = con.execute(\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT(mailing_address_1), '2 BATES TRL' AS fixed, '' AS mail_2, 'EAST GREENWHICH' AS city, \n",
    "    'RI' AS state, 'China Equity Inc.' AS inv_group\n",
    "    FROM Parcel\n",
    "    WHERE (TRIM(mailing_address_1) LIKE '221%HOGAN%ST%405%' OR TRIM(mailing_address_2) LIKE '221%HOGAN%ST%405%')\n",
    "    AND property_use = '0100'\n",
    "    \"\"\"\n",
    "    )\n",
    "invs16 = pd.DataFrame(invs16.fetchall(),\n",
    "                               columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "\n",
    "con.close()\n",
    "\n",
    "invs = pd.DataFrame(columns=['mailing_address_1', 'fixed', 'mail_2', 'city', 'state', \n",
    "                                        'inv_group'])\n",
    "inv_list = [invs1, invs2, invs3, invs4, invs5, invs6, invs7, invs8, invs9, invs10, invs11, invs12, invs13,\n",
    "           invs14, invs15, invs16]\n",
    "for i in inv_list:\n",
    "    invs = invs.append(i)\n",
    "invs = invs.reset_index(drop=True)\n",
    "invs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "wireless-preliminary",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Progress Residential                          1242\n",
       "American Homes 4 Rent                         1238\n",
       "Invitation Homes                              1054\n",
       "Heritage Florida Property Holdings             935\n",
       "Amherst Residential                            439\n",
       "JWB Real Estate Capital                        342\n",
       "Tricon American Homes                          309\n",
       "Morrocroft Neighborhood Stabilization Fund     284\n",
       "First Key Homes                                252\n",
       "Reven Housing                                  238\n",
       "Lafayette Real Estate                          218\n",
       "Avalon Group USA                               197\n",
       "Watson Realty Corp.                            139\n",
       "Havenbrook Homes                               134\n",
       "China Equity Inc.                              133\n",
       "Name: inv_group, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "houses['inv_group'] = None\n",
    "\n",
    "for address in invs['mailing_address_1']:\n",
    "    lst = invs.loc[invs['mailing_address_1']==address].iloc[0].to_list()\n",
    "    houses.loc[houses['mailing_address_1']==address,'mailing_address_2'] = lst[2]\n",
    "    houses.loc[houses['mailing_address_1']==address,'mail_city'] = lst[3]\n",
    "    houses.loc[houses['mailing_address_1']==address,'mail_state'] = lst[4]\n",
    "    houses.loc[houses['mailing_address_1']==address,'inv_group'] = lst[5]\n",
    "    houses.loc[houses['mailing_address_1']==address,'mailing_address_1'] = lst[1]\n",
    "\n",
    "houses['inv_group'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "average-technical",
   "metadata": {},
   "source": [
    "Define functions to use in string cleaning:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "classified-arrival",
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_punctuation(lst):\n",
    "    \"\"\"Removes punctuation from text strings.\n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    lst : list\n",
    "        List of strings\n",
    "\n",
    "    Returns\n",
    "    -------\n",
    "    cleaned: list\n",
    "        List of strings without punctuation\n",
    "    \"\"\"\n",
    "    cleaned = []\n",
    "    for thing in lst:\n",
    "        for punctuation in string.punctuation:\n",
    "            thing = thing.replace(punctuation,'')\n",
    "        cleaned.append(thing)\n",
    "    return cleaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "handy-consciousness",
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_spacing(lst):\n",
    "    \"\"\"Removes consecutive spaces.\n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    lst : list\n",
    "        List of strings\n",
    "\n",
    "    Returns\n",
    "    -------\n",
    "    cleaned: list\n",
    "        List of strings without consecutive spaces\n",
    "    \"\"\"\n",
    "    cleaned = []\n",
    "    for thing in lst:\n",
    "        thing = \" \".join(thing.split())\n",
    "        cleaned.append(thing)\n",
    "    return cleaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bacterial-healthcare",
   "metadata": {},
   "outputs": [],
   "source": [
    "def replace_special(lst, repl_dictionary):\n",
    "    \"\"\"Replaces dictionary keys with dictionary values in strings.\n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    lst : list\n",
    "        List of strings\n",
    "        \n",
    "    repl_dictionary : dictionary\n",
    "        Dictionary of replacements\n",
    "\n",
    "    Returns\n",
    "    -------\n",
    "    cleaned: list\n",
    "        List of strings with specified items replaced by specified values\n",
    "    \"\"\"\n",
    "    cleaned = []        \n",
    "    for thing in lst:\n",
    "        for k in list(repl_dictionary.keys()):\n",
    "            thing = thing.replace(k, repl_dictionary[k])\n",
    "        cleaned.append(thing)\n",
    "    return cleaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "absent-termination",
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_strings(lst, repl_dictionary={}):\n",
    "    \"\"\"Capitalizes, removes beginning and ending spaces, removes punctuation, removes consecutive\n",
    "    spaces and replaces specified substrings with specified values.\n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    lst : list\n",
    "        List of strings\n",
    "        \n",
    "    repl_dictionary : dictionary\n",
    "        Dictionary of replacements\n",
    "\n",
    "    Returns\n",
    "    -------\n",
    "    cleaned: list\n",
    "        List of cleaned strings\n",
    "    \"\"\"\n",
    "    lst = [x.upper().strip() for x in lst]\n",
    "    lst = remove_punctuation(lst)\n",
    "    lst = remove_spacing(lst)\n",
    "    lst = replace_special(lst, repl_dictionary)\n",
    "    return lst"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "several-brazil",
   "metadata": {},
   "source": [
    "Clean text columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "central-printer",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['owner'] = clean_strings(houses['owner'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "gorgeous-emergency",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Remove 'care of' lines in mailing address columns:\n",
    "\n",
    "special = ['C/O 8052 MONCRIEF-DINSMORE RD','C/O 2811 AUBREY AVE','C/O ALAN CAIN USMTM TABUK UNIT 94114',\n",
    " 'C/O 4006 STARRATT RD','C/O 4305 ROTH DR','C/O OFC WEALTH 7950 JONES BRANCH DR STE 700N']\n",
    "\n",
    "houses.loc[houses['mailing_address_1'].isin(special)==True,'mailing_address_1'] = \\\n",
    "houses.loc[houses['mailing_address_1'].isin(special)==True,'mailing_address_1'].str.replace('C/O ','')\n",
    "\n",
    "# houses.loc[houses['mailing_address_1'].str.startswith('C/O')==True,'care_of'] = \\\n",
    "# houses.loc[houses['mailing_address_1'].str.startswith('C/O')==True,'mailing_address_1'].str.replace('C/O ',\\\n",
    "#     '').str.replace('TAX DEPARTMENT','').str.replace('DEPT','')\n",
    "\n",
    "houses.loc[houses['mailing_address_1'].str.startswith('C/O')==True,'mailing_address_1'] = \\\n",
    "houses.loc[houses['mailing_address_1'].str.startswith('C/O')==True,'mailing_address_2']\n",
    "\n",
    "houses.loc[houses['mailing_address_1']==houses['mailing_address_2'],'mailing_address_2'] = ''\n",
    "\n",
    "# houses.loc[houses['mailing_address_2'].str.startswith('C/O')==True,'care_of'] = \\\n",
    "# houses.loc[houses['mailing_address_2'].str.startswith('C/O')==True,'mailing_address_2'].str.replace('C/O ',\\\n",
    "#     '').str.replace('TAX DEPARTMENT','').str.replace('DEPT','')\n",
    "\n",
    "houses.loc[houses['mailing_address_2'].str.startswith('C/O')==True,'mailing_address_2'] = ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "proprietary-sugar",
   "metadata": {},
   "outputs": [],
   "source": [
    "address_dictionary = \\\n",
    "    {' U S ':' US ', 'P O ':'PO ', 'FLORIDA':'FL', 'STATE ROAD':'SR', 'STATE RD':'SR',\n",
    "    ' FORT ':' FT ', ' SAINT':' ST', ' TRAIL':' TRL', ' PLACE':' PL', 'CIRCLE':'CIR', ' COURT':' CT', \n",
    "    ' BEACH':' BCH',\n",
    "    ' LANE':' LN', 'STREET':'ST', 'AVENUE':'AVE', ' AV ':' AVE ', 'DRIVE':'DR', ' ROAD':' RD', \n",
    "    'HIGHWAY':'HWY',\n",
    "    'BOULEVARD':'BLVD', ' CREEK':' CK', 'PLAZA':'PLZ',\n",
    "    'NORTHEAST':'NE', 'NORTHWEST':'NW', 'SOUTHEAST':'SE', 'SOUTHWEST':'SW',\n",
    "    'NORTH':'N', 'SOUTH':'S', 'EAST':'E', 'WEST':'W', \n",
    "    'FIRST':'1ST', 'SECOND':'2ND', 'THIRD':'3RD', \n",
    "    'FOURTH':'4TH', 'FIFTH':'5TH', 'SIXTH':'6TH', 'SEVENTH':'7TH', 'EIGHTH':'8TH', 'NINTH':'9TH', \n",
    "    'TENTH':'10TH', 'ELEVENTH':'11TH', 'TWELFTH':'12TH', 'THIRTEENTH':'13TH', 'FOURTEENTH':'14TH', \n",
    "    'FIFTEENTH':'15TH', 'SIXTEENTH':'16TH', 'SEVENTEENTH':'17TH', 'EIGHTEENTH':'18TH', 'NINETEENTH':'19TH',\n",
    "    'UNIT':'', 'SUITE':'', ' STE ':' ', ' SET ':'', 'BUILDING':'', 'BLDNG':'', 'APARTMENT':'',\n",
    "    ' APT ':''}\n",
    "\n",
    "houses['mailing_address_1'] = clean_strings(houses['mailing_address_1'], address_dictionary)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "moving-resolution",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['mailing_address_2'] = houses['mailing_address_2'].fillna('')\n",
    "houses['mailing_address_2'] = clean_strings(houses['mailing_address_2'], address_dictionary)\n",
    "houses['mailing_address_2'] = ['' if x.startswith('PO ') else x for x in houses['mailing_address_2']]\n",
    "\n",
    "### Combine mailing address columns\n",
    "houses['mailing_address'] = houses['mailing_address_1'] + ' ' + houses['mailing_address_2']\n",
    "houses['mailing_address'] = remove_spacing(houses['mailing_address'])\n",
    "\n",
    "houses.drop(columns=['mailing_address_1', 'mailing_address_2'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "certain-component",
   "metadata": {},
   "source": [
    "The mailing address city column is especially messy and thus needs extra steps to clean:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "stainless-nature",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-17-d0c59782482c>:35: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\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",
      "  houses['mail_city'][i] = 'NEW YORK'\n",
      "<ipython-input-17-d0c59782482c>:38: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\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",
      "  houses['mail_city'][i] = 'JACKSONVILLE'\n"
     ]
    }
   ],
   "source": [
    "houses['mail_city'] = houses['mail_city'].fillna('NONE').astype('str')\n",
    "### Remove numbers\n",
    "houses['mail_city'] = [re.sub(r'[0-9]+', '', x) for x in houses['mail_city']]\n",
    "\n",
    "### Fix certain misspellings and abbreviations\n",
    "first_replacements = \\\n",
    "    {' ON ':' ONTARIO ', 'ONTARIO THE':'ON THE', ' GU ':' GUAM ', 'ISREAL':'ISRAEL', ' IL ':' ISRAEL ',\n",
    "     'HONG KONG':'CHINA', ' CA ':' CANADA ', 'BCH':'BEACH', 'TOKYO':'JAPAN', 'LONDON':'UNITED KINGDOM',\n",
    "     'TRINIDAD':'TRINIDAD AND TOBEGO', 'ZURICH':'SWITZERLAND', 'ONTARIO':'CANADA', 'TIKVA':'ISRAEL',\n",
    "     'HAAYIN':'ISRAEL', 'TEL AVIV':'ISRAEL', 'KOREA':'SOUTH KOREA', 'MAIMI':'MIAMI'\n",
    "    }\n",
    "\n",
    "houses['mail_city'] = clean_strings(houses['mail_city'], first_replacements)\n",
    "\n",
    "### Remove directions and standardize certain words\n",
    "dirs = ['N', 'S', 'E', 'W', 'NORTH', 'SOUTH', 'EAST', 'WEST']\n",
    "repl = {'IL':'ISRAEL', 'ST':'SAINT', 'FT':'FORT', 'MT':'MOUNT'}\n",
    "cities = []\n",
    "for city in houses['mail_city']:\n",
    "    c = city.split()\n",
    "    if c[0] in dirs:\n",
    "        c = c[1:]\n",
    "    cities.append(\" \".join(c).strip())\n",
    "cities2 = []\n",
    "for city in cities:\n",
    "    c = city.split()\n",
    "    if c[0] in list(repl.keys()):\n",
    "        c[0] = repl[c[0]]\n",
    "    cities2.append(\" \".join(c).strip())\n",
    "houses['mail_city'] = cities2\n",
    "\n",
    "### Fix two specific abbreviations\n",
    "ny_index = houses[houses['mail_city'] == 'NEW YORK'].index.to_list()\n",
    "for i in ny_index:\n",
    "    houses['mail_city'][i] = 'NEW YORK'\n",
    "jax_index = houses[houses['mail_city'] == 'JAX'].index.to_list()\n",
    "for i in jax_index:\n",
    "    houses['mail_city'][i] = 'JACKSONVILLE'\n",
    "\n",
    "### If a foreign country is listed in the city column, change the value to 'FOREIGN COUNTRY'\n",
    "country_list = [\n",
    "    'SPAIN', 'ISRAEL', 'ETHIOPIA', 'CANADA', 'JAPAN', 'LEBANON','AUSTRALIA', 'COLUMBIA', 'BRAZIL', 'CHINA',\n",
    "    'KOREA', 'TURKEY', 'BAHAMAS', 'VIETNAM', 'PERU', 'NEW ZEALAND', 'FRANCE', 'BERMUDA', 'SWITZERLAND',\n",
    "    'TRINIDAD AND TOBEGO', 'ECUADOR', 'UNITED KINGDOM', 'CZECH REPUBLIC', 'JAMAICA']\n",
    "\n",
    "for country in country_list:\n",
    "    houses['mail_city'] = \\\n",
    "    ['FOREIGN COUNTRY' if x.__contains__(country) == True else x for x in houses['mail_city']]\n",
    "\n",
    "### Fix misspellings of common city names\n",
    "city_list = [\n",
    "    'JACKSONVILLE', 'JACKSONVILLE BEACH', 'SAINT AUGUSTINE', 'SAINT AUGUSTINE BEACH', 'ATLANTIC BEACH',\n",
    "    'ALEXANDRIA', 'ATLANTA', 'AMELIA ISLAND', 'BALDWIN', 'BALDWIN PARK', 'BATON ROUGE', 'BOCA RATON',\n",
    "    'CINCINNATI', 'PALATKA', 'FERNANDINA', 'FERNANDINA BEACH', 'FLEMING ISLAND', 'FLAGLER BEACH', \n",
    "    'FRONT ROYAL', 'GRAND PRAIRIE', 'HIGHSTOWN', 'HOBOKEN', 'JAMAICA', 'JAMAICA ESTATES', 'JAMAICA PLAIN',\n",
    "    'KEY BISCAYNE', 'LAS VEGAS', 'MANHATTAN', 'MIAMI', 'MIAMI BEACH', 'MIAMI GARDENS', 'MIAMI LAKES',\n",
    "    'MIAMI SHORES', 'MIAMI SPRINGS', 'MIAMISBURG', 'MARIETTA', 'MIDDLEBURG', 'NEPTUNE BEACH', \n",
    "    'NEW ORLEANS', 'NEW SMYRNA', 'NEW SMYRNA BEACH', 'OCALA', 'PONTE VEDRA BEACH', 'PONTE VEDRA',\n",
    "    'SAINT PETERSBURG', 'SAINT PETERSBURG BEACH', 'SARATOGA', 'TALLAHASSEE', 'WARNER ROBBINS', 'YULEE',\n",
    "    'INDIANAPOLIS', 'LOS ANGELES', 'FORT LAUDERDALE', 'FORT COLLINS', 'LONG BEACH', 'SCOTTSDALE']\n",
    "\n",
    "\n",
    "houses['mail_city'] = \\\n",
    "    [get_close_matches(x, city_list, n=1, cutoff=0.85)[0] if len(get_close_matches(x, city_list, \\\n",
    "    n=1, cutoff=0.85)) > 0 else x for x in houses['mail_city']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "surrounded-regulation",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['mail_state'] = houses['mail_state'].fillna('FOREIGN COUNTRY').str.replace('FG','GA')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "based-empty",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['mail_zipcode'] = houses['mail_zipcode'].fillna('00000').astype('str')\n",
    "houses['mail_zipcode'] = [x[:5] for x in houses['mail_zipcode']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "after-nursery",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['subdivision_name'] = houses['subdivision_name'].fillna('SECTION LAND')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "amino-inquiry",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['neighborhood'] = houses['neighborhood'].astype('str')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "forty-tuesday",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['street_num'] = houses['street_num'].fillna(0).astype('int64').astype('str')\n",
    "houses['direction'] = houses['direction'].fillna('')\n",
    "houses['street_name'] = houses['street_name'].fillna('NOT LISTED')\n",
    "houses['street_type'] = houses['street_type'].fillna('')\n",
    "houses['unit'] = houses['unit'].fillna('0')\n",
    "\n",
    "site_address = houses['street_num'] + ' ' + houses['direction'].map(str) + ' ' + \\\n",
    "    houses['street_name'].map(str) + ' ' + houses['street_type'].map(str) + ' ' + houses['unit'].map(str)\n",
    "site_address = [x[:-2] if x[-2:] == ' 0' else x for x in site_address]\n",
    "site_address = [re.sub(\"\\s+\",\" \", x) for x in site_address]\n",
    "houses['site_address'] = site_address\n",
    "houses.drop(columns=['street_num', 'direction', 'street_name', 'street_type', 'unit'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "united-delicious",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['city'] = remove_spacing(houses['city'].fillna('JACKSONVILLE'))\n",
    "houses['city'] = houses['city'].str.replace('JAX BEACH', 'JACKSONVILLE BEACH')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "alleged-socket",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['zipcode'] = houses['zipcode'].fillna('00000').astype('str')\n",
    "houses['zipcode'] = [x[:5] for x in houses['zipcode']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "average-witch",
   "metadata": {},
   "source": [
    "Clean columns with other data types:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "greater-dictionary",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['just_value'] = houses['just_value'].astype('int64')\n",
    "houses = houses[houses['just_value'] > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "running-picture",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['perc_school_deduction'] = houses['perc_school_deduction'].fillna(0)\n",
    "houses['perc_school_deduction'] = houses['perc_school_deduction']/100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "searching-schema",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['perc_county_deduction'] = houses['perc_county_deduction'].fillna(0)\n",
    "houses['perc_county_deduction'] = houses['perc_county_deduction']/100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "animated-montgomery",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['tax_district'] = houses['tax_district'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "regional-departure",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['lot_sf'] = houses['lot_sf'].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "amber-degree",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['type_descr'] = houses['type_descr'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "separated-london",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['class'] = houses['class'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "persistent-shannon",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['quality'] = houses['quality'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "declared-activation",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['building_value'] = houses['building_value'].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "ranking-gilbert",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['actual_yr_built'] = houses['actual_yr_built'].astype('int64')\n",
    "houses['age'] = houses['age'].astype('int64')\n",
    "houses['effec_yr_built'] = houses['effec_yr_built'].astype('int64')\n",
    "houses['effec_age'] = houses['effec_age'].astype('int64')\n",
    "houses['heated_sf'] = houses['heated_sf'].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "pediatric-importance",
   "metadata": {},
   "outputs": [],
   "source": [
    "subs = houses[houses.columns[houses.columns.str.startswith('sub_')==True].to_list()]\n",
    "for col in subs:\n",
    "    houses[col] = houses[col].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "quiet-rogers",
   "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>land_value</th>\n",
       "      <th>land_use_count</th>\n",
       "      <th>land_COMM/RES/OFF</th>\n",
       "      <th>land_RES_CANAL_LD_3-7_UNITS_PER_AC</th>\n",
       "      <th>land_RES_GOLF_LD_3-7_UNITS_PER_AC</th>\n",
       "      <th>land_RES_LD_3-7_UNITS_PER_AC</th>\n",
       "      <th>land_RES_MARSH_LD_3-7_UNITS_PER_AC</th>\n",
       "      <th>land_RES_MD_8-19_UNITS_PER_AC</th>\n",
       "      <th>land_RES_NATURAL_LD_3-7_UNITS_PER_AC</th>\n",
       "      <th>land_RES_POND_LD_3-7_UNITS_PER_AC</th>\n",
       "      <th>land_RES_RIVER_LD_3-7_UNITS_PER_AC</th>\n",
       "      <th>land_RES_RURAL_2_OR_LESS_UNITS_PER_AC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.527170e+05</td>\n",
       "      <td>252717.000000</td>\n",
       "      <td>252717.000000</td>\n",
       "      <td>252717.00000</td>\n",
       "      <td>252717.000000</td>\n",
       "      <td>252717.000000</td>\n",
       "      <td>252717.000000</td>\n",
       "      <td>252717.000000</td>\n",
       "      <td>252717.000000</td>\n",
       "      <td>252717.000000</td>\n",
       "      <td>252717.000000</td>\n",
       "      <td>252717.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>6.294612e+04</td>\n",
       "      <td>1.020477</td>\n",
       "      <td>0.004531</td>\n",
       "      <td>0.01252</td>\n",
       "      <td>0.008262</td>\n",
       "      <td>0.707645</td>\n",
       "      <td>0.004618</td>\n",
       "      <td>0.064064</td>\n",
       "      <td>0.057064</td>\n",
       "      <td>0.084644</td>\n",
       "      <td>0.013228</td>\n",
       "      <td>0.021906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.093053e+05</td>\n",
       "      <td>0.164626</td>\n",
       "      <td>0.067158</td>\n",
       "      <td>0.11119</td>\n",
       "      <td>0.090521</td>\n",
       "      <td>0.454845</td>\n",
       "      <td>0.067798</td>\n",
       "      <td>0.244867</td>\n",
       "      <td>0.231965</td>\n",
       "      <td>0.278352</td>\n",
       "      <td>0.114251</td>\n",
       "      <td>0.146377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2.572500e+04</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>4.000000e+04</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>6.402700e+04</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>7.084800e+06</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         land_value  land_use_count  land_COMM/RES/OFF  \\\n",
       "count  2.527170e+05   252717.000000      252717.000000   \n",
       "mean   6.294612e+04        1.020477           0.004531   \n",
       "std    1.093053e+05        0.164626           0.067158   \n",
       "min    0.000000e+00        0.000000           0.000000   \n",
       "25%    2.572500e+04        1.000000           0.000000   \n",
       "50%    4.000000e+04        1.000000           0.000000   \n",
       "75%    6.402700e+04        1.000000           0.000000   \n",
       "max    7.084800e+06        5.000000           1.000000   \n",
       "\n",
       "       land_RES_CANAL_LD_3-7_UNITS_PER_AC  land_RES_GOLF_LD_3-7_UNITS_PER_AC  \\\n",
       "count                        252717.00000                      252717.000000   \n",
       "mean                              0.01252                           0.008262   \n",
       "std                               0.11119                           0.090521   \n",
       "min                               0.00000                           0.000000   \n",
       "25%                               0.00000                           0.000000   \n",
       "50%                               0.00000                           0.000000   \n",
       "75%                               0.00000                           0.000000   \n",
       "max                               1.00000                           1.000000   \n",
       "\n",
       "       land_RES_LD_3-7_UNITS_PER_AC  land_RES_MARSH_LD_3-7_UNITS_PER_AC  \\\n",
       "count                 252717.000000                       252717.000000   \n",
       "mean                       0.707645                            0.004618   \n",
       "std                        0.454845                            0.067798   \n",
       "min                        0.000000                            0.000000   \n",
       "25%                        0.000000                            0.000000   \n",
       "50%                        1.000000                            0.000000   \n",
       "75%                        1.000000                            0.000000   \n",
       "max                        1.000000                            1.000000   \n",
       "\n",
       "       land_RES_MD_8-19_UNITS_PER_AC  land_RES_NATURAL_LD_3-7_UNITS_PER_AC  \\\n",
       "count                  252717.000000                         252717.000000   \n",
       "mean                        0.064064                              0.057064   \n",
       "std                         0.244867                              0.231965   \n",
       "min                         0.000000                              0.000000   \n",
       "25%                         0.000000                              0.000000   \n",
       "50%                         0.000000                              0.000000   \n",
       "75%                         0.000000                              0.000000   \n",
       "max                         1.000000                              1.000000   \n",
       "\n",
       "       land_RES_POND_LD_3-7_UNITS_PER_AC  land_RES_RIVER_LD_3-7_UNITS_PER_AC  \\\n",
       "count                      252717.000000                       252717.000000   \n",
       "mean                            0.084644                            0.013228   \n",
       "std                             0.278352                            0.114251   \n",
       "min                             0.000000                            0.000000   \n",
       "25%                             0.000000                            0.000000   \n",
       "50%                             0.000000                            0.000000   \n",
       "75%                             0.000000                            0.000000   \n",
       "max                             1.000000                            1.000000   \n",
       "\n",
       "       land_RES_RURAL_2_OR_LESS_UNITS_PER_AC  \n",
       "count                          252717.000000  \n",
       "mean                                0.021906  \n",
       "std                                 0.146377  \n",
       "min                                 0.000000  \n",
       "25%                                 0.000000  \n",
       "50%                                 0.000000  \n",
       "75%                                 0.000000  \n",
       "max                                 1.000000  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "houses['land_value'] = houses['land_value'].fillna(0)\n",
    "houses[houses.columns[houses.columns.str.startswith('land_')==True].to_list()].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "southeast-atlantic",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['feature_count'] = houses['feature_count'].fillna(0)\n",
    "houses['avg_ft_grade'] = houses['avg_ft_grade'].fillna(0)\n",
    "houses['features_value'] = houses['features_value'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "adult-territory",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['RE', 'mail_city', 'mail_state', 'mail_zipcode', 'subdivision_name',\n",
       "       'neighborhood', 'perc_capped', 'just_value', 'perc_school_deduction',\n",
       "       'perc_county_deduction', 'tax_district', 'lot_sf', 'building',\n",
       "       'type_descr', 'class', 'quality', 'actual_yr_built', 'age',\n",
       "       'effec_yr_built', 'effec_age', 'building_value', 'heated_sf',\n",
       "       'ft_Carport_Al', 'ft_Cov_Patio', 'ft_Deck_Wd', 'ft_Firep_Gas',\n",
       "       'ft_Firep_Ms', 'ft_Firep_Prf', 'ft_Pool', 'ft_Scr_Porch',\n",
       "       'ft_Screen_En', 'ft_Shed_wood', 'bldng_count', 'land_value',\n",
       "       'feature_count', 'avg_ft_grade', 'features_value', 'sub_ft_count',\n",
       "       'gross_sf', 'effec_sf', 'parcel_bldng_sf', 'owner', 'baths', 'bedrooms',\n",
       "       'stories', 'rooms', 'd_Canal', 'd_Golf_Course', 'd_Lake',\n",
       "       'd_Local_Riverside_Avondale_Historic_District',\n",
       "       'd_Local_Springfield_Historic_District', 'd_Miscellaneous_Waterway',\n",
       "       'd_National_Register_Ortega_Historic_District', 'd_Ocean_Front',\n",
       "       'd_St_Johns_River', 'd_Trout_River', 'land_use_count',\n",
       "       'land_COMM/RES/OFF', 'land_RES_CANAL_LD_3-7_UNITS_PER_AC',\n",
       "       'land_RES_GOLF_LD_3-7_UNITS_PER_AC', 'land_RES_LD_3-7_UNITS_PER_AC',\n",
       "       'land_RES_MARSH_LD_3-7_UNITS_PER_AC', 'land_RES_MD_8-19_UNITS_PER_AC',\n",
       "       'land_RES_NATURAL_LD_3-7_UNITS_PER_AC',\n",
       "       'land_RES_POND_LD_3-7_UNITS_PER_AC',\n",
       "       'land_RES_RIVER_LD_3-7_UNITS_PER_AC',\n",
       "       'land_RES_RURAL_2_OR_LESS_UNITS_PER_AC', 'sub_Addition',\n",
       "       'sub_Fin_Screened_Porch', 'sub_Finished_Carport',\n",
       "       'sub_Finished_Encl_Porch', 'sub_Finished_Garage',\n",
       "       'sub_Finished_Open_Porch', 'sub_Finished_upper_story_1',\n",
       "       'sub_Unfin_Open_Porch', 'sub_Unfinished_Garage',\n",
       "       'sub_Unfinished_Storage', 'city', 'zipcode', 'RE_b',\n",
       "       'sec_township_range', 'inv_group', 'mailing_address', 'site_address'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "houses.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adverse-killer",
   "metadata": {},
   "source": [
    "Create adjusted just value column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "broke-carol",
   "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>adj_just_value</th>\n",
       "      <th>bldng_proportion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>228683</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>75449</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>193391</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>95301</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>121447</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   adj_just_value  bldng_proportion\n",
       "0          228683               1.0\n",
       "1           75449               1.0\n",
       "2          193391               1.0\n",
       "3           95301               1.0\n",
       "4          121447               1.0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "houses['bldng_proportion'] = round(houses['gross_sf']/houses['parcel_bldng_sf'],5)\n",
    "houses['adj_just_value'] = round(houses['bldng_proportion']*houses['land_value']+houses['features_value']+\\\n",
    "    houses['building_value'],0).astype('int64')\n",
    "houses[['adj_just_value','bldng_proportion']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "right-delivery",
   "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>RE</th>\n",
       "      <th>mail_city</th>\n",
       "      <th>mail_state</th>\n",
       "      <th>mail_zipcode</th>\n",
       "      <th>subdivision_name</th>\n",
       "      <th>neighborhood</th>\n",
       "      <th>perc_capped</th>\n",
       "      <th>just_value</th>\n",
       "      <th>perc_school_deduction</th>\n",
       "      <th>perc_county_deduction</th>\n",
       "      <th>...</th>\n",
       "      <th>sub_Unfinished_Storage</th>\n",
       "      <th>city</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>RE_b</th>\n",
       "      <th>sec_township_range</th>\n",
       "      <th>inv_group</th>\n",
       "      <th>mailing_address</th>\n",
       "      <th>site_address</th>\n",
       "      <th>bldng_proportion</th>\n",
       "      <th>adj_just_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>252713</th>\n",
       "      <td>181770 0100</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>32224</td>\n",
       "      <td>SECTION LAND</td>\n",
       "      <td>314408.27</td>\n",
       "      <td>1.0</td>\n",
       "      <td>586022</td>\n",
       "      <td>-0.1252</td>\n",
       "      <td>-0.1679</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>32224</td>\n",
       "      <td>181770 0100 1</td>\n",
       "      <td>38 3S 29E</td>\n",
       "      <td>None</td>\n",
       "      <td>5055 DIXIE LANDING DR</td>\n",
       "      <td>5055 DIXIE LANDING DR</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>586022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252714</th>\n",
       "      <td>181771 0200</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>32224</td>\n",
       "      <td>SECTION LAND</td>\n",
       "      <td>314408.27</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3004603</td>\n",
       "      <td>-0.1757</td>\n",
       "      <td>-0.1840</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>32224</td>\n",
       "      <td>181771 0200 1</td>\n",
       "      <td>38 3S 29E</td>\n",
       "      <td>None</td>\n",
       "      <td>5147 DIXIE LANDING DR</td>\n",
       "      <td>5147 DIXIE LANDING DR</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>3004603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252715</th>\n",
       "      <td>181773 0000</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>32224</td>\n",
       "      <td>SECTION LAND</td>\n",
       "      <td>314408.27</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2117642</td>\n",
       "      <td>-0.7056</td>\n",
       "      <td>-0.7174</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>32224</td>\n",
       "      <td>181773 0000 1</td>\n",
       "      <td>38 3S 29E</td>\n",
       "      <td>None</td>\n",
       "      <td>5187 DIXIE LANDING DR</td>\n",
       "      <td>5187 DIXIE LANDING DR</td>\n",
       "      <td>0.79542</td>\n",
       "      <td>1686952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252716</th>\n",
       "      <td>181774 0000</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>32224</td>\n",
       "      <td>SECTION LAND</td>\n",
       "      <td>314408.27</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1814844</td>\n",
       "      <td>-0.3788</td>\n",
       "      <td>-0.3926</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>32224</td>\n",
       "      <td>181774 0000 1</td>\n",
       "      <td>38 3S 29E</td>\n",
       "      <td>None</td>\n",
       "      <td>5161 DIXIE LANDING DR</td>\n",
       "      <td>5161 DIXIE LANDING DR</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1814844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252717</th>\n",
       "      <td>181776 0000</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>FL</td>\n",
       "      <td>32250</td>\n",
       "      <td>SECTION LAND</td>\n",
       "      <td>314408.26</td>\n",
       "      <td>1.0</td>\n",
       "      <td>684370</td>\n",
       "      <td>-0.2523</td>\n",
       "      <td>-0.2889</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>JACKSONVILLE</td>\n",
       "      <td>32250</td>\n",
       "      <td>181776 0000 1</td>\n",
       "      <td>42 3S 29E</td>\n",
       "      <td>None</td>\n",
       "      <td>4351 PORT ARTHUR CT</td>\n",
       "      <td>4351 PORT ARTHUR CT</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>684370</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 86 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 RE     mail_city mail_state mail_zipcode subdivision_name  \\\n",
       "252713  181770 0100  JACKSONVILLE         FL        32224     SECTION LAND   \n",
       "252714  181771 0200  JACKSONVILLE         FL        32224     SECTION LAND   \n",
       "252715  181773 0000  JACKSONVILLE         FL        32224     SECTION LAND   \n",
       "252716  181774 0000  JACKSONVILLE         FL        32224     SECTION LAND   \n",
       "252717  181776 0000  JACKSONVILLE         FL        32250     SECTION LAND   \n",
       "\n",
       "       neighborhood  perc_capped  just_value  perc_school_deduction  \\\n",
       "252713    314408.27          1.0      586022                -0.1252   \n",
       "252714    314408.27          1.0     3004603                -0.1757   \n",
       "252715    314408.27          1.0     2117642                -0.7056   \n",
       "252716    314408.27          1.0     1814844                -0.3788   \n",
       "252717    314408.26          1.0      684370                -0.2523   \n",
       "\n",
       "        perc_county_deduction  ... sub_Unfinished_Storage          city  \\\n",
       "252713                -0.1679  ...                    1.0  JACKSONVILLE   \n",
       "252714                -0.1840  ...                    0.0  JACKSONVILLE   \n",
       "252715                -0.7174  ...                    0.0  JACKSONVILLE   \n",
       "252716                -0.3926  ...                    1.0  JACKSONVILLE   \n",
       "252717                -0.2889  ...                    0.0  JACKSONVILLE   \n",
       "\n",
       "        zipcode           RE_b sec_township_range inv_group  \\\n",
       "252713    32224  181770 0100 1          38 3S 29E      None   \n",
       "252714    32224  181771 0200 1          38 3S 29E      None   \n",
       "252715    32224  181773 0000 1          38 3S 29E      None   \n",
       "252716    32224  181774 0000 1          38 3S 29E      None   \n",
       "252717    32250  181776 0000 1          42 3S 29E      None   \n",
       "\n",
       "              mailing_address           site_address  bldng_proportion  \\\n",
       "252713  5055 DIXIE LANDING DR  5055 DIXIE LANDING DR           1.00000   \n",
       "252714  5147 DIXIE LANDING DR  5147 DIXIE LANDING DR           1.00000   \n",
       "252715  5187 DIXIE LANDING DR  5187 DIXIE LANDING DR           0.79542   \n",
       "252716  5161 DIXIE LANDING DR  5161 DIXIE LANDING DR           1.00000   \n",
       "252717    4351 PORT ARTHUR CT    4351 PORT ARTHUR CT           1.00000   \n",
       "\n",
       "        adj_just_value  \n",
       "252713          586022  \n",
       "252714         3004603  \n",
       "252715         1686952  \n",
       "252716         1814844  \n",
       "252717          684370  \n",
       "\n",
       "[5 rows x 86 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "houses.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "electronic-scheduling",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PO BOX 4090                 1242\n",
       "30601 AGOURA RD 200         1238\n",
       "1717 MAIN ST 2000           1054\n",
       "3721 DUPONT STATION CT S     758\n",
       "5001 PLZ ON THE LAKE 200     439\n",
       "1508 BROOKHOLLOW DR          408\n",
       "7563 PHILIPS HWY 208         342\n",
       "6836 MORRISON BLVD 320       284\n",
       "1850 PARKWAY PL 900          252\n",
       "PO BOX 1459                  238\n",
       "Name: mailing_address, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "houses['mailing_address'].value_counts()[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "periodic-milton",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['owner_type'] = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "honey-impact",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PRIMARY RESIDENCE    145078\n",
       "INVESTOR OWNED        23872\n",
       "FOREIGN BUYER           488\n",
       "Name: owner_type, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "investors = pd.DataFrame(houses['mailing_address'].value_counts())\n",
    "investors = investors[investors['mailing_address'] > 3].index.to_list()\n",
    "houses.loc[houses['mailing_address'].isin(investors)==True,'owner_type'] = 'INVESTOR OWNED'\n",
    "houses.loc[houses['mail_city']=='FOREIGN COUNTRY','owner_type'] = 'FOREIGN BUYER'\n",
    "houses.loc[houses['mail_state']=='FOREIGN COUNTRY','owner_type'] = 'FOREIGN BUYER'\n",
    "houses.loc[houses['mailing_address']==houses['site_address'],'owner_type'] = 'PRIMARY RESIDENCE'\n",
    "houses['owner_type'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "powered-springfield",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10000\n",
      "20000\n",
      "30000\n",
      "40000\n",
      "50000\n",
      "60000\n",
      "70000\n",
      "80000\n"
     ]
    }
   ],
   "source": [
    "ratio_list = []\n",
    "houses['ratio'] = None\n",
    "counter = 0\n",
    "for m, s in zip(houses.loc[houses['owner_type'].isnull()==True,'mailing_address'],\\\n",
    "               houses.loc[houses['owner_type'].isnull()==True,'site_address']):\n",
    "    counter +=1\n",
    "    if counter%10000==0:\n",
    "        print(counter)\n",
    "#     houses.loc[(houses['owner_type'].isnull()==True)&(houses['site_address']==s)\\\n",
    "#                 &(houses['mailing_address']==m),'ratio'] = \\\n",
    "    ratio_list.append(fuzz.ratio(m,s))\n",
    "houses.loc[houses['owner_type'].isnull()==True,'ratio'] = ratio_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "prescribed-trinity",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses.loc[houses['ratio']>=70,'owner_type'] = 'PRIMARY RESIDENCE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "federal-cattle",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses['owner_type'] = houses['owner_type'].fillna('SECONDARY RESIDENCE').astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "distributed-childhood",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses.drop(columns=['ratio'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "spanish-portfolio",
   "metadata": {},
   "outputs": [],
   "source": [
    "houses.to_csv('D:/Data/HousingInventory_EDA.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "comprehensive-convergence",
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</th>\n",
       "      <th>Unnamed: 0.1.1</th>\n",
       "      <th>RE</th>\n",
       "      <th>sale_id</th>\n",
       "      <th>seller</th>\n",
       "      <th>sale_date</th>\n",
       "      <th>price</th>\n",
       "      <th>building</th>\n",
       "      <th>actual_yr_built</th>\n",
       "      <th>heated_sf</th>\n",
       "      <th>age_at_sale</th>\n",
       "      <th>neighborhood</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>017964 0000</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1985-09-20</td>\n",
       "      <td>40500</td>\n",
       "      <td>1</td>\n",
       "      <td>1962</td>\n",
       "      <td>1075</td>\n",
       "      <td>23</td>\n",
       "      <td>412602.26</td>\n",
       "      <td>32210.0</td>\n",
       "      <td>6529 HUGH CT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>112600 0000</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2006-11-30</td>\n",
       "      <td>185000</td>\n",
       "      <td>1</td>\n",
       "      <td>1963</td>\n",
       "      <td>1644</td>\n",
       "      <td>43</td>\n",
       "      <td>214701.26</td>\n",
       "      <td>32277.0</td>\n",
       "      <td>5560 MAXINE DR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>167759 7350</td>\n",
       "      <td>4</td>\n",
       "      <td>REGIONS MORTGAGE</td>\n",
       "      <td>2002-07-10</td>\n",
       "      <td>274000</td>\n",
       "      <td>1</td>\n",
       "      <td>1999</td>\n",
       "      <td>3186</td>\n",
       "      <td>3</td>\n",
       "      <td>314411.28</td>\n",
       "      <td>32256.0</td>\n",
       "      <td>8714 E HAMPTON LANDING DR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>140237 0000</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1988-07-18</td>\n",
       "      <td>77500</td>\n",
       "      <td>1</td>\n",
       "      <td>1961</td>\n",
       "      <td>1924</td>\n",
       "      <td>27</td>\n",
       "      <td>215801.23</td>\n",
       "      <td>32216.0</td>\n",
       "      <td>1340 GROVE PARK BLVD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>010427 1020</td>\n",
       "      <td>6</td>\n",
       "      <td>POTEET MATTHEW D STEPHANIE J</td>\n",
       "      <td>2006-03-03</td>\n",
       "      <td>179000</td>\n",
       "      <td>1</td>\n",
       "      <td>1986</td>\n",
       "      <td>1533</td>\n",
       "      <td>20</td>\n",
       "      <td>412602.32</td>\n",
       "      <td>32210.0</td>\n",
       "      <td>7213 MIMOSA GROVE TRL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Unnamed: 0.1  Unnamed: 0.1.1           RE  sale_id  \\\n",
       "0           0             0               0  017964 0000        1   \n",
       "1           1             1               1  112600 0000        6   \n",
       "2           2             2               2  167759 7350        4   \n",
       "3           3             3               3  140237 0000        2   \n",
       "4           4             4               4  010427 1020        6   \n",
       "\n",
       "                         seller   sale_date   price  building  \\\n",
       "0                           NaN  1985-09-20   40500         1   \n",
       "1                           NaN  2006-11-30  185000         1   \n",
       "2              REGIONS MORTGAGE  2002-07-10  274000         1   \n",
       "3                           NaN  1988-07-18   77500         1   \n",
       "4  POTEET MATTHEW D STEPHANIE J  2006-03-03  179000         1   \n",
       "\n",
       "   actual_yr_built  heated_sf  age_at_sale  neighborhood  zipcode  \\\n",
       "0             1962       1075           23     412602.26  32210.0   \n",
       "1             1963       1644           43     214701.26  32277.0   \n",
       "2             1999       3186            3     314411.28  32256.0   \n",
       "3             1961       1924           27     215801.23  32216.0   \n",
       "4             1986       1533           20     412602.32  32210.0   \n",
       "\n",
       "                     address  \n",
       "0               6529 HUGH CT  \n",
       "1             5560 MAXINE DR  \n",
       "2  8714 E HAMPTON LANDING DR  \n",
       "3       1340 GROVE PARK BLVD  \n",
       "4      7213 MIMOSA GROVE TRL  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales = pd.read_csv('D:/Data/HomeSales.csv')\n",
    "sales.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "announced-species",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales['zipcode'] = sales['zipcode'].astype('str')\n",
    "sales['zipcode'] = [x[:5] for x in sales['zipcode']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "cellular-stevens",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales['sale_date'] = pd.to_datetime(sales['sale_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "twenty-novelty",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DR HORTON                               923\n",
       "JWB REAL ESTATE CAPITAL                 792\n",
       "SECRETARY OF VA                         461\n",
       "LAB INVESTMENTS                         403\n",
       "KB HOME                                 397\n",
       "OPENDOOR PROPERTY                       385\n",
       "LENNAR HOMES                            361\n",
       "SEDA CONSTRUCTION                       331\n",
       "LIGHTHOUSE DEVELOPMENT                  329\n",
       "MANKUS PROPERTIES                       305\n",
       "RICHMOND AMERICAN HOMES                 300\n",
       "ATLANTIC BUILDERS                       238\n",
       "DUVAL HOME BUYERS                       235\n",
       "ADAMS HOMES OF NORTHWEST FLORIDA        229\n",
       "HERITAGE FLORIDA PROPERTY HOLDINGS      204\n",
       "ATLANTIC ENTERPRISES OF JACKSONVILLE    196\n",
       "BENJIE SPERLING                         186\n",
       "CORNER LOT PROPERTIES                   177\n",
       "MERCEDES HOMES                          173\n",
       "VIGO MARK                               172\n",
       "Name: seller, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales.seller.value_counts()[:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "center-tonight",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DR HORTON                               923\n",
       "JWB REAL ESTATE CAPITAL                 792\n",
       "SECRETARY OF VA                         461\n",
       "LAB INVESTMENTS                         403\n",
       "KB HOME                                 397\n",
       "OPENDOOR PROPERTY                       385\n",
       "LENNAR HOMES                            361\n",
       "SEDA CONSTRUCTION                       331\n",
       "LIGHTHOUSE DEVELOPMENT                  329\n",
       "MANKUS PROPERTIES                       305\n",
       "RICHMOND AMERICAN HOMES                 300\n",
       "ATLANTIC BUILDERS                       238\n",
       "DUVAL HOME BUYERS                       235\n",
       "ADAMS HOMES OF NORTHWEST FLORIDA        229\n",
       "HERITAGE FLORIDA PROPERTY HOLDINGS      204\n",
       "ATLANTIC ENTERPRISES OF JACKSONVILLE    196\n",
       "BENJIE SPERLING                         186\n",
       "CORNER LOT PROPERTIES                   177\n",
       "MERCEDES HOMES                          173\n",
       "VIGO MARK                               172\n",
       "Name: seller, dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "seller_dict = {'LLC':'','INC':'','CORP':'','LP':'','F V A':'F VA','K B HOME':'KB HOME',\n",
    "               'D R HORTON':'DR HORTON', 'H U D':'HUD'}\n",
    "sales['seller'] = clean_strings(sales['seller'].fillna(''),seller_dict)\n",
    "\n",
    "\n",
    "def replace_seller(df, col, repl_dict):\n",
    "    \"\"\"Standardizes names of sellers based on repl_dict.\n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    df : DataFrame\n",
    "        Pandas DataFrame\n",
    "        \n",
    "    col: Series\n",
    "        Pandas Series\n",
    "        \n",
    "    repl_dict: dict\n",
    "        Dictionary\n",
    "\n",
    "    Returns\n",
    "    -------\n",
    "    df[col]: Series\n",
    "        Pandas Series\n",
    "    \"\"\"\n",
    "    for k, v in repl_dict.items():\n",
    "        df[col] = [v if k in x else x for x in df[col]]\n",
    "    return df[col]\n",
    "\n",
    "repl_dict = {\n",
    "    'DR HORT':'DR HORTON', 'KB HOME':'KB HOME', 'BCEL':'JWB REAL ESTATE CAPITAL',\n",
    "    'HOOSE H':'JWB REAL ESTATE CAPITAL', 'JWB':'JWB REAL ESTATE CAPITAL', \n",
    "    'MANKUS':'MANKUS PROPERTIES', 'SEDA ':'SEDA CONSTRUCTION', 'OPENDOOR':'OPENDOOR PROPERTY',\n",
    "    'RICHMOND AMERICAN':'RICHMOND AMERICAN HOMES', 'ADAMS HOMES':'ADAMS HOMES OF NORTHWEST FLORIDA',\n",
    "    'TITUS ':'TITUS GROUP', 'BEAZER':'BEAZER HOMES', 'BELLACOOP':'BELLACOOP FUND', \n",
    "    'BRIGGS RICH':'RICHARD BRIGGS', 'CENDANT ':'CENDANT MOBILITY FINANCIAL', 'COPPENBARGER':'COPPENBARGER HOMES',\n",
    "    'FIRST COAST REAL':'FIRST COAST REAL ESTATE', 'LAB INV':'LAB INVESTMENTS', \n",
    "    'LIGHTHOUSE DEV':'LIGHTHOUSE DEVELOPMENT', 'SPERLING ':'BENJIE SPERLING',\n",
    "    'SUNCOAST ':'SUNCOAST PROPERTY INVESTORS', 'WATSON WILLIAM A':'WATSON REALTY CORP',\n",
    "    '315 REAL ESTATE':'HERITAGE FLORIDA PROPERTY HOLDINGS', 'FIN HOME':'HERITAGE FLORIDA PROPERTY HOLDINGS',\n",
    "    'FLYNN H':'HERITAGE FLORIDA PROPERTY HOLDINGS', 'PIONEER PRO':'HERITAGE FLORIDA PROPERTY HOLDINGS',\n",
    "    'ARK INVESTMENT':'PRIME AREA PROPERTIES'\n",
    "    }\n",
    "\n",
    "sales['seller'] = replace_seller(sales, 'seller', repl_dict)\n",
    "\n",
    "sales['seller'] = [None if x == '' else x for x in sales['seller']]\n",
    "\n",
    "sales['seller'].value_counts()[:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "intelligent-active",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales['address'] = remove_spacing(sales['address'].fillna(''))\n",
    "sales['address'] = [None if x == '' else x for x in sales['address']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "roman-young",
   "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>RE</th>\n",
       "      <th>sale_id</th>\n",
       "      <th>seller</th>\n",
       "      <th>sale_date</th>\n",
       "      <th>price</th>\n",
       "      <th>building</th>\n",
       "      <th>actual_yr_built</th>\n",
       "      <th>heated_sf</th>\n",
       "      <th>age_at_sale</th>\n",
       "      <th>neighborhood</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>340292</th>\n",
       "      <td>061525 0000</td>\n",
       "      <td>6</td>\n",
       "      <td>TURNER APRIL L</td>\n",
       "      <td>2020-12-16</td>\n",
       "      <td>310000</td>\n",
       "      <td>1</td>\n",
       "      <td>1937</td>\n",
       "      <td>1656</td>\n",
       "      <td>83</td>\n",
       "      <td>502502.21</td>\n",
       "      <td>32205</td>\n",
       "      <td>3598 TRASK ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340293</th>\n",
       "      <td>049131 0000</td>\n",
       "      <td>8</td>\n",
       "      <td>STEVENS FAMILY TRUST</td>\n",
       "      <td>2020-12-10</td>\n",
       "      <td>78100</td>\n",
       "      <td>1</td>\n",
       "      <td>1949</td>\n",
       "      <td>1892</td>\n",
       "      <td>71</td>\n",
       "      <td>511800.22</td>\n",
       "      <td>32254</td>\n",
       "      <td>3527 ACACIA ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340294</th>\n",
       "      <td>062509 0000</td>\n",
       "      <td>11</td>\n",
       "      <td>HUTCHENS SEAMUS J</td>\n",
       "      <td>2020-12-07</td>\n",
       "      <td>240000</td>\n",
       "      <td>1</td>\n",
       "      <td>1946</td>\n",
       "      <td>1032</td>\n",
       "      <td>74</td>\n",
       "      <td>502502.21</td>\n",
       "      <td>32205</td>\n",
       "      <td>4620 ATTLEBORO ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340295</th>\n",
       "      <td>156442 0400</td>\n",
       "      <td>14</td>\n",
       "      <td>CHICOLA STEPHEN</td>\n",
       "      <td>2020-12-07</td>\n",
       "      <td>419900</td>\n",
       "      <td>1</td>\n",
       "      <td>2005</td>\n",
       "      <td>2128</td>\n",
       "      <td>15</td>\n",
       "      <td>316729.20</td>\n",
       "      <td>32257</td>\n",
       "      <td>5340 LOSCO RD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340296</th>\n",
       "      <td>106939 1020</td>\n",
       "      <td>6</td>\n",
       "      <td>WHITE NOAL D</td>\n",
       "      <td>2020-12-21</td>\n",
       "      <td>285000</td>\n",
       "      <td>1</td>\n",
       "      <td>2003</td>\n",
       "      <td>2897</td>\n",
       "      <td>17</td>\n",
       "      <td>610201.34</td>\n",
       "      <td>32218</td>\n",
       "      <td>12704 DAYLIGHT TRL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 RE  sale_id                seller  sale_date   price  \\\n",
       "340292  061525 0000        6        TURNER APRIL L 2020-12-16  310000   \n",
       "340293  049131 0000        8  STEVENS FAMILY TRUST 2020-12-10   78100   \n",
       "340294  062509 0000       11     HUTCHENS SEAMUS J 2020-12-07  240000   \n",
       "340295  156442 0400       14       CHICOLA STEPHEN 2020-12-07  419900   \n",
       "340296  106939 1020        6          WHITE NOAL D 2020-12-21  285000   \n",
       "\n",
       "        building  actual_yr_built  heated_sf  age_at_sale  neighborhood  \\\n",
       "340292         1             1937       1656           83     502502.21   \n",
       "340293         1             1949       1892           71     511800.22   \n",
       "340294         1             1946       1032           74     502502.21   \n",
       "340295         1             2005       2128           15     316729.20   \n",
       "340296         1             2003       2897           17     610201.34   \n",
       "\n",
       "       zipcode             address  \n",
       "340292   32205       3598 TRASK ST  \n",
       "340293   32254      3527 ACACIA ST  \n",
       "340294   32205   4620 ATTLEBORO ST  \n",
       "340295   32257       5340 LOSCO RD  \n",
       "340296   32218  12704 DAYLIGHT TRL  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "abroad-county",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.to_csv('D:/Data/HomeSales.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cardiac-investigation",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}