notebook/01_data_scrapping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Extraction\n",
    "\n",
    "### Import the required libraries\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  File Handling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "def handle_file(path: str, name: str) -> str:\n",
    "    \"\"\"\n",
    "    Rename any conflicting file to prevent unwanted overriding.\n",
    "    \"\"\"\n",
    "\n",
    "    file_path = os.path.join(path, name)\n",
    "\n",
    "    if os.path.isfile(file_path):\n",
    "        print(\"Warning!\")\n",
    "        print(\"A file exists with the same name as the one being saved.\")\n",
    "        keep = input(\"Should I override the existing file? (y/n): \").strip().lower()\n",
    "\n",
    "        if keep != 'y' and keep != 'yes':\n",
    "            new_name = f\"{name}.backup\"\n",
    "            new_file_path = os.path.join(path, new_name)\n",
    "            print(f\"Keeping existing file as {new_file_path}\")\n",
    "            os.rename(file_path, new_file_path)\n",
    "\n",
    "    return file_path"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* File Conflict Handling: The code effectively prevents accidental file overwrites by checking for existing files with the same name. If a conflict is detected, it renames the existing file with a \".backup\" extension, ensuring data preservation.\n",
    "\n",
    "* User Interaction: It engages the user by asking for confirmation before overwriting a file, enhancing user experience and preventing unintentional data loss."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Housing Construction Spending](https://www.census.gov/construction/c30/c30index.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Priv-Res SA\n"
     ]
    }
   ],
   "source": [
    "# Read the Excel file from the given URL and skip the first two rows.\n",
    "constr_spend = pd.read_excel('https://www.census.gov/construction/c30/xls/residentialsa.xls', skiprows=[0, 1])\n",
    "\n",
    "# Restrict the data to records up to 2022 only as 2023 has lot of missing values\n",
    "constr_spend = constr_spend.iloc[0:30, :]\n",
    "\n",
    "# Automatically convert the data types of columns to their suitable types.\n",
    "constr_spend = constr_spend.convert_dtypes()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* The code reads an Excel file from a URL, skipping the first two rows, and stores it as a Pandas DataFrame. It then selects data up to the year 2022, excluding 2023, known for missing values. Finally, it automatically converts the DataFrame's column data types to their most suitable types."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Top 5 values in the constrution spending"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Year</th>\n",
       "      <th>Jan</th>\n",
       "      <th>Feb</th>\n",
       "      <th>Mar</th>\n",
       "      <th>Apr</th>\n",
       "      <th>May</th>\n",
       "      <th>Jun</th>\n",
       "      <th>Jul</th>\n",
       "      <th>Aug</th>\n",
       "      <th>Sep</th>\n",
       "      <th>Oct</th>\n",
       "      <th>Nov</th>\n",
       "      <th>Dec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1993</td>\n",
       "      <td>194150</td>\n",
       "      <td>194689</td>\n",
       "      <td>190185</td>\n",
       "      <td>198296</td>\n",
       "      <td>198835</td>\n",
       "      <td>203175</td>\n",
       "      <td>208760</td>\n",
       "      <td>212238</td>\n",
       "      <td>214446</td>\n",
       "      <td>214778</td>\n",
       "      <td>227235</td>\n",
       "      <td>235648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1994</td>\n",
       "      <td>238612</td>\n",
       "      <td>236577</td>\n",
       "      <td>235150</td>\n",
       "      <td>244746</td>\n",
       "      <td>244056</td>\n",
       "      <td>244032</td>\n",
       "      <td>245463</td>\n",
       "      <td>243134</td>\n",
       "      <td>242619</td>\n",
       "      <td>234905</td>\n",
       "      <td>239857</td>\n",
       "      <td>241395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1995</td>\n",
       "      <td>241184</td>\n",
       "      <td>234257</td>\n",
       "      <td>228013</td>\n",
       "      <td>225488</td>\n",
       "      <td>222062</td>\n",
       "      <td>218972</td>\n",
       "      <td>224113</td>\n",
       "      <td>226045</td>\n",
       "      <td>228688</td>\n",
       "      <td>227589</td>\n",
       "      <td>231913</td>\n",
       "      <td>235572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1996</td>\n",
       "      <td>240958</td>\n",
       "      <td>243695</td>\n",
       "      <td>249952</td>\n",
       "      <td>252836</td>\n",
       "      <td>259296</td>\n",
       "      <td>263085</td>\n",
       "      <td>263549</td>\n",
       "      <td>263618</td>\n",
       "      <td>265677</td>\n",
       "      <td>261444</td>\n",
       "      <td>259668</td>\n",
       "      <td>253234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1997</td>\n",
       "      <td>255365</td>\n",
       "      <td>258595</td>\n",
       "      <td>264566</td>\n",
       "      <td>257490</td>\n",
       "      <td>264581</td>\n",
       "      <td>262299</td>\n",
       "      <td>261593</td>\n",
       "      <td>263951</td>\n",
       "      <td>270511</td>\n",
       "      <td>272980</td>\n",
       "      <td>272786</td>\n",
       "      <td>269192</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year     Jan     Feb     Mar     Apr     May     Jun     Jul     Aug  \\\n",
       "0  1993  194150  194689  190185  198296  198835  203175  208760  212238   \n",
       "1  1994  238612  236577  235150  244746  244056  244032  245463  243134   \n",
       "2  1995  241184  234257  228013  225488  222062  218972  224113  226045   \n",
       "3  1996  240958  243695  249952  252836  259296  263085  263549  263618   \n",
       "4  1997  255365  258595  264566  257490  264581  262299  261593  263951   \n",
       "\n",
       "      Sep     Oct     Nov     Dec  \n",
       "0  214446  214778  227235  235648  \n",
       "1  242619  234905  239857  241395  \n",
       "2  228688  227589  231913  235572  \n",
       "3  265677  261444  259668  253234  \n",
       "4  270511  272980  272786  269192  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "constr_spend.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Parse the data into an organized structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>spend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1993</td>\n",
       "      <td>Jan</td>\n",
       "      <td>194150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1993</td>\n",
       "      <td>Feb</td>\n",
       "      <td>194689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1993</td>\n",
       "      <td>Mar</td>\n",
       "      <td>190185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1993</td>\n",
       "      <td>Apr</td>\n",
       "      <td>198296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1993</td>\n",
       "      <td>May</td>\n",
       "      <td>198835</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year month   spend\n",
       "0  1993   Jan  194150\n",
       "1  1993   Feb  194689\n",
       "2  1993   Mar  190185\n",
       "3  1993   Apr  198296\n",
       "4  1993   May  198835"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "constr_spend = constr_spend.set_index('Year').stack().reset_index().rename(columns={'Year':'year', 'level_1':'month', 0:'spend'})\n",
    "constr_spend.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* The code reshapes a DataFrame by setting 'Year' as the index, stacking columns into rows, and then renaming the columns to 'year', 'month', and 'spend'. This transformation organizes data into a long format suitable for analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>spend</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1993-01-01</th>\n",
       "      <td>194150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1993-02-01</th>\n",
       "      <td>194689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1993-03-01</th>\n",
       "      <td>190185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1993-04-01</th>\n",
       "      <td>198296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1993-05-01</th>\n",
       "      <td>198835</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             spend\n",
       "date              \n",
       "1993-01-01  194150\n",
       "1993-02-01  194689\n",
       "1993-03-01  190185\n",
       "1993-04-01  198296\n",
       "1993-05-01  198835"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Combine 'year' and 'month' columns into a 'date' column as a string.\n",
    "constr_spend['date'] = constr_spend['year'].astype(str) + '-' + constr_spend['month']\n",
    "\n",
    "# Convert the 'date' column to a datetime format.\n",
    "constr_spend['date'] = pd.to_datetime(constr_spend['date'])\n",
    "\n",
    "# Drop the 'year' and 'month' columns.\n",
    "constr_spend.drop(['year', 'month'], axis=1, inplace=True)\n",
    "\n",
    "# Set the 'date' column as the DataFrame's index.\n",
    "constr_spend.set_index(['date'], inplace=True)\n",
    "\n",
    "# Display the updated DataFrame.\n",
    "constr_spend.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NewForSale_Annual\n",
      "NewForSale_Monthly\n"
     ]
    }
   ],
   "source": [
    "hud_supply_df = pd.read_excel('https://www.huduser.gov/portal/periodicals/ushmc/charts/HousingSupply.xlsx',\n",
    "                        sheet_name='Construction_Monthly')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Month</th>\n",
       "      <th>Auth_US</th>\n",
       "      <th>Auth_S1</th>\n",
       "      <th>Auth_S2to4</th>\n",
       "      <th>Auth_S5more</th>\n",
       "      <th>Auth_NE</th>\n",
       "      <th>Auth_MW</th>\n",
       "      <th>Auth_S</th>\n",
       "      <th>Auth_W</th>\n",
       "      <th>Starts_US</th>\n",
       "      <th>...</th>\n",
       "      <th>UndCon_MW</th>\n",
       "      <th>UndCon_S</th>\n",
       "      <th>UndCon_W</th>\n",
       "      <th>Complt_US</th>\n",
       "      <th>Complt_S1</th>\n",
       "      <th>Complt_S5more</th>\n",
       "      <th>Complt_NE</th>\n",
       "      <th>Complt_MW</th>\n",
       "      <th>Complt_S</th>\n",
       "      <th>Complt_W</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1968-01-01</td>\n",
       "      <td>1179</td>\n",
       "      <td>679</td>\n",
       "      <td>81</td>\n",
       "      <td>419</td>\n",
       "      <td>181</td>\n",
       "      <td>333</td>\n",
       "      <td>414</td>\n",
       "      <td>251</td>\n",
       "      <td>1380</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1968-02-01</td>\n",
       "      <td>1342</td>\n",
       "      <td>711</td>\n",
       "      <td>89</td>\n",
       "      <td>542</td>\n",
       "      <td>224</td>\n",
       "      <td>381</td>\n",
       "      <td>460</td>\n",
       "      <td>277</td>\n",
       "      <td>1520</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1968-03-01</td>\n",
       "      <td>1370</td>\n",
       "      <td>691</td>\n",
       "      <td>80</td>\n",
       "      <td>599</td>\n",
       "      <td>297</td>\n",
       "      <td>332</td>\n",
       "      <td>454</td>\n",
       "      <td>287</td>\n",
       "      <td>1466</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1968-04-01</td>\n",
       "      <td>1286</td>\n",
       "      <td>675</td>\n",
       "      <td>81</td>\n",
       "      <td>530</td>\n",
       "      <td>243</td>\n",
       "      <td>330</td>\n",
       "      <td>446</td>\n",
       "      <td>267</td>\n",
       "      <td>1554</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1968-05-01</td>\n",
       "      <td>1297</td>\n",
       "      <td>665</td>\n",
       "      <td>83</td>\n",
       "      <td>549</td>\n",
       "      <td>218</td>\n",
       "      <td>369</td>\n",
       "      <td>435</td>\n",
       "      <td>275</td>\n",
       "      <td>1408</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>661</th>\n",
       "      <td>2023-02-01</td>\n",
       "      <td>1482</td>\n",
       "      <td>796</td>\n",
       "      <td>48</td>\n",
       "      <td>638</td>\n",
       "      <td>113</td>\n",
       "      <td>188</td>\n",
       "      <td>825</td>\n",
       "      <td>356</td>\n",
       "      <td>1436</td>\n",
       "      <td>...</td>\n",
       "      <td>211.0</td>\n",
       "      <td>787.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>1577.0</td>\n",
       "      <td>1029.0</td>\n",
       "      <td>542.0</td>\n",
       "      <td>131.0</td>\n",
       "      <td>205.0</td>\n",
       "      <td>881.0</td>\n",
       "      <td>360.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>662</th>\n",
       "      <td>2023-03-01</td>\n",
       "      <td>1437</td>\n",
       "      <td>829</td>\n",
       "      <td>52</td>\n",
       "      <td>556</td>\n",
       "      <td>148</td>\n",
       "      <td>204</td>\n",
       "      <td>768</td>\n",
       "      <td>317</td>\n",
       "      <td>1380</td>\n",
       "      <td>...</td>\n",
       "      <td>214.0</td>\n",
       "      <td>787.0</td>\n",
       "      <td>462.0</td>\n",
       "      <td>1528.0</td>\n",
       "      <td>1035.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>761.0</td>\n",
       "      <td>417.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>663</th>\n",
       "      <td>2023-04-01</td>\n",
       "      <td>1417</td>\n",
       "      <td>856</td>\n",
       "      <td>58</td>\n",
       "      <td>503</td>\n",
       "      <td>107</td>\n",
       "      <td>174</td>\n",
       "      <td>803</td>\n",
       "      <td>333</td>\n",
       "      <td>1348</td>\n",
       "      <td>...</td>\n",
       "      <td>210.0</td>\n",
       "      <td>796.0</td>\n",
       "      <td>460.0</td>\n",
       "      <td>1416.0</td>\n",
       "      <td>979.0</td>\n",
       "      <td>420.0</td>\n",
       "      <td>136.0</td>\n",
       "      <td>211.0</td>\n",
       "      <td>734.0</td>\n",
       "      <td>335.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>664</th>\n",
       "      <td>2023-05-01</td>\n",
       "      <td>1496</td>\n",
       "      <td>902</td>\n",
       "      <td>54</td>\n",
       "      <td>540</td>\n",
       "      <td>137</td>\n",
       "      <td>187</td>\n",
       "      <td>822</td>\n",
       "      <td>350</td>\n",
       "      <td>1559</td>\n",
       "      <td>...</td>\n",
       "      <td>209.0</td>\n",
       "      <td>799.0</td>\n",
       "      <td>457.0</td>\n",
       "      <td>1518.0</td>\n",
       "      <td>1014.0</td>\n",
       "      <td>488.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>205.0</td>\n",
       "      <td>863.0</td>\n",
       "      <td>335.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>665</th>\n",
       "      <td>2023-06-01</td>\n",
       "      <td>1440</td>\n",
       "      <td>922</td>\n",
       "      <td>51</td>\n",
       "      <td>467</td>\n",
       "      <td>105</td>\n",
       "      <td>198</td>\n",
       "      <td>801</td>\n",
       "      <td>336</td>\n",
       "      <td>1434</td>\n",
       "      <td>...</td>\n",
       "      <td>208.0</td>\n",
       "      <td>798.0</td>\n",
       "      <td>456.0</td>\n",
       "      <td>1468.0</td>\n",
       "      <td>986.0</td>\n",
       "      <td>476.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>196.0</td>\n",
       "      <td>826.0</td>\n",
       "      <td>316.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>666 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Month  Auth_US  Auth_S1  Auth_S2to4  Auth_S5more  Auth_NE  Auth_MW  \\\n",
       "0   1968-01-01     1179      679          81          419      181      333   \n",
       "1   1968-02-01     1342      711          89          542      224      381   \n",
       "2   1968-03-01     1370      691          80          599      297      332   \n",
       "3   1968-04-01     1286      675          81          530      243      330   \n",
       "4   1968-05-01     1297      665          83          549      218      369   \n",
       "..         ...      ...      ...         ...          ...      ...      ...   \n",
       "661 2023-02-01     1482      796          48          638      113      188   \n",
       "662 2023-03-01     1437      829          52          556      148      204   \n",
       "663 2023-04-01     1417      856          58          503      107      174   \n",
       "664 2023-05-01     1496      902          54          540      137      187   \n",
       "665 2023-06-01     1440      922          51          467      105      198   \n",
       "\n",
       "     Auth_S  Auth_W  Starts_US  ...  UndCon_MW  UndCon_S  UndCon_W  Complt_US  \\\n",
       "0       414     251       1380  ...        NaN       NaN       NaN        NaN   \n",
       "1       460     277       1520  ...        NaN       NaN       NaN        NaN   \n",
       "2       454     287       1466  ...        NaN       NaN       NaN        NaN   \n",
       "3       446     267       1554  ...        NaN       NaN       NaN        NaN   \n",
       "4       435     275       1408  ...        NaN       NaN       NaN        NaN   \n",
       "..      ...     ...        ...  ...        ...       ...       ...        ...   \n",
       "661     825     356       1436  ...      211.0     787.0     469.0     1577.0   \n",
       "662     768     317       1380  ...      214.0     787.0     462.0     1528.0   \n",
       "663     803     333       1348  ...      210.0     796.0     460.0     1416.0   \n",
       "664     822     350       1559  ...      209.0     799.0     457.0     1518.0   \n",
       "665     801     336       1434  ...      208.0     798.0     456.0     1468.0   \n",
       "\n",
       "     Complt_S1  Complt_S5more  Complt_NE  Complt_MW  Complt_S  Complt_W  \n",
       "0          NaN            NaN        NaN        NaN       NaN       NaN  \n",
       "1          NaN            NaN        NaN        NaN       NaN       NaN  \n",
       "2          NaN            NaN        NaN        NaN       NaN       NaN  \n",
       "3          NaN            NaN        NaN        NaN       NaN       NaN  \n",
       "4          NaN            NaN        NaN        NaN       NaN       NaN  \n",
       "..         ...            ...        ...        ...       ...       ...  \n",
       "661     1029.0          542.0      131.0      205.0     881.0     360.0  \n",
       "662     1035.0          480.0      135.0      215.0     761.0     417.0  \n",
       "663      979.0          420.0      136.0      211.0     734.0     335.0  \n",
       "664     1014.0          488.0      115.0      205.0     863.0     335.0  \n",
       "665      986.0          476.0      130.0      196.0     826.0     316.0  \n",
       "\n",
       "[666 rows x 30 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hud_supply_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "## remove useless rows and columns\n",
    "\n",
    "permits = hud_supply_df[['Month', 'Auth_S1']].copy()\n",
    "\n",
    "permits.columns = ['date', 'permits']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "## date column is already in datetime format\n",
    "\n",
    "permits['date'] = pd.to_datetime(permits['date'])\n",
    "\n",
    "permits['date'] = permits['date'].apply(lambda dt: dt.replace(day=1))\n",
    "\n",
    "permits.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* The code takes a DataFrame column named 'date' containing datetime values, replaces the day component with 1 for each datetime, and sets the modified 'date' column as the index of the DataFrame 'permits'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>permits</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1968-01-01</th>\n",
       "      <td>679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-02-01</th>\n",
       "      <td>711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-03-01</th>\n",
       "      <td>691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-04-01</th>\n",
       "      <td>675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-05-01</th>\n",
       "      <td>665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-02-01</th>\n",
       "      <td>796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-03-01</th>\n",
       "      <td>829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-04-01</th>\n",
       "      <td>856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-05-01</th>\n",
       "      <td>902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-01</th>\n",
       "      <td>922</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>666 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            permits\n",
       "date               \n",
       "1968-01-01      679\n",
       "1968-02-01      711\n",
       "1968-03-01      691\n",
       "1968-04-01      675\n",
       "1968-05-01      665\n",
       "...             ...\n",
       "2023-02-01      796\n",
       "2023-03-01      829\n",
       "2023-04-01      856\n",
       "2023-05-01      902\n",
       "2023-06-01      922\n",
       "\n",
       "[666 rows x 1 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "permits"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [New construction of residential properties commences](https://www.huduser.gov/Portal/ushmc/hs_sfm.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_starts = hud_supply_df[['Month', 'Starts_S1']].copy()\n",
    "\n",
    "new_starts.columns = ['date', 'starts']\n",
    "\n",
    "new_starts['date'] = new_starts['date'].apply(lambda dt: dt.replace(day=1))\n",
    "\n",
    "new_starts.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* The code extracts the 'Month' and 'Starts_S1' columns from a DataFrame called 'hud_supply_df', renames the columns to 'date' and 'starts', and converts the 'date' values to the first day of each month. Finally, it sets the 'date' column as the index of the DataFrame 'new_starts'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>starts</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1968-01-01</th>\n",
       "      <td>839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-02-01</th>\n",
       "      <td>993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-03-01</th>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-04-01</th>\n",
       "      <td>925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-05-01</th>\n",
       "      <td>850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-02-01</th>\n",
       "      <td>835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-03-01</th>\n",
       "      <td>843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-04-01</th>\n",
       "      <td>847</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-05-01</th>\n",
       "      <td>1005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-01</th>\n",
       "      <td>935</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>666 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            starts\n",
       "date              \n",
       "1968-01-01     839\n",
       "1968-02-01     993\n",
       "1968-03-01     912\n",
       "1968-04-01     925\n",
       "1968-05-01     850\n",
       "...            ...\n",
       "2023-02-01     835\n",
       "2023-03-01     843\n",
       "2023-04-01     847\n",
       "2023-05-01    1005\n",
       "2023-06-01     935\n",
       "\n",
       "[666 rows x 1 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_starts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Compeletion of House](https://www.huduser.gov/Portal/ushmc/hs_sfm.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the 'Month' and 'Complt_S1' columns from the DataFrame 'hud_supply_df' and create a copy.\n",
    "completions = hud_supply_df[['Month', 'Complt_S1']].copy()\n",
    "\n",
    "# Rename the columns of the new DataFrame to 'date' and 'completions'.\n",
    "completions.columns = ['date', 'completions']\n",
    "\n",
    "# Convert the 'date' values to the first day of each month using a lambda function.\n",
    "completions['date'] = completions['date'].apply(lambda dt: dt.replace(day=1))\n",
    "\n",
    "# Set the 'date' column as the index of the DataFrame 'completions'.\n",
    "completions.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* The above creates a new DataFrame named 'completions' by copying the 'Month' and 'Complt_S1' columns from 'hud_supply_df,' renaming them, and converting the 'date' values to the first day of each month. It then sets the 'date' column as the index of the 'completions' DataFrame, essentially organizing the data by month for completions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>completions</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1968-01-01</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-02-01</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-03-01</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-04-01</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968-05-01</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-02-01</th>\n",
       "      <td>1029.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-03-01</th>\n",
       "      <td>1035.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-04-01</th>\n",
       "      <td>979.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-05-01</th>\n",
       "      <td>1014.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-01</th>\n",
       "      <td>986.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>666 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            completions\n",
       "date                   \n",
       "1968-01-01          NaN\n",
       "1968-02-01          NaN\n",
       "1968-03-01          NaN\n",
       "1968-04-01          NaN\n",
       "1968-05-01          NaN\n",
       "...                 ...\n",
       "2023-02-01       1029.0\n",
       "2023-03-01       1035.0\n",
       "2023-04-01        979.0\n",
       "2023-05-01       1014.0\n",
       "2023-06-01        986.0\n",
       "\n",
       "[666 rows x 1 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "completions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Manufacturing of House](https://www.huduser.gov/portal/ushmc/hs_man_hsg.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NewForSale_Annual\n",
      "NewForSale_Monthly\n"
     ]
    }
   ],
   "source": [
    "manufacturing = pd.read_excel('https://www.huduser.gov/portal/periodicals/ushmc/charts/HousingSupply.xlsx',\n",
    "                        sheet_name='Manufactured_Monthly')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "manufacturing = manufacturing.loc[:, ['Month', 'Shipments_US']]\n",
    "\n",
    "manufacturing.columns = ['date', 'manufactured']\n",
    "\n",
    "manufacturing['date'] = manufacturing['date'].apply(lambda dt: dt.replace(day=1))\n",
    "manufacturing.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>manufactured</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1978-01-01</th>\n",
       "      <td>293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-02-01</th>\n",
       "      <td>275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-03-01</th>\n",
       "      <td>277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-04-01</th>\n",
       "      <td>267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-05-01</th>\n",
       "      <td>276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-02-01</th>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-03-01</th>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-04-01</th>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-05-01</th>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-01</th>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>546 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            manufactured\n",
       "date                    \n",
       "1978-01-01           293\n",
       "1978-02-01           275\n",
       "1978-03-01           277\n",
       "1978-04-01           267\n",
       "1978-05-01           276\n",
       "...                  ...\n",
       "2023-02-01            85\n",
       "2023-03-01            83\n",
       "2023-04-01            84\n",
       "2023-05-01            87\n",
       "2023-06-01            87\n",
       "\n",
       "[546 rows x 1 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "manufacturing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* This is an Excel file from a URL, extracts specific columns ('Month' and 'Shipments_US'), renames the columns, converts the 'date' column to the first day of each month, and sets 'date' as the index for further analysis, likely related to manufacturing shipment data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Building Permit Value](https://www.census.gov/construction/bps/index.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "building_permit_value = pd.read_excel('https://www.census.gov/construction/bps/permitsbyusreg_cust.xls',\n",
    "                             sheet_name='Val Monthly',\n",
    "                            skiprows=list(range(4)), header=[0,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "## drop unnecessary columns\n",
    "\n",
    "building_permit_value = building_permit_value.iloc[:-6, [1, 3]]\n",
    "\n",
    "building_permit_value.columns = ['date', 'permit_val']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "## parse datetime\n",
    "\n",
    "building_permit_value['date'] = pd.to_datetime(building_permit_value['date'], format='%Y-%m-%d')\n",
    "\n",
    "building_permit_value['date'] = building_permit_value['date'].apply(lambda dt: dt.replace(day=1))\n",
    "\n",
    "building_permit_value.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>permit_val</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1988-01-01</th>\n",
       "      <td>4081.302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1988-02-01</th>\n",
       "      <td>5050.834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1988-03-01</th>\n",
       "      <td>7787.515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1988-04-01</th>\n",
       "      <td>7595.054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1988-05-01</th>\n",
       "      <td>8119.532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-01-01</th>\n",
       "      <td>15487.041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-02-01</th>\n",
       "      <td>16513.078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-03-01</th>\n",
       "      <td>22502.037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-04-01</th>\n",
       "      <td>21810.419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-05-01</th>\n",
       "      <td>25971.423</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>425 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            permit_val\n",
       "date                  \n",
       "1988-01-01    4081.302\n",
       "1988-02-01    5050.834\n",
       "1988-03-01    7787.515\n",
       "1988-04-01    7595.054\n",
       "1988-05-01    8119.532\n",
       "...                ...\n",
       "2023-01-01   15487.041\n",
       "2023-02-01   16513.078\n",
       "2023-03-01   22502.037\n",
       "2023-04-01   21810.419\n",
       "2023-05-01   25971.423\n",
       "\n",
       "[425 rows x 1 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "building_permit_value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* This code first parses a 'date' column in a DataFrame to datetime format with the specified format ('%Y-%m-%d'), then replaces the day component with 1 for each date and sets the 'date' column as the DataFrame's index, likely for time series analysis with monthly granularity."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [New House for Sale](https://www.huduser.gov/portal/ushmc/hs_newsf.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NewForSale_Annual\n",
      "NewForSale_Monthly\n"
     ]
    }
   ],
   "source": [
    "new_for_sale = pd.read_excel('https://www.huduser.gov/portal/periodicals/ushmc/charts/HousingSupply.xlsx',\n",
    "                        sheet_name='NewForSale_Monthly')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selecting specific columns 'Month', 'NewForSale_US', and 'NewMnSply_US'\n",
    "new_for_sale = new_for_sale.loc[:, ['Month', 'NewForSale_US', 'NewMnSply_US']]\n",
    "\n",
    "# Renaming columns for clarity\n",
    "new_for_sale.columns = ['date', 'new_for_sale', 'months_supply']\n",
    "\n",
    "# Replacing the day component of the 'date' column with 1 for each date\n",
    "new_for_sale['date'] = new_for_sale['date'].apply(lambda dt: dt.replace(day=1))\n",
    "\n",
    "# Setting the 'date' column as the index for time series analysis\n",
    "new_for_sale.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>new_for_sale</th>\n",
       "      <th>months_supply</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1963-01-01</th>\n",
       "      <td>234</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-02-01</th>\n",
       "      <td>236</td>\n",
       "      <td>6.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-03-01</th>\n",
       "      <td>246</td>\n",
       "      <td>6.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-04-01</th>\n",
       "      <td>254</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-05-01</th>\n",
       "      <td>254</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-02-01</th>\n",
       "      <td>437</td>\n",
       "      <td>8.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-03-01</th>\n",
       "      <td>433</td>\n",
       "      <td>8.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-04-01</th>\n",
       "      <td>432</td>\n",
       "      <td>7.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-05-01</th>\n",
       "      <td>429</td>\n",
       "      <td>7.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-01</th>\n",
       "      <td>432</td>\n",
       "      <td>7.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>726 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            new_for_sale  months_supply\n",
       "date                                   \n",
       "1963-01-01           234            4.7\n",
       "1963-02-01           236            6.6\n",
       "1963-03-01           246            6.4\n",
       "1963-04-01           254            5.3\n",
       "1963-05-01           254            5.1\n",
       "...                  ...            ...\n",
       "2023-02-01           437            8.4\n",
       "2023-03-01           433            8.1\n",
       "2023-04-01           432            7.7\n",
       "2023-05-01           429            7.2\n",
       "2023-06-01           432            7.4\n",
       "\n",
       "[726 rows x 2 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_for_sale"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* This code processes a DataFrame by selecting specific columns, renaming them, converting the 'date' column to the first day of each month, and setting 'date' as the index. It's likely preparing the data for time series analysis of new property listings and monthly supply."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Housing Vacancies Rate](https://www.census.gov/housing/hvs/data/histtabs.html) Quarterly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "vacancy_df = pd.read_excel('https://www.census.gov/housing/hvs/files/qtr222/tab1.xlsx',\n",
    "                       skiprows=range(4), header=[0,1,2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th colspan=\"4\" halign=\"left\">rent_vacancy</th>\n",
       "      <th colspan=\"4\" halign=\"left\">owner_vacancy</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Q1</th>\n",
       "      <th>Q2</th>\n",
       "      <th>Q3</th>\n",
       "      <th>Q4</th>\n",
       "      <th>Q1</th>\n",
       "      <th>Q2</th>\n",
       "      <th>Q3</th>\n",
       "      <th>Q4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021...…………..…….</td>\n",
       "      <td>6.8</td>\n",
       "      <td>6.2</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5.6</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020...…………..…….</td>\n",
       "      <td>6.6</td>\n",
       "      <td>5.7</td>\n",
       "      <td>6.4</td>\n",
       "      <td>6.5</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019...…………..…….</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.8</td>\n",
       "      <td>6.8</td>\n",
       "      <td>6.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018...…………..…….</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.8</td>\n",
       "      <td>7.1</td>\n",
       "      <td>6.6</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.5</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>1969.....…………........</td>\n",
       "      <td>5.6</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.5</td>\n",
       "      <td>5.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>1968..…………...........</td>\n",
       "      <td>6.1</td>\n",
       "      <td>6.2</td>\n",
       "      <td>5.9</td>\n",
       "      <td>5.4</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>1967..…………...........</td>\n",
       "      <td>7.3</td>\n",
       "      <td>6.9</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.2</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>1966..…………...........</td>\n",
       "      <td>8.3</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>1965..…………...........</td>\n",
       "      <td>8.5</td>\n",
       "      <td>8.2</td>\n",
       "      <td>7.8</td>\n",
       "      <td>8.5</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     year rent_vacancy                owner_vacancy            \\\n",
       "                                    Q1   Q2   Q3   Q4            Q1   Q2   Q3   \n",
       "3        2021...…………..…….          6.8  6.2  5.8  5.6           0.9  0.9  0.9   \n",
       "4        2020...…………..…….          6.6  5.7  6.4  6.5           1.1  0.9  0.9   \n",
       "5                     NaN          NaN  NaN  NaN  NaN           NaN  NaN  NaN   \n",
       "6        2019...…………..…….          7.0  6.8  6.8  6.4           1.4  1.3  1.4   \n",
       "7        2018...…………..…….          7.0  6.8  7.1  6.6           1.5  1.5  1.6   \n",
       "..                    ...          ...  ...  ...  ...           ...  ...  ...   \n",
       "65  1969.....…………........          5.6  5.7  5.5  5.1           1.0  1.0  1.1   \n",
       "66  1968..…………...........          6.1  6.2  5.9  5.4           1.1  1.1  1.2   \n",
       "67  1967..…………...........          7.3  6.9  7.0  6.2           1.4  1.3  1.4   \n",
       "68  1966..…………...........          8.3  7.4  7.4  7.7           1.5  1.5  1.4   \n",
       "69  1965..…………...........          8.5  8.2  7.8  8.5           1.7  1.5  1.6   \n",
       "\n",
       "         \n",
       "     Q4  \n",
       "3   0.9  \n",
       "4   1.0  \n",
       "5   NaN  \n",
       "6   1.4  \n",
       "7   1.5  \n",
       "..  ...  \n",
       "65  1.0  \n",
       "66  1.2  \n",
       "67  1.3  \n",
       "68  1.3  \n",
       "69  1.5  \n",
       "\n",
       "[67 rows x 9 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dropping the second level of column labels, if present\n",
    "vacancy_df.columns = vacancy_df.columns.droplevel(1)\n",
    "\n",
    "# Slicing the DataFrame to exclude the first 3 rows and last 7 rows\n",
    "vacancy_df = vacancy_df.iloc[3:-7]\n",
    "\n",
    "# Creating a multi-level column index with arrays 'rent_vacancy' and 'owner_vacancy' for the first level,\n",
    "# and 'Q1', 'Q2', 'Q3', 'Q4' for the second level\n",
    "arrays = [['rent_vacancy', 'owner_vacancy'], ['Q1', 'Q2', 'Q3', 'Q4']]\n",
    "\n",
    "# Creating a MultiIndex from the arrays and inserting a 'year' column at the beginning\n",
    "columns = pd.MultiIndex.from_product(arrays)\n",
    "columns = columns.insert(0, ('year', ''))\n",
    "\n",
    "# Assigning the newly created MultiIndex to the DataFrame columns\n",
    "vacancy_df.columns = columns\n",
    "\n",
    "vacancy_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "vacancy_df.dropna(axis=0, how='all', inplace=True)\n",
    "\n",
    "mask = vacancy_df['year'].str.find('r') !=-1\n",
    "\n",
    "repeat_cols = vacancy_df['year'][mask].str[:4].values\n",
    "\n",
    "drop_index = vacancy_df[vacancy_df['year'].apply(lambda x: str(x)[:4] in repeat_cols and str(x).find('r') == -1)].index\n",
    "\n",
    "\n",
    "vacancy_df = vacancy_df.drop(axis=0, index=drop_index).reset_index(drop=True)\n",
    "\n",
    "vacancy_df['year'] = vacancy_df['year'].str[:4]\n",
    "\n",
    "vacancy_df = vacancy_df.convert_dtypes()\n",
    "\n",
    "vacancy_df.sort_values(['year'], ascending=True, inplace=True, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th colspan=\"4\" halign=\"left\">rent_vacancy</th>\n",
       "      <th colspan=\"4\" halign=\"left\">owner_vacancy</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Q1</th>\n",
       "      <th>Q2</th>\n",
       "      <th>Q3</th>\n",
       "      <th>Q4</th>\n",
       "      <th>Q1</th>\n",
       "      <th>Q2</th>\n",
       "      <th>Q3</th>\n",
       "      <th>Q4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1965</td>\n",
       "      <td>8.5</td>\n",
       "      <td>8.2</td>\n",
       "      <td>7.8</td>\n",
       "      <td>8.5</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1966</td>\n",
       "      <td>8.3</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1967</td>\n",
       "      <td>7.3</td>\n",
       "      <td>6.9</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.2</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1968</td>\n",
       "      <td>6.1</td>\n",
       "      <td>6.2</td>\n",
       "      <td>5.9</td>\n",
       "      <td>5.4</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1969</td>\n",
       "      <td>5.6</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.5</td>\n",
       "      <td>5.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1970</td>\n",
       "      <td>5.4</td>\n",
       "      <td>5.4</td>\n",
       "      <td>5.3</td>\n",
       "      <td>5.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1971</td>\n",
       "      <td>5.3</td>\n",
       "      <td>5.3</td>\n",
       "      <td>5.6</td>\n",
       "      <td>5.6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1972</td>\n",
       "      <td>5.3</td>\n",
       "      <td>5.5</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5.6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1973</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1974</td>\n",
       "      <td>6.2</td>\n",
       "      <td>6.3</td>\n",
       "      <td>6.2</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1975</td>\n",
       "      <td>6.1</td>\n",
       "      <td>6.3</td>\n",
       "      <td>6.2</td>\n",
       "      <td>5.4</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1976</td>\n",
       "      <td>5.5</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.3</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1977</td>\n",
       "      <td>5.1</td>\n",
       "      <td>5.3</td>\n",
       "      <td>5.4</td>\n",
       "      <td>5.1</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1978</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1979</td>\n",
       "      <td>5.1</td>\n",
       "      <td>5.5</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.4</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1980</td>\n",
       "      <td>5.2</td>\n",
       "      <td>5.6</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1981</td>\n",
       "      <td>5.2</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1982</td>\n",
       "      <td>5.3</td>\n",
       "      <td>5.1</td>\n",
       "      <td>5.3</td>\n",
       "      <td>5.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1983</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.5</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1984</td>\n",
       "      <td>5.6</td>\n",
       "      <td>5.5</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.3</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1985</td>\n",
       "      <td>6.3</td>\n",
       "      <td>6.2</td>\n",
       "      <td>6.8</td>\n",
       "      <td>6.7</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1986</td>\n",
       "      <td>6.9</td>\n",
       "      <td>7.3</td>\n",
       "      <td>7.5</td>\n",
       "      <td>7.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1987</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.5</td>\n",
       "      <td>8.1</td>\n",
       "      <td>7.8</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1988</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.7</td>\n",
       "      <td>7.8</td>\n",
       "      <td>7.3</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1989</td>\n",
       "      <td>7.5</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.6</td>\n",
       "      <td>7.1</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1990</td>\n",
       "      <td>7.5</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>7.2</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1991</td>\n",
       "      <td>7.5</td>\n",
       "      <td>7.3</td>\n",
       "      <td>7.6</td>\n",
       "      <td>7.3</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1992</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.7</td>\n",
       "      <td>7.3</td>\n",
       "      <td>7.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1993</td>\n",
       "      <td>7.8</td>\n",
       "      <td>7.6</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.9</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1994</td>\n",
       "      <td>7.5</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.2</td>\n",
       "      <td>7.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1995</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.7</td>\n",
       "      <td>7.7</td>\n",
       "      <td>7.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1996</td>\n",
       "      <td>7.9</td>\n",
       "      <td>7.8</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.7</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1997</td>\n",
       "      <td>7.5</td>\n",
       "      <td>7.9</td>\n",
       "      <td>7.9</td>\n",
       "      <td>7.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1998</td>\n",
       "      <td>7.7</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.2</td>\n",
       "      <td>7.8</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>1999</td>\n",
       "      <td>8.2</td>\n",
       "      <td>8.1</td>\n",
       "      <td>8.2</td>\n",
       "      <td>7.9</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2000</td>\n",
       "      <td>7.9</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.2</td>\n",
       "      <td>7.8</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2001</td>\n",
       "      <td>8.2</td>\n",
       "      <td>8.3</td>\n",
       "      <td>8.4</td>\n",
       "      <td>8.8</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2002</td>\n",
       "      <td>9.1</td>\n",
       "      <td>8.4</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.3</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2003</td>\n",
       "      <td>9.4</td>\n",
       "      <td>9.6</td>\n",
       "      <td>9.9</td>\n",
       "      <td>10.2</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2004</td>\n",
       "      <td>10.4</td>\n",
       "      <td>10.2</td>\n",
       "      <td>10.1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2005</td>\n",
       "      <td>10.1</td>\n",
       "      <td>9.8</td>\n",
       "      <td>9.9</td>\n",
       "      <td>9.6</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.9</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2006</td>\n",
       "      <td>9.5</td>\n",
       "      <td>9.6</td>\n",
       "      <td>9.9</td>\n",
       "      <td>9.8</td>\n",
       "      <td>2.1</td>\n",
       "      <td>2.2</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2007</td>\n",
       "      <td>10.1</td>\n",
       "      <td>9.5</td>\n",
       "      <td>9.8</td>\n",
       "      <td>9.6</td>\n",
       "      <td>2.8</td>\n",
       "      <td>2.6</td>\n",
       "      <td>2.7</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2008</td>\n",
       "      <td>10.1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>9.9</td>\n",
       "      <td>10.1</td>\n",
       "      <td>2.9</td>\n",
       "      <td>2.8</td>\n",
       "      <td>2.8</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2009</td>\n",
       "      <td>10.1</td>\n",
       "      <td>10.6</td>\n",
       "      <td>11.1</td>\n",
       "      <td>10.7</td>\n",
       "      <td>2.7</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.6</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2010</td>\n",
       "      <td>10.6</td>\n",
       "      <td>10.6</td>\n",
       "      <td>10.3</td>\n",
       "      <td>9.4</td>\n",
       "      <td>2.6</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2011</td>\n",
       "      <td>9.7</td>\n",
       "      <td>9.2</td>\n",
       "      <td>9.8</td>\n",
       "      <td>9.4</td>\n",
       "      <td>2.6</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.4</td>\n",
       "      <td>2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2012</td>\n",
       "      <td>8.8</td>\n",
       "      <td>8.6</td>\n",
       "      <td>8.6</td>\n",
       "      <td>8.7</td>\n",
       "      <td>2.2</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2013</td>\n",
       "      <td>8.6</td>\n",
       "      <td>8.2</td>\n",
       "      <td>8.3</td>\n",
       "      <td>8.2</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1.9</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2014</td>\n",
       "      <td>8.3</td>\n",
       "      <td>7.5</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>2015</td>\n",
       "      <td>7.1</td>\n",
       "      <td>6.8</td>\n",
       "      <td>7.3</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>2016</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>6.8</td>\n",
       "      <td>6.9</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>2017</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.3</td>\n",
       "      <td>7.5</td>\n",
       "      <td>6.9</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>2018</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.8</td>\n",
       "      <td>7.1</td>\n",
       "      <td>6.6</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>2019</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.8</td>\n",
       "      <td>6.8</td>\n",
       "      <td>6.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>2020</td>\n",
       "      <td>6.6</td>\n",
       "      <td>5.7</td>\n",
       "      <td>6.4</td>\n",
       "      <td>6.5</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>2021</td>\n",
       "      <td>6.8</td>\n",
       "      <td>6.2</td>\n",
       "      <td>5.8</td>\n",
       "      <td>5.6</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    year rent_vacancy                   owner_vacancy               \n",
       "                   Q1    Q2    Q3    Q4            Q1   Q2   Q3   Q4\n",
       "0   1965          8.5   8.2   7.8   8.5           1.7  1.5  1.6  1.5\n",
       "1   1966          8.3   7.4   7.4   7.7           1.5  1.5  1.4  1.3\n",
       "2   1967          7.3   6.9   7.0   6.2           1.4  1.3  1.4  1.3\n",
       "3   1968          6.1   6.2   5.9   5.4           1.1  1.1  1.2  1.2\n",
       "4   1969          5.6   5.7   5.5   5.1           1.0  1.0  1.1  1.0\n",
       "5   1970          5.4   5.4   5.3   5.2           1.0  1.0  1.1  1.1\n",
       "6   1971          5.3   5.3   5.6   5.6           1.0  0.9  1.0  1.0\n",
       "7   1972          5.3   5.5   5.8   5.6           1.0  1.0  0.9  1.0\n",
       "8   1973          5.7   5.8   5.8   5.8           1.0  0.9  1.1  1.2\n",
       "9   1974          6.2   6.3   6.2   6.0           1.2  1.1  1.2  1.3\n",
       "10  1975          6.1   6.3   6.2   5.4           1.2  1.2  1.4  1.2\n",
       "11  1976          5.5   5.8   5.7   5.3           1.2  1.2  1.3  1.2\n",
       "12  1977          5.1   5.3   5.4   5.1           1.3  1.3  1.1  1.0\n",
       "13  1978          5.0   5.1   5.0   5.0           1.0  0.9  1.0  1.1\n",
       "14  1979          5.1   5.5   5.7   5.4           1.1  1.1  1.2  1.3\n",
       "15  1980          5.2   5.6   5.7   5.0           1.3  1.4  1.4  1.4\n",
       "16  1981          5.2   5.0   5.0   5.0           1.3  1.3  1.5  1.4\n",
       "17  1982          5.3   5.1   5.3   5.5           1.4  1.6  1.5  1.6\n",
       "18  1983          5.7   5.5   5.8   5.5           1.4  1.5  1.6  1.6\n",
       "19  1984          5.6   5.5   6.0   6.3           1.6  1.7  1.7  1.7\n",
       "20  1985          6.3   6.2   6.8   6.7           1.8  1.9  1.8  1.6\n",
       "21  1986          6.9   7.3   7.5   7.7           1.5  1.7  1.6  1.6\n",
       "22  1987          7.4   7.5   8.1   7.8           1.7  1.7  1.7  1.6\n",
       "23  1988          8.0   7.7   7.8   7.3           1.6  1.6  1.6  1.6\n",
       "24  1989          7.5   7.4   7.6   7.1           1.7  1.7  1.9  1.8\n",
       "25  1990          7.5   7.0   7.2   7.2           1.7  1.7  1.7  1.7\n",
       "26  1991          7.5   7.3   7.6   7.3           1.7  1.8  1.8  1.6\n",
       "27  1992          7.4   7.7   7.3   7.1           1.5  1.6  1.6  1.5\n",
       "28  1993          7.8   7.6   7.0   6.9           1.4  1.4  1.4  1.4\n",
       "29  1994          7.5   7.4   7.2   7.4           1.4  1.4  1.4  1.6\n",
       "30  1995          7.4   7.7   7.7   7.7           1.5  1.6  1.5  1.6\n",
       "31  1996          7.9   7.8   8.0   7.7           1.6  1.5  1.7  1.7\n",
       "32  1997          7.5   7.9   7.9   7.7           1.7  1.6  1.5  1.7\n",
       "33  1998          7.7   8.0   8.2   7.8           1.7  1.7  1.7  1.8\n",
       "34  1999          8.2   8.1   8.2   7.9           1.8  1.6  1.6  1.6\n",
       "35  2000          7.9   8.0   8.2   7.8           1.6  1.5  1.6  1.6\n",
       "36  2001          8.2   8.3   8.4   8.8           1.5  1.8  1.9  1.8\n",
       "37  2002          9.1   8.4   9.0   9.3           1.7  1.7  1.7  1.7\n",
       "38  2003          9.4   9.6   9.9  10.2           1.7  1.7  1.9  1.8\n",
       "39  2004         10.4  10.2  10.1  10.0           1.7  1.7  1.7  1.8\n",
       "40  2005         10.1   9.8   9.9   9.6           1.8  1.8  1.9  2.0\n",
       "41  2006          9.5   9.6   9.9   9.8           2.1  2.2  2.5  2.7\n",
       "42  2007         10.1   9.5   9.8   9.6           2.8  2.6  2.7  2.8\n",
       "43  2008         10.1  10.0   9.9  10.1           2.9  2.8  2.8  2.9\n",
       "44  2009         10.1  10.6  11.1  10.7           2.7  2.5  2.6  2.7\n",
       "45  2010         10.6  10.6  10.3   9.4           2.6  2.5  2.5  2.7\n",
       "46  2011          9.7   9.2   9.8   9.4           2.6  2.5  2.4  2.3\n",
       "47  2012          8.8   8.6   8.6   8.7           2.2  2.1  1.9  1.9\n",
       "48  2013          8.6   8.2   8.3   8.2           2.1  1.9  1.9  2.1\n",
       "49  2014          8.3   7.5   7.4   7.0           2.0  1.9  1.8  1.9\n",
       "50  2015          7.1   6.8   7.3   7.0           1.9  1.8  1.9  1.9\n",
       "51  2016          7.0   6.7   6.8   6.9           1.7  1.7  1.8  1.8\n",
       "52  2017          7.0   7.3   7.5   6.9           1.7  1.5  1.6  1.6\n",
       "53  2018          7.0   6.8   7.1   6.6           1.5  1.5  1.6  1.5\n",
       "54  2019          7.0   6.8   6.8   6.4           1.4  1.3  1.4  1.4\n",
       "55  2020          6.6   5.7   6.4   6.5           1.1  0.9  0.9  1.0\n",
       "56  2021          6.8   6.2   5.8   5.6           0.9  0.9  0.9  0.9"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vacancy_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* The provided code sorts the DataFrame 'vacancy_df' in ascending order based on the 'year' column, effectively arranging the data chronologically by year. It also resets the index of the DataFrame to maintain a continuous integer index sequence."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reshaping the DataFrame by setting the 'year' as the index, stacking the columns, and resetting the index\n",
    "vacancy_df = vacancy_df.set_index('year').stack().reset_index().rename(columns={'level_1': 'quarter'})\n",
    "\n",
    "# Creating a 'date' column by combining 'year' and 'quarter' columns\n",
    "vacancy_df['date'] = vacancy_df['year'] + '-' + vacancy_df['quarter']\n",
    "\n",
    "# Dropping 'year' and 'quarter' columns\n",
    "vacancy_df.drop(['year', 'quarter'], axis=1, inplace=True)\n",
    "\n",
    "# Converting the 'date' column to datetime format\n",
    "vacancy_df['date'] = pd.to_datetime(vacancy_df['date'])\n",
    "\n",
    "# Setting the 'date' column as the new index\n",
    "vacancy_df.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>rent_vacancy</th>\n",
       "      <th>owner_vacancy</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1965-01-01</th>\n",
       "      <td>8.5</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1965-04-01</th>\n",
       "      <td>8.2</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1965-07-01</th>\n",
       "      <td>7.8</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1965-10-01</th>\n",
       "      <td>8.5</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1966-01-01</th>\n",
       "      <td>8.3</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-01</th>\n",
       "      <td>6.5</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-01-01</th>\n",
       "      <td>6.8</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-01</th>\n",
       "      <td>6.2</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-07-01</th>\n",
       "      <td>5.8</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-10-01</th>\n",
       "      <td>5.6</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>228 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            rent_vacancy  owner_vacancy\n",
       "date                                   \n",
       "1965-01-01           8.5            1.7\n",
       "1965-04-01           8.2            1.5\n",
       "1965-07-01           7.8            1.6\n",
       "1965-10-01           8.5            1.5\n",
       "1966-01-01           8.3            1.5\n",
       "...                  ...            ...\n",
       "2020-10-01           6.5            1.0\n",
       "2021-01-01           6.8            0.9\n",
       "2021-04-01           6.2            0.9\n",
       "2021-07-01           5.8            0.9\n",
       "2021-10-01           5.6            0.9\n",
       "\n",
       "[228 rows x 2 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vacancy_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>rent_vacancy</th>\n",
       "      <th>owner_vacancy</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1965-01-31</th>\n",
       "      <td>8.5</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1965-02-28</th>\n",
       "      <td>8.2</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1965-03-31</th>\n",
       "      <td>8.2</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1965-04-30</th>\n",
       "      <td>8.2</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1965-05-31</th>\n",
       "      <td>7.8</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            rent_vacancy  owner_vacancy\n",
       "date                                   \n",
       "1965-01-31           8.5            1.7\n",
       "1965-02-28           8.2            1.5\n",
       "1965-03-31           8.2            1.5\n",
       "1965-04-30           8.2            1.5\n",
       "1965-05-31           7.8            1.6"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### upsample to monthly date using nearest neighbor\n",
    "\n",
    "vacancy_monthly = vacancy_df.resample('M').nearest()\n",
    "vacancy_monthly.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "1. The code reshapes the DataFrame 'vacancy_df' by setting the 'year' column as the index, stacking the columns to create a multi-index, and then resetting the index to make the data more accessible.\n",
    "2. It creates a new 'date' column by combining the 'year' and 'quarter' columns, facilitating time-based analysis.\n",
    "3. The code converts the 'date' column to a datetime format and sets it as the new index, preparing the DataFrame for time series analysis with dates as the primary axis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Employment Population Ratio](https://fred.stlouisfed.org/series/EMRATIO)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "Em_ratio = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=EMRATIO&scale=left&cosd=1948-01-01&coed=2022-07-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-08-22&revision_date=2022-08-22&nd=1948-01-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renaming the columns of the DataFrame to 'date' and 'emratio' for clarity\n",
    "Em_ratio.columns = ['date', 'emratio']\n",
    "\n",
    "# Converting the 'date' column to datetime format with the specified format\n",
    "Em_ratio['date'] = pd.to_datetime(Em_ratio['date'], format='%Y-%m-%d')\n",
    "\n",
    "# Setting the 'date' column as the index for time series analysis\n",
    "Em_ratio.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>emratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1948-01-01</th>\n",
       "      <td>56.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1948-02-01</th>\n",
       "      <td>56.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1948-03-01</th>\n",
       "      <td>56.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1948-04-01</th>\n",
       "      <td>56.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1948-05-01</th>\n",
       "      <td>56.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-03-01</th>\n",
       "      <td>60.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>59.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-05-01</th>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-06-01</th>\n",
       "      <td>59.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-07-01</th>\n",
       "      <td>59.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>895 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            emratio\n",
       "date               \n",
       "1948-01-01     56.6\n",
       "1948-02-01     56.7\n",
       "1948-03-01     56.1\n",
       "1948-04-01     56.7\n",
       "1948-05-01     56.2\n",
       "...             ...\n",
       "2022-03-01     60.1\n",
       "2022-04-01     59.9\n",
       "2022-05-01     60.0\n",
       "2022-06-01     59.9\n",
       "2022-07-01     59.9\n",
       "\n",
       "[895 rows x 1 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Em_ratio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* the above code first rename the columns in the DataFrame to 'date' and 'emratio' for better readability and then convert the 'date' column to datetime format with the specified format ('%Y-%m-%d'). Finally, it sets the 'date' column as the DataFrame's index, likely for time series analysis with date-based data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Population Level](https://fred.stlouisfed.org/series/CNP16OV)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "population_level = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=CNP16OV&scale=left&cosd=1948-01-01&coed=2022-07-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-08-22&revision_date=2022-08-22&nd=1948-01-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renames the columns in the DataFrame to 'date' and 'pop_level'.\n",
    "population_level.columns = ['date', 'pop_level']\n",
    "\n",
    "# Converts the 'date' column to datetime format with the specified format and sets it as the index.\n",
    "population_level['date'] = pd.to_datetime(population_level['date'], format='%Y-%m-%d')\n",
    "\n",
    "population_level.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>pop_level</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1948-01-01</th>\n",
       "      <td>102603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1948-02-01</th>\n",
       "      <td>102698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1948-03-01</th>\n",
       "      <td>102771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1948-04-01</th>\n",
       "      <td>102831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1948-05-01</th>\n",
       "      <td>102923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-03-01</th>\n",
       "      <td>263444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>263559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-05-01</th>\n",
       "      <td>263679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-06-01</th>\n",
       "      <td>263835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-07-01</th>\n",
       "      <td>264012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>895 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            pop_level\n",
       "date                 \n",
       "1948-01-01     102603\n",
       "1948-02-01     102698\n",
       "1948-03-01     102771\n",
       "1948-04-01     102831\n",
       "1948-05-01     102923\n",
       "...               ...\n",
       "2022-03-01     263444\n",
       "2022-04-01     263559\n",
       "2022-05-01     263679\n",
       "2022-06-01     263835\n",
       "2022-07-01     264012\n",
       "\n",
       "[895 rows x 1 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population_level"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* This code fetches population level data from a remote CSV file, renames the columns, converts the 'date' column to datetime format, and sets 'date' as the index for further analysis, likely related to population trends over time."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Gross Domestic Product](https://fred.stlouisfed.org/graph/?g=znfe)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdp = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=off&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=USALORSGPNOSTSAM&scale=left&cosd=1990-01-01&coed=2022-05-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-08-22&revision_date=2022-08-22&nd=1960-01-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdp.columns = ['date', 'gdp']\n",
    "\n",
    "gdp['date'] = pd.to_datetime(gdp['date'], format='%Y-%m-%d')\n",
    "\n",
    "gdp.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>gdp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990-01-01</th>\n",
       "      <td>101.344728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-02-01</th>\n",
       "      <td>101.366177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-03-01</th>\n",
       "      <td>101.360289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-04-01</th>\n",
       "      <td>101.315362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-01</th>\n",
       "      <td>101.223169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-01</th>\n",
       "      <td>100.284923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-01</th>\n",
       "      <td>100.151473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-03-01</th>\n",
       "      <td>100.008122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>99.882724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-05-01</th>\n",
       "      <td>99.795936</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>389 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   gdp\n",
       "date                  \n",
       "1990-01-01  101.344728\n",
       "1990-02-01  101.366177\n",
       "1990-03-01  101.360289\n",
       "1990-04-01  101.315362\n",
       "1990-05-01  101.223169\n",
       "...                ...\n",
       "2022-01-01  100.284923\n",
       "2022-02-01  100.151473\n",
       "2022-03-01  100.008122\n",
       "2022-04-01   99.882724\n",
       "2022-05-01   99.795936\n",
       "\n",
       "[389 rows x 1 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Mortgage Rate in United States](https://fred.stlouisfed.org/graph/?g=zneW)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "mortgage_rate = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=MORTGAGE30US&scale=left&cosd=1990-01-01&coed=2022-08-18&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-08-22&revision_date=2022-08-22&nd=1971-04-02')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "mortgage_rate.columns = ['date', 'mortgage_rate']\n",
    "\n",
    "mortgage_rate['date'] = pd.to_datetime(mortgage_rate['date'], format='%Y-%m-%d')\n",
    "\n",
    "mortgage_rate.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>mortgage_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990-01-01</th>\n",
       "      <td>9.8950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-02-01</th>\n",
       "      <td>10.1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-03-01</th>\n",
       "      <td>10.2680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-04-01</th>\n",
       "      <td>10.3700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-05-01</th>\n",
       "      <td>10.4775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>4.9825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-05-01</th>\n",
       "      <td>5.2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-06-01</th>\n",
       "      <td>5.5220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-07-01</th>\n",
       "      <td>5.4125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-08-01</th>\n",
       "      <td>5.2225</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>392 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            mortgage_rate\n",
       "date                     \n",
       "1990-01-01         9.8950\n",
       "1990-02-01        10.1975\n",
       "1990-03-01        10.2680\n",
       "1990-04-01        10.3700\n",
       "1990-05-01        10.4775\n",
       "...                   ...\n",
       "2022-04-01         4.9825\n",
       "2022-05-01         5.2300\n",
       "2022-06-01         5.5220\n",
       "2022-07-01         5.4125\n",
       "2022-08-01         5.2225\n",
       "\n",
       "[392 rows x 1 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mortgage_rate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Federal Funds Effective Rate](https://fred.stlouisfed.org/series/DFF#0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "fed_fund_rate = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=DFF&scale=left&cosd=1954-07-01&coed=2022-08-19&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Daily%2C%207-Day&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-08-22&revision_date=2022-08-22&nd=1954-07-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renames the columns in the DataFrame to 'date' and 'fed_fund_rate'.\n",
    "fed_fund_rate.columns = ['date', 'fed_fund_rate']\n",
    "\n",
    "# Converts the 'date' column to datetime format with the specified format and sets it as the index.\n",
    "fed_fund_rate['date'] = pd.to_datetime(fed_fund_rate['date'], format='%Y-%m-%d')\n",
    "\n",
    "fed_fund_rate.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>fed_fund_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1954-07-01</th>\n",
       "      <td>1.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1954-07-02</th>\n",
       "      <td>1.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1954-07-03</th>\n",
       "      <td>1.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1954-07-04</th>\n",
       "      <td>1.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1954-07-05</th>\n",
       "      <td>0.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-08-15</th>\n",
       "      <td>2.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-08-16</th>\n",
       "      <td>2.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-08-17</th>\n",
       "      <td>2.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-08-18</th>\n",
       "      <td>2.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-08-19</th>\n",
       "      <td>2.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>24887 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            fed_fund_rate\n",
       "date                     \n",
       "1954-07-01           1.13\n",
       "1954-07-02           1.25\n",
       "1954-07-03           1.25\n",
       "1954-07-04           1.25\n",
       "1954-07-05           0.88\n",
       "...                   ...\n",
       "2022-08-15           2.33\n",
       "2022-08-16           2.33\n",
       "2022-08-17           2.33\n",
       "2022-08-18           2.33\n",
       "2022-08-19           2.33\n",
       "\n",
       "[24887 rows x 1 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fed_fund_rate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* This code fetches Federal Funds Rate data from a remote CSV file, renames the columns, converts the 'date' column to datetime format, and sets 'date' as the index for further analysis, likely related to tracking changes in the Federal Funds Rate over time.\n",
    "\n",
    "\n",
    "### [Delinquency Rate](https://fred.stlouisfed.org/series/DRSFRMACBS) Quaterly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "delinquent_rate = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=DRSFRMACBS&scale=left&cosd=1991-01-01&coed=2022-04-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Quarterly%2C%20End%20of%20Period&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-08-22&revision_date=2022-08-22&nd=1991-01-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "delinquent_rate.columns = ['date', 'delinquent_rate']\n",
    "\n",
    "delinquent_rate['date'] = pd.to_datetime(delinquent_rate['date'], format='%Y-%m-%d')\n",
    "\n",
    "delinquent_rate.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>delinquent_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1991-01-01</th>\n",
       "      <td>3.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991-04-01</th>\n",
       "      <td>3.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991-07-01</th>\n",
       "      <td>3.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991-10-01</th>\n",
       "      <td>3.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992-01-01</th>\n",
       "      <td>3.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-01</th>\n",
       "      <td>2.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-07-01</th>\n",
       "      <td>2.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-10-01</th>\n",
       "      <td>2.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-01</th>\n",
       "      <td>2.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>1.96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>126 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            delinquent_rate\n",
       "date                       \n",
       "1991-01-01             3.09\n",
       "1991-04-01             3.18\n",
       "1991-07-01             3.22\n",
       "1991-10-01             3.28\n",
       "1992-01-01             3.12\n",
       "...                     ...\n",
       "2021-04-01             2.47\n",
       "2021-07-01             2.30\n",
       "2021-10-01             2.28\n",
       "2022-01-01             2.09\n",
       "2022-04-01             1.96\n",
       "\n",
       "[126 rows x 1 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delinquent_rate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  [Real Disposable Personal Income](https://fred.stlouisfed.org/series/DSPIC96#0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "real_disp_income = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=DSPIC96&scale=left&cosd=1959-01-01&coed=2022-06-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-08-22&revision_date=2022-08-22&nd=1959-01-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renames the columns in the DataFrame to 'date' and 'disp_income' for clarity.\n",
    "real_disp_income.columns = ['date', 'disp_income']\n",
    "\n",
    "# Converts the 'date' column to datetime format with the specified format and sets it as the index.\n",
    "real_disp_income['date'] = pd.to_datetime(real_disp_income['date'], format='%Y-%m-%d')\n",
    "\n",
    "real_disp_income.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>disp_income</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-01-01</th>\n",
       "      <td>2191.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-02-01</th>\n",
       "      <td>2198.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-03-01</th>\n",
       "      <td>2210.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-04-01</th>\n",
       "      <td>2225.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-05-01</th>\n",
       "      <td>2237.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-01</th>\n",
       "      <td>15125.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-03-01</th>\n",
       "      <td>15064.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>15055.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-05-01</th>\n",
       "      <td>15036.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-06-01</th>\n",
       "      <td>14973.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>762 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            disp_income\n",
       "date                   \n",
       "1959-01-01       2191.5\n",
       "1959-02-01       2198.1\n",
       "1959-03-01       2210.7\n",
       "1959-04-01       2225.0\n",
       "1959-05-01       2237.1\n",
       "...                 ...\n",
       "2022-02-01      15125.6\n",
       "2022-03-01      15064.1\n",
       "2022-04-01      15055.2\n",
       "2022-05-01      15036.4\n",
       "2022-06-01      14973.1\n",
       "\n",
       "[762 rows x 1 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "real_disp_income"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* This code renames the columns in the DataFrame to 'date' and 'disp_income' for better readability and converts the 'date' column to datetime format, setting it as the index for further analysis, likely related to disposable income data over time.\n",
    "\n",
    "\n",
    "### [Median usual weekly nominal earnings](https://fred.stlouisfed.org/series/LEU0252887700Q#0) Quaterly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "week_earning = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=LEU0252887700Q&scale=left&cosd=2000-01-01&coed=2022-04-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Quarterly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-08-22&revision_date=2022-08-22&nd=2000-01-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "week_earning.columns = ['date', 'week_earning']\n",
    "\n",
    "week_earning['date'] = pd.to_datetime(week_earning['date'], format='%Y-%m-%d')\n",
    "\n",
    "week_earning.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>week_earning</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2000-01-01</th>\n",
       "      <td>603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-04-01</th>\n",
       "      <td>606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-07-01</th>\n",
       "      <td>611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-10-01</th>\n",
       "      <td>614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001-01-01</th>\n",
       "      <td>620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-01</th>\n",
       "      <td>1048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-07-01</th>\n",
       "      <td>1068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-10-01</th>\n",
       "      <td>1069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-01</th>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>1107</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>90 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            week_earning\n",
       "date                    \n",
       "2000-01-01           603\n",
       "2000-04-01           606\n",
       "2000-07-01           611\n",
       "2000-10-01           614\n",
       "2001-01-01           620\n",
       "...                  ...\n",
       "2021-04-01          1048\n",
       "2021-07-01          1068\n",
       "2021-10-01          1069\n",
       "2022-01-01          1100\n",
       "2022-04-01          1107\n",
       "\n",
       "[90 rows x 1 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "week_earning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>week_earning</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2000-01-01</th>\n",
       "      <td>603.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-02-01</th>\n",
       "      <td>604.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-03-01</th>\n",
       "      <td>605.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-04-01</th>\n",
       "      <td>606.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-05-01</th>\n",
       "      <td>607.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-01</th>\n",
       "      <td>1089.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-01</th>\n",
       "      <td>1100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-01</th>\n",
       "      <td>1102.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-03-01</th>\n",
       "      <td>1104.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>1107.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>268 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            week_earning\n",
       "date                    \n",
       "2000-01-01    603.000000\n",
       "2000-02-01    604.000000\n",
       "2000-03-01    605.000000\n",
       "2000-04-01    606.000000\n",
       "2000-05-01    607.666667\n",
       "...                  ...\n",
       "2021-12-01   1089.666667\n",
       "2022-01-01   1100.000000\n",
       "2022-02-01   1102.333333\n",
       "2022-03-01   1104.666667\n",
       "2022-04-01   1107.000000\n",
       "\n",
       "[268 rows x 1 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## linear interpolation to create monthly data\n",
    "\n",
    "week_earning_monthly = week_earning.resample('MS').interpolate()\n",
    "\n",
    "week_earning_monthly"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Personal Saving](https://fred.stlouisfed.org/series/PMSAVE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "per_save = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=PMSAVE&scale=left&cosd=1959-01-01&coed=2022-06-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-08-22&revision_date=2022-08-22&nd=1959-01-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "per_save.columns = ['date', 'pm_save']\n",
    "\n",
    "per_save['date'] = pd.to_datetime(per_save['date'], format='%Y-%m-%d')\n",
    "\n",
    "per_save.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>pm_save</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-01-01</th>\n",
       "      <td>39.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-02-01</th>\n",
       "      <td>37.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-03-01</th>\n",
       "      <td>36.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-04-01</th>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-05-01</th>\n",
       "      <td>38.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-01</th>\n",
       "      <td>813.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-03-01</th>\n",
       "      <td>695.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>651.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-05-01</th>\n",
       "      <td>616.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-06-01</th>\n",
       "      <td>506.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>762 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            pm_save\n",
       "date               \n",
       "1959-01-01     39.6\n",
       "1959-02-01     37.5\n",
       "1959-03-01     36.5\n",
       "1959-04-01     40.0\n",
       "1959-05-01     38.1\n",
       "...             ...\n",
       "2022-02-01    813.3\n",
       "2022-03-01    695.8\n",
       "2022-04-01    651.1\n",
       "2022-05-01    616.0\n",
       "2022-06-01    506.3\n",
       "\n",
       "[762 rows x 1 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "per_save"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Personal Consumption Expenditures Durable Goods](https://fred.stlouisfed.org/series/PCEDG)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "consump_durable = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=PCEDG&scale=left&cosd=1959-01-01&coed=2022-06-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-08-22&revision_date=2022-08-22&nd=1959-01-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "consump_durable.columns = ['date', 'consump_durable']\n",
    "\n",
    "consump_durable['date'] = pd.to_datetime(consump_durable['date'], format='%Y-%m-%d')\n",
    "\n",
    "consump_durable.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>consump_durable</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-01-01</th>\n",
       "      <td>42.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-02-01</th>\n",
       "      <td>44.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-03-01</th>\n",
       "      <td>44.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-04-01</th>\n",
       "      <td>45.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-05-01</th>\n",
       "      <td>45.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-01</th>\n",
       "      <td>2178.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-03-01</th>\n",
       "      <td>2165.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>2191.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-05-01</th>\n",
       "      <td>2164.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-06-01</th>\n",
       "      <td>2189.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>762 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            consump_durable\n",
       "date                       \n",
       "1959-01-01             42.3\n",
       "1959-02-01             44.2\n",
       "1959-03-01             44.4\n",
       "1959-04-01             45.1\n",
       "1959-05-01             45.4\n",
       "...                     ...\n",
       "2022-02-01           2178.5\n",
       "2022-03-01           2165.6\n",
       "2022-04-01           2191.9\n",
       "2022-05-01           2164.2\n",
       "2022-06-01           2189.1\n",
       "\n",
       "[762 rows x 1 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "consump_durable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insights\n",
    "* This code renames the columns in the DataFrame to 'date' and 'consump_durable' for clarity. It then converts the 'date' column to datetime format using the specified format ('%Y-%m-%d') and sets 'date' as the index for further analysis, potentially related to durable goods consumption data over time\n",
    "\n",
    "### [Home Ownership Rate](https://www.huduser.gov/portal/ushmc/hi_HOR.html) Quarterly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "hor_qtr = pd.read_csv(r\"C:\\House_price\\notebook\\data\\hpi_quarterly_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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>hp_idx</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1987-01-01</td>\n",
       "      <td>64.374667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1987-04-01</td>\n",
       "      <td>65.588333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1987-07-01</td>\n",
       "      <td>66.924667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1987-10-01</td>\n",
       "      <td>68.116333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1988-01-01</td>\n",
       "      <td>69.252667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>2021-04-01</td>\n",
       "      <td>253.658000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>2021-07-01</td>\n",
       "      <td>266.828000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>277.493667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>290.924667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>302.264500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>142 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date      hp_idx\n",
       "0    1987-01-01   64.374667\n",
       "1    1987-04-01   65.588333\n",
       "2    1987-07-01   66.924667\n",
       "3    1987-10-01   68.116333\n",
       "4    1988-01-01   69.252667\n",
       "..          ...         ...\n",
       "137  2021-04-01  253.658000\n",
       "138  2021-07-01  266.828000\n",
       "139  2021-10-01  277.493667\n",
       "140  2022-01-01  290.924667\n",
       "141  2022-04-01  302.264500\n",
       "\n",
       "[142 rows x 2 columns]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hor_qtr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### New Homes Sold"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "demand_hud = pd.read_excel('https://www.huduser.gov/portal/periodicals/ushmc/charts/HousingDemand.xlsx',\n",
    "                          sheet_name='Sales_Monthly')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_sold = demand_hud[['Month', 'NewSold_US']].copy()\n",
    "new_sold.columns = ['date', 'new_sold']\n",
    "\n",
    "new_sold['date'] = pd.to_datetime(new_sold['date'])\n",
    "\n",
    "new_sold.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>new_sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1963-01-01</th>\n",
       "      <td>591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-02-01</th>\n",
       "      <td>464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-03-01</th>\n",
       "      <td>461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-04-01</th>\n",
       "      <td>605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-05-01</th>\n",
       "      <td>586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-02-01</th>\n",
       "      <td>625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-03-01</th>\n",
       "      <td>640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-04-01</th>\n",
       "      <td>671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-05-01</th>\n",
       "      <td>715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-01</th>\n",
       "      <td>697</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>726 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            new_sold\n",
       "date                \n",
       "1963-01-01       591\n",
       "1963-02-01       464\n",
       "1963-03-01       461\n",
       "1963-04-01       605\n",
       "1963-05-01       586\n",
       "...              ...\n",
       "2023-02-01       625\n",
       "2023-03-01       640\n",
       "2023-04-01       671\n",
       "2023-05-01       715\n",
       "2023-06-01       697\n",
       "\n",
       "[726 rows x 1 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_sold"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [S&P/Case-Shiller U.S. National Home Price Index](https://fred.stlouisfed.org/series/CSUSHPISA)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "hp_index = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=CSUSHPISA&scale=left&cosd=1987-01-01&coed=2022-05-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-08-23&revision_date=2022-08-23&nd=1987-01-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "hp_index.columns = ['date', 'hp_idx']\n",
    "\n",
    "hp_index['date'] = pd.to_datetime(hp_index['date'])\n",
    "\n",
    "hp_index.set_index(['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>hp_idx</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1987-01-01</th>\n",
       "      <td>63.965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987-02-01</th>\n",
       "      <td>64.424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987-03-01</th>\n",
       "      <td>64.735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987-04-01</th>\n",
       "      <td>65.131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987-05-01</th>\n",
       "      <td>65.563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-01</th>\n",
       "      <td>285.708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-01</th>\n",
       "      <td>290.795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-03-01</th>\n",
       "      <td>296.271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>300.625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-05-01</th>\n",
       "      <td>303.904</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>425 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             hp_idx\n",
       "date               \n",
       "1987-01-01   63.965\n",
       "1987-02-01   64.424\n",
       "1987-03-01   64.735\n",
       "1987-04-01   65.131\n",
       "1987-05-01   65.563\n",
       "...             ...\n",
       "2022-01-01  285.708\n",
       "2022-02-01  290.795\n",
       "2022-03-01  296.271\n",
       "2022-04-01  300.625\n",
       "2022-05-01  303.904\n",
       "\n",
       "[425 rows x 1 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "# downsampling to quarter period index\n",
    "\n",
    "hp_index_qtr = hp_index.resample('QS').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>hp_idx</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1987-01-01</th>\n",
       "      <td>64.374667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987-04-01</th>\n",
       "      <td>65.588333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987-07-01</th>\n",
       "      <td>66.924667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987-10-01</th>\n",
       "      <td>68.116333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1988-01-01</th>\n",
       "      <td>69.252667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-01</th>\n",
       "      <td>253.658000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-07-01</th>\n",
       "      <td>266.828000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-10-01</th>\n",
       "      <td>277.493667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-01</th>\n",
       "      <td>290.924667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-01</th>\n",
       "      <td>302.264500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>142 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                hp_idx\n",
       "date                  \n",
       "1987-01-01   64.374667\n",
       "1987-04-01   65.588333\n",
       "1987-07-01   66.924667\n",
       "1987-10-01   68.116333\n",
       "1988-01-01   69.252667\n",
       "...                ...\n",
       "2021-04-01  253.658000\n",
       "2021-07-01  266.828000\n",
       "2021-10-01  277.493667\n",
       "2022-01-01  290.924667\n",
       "2022-04-01  302.264500\n",
       "\n",
       "[142 rows x 1 columns]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_index_qtr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combining All Above Data\n",
    "\n",
    "#### Monthly dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "### monthly data frame\n",
    "monthly_df = [\n",
    "            permits,\n",
    "            building_permit_value,\n",
    "            new_starts,\n",
    "            completions,\n",
    "            manufacturing,\n",
    "            new_for_sale,\n",
    "            Em_ratio,\n",
    "            population_level,\n",
    "            gdp,\n",
    "            mortgage_rate,\n",
    "            fed_fund_rate,\n",
    "            real_disp_income,\n",
    "            per_save,\n",
    "            consump_durable,\n",
    "            new_sold,\n",
    "            ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_1990 = constr_spend.join(monthly_df, how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_1990 = monthly_1990.loc[:'2020-12-01']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>spend</th>\n",
       "      <th>permits</th>\n",
       "      <th>permit_val</th>\n",
       "      <th>starts</th>\n",
       "      <th>completions</th>\n",
       "      <th>manufactured</th>\n",
       "      <th>new_for_sale</th>\n",
       "      <th>months_supply</th>\n",
       "      <th>emratio</th>\n",
       "      <th>pop_level</th>\n",
       "      <th>gdp</th>\n",
       "      <th>mortgage_rate</th>\n",
       "      <th>fed_fund_rate</th>\n",
       "      <th>disp_income</th>\n",
       "      <th>pm_save</th>\n",
       "      <th>consump_durable</th>\n",
       "      <th>new_sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1993-01-01</th>\n",
       "      <td>194150</td>\n",
       "      <td>989</td>\n",
       "      <td>5383.028</td>\n",
       "      <td>1091</td>\n",
       "      <td>975.0</td>\n",
       "      <td>248</td>\n",
       "      <td>265</td>\n",
       "      <td>5.4</td>\n",
       "      <td>61.4</td>\n",
       "      <td>193962</td>\n",
       "      <td>99.750560</td>\n",
       "      <td>7.9925</td>\n",
       "      <td>2.66</td>\n",
       "      <td>7237.6</td>\n",
       "      <td>425.6</td>\n",
       "      <td>538.1</td>\n",
       "      <td>596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1993-02-01</th>\n",
       "      <td>194689</td>\n",
       "      <td>953</td>\n",
       "      <td>5844.533</td>\n",
       "      <td>1063</td>\n",
       "      <td>1043.0</td>\n",
       "      <td>248</td>\n",
       "      <td>263</td>\n",
       "      <td>5.3</td>\n",
       "      <td>61.4</td>\n",
       "      <td>194108</td>\n",
       "      <td>99.668158</td>\n",
       "      <td>7.6825</td>\n",
       "      <td>3.25</td>\n",
       "      <td>7271.8</td>\n",
       "      <td>441.5</td>\n",
       "      <td>524.2</td>\n",
       "      <td>604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1993-03-01</th>\n",
       "      <td>190185</td>\n",
       "      <td>881</td>\n",
       "      <td>8227.729</td>\n",
       "      <td>950</td>\n",
       "      <td>983.0</td>\n",
       "      <td>241</td>\n",
       "      <td>268</td>\n",
       "      <td>5.4</td>\n",
       "      <td>61.5</td>\n",
       "      <td>194248</td>\n",
       "      <td>99.585598</td>\n",
       "      <td>7.4950</td>\n",
       "      <td>3.65</td>\n",
       "      <td>7249.2</td>\n",
       "      <td>439.0</td>\n",
       "      <td>521.2</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1993-04-01</th>\n",
       "      <td>198296</td>\n",
       "      <td>922</td>\n",
       "      <td>8918.858</td>\n",
       "      <td>1110</td>\n",
       "      <td>1067.0</td>\n",
       "      <td>241</td>\n",
       "      <td>270</td>\n",
       "      <td>4.7</td>\n",
       "      <td>61.5</td>\n",
       "      <td>194398</td>\n",
       "      <td>99.511168</td>\n",
       "      <td>7.4720</td>\n",
       "      <td>3.31</td>\n",
       "      <td>7286.8</td>\n",
       "      <td>436.4</td>\n",
       "      <td>543.0</td>\n",
       "      <td>701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1993-05-01</th>\n",
       "      <td>198835</td>\n",
       "      <td>911</td>\n",
       "      <td>8320.702</td>\n",
       "      <td>1128</td>\n",
       "      <td>973.0</td>\n",
       "      <td>240</td>\n",
       "      <td>273</td>\n",
       "      <td>5.3</td>\n",
       "      <td>61.7</td>\n",
       "      <td>194549</td>\n",
       "      <td>99.450667</td>\n",
       "      <td>7.4650</td>\n",
       "      <td>3.01</td>\n",
       "      <td>7276.3</td>\n",
       "      <td>415.8</td>\n",
       "      <td>552.0</td>\n",
       "      <td>626</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-01</th>\n",
       "      <td>641103</td>\n",
       "      <td>1055</td>\n",
       "      <td>21995.756</td>\n",
       "      <td>1023</td>\n",
       "      <td>895.0</td>\n",
       "      <td>94</td>\n",
       "      <td>286</td>\n",
       "      <td>3.5</td>\n",
       "      <td>56.5</td>\n",
       "      <td>260558</td>\n",
       "      <td>97.297319</td>\n",
       "      <td>2.9350</td>\n",
       "      <td>0.10</td>\n",
       "      <td>15739.2</td>\n",
       "      <td>2714.5</td>\n",
       "      <td>1791.7</td>\n",
       "      <td>1029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-01</th>\n",
       "      <td>662353</td>\n",
       "      <td>1121</td>\n",
       "      <td>23179.135</td>\n",
       "      <td>1105</td>\n",
       "      <td>933.0</td>\n",
       "      <td>96</td>\n",
       "      <td>286</td>\n",
       "      <td>2.5</td>\n",
       "      <td>56.6</td>\n",
       "      <td>260742</td>\n",
       "      <td>97.433292</td>\n",
       "      <td>2.8900</td>\n",
       "      <td>0.09</td>\n",
       "      <td>15799.7</td>\n",
       "      <td>2569.3</td>\n",
       "      <td>1817.8</td>\n",
       "      <td>988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-01</th>\n",
       "      <td>684180</td>\n",
       "      <td>1141</td>\n",
       "      <td>23846.574</td>\n",
       "      <td>1162</td>\n",
       "      <td>898.0</td>\n",
       "      <td>100</td>\n",
       "      <td>284</td>\n",
       "      <td>3.5</td>\n",
       "      <td>57.4</td>\n",
       "      <td>260925</td>\n",
       "      <td>97.572374</td>\n",
       "      <td>2.8340</td>\n",
       "      <td>0.09</td>\n",
       "      <td>15729.1</td>\n",
       "      <td>2462.2</td>\n",
       "      <td>1826.1</td>\n",
       "      <td>1027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-11-01</th>\n",
       "      <td>702717</td>\n",
       "      <td>1155</td>\n",
       "      <td>19839.048</td>\n",
       "      <td>1182</td>\n",
       "      <td>913.0</td>\n",
       "      <td>101</td>\n",
       "      <td>290</td>\n",
       "      <td>4.0</td>\n",
       "      <td>57.4</td>\n",
       "      <td>261085</td>\n",
       "      <td>97.726275</td>\n",
       "      <td>2.7650</td>\n",
       "      <td>0.09</td>\n",
       "      <td>15522.5</td>\n",
       "      <td>2311.5</td>\n",
       "      <td>1803.1</td>\n",
       "      <td>863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-12-01</th>\n",
       "      <td>725025</td>\n",
       "      <td>1233</td>\n",
       "      <td>22196.074</td>\n",
       "      <td>1315</td>\n",
       "      <td>958.0</td>\n",
       "      <td>104</td>\n",
       "      <td>299</td>\n",
       "      <td>3.8</td>\n",
       "      <td>57.4</td>\n",
       "      <td>261230</td>\n",
       "      <td>98.095174</td>\n",
       "      <td>2.6840</td>\n",
       "      <td>0.09</td>\n",
       "      <td>15536.5</td>\n",
       "      <td>2402.8</td>\n",
       "      <td>1797.2</td>\n",
       "      <td>873</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>348 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             spend  permits  permit_val  starts  completions  manufactured  \\\n",
       "date                                                                         \n",
       "1993-01-01  194150      989    5383.028    1091        975.0           248   \n",
       "1993-02-01  194689      953    5844.533    1063       1043.0           248   \n",
       "1993-03-01  190185      881    8227.729     950        983.0           241   \n",
       "1993-04-01  198296      922    8918.858    1110       1067.0           241   \n",
       "1993-05-01  198835      911    8320.702    1128        973.0           240   \n",
       "...            ...      ...         ...     ...          ...           ...   \n",
       "2020-08-01  641103     1055   21995.756    1023        895.0            94   \n",
       "2020-09-01  662353     1121   23179.135    1105        933.0            96   \n",
       "2020-10-01  684180     1141   23846.574    1162        898.0           100   \n",
       "2020-11-01  702717     1155   19839.048    1182        913.0           101   \n",
       "2020-12-01  725025     1233   22196.074    1315        958.0           104   \n",
       "\n",
       "            new_for_sale  months_supply  emratio  pop_level        gdp  \\\n",
       "date                                                                     \n",
       "1993-01-01           265            5.4     61.4     193962  99.750560   \n",
       "1993-02-01           263            5.3     61.4     194108  99.668158   \n",
       "1993-03-01           268            5.4     61.5     194248  99.585598   \n",
       "1993-04-01           270            4.7     61.5     194398  99.511168   \n",
       "1993-05-01           273            5.3     61.7     194549  99.450667   \n",
       "...                  ...            ...      ...        ...        ...   \n",
       "2020-08-01           286            3.5     56.5     260558  97.297319   \n",
       "2020-09-01           286            2.5     56.6     260742  97.433292   \n",
       "2020-10-01           284            3.5     57.4     260925  97.572374   \n",
       "2020-11-01           290            4.0     57.4     261085  97.726275   \n",
       "2020-12-01           299            3.8     57.4     261230  98.095174   \n",
       "\n",
       "            mortgage_rate  fed_fund_rate  disp_income  pm_save  \\\n",
       "date                                                             \n",
       "1993-01-01         7.9925           2.66       7237.6    425.6   \n",
       "1993-02-01         7.6825           3.25       7271.8    441.5   \n",
       "1993-03-01         7.4950           3.65       7249.2    439.0   \n",
       "1993-04-01         7.4720           3.31       7286.8    436.4   \n",
       "1993-05-01         7.4650           3.01       7276.3    415.8   \n",
       "...                   ...            ...          ...      ...   \n",
       "2020-08-01         2.9350           0.10      15739.2   2714.5   \n",
       "2020-09-01         2.8900           0.09      15799.7   2569.3   \n",
       "2020-10-01         2.8340           0.09      15729.1   2462.2   \n",
       "2020-11-01         2.7650           0.09      15522.5   2311.5   \n",
       "2020-12-01         2.6840           0.09      15536.5   2402.8   \n",
       "\n",
       "            consump_durable  new_sold  \n",
       "date                                   \n",
       "1993-01-01            538.1       596  \n",
       "1993-02-01            524.2       604  \n",
       "1993-03-01            521.2       602  \n",
       "1993-04-01            543.0       701  \n",
       "1993-05-01            552.0       626  \n",
       "...                     ...       ...  \n",
       "2020-08-01           1791.7      1029  \n",
       "2020-09-01           1817.8       988  \n",
       "2020-10-01           1826.1      1027  \n",
       "2020-11-01           1803.1       863  \n",
       "2020-12-01           1797.2       873  \n",
       "\n",
       "[348 rows x 17 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monthly_1990"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Quarterly datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "quarter_df = [vacancy_df,\n",
    "\n",
    "            week_earning,\n",
    "\n",
    "            delinquent_rate,\n",
    "\n",
    "            hor_qtr]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "qtr_2000 = vacancy_df.join(quarter_df[1:], how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>rent_vacancy</th>\n",
       "      <th>owner_vacancy</th>\n",
       "      <th>week_earning</th>\n",
       "      <th>delinquent_rate</th>\n",
       "      <th>date</th>\n",
       "      <th>hp_idx</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [rent_vacancy, owner_vacancy, week_earning, delinquent_rate, date, hp_idx]\n",
       "Index: []"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qtr_2000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save Datasets in local folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_the_data(d:pd.DataFrame, name:str, path:str):\n",
    "    file = handle_file(path, name)\n",
    "    d.to_csv(file, index_label='date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_the_data(monthly_1990, 'monthly_data_1990.csv', r'C:\\House_price\\notebook\\data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_the_data(qtr_2000, 'quarterly_data_2000.csv', r'C:\\House_price\\notebook\\data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_the_data(hp_index, 'hpi_monthly_data.csv', r'C:\\House_price\\notebook\\data')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "us",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}