如何用dplyr按复杂分组条件筛选raw_data数据框？

现有数据框raw_data，需按以下4个条件筛选分组数据：

• 分组内第一条amount值>0
• 分组内最后一条amount值<0
• 分组内第一条到最后一条之间存在amount值>0的记录
• 分组内amount值>0的记录数>3

你当前使用的代码仅覆盖了前两个条件，未匹配全部需求，代码如下：

library(tidyverse)
raw_data <- data.frame(type=c("A","A","A","A","A","A","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B","B","B","B","B","B"),
                       amount=c(-8, -30, 20, 28, 26, -1, 18, 10, -2, 9, 58, 8, 51, 24, -7, -31, 27, 27, 16, 53, -59, -9, 11, 48, 34, 45, -56, 25, 25, 35))
# 无法实现需求的代码
raw_data %>% group_by(type) %>% filter(first(amount)>0,last(amount)<0)

正确的筛选代码 #

library(tidyverse)

raw_data <- data.frame(type=c("A","A","A","A","A","A","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B","B","B","B","B","B"),
                       amount=c(-8, -30, 20, 28, 26, -1, 18, 10, -2, 9, 58, 8, 51, 24, -7, -31, 27, 27, 16, 53, -59, -9, 11, 48, 34, 45, -56, 25, 25, 35))

filtered_data <- raw_data %>%
  group_by(type) %>%
  filter(
    first(amount) > 0,                # 条件1：分组第一条amount大于0
    last(amount) < 0,                 # 条件2：分组最后一条amount小于0
    any(amount[-c(1, n())] > 0),      # 条件3：首尾记录之间存在amount大于0的条目
    sum(amount > 0) > 3               # 条件4：分组内amount大于0的记录数超过3条
  ) %>%
  ungroup() # 可选，取消分组状态

print(filtered_data)

代码说明 #

• 原代码仅实现了前两个筛选条件，未覆盖后两个核心要求，因此无法得到预期结果。
• 条件3通过amount[-c(1, n())]排除分组的首尾记录，再用any()判断剩余记录中是否存在大于0的值。
• 条件4通过sum(amount > 0)统计分组内符合amount>0的记录数量，再判断是否超过3条。

注意：你的示例数据中，A、B两组的第一条amount均为负数，因此最终筛选结果为空数据框。若要得到非空结果，需调整数据使其满足所有4个条件。

内容的提问来源于stack exchange，提问作者anderwyang