#!/usr/bin/env python
# coding: utf-8

# In[27]:


get_ipython().run_line_magic('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.

# In[28]:


bikes = pd.read_csv('../data/bikes.csv', sep=';', encoding='latin1', parse_dates=['Date'], dayfirst=True, index_col='Date')
bikes['Berri 1'].plot()


# 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

# In[29]:


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


# In[30]:


berri_bikes[:5]


# 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.

# In[31]:


berri_bikes.index


# 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:

# In[32]:


berri_bikes.index.day


# We actually want the weekday, though:

# In[33]:


berri_bikes.index.weekday


# 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:

# In[34]:


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


# # 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](http://pandas.pydata.org/pandas-docs/stable/groupby.html) 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".

# In[35]:


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:

# In[36]:


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


# In[37]:


weekday_counts.plot(kind='bar')


# 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.

# In[38]:


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')


# In[ ]: