使用Dplyr实现两列匹配时按自定义顺序取第三列最大值

完整可运行代码 #

library(dplyr)

# 构造示例数据框
df <- tibble(
  ID = c(2, 2, 3, 3, 3, 4),
  CAT = c("A", "A", "B", "B", "A", "A"),
  VAL = c("I", "II", "I", "I", "Unknown", "III")
)

# 核心筛选逻辑
result <- df %>%
  # 将VAL转为有序因子，levels按优先级从低到高排列：I < II < III < IV < Unknown
  mutate(VAL = factor(VAL, levels = c("I", "II", "III", "IV", "Unknown"), ordered = TRUE)) %>%
  # 按ID、CAT两列分组
  group_by(ID, CAT) %>%
  # 每组取优先级最高的唯一行，with_ties=FALSE避免相同优先级返回多行
  slice_max(VAL, n = 1, with_ties = FALSE) %>%
  # 可选步骤：将VAL转回字符型，取消有序因子属性
  mutate(VAL = as.character(VAL)) %>%
  ungroup()

输出结果 #

运行后得到的result和需求预期完全一致：

# A tibble: 4 × 3
     ID CAT   VAL    
  <dbl> <chr> <chr>  
1     2 A     II     
2     3 A     Unknown
3     3 B     I      
4     4 A     III    

如果后续需要调整优先级规则，直接修改factor函数中levels的顺序即可。

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