Pytables and Plotly in Python/v3

An introduction to PyTables and HDF5 for data analysis in Python.


Note: this page is part of the documentation for version 3 of Plotly.py, which is not the most recent version.
See our Version 4 Migration Guide for information about how to upgrade.

This notebook will give an overview of using the excellent HDF5 Data Format for high performance computing and Plotly to graph data stored in this files. Plotly is a web-based graphing platform that lets you make and share interactive graphs and dashboards. You can use it for free online--sign up for an account here--and on-premise with Chart Studio Enterprise.

For those unfamilar with the HDF5 file format: HDF5 is a data model, library, and file format for storing and managing data. It supports an unlimited variety of datatypes, and is designed for flexible and efficient I/O and for high volume and complex data. HDF5 is portable and is extensible, allowing applications to evolve in their use of HDF5. The HDF5 Technology suite includes tools and applications for managing, manipulating, viewing, and analyzing data in the HDF5 format.

-- The HDF5 Group

The HDF group has some great reasons to use their files - namely that it works great with all kind of data. You can read more here.

In [1]:
import pandas as pd
from IPython.display import display
import plotly.plotly as py # interactive graphing
from plotly.graph_objs import Bar, Scatter, Marker, Layout, Data, Figure, Heatmap, XAxis, YAxis
import plotly.tools as tls
import numpy as np

The dataset that we'll be using is data from NYC's open data portal. We'll be exploring a 100mb dataset covering traffic accidents in NYC. While we are capable of fitting this data into memory, the HDF5 file format has some unique affordances that allow us to query and save data in convenient ways.

Now the first thing we'll want to do is open up an access point to this HDF5 file, doing so is simple because pandas provides ready access to doing so.

In [2]:
pd.set_option('io.hdf.default_format','table')
In [3]:
store = pd.HDFStore('nypd_motors.h5')

Now that we've opened up our store, let's start storing some data

In [4]:
# df = pd.read_csv('NYPD_motor_collisions.csv', parse_dates=['DATE'])
# df.columns = [col.lower().replace(" ", "_") for col in df.columns]
# store.append("nypd", df,format='table',data_columns=True)
In [5]:
store
Out[5]:
<class 'pandas.io.pytables.HDFStore'>
File path: nypd_motors.h5
/nypd            frame_table  (typ->appendable,nrows->596990,ncols->29,indexers->[index],dc->[date,time,borough,zip_code,latitude,longitude,location,on_street_name,cross_street_name,off_street_name,number_of_persons_injured,number_of_persons_killed,number_of_pedestrians_injured,number_of_pedestrians_killed,number_of_cyclist_injured,number_of_cyclist_killed,number_of_motorist_injured,number_of_motorist_killed,contributing_factor_vehicle_1,contributing_factor_vehicle_2,contributing_factor_vehicle_3,contributing_factor_vehicle_4,contributing_factor_vehicle_5,unique_key,vehicle_type_code_1,vehicle_type_code_2,vehicle_type_code_3,vehicle_type_code_4,vehicle_type_code_5])

One thing that's nice about the HDF5 file is that it's kind of like a key value store. It's simple to use, and allows you to store things just like you might in a file system type hierarchy.

What's awesome about the HDF5 format is that it's almost like a miniature file system. It supports hierarchical data and is accessed like a python dictionary.

In [7]:
store.get_storer("df")
In [8]:
store.select("nypd").head()
Out[8]:
date time borough zip_code latitude longitude location on_street_name cross_street_name off_street_name ... contributing_factor_vehicle_2 contributing_factor_vehicle_3 contributing_factor_vehicle_4 contributing_factor_vehicle_5 unique_key vehicle_type_code_1 vehicle_type_code_2 vehicle_type_code_3 vehicle_type_code_4 vehicle_type_code_5
0 2015-06-02 13:48 MANHATTAN 10038 40.711780 -73.999701 (40.7117796, -73.9997006) ST JAMES PLACE MADISON STREET NaN ... Unspecified NaN NaN NaN 3232026 VAN VAN NaN NaN NaN
1 2015-06-02 13:40 NaN NaN NaN NaN NaN NaN NaN NaN ... Turning Improperly NaN NaN NaN 3232021 PASSENGER VEHICLE SPORT UTILITY / STATION WAGON NaN NaN NaN
2 2015-06-02 13:40 NaN NaN NaN NaN NaN NaN NaN 1200 WATERS PLACE - PARKING LOT ... Unspecified NaN NaN NaN 3232261 PASSENGER VEHICLE PASSENGER VEHICLE NaN NaN NaN
3 2015-06-02 13:40 MANHATTAN 10004 40.706701 -74.016047 (40.7067007, -74.0160467) WEST STREET MORRIS STREET NaN ... Unspecified NaN NaN NaN 3232015 UNKNOWN PASSENGER VEHICLE NaN NaN NaN
4 2015-06-02 13:38 NaN NaN NaN NaN NaN WOOLLEY AVENUE GURDON STREET NaN ... Other Vehicular NaN NaN NaN 3233372 PASSENGER VEHICLE PASSENGER VEHICLE NaN NaN NaN

5 rows × 29 columns

