%matplotlib inline



import pandas as pd

import matplotlib.pyplot as plt



# Make the graphs a bit prettier, and bigger

plt.style.use('ggplot')

plt.rcParams['figure.figsize'] = (15, 5)

plt.rcParams['font.family'] = 'sans-serif'



# This is necessary to show lots of columns in pandas 0.12. 

# Not necessary in pandas 0.13.

pd.set_option('display.width', 5000) 

pd.set_option('display.max_columns', 60)

Okay! We're going back to our bike path dataset here. I live in Montreal, and I was curious about whether we're more of a commuter city or a biking-for-fun city -- do people bike more on weekends, or on weekdays?

4.1 Adding a 'weekday' column to our dataframe

First, we need to load up the data. We've done this before.

data_url = "https://sciencedata.dk/public/6e3ed434c0fa43df906ce2b6d1ba9fc6/pandas-cookbook/data/bikes.csv"

bikes = pd.read_csv(data_url, sep=';', encoding='latin1', parse_dates=['Date'], dayfirst=True, index_col='Date')

bikes['Berri 1'].plot()

<AxesSubplot:xlabel='Date'>

Next up, we're just going to look at the Berri bike path. Berri is a street in Montreal, with a pretty important bike path. I use it mostly on my way to the library now, but I used to take it to work sometimes when I worked in Old Montreal.

So we're going to create a dataframe with just the Berri bikepath in it

berri_bikes = bikes[['Berri 1']].copy()

berri_bikes[:5]

	Berri 1
Date
2012-01-01	35
2012-01-02	83
2012-01-03	135
2012-01-04	144
2012-01-05	197

Next, we need to add a 'weekday' column. Firstly, we can get the weekday from the index. We haven't talked about indexes yet, but the index is what's on the left on the above dataframe, under 'Date'. It's basically all the days of the year.

berri_bikes.index

DatetimeIndex(['2012-01-01', '2012-01-02', '2012-01-03', '2012-01-04', '2012-01-05', '2012-01-06', '2012-01-07', '2012-01-08', '2012-01-09', '2012-01-10',
               ...
               '2012-10-27', '2012-10-28', '2012-10-29', '2012-10-30', '2012-10-31', '2012-11-01', '2012-11-02', '2012-11-03', '2012-11-04', '2012-11-05'], dtype='datetime64[ns]', name='Date', length=310, freq=None)

You can see that actually some of the days are missing -- only 310 days of the year are actually there. Who knows why.

Pandas has a bunch of really great time series functionality, so if we wanted to get the day of the month for each row, we could do it like this:

berri_bikes.index.day

Int64Index([ 1,  2,  3,  4,  5,  6,  7,  8,  9, 10,
            ...
            27, 28, 29, 30, 31,  1,  2,  3,  4,  5], dtype='int64', name='Date', length=310)

We actually want the weekday, though:

berri_bikes.index.weekday

Int64Index([6, 0, 1, 2, 3, 4, 5, 6, 0, 1,
            ...
            5, 6, 0, 1, 2, 3, 4, 5, 6, 0], dtype='int64', name='Date', length=310)

These are the days of the week, where 0 is Monday. I found out that 0 was Monday by checking on a calendar.

Now that we know how to get the weekday, we can add it as a column in our dataframe like this:

berri_bikes.loc[:,'weekday'] = berri_bikes.index.weekday

berri_bikes[:5]

	Berri 1	weekday
Date
2012-01-01	35	6
2012-01-02	83	0
2012-01-03	135	1
2012-01-04	144	2
2012-01-05	197	3

4.2 Adding up the cyclists by weekday

This turns out to be really easy!

Dataframes have a .groupby() method that is similar to SQL groupby, if you're familiar with that. I'm not going to explain more about it right now -- if you want to to know more, the documentation is really good.

In this case, berri_bikes.groupby('weekday').aggregate(sum) means "Group the rows by weekday and then add up all the values with the same weekday".

weekday_counts = berri_bikes.groupby('weekday').aggregate(sum)

weekday_counts

	Berri 1
weekday
0	134298
1	135305
2	152972
3	160131
4	141771
5	101578
6	99310

It's hard to remember what 0, 1, 2, 3, 4, 5, 6 mean, so we can fix it up and graph it:

weekday_counts.index = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']

weekday_counts

	Berri 1
Monday	134298
Tuesday	135305
Wednesday	152972
Thursday	160131
Friday	141771
Saturday	101578
Sunday	99310

weekday_counts.plot(kind='bar')

<AxesSubplot:>

So it looks like Montrealers are commuter cyclists -- they bike much more during the week. Neat!

4.3 Putting it together

Let's put all that together, to prove how easy it is. 6 lines of magical pandas!

If you want to play around, try changing sum to max, numpy.median, or any other function you like.

bikes = pd.read_csv(data_url, 

                    sep=';', encoding='latin1', 

                    parse_dates=['Date'], dayfirst=True, 

                    index_col='Date')

# Add the weekday column

berri_bikes = bikes[['Berri 1']].copy()

berri_bikes.loc[:,'weekday'] = berri_bikes.index.weekday



# Add up the number of cyclists by weekday, and plot!

weekday_counts = berri_bikes.groupby('weekday').aggregate(sum)

weekday_counts.index = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']

weekday_counts.plot(kind='bar')

<AxesSubplot:>