按days最小值筛选Real_sample重复ID，保留唯一有效ID

原始数据 #

df <- structure(list(Deelnemernr. = c("4781061T0", "4781074T0", "4781076T0", 
"4781087T0", "4781047T0", "4781027T0", "4781024T0", "4781022T0", 
"4781017T0", "4781023T0", "4781078T0", "4781079T0", "4781063T0", 
"4781008T0", "4781026T0", "4781014T0", "4781015T0", "4781072T0", 
"4781046T0", "4781032T0", "4781077T0", "4781031T0", "4781030T0", 
"4781052T0", "4781056T0", "4781004T0", "4781043T0", "4781090T0", 
"4781036T0", "4781028T0", "4781053T0", "4781068T0", "4781048T0", 
"4781041T0", "4781050T0", "4781542T0", "4781095T0", "4781020T0", 
"4781097T0", "4781182T0"), Real_sample = c("4781061T0", "4781074T0", 
"4781076T0", "4781087T0", "4781047T0", "4781027T0", "4781024T0", 
"4781022T0", "4781017T0", "4781023T0", "4781078T0", "4781079T0", 
"4781063T0", "4781008T0", "4781026T0", "4781014T0", "", "4781072T0", 
"4781061T0", "4781032T0", "4781077T0", "4781031T0", "4781030T0", 
"", "4781056T0", "4781004T0", "4781043T0", "4781090T0", "4781036T0", 
"4781028T0", "4781053T0", "4781068T0", "4781048T0", "4781041T0", 
"4781050T0", "4781542T0", "4781095T0", "4781020T0", "4781097T0", 
"4781182T0"), days = c(16, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, 14, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), row.names = c(NA, 
-40L), class = c("tbl_df", "tbl", "data.frame"))

需求 #

检查Real_sample列中的重复ID，对每个重复ID仅保留对应days值最小的那一行的ID，其余行的该ID置为空（支持ID重复2次及以上的场景），最终使Real_sample列仅包含唯一有效ID。

解决方案 #

使用dplyr包实现分组标记与修改：

library(dplyr)

processed_df <- df %>%
  # 添加行号列，确保后续处理不打乱原始顺序
  mutate(row_id = row_number()) %>%
  # 按Real_sample分组处理
  group_by(Real_sample) %>%
  # 标记需要保留ID的行：空值直接保留；非空组保留days最小的行
  mutate(
    keep_flag = ifelse(Real_sample == "", 
                       TRUE, 
                       row_id == row_id[which.min(days)])
  ) %>%
  ungroup() %>%
  # 根据标记修改Real_sample列
  mutate(Real_sample = ifelse(keep_flag, Real_sample, "")) %>%
  # 移除辅助列
  select(-row_id, -keep_flag)

代码解释 #

1. 添加行号：row_id列用于记录原始行顺序，避免分组操作打乱行的排列
2. 分组标记：按Real_sample分组后，对每个组：
   • 空值行直接标记为保留（保持原状态）
   • 非空组中，通过which.min(days)找到days最小的行对应的row_id，标记该行需要保留ID
3. 修改列值：根据keep_flag将不需要保留的行的Real_sample置为空字符串
4. 清理辅助列：移除用于处理的row_id和keep_flag列，得到最终结果

处理后结果 #

structure(list(Deelnemernr. = c("4781061T0", "4781074T0", "4781076T0", 
"4781087T0", "4781047T0", "4781027T0", "4781024T0", "4781022T0", 
"4781017T0", "4781023T0", "4781078T0", "4781079T0", "4781063T0", 
"4781008T0", "4781026T0", "4781014T0", "4781015T0", "4781072T0", 
"4781046T0", "4781032T0", "4781077T0", "4781031T0", "4781030T0", 
"4781052T0", "4781056T0", "4781004T0", "4781043T0", "4781090T0", 
"4781036T0", "4781028T0", "4781053T0", "4781068T0", "4781048T0", 
"4781041T0", "4781050T0", "4781542T0", "4781095T0", "4781020T0", 
"4781097T0", "4781182T0"), Real_sample = c("", "4781074T0", "4781076T0", 
"4781087T0", "4781047T0", "4781027T0", "4781024T0", "4781022T0", 
"4781017T0", "4781023T0", "4781078T0", "4781079T0", "4781063T0", 
"4781008T0", "4781026T0", "4781014T0", "", "4781072T0", "4781061T0", 
"4781032T0", "4781077T0", "4781031T0", "4781030T0", "", "4781056T0", 
"4781004T0", "4781043T0", "4781090T0", "4781036T0", "4781028T0", 
"4781053T0", "4781068T0", "4781048T0", "4781041T0", "4781050T0", 
"4781542T0", "4781095T0", "4781020T0", "4781097T0", "4781182T0"
), days = c(16, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, 14, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), row.names = c(NA, 
-40L), class = c("tbl_df", "tbl", "data.frame"))

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