R语言：按x分组后基于y、z列的DataFrame条件过滤需求

需求说明 #

按列x对DataFrame分组，根据每组中y列和z列的取值执行条件过滤，缩减记录数量。

原始代码（存在语法/逻辑问题） #

# 所需包
library(tidyverse)

# 数据过滤
job_history <- data.frame(x = c(1, 1, 1, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4),
                          y = c("Hire", "Data Change", "Leave of Absence", "Hire", "Termination", "Hire", "Termination", "Rehire", "Transfer", "Hire", "Termination", "Rehire", "Termination"),
                          z = as.Date(c("2024-01-01", "2024-02-01", "2024-03-01", "2024-01-01", "2024-02-01", "2024-01-01", "2024-02-01", "2024-03-01", "2024-04-01", "2024-01-01", "2024-02-01", "2024-03-01", "2024-04-01")))

# 分组并条件过滤
job_history %>% 
  group_by(x) %>% 
  # 统计Hire、Termination和Rehire记录的数量
  mutate(hires   = sum(y == "Hire"),
         terms   = sum(y == "Termination"),
         rehires = sum(y == "Rehire")) %>% 
  case_when(
    # 若有1条Hire记录，无Termination或Rehire记录，筛选最新记录
    (hires = 1 & terms = 0 & rehires = 0) ~ filter(z = max(z)),
    # 若有1条Hire和1条Termination记录，筛选Termination记录
    (hires = 1 & terms = 1 & rehires = 0) ~ filter(y == "Termination"),
    # 若各有1条Hire、Termination、Rehire记录，筛选Termination记录和最新记录
    (hires = 1 & terms = 1 & rehires = 1) ~ filter(y == "Termination") | z = max(z)),
    # 若有1条Hire、2条Termination和1条Rehire记录，筛选所有Termination记录
    (hires = 1 & terms = 2 & rehires = 1) ~ filter(y == "Termination")
  )

期望输出 #

x       y                   z
1       Leave of Absence    2024-03-01
2       Termination         2024-02-01
3       Termination         2024-02-01
3       Transfer            2024-04-01
4       Termination         2024-02-01
4       Termination         2024-04-01

问题分析与修正代码 #

原代码存在几个关键问题：

1. 条件判断误用赋值符号=，应为比较符号==，属于语法错误。
2. case_when无法直接嵌套filter操作，需生成逻辑向量标记需保留的行。
3. 部分条件的括号匹配、逻辑写法有误。

修正后的代码如下：

library(tidyverse)

job_history <- data.frame(x = c(1, 1, 1, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4),
                          y = c("Hire", "Data Change", "Leave of Absence", "Hire", "Termination", "Hire", "Termination", "Rehire", "Transfer", "Hire", "Termination", "Rehire", "Termination"),
                          z = as.Date(c("2024-01-01", "2024-02-01", "2024-03-01", "2024-01-01", "2024-02-01", "2024-01-01", "2024-02-01", "2024-03-01", "2024-04-01", "2024-01-01", "2024-02-01", "2024-03-01", "2024-04-01")))

job_history %>%
  group_by(x) %>%
  mutate(
    hires = sum(y == "Hire"),
    terms = sum(y == "Termination"),
    rehires = sum(y == "Rehire"),
    max_z = max(z)
  ) %>%
  filter(
    # 条件1：1条Hire，无Termination/Rehire，保留最新记录
    (hires == 1 & terms == 0 & rehires == 0 & z == max_z) |
    # 条件2：1条Hire+1条Termination，保留Termination记录
    (hires == 1 & terms == 1 & rehires == 0 & y == "Termination") |
    # 条件3：1条Hire+1条Termination+1条Rehire，保留Termination和最新记录
    (hires == 1 & terms == 1 & rehires == 1 & (y == "Termination" | z == max_z)) |
    # 条件4：1条Hire+2条Termination+1条Rehire，保留所有Termination记录
    (hires == 1 & terms == 2 & rehires == 1 & y == "Termination")
  ) %>%
  select(-hires, -terms, -rehires, -max_z) %>%  # 移除辅助计算列
  ungroup()

运行上述代码后，即可得到预期的输出结果。

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