R data.table实操：将ID与Score重复组的指定列数值置零

实现方案 #

你不需要删除行，只要用duplicated()定位到ID和Score组合的重复行（首次出现的行保留，后续重复行标记为TRUE），再对标记行的年份列批量赋值为0即可，data.table的引用赋值语法可以高效完成这个操作。

library(data.table)
# 生成示例数据
data = data.table(
  ID = c("a1", "a2", "a2", "a1", "a2", "a1", "a1"),
  Score = c("A","B","C","A","B","C","A"),
  "2018" = c(3,5,1,3,5,6,6),
  "2019" = c(3,5,6,2,1,4,2),
  "2020" = c(9,6,6,9,6,9,9),
  "2021" = c(4,0,3,8,5,4,6))
data <- data[order(ID, Score)]

# 核心实现代码
# 1. 定义要修改的年份列
modify_cols <- as.character(2018:2021)
# 2. 定位ID+Score重复的行（首次出现之外的所有重复行），批量赋值为0
data[duplicated(data, by = c("ID", "Score")), (modify_cols) := 0]

结果验证 #

# 生成预期输出对照
solution = data.table(
  ID = c("a1", "a2", "a2", "a1", "a2", "a1", "a1"),
  Score = c("A","B","C","A","B","C","A"),
  "2018" = c(3,5,1,0,0,6,6),
  "2019" = c(3,5,6,2,1,4,0),
  "2020" = c(9,6,6,0,0,9,0),
  "2021" = c(4,0,3,8,5,4,6))
solution <- solution[order(ID, Score)]

identical(data, solution) # 运行返回TRUE，和预期结果完全一致

补充说明 #

• duplicated(data, by = c("ID", "Score")) 显式指定按ID、Score两列判断重复，避免其他列干扰判断逻辑
• (modify_cols) := 0 是data.table特有的批量修改多列的语法，操作直接在原对象上修改，不需要额外拷贝数据，处理大表时性能优势明显
• 如果需要保留最后一次出现的行、把之前的重复行置零，把duplicated替换为duplicated(data, by = c("ID", "Score"), fromLast = TRUE)即可

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