Pandas Groupby Geometric Average?

user_11830909 0
Viewed 1400

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!

pandas finance python
1 Answers

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)
user_50329410
Related