2-Compile_Dataset_Crime_Demographics.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import geopandas as gpd\n",
    "import matplotlib.pyplot as plt\n",
    "from shapely.geometry import Point, Polygon\n",
    "from geopandas.tools import sjoin\n",
    "import pysal as ps\n",
    "import numpy as np\n",
    "%matplotlib inline\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ACS Demographic Data\n",
    "\n",
    "Below, I read in raw demographic variables from ACS Block Group 5-Year Estimates in San Francisco. Data was downloaded from [American Fact Finder](http://factfinder.census.gov/faces/nav/jsf/pages/index.xhtml). I then convert demographic counts to rates and compile them in the same dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pop=pd.read_csv('Data/ACS/ACS_14_5YR_B01001_with_ann.csv',skiprows=2, usecols=[1,3],\\\n",
    "                names=['BGFIPS10','Pop'],dtype={1:pd.np.object}).set_index('BGFIPS10')['Pop']\n",
    "\n",
    "male=pd.read_csv('Data/ACS/ACS_14_5YR_B01001_with_ann.csv',skiprows=2, usecols=[1,5],\\\n",
    "                names=['BGFIPS10','Male'],dtype={1:pd.np.object}).set_index('BGFIPS10')['Male']\n",
    "\n",
    "pov=pd.read_csv('Data/ACS/ACS_14_5YR_B17017_with_ann.csv',skiprows=2, usecols=[1,3,5],\\\n",
    "                names=['BGFIPS10','HH','Pov'],dtype={1:pd.np.object}).set_index('BGFIPS10')\n",
    "\n",
    "\n",
    "hu=pd.read_csv('Data/ACS/ACS_14_5YR_B25001_with_ann.csv',skiprows=2, usecols=[1,3],\\\n",
    "                names=['BGFIPS10','HU'],dtype={1:pd.np.object}).set_index('BGFIPS10')['HU']\n",
    "\n",
    "vacant=pd.read_csv('Data/ACS/ACS_14_5YR_B25004_with_ann.csv',skiprows=2, usecols=[1,3],\\\n",
    "                names=['BGFIPS10','Vacant'],dtype={1:pd.np.object}).set_index('BGFIPS10')['Vacant']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "bgs=gpd.read_file('Data/SF_BlockGroups10.shp').set_index('BGFIPS10')\n",
    "dems=pd.DataFrame(index=bgs.index)\n",
    "dems['Pop']=pop\n",
    "dems['PopDens1k']=pop/(bgs.area/2.59e+6)/1000\n",
    "dems['pMale']=male/pop*100\n",
    "dems['pHHPov']=pov.Pov/pov.HH*100\n",
    "dems['VacantHU']=vacant/hu*100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Crime Data\n",
    "San Francisco crime data was downloaded from their open data portal. The dataset can be found [here](https://data.sfgov.org/Public-Safety/SFPD-Incidents-from-1-January-2003/tmnf-yvry) and contains data on all police incidents since 2003. I exported only those that occurred between January 1, 2010 and December 31, 2014 (to best match the 5-year demographic averages) and only those in the category \"DRUNKENNESS\" (my variable of interest). Fortunately, this data contains x/y coordinates so it's easy to convert them to ```shapely``` geometries and build a GeoDataFrame, which is then spatially joined to block groups in order to generate counts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "drunk=pd.read_csv('Data/SF_Crime_Incidents_2010-2014_Drunkenness.csv')\n",
    "drunk_loc=gpd.GeoDataFrame(geometry=drunk.apply(lambda row:Point(row['X'],row['Y']),1),\\\n",
    "                            crs={'init': 'epsg:4326'}).to_crs(bgs.crs)\n",
    "\n",
    "drunk_counts=sjoin(drunk_loc, \\\n",
    "                        bgs[['geometry']].reset_index()).groupby('BGFIPS10').size().reindex(bgs.index).fillna(0)\n",
    "drunk_counts.name='Drunk'\n",
    "drunk_rate=drunk_counts/dems['Pop']*1000\n",
    "drunk_rate.name='DrunkP1k'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bar Locations\n",
    "I then read in the locations of bars in San Francisco, which was obtained from the California ABC, and then cleaned and geocoded in the [previous notebook](https://github.com/agaidus/Predict_Crime_SF/blob/master/1-California_Alcohol_License_Data_Clean_Geocode.ipynb). Bars are spatially joined to block groups and then aggregated to get counts of bars within block groups. These are then divided by block group area to get bar density."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "bars=gpd.read_file('Data/sf_bar_locations.shp').to_crs(bgs.crs)\n",
    "bar_counts=sjoin(bars, \\\n",
    "                        bgs[['geometry']].reset_index()).groupby('BGFIPS10').size().reindex(bgs.index).fillna(0)\n",
    "bar_counts.name='Bars'\n",
    "bar_dens=bar_counts/(bgs.area/2.59e+6)\n",
    "bar_dens.name='BarPSqMi'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retail Data\n",
    "I want to include some measure of overall retail as a way to control and differentiate the effect of bar density from overall retail density. The SF Data Portal has a dataset of the land use of every parcel within the city. It can be dowloaded [here](https://data.sfgov.org/Housing-and-Buildings/Land-Use/us3s-fp9q). From this dataset, I extracted only those in the land use category \"RETAIL\" and also converted all of the polygon geometries to point centroid geometries, just to reduce file size. I then spatially joined these points to block groups and aggregated to get the count of retail establishments in each block group. These were then divided by block group area to get retail density."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "retail=gpd.read_file('Data/Retail_Land_Use_Parcel_Centroid.shp').to_crs(bgs.crs)\n",
    "retail_counts=sjoin(retail, \\\n",
    "                        bgs[['geometry']].reset_index()).groupby('BGFIPS10').size().reindex(bgs.index).fillna(0)\n",
    "retail_dens=retail_counts/(bgs.area/2.59e+6)\n",
    "retail_dens.name='RetailPSqMi'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bringing it All Together\n",
    "Lastly, I bring the demographic data, the bar location data, the retail data, and the crime data into one dataframe. There is a block group in San Francisco that has 0 population, which results in infinite crime rates and population densities. For this block group, I fill in the infinite values with the mean across all block groups.\n",
    "\n",
    "I then export the data to a CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "gdf=gpd.GeoDataFrame(pd.concat([drunk_counts, drunk_rate,bar_counts, bar_dens, retail_dens,dems],1)\\\n",
    "                     ,geometry=bgs.geometry)\n",
    "gdf.index.name='BGFIPS10'\n",
    "gdf['SqMiles']=gdf.geometry.area/2.59e+6\n",
    "gdf=gdf.replace(np.inf, np.nan)\n",
    "gdf=gdf.fillna(gdf.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Drunk             6.165517\n",
       "DrunkP1k          5.492257\n",
       "Bars              0.743103\n",
       "BarPSqMi         18.810465\n",
       "RetailPSqMi     100.671642\n",
       "Pop            1429.434483\n",
       "PopDens1k        30.116554\n",
       "pMale            50.777812\n",
       "pHHPov           12.613312\n",
       "VacantHU          7.419983\n",
       "SqMiles           0.081697\n",
       "dtype: float64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data=gdf.drop('geometry',1)\n",
    "data.to_csv('InputDataset.csv')"
   ]
  }
 ],
 "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
}