Convert pandas time series with monthly returns into a df with col cumulative yearly return

Question

My df containing monthly returns looks like this:

df=pd.DataFrame((x*x).dropna(),columns=['mthly rtrn']) 

             mthly rtrn
2016-09-30    0.002488
2016-10-31   -0.004692
2016-11-30    0.003157
2016-12-30   -0.000503
2017-01-31    0.008019
2017-02-28    0.010055
2017-03-31    0.003435
2017-04-28    0.002577
2017-05-31    0.012107
2017-06-30    0.001089

How can I convert this into a df with columns Jan to Dec plus column for the cumulative yearly return. Lines should be the years 2016, 2017 etc. And the numbers should ideally be shown in %.

Desired output:

      Jan   Feb  Mar  Apr  May  Jun  Jul  Aug  Sep   Oct  Nov  Dec ANNUAL
2016 -5.0  -0.1  6.7  0.4  1.7  0.3  3.6  0.1  0.0  -1.7  3.7  2.0   12.0
2017  1.8   3.9  0.1  1.0  1.4  0.6  0.1   NA   NA    NA   NA   NA    9.3

where ANNUAL is the cumprod of the monthly returns.

What is the best pythonic way to achieve this?

score 1 · Accepted Answer · answered Jul 07 '17 at 09:19

I would first add additional columns Year and Month to your df using this, then use a pivot table to create a new df to get the index as Year and columns as Month, around the monthly return values.

Once you have the pivot table, you can use apply along axis=1 to get whatever aggregation you need for each year.

I can't really comment on the aggregation because I'm not sure if by 'cumulative' do you mean additive or multiplicative. You might want to consider cumsum or gmean from scipy, or if you prefer not to get scipy this function works too.

to get cumulative annual return cumsum is probably the best way forward. — steff, Jul 08 '17 at 05:37

score 0 · Answer 2 · answered Jul 10 '17 at 20:20

0

I would first resample the data by month using the .resample() method:

http://pandas.pydata.org/pandas-docs/version/0.17.0/generated/pandas.core.groupby.DataFrameGroupBy.resample.html

Then use the pivot method to turn the rows into columns:

https://pandas.pydata.org/pandas-docs/stable/generated/pandas.pivot_table.html

Then I would create a new column for the annual total:

df['annual'] = df['jan']+df['feb']+...+df['dec']

answered Jul 10 '17 at 20:20

Graham Streich

874
3
15
31

Could you please give me an example code how to use the pivot_table function? am stuck there. The df is already already monthly frequency (did that using asfreq function). – steff Jul 13 '17 at 07:37
can you update the code in your question to show the new df with the code you used?? – Graham Streich Jul 13 '17 at 13:00

score 0 · Answer 3 · answered Jul 25 '17 at 03:45

0

I found a nice tool for what i need: https://github.com/ranaroussi/monthly-returns-heatmap

answered Jul 25 '17 at 03:45

steff

896
2
9
23

Convert pandas time series with monthly returns into a df with col cumulative yearly return

3 Answers3