I'm trying to calculate the stock returns for my portfolio which requires "geometrically averaging" the percentages by year.
For simplicity, I have a dataframe that looks likes this:
Date Returns
2013-06-01 1%
2013-07-01 5%
2013-08-01 -4%
2014-01-01 12%
2014-02-01 -9%
I'd like the output to show:
Date Geo Return 2013 1.8% 2015 1.9%
Which is derived by: (1+.01)(1+.05)(1+-.04) = 1.8%
I am able to use the groupby function by year, but it only sums for me and I can't get the geometric average to work. Could someone please help?
Thanks!
Note that you have requested the cumulative product, which is different that the usual definition for the geometric mean.
df["returns"] = 1 + .01*df.Returns.str.split("%").str[0].astype(int)
df["geom_ave"] = df.groupby(df.Date.dt.year).returns.transform("prod")
output:
Date Returns returns geom_ave
0 2013-06-01 1% 1.01 1.01808
1 2013-07-01 5% 1.05 1.01808
2 2013-08-01 -4% 0.96 1.01808
3 2014-01-01 12% 1.12 1.01920
4 2014-02-01 -9% 0.91 1.01920
If instead you want the geometric mean, you can try:
from scipy import stats
series = df.groupby(df.Date.dt.year).returns.apply(stats.gmean)