In [9]:
boroughs = store.select("nypd", "columns=['borough']")
In [10]:
boroughs['COUNT'] = 1
borough_groups = boroughs.groupby('borough')
In [11]:
borough_groups.sum().index
Out[11]:
Index([u'BRONX', u'BROOKLYN', u'MANHATTAN', u'QUEENS', u'STATEN ISLAND'], dtype='object')
In [12]:
data = Data([Bar(y=borough_groups.sum()['COUNT'], x=borough_groups.sum().index)])
layout = Layout(xaxis=XAxis(title="Borough"), yaxis=YAxis(title='Accident Count'))
fig = Figure(data=data, layout=layout)
In [13]:
py.iplot(fig)
Out[13]:
In [14]:
dates_borough = store.select("nypd", "columns=['date', 'borough']").sort('date')
In [15]:
dates_borough['COUNT'] = 1
In [16]:
date_borough_sum = dates_borough.groupby(['borough', "date"]).sum()
date_borough_sum.head()
Out[16]:
COUNT
borough date
BRONX 2012-07-01 39
2012-07-02 71
2012-07-03 73
2012-07-04 51
2012-07-05 60
In [17]:
data = []
for g, df in date_borough_sum.reset_index().groupby('borough'):
    data.append(Scatter(x= df.date, y=df.COUNT,name=g))
layout = Layout(xaxis=XAxis(title="Date"), yaxis=YAxis(title="Accident Count"))
In [18]:
py.iplot(Figure(data=Data(data), layout=layout), filename='nypd_crashes/over_time')
Out[18]:

Luckily for us, while this graph is a bit of a mess, we can still zoom in on specific times and ranges. This makes plotly perfect for exploring datasets. You can create a high level visual of the data then zoom into a more detailed level.

See below where using the above graph I could zoom in on a particular point and anontate it for future investigation.

In [19]:
tls.embed("https://plotly.com/~bill_chambers/274")
Out[19]:
In [20]:
car_types = store.select("nypd", "columns=['vehicle_type_code_1', 'vehicle_type_code_2']")
car_types['COUNT'] = 1
In [21]:
code_1 = car_types.groupby('vehicle_type_code_1').sum()
code_2 = car_types.groupby('vehicle_type_code_2').sum()
In [22]:
data = Data([
        Bar(x=code_1.index, y=code_1.COUNT,name='First Vehicle Type'),
        Bar(x=code_2.index, y=code_2.COUNT,name='Second Vehicle Type')
     ])
In [23]:
py.iplot(Figure(data=data, layout=Layout(barmode='group', yaxis=YAxis(title="Vehicle Incidents"))))
Out[23]:

No big surprises here, we can see that passenger vehicles, likely being the most prevalent vehicles, are the ones involved in the most accidents for the first and second vehicles. However this does make for some more interesting questions, does this extrapolate to each vehicle class. That is, do all kinds of vehicles hit all other vehicles in more or less the same frequency?

Let's explore large commercial vehicles.

In [24]:
large_vehicles = car_types.groupby(
    'vehicle_type_code_1'
).get_group(
    'LARGE COM VEH(6 OR MORE TIRES)'
).groupby('vehicle_type_code_2').sum()
In [25]:
data = Data([Bar(x=large_vehicles.index,y=large_vehicles.COUNT)])
py.iplot(Figure(data=data, layout=Layout(yaxis=YAxis(title="Incident Per Vehicle Type"))))
Out[25]:

At first glance it seems alright, but it's worth more exploration - let's Z-Score the data and compare their scores.

In [26]:
large_vehicles.head()
Out[26]:
COUNT
vehicle_type_code_2
AMBULANCE 9
BICYCLE 98
BUS 151
FIRE TRUCK 6
LARGE COM VEH(6 OR MORE TIRES) 878
In [27]:
code_2.head()
Out[27]:
COUNT
vehicle_type_code_2
AMBULANCE 842
BICYCLE 13891
BUS 8935
FIRE TRUCK 412
LARGE COM VEH(6 OR MORE TIRES) 10299
In [28]:
def z_score(df):
    df['zscore'] = ((df.COUNT - df.COUNT.mean())/df.COUNT.std())
    return df
In [29]:
data = Data([
        Bar(x=z_score(code_2).index,y=z_score(code_2).zscore, name='All Vehicles'),
        Bar(x=z_score(large_vehicles).index,y=z_score(large_vehicles).zscore,name='Large Vehicles'),

     ])
In [30]:
py.iplot(Figure(data=data, layout=Layout(yaxis=YAxis(title="Incident Per Vehicle Type"))),name='nypd_crashes/large vs all vehicles')
Out[30]:

We can see that things are relatively similar, except that large vehicles seem to hit large vehicles much more than most others. This could warrant further investigation.

While grouped bar charts can be useful for these kinds of comparisons, it can be great to visualize this data with heatmaps as well. We can create one of these by creation a contingency table or cross tabulation.

In [31]:
cont_table = pd.crosstab(car_types['vehicle_type_code_1'], car_types['vehicle_type_code_2']).apply(np.log)

Because of the different magnitudes of data, I decided to log scale it.

In [32]:
py.iplot(Data([
            Heatmap(z = cont_table.values, x=cont_table.columns, y=cont_table.index, colorscale='Jet')
        ]),filename='nypd_crashes/vehicle to vehicle heatmap')
Out[32]:

With this we are able to see more interesting nuances in the data. For instance taxis seems to have lots of accidents with other taxis, while vans and station wagons also seem to have many accidents.

There's clearly a lot to explore in this dataset.