preprocessing_4a_adding_additional_datasets_by_zipcodes.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "d56c7417",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import csv\n",
    "from pathlib import Path\n",
    "import datetime as dt\n",
    "from uszipcode import SearchEngine"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ddd9e06",
   "metadata": {},
   "source": [
    "## LOAD LOCATION DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "beafffb8",
   "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>RegionID</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Zip Code</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6181</td>\n",
       "      <td>New York</td>\n",
       "      <td>New York</td>\n",
       "      <td>10001</td>\n",
       "      <td>40.748418</td>\n",
       "      <td>-73.994147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17426</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>60601</td>\n",
       "      <td>41.885910</td>\n",
       "      <td>-87.623849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18959</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>89101</td>\n",
       "      <td>36.167540</td>\n",
       "      <td>-115.139739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38128</td>\n",
       "      <td>Dallas</td>\n",
       "      <td>Texas</td>\n",
       "      <td>75201</td>\n",
       "      <td>32.785918</td>\n",
       "      <td>-96.798987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10920</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>43085</td>\n",
       "      <td>40.099379</td>\n",
       "      <td>-83.015356</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>933</th>\n",
       "      <td>25643</td>\n",
       "      <td>Longboat Key</td>\n",
       "      <td>Florida</td>\n",
       "      <td>34228</td>\n",
       "      <td>27.392012</td>\n",
       "      <td>-82.641345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>934</th>\n",
       "      <td>49592</td>\n",
       "      <td>New Hope</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>18938</td>\n",
       "      <td>40.348777</td>\n",
       "      <td>-74.994791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>935</th>\n",
       "      <td>48710</td>\n",
       "      <td>Arnold</td>\n",
       "      <td>California</td>\n",
       "      <td>95223</td>\n",
       "      <td>38.257253</td>\n",
       "      <td>-120.328708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>936</th>\n",
       "      <td>12306</td>\n",
       "      <td>Langhorne</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>19047</td>\n",
       "      <td>40.178802</td>\n",
       "      <td>-74.913333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>937</th>\n",
       "      <td>33433</td>\n",
       "      <td>Pingree Grove</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>60081</td>\n",
       "      <td>42.440309</td>\n",
       "      <td>-88.217048</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>938 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     RegionID           City         State  Zip Code   Latitude   Longitude\n",
       "0        6181       New York      New York     10001  40.748418  -73.994147\n",
       "1       17426        Chicago      Illinois     60601  41.885910  -87.623849\n",
       "2       18959      Las Vegas        Nevada     89101  36.167540 -115.139739\n",
       "3       38128         Dallas         Texas     75201  32.785918  -96.798987\n",
       "4       10920       Columbus          Ohio     43085  40.099379  -83.015356\n",
       "..        ...            ...           ...       ...        ...         ...\n",
       "933     25643   Longboat Key       Florida     34228  27.392012  -82.641345\n",
       "934     49592       New Hope  Pennsylvania     18938  40.348777  -74.994791\n",
       "935     48710         Arnold    California     95223  38.257253 -120.328708\n",
       "936     12306      Langhorne  Pennsylvania     19047  40.178802  -74.913333\n",
       "937     33433  Pingree Grove      Illinois     60081  42.440309  -88.217048\n",
       "\n",
       "[938 rows x 6 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zipcodes_coords_df = pd.read_csv(\"./resources/zipcodes_coordinates.csv\")\n",
    "zipcodes_coords_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "3504ada0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create list of viable regionID's\n",
    "good_id_list = zipcodes_coords_df['RegionID'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "731649d5",
   "metadata": {},
   "source": [
    "### Load cleaned (non-transposed) dataset, then remove rows without matching location data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "790c592a",
   "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>RegionID</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>StateName</th>\n",
       "      <th>SizeRank</th>\n",
       "      <th>2011-01</th>\n",
       "      <th>2011-02</th>\n",
       "      <th>2011-03</th>\n",
       "      <th>2011-04</th>\n",
       "      <th>2011-05</th>\n",
       "      <th>2011-06</th>\n",
       "      <th>...</th>\n",
       "      <th>2019-06</th>\n",
       "      <th>2019-07</th>\n",
       "      <th>2019-08</th>\n",
       "      <th>2019-09</th>\n",
       "      <th>2019-10</th>\n",
       "      <th>2019-11</th>\n",
       "      <th>2019-12</th>\n",
       "      <th>2020-01</th>\n",
       "      <th>2020-02</th>\n",
       "      <th>2020-03</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6181</td>\n",
       "      <td>New York</td>\n",
       "      <td>New York</td>\n",
       "      <td>1</td>\n",
       "      <td>462700.0</td>\n",
       "      <td>466200.0</td>\n",
       "      <td>465600.0</td>\n",
       "      <td>469500.0</td>\n",
       "      <td>471300.0</td>\n",
       "      <td>468600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>563200.0</td>\n",
       "      <td>570500.0</td>\n",
       "      <td>572800.0</td>\n",
       "      <td>569900.0</td>\n",
       "      <td>560800.0</td>\n",
       "      <td>571500.0</td>\n",
       "      <td>575100.0</td>\n",
       "      <td>571700.0</td>\n",
       "      <td>568300.0</td>\n",
       "      <td>573600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17426</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>4</td>\n",
       "      <td>188500.0</td>\n",
       "      <td>189400.0</td>\n",
       "      <td>186300.0</td>\n",
       "      <td>177700.0</td>\n",
       "      <td>167900.0</td>\n",
       "      <td>174700.0</td>\n",
       "      <td>...</td>\n",
       "      <td>271500.0</td>\n",
       "      <td>266500.0</td>\n",
       "      <td>264900.0</td>\n",
       "      <td>265000.0</td>\n",
       "      <td>264100.0</td>\n",
       "      <td>264300.0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>281400.0</td>\n",
       "      <td>302900.0</td>\n",
       "      <td>309200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18959</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>8</td>\n",
       "      <td>115600.0</td>\n",
       "      <td>114000.0</td>\n",
       "      <td>110800.0</td>\n",
       "      <td>109600.0</td>\n",
       "      <td>108200.0</td>\n",
       "      <td>107500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>266000.0</td>\n",
       "      <td>268200.0</td>\n",
       "      <td>268400.0</td>\n",
       "      <td>270400.0</td>\n",
       "      <td>272200.0</td>\n",
       "      <td>274400.0</td>\n",
       "      <td>277500.0</td>\n",
       "      <td>278900.0</td>\n",
       "      <td>283000.0</td>\n",
       "      <td>284700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38128</td>\n",
       "      <td>Dallas</td>\n",
       "      <td>Texas</td>\n",
       "      <td>10</td>\n",
       "      <td>191900.0</td>\n",
       "      <td>184500.0</td>\n",
       "      <td>174500.0</td>\n",
       "      <td>152000.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>171400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>300600.0</td>\n",
       "      <td>301100.0</td>\n",
       "      <td>301500.0</td>\n",
       "      <td>310100.0</td>\n",
       "      <td>314600.0</td>\n",
       "      <td>316900.0</td>\n",
       "      <td>307100.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>315500.0</td>\n",
       "      <td>321700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10920</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>19</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>86000.0</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>79300.0</td>\n",
       "      <td>78600.0</td>\n",
       "      <td>82300.0</td>\n",
       "      <td>...</td>\n",
       "      <td>152600.0</td>\n",
       "      <td>154800.0</td>\n",
       "      <td>158500.0</td>\n",
       "      <td>157700.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>166600.0</td>\n",
       "      <td>172700.0</td>\n",
       "      <td>175300.0</td>\n",
       "      <td>173600.0</td>\n",
       "      <td>177600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1092</th>\n",
       "      <td>48710</td>\n",
       "      <td>Arnold</td>\n",
       "      <td>California</td>\n",
       "      <td>3704</td>\n",
       "      <td>266300.0</td>\n",
       "      <td>244200.0</td>\n",
       "      <td>217800.0</td>\n",
       "      <td>191200.0</td>\n",
       "      <td>201500.0</td>\n",
       "      <td>208400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>326500.0</td>\n",
       "      <td>317800.0</td>\n",
       "      <td>293100.0</td>\n",
       "      <td>281000.0</td>\n",
       "      <td>279100.0</td>\n",
       "      <td>279400.0</td>\n",
       "      <td>287100.0</td>\n",
       "      <td>305300.0</td>\n",
       "      <td>325600.0</td>\n",
       "      <td>345100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1093</th>\n",
       "      <td>12306</td>\n",
       "      <td>Langhorne</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>3714</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>291500.0</td>\n",
       "      <td>305600.0</td>\n",
       "      <td>342300.0</td>\n",
       "      <td>342600.0</td>\n",
       "      <td>329400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>339800.0</td>\n",
       "      <td>371300.0</td>\n",
       "      <td>358600.0</td>\n",
       "      <td>366800.0</td>\n",
       "      <td>362400.0</td>\n",
       "      <td>396600.0</td>\n",
       "      <td>398200.0</td>\n",
       "      <td>404100.0</td>\n",
       "      <td>397100.0</td>\n",
       "      <td>416200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1094</th>\n",
       "      <td>33433</td>\n",
       "      <td>Pingree Grove</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>3716</td>\n",
       "      <td>203600.0</td>\n",
       "      <td>175600.0</td>\n",
       "      <td>170200.0</td>\n",
       "      <td>184800.0</td>\n",
       "      <td>198900.0</td>\n",
       "      <td>191800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>206200.0</td>\n",
       "      <td>198200.0</td>\n",
       "      <td>214700.0</td>\n",
       "      <td>211800.0</td>\n",
       "      <td>217500.0</td>\n",
       "      <td>250200.0</td>\n",
       "      <td>270300.0</td>\n",
       "      <td>260200.0</td>\n",
       "      <td>224300.0</td>\n",
       "      <td>216600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1095</th>\n",
       "      <td>32133</td>\n",
       "      <td>Idyllwild</td>\n",
       "      <td>California</td>\n",
       "      <td>3718</td>\n",
       "      <td>182900.0</td>\n",
       "      <td>225000.0</td>\n",
       "      <td>230500.0</td>\n",
       "      <td>240100.0</td>\n",
       "      <td>182500.0</td>\n",
       "      <td>183700.0</td>\n",
       "      <td>...</td>\n",
       "      <td>313500.0</td>\n",
       "      <td>309100.0</td>\n",
       "      <td>322400.0</td>\n",
       "      <td>321300.0</td>\n",
       "      <td>342400.0</td>\n",
       "      <td>330500.0</td>\n",
       "      <td>353800.0</td>\n",
       "      <td>355500.0</td>\n",
       "      <td>389300.0</td>\n",
       "      <td>360100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1096</th>\n",
       "      <td>50339</td>\n",
       "      <td>Sea Isle City</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>3722</td>\n",
       "      <td>500500.0</td>\n",
       "      <td>538200.0</td>\n",
       "      <td>543800.0</td>\n",
       "      <td>533600.0</td>\n",
       "      <td>552300.0</td>\n",
       "      <td>536300.0</td>\n",
       "      <td>...</td>\n",
       "      <td>718400.0</td>\n",
       "      <td>743300.0</td>\n",
       "      <td>762300.0</td>\n",
       "      <td>820300.0</td>\n",
       "      <td>783400.0</td>\n",
       "      <td>789700.0</td>\n",
       "      <td>776600.0</td>\n",
       "      <td>811700.0</td>\n",
       "      <td>815700.0</td>\n",
       "      <td>795900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1097 rows × 115 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      RegionID     RegionName     StateName  SizeRank   2011-01   2011-02  \\\n",
       "0         6181       New York      New York         1  462700.0  466200.0   \n",
       "1        17426        Chicago      Illinois         4  188500.0  189400.0   \n",
       "2        18959      Las Vegas        Nevada         8  115600.0  114000.0   \n",
       "3        38128         Dallas         Texas        10  191900.0  184500.0   \n",
       "4        10920       Columbus          Ohio        19   89100.0   86000.0   \n",
       "...        ...            ...           ...       ...       ...       ...   \n",
       "1092     48710         Arnold    California      3704  266300.0  244200.0   \n",
       "1093     12306      Langhorne  Pennsylvania      3714  272300.0  291500.0   \n",
       "1094     33433  Pingree Grove      Illinois      3716  203600.0  175600.0   \n",
       "1095     32133      Idyllwild    California      3718  182900.0  225000.0   \n",
       "1096     50339  Sea Isle City    New Jersey      3722  500500.0  538200.0   \n",
       "\n",
       "       2011-03   2011-04   2011-05   2011-06  ...   2019-06   2019-07  \\\n",
       "0     465600.0  469500.0  471300.0  468600.0  ...  563200.0  570500.0   \n",
       "1     186300.0  177700.0  167900.0  174700.0  ...  271500.0  266500.0   \n",
       "2     110800.0  109600.0  108200.0  107500.0  ...  266000.0  268200.0   \n",
       "3     174500.0  152000.0  162900.0  171400.0  ...  300600.0  301100.0   \n",
       "4      79800.0   79300.0   78600.0   82300.0  ...  152600.0  154800.0   \n",
       "...        ...       ...       ...       ...  ...       ...       ...   \n",
       "1092  217800.0  191200.0  201500.0  208400.0  ...  326500.0  317800.0   \n",
       "1093  305600.0  342300.0  342600.0  329400.0  ...  339800.0  371300.0   \n",
       "1094  170200.0  184800.0  198900.0  191800.0  ...  206200.0  198200.0   \n",
       "1095  230500.0  240100.0  182500.0  183700.0  ...  313500.0  309100.0   \n",
       "1096  543800.0  533600.0  552300.0  536300.0  ...  718400.0  743300.0   \n",
       "\n",
       "       2019-08   2019-09   2019-10   2019-11   2019-12   2020-01   2020-02  \\\n",
       "0     572800.0  569900.0  560800.0  571500.0  575100.0  571700.0  568300.0   \n",
       "1     264900.0  265000.0  264100.0  264300.0  270000.0  281400.0  302900.0   \n",
       "2     268400.0  270400.0  272200.0  274400.0  277500.0  278900.0  283000.0   \n",
       "3     301500.0  310100.0  314600.0  316900.0  307100.0  321500.0  315500.0   \n",
       "4     158500.0  157700.0  162900.0  166600.0  172700.0  175300.0  173600.0   \n",
       "...        ...       ...       ...       ...       ...       ...       ...   \n",
       "1092  293100.0  281000.0  279100.0  279400.0  287100.0  305300.0  325600.0   \n",
       "1093  358600.0  366800.0  362400.0  396600.0  398200.0  404100.0  397100.0   \n",
       "1094  214700.0  211800.0  217500.0  250200.0  270300.0  260200.0  224300.0   \n",
       "1095  322400.0  321300.0  342400.0  330500.0  353800.0  355500.0  389300.0   \n",
       "1096  762300.0  820300.0  783400.0  789700.0  776600.0  811700.0  815700.0   \n",
       "\n",
       "       2020-03  \n",
       "0     573600.0  \n",
       "1     309200.0  \n",
       "2     284700.0  \n",
       "3     321700.0  \n",
       "4     177600.0  \n",
       "...        ...  \n",
       "1092  345100.0  \n",
       "1093  416200.0  \n",
       "1094  216600.0  \n",
       "1095  360100.0  \n",
       "1096  795900.0  \n",
       "\n",
       "[1097 rows x 115 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_df = pd.read_csv(\"./resources/Sale_Prices_City/Sale_Prices_City_sample.csv\")\n",
    "time_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "94fa14e8",
   "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>RegionID</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>StateName</th>\n",
       "      <th>SizeRank</th>\n",
       "      <th>2011-01</th>\n",
       "      <th>2011-02</th>\n",
       "      <th>2011-03</th>\n",
       "      <th>2011-04</th>\n",
       "      <th>2011-05</th>\n",
       "      <th>2011-06</th>\n",
       "      <th>...</th>\n",
       "      <th>2019-06</th>\n",
       "      <th>2019-07</th>\n",
       "      <th>2019-08</th>\n",
       "      <th>2019-09</th>\n",
       "      <th>2019-10</th>\n",
       "      <th>2019-11</th>\n",
       "      <th>2019-12</th>\n",
       "      <th>2020-01</th>\n",
       "      <th>2020-02</th>\n",
       "      <th>2020-03</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6181</td>\n",
       "      <td>New York</td>\n",
       "      <td>New York</td>\n",
       "      <td>1</td>\n",
       "      <td>462700.0</td>\n",
       "      <td>466200.0</td>\n",
       "      <td>465600.0</td>\n",
       "      <td>469500.0</td>\n",
       "      <td>471300.0</td>\n",
       "      <td>468600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>563200.0</td>\n",
       "      <td>570500.0</td>\n",
       "      <td>572800.0</td>\n",
       "      <td>569900.0</td>\n",
       "      <td>560800.0</td>\n",
       "      <td>571500.0</td>\n",
       "      <td>575100.0</td>\n",
       "      <td>571700.0</td>\n",
       "      <td>568300.0</td>\n",
       "      <td>573600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17426</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>4</td>\n",
       "      <td>188500.0</td>\n",
       "      <td>189400.0</td>\n",
       "      <td>186300.0</td>\n",
       "      <td>177700.0</td>\n",
       "      <td>167900.0</td>\n",
       "      <td>174700.0</td>\n",
       "      <td>...</td>\n",
       "      <td>271500.0</td>\n",
       "      <td>266500.0</td>\n",
       "      <td>264900.0</td>\n",
       "      <td>265000.0</td>\n",
       "      <td>264100.0</td>\n",
       "      <td>264300.0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>281400.0</td>\n",
       "      <td>302900.0</td>\n",
       "      <td>309200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18959</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>8</td>\n",
       "      <td>115600.0</td>\n",
       "      <td>114000.0</td>\n",
       "      <td>110800.0</td>\n",
       "      <td>109600.0</td>\n",
       "      <td>108200.0</td>\n",
       "      <td>107500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>266000.0</td>\n",
       "      <td>268200.0</td>\n",
       "      <td>268400.0</td>\n",
       "      <td>270400.0</td>\n",
       "      <td>272200.0</td>\n",
       "      <td>274400.0</td>\n",
       "      <td>277500.0</td>\n",
       "      <td>278900.0</td>\n",
       "      <td>283000.0</td>\n",
       "      <td>284700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38128</td>\n",
       "      <td>Dallas</td>\n",
       "      <td>Texas</td>\n",
       "      <td>10</td>\n",
       "      <td>191900.0</td>\n",
       "      <td>184500.0</td>\n",
       "      <td>174500.0</td>\n",
       "      <td>152000.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>171400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>300600.0</td>\n",
       "      <td>301100.0</td>\n",
       "      <td>301500.0</td>\n",
       "      <td>310100.0</td>\n",
       "      <td>314600.0</td>\n",
       "      <td>316900.0</td>\n",
       "      <td>307100.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>315500.0</td>\n",
       "      <td>321700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10920</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>19</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>86000.0</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>79300.0</td>\n",
       "      <td>78600.0</td>\n",
       "      <td>82300.0</td>\n",
       "      <td>...</td>\n",
       "      <td>152600.0</td>\n",
       "      <td>154800.0</td>\n",
       "      <td>158500.0</td>\n",
       "      <td>157700.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>166600.0</td>\n",
       "      <td>172700.0</td>\n",
       "      <td>175300.0</td>\n",
       "      <td>173600.0</td>\n",
       "      <td>177600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1090</th>\n",
       "      <td>25643</td>\n",
       "      <td>Longboat Key</td>\n",
       "      <td>Florida</td>\n",
       "      <td>3690</td>\n",
       "      <td>432300.0</td>\n",
       "      <td>476900.0</td>\n",
       "      <td>440900.0</td>\n",
       "      <td>457500.0</td>\n",
       "      <td>470600.0</td>\n",
       "      <td>489500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>567400.0</td>\n",
       "      <td>554700.0</td>\n",
       "      <td>502000.0</td>\n",
       "      <td>495600.0</td>\n",
       "      <td>535300.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>577100.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>565600.0</td>\n",
       "      <td>579700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1091</th>\n",
       "      <td>49592</td>\n",
       "      <td>New Hope</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>3697</td>\n",
       "      <td>528100.0</td>\n",
       "      <td>482600.0</td>\n",
       "      <td>454800.0</td>\n",
       "      <td>463300.0</td>\n",
       "      <td>475400.0</td>\n",
       "      <td>478600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>545100.0</td>\n",
       "      <td>548300.0</td>\n",
       "      <td>579100.0</td>\n",
       "      <td>556500.0</td>\n",
       "      <td>592700.0</td>\n",
       "      <td>530300.0</td>\n",
       "      <td>579600.0</td>\n",
       "      <td>539400.0</td>\n",
       "      <td>609700.0</td>\n",
       "      <td>608700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1092</th>\n",
       "      <td>48710</td>\n",
       "      <td>Arnold</td>\n",
       "      <td>California</td>\n",
       "      <td>3704</td>\n",
       "      <td>266300.0</td>\n",
       "      <td>244200.0</td>\n",
       "      <td>217800.0</td>\n",
       "      <td>191200.0</td>\n",
       "      <td>201500.0</td>\n",
       "      <td>208400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>326500.0</td>\n",
       "      <td>317800.0</td>\n",
       "      <td>293100.0</td>\n",
       "      <td>281000.0</td>\n",
       "      <td>279100.0</td>\n",
       "      <td>279400.0</td>\n",
       "      <td>287100.0</td>\n",
       "      <td>305300.0</td>\n",
       "      <td>325600.0</td>\n",
       "      <td>345100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1093</th>\n",
       "      <td>12306</td>\n",
       "      <td>Langhorne</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>3714</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>291500.0</td>\n",
       "      <td>305600.0</td>\n",
       "      <td>342300.0</td>\n",
       "      <td>342600.0</td>\n",
       "      <td>329400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>339800.0</td>\n",
       "      <td>371300.0</td>\n",
       "      <td>358600.0</td>\n",
       "      <td>366800.0</td>\n",
       "      <td>362400.0</td>\n",
       "      <td>396600.0</td>\n",
       "      <td>398200.0</td>\n",
       "      <td>404100.0</td>\n",
       "      <td>397100.0</td>\n",
       "      <td>416200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1094</th>\n",
       "      <td>33433</td>\n",
       "      <td>Pingree Grove</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>3716</td>\n",
       "      <td>203600.0</td>\n",
       "      <td>175600.0</td>\n",
       "      <td>170200.0</td>\n",
       "      <td>184800.0</td>\n",
       "      <td>198900.0</td>\n",
       "      <td>191800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>206200.0</td>\n",
       "      <td>198200.0</td>\n",
       "      <td>214700.0</td>\n",
       "      <td>211800.0</td>\n",
       "      <td>217500.0</td>\n",
       "      <td>250200.0</td>\n",
       "      <td>270300.0</td>\n",
       "      <td>260200.0</td>\n",
       "      <td>224300.0</td>\n",
       "      <td>216600.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>938 rows × 115 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      RegionID     RegionName     StateName  SizeRank   2011-01   2011-02  \\\n",
       "0         6181       New York      New York         1  462700.0  466200.0   \n",
       "1        17426        Chicago      Illinois         4  188500.0  189400.0   \n",
       "2        18959      Las Vegas        Nevada         8  115600.0  114000.0   \n",
       "3        38128         Dallas         Texas        10  191900.0  184500.0   \n",
       "4        10920       Columbus          Ohio        19   89100.0   86000.0   \n",
       "...        ...            ...           ...       ...       ...       ...   \n",
       "1090     25643   Longboat Key       Florida      3690  432300.0  476900.0   \n",
       "1091     49592       New Hope  Pennsylvania      3697  528100.0  482600.0   \n",
       "1092     48710         Arnold    California      3704  266300.0  244200.0   \n",
       "1093     12306      Langhorne  Pennsylvania      3714  272300.0  291500.0   \n",
       "1094     33433  Pingree Grove      Illinois      3716  203600.0  175600.0   \n",
       "\n",
       "       2011-03   2011-04   2011-05   2011-06  ...   2019-06   2019-07  \\\n",
       "0     465600.0  469500.0  471300.0  468600.0  ...  563200.0  570500.0   \n",
       "1     186300.0  177700.0  167900.0  174700.0  ...  271500.0  266500.0   \n",
       "2     110800.0  109600.0  108200.0  107500.0  ...  266000.0  268200.0   \n",
       "3     174500.0  152000.0  162900.0  171400.0  ...  300600.0  301100.0   \n",
       "4      79800.0   79300.0   78600.0   82300.0  ...  152600.0  154800.0   \n",
       "...        ...       ...       ...       ...  ...       ...       ...   \n",
       "1090  440900.0  457500.0  470600.0  489500.0  ...  567400.0  554700.0   \n",
       "1091  454800.0  463300.0  475400.0  478600.0  ...  545100.0  548300.0   \n",
       "1092  217800.0  191200.0  201500.0  208400.0  ...  326500.0  317800.0   \n",
       "1093  305600.0  342300.0  342600.0  329400.0  ...  339800.0  371300.0   \n",
       "1094  170200.0  184800.0  198900.0  191800.0  ...  206200.0  198200.0   \n",
       "\n",
       "       2019-08   2019-09   2019-10   2019-11   2019-12   2020-01   2020-02  \\\n",
       "0     572800.0  569900.0  560800.0  571500.0  575100.0  571700.0  568300.0   \n",
       "1     264900.0  265000.0  264100.0  264300.0  270000.0  281400.0  302900.0   \n",
       "2     268400.0  270400.0  272200.0  274400.0  277500.0  278900.0  283000.0   \n",
       "3     301500.0  310100.0  314600.0  316900.0  307100.0  321500.0  315500.0   \n",
       "4     158500.0  157700.0  162900.0  166600.0  172700.0  175300.0  173600.0   \n",
       "...        ...       ...       ...       ...       ...       ...       ...   \n",
       "1090  502000.0  495600.0  535300.0  557900.0  577100.0  557900.0  565600.0   \n",
       "1091  579100.0  556500.0  592700.0  530300.0  579600.0  539400.0  609700.0   \n",
       "1092  293100.0  281000.0  279100.0  279400.0  287100.0  305300.0  325600.0   \n",
       "1093  358600.0  366800.0  362400.0  396600.0  398200.0  404100.0  397100.0   \n",
       "1094  214700.0  211800.0  217500.0  250200.0  270300.0  260200.0  224300.0   \n",
       "\n",
       "       2020-03  \n",
       "0     573600.0  \n",
       "1     309200.0  \n",
       "2     284700.0  \n",
       "3     321700.0  \n",
       "4     177600.0  \n",
       "...        ...  \n",
       "1090  579700.0  \n",
       "1091  608700.0  \n",
       "1092  345100.0  \n",
       "1093  416200.0  \n",
       "1094  216600.0  \n",
       "\n",
       "[938 rows x 115 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove rows from time_df that aren't in the list of good locations\n",
    "for index, row in time_df.iterrows():\n",
    "    if row['RegionID'] not in good_id_list:\n",
    "        time_df.drop(index, inplace=True)\n",
    "        \n",
    "time_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "1cbe63b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Zip Code       object\n",
      "RegionID        int64\n",
      "RegionName     object\n",
      "StateName      object\n",
      "SizeRank        int64\n",
      "               ...   \n",
      "2020-03       float64\n",
      "City           object\n",
      "State          object\n",
      "Latitude      float64\n",
      "Longitude     float64\n",
      "Length: 120, dtype: object\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>Zip Code</th>\n",
       "      <th>RegionID</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>StateName</th>\n",
       "      <th>SizeRank</th>\n",
       "      <th>2011-01</th>\n",
       "      <th>2011-02</th>\n",
       "      <th>2011-03</th>\n",
       "      <th>2011-04</th>\n",
       "      <th>2011-05</th>\n",
       "      <th>...</th>\n",
       "      <th>2019-10</th>\n",
       "      <th>2019-11</th>\n",
       "      <th>2019-12</th>\n",
       "      <th>2020-01</th>\n",
       "      <th>2020-02</th>\n",
       "      <th>2020-03</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001</td>\n",
       "      <td>6181</td>\n",
       "      <td>New York</td>\n",
       "      <td>New York</td>\n",
       "      <td>1</td>\n",
       "      <td>462700.0</td>\n",
       "      <td>466200.0</td>\n",
       "      <td>465600.0</td>\n",
       "      <td>469500.0</td>\n",
       "      <td>471300.0</td>\n",
       "      <td>...</td>\n",
       "      <td>560800.0</td>\n",
       "      <td>571500.0</td>\n",
       "      <td>575100.0</td>\n",
       "      <td>571700.0</td>\n",
       "      <td>568300.0</td>\n",
       "      <td>573600.0</td>\n",
       "      <td>New York</td>\n",
       "      <td>New York</td>\n",
       "      <td>40.748418</td>\n",
       "      <td>-73.994147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60601</td>\n",
       "      <td>17426</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>4</td>\n",
       "      <td>188500.0</td>\n",
       "      <td>189400.0</td>\n",
       "      <td>186300.0</td>\n",
       "      <td>177700.0</td>\n",
       "      <td>167900.0</td>\n",
       "      <td>...</td>\n",
       "      <td>264100.0</td>\n",
       "      <td>264300.0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>281400.0</td>\n",
       "      <td>302900.0</td>\n",
       "      <td>309200.0</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>41.885910</td>\n",
       "      <td>-87.623849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>89101</td>\n",
       "      <td>18959</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>8</td>\n",
       "      <td>115600.0</td>\n",
       "      <td>114000.0</td>\n",
       "      <td>110800.0</td>\n",
       "      <td>109600.0</td>\n",
       "      <td>108200.0</td>\n",
       "      <td>...</td>\n",
       "      <td>272200.0</td>\n",
       "      <td>274400.0</td>\n",
       "      <td>277500.0</td>\n",
       "      <td>278900.0</td>\n",
       "      <td>283000.0</td>\n",
       "      <td>284700.0</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>36.167540</td>\n",
       "      <td>-115.139739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>75201</td>\n",
       "      <td>38128</td>\n",
       "      <td>Dallas</td>\n",
       "      <td>Texas</td>\n",
       "      <td>10</td>\n",
       "      <td>191900.0</td>\n",
       "      <td>184500.0</td>\n",
       "      <td>174500.0</td>\n",
       "      <td>152000.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>...</td>\n",
       "      <td>314600.0</td>\n",
       "      <td>316900.0</td>\n",
       "      <td>307100.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>315500.0</td>\n",
       "      <td>321700.0</td>\n",
       "      <td>Dallas</td>\n",
       "      <td>Texas</td>\n",
       "      <td>32.785918</td>\n",
       "      <td>-96.798987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>43085</td>\n",
       "      <td>10920</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>19</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>86000.0</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>79300.0</td>\n",
       "      <td>78600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>166600.0</td>\n",
       "      <td>172700.0</td>\n",
       "      <td>175300.0</td>\n",
       "      <td>173600.0</td>\n",
       "      <td>177600.0</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>40.099379</td>\n",
       "      <td>-83.015356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>933</th>\n",
       "      <td>34228</td>\n",
       "      <td>25643</td>\n",
       "      <td>Longboat Key</td>\n",
       "      <td>Florida</td>\n",
       "      <td>3690</td>\n",
       "      <td>432300.0</td>\n",
       "      <td>476900.0</td>\n",
       "      <td>440900.0</td>\n",
       "      <td>457500.0</td>\n",
       "      <td>470600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>535300.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>577100.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>565600.0</td>\n",
       "      <td>579700.0</td>\n",
       "      <td>Longboat Key</td>\n",
       "      <td>Florida</td>\n",
       "      <td>27.392012</td>\n",
       "      <td>-82.641345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>934</th>\n",
       "      <td>18938</td>\n",
       "      <td>49592</td>\n",
       "      <td>New Hope</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>3697</td>\n",
       "      <td>528100.0</td>\n",
       "      <td>482600.0</td>\n",
       "      <td>454800.0</td>\n",
       "      <td>463300.0</td>\n",
       "      <td>475400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>592700.0</td>\n",
       "      <td>530300.0</td>\n",
       "      <td>579600.0</td>\n",
       "      <td>539400.0</td>\n",
       "      <td>609700.0</td>\n",
       "      <td>608700.0</td>\n",
       "      <td>New Hope</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>40.348777</td>\n",
       "      <td>-74.994791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>935</th>\n",
       "      <td>95223</td>\n",
       "      <td>48710</td>\n",
       "      <td>Arnold</td>\n",
       "      <td>California</td>\n",
       "      <td>3704</td>\n",
       "      <td>266300.0</td>\n",
       "      <td>244200.0</td>\n",
       "      <td>217800.0</td>\n",
       "      <td>191200.0</td>\n",
       "      <td>201500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>279100.0</td>\n",
       "      <td>279400.0</td>\n",
       "      <td>287100.0</td>\n",
       "      <td>305300.0</td>\n",
       "      <td>325600.0</td>\n",
       "      <td>345100.0</td>\n",
       "      <td>Arnold</td>\n",
       "      <td>California</td>\n",
       "      <td>38.257253</td>\n",
       "      <td>-120.328708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>936</th>\n",
       "      <td>19047</td>\n",
       "      <td>12306</td>\n",
       "      <td>Langhorne</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>3714</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>291500.0</td>\n",
       "      <td>305600.0</td>\n",
       "      <td>342300.0</td>\n",
       "      <td>342600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>362400.0</td>\n",
       "      <td>396600.0</td>\n",
       "      <td>398200.0</td>\n",
       "      <td>404100.0</td>\n",
       "      <td>397100.0</td>\n",
       "      <td>416200.0</td>\n",
       "      <td>Langhorne</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>40.178802</td>\n",
       "      <td>-74.913333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>937</th>\n",
       "      <td>60081</td>\n",
       "      <td>33433</td>\n",
       "      <td>Pingree Grove</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>3716</td>\n",
       "      <td>203600.0</td>\n",
       "      <td>175600.0</td>\n",
       "      <td>170200.0</td>\n",
       "      <td>184800.0</td>\n",
       "      <td>198900.0</td>\n",
       "      <td>...</td>\n",
       "      <td>217500.0</td>\n",
       "      <td>250200.0</td>\n",
       "      <td>270300.0</td>\n",
       "      <td>260200.0</td>\n",
       "      <td>224300.0</td>\n",
       "      <td>216600.0</td>\n",
       "      <td>Pingree Grove</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>42.440309</td>\n",
       "      <td>-88.217048</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>938 rows × 120 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Zip Code  RegionID     RegionName     StateName  SizeRank   2011-01  \\\n",
       "0      10001      6181       New York      New York         1  462700.0   \n",
       "1      60601     17426        Chicago      Illinois         4  188500.0   \n",
       "2      89101     18959      Las Vegas        Nevada         8  115600.0   \n",
       "3      75201     38128         Dallas         Texas        10  191900.0   \n",
       "4      43085     10920       Columbus          Ohio        19   89100.0   \n",
       "..       ...       ...            ...           ...       ...       ...   \n",
       "933    34228     25643   Longboat Key       Florida      3690  432300.0   \n",
       "934    18938     49592       New Hope  Pennsylvania      3697  528100.0   \n",
       "935    95223     48710         Arnold    California      3704  266300.0   \n",
       "936    19047     12306      Langhorne  Pennsylvania      3714  272300.0   \n",
       "937    60081     33433  Pingree Grove      Illinois      3716  203600.0   \n",
       "\n",
       "      2011-02   2011-03   2011-04   2011-05  ...   2019-10   2019-11  \\\n",
       "0    466200.0  465600.0  469500.0  471300.0  ...  560800.0  571500.0   \n",
       "1    189400.0  186300.0  177700.0  167900.0  ...  264100.0  264300.0   \n",
       "2    114000.0  110800.0  109600.0  108200.0  ...  272200.0  274400.0   \n",
       "3    184500.0  174500.0  152000.0  162900.0  ...  314600.0  316900.0   \n",
       "4     86000.0   79800.0   79300.0   78600.0  ...  162900.0  166600.0   \n",
       "..        ...       ...       ...       ...  ...       ...       ...   \n",
       "933  476900.0  440900.0  457500.0  470600.0  ...  535300.0  557900.0   \n",
       "934  482600.0  454800.0  463300.0  475400.0  ...  592700.0  530300.0   \n",
       "935  244200.0  217800.0  191200.0  201500.0  ...  279100.0  279400.0   \n",
       "936  291500.0  305600.0  342300.0  342600.0  ...  362400.0  396600.0   \n",
       "937  175600.0  170200.0  184800.0  198900.0  ...  217500.0  250200.0   \n",
       "\n",
       "      2019-12   2020-01   2020-02   2020-03           City         State  \\\n",
       "0    575100.0  571700.0  568300.0  573600.0       New York      New York   \n",
       "1    270000.0  281400.0  302900.0  309200.0        Chicago      Illinois   \n",
       "2    277500.0  278900.0  283000.0  284700.0      Las Vegas        Nevada   \n",
       "3    307100.0  321500.0  315500.0  321700.0         Dallas         Texas   \n",
       "4    172700.0  175300.0  173600.0  177600.0       Columbus          Ohio   \n",
       "..        ...       ...       ...       ...            ...           ...   \n",
       "933  577100.0  557900.0  565600.0  579700.0   Longboat Key       Florida   \n",
       "934  579600.0  539400.0  609700.0  608700.0       New Hope  Pennsylvania   \n",
       "935  287100.0  305300.0  325600.0  345100.0         Arnold    California   \n",
       "936  398200.0  404100.0  397100.0  416200.0      Langhorne  Pennsylvania   \n",
       "937  270300.0  260200.0  224300.0  216600.0  Pingree Grove      Illinois   \n",
       "\n",
       "      Latitude   Longitude  \n",
       "0    40.748418  -73.994147  \n",
       "1    41.885910  -87.623849  \n",
       "2    36.167540 -115.139739  \n",
       "3    32.785918  -96.798987  \n",
       "4    40.099379  -83.015356  \n",
       "..         ...         ...  \n",
       "933  27.392012  -82.641345  \n",
       "934  40.348777  -74.994791  \n",
       "935  38.257253 -120.328708  \n",
       "936  40.178802  -74.913333  \n",
       "937  42.440309  -88.217048  \n",
       "\n",
       "[938 rows x 120 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge zipcodes into dataset so we can use them as column headers\n",
    "time_df = time_df.merge(zipcodes_coords_df, how='inner', on='RegionID')\n",
    "cols = ['Zip Code', 'RegionID'] + [col for col in time_df.columns if col != 'Zip Code' and col != 'RegionID']\n",
    "time_df = time_df[cols]\n",
    "time_df = time_df.dropna()\n",
    "time_df = time_df.astype({'Zip Code': 'str'})\n",
    "print(time_df.dtypes)\n",
    "time_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "4fb215a1",
   "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>DATE</th>\n",
       "      <th>10001</th>\n",
       "      <th>60601</th>\n",
       "      <th>89101</th>\n",
       "      <th>75201</th>\n",
       "      <th>43085</th>\n",
       "      <th>40202</th>\n",
       "      <th>32801</th>\n",
       "      <th>80202</th>\n",
       "      <th>20001</th>\n",
       "      <th>...</th>\n",
       "      <th>19468</th>\n",
       "      <th>32550</th>\n",
       "      <th>96738</th>\n",
       "      <th>76262</th>\n",
       "      <th>85641</th>\n",
       "      <th>34228</th>\n",
       "      <th>18938</th>\n",
       "      <th>95223</th>\n",
       "      <th>19047</th>\n",
       "      <th>60081</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>462700.0</td>\n",
       "      <td>188500.0</td>\n",
       "      <td>115600.0</td>\n",
       "      <td>191900.0</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>125900.0</td>\n",
       "      <td>82000.0</td>\n",
       "      <td>201400.0</td>\n",
       "      <td>401100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>229700.0</td>\n",
       "      <td>284400.0</td>\n",
       "      <td>237800.0</td>\n",
       "      <td>128400.0</td>\n",
       "      <td>204100.0</td>\n",
       "      <td>432300.0</td>\n",
       "      <td>528100.0</td>\n",
       "      <td>266300.0</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>203600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>466200.0</td>\n",
       "      <td>189400.0</td>\n",
       "      <td>114000.0</td>\n",
       "      <td>184500.0</td>\n",
       "      <td>86000.0</td>\n",
       "      <td>124000.0</td>\n",
       "      <td>82800.0</td>\n",
       "      <td>205300.0</td>\n",
       "      <td>386000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>216700.0</td>\n",
       "      <td>258100.0</td>\n",
       "      <td>216000.0</td>\n",
       "      <td>148400.0</td>\n",
       "      <td>194000.0</td>\n",
       "      <td>476900.0</td>\n",
       "      <td>482600.0</td>\n",
       "      <td>244200.0</td>\n",
       "      <td>291500.0</td>\n",
       "      <td>175600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>465600.0</td>\n",
       "      <td>186300.0</td>\n",
       "      <td>110800.0</td>\n",
       "      <td>174500.0</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>118300.0</td>\n",
       "      <td>85500.0</td>\n",
       "      <td>198800.0</td>\n",
       "      <td>376200.0</td>\n",
       "      <td>...</td>\n",
       "      <td>199800.0</td>\n",
       "      <td>253800.0</td>\n",
       "      <td>208800.0</td>\n",
       "      <td>150500.0</td>\n",
       "      <td>192400.0</td>\n",
       "      <td>440900.0</td>\n",
       "      <td>454800.0</td>\n",
       "      <td>217800.0</td>\n",
       "      <td>305600.0</td>\n",
       "      <td>170200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-04-01</td>\n",
       "      <td>469500.0</td>\n",
       "      <td>177700.0</td>\n",
       "      <td>109600.0</td>\n",
       "      <td>152000.0</td>\n",
       "      <td>79300.0</td>\n",
       "      <td>117600.0</td>\n",
       "      <td>87800.0</td>\n",
       "      <td>198500.0</td>\n",
       "      <td>362800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>211500.0</td>\n",
       "      <td>225700.0</td>\n",
       "      <td>229800.0</td>\n",
       "      <td>161600.0</td>\n",
       "      <td>185900.0</td>\n",
       "      <td>457500.0</td>\n",
       "      <td>463300.0</td>\n",
       "      <td>191200.0</td>\n",
       "      <td>342300.0</td>\n",
       "      <td>184800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2011-05-01</td>\n",
       "      <td>471300.0</td>\n",
       "      <td>167900.0</td>\n",
       "      <td>108200.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>78600.0</td>\n",
       "      <td>113800.0</td>\n",
       "      <td>88800.0</td>\n",
       "      <td>190900.0</td>\n",
       "      <td>370300.0</td>\n",
       "      <td>...</td>\n",
       "      <td>215300.0</td>\n",
       "      <td>230800.0</td>\n",
       "      <td>260100.0</td>\n",
       "      <td>131500.0</td>\n",
       "      <td>175900.0</td>\n",
       "      <td>470600.0</td>\n",
       "      <td>475400.0</td>\n",
       "      <td>201500.0</td>\n",
       "      <td>342600.0</td>\n",
       "      <td>198900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>571500.0</td>\n",
       "      <td>264300.0</td>\n",
       "      <td>274400.0</td>\n",
       "      <td>316900.0</td>\n",
       "      <td>166600.0</td>\n",
       "      <td>174600.0</td>\n",
       "      <td>247400.0</td>\n",
       "      <td>445400.0</td>\n",
       "      <td>566400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>264100.0</td>\n",
       "      <td>370700.0</td>\n",
       "      <td>402700.0</td>\n",
       "      <td>343700.0</td>\n",
       "      <td>297200.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>530300.0</td>\n",
       "      <td>279400.0</td>\n",
       "      <td>396600.0</td>\n",
       "      <td>250200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>575100.0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>277500.0</td>\n",
       "      <td>307100.0</td>\n",
       "      <td>172700.0</td>\n",
       "      <td>176600.0</td>\n",
       "      <td>251100.0</td>\n",
       "      <td>435500.0</td>\n",
       "      <td>577600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>265200.0</td>\n",
       "      <td>412500.0</td>\n",
       "      <td>454400.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>292100.0</td>\n",
       "      <td>577100.0</td>\n",
       "      <td>579600.0</td>\n",
       "      <td>287100.0</td>\n",
       "      <td>398200.0</td>\n",
       "      <td>270300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>571700.0</td>\n",
       "      <td>281400.0</td>\n",
       "      <td>278900.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>175300.0</td>\n",
       "      <td>170800.0</td>\n",
       "      <td>254800.0</td>\n",
       "      <td>435100.0</td>\n",
       "      <td>580100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>408900.0</td>\n",
       "      <td>499200.0</td>\n",
       "      <td>273700.0</td>\n",
       "      <td>275100.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>539400.0</td>\n",
       "      <td>305300.0</td>\n",
       "      <td>404100.0</td>\n",
       "      <td>260200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>568300.0</td>\n",
       "      <td>302900.0</td>\n",
       "      <td>283000.0</td>\n",
       "      <td>315500.0</td>\n",
       "      <td>173600.0</td>\n",
       "      <td>173100.0</td>\n",
       "      <td>263500.0</td>\n",
       "      <td>436900.0</td>\n",
       "      <td>575700.0</td>\n",
       "      <td>...</td>\n",
       "      <td>248800.0</td>\n",
       "      <td>409000.0</td>\n",
       "      <td>513400.0</td>\n",
       "      <td>314400.0</td>\n",
       "      <td>265400.0</td>\n",
       "      <td>565600.0</td>\n",
       "      <td>609700.0</td>\n",
       "      <td>325600.0</td>\n",
       "      <td>397100.0</td>\n",
       "      <td>224300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>573600.0</td>\n",
       "      <td>309200.0</td>\n",
       "      <td>284700.0</td>\n",
       "      <td>321700.0</td>\n",
       "      <td>177600.0</td>\n",
       "      <td>176200.0</td>\n",
       "      <td>270500.0</td>\n",
       "      <td>442700.0</td>\n",
       "      <td>586100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>235100.0</td>\n",
       "      <td>379500.0</td>\n",
       "      <td>487200.0</td>\n",
       "      <td>319400.0</td>\n",
       "      <td>269300.0</td>\n",
       "      <td>579700.0</td>\n",
       "      <td>608700.0</td>\n",
       "      <td>345100.0</td>\n",
       "      <td>416200.0</td>\n",
       "      <td>216600.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>111 rows × 939 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          DATE     10001     60601     89101     75201     43085     40202  \\\n",
       "1   2011-01-01  462700.0  188500.0  115600.0  191900.0   89100.0  125900.0   \n",
       "2   2011-02-01  466200.0  189400.0  114000.0  184500.0   86000.0  124000.0   \n",
       "3   2011-03-01  465600.0  186300.0  110800.0  174500.0   79800.0  118300.0   \n",
       "4   2011-04-01  469500.0  177700.0  109600.0  152000.0   79300.0  117600.0   \n",
       "5   2011-05-01  471300.0  167900.0  108200.0  162900.0   78600.0  113800.0   \n",
       "..         ...       ...       ...       ...       ...       ...       ...   \n",
       "107 2019-11-01  571500.0  264300.0  274400.0  316900.0  166600.0  174600.0   \n",
       "108 2019-12-01  575100.0  270000.0  277500.0  307100.0  172700.0  176600.0   \n",
       "109 2020-01-01  571700.0  281400.0  278900.0  321500.0  175300.0  170800.0   \n",
       "110 2020-02-01  568300.0  302900.0  283000.0  315500.0  173600.0  173100.0   \n",
       "111 2020-03-01  573600.0  309200.0  284700.0  321700.0  177600.0  176200.0   \n",
       "\n",
       "        32801     80202     20001  ...     19468     32550     96738  \\\n",
       "1     82000.0  201400.0  401100.0  ...  229700.0  284400.0  237800.0   \n",
       "2     82800.0  205300.0  386000.0  ...  216700.0  258100.0  216000.0   \n",
       "3     85500.0  198800.0  376200.0  ...  199800.0  253800.0  208800.0   \n",
       "4     87800.0  198500.0  362800.0  ...  211500.0  225700.0  229800.0   \n",
       "5     88800.0  190900.0  370300.0  ...  215300.0  230800.0  260100.0   \n",
       "..        ...       ...       ...  ...       ...       ...       ...   \n",
       "107  247400.0  445400.0  566400.0  ...  264100.0  370700.0  402700.0   \n",
       "108  251100.0  435500.0  577600.0  ...  265200.0  412500.0  454400.0   \n",
       "109  254800.0  435100.0  580100.0  ...  272300.0  408900.0  499200.0   \n",
       "110  263500.0  436900.0  575700.0  ...  248800.0  409000.0  513400.0   \n",
       "111  270500.0  442700.0  586100.0  ...  235100.0  379500.0  487200.0   \n",
       "\n",
       "        76262     85641     34228     18938     95223     19047     60081  \n",
       "1    128400.0  204100.0  432300.0  528100.0  266300.0  272300.0  203600.0  \n",
       "2    148400.0  194000.0  476900.0  482600.0  244200.0  291500.0  175600.0  \n",
       "3    150500.0  192400.0  440900.0  454800.0  217800.0  305600.0  170200.0  \n",
       "4    161600.0  185900.0  457500.0  463300.0  191200.0  342300.0  184800.0  \n",
       "5    131500.0  175900.0  470600.0  475400.0  201500.0  342600.0  198900.0  \n",
       "..        ...       ...       ...       ...       ...       ...       ...  \n",
       "107  343700.0  297200.0  557900.0  530300.0  279400.0  396600.0  250200.0  \n",
       "108  321500.0  292100.0  577100.0  579600.0  287100.0  398200.0  270300.0  \n",
       "109  273700.0  275100.0  557900.0  539400.0  305300.0  404100.0  260200.0  \n",
       "110  314400.0  265400.0  565600.0  609700.0  325600.0  397100.0  224300.0  \n",
       "111  319400.0  269300.0  579700.0  608700.0  345100.0  416200.0  216600.0  \n",
       "\n",
       "[111 rows x 939 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# recreate transposed dataset\n",
    "time_df = time_df.drop(columns=[\"RegionID\", \"City\", \"State\", \"Latitude\", \"Longitude\", \"RegionName\", \"StateName\", \"SizeRank\"])\n",
    "time_transposed = time_df.transpose()\n",
    "time_transposed.reset_index(drop = False, inplace = True)\n",
    "time_transposed = time_transposed.rename(columns=time_transposed.iloc[0])\n",
    "time_transposed = time_transposed.drop([0, 0])\n",
    "time_transposed = time_transposed.rename(columns={\"Zip Code\": 'DATE'})\n",
    "time_transposed['DATE'] = pd.to_datetime(time_transposed['DATE'], infer_datetime_format=True)\n",
    "time_transposed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "985a02fd",
   "metadata": {},
   "source": [
    "### LOAD/CLEAN INFLATION DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "ae341e3b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DATE        datetime64[ns]\n",
      "CPIAUCNS           float64\n",
      "dtype: object\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>DATE</th>\n",
       "      <th>CPIAUCNS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1913-01-01</td>\n",
       "      <td>9.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1913-02-01</td>\n",
       "      <td>9.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1913-03-01</td>\n",
       "      <td>9.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1913-04-01</td>\n",
       "      <td>9.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1913-05-01</td>\n",
       "      <td>9.700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1304</th>\n",
       "      <td>2021-09-01</td>\n",
       "      <td>274.310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1305</th>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>276.589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1306</th>\n",
       "      <td>2021-11-01</td>\n",
       "      <td>277.948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1307</th>\n",
       "      <td>2021-12-01</td>\n",
       "      <td>278.802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1308</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>281.148</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1309 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           DATE  CPIAUCNS\n",
       "0    1913-01-01     9.800\n",
       "1    1913-02-01     9.800\n",
       "2    1913-03-01     9.800\n",
       "3    1913-04-01     9.800\n",
       "4    1913-05-01     9.700\n",
       "...         ...       ...\n",
       "1304 2021-09-01   274.310\n",
       "1305 2021-10-01   276.589\n",
       "1306 2021-11-01   277.948\n",
       "1307 2021-12-01   278.802\n",
       "1308 2022-01-01   281.148\n",
       "\n",
       "[1309 rows x 2 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file_path = Path(\"./resources/CPIAUCNS/CPIAUCNS.csv\")\n",
    "cpi_df = pd.read_csv(file_path)\n",
    "cpi_df['DATE'] = pd.to_datetime(cpi_df['DATE'], infer_datetime_format=True)\n",
    "print(cpi_df.dtypes)\n",
    "cpi_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "aec1f7ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "# export inflation data for use with SQL\n",
    "cpi_df.to_csv(\"./resources/CPIAUCNS/cpi_sql.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "9f7b6a1d",
   "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>DATE</th>\n",
       "      <th>INFLATION</th>\n",
       "      <th>10001</th>\n",
       "      <th>60601</th>\n",
       "      <th>89101</th>\n",
       "      <th>75201</th>\n",
       "      <th>43085</th>\n",
       "      <th>40202</th>\n",
       "      <th>32801</th>\n",
       "      <th>80202</th>\n",
       "      <th>...</th>\n",
       "      <th>19468</th>\n",
       "      <th>32550</th>\n",
       "      <th>96738</th>\n",
       "      <th>76262</th>\n",
       "      <th>85641</th>\n",
       "      <th>34228</th>\n",
       "      <th>18938</th>\n",
       "      <th>95223</th>\n",
       "      <th>19047</th>\n",
       "      <th>60081</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>220.223</td>\n",
       "      <td>462700.0</td>\n",
       "      <td>188500.0</td>\n",
       "      <td>115600.0</td>\n",
       "      <td>191900.0</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>125900.0</td>\n",
       "      <td>82000.0</td>\n",
       "      <td>201400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>229700.0</td>\n",
       "      <td>284400.0</td>\n",
       "      <td>237800.0</td>\n",
       "      <td>128400.0</td>\n",
       "      <td>204100.0</td>\n",
       "      <td>432300.0</td>\n",
       "      <td>528100.0</td>\n",
       "      <td>266300.0</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>203600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>221.309</td>\n",
       "      <td>466200.0</td>\n",
       "      <td>189400.0</td>\n",
       "      <td>114000.0</td>\n",
       "      <td>184500.0</td>\n",
       "      <td>86000.0</td>\n",
       "      <td>124000.0</td>\n",
       "      <td>82800.0</td>\n",
       "      <td>205300.0</td>\n",
       "      <td>...</td>\n",
       "      <td>216700.0</td>\n",
       "      <td>258100.0</td>\n",
       "      <td>216000.0</td>\n",
       "      <td>148400.0</td>\n",
       "      <td>194000.0</td>\n",
       "      <td>476900.0</td>\n",
       "      <td>482600.0</td>\n",
       "      <td>244200.0</td>\n",
       "      <td>291500.0</td>\n",
       "      <td>175600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>223.467</td>\n",
       "      <td>465600.0</td>\n",
       "      <td>186300.0</td>\n",
       "      <td>110800.0</td>\n",
       "      <td>174500.0</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>118300.0</td>\n",
       "      <td>85500.0</td>\n",
       "      <td>198800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>199800.0</td>\n",
       "      <td>253800.0</td>\n",
       "      <td>208800.0</td>\n",
       "      <td>150500.0</td>\n",
       "      <td>192400.0</td>\n",
       "      <td>440900.0</td>\n",
       "      <td>454800.0</td>\n",
       "      <td>217800.0</td>\n",
       "      <td>305600.0</td>\n",
       "      <td>170200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-04-01</td>\n",
       "      <td>224.906</td>\n",
       "      <td>469500.0</td>\n",
       "      <td>177700.0</td>\n",
       "      <td>109600.0</td>\n",
       "      <td>152000.0</td>\n",
       "      <td>79300.0</td>\n",
       "      <td>117600.0</td>\n",
       "      <td>87800.0</td>\n",
       "      <td>198500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>211500.0</td>\n",
       "      <td>225700.0</td>\n",
       "      <td>229800.0</td>\n",
       "      <td>161600.0</td>\n",
       "      <td>185900.0</td>\n",
       "      <td>457500.0</td>\n",
       "      <td>463300.0</td>\n",
       "      <td>191200.0</td>\n",
       "      <td>342300.0</td>\n",
       "      <td>184800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-05-01</td>\n",
       "      <td>225.964</td>\n",
       "      <td>471300.0</td>\n",
       "      <td>167900.0</td>\n",
       "      <td>108200.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>78600.0</td>\n",
       "      <td>113800.0</td>\n",
       "      <td>88800.0</td>\n",
       "      <td>190900.0</td>\n",
       "      <td>...</td>\n",
       "      <td>215300.0</td>\n",
       "      <td>230800.0</td>\n",
       "      <td>260100.0</td>\n",
       "      <td>131500.0</td>\n",
       "      <td>175900.0</td>\n",
       "      <td>470600.0</td>\n",
       "      <td>475400.0</td>\n",
       "      <td>201500.0</td>\n",
       "      <td>342600.0</td>\n",
       "      <td>198900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>257.208</td>\n",
       "      <td>571500.0</td>\n",
       "      <td>264300.0</td>\n",
       "      <td>274400.0</td>\n",
       "      <td>316900.0</td>\n",
       "      <td>166600.0</td>\n",
       "      <td>174600.0</td>\n",
       "      <td>247400.0</td>\n",
       "      <td>445400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>264100.0</td>\n",
       "      <td>370700.0</td>\n",
       "      <td>402700.0</td>\n",
       "      <td>343700.0</td>\n",
       "      <td>297200.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>530300.0</td>\n",
       "      <td>279400.0</td>\n",
       "      <td>396600.0</td>\n",
       "      <td>250200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>256.974</td>\n",
       "      <td>575100.0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>277500.0</td>\n",
       "      <td>307100.0</td>\n",
       "      <td>172700.0</td>\n",
       "      <td>176600.0</td>\n",
       "      <td>251100.0</td>\n",
       "      <td>435500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>265200.0</td>\n",
       "      <td>412500.0</td>\n",
       "      <td>454400.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>292100.0</td>\n",
       "      <td>577100.0</td>\n",
       "      <td>579600.0</td>\n",
       "      <td>287100.0</td>\n",
       "      <td>398200.0</td>\n",
       "      <td>270300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>257.971</td>\n",
       "      <td>571700.0</td>\n",
       "      <td>281400.0</td>\n",
       "      <td>278900.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>175300.0</td>\n",
       "      <td>170800.0</td>\n",
       "      <td>254800.0</td>\n",
       "      <td>435100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>408900.0</td>\n",
       "      <td>499200.0</td>\n",
       "      <td>273700.0</td>\n",
       "      <td>275100.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>539400.0</td>\n",
       "      <td>305300.0</td>\n",
       "      <td>404100.0</td>\n",
       "      <td>260200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>258.678</td>\n",
       "      <td>568300.0</td>\n",
       "      <td>302900.0</td>\n",
       "      <td>283000.0</td>\n",
       "      <td>315500.0</td>\n",
       "      <td>173600.0</td>\n",
       "      <td>173100.0</td>\n",
       "      <td>263500.0</td>\n",
       "      <td>436900.0</td>\n",
       "      <td>...</td>\n",
       "      <td>248800.0</td>\n",
       "      <td>409000.0</td>\n",
       "      <td>513400.0</td>\n",
       "      <td>314400.0</td>\n",
       "      <td>265400.0</td>\n",
       "      <td>565600.0</td>\n",
       "      <td>609700.0</td>\n",
       "      <td>325600.0</td>\n",
       "      <td>397100.0</td>\n",
       "      <td>224300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>258.115</td>\n",
       "      <td>573600.0</td>\n",
       "      <td>309200.0</td>\n",
       "      <td>284700.0</td>\n",
       "      <td>321700.0</td>\n",
       "      <td>177600.0</td>\n",
       "      <td>176200.0</td>\n",
       "      <td>270500.0</td>\n",
       "      <td>442700.0</td>\n",
       "      <td>...</td>\n",
       "      <td>235100.0</td>\n",
       "      <td>379500.0</td>\n",
       "      <td>487200.0</td>\n",
       "      <td>319400.0</td>\n",
       "      <td>269300.0</td>\n",
       "      <td>579700.0</td>\n",
       "      <td>608700.0</td>\n",
       "      <td>345100.0</td>\n",
       "      <td>416200.0</td>\n",
       "      <td>216600.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>111 rows × 940 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          DATE  INFLATION     10001     60601     89101     75201     43085  \\\n",
       "0   2011-01-01    220.223  462700.0  188500.0  115600.0  191900.0   89100.0   \n",
       "1   2011-02-01    221.309  466200.0  189400.0  114000.0  184500.0   86000.0   \n",
       "2   2011-03-01    223.467  465600.0  186300.0  110800.0  174500.0   79800.0   \n",
       "3   2011-04-01    224.906  469500.0  177700.0  109600.0  152000.0   79300.0   \n",
       "4   2011-05-01    225.964  471300.0  167900.0  108200.0  162900.0   78600.0   \n",
       "..         ...        ...       ...       ...       ...       ...       ...   \n",
       "106 2019-11-01    257.208  571500.0  264300.0  274400.0  316900.0  166600.0   \n",
       "107 2019-12-01    256.974  575100.0  270000.0  277500.0  307100.0  172700.0   \n",
       "108 2020-01-01    257.971  571700.0  281400.0  278900.0  321500.0  175300.0   \n",
       "109 2020-02-01    258.678  568300.0  302900.0  283000.0  315500.0  173600.0   \n",
       "110 2020-03-01    258.115  573600.0  309200.0  284700.0  321700.0  177600.0   \n",
       "\n",
       "        40202     32801     80202  ...     19468     32550     96738  \\\n",
       "0    125900.0   82000.0  201400.0  ...  229700.0  284400.0  237800.0   \n",
       "1    124000.0   82800.0  205300.0  ...  216700.0  258100.0  216000.0   \n",
       "2    118300.0   85500.0  198800.0  ...  199800.0  253800.0  208800.0   \n",
       "3    117600.0   87800.0  198500.0  ...  211500.0  225700.0  229800.0   \n",
       "4    113800.0   88800.0  190900.0  ...  215300.0  230800.0  260100.0   \n",
       "..        ...       ...       ...  ...       ...       ...       ...   \n",
       "106  174600.0  247400.0  445400.0  ...  264100.0  370700.0  402700.0   \n",
       "107  176600.0  251100.0  435500.0  ...  265200.0  412500.0  454400.0   \n",
       "108  170800.0  254800.0  435100.0  ...  272300.0  408900.0  499200.0   \n",
       "109  173100.0  263500.0  436900.0  ...  248800.0  409000.0  513400.0   \n",
       "110  176200.0  270500.0  442700.0  ...  235100.0  379500.0  487200.0   \n",
       "\n",
       "        76262     85641     34228     18938     95223     19047     60081  \n",
       "0    128400.0  204100.0  432300.0  528100.0  266300.0  272300.0  203600.0  \n",
       "1    148400.0  194000.0  476900.0  482600.0  244200.0  291500.0  175600.0  \n",
       "2    150500.0  192400.0  440900.0  454800.0  217800.0  305600.0  170200.0  \n",
       "3    161600.0  185900.0  457500.0  463300.0  191200.0  342300.0  184800.0  \n",
       "4    131500.0  175900.0  470600.0  475400.0  201500.0  342600.0  198900.0  \n",
       "..        ...       ...       ...       ...       ...       ...       ...  \n",
       "106  343700.0  297200.0  557900.0  530300.0  279400.0  396600.0  250200.0  \n",
       "107  321500.0  292100.0  577100.0  579600.0  287100.0  398200.0  270300.0  \n",
       "108  273700.0  275100.0  557900.0  539400.0  305300.0  404100.0  260200.0  \n",
       "109  314400.0  265400.0  565600.0  609700.0  325600.0  397100.0  224300.0  \n",
       "110  319400.0  269300.0  579700.0  608700.0  345100.0  416200.0  216600.0  \n",
       "\n",
       "[111 rows x 940 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge inflation data into transposed dataset\n",
    "time_transposed = time_transposed.merge(cpi_df, how='inner', on='DATE')\n",
    "inf = time_transposed['CPIAUCNS']\n",
    "time_transposed = time_transposed.drop(columns=['CPIAUCNS'])\n",
    "time_transposed.insert(loc=1, column='INFLATION', value=inf)\n",
    "time_transposed = time_transposed.dropna()\n",
    "time_transposed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ab7ca98",
   "metadata": {},
   "source": [
    "### LOAD/CLEAN INTEREST RATE DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "7c170b6d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DATE        datetime64[ns]\n",
      "FEDFUNDS           float64\n",
      "dtype: object\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>DATE</th>\n",
       "      <th>FEDFUNDS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1954-07-01</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1954-08-01</td>\n",
       "      <td>1.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1954-09-01</td>\n",
       "      <td>1.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1954-10-01</td>\n",
       "      <td>0.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1954-11-01</td>\n",
       "      <td>0.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>807</th>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808</th>\n",
       "      <td>2021-11-01</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>809</th>\n",
       "      <td>2021-12-01</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>810</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>811</th>\n",
       "      <td>2022-02-01</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>812 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          DATE  FEDFUNDS\n",
       "0   1954-07-01      0.80\n",
       "1   1954-08-01      1.22\n",
       "2   1954-09-01      1.07\n",
       "3   1954-10-01      0.85\n",
       "4   1954-11-01      0.83\n",
       "..         ...       ...\n",
       "807 2021-10-01      0.08\n",
       "808 2021-11-01      0.08\n",
       "809 2021-12-01      0.08\n",
       "810 2022-01-01      0.08\n",
       "811 2022-02-01      0.08\n",
       "\n",
       "[812 rows x 2 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "interest_file = Path(\"./resources/fedfunds/FEDFUNDS.csv\")\n",
    "interest_df = pd.read_csv(interest_file)\n",
    "interest_df['DATE'] = pd.to_datetime(interest_df['DATE'], infer_datetime_format=True)\n",
    "print(interest_df.dtypes)\n",
    "interest_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "b9bb88d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# export interest data for use with SQL\n",
    "interest_df.to_csv(\"./resources/fedfunds/interest_sql.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "0500044b",
   "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>DATE</th>\n",
       "      <th>INTEREST_RATES</th>\n",
       "      <th>INFLATION</th>\n",
       "      <th>10001</th>\n",
       "      <th>60601</th>\n",
       "      <th>89101</th>\n",
       "      <th>75201</th>\n",
       "      <th>43085</th>\n",
       "      <th>40202</th>\n",
       "      <th>32801</th>\n",
       "      <th>...</th>\n",
       "      <th>19468</th>\n",
       "      <th>32550</th>\n",
       "      <th>96738</th>\n",
       "      <th>76262</th>\n",
       "      <th>85641</th>\n",
       "      <th>34228</th>\n",
       "      <th>18938</th>\n",
       "      <th>95223</th>\n",
       "      <th>19047</th>\n",
       "      <th>60081</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>0.17</td>\n",
       "      <td>220.223</td>\n",
       "      <td>462700.0</td>\n",
       "      <td>188500.0</td>\n",
       "      <td>115600.0</td>\n",
       "      <td>191900.0</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>125900.0</td>\n",
       "      <td>82000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>229700.0</td>\n",
       "      <td>284400.0</td>\n",
       "      <td>237800.0</td>\n",
       "      <td>128400.0</td>\n",
       "      <td>204100.0</td>\n",
       "      <td>432300.0</td>\n",
       "      <td>528100.0</td>\n",
       "      <td>266300.0</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>203600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>0.16</td>\n",
       "      <td>221.309</td>\n",
       "      <td>466200.0</td>\n",
       "      <td>189400.0</td>\n",
       "      <td>114000.0</td>\n",
       "      <td>184500.0</td>\n",
       "      <td>86000.0</td>\n",
       "      <td>124000.0</td>\n",
       "      <td>82800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>216700.0</td>\n",
       "      <td>258100.0</td>\n",
       "      <td>216000.0</td>\n",
       "      <td>148400.0</td>\n",
       "      <td>194000.0</td>\n",
       "      <td>476900.0</td>\n",
       "      <td>482600.0</td>\n",
       "      <td>244200.0</td>\n",
       "      <td>291500.0</td>\n",
       "      <td>175600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>0.14</td>\n",
       "      <td>223.467</td>\n",
       "      <td>465600.0</td>\n",
       "      <td>186300.0</td>\n",
       "      <td>110800.0</td>\n",
       "      <td>174500.0</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>118300.0</td>\n",
       "      <td>85500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>199800.0</td>\n",
       "      <td>253800.0</td>\n",
       "      <td>208800.0</td>\n",
       "      <td>150500.0</td>\n",
       "      <td>192400.0</td>\n",
       "      <td>440900.0</td>\n",
       "      <td>454800.0</td>\n",
       "      <td>217800.0</td>\n",
       "      <td>305600.0</td>\n",
       "      <td>170200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-04-01</td>\n",
       "      <td>0.10</td>\n",
       "      <td>224.906</td>\n",
       "      <td>469500.0</td>\n",
       "      <td>177700.0</td>\n",
       "      <td>109600.0</td>\n",
       "      <td>152000.0</td>\n",
       "      <td>79300.0</td>\n",
       "      <td>117600.0</td>\n",
       "      <td>87800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>211500.0</td>\n",
       "      <td>225700.0</td>\n",
       "      <td>229800.0</td>\n",
       "      <td>161600.0</td>\n",
       "      <td>185900.0</td>\n",
       "      <td>457500.0</td>\n",
       "      <td>463300.0</td>\n",
       "      <td>191200.0</td>\n",
       "      <td>342300.0</td>\n",
       "      <td>184800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-05-01</td>\n",
       "      <td>0.09</td>\n",
       "      <td>225.964</td>\n",
       "      <td>471300.0</td>\n",
       "      <td>167900.0</td>\n",
       "      <td>108200.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>78600.0</td>\n",
       "      <td>113800.0</td>\n",
       "      <td>88800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>215300.0</td>\n",
       "      <td>230800.0</td>\n",
       "      <td>260100.0</td>\n",
       "      <td>131500.0</td>\n",
       "      <td>175900.0</td>\n",
       "      <td>470600.0</td>\n",
       "      <td>475400.0</td>\n",
       "      <td>201500.0</td>\n",
       "      <td>342600.0</td>\n",
       "      <td>198900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>1.55</td>\n",
       "      <td>257.208</td>\n",
       "      <td>571500.0</td>\n",
       "      <td>264300.0</td>\n",
       "      <td>274400.0</td>\n",
       "      <td>316900.0</td>\n",
       "      <td>166600.0</td>\n",
       "      <td>174600.0</td>\n",
       "      <td>247400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>264100.0</td>\n",
       "      <td>370700.0</td>\n",
       "      <td>402700.0</td>\n",
       "      <td>343700.0</td>\n",
       "      <td>297200.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>530300.0</td>\n",
       "      <td>279400.0</td>\n",
       "      <td>396600.0</td>\n",
       "      <td>250200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>1.55</td>\n",
       "      <td>256.974</td>\n",
       "      <td>575100.0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>277500.0</td>\n",
       "      <td>307100.0</td>\n",
       "      <td>172700.0</td>\n",
       "      <td>176600.0</td>\n",
       "      <td>251100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>265200.0</td>\n",
       "      <td>412500.0</td>\n",
       "      <td>454400.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>292100.0</td>\n",
       "      <td>577100.0</td>\n",
       "      <td>579600.0</td>\n",
       "      <td>287100.0</td>\n",
       "      <td>398200.0</td>\n",
       "      <td>270300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1.55</td>\n",
       "      <td>257.971</td>\n",
       "      <td>571700.0</td>\n",
       "      <td>281400.0</td>\n",
       "      <td>278900.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>175300.0</td>\n",
       "      <td>170800.0</td>\n",
       "      <td>254800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>408900.0</td>\n",
       "      <td>499200.0</td>\n",
       "      <td>273700.0</td>\n",
       "      <td>275100.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>539400.0</td>\n",
       "      <td>305300.0</td>\n",
       "      <td>404100.0</td>\n",
       "      <td>260200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>1.58</td>\n",
       "      <td>258.678</td>\n",
       "      <td>568300.0</td>\n",
       "      <td>302900.0</td>\n",
       "      <td>283000.0</td>\n",
       "      <td>315500.0</td>\n",
       "      <td>173600.0</td>\n",
       "      <td>173100.0</td>\n",
       "      <td>263500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>248800.0</td>\n",
       "      <td>409000.0</td>\n",
       "      <td>513400.0</td>\n",
       "      <td>314400.0</td>\n",
       "      <td>265400.0</td>\n",
       "      <td>565600.0</td>\n",
       "      <td>609700.0</td>\n",
       "      <td>325600.0</td>\n",
       "      <td>397100.0</td>\n",
       "      <td>224300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>0.65</td>\n",
       "      <td>258.115</td>\n",
       "      <td>573600.0</td>\n",
       "      <td>309200.0</td>\n",
       "      <td>284700.0</td>\n",
       "      <td>321700.0</td>\n",
       "      <td>177600.0</td>\n",
       "      <td>176200.0</td>\n",
       "      <td>270500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>235100.0</td>\n",
       "      <td>379500.0</td>\n",
       "      <td>487200.0</td>\n",
       "      <td>319400.0</td>\n",
       "      <td>269300.0</td>\n",
       "      <td>579700.0</td>\n",
       "      <td>608700.0</td>\n",
       "      <td>345100.0</td>\n",
       "      <td>416200.0</td>\n",
       "      <td>216600.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>111 rows × 941 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          DATE  INTEREST_RATES  INFLATION     10001     60601     89101  \\\n",
       "0   2011-01-01            0.17    220.223  462700.0  188500.0  115600.0   \n",
       "1   2011-02-01            0.16    221.309  466200.0  189400.0  114000.0   \n",
       "2   2011-03-01            0.14    223.467  465600.0  186300.0  110800.0   \n",
       "3   2011-04-01            0.10    224.906  469500.0  177700.0  109600.0   \n",
       "4   2011-05-01            0.09    225.964  471300.0  167900.0  108200.0   \n",
       "..         ...             ...        ...       ...       ...       ...   \n",
       "106 2019-11-01            1.55    257.208  571500.0  264300.0  274400.0   \n",
       "107 2019-12-01            1.55    256.974  575100.0  270000.0  277500.0   \n",
       "108 2020-01-01            1.55    257.971  571700.0  281400.0  278900.0   \n",
       "109 2020-02-01            1.58    258.678  568300.0  302900.0  283000.0   \n",
       "110 2020-03-01            0.65    258.115  573600.0  309200.0  284700.0   \n",
       "\n",
       "        75201     43085     40202     32801  ...     19468     32550  \\\n",
       "0    191900.0   89100.0  125900.0   82000.0  ...  229700.0  284400.0   \n",
       "1    184500.0   86000.0  124000.0   82800.0  ...  216700.0  258100.0   \n",
       "2    174500.0   79800.0  118300.0   85500.0  ...  199800.0  253800.0   \n",
       "3    152000.0   79300.0  117600.0   87800.0  ...  211500.0  225700.0   \n",
       "4    162900.0   78600.0  113800.0   88800.0  ...  215300.0  230800.0   \n",
       "..        ...       ...       ...       ...  ...       ...       ...   \n",
       "106  316900.0  166600.0  174600.0  247400.0  ...  264100.0  370700.0   \n",
       "107  307100.0  172700.0  176600.0  251100.0  ...  265200.0  412500.0   \n",
       "108  321500.0  175300.0  170800.0  254800.0  ...  272300.0  408900.0   \n",
       "109  315500.0  173600.0  173100.0  263500.0  ...  248800.0  409000.0   \n",
       "110  321700.0  177600.0  176200.0  270500.0  ...  235100.0  379500.0   \n",
       "\n",
       "        96738     76262     85641     34228     18938     95223     19047  \\\n",
       "0    237800.0  128400.0  204100.0  432300.0  528100.0  266300.0  272300.0   \n",
       "1    216000.0  148400.0  194000.0  476900.0  482600.0  244200.0  291500.0   \n",
       "2    208800.0  150500.0  192400.0  440900.0  454800.0  217800.0  305600.0   \n",
       "3    229800.0  161600.0  185900.0  457500.0  463300.0  191200.0  342300.0   \n",
       "4    260100.0  131500.0  175900.0  470600.0  475400.0  201500.0  342600.0   \n",
       "..        ...       ...       ...       ...       ...       ...       ...   \n",
       "106  402700.0  343700.0  297200.0  557900.0  530300.0  279400.0  396600.0   \n",
       "107  454400.0  321500.0  292100.0  577100.0  579600.0  287100.0  398200.0   \n",
       "108  499200.0  273700.0  275100.0  557900.0  539400.0  305300.0  404100.0   \n",
       "109  513400.0  314400.0  265400.0  565600.0  609700.0  325600.0  397100.0   \n",
       "110  487200.0  319400.0  269300.0  579700.0  608700.0  345100.0  416200.0   \n",
       "\n",
       "        60081  \n",
       "0    203600.0  \n",
       "1    175600.0  \n",
       "2    170200.0  \n",
       "3    184800.0  \n",
       "4    198900.0  \n",
       "..        ...  \n",
       "106  250200.0  \n",
       "107  270300.0  \n",
       "108  260200.0  \n",
       "109  224300.0  \n",
       "110  216600.0  \n",
       "\n",
       "[111 rows x 941 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge interest data into transposed dataset\n",
    "time_transposed = time_transposed.merge(interest_df, how='inner', on='DATE')\n",
    "interest = time_transposed['FEDFUNDS']\n",
    "time_transposed = time_transposed.drop(columns=['FEDFUNDS'])\n",
    "time_transposed.insert(loc=1, column='INTEREST_RATES', value=interest)\n",
    "time_transposed = time_transposed.dropna()\n",
    "time_transposed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b93e95cd",
   "metadata": {},
   "source": [
    "### LOAD/CLEAN MORTGAGE RATE DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "4b2e2e45",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DATE                 datetime64[ns]\n",
      "AVG_MORTGAGE_RATE           float64\n",
      "dtype: object\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>DATE</th>\n",
       "      <th>AVG_MORTGAGE_RATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1998-01-01</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1998-02-01</td>\n",
       "      <td>7.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1998-03-01</td>\n",
       "      <td>7.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>276</th>\n",
       "      <td>2020-11-01</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277</th>\n",
       "      <td>2020-12-01</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>279</th>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>3.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>281 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          DATE  AVG_MORTGAGE_RATE\n",
       "0          NaT                NaN\n",
       "1          NaT                NaN\n",
       "2   1998-01-01                7.3\n",
       "3   1998-02-01                7.2\n",
       "4   1998-03-01                7.2\n",
       "..         ...                ...\n",
       "276 2020-11-01                3.0\n",
       "277 2020-12-01                3.0\n",
       "278 2021-01-01                3.0\n",
       "279 2021-02-01                3.0\n",
       "280 2021-03-01                3.1\n",
       "\n",
       "[281 rows x 2 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mortgage_file = Path(\"./resources/mortgage_data_trimmed/Mortgage_data_trimmed.csv\")\n",
    "mortgage_df = pd.read_csv(mortgage_file)\n",
    "mortgage_df = mortgage_df.rename(columns={'Month': 'DATE', 'Average\\r\\nContract\\r\\nRate': 'AVG_MORTGAGE_RATE'})\n",
    "mortgage_df['DATE'] = pd.to_datetime(mortgage_df['DATE'], infer_datetime_format=True)\n",
    "print(mortgage_df.dtypes)\n",
    "mortgage_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "f56eeb12",
   "metadata": {},
   "outputs": [],
   "source": [
    "# export mortgage data for use with SQL\n",
    "mortgage_df.to_csv(\"./resources/mortgage_data_trimmed/mortgage_sql.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "c5ba1caf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export cleaned version of mortage data for use with SQL\n",
    "mortgage_df.to_csv(\"./resources/mortgage_data_trimmed/mortgage_data_cleaned.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "07dafaf4",
   "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>DATE</th>\n",
       "      <th>AVG_MORTGAGE_RATE</th>\n",
       "      <th>INTEREST_RATES</th>\n",
       "      <th>INFLATION</th>\n",
       "      <th>10001</th>\n",
       "      <th>60601</th>\n",
       "      <th>89101</th>\n",
       "      <th>75201</th>\n",
       "      <th>43085</th>\n",
       "      <th>40202</th>\n",
       "      <th>...</th>\n",
       "      <th>19468</th>\n",
       "      <th>32550</th>\n",
       "      <th>96738</th>\n",
       "      <th>76262</th>\n",
       "      <th>85641</th>\n",
       "      <th>34228</th>\n",
       "      <th>18938</th>\n",
       "      <th>95223</th>\n",
       "      <th>19047</th>\n",
       "      <th>60081</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0.17</td>\n",
       "      <td>220.223</td>\n",
       "      <td>462700.0</td>\n",
       "      <td>188500.0</td>\n",
       "      <td>115600.0</td>\n",
       "      <td>191900.0</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>125900.0</td>\n",
       "      <td>...</td>\n",
       "      <td>229700.0</td>\n",
       "      <td>284400.0</td>\n",
       "      <td>237800.0</td>\n",
       "      <td>128400.0</td>\n",
       "      <td>204100.0</td>\n",
       "      <td>432300.0</td>\n",
       "      <td>528100.0</td>\n",
       "      <td>266300.0</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>203600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>4.9</td>\n",
       "      <td>0.16</td>\n",
       "      <td>221.309</td>\n",
       "      <td>466200.0</td>\n",
       "      <td>189400.0</td>\n",
       "      <td>114000.0</td>\n",
       "      <td>184500.0</td>\n",
       "      <td>86000.0</td>\n",
       "      <td>124000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>216700.0</td>\n",
       "      <td>258100.0</td>\n",
       "      <td>216000.0</td>\n",
       "      <td>148400.0</td>\n",
       "      <td>194000.0</td>\n",
       "      <td>476900.0</td>\n",
       "      <td>482600.0</td>\n",
       "      <td>244200.0</td>\n",
       "      <td>291500.0</td>\n",
       "      <td>175600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.14</td>\n",
       "      <td>223.467</td>\n",
       "      <td>465600.0</td>\n",
       "      <td>186300.0</td>\n",
       "      <td>110800.0</td>\n",
       "      <td>174500.0</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>118300.0</td>\n",
       "      <td>...</td>\n",
       "      <td>199800.0</td>\n",
       "      <td>253800.0</td>\n",
       "      <td>208800.0</td>\n",
       "      <td>150500.0</td>\n",
       "      <td>192400.0</td>\n",
       "      <td>440900.0</td>\n",
       "      <td>454800.0</td>\n",
       "      <td>217800.0</td>\n",
       "      <td>305600.0</td>\n",
       "      <td>170200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-04-01</td>\n",
       "      <td>4.9</td>\n",
       "      <td>0.10</td>\n",
       "      <td>224.906</td>\n",
       "      <td>469500.0</td>\n",
       "      <td>177700.0</td>\n",
       "      <td>109600.0</td>\n",
       "      <td>152000.0</td>\n",
       "      <td>79300.0</td>\n",
       "      <td>117600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>211500.0</td>\n",
       "      <td>225700.0</td>\n",
       "      <td>229800.0</td>\n",
       "      <td>161600.0</td>\n",
       "      <td>185900.0</td>\n",
       "      <td>457500.0</td>\n",
       "      <td>463300.0</td>\n",
       "      <td>191200.0</td>\n",
       "      <td>342300.0</td>\n",
       "      <td>184800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-05-01</td>\n",
       "      <td>4.9</td>\n",
       "      <td>0.09</td>\n",
       "      <td>225.964</td>\n",
       "      <td>471300.0</td>\n",
       "      <td>167900.0</td>\n",
       "      <td>108200.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>78600.0</td>\n",
       "      <td>113800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>215300.0</td>\n",
       "      <td>230800.0</td>\n",
       "      <td>260100.0</td>\n",
       "      <td>131500.0</td>\n",
       "      <td>175900.0</td>\n",
       "      <td>470600.0</td>\n",
       "      <td>475400.0</td>\n",
       "      <td>201500.0</td>\n",
       "      <td>342600.0</td>\n",
       "      <td>198900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.55</td>\n",
       "      <td>257.208</td>\n",
       "      <td>571500.0</td>\n",
       "      <td>264300.0</td>\n",
       "      <td>274400.0</td>\n",
       "      <td>316900.0</td>\n",
       "      <td>166600.0</td>\n",
       "      <td>174600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>264100.0</td>\n",
       "      <td>370700.0</td>\n",
       "      <td>402700.0</td>\n",
       "      <td>343700.0</td>\n",
       "      <td>297200.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>530300.0</td>\n",
       "      <td>279400.0</td>\n",
       "      <td>396600.0</td>\n",
       "      <td>250200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.55</td>\n",
       "      <td>256.974</td>\n",
       "      <td>575100.0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>277500.0</td>\n",
       "      <td>307100.0</td>\n",
       "      <td>172700.0</td>\n",
       "      <td>176600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>265200.0</td>\n",
       "      <td>412500.0</td>\n",
       "      <td>454400.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>292100.0</td>\n",
       "      <td>577100.0</td>\n",
       "      <td>579600.0</td>\n",
       "      <td>287100.0</td>\n",
       "      <td>398200.0</td>\n",
       "      <td>270300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.55</td>\n",
       "      <td>257.971</td>\n",
       "      <td>571700.0</td>\n",
       "      <td>281400.0</td>\n",
       "      <td>278900.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>175300.0</td>\n",
       "      <td>170800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>408900.0</td>\n",
       "      <td>499200.0</td>\n",
       "      <td>273700.0</td>\n",
       "      <td>275100.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>539400.0</td>\n",
       "      <td>305300.0</td>\n",
       "      <td>404100.0</td>\n",
       "      <td>260200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.58</td>\n",
       "      <td>258.678</td>\n",
       "      <td>568300.0</td>\n",
       "      <td>302900.0</td>\n",
       "      <td>283000.0</td>\n",
       "      <td>315500.0</td>\n",
       "      <td>173600.0</td>\n",
       "      <td>173100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>248800.0</td>\n",
       "      <td>409000.0</td>\n",
       "      <td>513400.0</td>\n",
       "      <td>314400.0</td>\n",
       "      <td>265400.0</td>\n",
       "      <td>565600.0</td>\n",
       "      <td>609700.0</td>\n",
       "      <td>325600.0</td>\n",
       "      <td>397100.0</td>\n",
       "      <td>224300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>3.7</td>\n",
       "      <td>0.65</td>\n",
       "      <td>258.115</td>\n",
       "      <td>573600.0</td>\n",
       "      <td>309200.0</td>\n",
       "      <td>284700.0</td>\n",
       "      <td>321700.0</td>\n",
       "      <td>177600.0</td>\n",
       "      <td>176200.0</td>\n",
       "      <td>...</td>\n",
       "      <td>235100.0</td>\n",
       "      <td>379500.0</td>\n",
       "      <td>487200.0</td>\n",
       "      <td>319400.0</td>\n",
       "      <td>269300.0</td>\n",
       "      <td>579700.0</td>\n",
       "      <td>608700.0</td>\n",
       "      <td>345100.0</td>\n",
       "      <td>416200.0</td>\n",
       "      <td>216600.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>111 rows × 942 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          DATE  AVG_MORTGAGE_RATE  INTEREST_RATES  INFLATION     10001  \\\n",
       "0   2011-01-01                4.8            0.17    220.223  462700.0   \n",
       "1   2011-02-01                4.9            0.16    221.309  466200.0   \n",
       "2   2011-03-01                5.0            0.14    223.467  465600.0   \n",
       "3   2011-04-01                4.9            0.10    224.906  469500.0   \n",
       "4   2011-05-01                4.9            0.09    225.964  471300.0   \n",
       "..         ...                ...             ...        ...       ...   \n",
       "106 2019-11-01                4.1            1.55    257.208  571500.0   \n",
       "107 2019-12-01                4.1            1.55    256.974  575100.0   \n",
       "108 2020-01-01                4.1            1.55    257.971  571700.0   \n",
       "109 2020-02-01                4.0            1.58    258.678  568300.0   \n",
       "110 2020-03-01                3.7            0.65    258.115  573600.0   \n",
       "\n",
       "        60601     89101     75201     43085     40202  ...     19468  \\\n",
       "0    188500.0  115600.0  191900.0   89100.0  125900.0  ...  229700.0   \n",
       "1    189400.0  114000.0  184500.0   86000.0  124000.0  ...  216700.0   \n",
       "2    186300.0  110800.0  174500.0   79800.0  118300.0  ...  199800.0   \n",
       "3    177700.0  109600.0  152000.0   79300.0  117600.0  ...  211500.0   \n",
       "4    167900.0  108200.0  162900.0   78600.0  113800.0  ...  215300.0   \n",
       "..        ...       ...       ...       ...       ...  ...       ...   \n",
       "106  264300.0  274400.0  316900.0  166600.0  174600.0  ...  264100.0   \n",
       "107  270000.0  277500.0  307100.0  172700.0  176600.0  ...  265200.0   \n",
       "108  281400.0  278900.0  321500.0  175300.0  170800.0  ...  272300.0   \n",
       "109  302900.0  283000.0  315500.0  173600.0  173100.0  ...  248800.0   \n",
       "110  309200.0  284700.0  321700.0  177600.0  176200.0  ...  235100.0   \n",
       "\n",
       "        32550     96738     76262     85641     34228     18938     95223  \\\n",
       "0    284400.0  237800.0  128400.0  204100.0  432300.0  528100.0  266300.0   \n",
       "1    258100.0  216000.0  148400.0  194000.0  476900.0  482600.0  244200.0   \n",
       "2    253800.0  208800.0  150500.0  192400.0  440900.0  454800.0  217800.0   \n",
       "3    225700.0  229800.0  161600.0  185900.0  457500.0  463300.0  191200.0   \n",
       "4    230800.0  260100.0  131500.0  175900.0  470600.0  475400.0  201500.0   \n",
       "..        ...       ...       ...       ...       ...       ...       ...   \n",
       "106  370700.0  402700.0  343700.0  297200.0  557900.0  530300.0  279400.0   \n",
       "107  412500.0  454400.0  321500.0  292100.0  577100.0  579600.0  287100.0   \n",
       "108  408900.0  499200.0  273700.0  275100.0  557900.0  539400.0  305300.0   \n",
       "109  409000.0  513400.0  314400.0  265400.0  565600.0  609700.0  325600.0   \n",
       "110  379500.0  487200.0  319400.0  269300.0  579700.0  608700.0  345100.0   \n",
       "\n",
       "        19047     60081  \n",
       "0    272300.0  203600.0  \n",
       "1    291500.0  175600.0  \n",
       "2    305600.0  170200.0  \n",
       "3    342300.0  184800.0  \n",
       "4    342600.0  198900.0  \n",
       "..        ...       ...  \n",
       "106  396600.0  250200.0  \n",
       "107  398200.0  270300.0  \n",
       "108  404100.0  260200.0  \n",
       "109  397100.0  224300.0  \n",
       "110  416200.0  216600.0  \n",
       "\n",
       "[111 rows x 942 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge mortgage rate data into transposed dataset\n",
    "time_transposed = time_transposed.merge(mortgage_df, how='inner', on='DATE')\n",
    "mort = time_transposed['AVG_MORTGAGE_RATE']\n",
    "time_transposed = time_transposed.drop(columns=['AVG_MORTGAGE_RATE'])\n",
    "time_transposed.insert(loc=1, column='AVG_MORTGAGE_RATE', value=mort)\n",
    "time_transposed = time_transposed.dropna()\n",
    "time_transposed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46d11ed7",
   "metadata": {},
   "source": [
    "### LOAD/CLEAN UNEMPLOYMENT RATE DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "97265d98",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DATE                 datetime64[ns]\n",
      "UNEMPLOYMENT_RATE           float64\n",
      "dtype: object\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>DATE</th>\n",
       "      <th>UNEMPLOYMENT_RATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-11-01</td>\n",
       "      <td>9.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010-12-01</td>\n",
       "      <td>9.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>9.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>4.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>2021-11-01</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>2021-12-01</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>2022-02-01</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>136 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          DATE  UNEMPLOYMENT_RATE\n",
       "0   2010-11-01                9.8\n",
       "1   2010-12-01                9.3\n",
       "2   2011-01-01                9.1\n",
       "3   2011-02-01                9.0\n",
       "4   2011-03-01                9.0\n",
       "..         ...                ...\n",
       "131 2021-10-01                4.6\n",
       "132 2021-11-01                4.2\n",
       "133 2021-12-01                3.9\n",
       "134 2022-01-01                4.0\n",
       "135 2022-02-01                3.8\n",
       "\n",
       "[136 rows x 2 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unemployment_file = Path(\"./resources/unemployment_data/unemployment_data_usa_only.csv\")\n",
    "unemployment_df = pd.read_csv(unemployment_file)\n",
    "unemployment_df['TIME'] = pd.to_datetime(unemployment_df['TIME'], infer_datetime_format=True)\n",
    "unemployment_df = unemployment_df.rename(columns={'TIME': 'DATE', 'Value': 'UNEMPLOYMENT_RATE'})\n",
    "print(unemployment_df.dtypes)\n",
    "unemployment_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "9f3a2529",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export cleaned unemployment data for use with SQL\n",
    "unemployment_df.to_csv(\"./resources/unemployment_data/unemployment_data_sql.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "1eac2ac6",
   "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>DATE</th>\n",
       "      <th>UNEMPLOYMENT_RATE</th>\n",
       "      <th>AVG_MORTGAGE_RATE</th>\n",
       "      <th>INTEREST_RATES</th>\n",
       "      <th>INFLATION</th>\n",
       "      <th>10001</th>\n",
       "      <th>60601</th>\n",
       "      <th>89101</th>\n",
       "      <th>75201</th>\n",
       "      <th>43085</th>\n",
       "      <th>...</th>\n",
       "      <th>19468</th>\n",
       "      <th>32550</th>\n",
       "      <th>96738</th>\n",
       "      <th>76262</th>\n",
       "      <th>85641</th>\n",
       "      <th>34228</th>\n",
       "      <th>18938</th>\n",
       "      <th>95223</th>\n",
       "      <th>19047</th>\n",
       "      <th>60081</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>9.1</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0.17</td>\n",
       "      <td>220.223</td>\n",
       "      <td>462700.0</td>\n",
       "      <td>188500.0</td>\n",
       "      <td>115600.0</td>\n",
       "      <td>191900.0</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>229700.0</td>\n",
       "      <td>284400.0</td>\n",
       "      <td>237800.0</td>\n",
       "      <td>128400.0</td>\n",
       "      <td>204100.0</td>\n",
       "      <td>432300.0</td>\n",
       "      <td>528100.0</td>\n",
       "      <td>266300.0</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>203600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>0.16</td>\n",
       "      <td>221.309</td>\n",
       "      <td>466200.0</td>\n",
       "      <td>189400.0</td>\n",
       "      <td>114000.0</td>\n",
       "      <td>184500.0</td>\n",
       "      <td>86000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>216700.0</td>\n",
       "      <td>258100.0</td>\n",
       "      <td>216000.0</td>\n",
       "      <td>148400.0</td>\n",
       "      <td>194000.0</td>\n",
       "      <td>476900.0</td>\n",
       "      <td>482600.0</td>\n",
       "      <td>244200.0</td>\n",
       "      <td>291500.0</td>\n",
       "      <td>175600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.14</td>\n",
       "      <td>223.467</td>\n",
       "      <td>465600.0</td>\n",
       "      <td>186300.0</td>\n",
       "      <td>110800.0</td>\n",
       "      <td>174500.0</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>199800.0</td>\n",
       "      <td>253800.0</td>\n",
       "      <td>208800.0</td>\n",
       "      <td>150500.0</td>\n",
       "      <td>192400.0</td>\n",
       "      <td>440900.0</td>\n",
       "      <td>454800.0</td>\n",
       "      <td>217800.0</td>\n",
       "      <td>305600.0</td>\n",
       "      <td>170200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-04-01</td>\n",
       "      <td>9.1</td>\n",
       "      <td>4.9</td>\n",
       "      <td>0.10</td>\n",
       "      <td>224.906</td>\n",
       "      <td>469500.0</td>\n",
       "      <td>177700.0</td>\n",
       "      <td>109600.0</td>\n",
       "      <td>152000.0</td>\n",
       "      <td>79300.0</td>\n",
       "      <td>...</td>\n",
       "      <td>211500.0</td>\n",
       "      <td>225700.0</td>\n",
       "      <td>229800.0</td>\n",
       "      <td>161600.0</td>\n",
       "      <td>185900.0</td>\n",
       "      <td>457500.0</td>\n",
       "      <td>463300.0</td>\n",
       "      <td>191200.0</td>\n",
       "      <td>342300.0</td>\n",
       "      <td>184800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-05-01</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>0.09</td>\n",
       "      <td>225.964</td>\n",
       "      <td>471300.0</td>\n",
       "      <td>167900.0</td>\n",
       "      <td>108200.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>78600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>215300.0</td>\n",
       "      <td>230800.0</td>\n",
       "      <td>260100.0</td>\n",
       "      <td>131500.0</td>\n",
       "      <td>175900.0</td>\n",
       "      <td>470600.0</td>\n",
       "      <td>475400.0</td>\n",
       "      <td>201500.0</td>\n",
       "      <td>342600.0</td>\n",
       "      <td>198900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>3.6</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.55</td>\n",
       "      <td>257.208</td>\n",
       "      <td>571500.0</td>\n",
       "      <td>264300.0</td>\n",
       "      <td>274400.0</td>\n",
       "      <td>316900.0</td>\n",
       "      <td>166600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>264100.0</td>\n",
       "      <td>370700.0</td>\n",
       "      <td>402700.0</td>\n",
       "      <td>343700.0</td>\n",
       "      <td>297200.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>530300.0</td>\n",
       "      <td>279400.0</td>\n",
       "      <td>396600.0</td>\n",
       "      <td>250200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>3.6</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.55</td>\n",
       "      <td>256.974</td>\n",
       "      <td>575100.0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>277500.0</td>\n",
       "      <td>307100.0</td>\n",
       "      <td>172700.0</td>\n",
       "      <td>...</td>\n",
       "      <td>265200.0</td>\n",
       "      <td>412500.0</td>\n",
       "      <td>454400.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>292100.0</td>\n",
       "      <td>577100.0</td>\n",
       "      <td>579600.0</td>\n",
       "      <td>287100.0</td>\n",
       "      <td>398200.0</td>\n",
       "      <td>270300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>3.5</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.55</td>\n",
       "      <td>257.971</td>\n",
       "      <td>571700.0</td>\n",
       "      <td>281400.0</td>\n",
       "      <td>278900.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>175300.0</td>\n",
       "      <td>...</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>408900.0</td>\n",
       "      <td>499200.0</td>\n",
       "      <td>273700.0</td>\n",
       "      <td>275100.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>539400.0</td>\n",
       "      <td>305300.0</td>\n",
       "      <td>404100.0</td>\n",
       "      <td>260200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>3.5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.58</td>\n",
       "      <td>258.678</td>\n",
       "      <td>568300.0</td>\n",
       "      <td>302900.0</td>\n",
       "      <td>283000.0</td>\n",
       "      <td>315500.0</td>\n",
       "      <td>173600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>248800.0</td>\n",
       "      <td>409000.0</td>\n",
       "      <td>513400.0</td>\n",
       "      <td>314400.0</td>\n",
       "      <td>265400.0</td>\n",
       "      <td>565600.0</td>\n",
       "      <td>609700.0</td>\n",
       "      <td>325600.0</td>\n",
       "      <td>397100.0</td>\n",
       "      <td>224300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>4.4</td>\n",
       "      <td>3.7</td>\n",
       "      <td>0.65</td>\n",
       "      <td>258.115</td>\n",
       "      <td>573600.0</td>\n",
       "      <td>309200.0</td>\n",
       "      <td>284700.0</td>\n",
       "      <td>321700.0</td>\n",
       "      <td>177600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>235100.0</td>\n",
       "      <td>379500.0</td>\n",
       "      <td>487200.0</td>\n",
       "      <td>319400.0</td>\n",
       "      <td>269300.0</td>\n",
       "      <td>579700.0</td>\n",
       "      <td>608700.0</td>\n",
       "      <td>345100.0</td>\n",
       "      <td>416200.0</td>\n",
       "      <td>216600.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>111 rows × 943 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          DATE  UNEMPLOYMENT_RATE  AVG_MORTGAGE_RATE  INTEREST_RATES  \\\n",
       "0   2011-01-01                9.1                4.8            0.17   \n",
       "1   2011-02-01                9.0                4.9            0.16   \n",
       "2   2011-03-01                9.0                5.0            0.14   \n",
       "3   2011-04-01                9.1                4.9            0.10   \n",
       "4   2011-05-01                9.0                4.9            0.09   \n",
       "..         ...                ...                ...             ...   \n",
       "106 2019-11-01                3.6                4.1            1.55   \n",
       "107 2019-12-01                3.6                4.1            1.55   \n",
       "108 2020-01-01                3.5                4.1            1.55   \n",
       "109 2020-02-01                3.5                4.0            1.58   \n",
       "110 2020-03-01                4.4                3.7            0.65   \n",
       "\n",
       "     INFLATION     10001     60601     89101     75201     43085  ...  \\\n",
       "0      220.223  462700.0  188500.0  115600.0  191900.0   89100.0  ...   \n",
       "1      221.309  466200.0  189400.0  114000.0  184500.0   86000.0  ...   \n",
       "2      223.467  465600.0  186300.0  110800.0  174500.0   79800.0  ...   \n",
       "3      224.906  469500.0  177700.0  109600.0  152000.0   79300.0  ...   \n",
       "4      225.964  471300.0  167900.0  108200.0  162900.0   78600.0  ...   \n",
       "..         ...       ...       ...       ...       ...       ...  ...   \n",
       "106    257.208  571500.0  264300.0  274400.0  316900.0  166600.0  ...   \n",
       "107    256.974  575100.0  270000.0  277500.0  307100.0  172700.0  ...   \n",
       "108    257.971  571700.0  281400.0  278900.0  321500.0  175300.0  ...   \n",
       "109    258.678  568300.0  302900.0  283000.0  315500.0  173600.0  ...   \n",
       "110    258.115  573600.0  309200.0  284700.0  321700.0  177600.0  ...   \n",
       "\n",
       "        19468     32550     96738     76262     85641     34228     18938  \\\n",
       "0    229700.0  284400.0  237800.0  128400.0  204100.0  432300.0  528100.0   \n",
       "1    216700.0  258100.0  216000.0  148400.0  194000.0  476900.0  482600.0   \n",
       "2    199800.0  253800.0  208800.0  150500.0  192400.0  440900.0  454800.0   \n",
       "3    211500.0  225700.0  229800.0  161600.0  185900.0  457500.0  463300.0   \n",
       "4    215300.0  230800.0  260100.0  131500.0  175900.0  470600.0  475400.0   \n",
       "..        ...       ...       ...       ...       ...       ...       ...   \n",
       "106  264100.0  370700.0  402700.0  343700.0  297200.0  557900.0  530300.0   \n",
       "107  265200.0  412500.0  454400.0  321500.0  292100.0  577100.0  579600.0   \n",
       "108  272300.0  408900.0  499200.0  273700.0  275100.0  557900.0  539400.0   \n",
       "109  248800.0  409000.0  513400.0  314400.0  265400.0  565600.0  609700.0   \n",
       "110  235100.0  379500.0  487200.0  319400.0  269300.0  579700.0  608700.0   \n",
       "\n",
       "        95223     19047     60081  \n",
       "0    266300.0  272300.0  203600.0  \n",
       "1    244200.0  291500.0  175600.0  \n",
       "2    217800.0  305600.0  170200.0  \n",
       "3    191200.0  342300.0  184800.0  \n",
       "4    201500.0  342600.0  198900.0  \n",
       "..        ...       ...       ...  \n",
       "106  279400.0  396600.0  250200.0  \n",
       "107  287100.0  398200.0  270300.0  \n",
       "108  305300.0  404100.0  260200.0  \n",
       "109  325600.0  397100.0  224300.0  \n",
       "110  345100.0  416200.0  216600.0  \n",
       "\n",
       "[111 rows x 943 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge unemployment rate data into transposed dataset\n",
    "time_transposed = time_transposed.merge(unemployment_df, how='inner', on='DATE')\n",
    "unemp = time_transposed['UNEMPLOYMENT_RATE']\n",
    "time_transposed = time_transposed.drop(columns=['UNEMPLOYMENT_RATE'])\n",
    "time_transposed.insert(loc=1, column='UNEMPLOYMENT_RATE', value=unemp)\n",
    "time_transposed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "542d05a1",
   "metadata": {},
   "source": [
    "### LOAD/CLEAN GAS PRICE DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "77926850",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DATE                datetime64[ns]\n",
      "PRICE_PER_GALLON           float64\n",
      "dtype: object\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>DATE</th>\n",
       "      <th>PRICE_PER_GALLON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1993-04-01</td>\n",
       "      <td>1.078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1993-05-01</td>\n",
       "      <td>1.100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1993-06-01</td>\n",
       "      <td>1.097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1993-07-01</td>\n",
       "      <td>1.078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1993-08-01</td>\n",
       "      <td>1.062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342</th>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>3.384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343</th>\n",
       "      <td>2021-11-01</td>\n",
       "      <td>3.491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>344</th>\n",
       "      <td>2021-12-01</td>\n",
       "      <td>3.406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>345</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>3.413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>346</th>\n",
       "      <td>2022-02-01</td>\n",
       "      <td>3.611</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>347 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          DATE  PRICE_PER_GALLON\n",
       "0   1993-04-01             1.078\n",
       "1   1993-05-01             1.100\n",
       "2   1993-06-01             1.097\n",
       "3   1993-07-01             1.078\n",
       "4   1993-08-01             1.062\n",
       "..         ...               ...\n",
       "342 2021-10-01             3.384\n",
       "343 2021-11-01             3.491\n",
       "344 2021-12-01             3.406\n",
       "345 2022-01-01             3.413\n",
       "346 2022-02-01             3.611\n",
       "\n",
       "[347 rows x 2 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gas_file = Path('./resources/gas_price/gas_price.csv')\n",
    "gas_df = pd.read_csv(gas_file)\n",
    "gas_df['DATE'] = pd.to_datetime(gas_df['DATE'], infer_datetime_format=True)\n",
    "print(gas_df.dtypes)\n",
    "gas_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "1401dcfa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# export gas price data for use with SQL\n",
    "gas_df.to_csv(\"./resources/gas_price/gas_price_sql.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "ae19c7b1",
   "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>DATE</th>\n",
       "      <th>PRICE_PER_GALLON</th>\n",
       "      <th>UNEMPLOYMENT_RATE</th>\n",
       "      <th>AVG_MORTGAGE_RATE</th>\n",
       "      <th>INTEREST_RATES</th>\n",
       "      <th>INFLATION</th>\n",
       "      <th>10001</th>\n",
       "      <th>60601</th>\n",
       "      <th>89101</th>\n",
       "      <th>75201</th>\n",
       "      <th>...</th>\n",
       "      <th>19468</th>\n",
       "      <th>32550</th>\n",
       "      <th>96738</th>\n",
       "      <th>76262</th>\n",
       "      <th>85641</th>\n",
       "      <th>34228</th>\n",
       "      <th>18938</th>\n",
       "      <th>95223</th>\n",
       "      <th>19047</th>\n",
       "      <th>60081</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>3.148</td>\n",
       "      <td>9.1</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0.17</td>\n",
       "      <td>220.223</td>\n",
       "      <td>462700.0</td>\n",
       "      <td>188500.0</td>\n",
       "      <td>115600.0</td>\n",
       "      <td>191900.0</td>\n",
       "      <td>...</td>\n",
       "      <td>229700.0</td>\n",
       "      <td>284400.0</td>\n",
       "      <td>237800.0</td>\n",
       "      <td>128400.0</td>\n",
       "      <td>204100.0</td>\n",
       "      <td>432300.0</td>\n",
       "      <td>528100.0</td>\n",
       "      <td>266300.0</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>203600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>3.264</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>0.16</td>\n",
       "      <td>221.309</td>\n",
       "      <td>466200.0</td>\n",
       "      <td>189400.0</td>\n",
       "      <td>114000.0</td>\n",
       "      <td>184500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>216700.0</td>\n",
       "      <td>258100.0</td>\n",
       "      <td>216000.0</td>\n",
       "      <td>148400.0</td>\n",
       "      <td>194000.0</td>\n",
       "      <td>476900.0</td>\n",
       "      <td>482600.0</td>\n",
       "      <td>244200.0</td>\n",
       "      <td>291500.0</td>\n",
       "      <td>175600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>3.615</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.14</td>\n",
       "      <td>223.467</td>\n",
       "      <td>465600.0</td>\n",
       "      <td>186300.0</td>\n",
       "      <td>110800.0</td>\n",
       "      <td>174500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>199800.0</td>\n",
       "      <td>253800.0</td>\n",
       "      <td>208800.0</td>\n",
       "      <td>150500.0</td>\n",
       "      <td>192400.0</td>\n",
       "      <td>440900.0</td>\n",
       "      <td>454800.0</td>\n",
       "      <td>217800.0</td>\n",
       "      <td>305600.0</td>\n",
       "      <td>170200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-04-01</td>\n",
       "      <td>3.852</td>\n",
       "      <td>9.1</td>\n",
       "      <td>4.9</td>\n",
       "      <td>0.10</td>\n",
       "      <td>224.906</td>\n",
       "      <td>469500.0</td>\n",
       "      <td>177700.0</td>\n",
       "      <td>109600.0</td>\n",
       "      <td>152000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>211500.0</td>\n",
       "      <td>225700.0</td>\n",
       "      <td>229800.0</td>\n",
       "      <td>161600.0</td>\n",
       "      <td>185900.0</td>\n",
       "      <td>457500.0</td>\n",
       "      <td>463300.0</td>\n",
       "      <td>191200.0</td>\n",
       "      <td>342300.0</td>\n",
       "      <td>184800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-05-01</td>\n",
       "      <td>3.960</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>0.09</td>\n",
       "      <td>225.964</td>\n",
       "      <td>471300.0</td>\n",
       "      <td>167900.0</td>\n",
       "      <td>108200.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>...</td>\n",
       "      <td>215300.0</td>\n",
       "      <td>230800.0</td>\n",
       "      <td>260100.0</td>\n",
       "      <td>131500.0</td>\n",
       "      <td>175900.0</td>\n",
       "      <td>470600.0</td>\n",
       "      <td>475400.0</td>\n",
       "      <td>201500.0</td>\n",
       "      <td>342600.0</td>\n",
       "      <td>198900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>2.693</td>\n",
       "      <td>3.6</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.55</td>\n",
       "      <td>257.208</td>\n",
       "      <td>571500.0</td>\n",
       "      <td>264300.0</td>\n",
       "      <td>274400.0</td>\n",
       "      <td>316900.0</td>\n",
       "      <td>...</td>\n",
       "      <td>264100.0</td>\n",
       "      <td>370700.0</td>\n",
       "      <td>402700.0</td>\n",
       "      <td>343700.0</td>\n",
       "      <td>297200.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>530300.0</td>\n",
       "      <td>279400.0</td>\n",
       "      <td>396600.0</td>\n",
       "      <td>250200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>2.645</td>\n",
       "      <td>3.6</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.55</td>\n",
       "      <td>256.974</td>\n",
       "      <td>575100.0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>277500.0</td>\n",
       "      <td>307100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>265200.0</td>\n",
       "      <td>412500.0</td>\n",
       "      <td>454400.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>292100.0</td>\n",
       "      <td>577100.0</td>\n",
       "      <td>579600.0</td>\n",
       "      <td>287100.0</td>\n",
       "      <td>398200.0</td>\n",
       "      <td>270300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2.636</td>\n",
       "      <td>3.5</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.55</td>\n",
       "      <td>257.971</td>\n",
       "      <td>571700.0</td>\n",
       "      <td>281400.0</td>\n",
       "      <td>278900.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>408900.0</td>\n",
       "      <td>499200.0</td>\n",
       "      <td>273700.0</td>\n",
       "      <td>275100.0</td>\n",
       "      <td>557900.0</td>\n",
       "      <td>539400.0</td>\n",
       "      <td>305300.0</td>\n",
       "      <td>404100.0</td>\n",
       "      <td>260200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>2.533</td>\n",
       "      <td>3.5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.58</td>\n",
       "      <td>258.678</td>\n",
       "      <td>568300.0</td>\n",
       "      <td>302900.0</td>\n",
       "      <td>283000.0</td>\n",
       "      <td>315500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>248800.0</td>\n",
       "      <td>409000.0</td>\n",
       "      <td>513400.0</td>\n",
       "      <td>314400.0</td>\n",
       "      <td>265400.0</td>\n",
       "      <td>565600.0</td>\n",
       "      <td>609700.0</td>\n",
       "      <td>325600.0</td>\n",
       "      <td>397100.0</td>\n",
       "      <td>224300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>2.329</td>\n",
       "      <td>4.4</td>\n",
       "      <td>3.7</td>\n",
       "      <td>0.65</td>\n",
       "      <td>258.115</td>\n",
       "      <td>573600.0</td>\n",
       "      <td>309200.0</td>\n",
       "      <td>284700.0</td>\n",
       "      <td>321700.0</td>\n",
       "      <td>...</td>\n",
       "      <td>235100.0</td>\n",
       "      <td>379500.0</td>\n",
       "      <td>487200.0</td>\n",
       "      <td>319400.0</td>\n",
       "      <td>269300.0</td>\n",
       "      <td>579700.0</td>\n",
       "      <td>608700.0</td>\n",
       "      <td>345100.0</td>\n",
       "      <td>416200.0</td>\n",
       "      <td>216600.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>111 rows × 944 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          DATE  PRICE_PER_GALLON  UNEMPLOYMENT_RATE  AVG_MORTGAGE_RATE  \\\n",
       "0   2011-01-01             3.148                9.1                4.8   \n",
       "1   2011-02-01             3.264                9.0                4.9   \n",
       "2   2011-03-01             3.615                9.0                5.0   \n",
       "3   2011-04-01             3.852                9.1                4.9   \n",
       "4   2011-05-01             3.960                9.0                4.9   \n",
       "..         ...               ...                ...                ...   \n",
       "106 2019-11-01             2.693                3.6                4.1   \n",
       "107 2019-12-01             2.645                3.6                4.1   \n",
       "108 2020-01-01             2.636                3.5                4.1   \n",
       "109 2020-02-01             2.533                3.5                4.0   \n",
       "110 2020-03-01             2.329                4.4                3.7   \n",
       "\n",
       "     INTEREST_RATES  INFLATION     10001     60601     89101     75201  ...  \\\n",
       "0              0.17    220.223  462700.0  188500.0  115600.0  191900.0  ...   \n",
       "1              0.16    221.309  466200.0  189400.0  114000.0  184500.0  ...   \n",
       "2              0.14    223.467  465600.0  186300.0  110800.0  174500.0  ...   \n",
       "3              0.10    224.906  469500.0  177700.0  109600.0  152000.0  ...   \n",
       "4              0.09    225.964  471300.0  167900.0  108200.0  162900.0  ...   \n",
       "..              ...        ...       ...       ...       ...       ...  ...   \n",
       "106            1.55    257.208  571500.0  264300.0  274400.0  316900.0  ...   \n",
       "107            1.55    256.974  575100.0  270000.0  277500.0  307100.0  ...   \n",
       "108            1.55    257.971  571700.0  281400.0  278900.0  321500.0  ...   \n",
       "109            1.58    258.678  568300.0  302900.0  283000.0  315500.0  ...   \n",
       "110            0.65    258.115  573600.0  309200.0  284700.0  321700.0  ...   \n",
       "\n",
       "        19468     32550     96738     76262     85641     34228     18938  \\\n",
       "0    229700.0  284400.0  237800.0  128400.0  204100.0  432300.0  528100.0   \n",
       "1    216700.0  258100.0  216000.0  148400.0  194000.0  476900.0  482600.0   \n",
       "2    199800.0  253800.0  208800.0  150500.0  192400.0  440900.0  454800.0   \n",
       "3    211500.0  225700.0  229800.0  161600.0  185900.0  457500.0  463300.0   \n",
       "4    215300.0  230800.0  260100.0  131500.0  175900.0  470600.0  475400.0   \n",
       "..        ...       ...       ...       ...       ...       ...       ...   \n",
       "106  264100.0  370700.0  402700.0  343700.0  297200.0  557900.0  530300.0   \n",
       "107  265200.0  412500.0  454400.0  321500.0  292100.0  577100.0  579600.0   \n",
       "108  272300.0  408900.0  499200.0  273700.0  275100.0  557900.0  539400.0   \n",
       "109  248800.0  409000.0  513400.0  314400.0  265400.0  565600.0  609700.0   \n",
       "110  235100.0  379500.0  487200.0  319400.0  269300.0  579700.0  608700.0   \n",
       "\n",
       "        95223     19047     60081  \n",
       "0    266300.0  272300.0  203600.0  \n",
       "1    244200.0  291500.0  175600.0  \n",
       "2    217800.0  305600.0  170200.0  \n",
       "3    191200.0  342300.0  184800.0  \n",
       "4    201500.0  342600.0  198900.0  \n",
       "..        ...       ...       ...  \n",
       "106  279400.0  396600.0  250200.0  \n",
       "107  287100.0  398200.0  270300.0  \n",
       "108  305300.0  404100.0  260200.0  \n",
       "109  325600.0  397100.0  224300.0  \n",
       "110  345100.0  416200.0  216600.0  \n",
       "\n",
       "[111 rows x 944 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add gas price data to our master datasource\n",
    "time_transposed = time_transposed.merge(gas_df, how='inner', on='DATE')\n",
    "gas = time_transposed['PRICE_PER_GALLON']\n",
    "time_transposed = time_transposed.drop(columns=['PRICE_PER_GALLON'])\n",
    "time_transposed.insert(loc=1, column='PRICE_PER_GALLON', value=gas)\n",
    "time_transposed = time_transposed.dropna()\n",
    "time_transposed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "91ad04e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create CSV with fully cleaned and incorporated data\n",
    "time_transposed.to_csv('./resources/full_data_set_zipcodes.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mlenv",
   "language": "python",
   "name": "mlenv"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}