To be honest, I didn't get what is the purpose of the months column, so I forgot to include it in the output. Adding number of months is not so different from my first attempt:
library(tidyverse)
max_lookback_mo <- 3 # This will be 12 in real use, using 3 for simpler example.
monthly_data <- tribble(~month, ~total, ~desired_mos_incl, ~desired_avg, 1,
100, 1, 100, 2, 200, 2, 150, 3, NA, NA, NA, 4, 200, 1, 200, 5, 300, 2, 250,
6, 400, 3, 300, 7, 500, 3, 400, 8, 420, 3, 440)
filter_nas <- function(vec) {
last_na <- max(which(is.na(vec)))
res <- vec[seq_along(vec) > last_na]
res
}
calc_positions <- function(x, n_months = 3) {
vec <- (2 - n_months):(length(x))
purrr::map(seq_along(x), function(pos) {
vec[pos >= vec & (pos - n_months) < vec & vec > 0]
})
}
add_desireds <- function(x, n_months = max_lookback_mo) {
total_vec <- x[["total"]]
positions <- calc_positions(total_vec, n_months = n_months)
vecs <- purrr::map(positions, function(position) {
total_vec[position] %>% filter_nas
})
x[["avg"]] <- purrr::map_dbl(vecs, mean)
x[["months"]] <- purrr::map_dbl(vecs, length)
x
}
res <- monthly_data %>% add_desireds
res
#> # A tibble: 8 x 6
#> month total desired_mos_incl desired_avg avg months
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 1 100 1 100 100 1
#> 2 2 200 2 150 150 2
#> 3 3 NA NA NA NaN 0
#> 4 4 200 1 200 200 1
#> 5 5 300 2 250 250 2
#> 6 6 400 3 300 300 3
#> 7 7 500 3 400 400 3
#> 8 8 420 3 440 440 3
NaN and 0 in two new columns look a little bit more consistent than NAs, but it is a preference, so if you think you absolutely need NA's it is easy to substitute them with, e.g., ifelse.