Practical Data Analysis in Python - Grouping and Aggregating Data¶

Grouping and querying data¶

Now we will slice and dice our dataset using grouping, aggregation functions, summary statistics, and various querying and indexing operations. To really understand the bike sharing data, we need to look at natural groupings such as starting landmark, subscription type, and the time of day of the start of a bicycle trip. The work done in this section will set up some beautiful and informative plots in the next section of the analysis. Most of the tables below have plotting representations in the visualization section.

Grouping and aggregating or reducing data and obtaining summary measures and statistics is the heart of data analysis and work together with the visualization process including those of an exploratory nature. The iterative process of data discovery at best leads to new questions and insights being discovered which inform and refine the data carpentry process. This is where the interactive repl excels. Think of it as a circular inquistive process of data munging, grouping and aggregating, and visualizing. We will explore using SQL inspired methods of grouping operations in Pandas and the use of mult-indexes and hierarchial indexing.

import numpy as np
import pandas as pd
from datetime import datetime, time

%matplotlib inline
import seaborn as sns
from ggplot import *

print pd.__version__
print np.__version__

0.14.1
1.9.0

%load_ext ipycache

Grouping and aggregating¶

It's time to explore various ways of grouping and aggregating data. Let's review some terminology. Dockcount refers to the number of docks installed, subscription type is either customer or subscriber, and landmark refers to an area covered by San Francisco, San Jose, Redwood City, or Palo Alto.

In the data munging notebook, we created a column indicating whether the duration of a segment of a bicycle trip was under thirty minutes or over thirty minutes as this is the alloted time before being charged overtime fees. So, what percentage of total trips were over thirty minutes?

What percentage of total trips were over thirty minutes?¶

dmerge4.thirtymin.value_counts()

in_thirty      134912
over_thirty      9103
dtype: int64

Only 6% of total rides had a duration over thirty minutes so it looks like people are good about returning bikes on time. Or, were they? When we look at the statistics based on landmark and subscriber type, we will get a better picture of who and where have longer duration times.

How does average duration vary by time of day for trips both under thirty minutes and over thirty minutes?¶

dmerge4.groupby(['thirtymin','timeofday'])[['duration_f']].mean()

Here is another way to check on how many observations out of the total had trip durations over thirty minutes

len(dmerge4[dmerge4['thirtymin']=='over_thirty'])

9103

How many trips began and ended at the same station?¶

Only 6878 out 144015 observations or 4.8% were round trips so it would seem that people are really using the bicycles to get from point A to point B

len(dmerge4[dmerge4['startstation']== dmerge4['endstation']])

6878

Let's look at average duration by subscription type. Customers are defined has having either a twenty-four hour or three day pass while Subscribers are defined as having an annual pass. Average durations for subscribers is almost ten minutes while it is sixty minutes for customers. Perhaps customers are recreational riders who take longer trips while subscribers are shorter distance commuters. We'll learn more when we slice and dice the data later.

What is average duration by subscription type?¶

dmerge4.groupby(['subscriptiontype']).agg({'duration_f' : np.mean})

How many observations in the dataset belong to each subscription type?¶

dmerge4.groupby('subscriptiontype').size()

subscriptiontype
Customer             30368
Subscriber          113647
dtype: int64

What landmark occurs the most frequently among subscription type of subscribers?¶

dmerge4[dmerge4['subscriptiontype'] =="Subscriber"].landmark.value_counts()

San Francisco    102735
San Jose           7219
Mountain View      2157
Palo Alto           905
Redwood City        631
dtype: int64

So, we've learned that the majority of total subscribers are in San Francisco and take very short trips.

What are the top five start stations?¶

dmerge4.groupby('startstation').duration.sum().order(ascending=False)[:5]

startstation
Harry Bridges Plaza (Ferry Building)        12579544
Embarcadero at Sansome                      10294998
San Francisco Caltrain (Townsend at 4th)     8457338
Market at 4th                                7706843
Powell Street BART                           6100023
Name: duration, dtype: int64

What landmark has the highest total duration?¶

The table below indicates that San Francisco had the highest total duration but keep in mind that the majority of total observations are also in San Francisco so of course the total would be higher. Using summary statistics will give a better idea of bicycling behavior than looking at total values. Although, for some purposes, it is good to know the total as well. For example, bicycle wear and tear based on total durations.

dmerge4.groupby("landmark")['duration_i','subscriptiontype'].aggregate(np.sum)

%%cache station.pkl stationdata
stationdata

What percentage of total bicycle docks area located in each landmark?¶

54% of all docks are located in San Francisco so this may partially explain why total duration and bicycle trips taken in San Francisco are higher and why durations are higher in Palo Alto where only 6% of the docks are located.

stationdata.groupby('landmark')[['dockcount']].sum()/stationdata[['dockcount']].sum()

What are the dockcounts by landmark and station name?¶

stationdata.groupby(['landmark','name'])[['dockcount']].sum()

What are the total number of bicycle trips by landmark?¶

When looking at number of bicycle trips based on landmark, we see that 129,853 or 90% of total observations in the dataset were in San Francisco. 6% of total observations were in San Jose while only 1% were in Palo Alto.

dmerge4.groupby("landmark")[['duration_i']].aggregate(np.size)

What are average durations by landmark?¶

Looking at average duration by landmark, San Francisco has the lowest duration followed by San Jose while Palo Alto had the highest duration. We can hypothesize that this may be partly due to fewer docks in Palo Alto or greater distances to travel as well as the subscription profile of Palo Alto bicycle users.

