tract-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, use QGIS to intersect (or spatial join) census tracts fips data to the listings point data for the metro area of interest. Then run this notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd, json\n",
    "from geopandas import GeoDataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "names = ['ca', 'wa', 'ny']\n",
    "\n",
    "tract_shapes = ['ca_tracts/cb_2014_06_tract_500k', \n",
    "                'wa_tracts/cb_2014_53_tract_500k',\n",
    "                'ny_tracts/cb_2014_36_tract_500k']\n",
    "\n",
    "point_shapes = ['ca_points/ca_points', #sf bay area\n",
    "                'wa_points/wa_points', #seattle\n",
    "                'ny_points/ny_points'] #new york city\n",
    "\n",
    "metro_counties = [['001', '013', '041', '055', '075', '081', '085', '087', '095', '097'], \n",
    "                  ['033'],\n",
    "                  ['005', '047', '061', '081', '085']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# get fips code of state + county + tract\n",
    "def get_tract_fips(row):\n",
    "    return '{}{}{}'.format(row['STATEFP'], row['COUNTYFP'], row['TRACTCE'])\n",
    "\n",
    "# for each tract in the shapefile, pull its attribute value from the passed in data set\n",
    "def get_attribute(tract_fips, data):\n",
    "    if tract_fips in data.index:\n",
    "        return data.loc[tract_fips]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ca: 106,588 points\n",
      "085    46923\n",
      "001    17386\n",
      "013    12514\n",
      "081     8694\n",
      "075     8151\n",
      "095     3944\n",
      "097     2769\n",
      "041     2442\n",
      "087     1225\n",
      "055      650\n",
      "Name: COUNTYFP, dtype: int64\n",
      "8,043 tracts saved to choropleth-maps/ca_tracts.geojson\n",
      "  CA bin 0: $0.34-1.66\n",
      "  CA bin 1: $1.66-2.01\n",
      "  CA bin 2: $2.01-2.36\n",
      "  CA bin 3: $2.36-2.81\n",
      "  CA bin 4: $2.81-5.88\n",
      "\n",
      "wa: 127,492 points\n",
      "033    75077\n",
      "Name: COUNTYFP, dtype: int64\n",
      "1,454 tracts saved to choropleth-maps/wa_tracts.geojson\n",
      "  WA bin 0: $0.64-1.13\n",
      "  WA bin 1: $1.14-1.32\n",
      "  WA bin 2: $1.32-1.50\n",
      "  WA bin 3: $1.51-1.83\n",
      "  WA bin 4: $1.83-2.83\n",
      "\n",
      "ny: 68,029 points\n",
      "061    29257\n",
      "047    18156\n",
      "081     5101\n",
      "005      855\n",
      "085      399\n",
      "Name: COUNTYFP, dtype: int64\n",
      "4,906 tracts saved to choropleth-maps/ny_tracts.geojson\n",
      "  NY bin 0: $0.46-1.79\n",
      "  NY bin 1: $1.79-2.18\n",
      "  NY bin 2: $2.18-2.69\n",
      "  NY bin 3: $2.69-3.46\n",
      "  NY bin 4: $3.46-6.82\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for name, tract_shape, point_shape, counties in zip(names, tract_shapes, point_shapes, metro_counties):\n",
    "    # load the tract shapefiles from the census bureau\n",
    "    tracts = GeoDataFrame.from_file('census_shapefiles/{}.shp'.format(tract_shape))\n",
    "    \n",
    "    # load the filtered data sets\n",
    "    points = GeoDataFrame.from_file('listings/{}.shp'.format(point_shape))\n",
    "    print '{}: {:,} points'.format(name, len(points))\n",
    "    \n",
    "    # retain points only in the desired counties\n",
    "    points = points[points['COUNTYFP'].isin(counties)]\n",
    "    print points['COUNTYFP'].value_counts()\n",
    "    \n",
    "    # get fips code of state + county + tract\n",
    "    grouping = 'tract_fips'\n",
    "    points[grouping] = points.apply(get_tract_fips, axis=1)\n",
    "    tracts[grouping] = tracts.apply(get_tract_fips, axis=1)\n",
    "    \n",
    "    # calculate summary stats per tract, then add them as new columns to the tract shapefile\n",
    "    tracts['median_rent'] = tracts[grouping].apply(get_attribute, data=points.groupby(grouping)['rent'].median())\n",
    "    tracts['median_sqft'] = tracts[grouping].apply(get_attribute, data=points.groupby(grouping)['sqft'].median())\n",
    "    tracts['median_rent_sqft'] = tracts[grouping].apply(get_attribute, data=points.groupby(grouping)['rent_sqft'].median())\n",
    "    tracts['mean_bedrooms'] = tracts[grouping].apply(get_attribute, data=points.groupby(grouping)['bedrooms'].mean())\n",
    "    \n",
    "    # create a new categorical variable 'half' if row is in first or second half of data set by date\n",
    "    points['date'] = pd.to_datetime(points['date'], format='%Y-%m-%d')\n",
    "    points = points.sort_values(by='date')\n",
    "    first_half = points.iloc[:(len(points) / 2)].index\n",
    "    points['half'] = points.index.map(lambda x: '1' if x in first_half else '2')\n",
    "    \n",
    "    # calculate ratio of second half to first half med rent/sqft per tract\n",
    "    halves = points.groupby(['tract_fips', 'half'])['rent_sqft'].median().unstack()\n",
    "    change = halves['2'] / halves['1']\n",
    "    tracts['change_median_rent_sqft'] = tracts[grouping].apply(get_attribute, data=change)\n",
    "    \n",
    "    # create categorical variables for median rent/sqft and change in median rent/sqft over time\n",
    "    num_bins = 5\n",
    "    bin_labels = range(num_bins)\n",
    "    tracts['median_rent_sqft_bin'] = pd.qcut(x=tracts['median_rent_sqft'], q=num_bins, labels=bin_labels)\n",
    "    tracts['change_median_rent_sqft_bin'] = pd.qcut(x=tracts['change_median_rent_sqft'], q=num_bins, labels=bin_labels)\n",
    "    \n",
    "    # dump the tract shapefile to geojson string\n",
    "    geojson = tracts.to_json()\n",
    "    \n",
    "    # save the geojson to file\n",
    "    filename = 'choropleth-maps/{}_tracts.geojson'.format(name)\n",
    "    with open(filename, 'wb') as output_file:\n",
    "        output_file.write(geojson)\n",
    "    print '{:,} tracts saved to {}'.format(len(json.loads(geojson)['features']), filename)\n",
    "    \n",
    "    # get the quantiles' value ranges\n",
    "    for cat in tracts['median_rent_sqft_bin'].sort_values().unique():\n",
    "        if cat >= 0:\n",
    "            min_val = tracts[tracts['median_rent_sqft_bin']==cat]['median_rent_sqft'].min()\n",
    "            max_val = tracts[tracts['median_rent_sqft_bin']==cat]['median_rent_sqft'].max()\n",
    "            print '  {} bin {}: ${:.2f}-{:.2f}'.format(name.upper(), int(cat), min_val, max_val)\n",
    "    print ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}