How to Calculate Geometric Mean for Specific Quarters

Question

I am finding hard time calculating Geometric date for each column for the specific years. I had to calculate that for multiple columns. It was done in excel and now we like to move to r for broader audience Below are the formulas used in Excel

(GEOMEAN(1+DK45:DK48)^4)^(1/4)-1

I tried applying mean.geometric formula from performance library which give exactly the result but not sure how to apply that by different quarters on whole column

  TotalReturn %>% 
  mutate(mpgGM = rollapply(l12420, 3, geometric.mean, fill=NA, 
  align="left"))

My sample data set is

structure(list(Quarter = structure(c(18717, 18808, 18900, 18992, 
19082, 19173, 19265, 19357, 19447, 19538, 19630, 19722), class = "Date"), 
    A = c(0.043, 0.044, 0.044, 0.044, 0.044, 0.046, 0.048, 0.049, 
    0.05, 0.05, 0.05, 0.051), B = c(-0.002, -0.001, 0.002, 0.008, 
    0.015, 0.02, 0.024, 0.025, 0.025, 0.023, 0.022, 0.022)), row.names = c(NA, 
-12L), class = "data.frame")

This is the expected result

2021 Q4 4.06%
2022 Q4 4.68%
2023 Q4 5.04%

Your description says to apply for different quarters, but why is `Q4` only `filtered` — akrun, Mar 29 '19 at 16:20
Are you talking about the geometric mean of the values in `A` and `B` column in each row? Could you please clarify that? — jay.sf, Mar 29 '19 at 16:49
@gurtej Have you seen my edit? Does this answer your question now? — jay.sf, Mar 31 '19 at 06:48
@jay.sf Sorry for late reply. My laptop was not with me. Calculations are working as expected. Thank you very much for all your help — gurtej, Apr 01 '19 at 15:01
@gurtej Great, glad to help! Consider to [**accept answers**](https://meta.stackexchange.com/a/5235/371738) that solved questions are marked as such. — jay.sf, Apr 01 '19 at 15:12

jay.sf · Answer 1 · 2019-03-29T19:35:04.753

We could adapt the function for geometric mean for our purposes, to get % display.

gmMean <- function(x, na.rm=TRUE) {
  paste0(sprintf("%.1f", exp(sum(log(x[x > 0]), na.rm=na.rm) / length(x))*100), "%")
}

The function then can be applied in the data frame creation.

res <- data.frame(Year=strftime(dat$Quarter, format="%Y"),
                  Quarter=factor(substr(dat$Quarter, 6, 7), labels=paste0("Q", 1:4)),
                  Geom.Mean=apply(dat[-1], 1, gmMean))
res
#    Year Quarter Geom.Mean
# 1  2021      Q1     20.7%
# 2  2021      Q2     21.0%
# 3  2021      Q3      0.9%
# 4  2021      Q4      1.9%
# 5  2022      Q1      2.6%
# 6  2022      Q2      3.0%
# 7  2022      Q3      3.4%
# 8  2022      Q4      3.5%
# 9  2023      Q1      3.5%
# 10 2023      Q2      3.4%
# 11 2023      Q3      3.3%
# 12 2023      Q4      3.3%

To get the geometric mean for whole years we can first create a year variable

dat$year <- strftime(dat$Quarter, format="%Y")

and then do

res <- aggregate(. ~ year, dat, gmMean)[-2]
res
#   year    A    B
# 1 2021 4.4% 6.3%
# 2 2022 4.7% 2.1%
# 3 2023 5.0% 2.3%

Data

dat <- structure(list(Quarter = structure(c(18717, 18808, 18900, 18992, 
19082, 19173, 19265, 19357, 19447, 19538, 19630, 19722), class = "Date"), 
    A = c(0.043, 0.044, 0.044, 0.044, 0.044, 0.046, 0.048, 0.049, 
    0.05, 0.05, 0.05, 0.051), B = c(-0.002, -0.001, 0.002, 0.008, 
    0.015, 0.02, 0.024, 0.025, 0.025, 0.023, 0.022, 0.022)), row.names = c(NA, 
-12L), class = "data.frame")

How to Calculate Geometric Mean for Specific Quarters

1 Answers1