dmerge4.groupby("landmark")[['duration_i']].aggregate(np.mean)

What are average duration and dockcount by landmark and time of day for first six months of the bike share system?¶

timelandmark = dmerge4.groupby(['timeofday','landmark']).agg({'dockcount' : np.mean, 'duration_f': np.mean,  }).reset_index()
timelandmark

timelandmark.dockcount.max()

Where and at when was the highest average dockcount?¶

The highest dockcount was in Redwood City in the morning.

timelandmark.dockcount.max()

22.647058823529413

What are the top five longest average duration grouped by time of day and landmark?¶

The highest average duration was in Palo Alto in the wee hours.

timelandmark.duration_f.order(ascending=False)[:5]

21    150.501852
24    127.363475
15    126.402177
6     109.684100
16     83.482982
Name: duration_f, dtype: float64

What are the average dockcount and durations by time of day and landmark for the first six months of the bike share system?¶

Pandas produces nice readable multi-index tables.

np.round(dmerge4.groupby(['timeofday','landmark']).agg({'dockcount' : np.mean, 'duration_i': np.mean,  }))

Grouping and aggregating time-series data¶

Let's obtain mean values by day of the month by creating a grouped object from the day component of the timestamp and applying an aggregation function. We want to know if there is higher demand or trip duration at certain days of the month.

day_means = dmerge4.groupby('day').aggregate(np.mean)
day_means

We can also create a grouped object using Pandas grouper. We can actually specify a resampling and a grouping using the Grouper. So, in this case I want the frequency to be monthly and group by landmark and subscription type.

three_grouper = dmerge4.groupby([pd.Grouper(freq='M',key='startdate'),"landmark","subscriptiontype"])[['duration_f']].mean().reset_index()
three_grouper

By unstacking the data created in the grouper object, we now have duration by customer and subscriber by landmark. This is a nice way to present the data in tabular format.

three_grouper_unstack = dmerge4.groupby([pd.Grouper(freq='M',key='startdate'),"landmark","subscriptiontype"])[['duration']].mean().unstack().reset_index()
three_grouper_unstack.head()

three_grouper_unstack = dmerge4.groupby([pd.Grouper(freq='M',key='startdate'),"landmark","subscriptiontype"])[['duration']].mean().unstack()
three_grouper_unstack.head()

Average duration by month faceted by landmark

grouperl = dmerge4.groupby([pd.Grouper(freq='M',key='startdate'),'landmark']).mean()[['duration_f']]
grouperl.head(10)

Grouping on a variable with many values is also possible. In this case, we can see summary statistics on every single duration as an example.

dmerge4.groupby([pd.Grouper(freq='M',key='startdate'),'duration_f']).sum()[:5]

We can be very specific and the level of granularity can be very detailed. We can pinpoint the average duration for a very specific profile or demographic which can be very powerful for a data-driven decision making including marketing or civic infrastructure goals for the bike sharing system.

This table gives an indication of monthly durations based on starting landmark and endstation. In other words, we get an idea of how long it takes to reach by bicycle and the differences between customers and subscribers all in one table. We can identify which customers are keeping the bicycles for longer periods of time.

np.round(dmerge4.groupby([pd.Grouper(freq='M',key='startdate'),"landmark","subscriptiontype","endstation"])[['duration_f']].mean())

Let's look at the breakdown of average duration by both landmark and subscription type. We can see some clear differences between customers and subscribers. In most cases, subscribers are taking short trips.

np.round(dmerge4.groupby([pd.Grouper(freq='M',key='startdate'),"landmark","subscriptiontype"])[['duration_f']].mean()).unstack()

Let's look at each level of duration on an hourly basis.

dmerge4.groupby([pd.Grouper(freq='H',key='startdate'),'duration_f']).mean().head()

In this table, we are looking at hourly data by landmark.

hourlytry = dmerge4.groupby([pd.Grouper(freq='H',key='startdate'),'landmark'],as_index=False)[['duration_f']].mean()
hourlytry

Group operations on a datetime index

dmerge4.groupby(dmerge4.index.month).agg(['count','sum','mean','min','max'])[['duration_f']]

Let's make a table of average dockcount by each precipitation level and subscription type

i = pd.DataFrame(dmerge4.groupby(['precipitation_in','subscriptiontype']).dockcount.mean()).reset_index()
i

Whate are the total number of observations in each of the initial six months. Other than first month of operation, the distribution of observations distribution is pretty evenly spread out.

dmerge4.groupby(dmerge4.index.month).size()

1     24428
2     19024
8      2102
9     25243
10    29105
11    24219
12    19894
dtype: int64

dmerge4.groupby(dmerge4.index.month).size().order(ascending=False)[:5]

10    29105
9     25243
1     24428
11    24219
12    19894
dtype: int64

Working with multi-level indexes¶

Let's create a multi-level index without grouping because we'll often want to perform queries on the full dataset.

dmerge4twolevels = dmerge4.set_index(['landmark','subscriptiontype'])
dmerge4twolevels

Creates a groupby object dataframe on which aggregate actions can be performed and can group by the multiindexes

#creates a groupby object dataframe and can group by the multiindexes
grouplevel0 = dmerge4twolevels.groupby(level=0)
grouplevel0

<pandas.core.groupby.DataFrameGroupBy object at 0x7ffbc802c8d0>

grouplevel0.mean()
#same result as dmerge4.groupby('landmark').mean()

grouplevel1 = dmerge4twolevels.groupby(level=1)
grouplevel1

<pandas.core.groupby.DataFrameGroupBy object at 0x7ffb96e0db90>

