rental_tabulations.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# import necessary modules and display matplotlib plots inline within the ipython notebook webpage\n",
    "\n",
    "import pandas as pd, numpy as np, statsmodels.api as sm\n",
    "import matplotlib.pyplot as plt, matplotlib.cm as cm, matplotlib.font_manager as fm\n",
    "import matplotlib.mlab as mlab\n",
    "from scipy.stats import pearsonr, ttest_rel\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with open('pg_engine.txt') as f:\n",
    "    pg_engine = f.readlines()\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine(pg_engine[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "import pandas as pd\n",
    "df = pd.read_sql_query('select * from \"rental_listings\"',con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(df.dtypes)\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# convert the date column to yyyy-mm-dd date format\n",
    "df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d')\n",
    "df['week'] = df['date'].dt.week\n",
    "df['month'] = df['date'].dt.month\n",
    "df['year'] = df['date'].dt.year\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_colors(cmap, n, start=0., stop=1., alpha=1., reverse=False):\n",
    "    '''return n-length list of rgba colors from the passed colormap name and alpha,\n",
    "       limit extent by start/stop values and reverse list order if flag is true'''\n",
    "    colors = [cm.get_cmap(cmap)(x) for x in np.linspace(start, stop, n)]\n",
    "    colors = [(r, g, b, alpha) for r, g, b, _ in colors]\n",
    "    return list(reversed(colors)) if reverse else colors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# define the font styles\n",
    "family = 'Arial'\n",
    "title_font = fm.FontProperties(family=family, style='normal', size=18, weight='normal', stretch='normal')\n",
    "label_font = fm.FontProperties(family=family, style='normal', size=16, weight='normal', stretch='normal')\n",
    "ticks_font = fm.FontProperties(family=family, style='normal', size=14, weight='normal', stretch='normal')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# load the 2014 census data set of MSAs\n",
    "census = pd.read_csv('data/census_pop_income.csv', encoding='ISO-8859-1')\n",
    "census['2014_median_income'] = census['2014_median_income'].str.replace(',','').astype(int)\n",
    "census['2014_pop_est'] = census['2014_pop_est'].str.replace(',','').astype(int)\n",
    "census = census.drop(labels='notes', axis=1, inplace=False)\n",
    "census = census.set_index('region')\n",
    "census.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# these are the 15 most populous metros by population, defined by census bureau 2014 estimates\n",
    "most_populous_regions = census['2014_pop_est'].sort_values(ascending=False, inplace=False)\n",
    "print(most_populous_regions.head(15))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "rents = pd.HDFStore('data/rents.h5')\n",
    "rents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['region'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfnew = df.merge(census, left_on='region', right_index=True)\n",
    "dfnew.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfnew['census_name'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfnew['yrmonth'] = dfnew['year']*100+dfnew['month']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#results = dfnew.groupby(['census_name','year', 'month'])['rent_sqft'].quantile([.05, .1, .25, .5, .75, .9, .95])\n",
    "results = dfnew.groupby(['census_name','yrmonth'])['rent_sqft'].quantile(.5)\n",
    "\n",
    "#metro_rent_sqft = results.unstack().reset_index()\n",
    "metro_rent_sqft = results.unstack()\n",
    "\n",
    "metro_rent_sqft.sort_values(by=201702, ascending=False)\n",
    "#metro_rent_sqft.sort_values(by=['census_name','year','month'])\n",
    "#metro_rent_sqft.to_csv('metro_rent_sqft.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bed1 = dfnew[dfnew['bedrooms']==1]\n",
    "bed2 = dfnew[dfnew['bedrooms']==2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n",
    "#results = dfnew.groupby(['census_name','year', 'month'])['rent_sqft'].quantile([.05, .1, .25, .5, .75, .9, .95])\n",
    "results = bed1.groupby(['census_name','yrmonth'])['rent_sqft'].quantile(.5)\n",
    "\n",
    "#metro_rent_sqft = results.unstack().reset_index()\n",
    "metro_rent_sqft_1 = results.unstack()\n",
    "\n",
    "metro_rent_sqft_1.sort_values(by=201702, ascending=False)\n",
    "#metro_rent_sqft.sort_values(by=['census_name','year','month'])\n",
    "metro_rent_sqft_1.to_csv('metro_rent_sqft_1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "results = bed2.groupby(['census_name','yrmonth'])['rent_sqft'].quantile(.5)\n",
    "metro_rent_sqft_2 = results.unstack()\n",
    "metro_rent_sqft_2.sort_values(by=201702, ascending=False)\n",
    "metro_rent_sqft_2.to_csv('metro_rent_sqft_2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "results = bed1.groupby(['census_name', 'yrmonth'])['rent'].quantile(.5)\n",
    "metro_rents_1 = results.unstack()\n",
    "metro_rents_1.sort_values(by=201702, ascending=False)\n",
    "metro_rents_1.to_csv('metro_rents_1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "results = bed2.groupby(['census_name', 'yrmonth'])['rent'].quantile(.5)\n",
    "metro_rents_2 = results.unstack()\n",
    "metro_rents_2.sort_values(by=201702, ascending=False)\n",
    "metro_rents_2.to_csv('metro_rents_2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# function to save images consistently\n",
    "save_dpi = [96, 300]\n",
    "def save_fig(fig, title, tight=True):    \n",
    "    if tight:\n",
    "        fig.tight_layout()\n",
    "    for dpi in save_dpi:\n",
    "        save_folder = 'images/dpi_{}/'.format(dpi)\n",
    "        fig.savefig(save_folder + title, dpi=dpi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# create ticks and tick labels for the time series\n",
    "listings_per_date = df['date'].value_counts()\n",
    "listings_per_date = listings_per_date.sort_index()\n",
    "listings_per_date = listings_per_date.reset_index()\n",
    "xticks = listings_per_date.iloc[range(0, len(listings_per_date), 7)].index\n",
    "xtick_labels = listings_per_date.loc[xticks, 'index']\n",
    "xtick_labels = [str(x).split()[0] for x in xtick_labels]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# plot the total number of listings (includes dupes/re-posts) posted on each day in the data set\n",
    "ax = listings_per_date.plot(kind='line', figsize=[10, 6], ylim=[0,100000], linewidth=3, \n",
    "                            marker='o', markeredgewidth=0, alpha=0.7, color='#003399')\n",
    "ax.grid(True)\n",
    "ax.set_title('Total rental listings posted per day', fontproperties=title_font)\n",
    "ax.set_ylabel('Number of listings posted', fontproperties=label_font)\n",
    "ax.legend_.remove()\n",
    "\n",
    "ax.set_xticks(xticks)\n",
    "ax.set_xticklabels(xtick_labels, rotation=40, rotation_mode='anchor', ha='right', fontproperties=ticks_font)\n",
    "for label in ax.get_yticklabels():\n",
    "    label.set_fontproperties(ticks_font)\n",
    "\n",
    "save_fig(plt.gcf(), 'date_count_listings_posted.png')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#store = pd.HDFStore('data/rents.h5')\n",
    "#store['rents'] = df\n",
    "df.to_hdf('data/rents.h5','rents',append=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "store = pd.HDFStore('data/rents.h5')\n",
    "store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "upper_percentile = 0.998\n",
    "lower_percentile = 0.002\n",
    "\n",
    "# how many rows would be within the upper and lower percentiles?\n",
    "upper = int(len(df) * upper_percentile)\n",
    "lower = int(len(df) * lower_percentile)\n",
    "\n",
    "# get the rent/sqft values at the upper and lower percentiles\n",
    "rent_sqft_sorted = df['rent_sqft'].sort_values(ascending=True, inplace=False)\n",
    "upper_rent_sqft = rent_sqft_sorted.iloc[upper]\n",
    "lower_rent_sqft = rent_sqft_sorted.iloc[lower]\n",
    "\n",
    "# get the rent values at the upper and lower percentiles\n",
    "rent_sorted = df['rent'].sort_values(ascending=True, inplace=False)\n",
    "upper_rent = rent_sorted.iloc[upper]\n",
    "lower_rent = rent_sorted.iloc[lower]\n",
    "\n",
    "# get the sqft values at the upper and lower percentiles\n",
    "sqft_sorted = df['sqft'].sort_values(ascending=True, inplace=False)\n",
    "upper_sqft = sqft_sorted.iloc[upper]\n",
    "lower_sqft = sqft_sorted.iloc[lower]\n",
    "\n",
    "print('valid rent_sqft range:', [lower_rent_sqft, upper_rent_sqft])\n",
    "print('valid rent range:', [lower_rent, upper_rent])\n",
    "print('valid sqft range:', [lower_sqft, upper_sqft])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# create a boolean vector mask to filter out any rows with rent_sqft outside of the reasonable values\n",
    "rent_sqft_mask = (df['rent_sqft'] > lower_rent_sqft) & (df['rent_sqft'] < upper_rent_sqft)\n",
    "\n",
    "# create boolean vector masks to filter out any rows with rent or sqft outside of the reasonable values\n",
    "rent_mask = (df['rent'] > lower_rent) & (df['rent'] < upper_rent)\n",
    "sqft_mask = (df['sqft'] > lower_sqft) & (df['sqft'] < upper_sqft)\n",
    "\n",
    "# filter the thorough listings according to these masks\n",
    "filtered_listings = pd.DataFrame(df[rent_sqft_mask & rent_mask & sqft_mask])\n",
    "len(filtered_listings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "filtered_listings.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sfbay = filtered_listings[filtered_listings['region']=='sfbay']\n",
    "sfbay.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sfbay['rent_sqft'].quantile(.01)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sfbay['sqft'].quantile(.01)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# create a boolean vector mask to filter out any rows with rent_sqft and sqft in Bay Area under 1 percentile\n",
    "sfbay_rent_sqft_mask = (sfbay['rent_sqft'] > sfbay['rent_sqft'].quantile(.01) )\n",
    "\n",
    "# create boolean vector masks to filter out any rows with rent or sqft outside of the reasonable values\n",
    "sfbay_sqft_mask = (sfbay['sqft'] > sfbay['sqft'].quantile(.01) )\n",
    "\n",
    "# filter the thorough listings according to these masks\n",
    "sfbay_filtered = pd.DataFrame(sfbay[sfbay_rent_sqft_mask & sfbay_sqft_mask])\n",
    "len(sfbay_filtered)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sfbay_filtered.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}