Calculate monthly returns from daily returns in pandas(cumpound)

I was trying to calculate monthly returns for a particular stock, but I can't figure out a good method which doesn't use a big quantity of for cycles.
The df has the following form

MSFT-US AAPL-US GE RF
20150501 1.01 -0.33 -0.60 0.000
20150504 0.32 0.06 0.16 0.000
20150505 -1.19 -0.10 0.34 0.000
20150506 -0.31 0.62 -0.20 0.000
20150507 0.39 0.03 -0.43 0.000
20150508 1.21 -0.54 -0.21 0.000
20150511 -0.39 0.67 -0.11 0.000
20150512 -0.27 0.00 0.11 0.000
20150513 0.01 0.02 -0.06 0.000
20150514 1.01 -0.10 -0.36 0.000
20150515 0.05 -0.26 -0.01 0.000
20150518 0.44 0.72 -0.09 0.000
20150519 -0.09 -0.08 0.03 0.000
20150520 -0.05 0.21 -0.09 0.000
20150521 0.23 -0.31 0.09 0.000
20150522 -0.22 -0.11 -0.14 0.000
20150526 -1.01 -0.04 -0.02 0.000
20150527 0.93 0.33 -0.39 0.000
20150528 -0.11 0.11 0.07 0.000
20150529 -0.58 0.02 0.05 0.000


So I want to have something like this (but coumpound not sum):

MSFT-US AAPL-US GE RF
201505 1.36 0.92 -1.89 0.00


2 Answers
2

Assuming your date column is called 'date':

'date'

df['month'] = df['date'].astype(str).str[:6]

monthly_total = df.groupby('month').sum().drop('date', axis='columns')


gives you

MSFT-US AAPL-US GE RF
month
201505 1.38 0.92 -1.86 0.0


To get compound returns, we need to add 1 to each value and then use .prod():

.prod()

df[['MSFT-US', 'AAPL-US', 'GE', 'RF']] += 1
monthly_total = df.groupby('month').prod().drop('date', axis='columns')


Giving us:

MSFT-US AAPL-US GE RF
month
201505 0.008739 0.946043 0.070769 1.0


df.index = df.index.map(lambda x:pd.to_datetime(str(x)))
df.groupby([df.index.year,df.index.month]).sum()


output:

MSFT-US AAPL-US GE RF
2015 5 1.38 0.92 -1.86 0.0


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.