grouplevel1.mean()

The level can also be specified by name

#can also specify level by name (same as above)
dmerge4twolevels.groupby(level='subscriptiontype').sum()
#dmerge4twolevels.sum(level='subscriptiontype')  #same as above

Create the level without dropping it as a column

#don't drop the level as a column; another way to select the level of a multi-level index
dmerge4twolevels.xs('Subscriber', level='subscriptiontype',drop_level=False)

Querying data¶

By setting up the initial query, We can then change the inputs for interactive reporting of results. We can answer very specific questions by querying.

Let's created a grouped object, apply aggregate functions, and then query the data. In this table, we have the average duration for subscription type of customer only.

grouped1 = dmerge4.groupby(['landmark','subscriptiontype'])
group2 = grouped1[['duration']].agg([np.mean, np.std])
group2
query1 = group2.query('subscriptiontype == "Customer"')
query1

Return a dataset from a multi-index dataframe that contains only startterminal number 62.

#subseting and indexing a non-grouped multi index:
dmerge4twolevels.query('startterminal == 62')

Query a multi-index dataframe for subscriptiontype of subscriber.

dmerge4twolevels.query('subscriptiontype == "Subscriber"')

What is the average duration in descending order by endstation when the startterminal is number 62?¶

We can get this type of granular data by querying. By setting up the initial query, we can then change the inputs for interactive reporting of results.

duration62 = dmerge4twolevels.query('startterminal == 62').groupby('endstation')['duration_f'].mean().order(ascending=False)
duration62

endstation
2nd at Folsom                                    84.510405
Beale at Market                                  28.968750
Civic Center BART (7th at Market)                25.788571
Embarcadero at Vallejo                           22.056000
Powell at Post (Union Square)                    21.702200
Embarcadero at Sansome                           20.633906
Mechanics Plaza (Market at Battery)              18.325526
Golden Gate at Polk                              18.093333
San Francisco Caltrain (Townsend at 4th)         17.642372
South Van Ness at Market                         16.725909
Commercial at Montgomery                         15.988511
Market at 10th                                   15.887500
Townsend at 7th                                  15.233402
Grant Avenue at Columbus Avenue                  15.224000
San Francisco City Hall                          14.389286
Davis at Jackson                                 11.523784
Broadway St at Battery St                        11.013333
Washington at Kearney                            10.850811
Harry Bridges Plaza (Ferry Building)             10.394581
5th at Howard                                     9.919462
Powell Street BART                                9.627881
Clay at Battery                                   9.399346
Post at Kearney                                   8.780957
Steuart at Market                                 8.627549
Embarcadero at Bryant                             8.034000
Yerba Buena Center of the Arts (3rd @ Howard)     7.567119
Market at 4th                                     7.431879
San Francisco Caltrain 2 (330 Townsend)           7.380276
Embarcadero at Folsom                             5.832000
Temporary Transbay Terminal (Howard at Beale)     5.819636
Spear at Folsom                                   5.556141
2nd at South Park                                 5.551429
2nd at Townsend                                   4.662283
Market at Sansome                                 4.334730
Howard at 2nd                                     3.326883
Name: duration_f, dtype: float64

Merging and concatenating¶

Let's concatenate the morning monthly and evening monthly created in the time series section into a dataframe for the plotting notebook.

concatenated = pd.concat([morning_monthly,evening_monthly], keys=['morning', 'evening'],axis=1)
concatenated

Merge same named column from two datasets but keep both columns but rename them with a suffix

merge = pd.merge(morning_monthly,evening_monthly,how='outer',left_index=True, right_index=True,suffixes=['_morning', '_evening'])
merge

Ad-hoc data summary or string operations¶

Use a lambda function to get summary statistics of average duration by landmark. The maximum average duration was in San Jose, the highest average duration was in Palo Alto and the lowest was in San Francisco.

dmerge4.groupby("landmark")[['duration_f']].apply(lambda x: x.describe()).unstack()

Make a grouped object without making it the index.

Another use of the lambda function is to map a function. For example sake, to check the length of a string.

#apply a lambda function
f = lambda x: len(str(x))
dmerge4[['landmark']].applymap(f)[:3]

Here is another string operation by way of mapping a lambda function. Very useful.

dmerge4['endstation'].map(lambda x: x.startswith('Grant')).head()

startdate
2013-08-29 15:11:00    False
2013-08-29 17:35:00    False
2013-08-29 20:00:00    False
2013-08-29 17:30:00    False
2013-08-29 19:07:00    False
Name: endstation, dtype: bool

Make a grouped object without making it the index.

dmerge4.groupby('landmark', as_index=False).mean()

		duration_f
thirtymin	timeofday
in_thirty	evening	9.736020
	mid_day	10.387775
	morning	8.914918
	night	9.641814
	wee_hours	10.150168
over_thirty	evening	161.593282
	mid_day	164.873060
	morning	218.527759
	night	271.402983
	wee_hours	345.829388

	timeofday	landmark	duration_f	dockcount
