DataCleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2023 Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1506 entries, 0 to 1505\n",
      "Data columns (total 25 columns):\n",
      " #   Column                                  Non-Null Count  Dtype  \n",
      "---  ------                                  --------------  -----  \n",
      " 0   CompanyID                               1506 non-null   object \n",
      " 1   Company                                 1506 non-null   object \n",
      " 2   2019 Volume                             1011 non-null   object \n",
      " 3   2020 Volume                             1080 non-null   object \n",
      " 4   2021 Volume                             1101 non-null   object \n",
      " 5   2022 Volume                             1337 non-null   object \n",
      " 6   2023 Volume                             1506 non-null   object \n",
      " 7   2019 Sides                              1011 non-null   float64\n",
      " 8   2020 Sides                              1080 non-null   float64\n",
      " 9   2021 Sides                              1101 non-null   float64\n",
      " 10  2022 Sides                              1337 non-null   float64\n",
      " 11  2023 Sides                              1506 non-null   int64  \n",
      " 12  City                                    1506 non-null   object \n",
      " 13  State                                   1506 non-null   object \n",
      " 14  Country Code                            1506 non-null   object \n",
      " 15  Referral Network                        1506 non-null   object \n",
      " 16  Logo                                    1503 non-null   object \n",
      " 17  Mortgage Loan Transactions              37 non-null     object \n",
      " 18  Mortgage Loan Volume                    37 non-null     object \n",
      " 19  Title Policies & Services Transactions  44 non-null     object \n",
      " 20  Title Policies & Services Volume        40 non-null     object \n",
      " 21  Home Warranty Policies Sold             21 non-null     object \n",
      " 22  Homeowners Insurance Policies Sold      31 non-null     object \n",
      " 23  Escrow Policies Sold                    25 non-null     object \n",
      " 24  Avg Home Price                          1506 non-null   object \n",
      "dtypes: float64(4), int64(1), object(20)\n",
      "memory usage: 294.3+ KB\n"
     ]
    }
   ],
   "source": [
    "# df = pd.read_csv('./raw/2024_submissions_static.csv')\n",
    "df = pd.read_csv('./raw/2024_submissionsView_Will.csv')\n",
    "df.rename(columns={'Title Policies & Services Volulme':'Title Policies & Services Volume',\n",
    "                    '2024 avg home price':'Avg Home Price'}, inplace=True)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2023 Mortgages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CompanyID</th>\n",
       "      <th>Company</th>\n",
       "      <th>Mortgage Loan Transactions</th>\n",
       "      <th>Mortgage Loan Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>reczKnazN8kUWC7Ia</td>\n",
       "      <td>Seven Gables Real Estate</td>\n",
       "      <td>156</td>\n",
       "      <td>65500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>recv7rHCTFzGFuq4X</td>\n",
       "      <td>Pinnacle Estate Properties, Inc</td>\n",
       "      <td>81</td>\n",
       "      <td>537000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>recYd3CVoXzCgVO58</td>\n",
       "      <td>Helen Adams Realty</td>\n",
       "      <td>92</td>\n",
       "      <td>149000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>recKPKgjX6JQbz3qK</td>\n",
       "      <td>The Keyes Company/Illustrated Properties</td>\n",
       "      <td>390</td>\n",
       "      <td>124719168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rec7KuhkLJHD7fZhW</td>\n",
       "      <td>Cutler Real Estate</td>\n",
       "      <td>337</td>\n",
       "      <td>87779192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>recaw2ijvz7cQEjZK</td>\n",
       "      <td>The Group Inc. Real Estate Associates</td>\n",
       "      <td>393</td>\n",
       "      <td>170283560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>recNQyP3VhQnyyLHW</td>\n",
       "      <td>RE/MAX Select Realty</td>\n",
       "      <td>85</td>\n",
       "      <td>18217427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>recShAgvIUgbucJ3P</td>\n",
       "      <td>@Properties</td>\n",
       "      <td>4707</td>\n",
       "      <td>2301182594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>recE8cogq3Qq3MbfX</td>\n",
       "      <td>Michael Saunders &amp; Company</td>\n",
       "      <td>103</td>\n",
       "      <td>40574499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>recDpzQMTZlVLU3AF</td>\n",
       "      <td>Carolina One Real Estate Services</td>\n",
       "      <td>437</td>\n",
       "      <td>139414552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>recfIaMe2c3nA3VUu</td>\n",
       "      <td>The Real Brokerage, Inc.</td>\n",
       "      <td>238</td>\n",
       "      <td>79736971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>recofEf2Uvn2iqGJ0</td>\n",
       "      <td>Five Star Real Estate</td>\n",
       "      <td>15</td>\n",
       "      <td>4356714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>rec8rtGLMkTHxY4He</td>\n",
       "      <td>HUNT Real Estate ERA &amp; ERA Key Realty Services</td>\n",
       "      <td>1744</td>\n",
       "      <td>426967641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>recSwGTLQvvxIWQRu</td>\n",
       "      <td>Corner House Realty</td>\n",
       "      <td>34</td>\n",
       "      <td>11639916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>recVasXPL6zHeELWI</td>\n",
       "      <td>Madison &amp; Company Properties LLC</td>\n",
       "      <td>51</td>\n",
       "      <td>23899283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>recXwUt1EVWh1wAXd</td>\n",
       "      <td>Hanna Holdings Inc</td>\n",
       "      <td>6084</td>\n",
       "      <td>1448004553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>rec37Nf0DXWL2zIPe</td>\n",
       "      <td>Realty Concepts</td>\n",
       "      <td>112</td>\n",
       "      <td>38786889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>rec9FdKWlPikSqX7c</td>\n",
       "      <td>C3 Real Estate Solutions</td>\n",
       "      <td>29</td>\n",
       "      <td>16132814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>reccoMROv3nnDd0Cj</td>\n",
       "      <td>Terrie O'Connor Realtors</td>\n",
       "      <td>23</td>\n",
       "      <td>9926829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>rectWprZrnwysWo79</td>\n",
       "      <td>HomeServices of America</td>\n",
       "      <td>21271</td>\n",
       "      <td>6951339309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>recAQgaKshVaADSQO</td>\n",
       "      <td>EXIT Results Realty</td>\n",
       "      <td>170</td>\n",
       "      <td>55000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>recfJeDgt96jJOws9</td>\n",
       "      <td>Radius</td>\n",
       "      <td>76</td>\n",
       "      <td>303500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>recnsbQUkM7fqsG2Q</td>\n",
       "      <td>Wallace Real Estate</td>\n",
       "      <td>175</td>\n",
       "      <td>52690325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>recRTLsmLOia9EsNS</td>\n",
       "      <td>William Raveis</td>\n",
       "      <td>1525</td>\n",
       "      <td>766251704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>recwSyoFJxCRJl7La</td>\n",
       "      <td>Ruhl&amp;Ruhl Realtors</td>\n",
       "      <td>614</td>\n",
       "      <td>133359976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>recA67XhWu9KnUO51</td>\n",
       "      <td>Real Estate One Family of Companies</td>\n",
       "      <td>1734</td>\n",
       "      <td>598000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>rech3TkIZ0BRC4eYj</td>\n",
       "      <td>Baird &amp; Warner Real Estate, Inc.</td>\n",
       "      <td>1226</td>\n",
       "      <td>375817572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>recr0ORds3XtqaiDm</td>\n",
       "      <td>Crye-Leike Real Estate Services</td>\n",
       "      <td>1624</td>\n",
       "      <td>444900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>recAfTfuXZjIn8zZ8</td>\n",
       "      <td>Berkshire Hathaway HomeServices Homesale Realty</td>\n",
       "      <td>574</td>\n",
       "      <td>117700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>recLqJRvdTGNZz4p2</td>\n",
       "      <td>Coldwell Banker Howard Perry and Walston</td>\n",
       "      <td>248</td>\n",
       "      <td>68774425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>recdI2ZyMdTdDCTjE</td>\n",
       "      <td>First Team Real Estate</td>\n",
       "      <td>21</td>\n",
       "      <td>13313967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>rec29a5ryHxyfjr5m</td>\n",
       "      <td>RE/MAX Alliance</td>\n",
       "      <td>396</td>\n",
       "      <td>170000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>rec4i6lPbIGiASyPC</td>\n",
       "      <td>Watson Realty Corp.</td>\n",
       "      <td>285</td>\n",
       "      <td>84963235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>recZoQzZkPvG9ybIK</td>\n",
       "      <td>Sibcy Cline, Inc.</td>\n",
       "      <td>194</td>\n",
       "      <td>50217784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>rec2eAquo54soIhE7</td>\n",
       "      <td>London Properties</td>\n",
       "      <td>78</td>\n",
       "      <td>24852000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>rec4d2yVoDXlbQoH4</td>\n",
       "      <td>8z Real Estate</td>\n",
       "      <td>290</td>\n",
       "      <td>132720787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>recR1FWrrs0H0672r</td>\n",
       "      <td>RE/MAX Signature</td>\n",
       "      <td>100</td>\n",
       "      <td>30000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CompanyID                                          Company  \\\n",
       "0   reczKnazN8kUWC7Ia                         Seven Gables Real Estate   \n",
       "1   recv7rHCTFzGFuq4X                  Pinnacle Estate Properties, Inc   \n",
       "2   recYd3CVoXzCgVO58                               Helen Adams Realty   \n",
       "3   recKPKgjX6JQbz3qK         The Keyes Company/Illustrated Properties   \n",
       "4   rec7KuhkLJHD7fZhW                               Cutler Real Estate   \n",
       "5   recaw2ijvz7cQEjZK            The Group Inc. Real Estate Associates   \n",
       "6   recNQyP3VhQnyyLHW                             RE/MAX Select Realty   \n",
       "7   recShAgvIUgbucJ3P                                      @Properties   \n",
       "8   recE8cogq3Qq3MbfX                       Michael Saunders & Company   \n",
       "9   recDpzQMTZlVLU3AF                Carolina One Real Estate Services   \n",
       "10  recfIaMe2c3nA3VUu                         The Real Brokerage, Inc.   \n",
       "11  recofEf2Uvn2iqGJ0                            Five Star Real Estate   \n",
       "12  rec8rtGLMkTHxY4He   HUNT Real Estate ERA & ERA Key Realty Services   \n",
       "13  recSwGTLQvvxIWQRu                              Corner House Realty   \n",
       "14  recVasXPL6zHeELWI                 Madison & Company Properties LLC   \n",
       "15  recXwUt1EVWh1wAXd                               Hanna Holdings Inc   \n",
       "16  rec37Nf0DXWL2zIPe                                  Realty Concepts   \n",
       "17  rec9FdKWlPikSqX7c                         C3 Real Estate Solutions   \n",
       "18  reccoMROv3nnDd0Cj                         Terrie O'Connor Realtors   \n",
       "19  rectWprZrnwysWo79                          HomeServices of America   \n",
       "20  recAQgaKshVaADSQO                              EXIT Results Realty   \n",
       "21  recfJeDgt96jJOws9                                          Radius    \n",
       "22  recnsbQUkM7fqsG2Q                              Wallace Real Estate   \n",
       "23  recRTLsmLOia9EsNS                                   William Raveis   \n",
       "24  recwSyoFJxCRJl7La                               Ruhl&Ruhl Realtors   \n",
       "25  recA67XhWu9KnUO51             Real Estate One Family of Companies    \n",
       "26  rech3TkIZ0BRC4eYj                 Baird & Warner Real Estate, Inc.   \n",
       "27  recr0ORds3XtqaiDm                  Crye-Leike Real Estate Services   \n",
       "28  recAfTfuXZjIn8zZ8  Berkshire Hathaway HomeServices Homesale Realty   \n",
       "29  recLqJRvdTGNZz4p2         Coldwell Banker Howard Perry and Walston   \n",
       "30  recdI2ZyMdTdDCTjE                           First Team Real Estate   \n",
       "31  rec29a5ryHxyfjr5m                                  RE/MAX Alliance   \n",
       "32  rec4i6lPbIGiASyPC                             Watson Realty Corp.    \n",
       "33  recZoQzZkPvG9ybIK                                Sibcy Cline, Inc.   \n",
       "34  rec2eAquo54soIhE7                                London Properties   \n",
       "35  rec4d2yVoDXlbQoH4                                   8z Real Estate   \n",
       "36  recR1FWrrs0H0672r                                 RE/MAX Signature   \n",
       "\n",
       "    Mortgage Loan Transactions  Mortgage Loan Volume  \n",
       "0                          156              65500000  \n",
       "1                           81             537000000  \n",
       "2                           92             149000000  \n",
       "3                          390             124719168  \n",
       "4                          337              87779192  \n",
       "5                          393             170283560  \n",
       "6                           85              18217427  \n",
       "7                         4707            2301182594  \n",
       "8                          103              40574499  \n",
       "9                          437             139414552  \n",
       "10                         238              79736971  \n",
       "11                          15               4356714  \n",
       "12                        1744             426967641  \n",
       "13                          34              11639916  \n",
       "14                          51              23899283  \n",
       "15                        6084            1448004553  \n",
       "16                         112              38786889  \n",
       "17                          29              16132814  \n",
       "18                          23               9926829  \n",
       "19                       21271            6951339309  \n",
       "20                         170              55000000  \n",
       "21                          76             303500000  \n",
       "22                         175              52690325  \n",
       "23                        1525             766251704  \n",
       "24                         614             133359976  \n",
       "25                        1734             598000000  \n",
       "26                        1226             375817572  \n",
       "27                        1624             444900000  \n",
       "28                         574             117700000  \n",
       "29                         248              68774425  \n",
       "30                          21              13313967  \n",
       "31                         396             170000000  \n",
       "32                         285              84963235  \n",
       "33                         194              50217784  \n",
       "34                          78              24852000  \n",
       "35                         290             132720787  \n",
       "36                         100              30000000  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mortgage = df.loc[~df['Mortgage Loan Transactions'].isnull(), ['CompanyID', 'Company', 'Mortgage Loan Transactions', 'Mortgage Loan Volume']].reset_index(drop=True)\n",
    "# clean this: \"30.35M (total transactions overall)\"\n",
    "mortgage.loc[mortgage['Mortgage Loan Volume']=='30.35M (total transactions overall)', 'Mortgage Loan Volume'] = str(30.35 * 1000000)\n",
    "# clean punctuation\n",
    "mortgage['Mortgage Loan Volume'] = [int(x.replace(',','').replace('.','').replace('$','')) for x in mortgage['Mortgage Loan Volume']]\n",
    "mortgage['Mortgage Loan Transactions'] = [int(x.replace(',','').replace('.','').replace('$','')) for x in mortgage['Mortgage Loan Transactions']]\n",
    "mortgage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "mortgage.to_excel('./cleaned/MortgageCos.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2023 Title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>CompanyID</th>\n",
       "      <th>Company</th>\n",
       "      <th>Title Policies &amp; Services Transactions</th>\n",
       "      <th>Title Policies &amp; Services Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>recn0HaY78aRYlVnf</td>\n",
       "      <td>Stark Real Estate Operations, LLC</td>\n",
       "      <td>707</td>\n",
       "      <td>3.181734e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>recv7rHCTFzGFuq4X</td>\n",
       "      <td>Pinnacle Estate Properties, Inc</td>\n",
       "      <td>2189</td>\n",
       "      <td>5.694967e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>recd08ciluNKrBgQO</td>\n",
       "      <td>The Signature Real Estate Companies</td>\n",
       "      <td>495</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>recYd3CVoXzCgVO58</td>\n",
       "      <td>Helen Adams Realty</td>\n",
       "      <td>4045</td>\n",
       "      <td>4.115536e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>recKPKgjX6JQbz3qK</td>\n",
       "      <td>The Keyes Company/Illustrated Properties</td>\n",
       "      <td>1903</td>\n",
       "      <td>8.984137e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>rec7KuhkLJHD7fZhW</td>\n",
       "      <td>Cutler Real Estate</td>\n",
       "      <td>984</td>\n",
       "      <td>2.868011e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>recSDHAGpINE5zlGB</td>\n",
       "      <td>LAER Realty Partners</td>\n",
       "      <td>228</td>\n",
       "      <td>9.931000e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>recatTuQjuR6jwN2T</td>\n",
       "      <td>Liz Moore &amp; Associates</td>\n",
       "      <td>750</td>\n",
       "      <td>3.430000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>recNQyP3VhQnyyLHW</td>\n",
       "      <td>RE/MAX Select Realty</td>\n",
       "      <td>1282</td>\n",
       "      <td>3.252305e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>recxhnPWZGlemMAIt</td>\n",
       "      <td>Premier Sotheby's International Realty (A Peer...</td>\n",
       "      <td>219</td>\n",
       "      <td>2.900000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>recShAgvIUgbucJ3P</td>\n",
       "      <td>@Properties</td>\n",
       "      <td>6333</td>\n",
       "      <td>3.596997e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>recE8cogq3Qq3MbfX</td>\n",
       "      <td>Michael Saunders &amp; Company</td>\n",
       "      <td>836</td>\n",
       "      <td>4.214826e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>recDpzQMTZlVLU3AF</td>\n",
       "      <td>Carolina One Real Estate Services</td>\n",
       "      <td>1158</td>\n",
       "      <td>5.512080e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>recQ0oYU2eit4fkBF</td>\n",
       "      <td>McColly Real Estate</td>\n",
       "      <td>2578</td>\n",
       "      <td>6.801208e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>recfIaMe2c3nA3VUu</td>\n",
       "      <td>The Real Brokerage, Inc.</td>\n",
       "      <td>964</td>\n",
       "      <td>2.943271e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>recofEf2Uvn2iqGJ0</td>\n",
       "      <td>Five Star Real Estate</td>\n",
       "      <td>3148</td>\n",
       "      <td>5.177738e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>rec8rtGLMkTHxY4He</td>\n",
       "      <td>HUNT Real Estate ERA &amp; ERA Key Realty Services</td>\n",
       "      <td>3102</td>\n",
       "      <td>7.714631e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>rec3i5RiG6rndopjI</td>\n",
       "      <td>Samson Properties</td>\n",
       "      <td>4729</td>\n",
       "      <td>2.128050e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>recSostp44bFr6hIC</td>\n",
       "      <td>Janet McAfee Inc.</td>\n",
       "      <td>349</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>recSwGTLQvvxIWQRu</td>\n",
       "      <td>Corner House Realty</td>\n",
       "      <td>249</td>\n",
       "      <td>4.323624e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>recVasXPL6zHeELWI</td>\n",
       "      <td>Madison &amp; Company Properties LLC</td>\n",
       "      <td>41</td>\n",
       "      <td>3.121060e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>recXwUt1EVWh1wAXd</td>\n",
       "      <td>Hanna Holdings Inc</td>\n",
       "      <td>16566</td>\n",
       "      <td>4.513980e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>recZpiZw84Ktis2P7</td>\n",
       "      <td>Douglas Elliman</td>\n",
       "      <td>308</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>rec9FdKWlPikSqX7c</td>\n",
       "      <td>C3 Real Estate Solutions</td>\n",
       "      <td>157</td>\n",
       "      <td>9.828293e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>reccoMROv3nnDd0Cj</td>\n",
       "      <td>Terrie O'Connor Realtors</td>\n",
       "      <td>192</td>\n",
       "      <td>9.847600e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>receOapiZoNydj6Vx</td>\n",
       "      <td>DASH Carolina</td>\n",
       "      <td>757</td>\n",
       "      <td>5.868020e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>rectWprZrnwysWo79</td>\n",
       "      <td>HomeServices of America</td>\n",
       "      <td>79835</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>recxiWzrUbTpyL3d9</td>\n",
       "      <td>Lohmiller Real Estate</td>\n",
       "      <td>94</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>recAQgaKshVaADSQO</td>\n",
       "      <td>EXIT Results Realty</td>\n",
       "      <td>295</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>recEKkbVPLwnZMy6K</td>\n",
       "      <td>Coldwell Banker River Valley, Realtors</td>\n",
       "      <td>366</td>\n",
       "      <td>3.982360e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>recEVsAF1GXTS4FA8</td>\n",
       "      <td>Realty Executives Arizona Territory</td>\n",
       "      <td>1265</td>\n",
       "      <td>2.875279e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>reclSueTm96CQZEs7</td>\n",
       "      <td>Berkshire Hathaway HomeServices Michigan Real ...</td>\n",
       "      <td>7500</td>\n",
       "      <td>8.837612e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>recRTLsmLOia9EsNS</td>\n",
       "      <td>William Raveis</td>\n",
       "      <td>489</td>\n",
       "      <td>4.011251e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>recA67XhWu9KnUO51</td>\n",
       "      <td>Real Estate One Family of Companies</td>\n",
       "      <td>8100</td>\n",
       "      <td>2.720000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>rech3TkIZ0BRC4eYj</td>\n",
       "      <td>Baird &amp; Warner Real Estate, Inc.</td>\n",
       "      <td>5760</td>\n",
       "      <td>2.191865e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>recr0ORds3XtqaiDm</td>\n",
       "      <td>Crye-Leike Real Estate Services</td>\n",
       "      <td>9070</td>\n",
       "      <td>2.303209e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>recAfTfuXZjIn8zZ8</td>\n",
       "      <td>Berkshire Hathaway HomeServices Homesale Realty</td>\n",
       "      <td>3649</td>\n",
       "      <td>9.394758e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>recLqJRvdTGNZz4p2</td>\n",
       "      <td>Coldwell Banker Howard Perry and Walston</td>\n",
       "      <td>2300</td>\n",
       "      <td>1.170422e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>recdI2ZyMdTdDCTjE</td>\n",
       "      <td>First Team Real Estate</td>\n",
       "      <td>3851</td>\n",
       "      <td>4.790000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>rec4i6lPbIGiASyPC</td>\n",
       "      <td>Watson Realty Corp.</td>\n",
       "      <td>1938</td>\n",
       "      <td>6.627656e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>recZoQzZkPvG9ybIK</td>\n",
       "      <td>Sibcy Cline, Inc.</td>\n",
       "      <td>974</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>recJQMZy7jggpQ0mR</td>\n",
       "      <td>The Russell Realty Co</td>\n",
       "      <td>1525</td>\n",
       "      <td>8.446370e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>recbUBQTEhvKNjc3g</td>\n",
       "      <td>Chinowth &amp; Cohen</td>\n",
       "      <td>2346</td>\n",
       "      <td>4.035270e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>recdltbf6X9WGyV01</td>\n",
       "      <td>Chase International</td>\n",
       "      <td>342</td>\n",
       "      <td>3.533860e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CompanyID                                            Company  \\\n",
       "0   recn0HaY78aRYlVnf                  Stark Real Estate Operations, LLC   \n",
       "1   recv7rHCTFzGFuq4X                    Pinnacle Estate Properties, Inc   \n",
       "2   recd08ciluNKrBgQO                The Signature Real Estate Companies   \n",
       "3   recYd3CVoXzCgVO58                                 Helen Adams Realty   \n",
       "4   recKPKgjX6JQbz3qK           The Keyes Company/Illustrated Properties   \n",
       "5   rec7KuhkLJHD7fZhW                                 Cutler Real Estate   \n",
       "6   recSDHAGpINE5zlGB                               LAER Realty Partners   \n",
       "7   recatTuQjuR6jwN2T                             Liz Moore & Associates   \n",
       "8   recNQyP3VhQnyyLHW                               RE/MAX Select Realty   \n",
       "9   recxhnPWZGlemMAIt  Premier Sotheby's International Realty (A Peer...   \n",
       "10  recShAgvIUgbucJ3P                                        @Properties   \n",
       "11  recE8cogq3Qq3MbfX                         Michael Saunders & Company   \n",
       "12  recDpzQMTZlVLU3AF                  Carolina One Real Estate Services   \n",
       "13  recQ0oYU2eit4fkBF                                McColly Real Estate   \n",
       "14  recfIaMe2c3nA3VUu                           The Real Brokerage, Inc.   \n",
       "15  recofEf2Uvn2iqGJ0                              Five Star Real Estate   \n",
       "16  rec8rtGLMkTHxY4He     HUNT Real Estate ERA & ERA Key Realty Services   \n",
       "17  rec3i5RiG6rndopjI                                  Samson Properties   \n",
       "18  recSostp44bFr6hIC                                  Janet McAfee Inc.   \n",
       "19  recSwGTLQvvxIWQRu                                Corner House Realty   \n",
       "20  recVasXPL6zHeELWI                   Madison & Company Properties LLC   \n",
       "21  recXwUt1EVWh1wAXd                                 Hanna Holdings Inc   \n",
       "22  recZpiZw84Ktis2P7                                    Douglas Elliman   \n",
       "23  rec9FdKWlPikSqX7c                           C3 Real Estate Solutions   \n",
       "24  reccoMROv3nnDd0Cj                           Terrie O'Connor Realtors   \n",
       "25  receOapiZoNydj6Vx                                      DASH Carolina   \n",
       "26  rectWprZrnwysWo79                            HomeServices of America   \n",
       "27  recxiWzrUbTpyL3d9                              Lohmiller Real Estate   \n",
       "28  recAQgaKshVaADSQO                                EXIT Results Realty   \n",
       "29  recEKkbVPLwnZMy6K             Coldwell Banker River Valley, Realtors   \n",
       "30  recEVsAF1GXTS4FA8                Realty Executives Arizona Territory   \n",
       "31  reclSueTm96CQZEs7  Berkshire Hathaway HomeServices Michigan Real ...   \n",
       "32  recRTLsmLOia9EsNS                                     William Raveis   \n",
       "33  recA67XhWu9KnUO51               Real Estate One Family of Companies    \n",
       "34  rech3TkIZ0BRC4eYj                   Baird & Warner Real Estate, Inc.   \n",
       "35  recr0ORds3XtqaiDm                    Crye-Leike Real Estate Services   \n",
       "36  recAfTfuXZjIn8zZ8    Berkshire Hathaway HomeServices Homesale Realty   \n",
       "37  recLqJRvdTGNZz4p2           Coldwell Banker Howard Perry and Walston   \n",
       "38  recdI2ZyMdTdDCTjE                             First Team Real Estate   \n",
       "39  rec4i6lPbIGiASyPC                               Watson Realty Corp.    \n",
       "40  recZoQzZkPvG9ybIK                                  Sibcy Cline, Inc.   \n",
       "41  recJQMZy7jggpQ0mR                              The Russell Realty Co   \n",
       "42  recbUBQTEhvKNjc3g                                   Chinowth & Cohen   \n",
       "43  recdltbf6X9WGyV01                                Chase International   \n",
       "\n",
       "    Title Policies & Services Transactions  Title Policies & Services Volume  \n",
       "0                                      707                      3.181734e+08  \n",
       "1                                     2189                      5.694967e+06  \n",
       "2                                      495                               NaN  \n",
       "3                                     4045                      4.115536e+06  \n",
       "4                                     1903                      8.984137e+08  \n",
       "5                                      984                      2.868011e+08  \n",
       "6                                      228                      9.931000e+07  \n",
       "7                                      750                      3.430000e+08  \n",
       "8                                     1282                      3.252305e+08  \n",
       "9                                      219                      2.900000e+08  \n",
       "10                                    6333                      3.596997e+09  \n",
       "11                                     836                      4.214826e+08  \n",
       "12                                    1158                      5.512080e+08  \n",
       "13                                    2578                      6.801208e+08  \n",
       "14                                     964                      2.943271e+06  \n",
       "15                                    3148                      5.177738e+06  \n",
       "16                                    3102                      7.714631e+08  \n",
       "17                                    4729                      2.128050e+09  \n",
       "18                                     349                               NaN  \n",
       "19                                     249                      4.323624e+05  \n",
       "20                                      41                      3.121060e+07  \n",
       "21                                   16566                      4.513980e+09  \n",
       "22                                     308                               NaN  \n",
       "23                                     157                      9.828293e+07  \n",
       "24                                     192                      9.847600e+07  \n",
       "25                                     757                      5.868020e+05  \n",
       "26                                   79835                               NaN  \n",
       "27                                      94                               NaN  \n",
       "28                                     295                               NaN  \n",
       "29                                     366                      3.982360e+05  \n",
       "30                                    1265                      2.875279e+06  \n",
       "31                                    7500                      8.837612e+06  \n",
       "32                                     489                      4.011251e+08  \n",
       "33                                    8100                      2.720000e+09  \n",
       "34                                    5760                      2.191865e+06  \n",
       "35                                    9070                      2.303209e+09  \n",
       "36                                    3649                      9.394758e+08  \n",
       "37                                    2300                      1.170422e+09  \n",
       "38                                    3851                      4.790000e+09  \n",
       "39                                    1938                      6.627656e+08  \n",
       "40                                     974                               NaN  \n",
       "41                                    1525                      8.446370e+05  \n",
       "42                                    2346                      4.035270e+08  \n",
       "43                                     342                      3.533860e+08  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "titles = df.loc[~df['Title Policies & Services Transactions'].isnull(), ['CompanyID', 'Company', 'Title Policies & Services Transactions', 'Title Policies & Services Volume']].reset_index(drop=True)\n",
    "titles['Title Policies & Services Volume'] = [float(str(x).replace('$','').replace(',','')) if x != '' else x for x in titles['Title Policies & Services Volume']]\n",
    "titles['Title Policies & Services Transactions'] = titles['Title Policies & Services Transactions'].str.replace(',','').astype('int')\n",
    "titles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "titles.to_excel('./cleaned/TitleCos.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2023 Home Warranty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>CompanyID</th>\n",
       "      <th>Company</th>\n",
       "      <th>Home Warranty Policies Sold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rectWprZrnwysWo79</td>\n",
       "      <td>HomeServices of America</td>\n",
       "      <td>13539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>recMmJQNYCm4qAjB6</td>\n",
       "      <td>Jason Mitchell Real Estate</td>\n",
       "      <td>1800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>recoOjX7rPPUNShBi</td>\n",
       "      <td>Key Realty LTD</td>\n",
       "      <td>1202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>recZoQzZkPvG9ybIK</td>\n",
       "      <td>Sibcy Cline, Inc.</td>\n",
       "      <td>893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>reczKnazN8kUWC7Ia</td>\n",
       "      <td>Seven Gables Real Estate</td>\n",
       "      <td>675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>recr0ORds3XtqaiDm</td>\n",
       "      <td>Crye-Leike Real Estate Services</td>\n",
       "      <td>620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>recAfTfuXZjIn8zZ8</td>\n",
       "      <td>Berkshire Hathaway HomeServices Homesale Realty</td>\n",
       "      <td>507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>recnsbQUkM7fqsG2Q</td>\n",
       "      <td>Wallace Real Estate</td>\n",
       "      <td>480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>recSHgjKjMujDwrsd</td>\n",
       "      <td>NorthGroup Real Estate</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>rec7KuhkLJHD7fZhW</td>\n",
       "      <td>Cutler Real Estate</td>\n",
       "      <td>277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>recDpzQMTZlVLU3AF</td>\n",
       "      <td>Carolina One Real Estate Services</td>\n",
       "      <td>256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>recn0HaY78aRYlVnf</td>\n",
       "      <td>Stark Real Estate Operations, LLC</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>rec4i6lPbIGiASyPC</td>\n",
       "      <td>Watson Realty Corp.</td>\n",
       "      <td>189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>recQ0oYU2eit4fkBF</td>\n",
       "      <td>McColly Real Estate</td>\n",
       "      <td>188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>recxiWzrUbTpyL3d9</td>\n",
       "      <td>Lohmiller Real Estate</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>recHeHB9yxZudPmYr</td>\n",
       "      <td>Cressy &amp; Everett, Inc.</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>recEKkbVPLwnZMy6K</td>\n",
       "      <td>Coldwell Banker River Valley, Realtors</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>recAz270EacnZmRyu</td>\n",
       "      <td>ERA Central Realty Group</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>recHObaNWBAld9A0G</td>\n",
       "      <td>The Platinum Group, Realtors</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>recd08ciluNKrBgQO</td>\n",
       "      <td>The Signature Real Estate Companies</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CompanyID                                          Company  \\\n",
       "0   rectWprZrnwysWo79                          HomeServices of America   \n",
       "1   recMmJQNYCm4qAjB6                      Jason Mitchell Real Estate    \n",
       "2   recoOjX7rPPUNShBi                                   Key Realty LTD   \n",
       "3   recZoQzZkPvG9ybIK                                Sibcy Cline, Inc.   \n",
       "4   reczKnazN8kUWC7Ia                         Seven Gables Real Estate   \n",
       "5   recr0ORds3XtqaiDm                  Crye-Leike Real Estate Services   \n",
       "6   recAfTfuXZjIn8zZ8  Berkshire Hathaway HomeServices Homesale Realty   \n",
       "7   recnsbQUkM7fqsG2Q                              Wallace Real Estate   \n",
       "8   recSHgjKjMujDwrsd                          NorthGroup Real Estate    \n",
       "9   rec7KuhkLJHD7fZhW                               Cutler Real Estate   \n",
       "10  recDpzQMTZlVLU3AF                Carolina One Real Estate Services   \n",
       "11  recn0HaY78aRYlVnf                Stark Real Estate Operations, LLC   \n",
       "12  rec4i6lPbIGiASyPC                             Watson Realty Corp.    \n",
       "13  recQ0oYU2eit4fkBF                              McColly Real Estate   \n",
       "14  recxiWzrUbTpyL3d9                            Lohmiller Real Estate   \n",
       "15  recHeHB9yxZudPmYr                           Cressy & Everett, Inc.   \n",
       "16  recEKkbVPLwnZMy6K           Coldwell Banker River Valley, Realtors   \n",
       "17  recAz270EacnZmRyu                         ERA Central Realty Group   \n",
       "18  recHObaNWBAld9A0G                     The Platinum Group, Realtors   \n",
       "19  recd08ciluNKrBgQO              The Signature Real Estate Companies   \n",
       "\n",
       "    Home Warranty Policies Sold  \n",
       "0                         13539  \n",
       "1                          1800  \n",
       "2                          1202  \n",
       "3                           893  \n",
       "4                           675  \n",
       "5                           620  \n",
       "6                           507  \n",
       "7                           480  \n",
       "8                           300  \n",
       "9                           277  \n",
       "10                          256  \n",
       "11                          192  \n",
       "12                          189  \n",
       "13                          188  \n",
       "14                          111  \n",
       "15                          103  \n",
       "16                           52  \n",
       "17                           45  \n",
       "18                           44  \n",
       "19                           26  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "warranty = df.loc[~df['Home Warranty Policies Sold'].isnull(), ['CompanyID', 'Company', 'Home Warranty Policies Sold']].reset_index(drop=True)\n",
    "# drop 'none' value\n",
    "warranty = warranty.loc[warranty['Home Warranty Policies Sold']!='none']\n",
    "warranty['Home Warranty Policies Sold'] = warranty['Home Warranty Policies Sold'].astype('int')\n",
    "warranty.sort_values(by='Home Warranty Policies Sold', ascending=False, inplace=True)\n",
    "warranty.reset_index(drop=True, inplace=True)\n",
    "warranty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "warranty.to_excel('./cleaned/WarrantyCos.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2023 Escrow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>CompanyID</th>\n",
       "      <th>Company</th>\n",
       "      <th>Escrow Policies Sold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rectWprZrnwysWo79</td>\n",
       "      <td>HomeServices of America</td>\n",
       "      <td>25595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rec3i5RiG6rndopjI</td>\n",
       "      <td>Samson Properties</td>\n",
       "      <td>4729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>recofEf2Uvn2iqGJ0</td>\n",
       "      <td>Five Star Real Estate</td>\n",
       "      <td>3563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>recLbGeTk8MjhEEa4</td>\n",
       "      <td>NP Dodge Real Estate</td>\n",
       "      <td>2614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>recdI2ZyMdTdDCTjE</td>\n",
       "      <td>First Team Real Estate</td>\n",
       "      <td>2038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>recv7rHCTFzGFuq4X</td>\n",
       "      <td>Pinnacle Estate Properties, Inc</td>\n",
       "      <td>1734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>recaw2ijvz7cQEjZK</td>\n",
       "      <td>The Group Inc. Real Estate Associates</td>\n",
       "      <td>1711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>recZpiZw84Ktis2P7</td>\n",
       "      <td>Douglas Elliman</td>\n",
       "      <td>1439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>recJQMZy7jggpQ0mR</td>\n",
       "      <td>The Russell Realty Co</td>\n",
       "      <td>1375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>reczKnazN8kUWC7Ia</td>\n",
       "      <td>Seven Gables Real Estate</td>\n",
       "      <td>980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>recfIaMe2c3nA3VUu</td>\n",
       "      <td>The Real Brokerage, Inc.</td>\n",
       "      <td>964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>rec4d2yVoDXlbQoH4</td>\n",
       "      <td>8z Real Estate</td>\n",
       "      <td>707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>recoQkkECxZntS6VN</td>\n",
       "      <td>RE/MAX Big Bear/Area Experts</td>\n",
       "      <td>392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>recdltbf6X9WGyV01</td>\n",
       "      <td>Chase International</td>\n",
       "      <td>342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>recAQgaKshVaADSQO</td>\n",
       "      <td>EXIT Results Realty</td>\n",
       "      <td>295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>recSwGTLQvvxIWQRu</td>\n",
       "      <td>Corner House Realty</td>\n",
       "      <td>249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>recSostp44bFr6hIC</td>\n",
       "      <td>Janet McAfee Inc.</td>\n",
       "      <td>229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>recQ82De9d3cLQioA</td>\n",
       "      <td>RE/MAX Connections</td>\n",
       "      <td>223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>rec9FdKWlPikSqX7c</td>\n",
       "      <td>C3 Real Estate Solutions</td>\n",
       "      <td>157</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CompanyID                                Company  \\\n",
       "0   rectWprZrnwysWo79                HomeServices of America   \n",
       "1   rec3i5RiG6rndopjI                      Samson Properties   \n",
       "2   recofEf2Uvn2iqGJ0                  Five Star Real Estate   \n",
       "3   recLbGeTk8MjhEEa4                   NP Dodge Real Estate   \n",
       "4   recdI2ZyMdTdDCTjE                 First Team Real Estate   \n",
       "5   recv7rHCTFzGFuq4X        Pinnacle Estate Properties, Inc   \n",
       "6   recaw2ijvz7cQEjZK  The Group Inc. Real Estate Associates   \n",
       "7   recZpiZw84Ktis2P7                        Douglas Elliman   \n",
       "8   recJQMZy7jggpQ0mR                  The Russell Realty Co   \n",
       "9   reczKnazN8kUWC7Ia               Seven Gables Real Estate   \n",
       "10  recfIaMe2c3nA3VUu               The Real Brokerage, Inc.   \n",
       "11  rec4d2yVoDXlbQoH4                         8z Real Estate   \n",
       "12  recoQkkECxZntS6VN           RE/MAX Big Bear/Area Experts   \n",
       "13  recdltbf6X9WGyV01                    Chase International   \n",
       "14  recAQgaKshVaADSQO                    EXIT Results Realty   \n",
       "15  recSwGTLQvvxIWQRu                    Corner House Realty   \n",
       "16  recSostp44bFr6hIC                      Janet McAfee Inc.   \n",
       "17  recQ82De9d3cLQioA                     RE/MAX Connections   \n",
       "18  rec9FdKWlPikSqX7c               C3 Real Estate Solutions   \n",
       "\n",
       "    Escrow Policies Sold  \n",
       "0                  25595  \n",
       "1                   4729  \n",
       "2                   3563  \n",
       "3                   2614  \n",
       "4                   2038  \n",
       "5                   1734  \n",
       "6                   1711  \n",
       "7                   1439  \n",
       "8                   1375  \n",
       "9                    980  \n",
       "10                   964  \n",
       "11                   707  \n",
       "12                   392  \n",
       "13                   342  \n",
       "14                   295  \n",
       "15                   249  \n",
       "16                   229  \n",
       "17                   223  \n",
       "18                   157  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "escrow = df.loc[~df['Escrow Policies Sold'].isnull(), ['CompanyID', 'Company', 'Escrow Policies Sold']].reset_index(drop=True)\n",
    "# drop Pamela\n",
    "escrow = escrow.loc[escrow['Escrow Policies Sold']!='Pamela']\n",
    "# drop nulls\n",
    "escrow['Escrow Policies Sold'] = escrow['Escrow Policies Sold'].str.replace(',','').astype('int')\n",
    "escrow = escrow.loc[escrow['Escrow Policies Sold']>0].sort_values(by='Escrow Policies Sold', ascending=False).reset_index(drop=True)\n",
    "escrow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "escrow.to_excel('./cleaned/EscrowCos.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2023 Brokers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns=[\n",
    "    'Mortgage Loan Transactions', 'Mortgage Loan Volume',\n",
    "    'Title Policies & Services Transactions', 'Title Policies & Services Volume',\n",
    "    'Home Warranty Policies Sold',\n",
    "    'Escrow Policies Sold',\n",
    "    'Homeowners Insurance Policies Sold'\n",
    "], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CompanyID</th>\n",
       "      <td>reczKnazN8kUWC7Ia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company</th>\n",
       "      <td>Seven Gables Real Estate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019 Volume</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020 Volume</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021 Volume</th>\n",
       "      <td>$2513166869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022 Volume</th>\n",
       "      <td>$2179788441.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023 Volume</th>\n",
       "      <td>$1709676747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019 Sides</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020 Sides</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021 Sides</th>\n",
       "      <td>3031.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022 Sides</th>\n",
       "      <td>2433.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023 Sides</th>\n",
       "      <td>2119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>City</th>\n",
       "      <td>Tustin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country Code</th>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Referral Network</th>\n",
       "      <td>LeadingRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Logo</th>\n",
       "      <td>7.png (https://v5.airtableusercontent.com/v3/u...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Avg Home Price</th>\n",
       "      <td>$806832</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                  0\n",
       "CompanyID                                         reczKnazN8kUWC7Ia\n",
       "Company                                    Seven Gables Real Estate\n",
       "2019 Volume                                                     NaN\n",
       "2020 Volume                                                     NaN\n",
       "2021 Volume                                             $2513166869\n",
       "2022 Volume                                          $2179788441.00\n",
       "2023 Volume                                             $1709676747\n",
       "2019 Sides                                                      NaN\n",
       "2020 Sides                                                      NaN\n",
       "2021 Sides                                                   3031.0\n",
       "2022 Sides                                                   2433.0\n",
       "2023 Sides                                                     2119\n",
       "City                                                         Tustin\n",
       "State                                                            CA\n",
       "Country Code                                                     US\n",
       "Referral Network                                          LeadingRE\n",
       "Logo              7.png (https://v5.airtableusercontent.com/v3/u...\n",
       "Avg Home Price                                              $806832"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(1).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>CompanyID</th>\n",
       "      <th>Company</th>\n",
       "      <th>2019 Volume</th>\n",
       "      <th>2020 Volume</th>\n",
       "      <th>2021 Volume</th>\n",
       "      <th>2022 Volume</th>\n",
       "      <th>2023 Volume</th>\n",
       "      <th>2019 Sides</th>\n",
       "      <th>2020 Sides</th>\n",
       "      <th>2021 Sides</th>\n",
       "      <th>2022 Sides</th>\n",
       "      <th>2023 Sides</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>Referral Network</th>\n",
       "      <th>Logo</th>\n",
       "      <th>Avg Home Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>reczKnazN8kUWC7Ia</td>\n",
       "      <td>Seven Gables Real Estate</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.513167e+09</td>\n",
       "      <td>2.179788e+09</td>\n",
       "      <td>1.709677e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3031.0</td>\n",
       "      <td>2433.0</td>\n",
       "      <td>2119</td>\n",
       "      <td>Tustin</td>\n",
       "      <td>CA</td>\n",
       "      <td>US</td>\n",
       "      <td>LeadingRE</td>\n",
       "      <td>7.png (https://v5.airtableusercontent.com/v3/u...</td>\n",
       "      <td>806832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>recn0HaY78aRYlVnf</td>\n",
       "      <td>Stark Real Estate Operations, LLC</td>\n",
       "      <td>7.147031e+08</td>\n",
       "      <td>7.539407e+08</td>\n",
       "      <td>7.938111e+08</td>\n",
       "      <td>7.029704e+08</td>\n",
       "      <td>9.513262e+08</td>\n",
       "      <td>2469.0</td>\n",
       "      <td>2551.0</td>\n",
       "      <td>2371.0</td>\n",
       "      <td>1904.0</td>\n",
       "      <td>2203</td>\n",
       "      <td>Ficthburg</td>\n",
       "      <td>WI</td>\n",
       "      <td>US</td>\n",
       "      <td>LeadingRE</td>\n",
       "      <td>Stark_Logo__Real_People_Tagline.jpg (https://v...</td>\n",
       "      <td>431832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>recR8yxovQcfGnqBc</td>\n",
       "      <td>Keller Williams Main Line</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.392892e+08</td>\n",
       "      <td>9.685138e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1925.0</td>\n",
       "      <td>2240</td>\n",
       "      <td>Ardmore</td>\n",
       "      <td>PA</td>\n",
       "      <td>US</td>\n",
       "      <td>Keller Williams</td>\n",
       "      <td>kellerwilliams_prim_logo_pms200.png (https://v...</td>\n",
       "      <td>432372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>reccrsQM3YrT1nMgO</td>\n",
       "      <td>Keller Williams Success Realty</td>\n",
       "      <td>1.213888e+09</td>\n",
       "      <td>1.967353e+09</td>\n",
       "      <td>2.107071e+09</td>\n",
       "      <td>1.180615e+09</td>\n",
       "      <td>1.409216e+09</td>\n",
       "      <td>3856.0</td>\n",
       "      <td>5230.0</td>\n",
       "      <td>4472.0</td>\n",
       "      <td>2184.0</td>\n",
       "      <td>2737</td>\n",
       "      <td>South Ogden</td>\n",
       "      <td>UT</td>\n",
       "      <td>US</td>\n",
       "      <td>Keller Williams</td>\n",
       "      <td>kellerwilliams_prim_logo_pms200.png (https://v...</td>\n",
       "      <td>514876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>recsWmbo7T6v00rYa</td>\n",
       "      <td>CENTURY 21 North East</td>\n",
       "      <td>1.232887e+09</td>\n",
       "      <td>1.283628e+09</td>\n",
       "      <td>1.682546e+09</td>\n",
       "      <td>1.594295e+09</td>\n",
       "      <td>2.176130e+09</td>\n",
       "      <td>3293.0</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>3567.0</td>\n",
       "      <td>3277.0</td>\n",
       "      <td>5098</td>\n",
       "      <td>Peabody</td>\n",
       "      <td>MA</td>\n",
       "      <td>US</td>\n",
       "      <td>CENTURY 21</td>\n",
       "      <td>38V3SivORu6asYe0CMAi (https://v5.airtableuserc...</td>\n",
       "      <td>426860</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           CompanyID                            Company   2019 Volume  \\\n",
       "0  reczKnazN8kUWC7Ia           Seven Gables Real Estate           NaN   \n",
       "1  recn0HaY78aRYlVnf  Stark Real Estate Operations, LLC  7.147031e+08   \n",
       "2  recR8yxovQcfGnqBc          Keller Williams Main Line           NaN   \n",
       "3  reccrsQM3YrT1nMgO     Keller Williams Success Realty  1.213888e+09   \n",
       "4  recsWmbo7T6v00rYa              CENTURY 21 North East  1.232887e+09   \n",
       "\n",
       "    2020 Volume   2021 Volume   2022 Volume   2023 Volume  2019 Sides  \\\n",
       "0           NaN  2.513167e+09  2.179788e+09  1.709677e+09         NaN   \n",
       "1  7.539407e+08  7.938111e+08  7.029704e+08  9.513262e+08      2469.0   \n",
       "2           NaN           NaN  8.392892e+08  9.685138e+08         NaN   \n",
       "3  1.967353e+09  2.107071e+09  1.180615e+09  1.409216e+09      3856.0   \n",
       "4  1.283628e+09  1.682546e+09  1.594295e+09  2.176130e+09      3293.0   \n",
       "\n",
       "   2020 Sides  2021 Sides  2022 Sides  2023 Sides         City State  \\\n",
       "0         NaN      3031.0      2433.0        2119       Tustin    CA   \n",
       "1      2551.0      2371.0      1904.0        2203    Ficthburg    WI   \n",
       "2         NaN         NaN      1925.0        2240      Ardmore    PA   \n",
       "3      5230.0      4472.0      2184.0        2737  South Ogden    UT   \n",
       "4      3105.0      3567.0      3277.0        5098      Peabody    MA   \n",
       "\n",
       "  Country Code Referral Network  \\\n",
       "0           US        LeadingRE   \n",
       "1           US        LeadingRE   \n",
       "2           US  Keller Williams   \n",
       "3           US  Keller Williams   \n",
       "4           US       CENTURY 21   \n",
       "\n",
       "                                                Logo  Avg Home Price  \n",
       "0  7.png (https://v5.airtableusercontent.com/v3/u...          806832  \n",
       "1  Stark_Logo__Real_People_Tagline.jpg (https://v...          431832  \n",
       "2  kellerwilliams_prim_logo_pms200.png (https://v...          432372  \n",
       "3  kellerwilliams_prim_logo_pms200.png (https://v...          514876  \n",
       "4  38V3SivORu6asYe0CMAi (https://v5.airtableuserc...          426860  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Avg Home Price'] = df['Avg Home Price'].str.replace(',','').str.replace('$','').astype('int')\n",
    "\n",
    "for col in df.columns[(df.columns.str.contains('Volume'))]:\n",
    "    df[col] = df[col].str.replace(',','').str.replace('$','').astype('float')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('./cleaned/CleanedSubmissions.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}