puma_aggregation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate Mobility Data to the PUMA level for interactive visualizations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Populating the interactive namespace from numpy and matplotlib\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%pylab inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read in puma shape files\n",
    "\n",
    "ny_shapes = gpd.read_file(os.getcwd()+'/tl_2018_36_puma10/tl_2018_36_puma10.shp')\n",
    "nj_shapes = gpd.read_file(os.getcwd()+'/tl_2018_34_puma10/tl_2018_34_puma10.shp')\n",
    "ill_shapes = gpd.read_file(os.getcwd()+'/tl_2018_17_puma10/tl_2018_17_puma10.shp')\n",
    "cali_shapes = gpd.read_file(os.getcwd()+'/tl_2018_06_puma10/tl_2018_06_puma10.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#concatenate puma shapefiles\n",
    "shapefile_list = [ny_shapes,nj_shapes,ill_shapes,cali_shapes]\n",
    "all_shapes = pd.concat(shapefile_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#change working directory\n",
    "os.chdir(os.getcwd()+'/Data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read in census tract shape files\n",
    "all_ct_shape = gpd.read_file('all_cts.shp')\n",
    "\n",
    "#read in ct level mobility data\n",
    "nyc = pd.read_csv('nyc_vis.csv')\n",
    "chic = pd.read_csv('chic_vis.csv')\n",
    "sf = pd.read_csv('sf_vis.csv')\n",
    "la = pd.read_csv('la_vis.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#spatial join census tracts to puma areas\n",
    "merged_shapes = gpd.sjoin(all_ct_shape,all_shapes,how='right')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "chic_puma = pd.merge(left=chic,right=merged_shapes,how='left',left_on='dest_ct',right_on='GEOID')\n",
    "nyc_puma = pd.merge(left=nyc,right=merged_shapes,how='left',left_on='dest_ct',right_on='GEOID')\n",
    "la_puma = pd.merge(left=la,right=merged_shapes,how='left',left_on='dest_ct',right_on='GEOID')\n",
    "sf_puma = pd.merge(left=sf,right=merged_shapes,how='left',left_on='dest_ct',right_on='GEOID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "#keep only needed columns\n",
    "chic_puma = chic_puma[['origin_ct','dest_ct','od_counts','PUMACE10']]\n",
    "nyc_puma = nyc_puma[['origin_ct','dest_ct','od_counts','PUMACE10']]\n",
    "la_puma = la_puma[['origin_ct','dest_ct','od_counts','PUMACE10']]\n",
    "sf_puma = sf_puma[['origin_ct','dest_ct','od_counts','PUMACE10']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rename puma columns\n",
    "chic_puma.rename({'PUMACE10':'dest_puma'},inplace=True,axis=1)\n",
    "nyc_puma.rename({'PUMACE10':'dest_puma'},inplace=True,axis=1)\n",
    "la_puma.rename({'PUMACE10':'dest_puma'},inplace=True,axis=1)\n",
    "sf_puma.rename({'PUMACE10':'dest_puma'},inplace=True,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "#join again to get origin puma\n",
    "chic_puma = pd.merge(left=chic_puma,right=merged_shapes,how='left',left_on='origin_ct',right_on='GEOID')\n",
    "nyc_puma = pd.merge(left=nyc_puma,right=merged_shapes,how='left',left_on='origin_ct',right_on='GEOID')\n",
    "la_puma = pd.merge(left=la_puma,right=merged_shapes,how='left',left_on='origin_ct',right_on='GEOID')\n",
    "sf_puma = pd.merge(left=sf_puma,right=merged_shapes,how='left',left_on='origin_ct',right_on='GEOID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "chic_puma = chic_puma[['origin_ct','dest_ct','od_counts','dest_puma','PUMACE10']]\n",
    "nyc_puma = nyc_puma[['origin_ct','dest_ct','od_counts','dest_puma','PUMACE10']]\n",
    "la_puma = la_puma[['origin_ct','dest_ct','od_counts','dest_puma','PUMACE10']]\n",
    "sf_puma = sf_puma[['origin_ct','dest_ct','od_counts','dest_puma','PUMACE10']]\n",
    "\n",
    "#rename puma columns\n",
    "chic_puma.rename({'PUMACE10':'origin_puma'},inplace=True,axis=1)\n",
    "nyc_puma.rename({'PUMACE10':'origin_puma'},inplace=True,axis=1)\n",
    "la_puma.rename({'PUMACE10':'origin_puma'},inplace=True,axis=1)\n",
    "sf_puma.rename({'PUMACE10':'origin_puma'},inplace=True,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "#aggregate to puma level\n",
    "chic_puma = pd.DataFrame(chic_puma.groupby(['origin_puma','dest_puma'])['od_counts'].sum())\n",
    "nyc_puma = pd.DataFrame(nyc_puma.groupby(['origin_puma','dest_puma'])['od_counts'].sum())\n",
    "la_puma = pd.DataFrame(la_puma.groupby(['origin_puma','dest_puma'])['od_counts'].sum())\n",
    "sf_puma = pd.DataFrame(sf_puma.groupby(['origin_puma','dest_puma'])['od_counts'].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reset indices\n",
    "chic_puma.reset_index(inplace=True)\n",
    "nyc_puma.reset_index(inplace=True)\n",
    "la_puma.reset_index(inplace=True)\n",
    "sf_puma.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_shapes.to_file(\"puma.geojson\", driver='GeoJSON')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "chic_puma.to_csv('chic_puma.csv')\n",
    "nyc_puma.to_csv('nyc_puma.csv')\n",
    "la_puma.to_csv('la_puma.csv')\n",
    "sf_puma.to_csv('sf_puma.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "deeplearn",
   "language": "python",
   "name": "deeplearn"
  },
  "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.5.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}