0	evening	Mountain View	34.096655	18.079393
1	evening	Palo Alto	66.580671	14.565495
2	evening	Redwood City	50.191700	18.198381
3	evening	San Francisco	16.224655	19.349130
4	evening	San Jose	18.737437	17.385234
5	mid_day	Mountain View	82.030353	18.465995
6	mid_day	Palo Alto	109.684100	14.439331
7	mid_day	Redwood City	76.925088	17.982456
8	mid_day	San Francisco	27.520457	19.436506
9	mid_day	San Jose	23.002496	16.776488
10	morning	Mountain View	29.786291	19.715105
11	morning	Palo Alto	52.318829	16.978308
12	morning	Redwood City	22.378051	22.647059
13	morning	San Francisco	15.172821	19.948991
14	morning	San Jose	18.945759	19.782161
15	night	Mountain View	126.402177	20.061224
16	night	Palo Alto	83.482982	14.017544
17	night	Redwood City	65.530455	20.000000
18	night	San Francisco	19.055272	19.432370
19	night	San Jose	35.876195	18.286119
20	wee_hours	Mountain View	81.724706	21.117647
21	wee_hours	Palo Alto	150.501852	16.185185
22	wee_hours	Redwood City	13.585000	20.000000
23	wee_hours	San Francisco	47.574147	18.926380
24	wee_hours	San Jose	127.363475	17.474576

	tripid	duration	startterminal	endterminal	bike	max_temperature_f	mean_temperature_f	min_temperaturef	max_dew_point_f	meandew_point_f	…	station_id	lat	long	dockcount	hour	month	hourdiff	durationminutes	duration_i	duration_f
day
1	73878.288738	1816.236080	56.575208	56.547493	432.526833	69.336105	60.620559	51.125976	51.413706	46.602923	…	56.575208	37.743179	-122.356093	19.177879	13.309146	8.684051	0.312169	30.270601	30.271353	30.270668
2	80351.443674	1273.880168	57.369151	57.382025	433.812825	66.287447	58.723942	50.482298	51.430800	47.488240	…	57.369151	37.747079	-122.360427	19.446150	13.231245	8.443179	0.175043	21.231336	21.227532	21.231300
3	98238.235528	1135.100522	57.273354	57.356113	436.655799	63.523302	56.361338	49.060397	47.898433	42.093626	…	57.273354	37.743680	-122.357780	19.405016	13.084222	7.262696	0.238662	18.918342	18.918704	18.918305
4	94721.030403	1101.818939	57.161696	57.186871	437.853021	64.680093	56.829783	48.094694	44.157242	39.132843	…	57.161696	37.742673	-122.356006	19.465918	13.127808	8.324942	0.201588	18.363649	18.365414	18.363654
5	95326.050161	1173.435091	56.902045	56.801292	432.594833	65.471259	56.496878	47.178902	44.220883	37.509580	…	56.902045	37.742166	-122.354954	19.422390	13.254682	8.448439	0.231001	19.557252	19.561895	19.557175
6	94048.015109	1202.085258	57.029570	57.127131	439.644507	68.017052	58.334556	48.195985	49.679258	42.862724	…	57.029570	37.744666	-122.357646	19.334988	13.356357	7.755666	0.209152	20.034754	20.031945	20.034757
7	89738.111635	1146.163488	57.201200	57.143561	438.932719	71.251125	62.068138	52.430684	52.893936	45.455967	…	57.201200	37.745178	-122.358731	19.379687	13.105207	7.541033	0.214699	19.102725	19.097493	19.102704
8	84325.793095	1386.051549	57.085836	57.125325	436.400804	67.203358	59.201939	50.980137	51.354694	47.276425	…	57.085836	37.746047	-122.359317	19.342871	13.099551	8.004493	0.278080	23.100859	23.105226	23.100865
9	87579.505991	1144.179954	57.035023	57.111521	439.260829	64.475576	56.658986	48.722811	47.882949	43.535714	…	57.035023	37.744060	-122.357155	19.301843	13.283641	8.159217	0.232028	19.069666	19.067512	19.069680
10	100164.634270	941.555230	57.384660	57.602459	434.971507	63.406518	56.257026	48.694184	49.707650	46.169789	…	57.384660	37.746870	-122.359709	19.375878	13.193404	7.282397	0.194770	15.692587	15.689500	15.692576
11	96293.107869	982.990743	57.493862	57.524452	436.820487	62.358221	56.467498	50.252767	48.957939	46.354397	…	57.493862	37.746363	-122.359027	19.456027	13.155162	8.425639	0.219763	16.383179	16.385389	16.383139
12	101958.670921	1180.773218	57.565482	57.644021	441.782250	63.251522	56.464559	49.364422	50.882388	46.859415	…	57.565482	37.748906	-122.361073	19.408404	13.290006	8.206362	0.233458	19.679554	19.679560	19.679543
13	108374.918449	1113.472481	57.505760	57.708173	439.695922	65.535564	57.216310	48.298775	50.977144	46.897605	…	57.505760	37.747396	-122.361078	19.364600	13.081185	7.314683	0.224173	18.557875	18.563357	18.557855
14	107914.239321	1331.676583	57.442601	57.464912	435.837338	68.113272	59.215294	50.194889	49.682113	45.583715	…	57.442601	37.747588	-122.360382	19.458810	13.210717	7.004195	0.222349	22.194610	22.194699	22.194594
15	100907.198866	1433.743385	57.403612	57.506510	440.304704	71.000420	61.042839	50.341033	47.715456	41.582108	…	57.403612	37.751215	-122.364489	19.319194	13.206636	7.464301	0.265855	23.895723	23.896472	23.895792
16	101172.588106	1202.279385	58.006985	57.971463	437.107763	71.047895	60.637597	49.693474	49.862303	42.463381	…	58.006985	37.750837	-122.364332	19.475354	13.297945	7.656955	0.217322	20.037990	20.040511	20.038014
17	99680.420043	1112.376430	57.745106	57.859081	438.638884	68.343090	59.417523	49.817794	49.477418	44.306842	…	57.745106	37.749572	-122.362625	19.422563	13.311882	7.760225	0.187633	18.539607	18.532468	18.539643
18	103648.507761	1175.414987	57.896343	57.921677	439.719536	65.938805	57.212310	48.155219	50.161285	45.867440	…	57.896343	37.749978	-122.363236	19.535593	13.156824	8.155754	0.191258	19.590250	19.589652	19.590268
19	109548.449822	1014.061177	57.204903	57.208045	434.562958	67.482296	59.006704	50.093861	51.146868	45.393882	…	57.204903	37.745790	-122.358802	19.439556	12.929395	7.885397	0.254766	16.901020	16.898387	16.901001
20	112970.702050	1087.413808	56.822654	57.027832	436.416613	65.947573	56.888457	47.428479	50.418554	45.236893	…	56.822654	37.740981	-122.353057	19.434520	13.388997	7.395901	0.189213	18.123563	18.130744	18.123556
21	121094.538382	954.694196	56.866029	56.913252	435.960266	65.016434	56.554816	47.660495	49.190347	43.927606	…	56.866029	37.740547	-122.353294	19.523403	13.082172	6.843561	0.169128	15.911570	15.910547	15.911610
22	111480.550011	1387.006294	56.852102	57.083390	429.968982	66.873455	58.093504	48.799506	47.600135	42.593392	…	56.852102	37.743398	-122.355859	19.332209	13.213082	7.225893	0.271297	23.116772	23.113059	23.116777
23	104470.365624	1258.963995	56.603943	56.477925	435.362195	67.403986	58.234248	48.505787	49.957565	44.531719	…	56.603943	37.737760	-122.349702	19.325761	13.089156	7.512859	0.253751	20.982733	20.980283	20.982722
24	114953.246078	1067.051404	57.187242	57.069777	437.834228	65.759496	57.860652	49.440132	50.049752	46.008258	…	57.187242	37.741583	-122.354357	19.540875	13.002064	6.421965	0.200041	17.784190	17.784476	17.784242
25	107939.261435	1299.720000	57.650335	57.680383	440.331866	64.618756	57.094163	48.879234	49.399043	45.445550	…	57.650335	37.750310	-122.363495	19.331866	13.258947	7.657225	0.262201	21.662000	21.659522	21.661989
26	101270.468742	1384.907149	57.155632	57.270905	438.265102	66.128198	57.792931	49.027697	48.403060	44.431285	…	57.155632	37.744375	-122.357659	19.326035	13.073068	8.448958	0.225007	23.081786	23.084674	23.081862
27	121824.804303	1052.402609	57.767910	57.717098	432.240787	65.005264	57.621881	50.223850	48.471275	45.037308	…	57.767910	37.747197	-122.359888	19.470588	13.196384	6.535134	0.177386	17.540043	17.534218	17.540053
28	120628.722106	1437.819277	57.629387	57.609481	439.961498	65.838397	58.754584	51.148245	52.253798	47.578575	…	57.629387	37.748278	-122.361829	19.413305	13.337873	6.576218	0.270299	23.963655	23.969094	23.963620
29	88695.445887	1615.017825	57.283422	57.482047	433.769544	67.336389	60.667176	53.387319	53.541380	49.878024	…	57.283422	37.746765	-122.360245	19.294882	13.633308	7.565317	0.260250	26.916964	26.918513	26.916883
30	87779.856531	1533.849251	57.189507	57.323769	440.363812	66.780514	59.447323	51.803640	53.407709	49.461456	…	57.189507	37.745982	-122.358819	19.537473	13.335974	7.986938	0.234904	25.564154	25.560385	25.564158
31	98845.505424	1693.046905	57.640715	57.788449	437.254627	66.005105	57.257498	48.310785	49.848117	44.868220	…	57.640715	37.748891	-122.362839	19.306318	13.128909	7.504467	0.272176	28.217448	28.219847	28.217553

	startdate	landmark	duration
