dput of the input dataframe :
structure(list(Entity = c("A", "A", "A", "A", "A", "A", "A",
"A", "A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B"
), Date = structure(c(1640995200, 1641081600, 1641168000, 1641254400,
1641340800, 1641427200, 1641513600, 1641600000, 1641686400, 1641772800,
1640995200, 1641081600, 1641168000, 1641254400, 1641340800, 1641427200,
1641513600, 1641600000, 1641686400, 1641772800), tzone = "UTC", class = c("POSIXct",
"POSIXt")), Test = c("Y", "Y", "N", "Y", "N", "N", "Y", "Y",
"Y", "Y", "Y", "Y", "N", "Y", "N", "N", "Y", "Y", "Y", "Y"),
Value = c(5, 10, 5, 10, 10, 5, 5, 5, 5, 20, 10, 10, 5, 20,
20, 5, 5, 20, 20, 20), COUNTER = c(1L, 2L, 3L, 4L, 5L, 6L,
7L, 8L, 9L, 10L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L)), class = c("tbl_df",
"tbl", "data.frame"), row.names = c(NA, -20L))
I would like to get a final dataframe that shows running 3 day average per entity where Test = 'Y'. For ex., based on the below screenshot,
the running averages for the entity A would be:
Entity Counter_Running_avg Running_Avg
A 1 7.5 (15/2)
A 2 10 (10/1)
A 3 5 (15/3) and so on..
I began by writing the below code but this is not what I need…
dt %>%
arrange(Entity, Date) %>%
group_by(Entity) %>%
filter(Test = 'Y') %>%
summarise(Avg = mean(head(Value, 3), na.rm = TRUE))
>Solution :
df %>%
group_by(Entity,id = (COUNTER - 1) %/%3 + 1) %>%
summarise(Running_Avg = mean(Value[Test == 'Y']), .groups = 'drop')
# Groups: Entity [2]
Entity id Running_Avg
<chr> <dbl> <dbl>
1 A 1 7.5
2 A 2 10
3 A 3 5
4 A 4 20
5 B 1 10
6 B 2 20
7 B 3 15
8 B 4 20