CODEWITHSUNDEEP
pythonpandaspython-3.6
steff
steff

Reputation: 906

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

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?

Upvotes: 0

Views: 1367

Answers (3)

steff
steff

Reputation: 906

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

Upvotes: 0

Graham Streich
Graham Streich

Reputation: 924

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

Upvotes: 0

Yeile
Yeile

Reputation: 755

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.

Upvotes: 1

Related Questions