subscriptiontype			Customer	Subscriber
0	2013-08-31	Mountain View	2346.444444	6485.000000
1	2013-08-31	Palo Alto	2617.075000	1016.541667
2	2013-08-31	Redwood City	6075.250000	586.157895
3	2013-08-31	San Francisco	4047.301633	731.557012
4	2013-08-31	San Jose	3364.852459	692.814433

				duration_f
startdate	landmark	subscriptiontype	endstation
2013-08-31	Mountain View	Customer	Evelyn Park and Ride	26
			Mountain View Caltrain Station	61
			Mountain View City Hall	11
			Park at Olive	34
			Rengstorff Avenue / California Street	16
			San Antonio Caltrain Station	52
		Subscriber	Evelyn Park and Ride	22
			Mountain View Caltrain Station	8
			Mountain View City Hall	215
			Park at Olive	453
			Rengstorff Avenue / California Street	10
			San Antonio Caltrain Station	15
	Palo Alto	Customer	California Ave Caltrain Station	19
			Cowper at University	24
			Palo Alto Caltrain Station	105
			Park at Olive	17
			Rengstorff Avenue / California Street	25
			San Antonio Caltrain Station	17
			University and Emerson	62
		Subscriber	California Ave Caltrain Station	13
			Cowper at University	4
			Palo Alto Caltrain Station	25
			Park at Olive	15
			San Antonio Caltrain Station	30
			University and Emerson	17
	Redwood City	Customer	Franklin at Maple	31
			Palo Alto Caltrain Station	279
			Redwood City Caltrain Station	64
		Subscriber	Broadway at Main	4
		Subscriber	Franklin at Maple	7
