I’m trying to calculate rolling averages in R, including the current year in the calculation. For simplicity, I’m using a data frame with integers. my data are groupped. Here’s a sample data frame:

library(dplyr)
library(tibble)

data <- tibble::tribble(
  ~groupID, ~value, ~year,
  "A",      10,     2010,
  "A",      20,     2011,
  "A",      30,     2012,
  "A",      40,     2013,
  "B",      15,     2010,
  "B",      25,     2011,
  "B",      35,     2012,
  "B",      45,     2013
)

I want to calculate the average of value for the current year and the previous 1, 2, and 3 years within each groupID. The expected output should include columns for the , 1-year (mean(current year, previous year),
2-year (mean current, and two previous year), and 3-year averages.

How can I achieve this in R?

Here is where I am at, but not sure how to include there ‘current year’ as well

# Function to calculate rolling averages including the current year
calculate_rolling_avg <- function(df, window) {
  df %>%
    group_by(groupID) %>%
    arrange(year) %>%
    mutate(!!paste0("val_", window, "yr") := slide_dbl(value, mean, .before = window - 1, .complete = FALSE)) %>%
    ungroup()
}

# Calculate rolling averages for 1, 2, and 3 years including the current year
data_with_avg <- data %>%
  calculate_rolling_avg(1) %>%
  calculate_rolling_avg(2) %>%
  calculate_rolling_avg(3)

Which is not corect

> data_with_avg
# A tibble: 8 x 6
  groupID value  year val_1yr val_2yr val_3yr
  <chr>   <dbl> <dbl>   <dbl>   <dbl>   <dbl>
1 A          10  2010      10      10      10
2 A          20  2011      20      15      15
3 A          30  2012      30      25      20
4 A          40  2013      40      35      30
5 B          15  2010      15      15      15
6 B          25  2011      25      20      20
7 B          35  2012      35      30      25
8 B          45  2013      45      40      35