Add months to xaxis and legend on a matplotlib line plot

Question

I am trying to plot stacked yearly line graphs by months. I have a dataframe df_year as below:

Day                    Number of Bicycle Hires            
2010-07-30                     6897

2010-07-31                     5564

2010-08-01                     4303

2010-08-02                     6642

2010-08-03                     7966

with the index set to the date going from 2010 July to 2017 July

I want to plot a line graph for each year with the xaxis being months from Jan to Dec and only the total sum per month is plotted

I have achieved this by converting the dataframe to a pivot table as below:

pt = pd.pivot_table(df_year, index=df_year.index.month, columns=df_year.index.year, aggfunc='sum')

This creates the pivot table as below which I can plot as show in the attached figure:

  Number of Bicycle Hires    2010      2011       2012      2013       2014   
1                      NaN  403178.0   494325.0  565589.0   493870.0   
2                      NaN  398292.0   481826.0  516588.0   522940.0   
3                      NaN  556155.0   818209.0  504611.0   757864.0   
4                      NaN  673639.0   649473.0  658230.0   805571.0   
5                      NaN  722072.0   926952.0  749934.0   890709.0  

plot showing yearly data with months on xaxis

The only problem is that the months show up as integers and I would like them to be shown as Jan, Feb .... Dec with each line representing one year. And I am unable to add a legend for each year.

I have tried the following code to achieve this:

dims = (15,5)
fig, ax = plt.subplots(figsize=dims)
ax.plot(pt)

months = MonthLocator(range(1, 13), bymonthday=1, interval=1)
monthsFmt = DateFormatter("%b '%y")
ax.xaxis.set_major_locator(months) #adding this makes the month ints disapper
ax.xaxis.set_major_formatter(monthsFmt)
handles, labels = ax.get_legend_handles_labels() #legend is nowhere on the plot
ax.legend(handles, labels)

Please can anyone help me out with this, what am I doing incorrectly here?

Thanks!

Solution

There is nothing in your legend handles and labels, furthermore the DateFormatter is not returning the right values considering they are not datetime objects your translating.

You could set the index specifically for the dates, then drop the multiindex column level which is created by the pivot (the '0') and then use explicit ticklabels for the months whilst setting where they need to occur on your x-axis. As follows:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
import datetime

# dummy data (Days)
dates_d = pd.date_range('2010-01-01', '2017-12-31', freq='D')
df_year = pd.DataFrame(np.random.randint(100, 200, (dates_d.shape[0], 1)), columns=['Data'])
df_year.index = dates_d #set index

pt = pd.pivot_table(df_year, index=df_year.index.month, columns=df_year.index.year, aggfunc='sum')
pt.columns = pt.columns.droplevel() # remove the double header (0) as pivot creates a multiindex.

ax = plt.figure().add_subplot(111)
ax.plot(pt)

ticklabels = [datetime.date(1900, item, 1).strftime('%b') for item in pt.index]
ax.set_xticks(np.arange(1,13))
ax.set_xticklabels(ticklabels) #add monthlabels to the xaxis

ax.legend(pt.columns.tolist(), loc='center left', bbox_to_anchor=(1, .5)) #add the column names as legend.
plt.tight_layout(rect=[0, 0, 0.85, 1])

plt.show()