001.Data_Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning \n",
    "Data was sourced from [Zillow](https://www.zillow.com/research/data/). Our data contains location information along with the Zillow Home Value Index (ZHVI). (*From Zillow website: \"A smoothed, seasonally adjusted measure of the typical home value and market changes across a given region and housing type. It reflects the typical value for homes in the 35th to 65th percentile range.\"*)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Zip_zhvi_uc_sfrcondo_tier_0.33_0.67_sm_sa_mon.csv\r\n",
      "timeseries_queens_p.csv\r\n"
     ]
    }
   ],
   "source": [
    "ls DATA"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RegionID</th>\n",
       "      <th>SizeRank</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>RegionType</th>\n",
       "      <th>StateName</th>\n",
       "      <th>State</th>\n",
       "      <th>City</th>\n",
       "      <th>Metro</th>\n",
       "      <th>CountyName</th>\n",
       "      <th>1996-01-31</th>\n",
       "      <th>...</th>\n",
       "      <th>2019-11-30</th>\n",
       "      <th>2019-12-31</th>\n",
       "      <th>2020-01-31</th>\n",
       "      <th>2020-02-29</th>\n",
       "      <th>2020-03-31</th>\n",
       "      <th>2020-04-30</th>\n",
       "      <th>2020-05-31</th>\n",
       "      <th>2020-06-30</th>\n",
       "      <th>2020-07-31</th>\n",
       "      <th>2020-08-31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>61639</td>\n",
       "      <td>0</td>\n",
       "      <td>10025</td>\n",
       "      <td>Zip</td>\n",
       "      <td>NY</td>\n",
       "      <td>NY</td>\n",
       "      <td>New York</td>\n",
       "      <td>New York-Newark-Jersey City</td>\n",
       "      <td>New York County</td>\n",
       "      <td>245762.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1292776.0</td>\n",
       "      <td>1288753.0</td>\n",
       "      <td>1269532.0</td>\n",
       "      <td>1243884.0</td>\n",
       "      <td>1211977.0</td>\n",
       "      <td>1197322.0</td>\n",
       "      <td>1185428.0</td>\n",
       "      <td>1179938.0</td>\n",
       "      <td>1175379.0</td>\n",
       "      <td>1173231.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>84654</td>\n",
       "      <td>1</td>\n",
       "      <td>60657</td>\n",
       "      <td>Zip</td>\n",
       "      <td>IL</td>\n",
       "      <td>IL</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>Chicago-Naperville-Elgin</td>\n",
       "      <td>Cook County</td>\n",
       "      <td>209547.0</td>\n",
       "      <td>...</td>\n",
       "      <td>487111.0</td>\n",
       "      <td>486300.0</td>\n",
       "      <td>486154.0</td>\n",
       "      <td>487283.0</td>\n",
       "      <td>488823.0</td>\n",
       "      <td>489789.0</td>\n",
       "      <td>489865.0</td>\n",
       "      <td>490118.0</td>\n",
       "      <td>491195.0</td>\n",
       "      <td>493022.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>61637</td>\n",
       "      <td>2</td>\n",
       "      <td>10023</td>\n",
       "      <td>Zip</td>\n",
       "      <td>NY</td>\n",
       "      <td>NY</td>\n",
       "      <td>New York</td>\n",
       "      <td>New York-Newark-Jersey City</td>\n",
       "      <td>New York County</td>\n",
       "      <td>230594.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1080810.0</td>\n",
       "      <td>1099111.0</td>\n",
       "      <td>1117633.0</td>\n",
       "      <td>1130101.0</td>\n",
       "      <td>1129983.0</td>\n",
       "      <td>1138594.0</td>\n",
       "      <td>1143043.0</td>\n",
       "      <td>1147409.0</td>\n",
       "      <td>1149477.0</td>\n",
       "      <td>1155724.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 305 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   RegionID  SizeRank  RegionName RegionType StateName State      City  \\\n",
       "0     61639         0       10025        Zip        NY    NY  New York   \n",
       "1     84654         1       60657        Zip        IL    IL   Chicago   \n",
       "2     61637         2       10023        Zip        NY    NY  New York   \n",
       "\n",
       "                         Metro       CountyName  1996-01-31  ...  2019-11-30  \\\n",
       "0  New York-Newark-Jersey City  New York County    245762.0  ...   1292776.0   \n",
       "1     Chicago-Naperville-Elgin      Cook County    209547.0  ...    487111.0   \n",
       "2  New York-Newark-Jersey City  New York County    230594.0  ...   1080810.0   \n",
       "\n",
       "   2019-12-31  2020-01-31  2020-02-29  2020-03-31  2020-04-30  2020-05-31  \\\n",
       "0   1288753.0   1269532.0   1243884.0   1211977.0   1197322.0   1185428.0   \n",
       "1    486300.0    486154.0    487283.0    488823.0    489789.0    489865.0   \n",
       "2   1099111.0   1117633.0   1130101.0   1129983.0   1138594.0   1143043.0   \n",
       "\n",
       "   2020-06-30  2020-07-31  2020-08-31  \n",
       "0   1179938.0   1175379.0   1173231.0  \n",
       "1    490118.0    491195.0    493022.0  \n",
       "2   1147409.0   1149477.0   1155724.0  \n",
       "\n",
       "[3 rows x 305 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('DATA/Zip_zhvi_uc_sfrcondo_tier_0.33_0.67_sm_sa_mon.csv')\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The dataframe is in the wide format. I want it to have a column per region and row per timestamp. I will also filter the location down to include only New York. First let's check if our region's are unique."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export Metadata\n",
    "Let's separate out the region meta info so we can reference it later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "meta = df.iloc[:, 0:9]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import os\n",
    "#os.mkdir('PKL')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# saving meta data\n",
    "with open('PKL/meta.pkl', 'wb') as fp:\n",
    "    pickle.dump(meta, fp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Subset\n",
    "Subsetting to only Queens for now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "queens = df[df.CountyName == 'Queens County']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "meta_cols = list(df.columns[0:9])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "meta_cols.remove('RegionName')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "queens = queens.drop(meta_cols, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(54, 297)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queens.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2003-08-31'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queens.columns[queens.isnull().sum() != 0][-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It seems like we have full data of all queens zipcode starting from 2003 September. Let's cap it at that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "queens = queens.dropna(axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Percentage Increase\n",
    "We are trying to find the best neighborhood to invest in. We can approach this in different ways. I can predict the housing price for coming year then calculate the difference OR I can predict the percent increase for each time point. I'll try the percent increase first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_percent_increase(x1, x2):\n",
    "    return ((x2-x1)/x1)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "queens_p = queens.copy()\n",
    "for i in range(queens_p.shape[1]-1,1, -1):\n",
    "    prior = queens_p.iloc[:, i-1]\n",
    "    current = queens_p.iloc[:, i] \n",
    "    queens_p.iloc[:, i] = calculate_percent_increase(prior, current)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "queens_p = queens_p.drop(['2003-09-30'], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>RegionName</th>\n",
       "      <th>2003-10-31</th>\n",
       "      <th>2003-11-30</th>\n",
       "      <th>2003-12-31</th>\n",
       "      <th>2004-01-31</th>\n",
       "      <th>2004-02-29</th>\n",
       "      <th>2004-03-31</th>\n",
       "      <th>2004-04-30</th>\n",
       "      <th>2004-05-31</th>\n",
       "      <th>2004-06-30</th>\n",
       "      <th>...</th>\n",
       "      <th>2019-11-30</th>\n",
       "      <th>2019-12-31</th>\n",
       "      <th>2020-01-31</th>\n",
       "      <th>2020-02-29</th>\n",
       "      <th>2020-03-31</th>\n",
       "      <th>2020-04-30</th>\n",
       "      <th>2020-05-31</th>\n",
       "      <th>2020-06-30</th>\n",
       "      <th>2020-07-31</th>\n",
       "      <th>2020-08-31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>11375</td>\n",
       "      <td>0.641106</td>\n",
       "      <td>0.913096</td>\n",
       "      <td>0.393119</td>\n",
       "      <td>0.631139</td>\n",
       "      <td>0.435425</td>\n",
       "      <td>0.625393</td>\n",
       "      <td>0.455833</td>\n",
       "      <td>0.666596</td>\n",
       "      <td>1.167856</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.235229</td>\n",
       "      <td>0.303563</td>\n",
       "      <td>-0.195293</td>\n",
       "      <td>0.024489</td>\n",
       "      <td>-0.426299</td>\n",
       "      <td>-0.056890</td>\n",
       "      <td>-0.313316</td>\n",
       "      <td>0.178322</td>\n",
       "      <td>0.533047</td>\n",
       "      <td>0.647460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>11377</td>\n",
       "      <td>0.025764</td>\n",
       "      <td>0.023655</td>\n",
       "      <td>0.921259</td>\n",
       "      <td>1.147181</td>\n",
       "      <td>1.159911</td>\n",
       "      <td>0.756265</td>\n",
       "      <td>0.799079</td>\n",
       "      <td>0.995691</td>\n",
       "      <td>1.538103</td>\n",
       "      <td>...</td>\n",
       "      <td>0.310192</td>\n",
       "      <td>0.464618</td>\n",
       "      <td>1.262602</td>\n",
       "      <td>1.144025</td>\n",
       "      <td>1.365576</td>\n",
       "      <td>1.264332</td>\n",
       "      <td>0.898478</td>\n",
       "      <td>0.549540</td>\n",
       "      <td>0.489825</td>\n",
       "      <td>0.827001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 204 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     RegionName  2003-10-31  2003-11-30  2003-12-31  2004-01-31  2004-02-29  \\\n",
       "20        11375    0.641106    0.913096    0.393119    0.631139    0.435425   \n",
       "108       11377    0.025764    0.023655    0.921259    1.147181    1.159911   \n",
       "\n",
       "     2004-03-31  2004-04-30  2004-05-31  2004-06-30  ...  2019-11-30  \\\n",
       "20     0.625393    0.455833    0.666596    1.167856  ...   -0.235229   \n",
       "108    0.756265    0.799079    0.995691    1.538103  ...    0.310192   \n",
       "\n",
       "     2019-12-31  2020-01-31  2020-02-29  2020-03-31  2020-04-30  2020-05-31  \\\n",
       "20     0.303563   -0.195293    0.024489   -0.426299   -0.056890   -0.313316   \n",
       "108    0.464618    1.262602    1.144025    1.365576    1.264332    0.898478   \n",
       "\n",
       "     2020-06-30  2020-07-31  2020-08-31  \n",
       "20     0.178322    0.533047    0.647460  \n",
       "108    0.549540    0.489825    0.827001  \n",
       "\n",
       "[2 rows x 204 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queens_p.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transpose\n",
    "Now I'll change the format to the wide format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "queens_p = queens_p.set_index('RegionName').transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "queens = queens.set_index('RegionName').transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fix Datetime\n",
    "Let's convert the index to datetime."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "queens_p.index = pd.DatetimeIndex(queens_p.index)\n",
    "queens_p.index = queens_p.index.strftime('%m/%Y')\n",
    "queens_p.columns.name = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "queens.index = pd.DatetimeIndex(queens.index)\n",
    "queens.index = queens.index.strftime('%m/%Y')\n",
    "queens.columns.name = None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting \n",
    "Now let's export the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('PKL/timeseries_queens_p.pkl', 'wb') as fp:\n",
    "    pickle.dump(queens_p, fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('PKL/timeseries_queens.pkl', 'wb') as fp:\n",
    "    pickle.dump(queens, fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "queens_p.to_csv('DATA/timeseries_queens_p.csv', header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}