…	…	…	…	…
2014-02-28	San Francisco	Subscriber	Yerba Buena Center of the Arts (3rd @ Howard)	8
	San Jose	Customer	Adobe on Almaden	28
			Arena Green / SAP Center	73
			Japantown	19
			MLK Library	360
			Paseo de San Antonio	26
			SJSU - San Salvador at 9th	19
			SJSU 4th at San Carlos	236
			San Jose City Hall	56
			San Jose Civic Center	106
			San Jose Diridon Caltrain Station	177
			San Pedro Square	181
			San Salvador at 1st	72
			Santa Clara at Almaden	37
			St James Park	87
		Subscriber	Adobe on Almaden	5
			Arena Green / SAP Center	5
			Japantown	11
			MLK Library	18
			Paseo de San Antonio	8
			SJSU - San Salvador at 9th	12
			SJSU 4th at San Carlos	6
			San Jose City Hall	10
			San Jose Civic Center	8
			San Jose Diridon Caltrain Station	7
			San Jose Government Center	19
			San Pedro Square	7
			San Salvador at 1st	6
			Santa Clara at Almaden	5
			St James Park	9

	duration_i
landmark
Mountain View	121895
Palo Alto	131810
Redwood City	38924
San Francisco	2454177
San Jose	207658

	dockcount
landmark
Mountain View	0.095823
Palo Alto	0.061425
Redwood City	0.094185
San Francisco	0.544636
San Jose	0.203931

		dockcount
landmark	name
Mountain View	Castro Street and El Camino Real	11
	Evelyn Park and Ride	15
	Mountain View Caltrain Station	23
	Mountain View City Hall	15
	Rengstorff Avenue / California Street	15
	San Antonio Caltrain Station	23
	San Antonio Shopping Center	15
Palo Alto	California Ave Caltrain Station	15
	Cowper at University	11
	Palo Alto Caltrain Station	23
	Park at Olive	15
	University and Emerson	11
Redwood City	Broadway at Main	15
	Franklin at Maple	15
	Mezes Park	15
	Redwood City Caltrain Station	25
	Redwood City Medical Center	15
	Redwood City Public Library	15
	San Mateo County Center	15
San Francisco	2nd at Folsom	19
	2nd at South Park	15
	2nd at Townsend	27
	5th at Howard	15
	Beale at Market	19
	Broadway St at Battery St	15
	Civic Center BART (7th at Market)	23
	Clay at Battery	15
	Commercial at Montgomery	15
	Davis at Jackson	15
	Embarcadero at Bryant	15
	…	…
	Market at Sansome	27
	Mechanics Plaza (Market at Battery)	19
	Post at Kearney	19
	Powell Street BART	19
	Powell at Post (Union Square)	19
	San Francisco Caltrain (Townsend at 4th)	19
	San Francisco Caltrain 2 (330 Townsend)	23
	San Francisco City Hall	19
	South Van Ness at Market	19
	Spear at Folsom	19
	Steuart at Market	23
	Temporary Transbay Terminal (Howard at Beale)	23
	Townsend at 7th	15
	Washington at Kearney	15
	Yerba Buena Center of the Arts (3rd @ Howard)	19
San Jose	Adobe on Almaden	19
	Arena Green / SAP Center	19
	Japantown	15
	MLK Library	19
	Paseo de San Antonio	15
	SJSU - San Salvador at 9th	15
	SJSU 4th at San Carlos	19
	San Jose City Hall	15
	San Jose Civic Center	15
	San Jose Diridon Caltrain Station	27
	San Jose Government Center	15
	San Pedro Square	15
	San Salvador at 1st	15
	Santa Clara at Almaden	11
	St James Park	15

	duration_i
landmark
Mountain View	2728
Palo Alto	1706
Redwood City	793
San Francisco	129853
San Jose	8935

	duration_i
landmark
Mountain View	44.682918
Palo Alto	77.262603
Redwood City	49.084489
San Francisco	18.899656
San Jose	23.240963

		tripid	duration	startterminal	endterminal	bike	max_temperature_f	mean_temperature_f	min_temperaturef	max_dew_point_f	meandew_point_f	…	station_id	lat	long	dockcount	day	hour	month	hourdiff	durationminutes	duration_i
startdate	duration_f
2013-08-29 09:00:00	2.90	4069	174	64	64	288	74	68	61	61	58	…	64	37.782259	-122.392738	15	29	9	8	0	2.900000	3
	2.97	4086	178	45	45	379	74	68	61	61	58	…	45	37.794231	-122.402923	15	29	9	8	0	2.966667	3
	3.63	4081	218	27	27	150	80	70	64	65	61	…	27	37.389218	-122.081896	15	29	9	8	0	3.633333	4
	4.78	4084	287	27	27	138	80	70	64	65	61	…	27	37.389218	-122.081896	15	29	9	8	0	4.783333	5
	12.73	4080	764	66	69	315	74	68	61	61	58	…	66	37.774814	-122.418954	19	29	9	8	0	12.733333	13

	startdate	landmark	duration_f
