r - 根据条件从每组的列中获取行

Question

我有一个data.frame如下图:

基本要求是在每组某个日期之后找到“n”个“值”的平均值。

例如:，用户提供:

Certain Date = Failure Date

n = 4

因此，对于 A，平均值为 (60+70+80+100)/4 ；忽略 NAs

对于 B，平均值为 (80+90+100)/3。注意 B，n=4 不会发生，因为在满足条件 failuredate = valuedate 之后只有 3 个值。

这里是 dput:

structure(list(Name = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("A", 
"B"), class = "factor"), FailureDate = structure(c(1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L), .Label = c("1/5/2020", "1/7/2020"), class = "factor"), ValueDate = structure(c(1L, 
3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 2L, 1L, 3L, 4L, 5L, 6L, 7L, 
8L, 9L, 10L, 2L), .Label = c("1/1/2020", "1/10/2020", "1/2/2020", 
"1/3/2020", "1/4/2020", "1/5/2020", "1/6/2020", "1/7/2020", "1/8/2020", 
"1/9/2020"), class = "factor"), Value = c(10L, 20L, 30L, 40L, 
NA, 60L, 70L, 80L, NA, 100L, 10L, 20L, 30L, 40L, 50L, 60L, 70L, 
80L, 90L, 100L)), class = "data.frame", row.names = c(NA, -20L
))

Answer 1

我们可以在按“名称”分组后使用 cumsum 创建一个索引，提取“值”元素并获取平均值

library(dplyr)
n <- 4
df1 %>%
   type.convert(as.is = TRUE) %>% 
   group_by(Name) %>% 
   summarise(Ave = mean(head(na.omit(Value[lag(cumsum(FailureDate == ValueDate),
        default = 0) > 0]), n), na.rm = TRUE))
# A tibble: 2 x 2
#  Name    Ave
#  <chr> <dbl>
#1 A      77.5
#2 B      90