I have data that looks something like this.
df <- data.frame(
Week = seq(1:10),
BA.1 = c(.55, .52, .45, .39, .25, .10, 0, NA, NA, NA),
JN.1 = c(0, 0, 0.1, 0.3, 0.56, 0.71, 0.79, NA, NA, NA),
other = c(0.24, 0.35, 0.32, 0.44, 0.29, 0.49, 0.23, NA, NA, NA),
Cases = c(22, 34, 45, 56, 79, 90, 99, 96, 93, 87)
)
df
> df
Week BA.1 JN.1 other Cases
1 1 0.55 0.00 0.24 22
2 2 0.52 0.00 0.35 34
3 3 0.45 0.10 0.32 45
4 4 0.39 0.30 0.44 56
5 5 0.25 0.56 0.29 79
6 6 0.10 0.71 0.49 90
7 7 0.00 0.79 0.23 99
8 8 NA NA NA 96
9 9 NA NA NA 93
10 10 NA NA NA 87
I would like to dynamically calculate a 3-week rolling average and replace the NA
values in BA.1
, JN.1
, and other
columns with this 3-week rolling average value. When I say dynamic, the solution I am looking for would have to detect an NA value (possibly through a case_when()
statement) rather than implicitly knowing which rows are NA
. I am prospectively updating this data, so the rows where the NA values exist change often for these columns.
This also means that the rolling average calculated in the row prior will be used in the calculation for the next row. An ideal output (after rounding 2 decimal places) would look like this example df.
> output_df
Week BA.1 JN.1 other Cases
1 1 0.55 0.00 0.24 22
2 2 0.52 0.00 0.35 34
3 3 0.45 0.10 0.32 45
4 4 0.39 0.30 0.44 56
5 5 0.25 0.56 0.29 79
6 6 0.10 0.71 0.49 90
7 7 0.00 0.79 0.23 99
8 8 0.12 0.69 0.34 96
9 9 0.07 0.73 0.35 93
10 10 0.06 0.74 0.31 87