0	2013-08-29 09:00:00	Mountain View	4.205000
1	2013-08-29 09:00:00	Redwood City	63.820000
2	2013-08-29 09:00:00	San Francisco	14.695556
3	2013-08-29 10:00:00	Mountain View	6.325000
4	2013-08-29 10:00:00	Palo Alto	18.775000
5	2013-08-29 10:00:00	San Francisco	37.356250
6	2013-08-29 10:00:00	San Jose	21.225000
7	2013-08-29 11:00:00	San Francisco	40.284737
8	2013-08-29 11:00:00	San Jose	2.957500
9	2013-08-29 12:00:00	Palo Alto	11.658000
10	2013-08-29 12:00:00	Redwood City	8.353333
11	2013-08-29 12:00:00	San Francisco	16.223173
12	2013-08-29 12:00:00	San Jose	12.387500
13	2013-08-29 13:00:00	Mountain View	1628.550000
14	2013-08-29 13:00:00	Palo Alto	23.544286
15	2013-08-29 13:00:00	San Francisco	53.158529
16	2013-08-29 13:00:00	San Jose	26.937273
17	2013-08-29 14:00:00	Mountain View	15.880000
18	2013-08-29 14:00:00	San Francisco	24.205366
19	2013-08-29 14:00:00	San Jose	6.595000
20	2013-08-29 15:00:00	Mountain View	878.300000
21	2013-08-29 15:00:00	Palo Alto	11.620000
22	2013-08-29 15:00:00	San Francisco	23.209492
23	2013-08-29 15:00:00	San Jose	16.200000
24	2013-08-29 16:00:00	Mountain View	4.450000
25	2013-08-29 16:00:00	San Francisco	14.293684
26	2013-08-29 16:00:00	San Jose	5.440000
27	2013-08-29 17:00:00	Mountain View	12.970000
28	2013-08-29 17:00:00	Palo Alto	10.422000
29	2013-08-29 17:00:00	Redwood City	8.925000
…	…	…	…
9781	2014-02-28 11:00:00	San Jose	5.270000
9782	2014-02-28 12:00:00	Palo Alto	3.320000
9783	2014-02-28 12:00:00	San Francisco	6.805385
9784	2014-02-28 12:00:00	San Jose	5.766667
9785	2014-02-28 13:00:00	Palo Alto	4.850000
9786	2014-02-28 13:00:00	San Francisco	7.604667
9787	2014-02-28 13:00:00	San Jose	43.920000
9788	2014-02-28 14:00:00	San Francisco	9.297692
9789	2014-02-28 14:00:00	San Jose	5.252000
9790	2014-02-28 15:00:00	Mountain View	4.330000
9791	2014-02-28 15:00:00	San Francisco	14.967143
9792	2014-02-28 15:00:00	San Jose	12.970000
9793	2014-02-28 16:00:00	Mountain View	4.470000
9794	2014-02-28 16:00:00	San Francisco	49.152857
9795	2014-02-28 16:00:00	San Jose	5.080000
9796	2014-02-28 17:00:00	Mountain View	5.625000
9797	2014-02-28 17:00:00	San Francisco	9.254571
9798	2014-02-28 17:00:00	San Jose	6.200000
9799	2014-02-28 18:00:00	Mountain View	4.740000
9800	2014-02-28 18:00:00	Palo Alto	13.415000
9801	2014-02-28 18:00:00	San Francisco	8.176364
9802	2014-02-28 19:00:00	Mountain View	9.435000
9803	2014-02-28 19:00:00	San Francisco	132.491364
9804	2014-02-28 19:00:00	San Jose	11.815000
9805	2014-02-28 20:00:00	Mountain View	4.620000
9806	2014-02-28 20:00:00	Redwood City	24.380000
9807	2014-02-28 20:00:00	San Francisco	7.406667
9808	2014-02-28 21:00:00	San Francisco	7.203333
9809	2014-02-28 22:00:00	San Francisco	8.572500
9810	2014-02-28 23:00:00	San Francisco	539.150000

	duration_f
	count	sum	mean	min	max
1	24428	412751.48	16.896655	1.00	9772.60
2	19024	332260.54	17.465335	1.02	3077.22
8	2102	90672.74	43.136413	1.03	4022.23
9	25243	663946.54	26.302204	1.00	9958.62
10	29105	578249.15	19.867691	1.00	7156.40
11	24219	500883.27	20.681418	1.00	12037.27
12	19894	375728.66	18.886532	1.00	10322.03

	precipitation_in	subscriptiontype	dockcount
0	0	Customer	18.940762
1	0	Subscriber	19.472218
2	0.01	Customer	19.660550
3	0.01	Subscriber	19.833333
4	0	Customer	18.942500
5	0	Subscriber	19.528072
6	0.01	Customer	18.397083
7	0.01	Subscriber	19.411516
8	0.02	Customer	19.000000
9	0.02	Subscriber	18.341772
10	0.03	Customer	12.000000
11	0.03	Subscriber	17.666667
12	0.04	Customer	17.166667
13	0.04	Subscriber	18.132075
14	0.05	Subscriber	17.545455
15	0.06	Customer	19.915663
16	0.06	Subscriber	18.818182
17	0.07	Customer	11.000000
18	0.07	Subscriber	15.000000
19	0.08	Customer	17.666667
20	0.08	Subscriber	19.000000
21	0.09	Customer	19.952381
22	0.09	Subscriber	19.588079
23	0.1	Customer	16.600000
24	0.1	Subscriber	19.102564
25	0.12	Subscriber	25.000000
26	0.14	Customer	17.000000
27	0.14	Subscriber	19.000000
28	0.16	Subscriber	15.000000
29	0.17	Customer	13.000000
…	…	…	…
48	0.45	Subscriber	18.428571
49	0.47	Customer	21.000000
50	0.47	Subscriber	19.105263
51	0.49	Customer	11.000000
52	0.49	Subscriber	11.000000
53	0.53	Subscriber	17.000000
54	0.61	Subscriber	17.608696
55	0.63	Customer	18.714286
56	0.63	Subscriber	19.572519
57	0.64	Customer	20.161290
58	0.64	Subscriber	19.519608
59	0.66	Customer	16.756098
60	0.66	Subscriber	17.800000
61	0.68	Subscriber	18.407407
62	0.74	Customer	18.902439
63	0.74	Subscriber	19.868009
64	0.78	Customer	15.000000
65	0.78	Subscriber	18.200000
66	0.83	Subscriber	15.000000
67	0.85	Customer	19.355556
68	0.85	Subscriber	18.407407
69	0.88	Customer	15.000000
70	0.88	Subscriber	18.000000
71	0.97	Customer	19.000000
72	0.97	Subscriber	18.130435
73	1.06	Subscriber	13.285714
74	1.71	Customer	20.333333
75	1.71	Subscriber	17.000000
76	T	Customer	19.195219
77	T	Subscriber	19.671620

	tripid	duration	startterminal	endterminal	bike	max_temperature_f	mean_temperature_f	min_temperaturef	max_dew_point_f	meandew_point_f	…	lat	long	dockcount	day	hour	month	hourdiff	durationminutes	duration_i	duration_f
