在R语言中按ID分组移除首次成功年份后的行

需要按id分组处理数据框，规则如下：

• 若某个id存在非0的first_success值，移除最早成功年份之后的所有观测行
• 若id的first_success全为0，则保留所有行

初始测试数据框：

df <- data.frame(year = c("2000", "2000", "2001", "2001", "2002", "2002", "2003", "2007", "2008"), 
                 id = c("A", "A", "A", "A", "A", "A", "A", "B", "B"), 
                 product = c("1", "2", "3", "4", "5", "6", "7", "8", "9"), 
                 market = c("CHN", "USA", "CHN", "CAN", "CHN", "ECU", "CHN", "ESP", "IRL"), 
                 FP = c(1, 1, 1, 1, 1, 1, 1, 1, 1),
                 FM = c(1, 1, 0, 1, 0, 1, 0, 1, 1),
                 first_success = c(0,0,0,0,2002,2002,2002,0, 0)
                 )

预期结果：移除第7行（id=A的2003年数据）。

补充复杂场景数据框（存在成功后first_success再次为0、或多个不同成功年份的情况）：

df <- data.frame(year = c("2000", "2000", "2001", "2001", "2002", "2002", "2003", "2004", "2005", "2007", "2008"), 
                 id = c("A", "A", "A", "A", "A", "A", "A", "A", "A", "B", "B"), 
                 product = c("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11"), 
                 market = c("CHN", "USA", "CHN", "CAN", "CHN", "ECU", "CHN", "NZL", "ESP", "IRL", "POR"), 
                 FP = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1),
                 FM = c(1, 1, 0, 1, 0, 1, 0, 1, 1, 1, 1),
                 first_success = c(0,0,0,0,2002,2002,2002,0, 2005, 0, 0)
)

预期结果：移除id=A的2003、2004、2005年数据（第7-9行）。

尝试的错误代码：

df %>% 
 group_by(id) %>%
 subset(., first_succes!=0 & first_success<year)
 filter(if(any(first_success)!=0) year<=first_success else TRUE)

该代码未实现预期效果，反而保留了不符合条件的行。

核心问题分析 #

1. year字段是字符型，无法直接与数值型的first_success进行大小比较
2. 需以每个id的最早成功年份（即first_success中非0值的最小值）作为分界点，忽略后续出现的0或其他年份值

实现代码 #

library(dplyr)

df_new <- df %>%
  # 将year转为数值型，确保年份比较逻辑正确
  mutate(year = as.numeric(year)) %>%
  group_by(id) %>%
  # 计算每个id的最早成功年份：取first_success中非0值的最小值
  mutate(earliest_success = min(first_success[first_success != 0], na.rm = TRUE)) %>%
  # 处理无成功年份的情况：当min返回-Inf时，替换为Inf，确保所有行都满足筛选条件
  mutate(earliest_success = ifelse(is.infinite(earliest_success) && earliest_success < 0, Inf, earliest_success)) %>%
  # 筛选：年份不晚于最早成功年份，或无成功年份的所有行
  filter(year <= earliest_success) %>%
  # 移除临时计算列，若需要可将year转回字符型
  select(-earliest_success) %>%
  mutate(year = as.character(year)) %>%
  ungroup()

代码说明 #

• 转换year为数值型：解决字符与数值无法直接比较的问题
• 计算earliest_success：精准定位每个id的首次成功年份，避免后续无效的0或其他年份干扰
• 处理无成功年份的id：用Inf作为阈值，确保所有行都能通过筛选
• 最后恢复数据格式并取消分组，得到符合预期的结果

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