Daniel M Sheehan - Mini Assignment - IPython notebook and CartoDB Crash Data

I missed last Thursday's STAT W4701 class due to being under the weather but was able to check it out via Columbia Video Network. Dr. Malecki posted a mini assignment where we could use NYC Crash Data. I've used this data before for mapping pedestrian and bicycle injuries and fatalities. This data used to be available via Transportation Alternatives: CrashStat but is now delivered via NYC Open Data.

Some Python Pandas in IPython Notebook

Converted to Markdown (of course). See my old blog post Using IPython Notebook with Pandas and exporting to Markdown.

Importing and cleaning the CSV

import pandas as pd

#csv_path = 'https://raw.githubusercontent.com/nygeog/data/master/nyc_crashes/data/NYPD_Motor_Vehicle_Collisions.csv'
inCSV = 'data/NYPD_Motor_Vehicle_Collisions.csv'
ouCSV = 'data/nypd_mv_collisions.csv'

df = pd.read_csv(inCSV).rename(columns=lambda x: x.lower())

#drop ones w/out valid lat #super lazy, just grabbing lat's above 35
df = df[(df.latitude > 35)]
#print df.dtypes 
print len(df.index)
df.head(5)

449644 #count of valid lat's records 

The original CSV data (first 5 records)

Mergin' the date (day) and time (hours)

#create datetime http://stackoverflow.com/questions/17978092/combine-date-and-time-columns-using-python-pandas
df['datetime'] = pd.to_datetime(df['date'] + ' ' + df['time'])


df = df[['datetime','latitude','longitude']]
df = df[(df.datetime > '2014-01-01 00:00:01')] #query out only data from 2014 onward
df = df.sort('datetime')
df.to_csv(ouCSV,index=False)
print len(df.index)

192324 #count of records 2014+

df.head(5)

Clean and small data to import to CartoDB

The table on CartoDB's PostGIS Server

IPython Notebook to Markdown

IPython nbconvert 01-read-crash-data.ipynb --to markdown

Maps in CartoDB

PostGIS SQL Statement to grab 2014 from the 2014-2015/02/13 Table

SELECT * FROM nypd_mv_collisions_2014_20150213 WHERE datetime BETWEEN '2014-01-01 00:00:00' and '2015-01-01 00:00:00'

Density Hex Bins of 2014 Collisions

I'm not totally sure if when creating the classes if it ignores hex bins with no collisions or what. Need to look into that.

Density of 2014 Collisions over time (using Torque)

Animation of Density of Collisions. I think these are Kernel Densities but not sure, need to check CartoDB documentation.

Valentine's Day 2014 Collisions (Clickbait)

I hate Clickbait. :)

Parting Note:

While informative, crash data doesn't really tell us anything interesting about street design and whether specific places are more prone to crashes as without a denominator (such as traffic and/or pedestrian and/or cyclists) its difficult to discern if there's just more targets for collisions (like counting homicides vs. homicides per 10,000 people) or if street design (or in the case of homicides, higher homicide rate) is actually to blame.

New York City has some traffic estimates but nothing we can attribute to every street over the whole city. There may be something that NYC DOT or someone else has, but until its FOIL'ed we can't really create that denominator without more reliable data.

Code:

• IPython Notebook
• As a Python file