landmark
Mountain View	113506.415689	2681.418988	28.782991	28.980572	266.556085	67.732405	54.907991	44.681085	48.479106	42.678886	…	37.394312	-122.083341	18.888563	15.521261	13.072947	6.898827	0.251833	44.690316	44.682918	44.690359
Palo Alto	92832.010551	4635.689918	35.669988	35.193435	208.521688	69.908558	59.389215	48.392145	48.810082	44.058617	…	37.440460	-122.156431	15.171161	15.759086	13.461313	7.947831	0.566237	77.261499	77.262603	77.261483
Redwood City	82721.219420	2945.804540	23.134931	24.148802	237.013871	68.611602	59.397226	50.197982	51.931904	45.466583	…	37.486049	-122.229363	19.766709	15.145019	12.639344	8.464061	0.161412	49.096742	49.084489	49.096608
San Francisco	101092.908674	1133.965253	61.825657	61.868821	455.360123	66.115962	58.201636	49.776278	49.848767	45.052167	…	37.787144	-122.400225	19.565963	15.746290	13.177293	7.668687	0.231462	18.899421	18.899656	18.899424
San Jose	98496.783100	1394.662899	7.371684	7.563290	284.057862	69.322104	58.393397	46.986682	47.791046	42.897146	…	37.334148	-121.892161	17.977952	15.714605	13.638500	7.585115	0.095467	23.244382	23.240963	23.244376

		tripid	duration	startterminal	endterminal	bike	max_temperature_f	mean_temperature_f	min_temperaturef	max_dew_point_f	meandew_point_f	…	station_id	lat	long	dockcount	day	hour	month	hourdiff	durationminutes	duration_i
startdate	duration_f
2013-08-31	1.03	7416	62	60	60	511	71	64	57	57	56	…	60	37.804770	-122.403234	15	31	20	8	0	1.033333	1
	1.05	4576	63	66	66	520	74	68	61	61	58	…	66	37.774814	-122.418954	19	29	14	8	0	1.050000	1
	1.13	7250	68	35	35	100	76	69	62	61	59	…	35	37.444521	-122.163093	11	31	16	8	0	1.133333	1
	1.15	13518	138	122	122	965	149	133	117	118	114	…	122	75.590069	-244.799470	34	61	35	16	0	2.300000	2
	1.17	4607	70	10	10	661	81	72	63	62	61	…	10	37.337391	-121.886995	15	29	14	8	0	1.166667	1

	tripid	duration	startterminal	endterminal	bike	max_temperature_f	mean_temperature_f	min_temperaturef	max_dew_point_f	meandew_point_f	…	lat	long	dockcount	day	hour	month	hourdiff	durationminutes	duration_i	duration_f
subscriptiontype
Customer	79116.721714	3629.519659	56.400586	56.781909	433.907238	68.987487	60.303115	51.149137	51.473953	46.698992	…	37.745722	-122.358957	18.944810	15.439970	13.707949	8.313192	0.580051	60.491994	60.490022	60.491974
Subscriber	106806.906130	589.969564	57.538334	57.505486	437.863692	65.713868	57.602277	49.049971	49.218783	44.373692	…	37.745863	-122.358849	19.526261	15.816247	13.069760	7.481157	0.132648	9.832826	9.833106	9.832834

	tripid	duration	startterminal	endterminal	bike	max_temperature_f	mean_temperature_f	min_temperaturef	max_dew_point_f	meandew_point_f	…	lat	long	dockcount	day	hour	month	hourdiff	durationminutes	duration_i	duration_f
subscriptiontype
Customer	2402616605	110221253	1712773	1724353	13176895	2095012	1831285	1553297	1563161	1418155	…	1146262.097908	-3715796.806334	575316	468881	416283	252455	17615	1837020.883333	1836961	1837020.26
Subscriber	12138284461	67048271	6539059	6535326	49761895	7468184	6546326	5574382	5593567	5042937	…	4289704.103845	-13905716.142004	2219101	1797469	1485339	850211	15075	1117471.183333	1117503	1117472.12

	morning	evening
	duration_f	duration_f
startdate
2013-08-31	37.648913	37.240278
2013-09-30	17.973624	24.621531
2013-10-31	13.302877	16.752767
2013-11-30	13.209249	17.277542
2013-12-31	15.384694	14.980330
2014-01-31	11.679421	14.962542
2014-02-28	11.441120	15.718395

	landmark
startdate
2013-08-29 15:11:00	13
2013-08-29 17:35:00	13
2013-08-29 20:00:00	13