%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?
First, we need to load up the data. We've done this before.
bikes = pd.read_csv('../data/bikes.csv', sep=';', encoding='latin1', parse_dates=['Date'], dayfirst=True, index_col='Date') bikes['Berri 1'].plot()
<matplotlib.axes._subplots.AxesSubplot at 0x114cd4190>
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()
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.
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:
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:
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]
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
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
<matplotlib.axes._subplots.AxesSubplot at 0x114d5d820>
So it looks like Montrealers are commuter cyclists -- they bike much more during the week. Neat!
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
numpy.median, or any other function you like.
bikes = pd.read_csv('../data/bikes.csv', 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')
<matplotlib.axes._subplots.AxesSubplot at 0x114e7a430>