Grouped rolling average

Question

What is the solution for calculating a rolling average for a numerical variable that is observed for two groups over three periods?

df <- data.frame(
  Day = c(1, 2, 3, 1, 2, 3),
  Id = c("A", "A", "A", "B", "B", "B"),
  Num = c(1, 2, 3, 1, 3, 9)
)

The dplyr solution is easy enough, but is there an obvious way to do this in base?

library(dplyr)

df %>%
  group_by(Id) %>%
  mutate(RollingAvg = (Num + lag(Num))/2)

Answer 1

Note that for rolling mean, you could use stats::filter which is loaded by default thus could be considered part of base R:

transform(df, RollingAve = ave(Num, Id, FUN = \(x)stats::filter(x, rep(1,2)/2,sides = 1)))

  Day Id Num RollingAve
1   1  A   1         NA
2   2  A   2        1.5
3   3  A   3        2.5
4   1  B   1         NA
5   2  B   3        2.0
6   3  B   9        6.0

Answer 2

If you do not like to write a wrapper (b_lag) which does what dplyr::lag() does, you can use embed(). Try ave():

b_lag = \(x, k, fill = NA) c(rep(fill, k), head(x, -k))
within(df, { RollingAvg = ave(Num, Id, FUN = \(x) (x + b_lag(x, 1)) / 2) })

giving

  Day Id Num RollingAvg
1   1  A   1         NA
2   2  A   2        1.5
3   3  A   3        2.5
4   1  B   1         NA
5   2  B   3        2.0
6   3  B   9        6.0

Note. within() is used to generate the output as is. You could also do

df$RollingAvg = with(df, ave(Num, Id, FUN = \(x) (x + b_lag(x, 1)) / 2))