Travis Millburn Travis Millburn - 4 months ago 93
Python Question

Pandas Pivot Time-series by year

Hello and thanks in advance for any help. I have a simple dataframe with two columns. I did not set an index explicitly, but I believe a dataframe gets an integer index that I see along the left side of the output. Question below:

df = pandas.DataFrame(res)
df.columns = ['date', 'pb']
df['date'] = pandas.to_datetime(df['date'])
df.dtypes

date datetime64[ns]
pb float64
dtype: object


date pb
0 2016-04-01 24199.933333
1 2016-03-01 23860.870968
2 2016-02-01 23862.275862
3 2016-01-01 25049.193548
4 2015-12-01 24882.419355
5 2015-11-01 24577.000000


date datetime64[ns]
pb float64
dtype: object


I would like to pivot the dataframe so that I have years across the top (columns): 2016, 2015, etc
and a row for each month: 1 - 12.

Answer

Using the .dt accessor you can create columns for year and month and then pivot on those:

df['Year'] = df['date'].dt.year
df['Month'] = df['date'].dt.month
pd.pivot_table(df,index='Month',columns='Year',values='pb',aggfunc=np.sum)

Alternately if you don't want those other columns you can do:

pd.pivot_table(df,index=df['date'].dt.month,columns=df['date'].dt.year,
               values='pb',aggfunc=np.sum)

With my dummy dataset that produces:

Year             2013      2014      2015      2016
date
1             92924.0  102072.0  134660.0  132464.0
2             79935.0   82145.0  118234.0  147523.0
3             86878.0   94959.0  130520.0  138325.0
4             80267.0   89394.0  120739.0  129002.0
5             79283.0   91205.0  118904.0  125878.0
6             77828.0   89884.0  112488.0  121953.0
7             78839.0   94407.0  113124.0       NaN
8             79885.0   97513.0  116771.0       NaN
9             79455.0   99555.0  114833.0       NaN
10            77616.0   98764.0  115872.0       NaN
11            75043.0   95756.0  107123.0       NaN
12            81996.0  102637.0  114952.0       NaN