R语言按ID合并行并保留各列自定义优先级的主导值

通用实现思路 #

• 预先定义优先级向量，优先级越高的元素排在越靠前的位置
• 按ID分组后，对每列筛选出优先级最高的非NA值即可

dplyr 实现代码 #

场景1：优先级规则 C > B > A #

library(dplyr)

# 构造原始数据
data <- data.frame(ID = c(1,1,2,3,3),
                   V1 = c("A","B","A","B","C"),
                   V2 = c("C","B",NA,"B","A"),
                   V3 = c("A","B","C","B",NA))

# 定义优先级，顺序为 高优先级 -> 低优先级
priority_levels <- c("C", "B", "A")

# 分组聚合
result <- data %>%
  group_by(ID) %>%
  summarise(across(V1:V3, ~factor(., levels = priority_levels) %>% 
                     sort(na.last = TRUE) %>% 
                     first() %>% 
                     as.character()))

运行后输出结果和预期完全一致：

场景2：自定义优先级 yes > no > unsure #

仅需要修改优先级向量的定义，聚合逻辑完全复用：

# 构造原始数据
data <- data.frame(ID = c(1,1,2,3,3),
                   V1 = c("yes","no","yes","no","unsure"),
                   V2 = c("unsure","no",NA,"no","yes"),
                   V3 = c("yes","no","unsure","no",NA))

# 自定义优先级
priority_levels <- c("yes", "no", "unsure")

# 聚合逻辑与上文完全一致
result <- data %>%
  group_by(ID) %>%
  summarise(across(V1:V3, ~factor(., levels = priority_levels) %>% 
                     sort(na.last = TRUE) %>% 
                     first() %>% 
                     as.character()))

大数据量优化方案 #

如果处理的数据集量级较大，可以用match方法省略因子转换步骤，提升运行效率：

result <- data %>%
  group_by(ID) %>%
  summarise(across(V1:V3, ~.[na.omit(match(., priority_levels)) %>% which.min()]))

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