如何用R data.table关联手术与感染并判定术后感染？

• 现有四个data.table：三个表分别记录三种不同手术的日期，一个表记录感染日期及感染诊断体征。需完成两项操作：
  1. 识别与感染相关的手术：找到感染发生前最后一次实施、且与感染日期间隔小于1年的手术；
  2. 按预设标准判定是否为真实感染：感染发病时间为感染日期与该手术日期的差值，需满足术后1年内发病、fever=yes、discharge=yes、culture=positive。
• 曾尝试用merge函数合并各表，再用as.duration计算日期差，但结果不符合预期，寻求解决方法。

示例数据 #

dt1 = data.table(
  participant.id = c("1","2","3", "3"),
  date.procedure1 = c("2000-11-19", "2003-08-29", "2000-01-08", "2002-03-08"),
  repeat.instance.procedure1 = c("1", "1", "1", "2")
)
dt2 = data.table (participant.id = c("1","2","3"),
                  date.procedure2 = c("2000-10-19", "2003-07-02", "1999-12-12"),
                  repeat.instance.procedure2 = c("1", "1", "1")
)
dt3 = data.table (participant.id = c("1","1", "2","2" ,"3"),
                  date.procedure3 = c("2002-10-19","2004-10-10", "2006-10-02", "2010-10-10", "2009-01-12"),
                  repeat.instance.procedure3 = c("1", "2", "1", "2", "1")
)
dt4 = data.table (
  participant.id = c("1", "2", "3"),
  date.infection = c("2001-05-10", "2007-02-10", "2002-03-25"),
  repeat.instance.infection = c("1", "1", "1"),
  fever = c("yes", "no", "yes"),
  discharge = c("yes", "no", "yes"),
  culture = c("positive", "positive","negative"),
  pain = c("yes", "yes", "yes")
)

步骤1：统一手术表结构并合并 #

先把三个手术表整理为相同结构，标记手术类型后合并成总手术表：

library(data.table)

# 标准化dt1结构
dt1[, `:=`(procedure_type = "procedure1", date_procedure = as.Date(date.procedure1))]
dt1 = dt1[, .(participant.id, procedure_type, date_procedure, repeat.instance = repeat.instance.procedure1)]

# 标准化dt2结构
dt2[, `:=`(procedure_type = "procedure2", date_procedure = as.Date(date.procedure2))]
dt2 = dt2[, .(participant.id, procedure_type, date_procedure, repeat.instance = repeat.instance.procedure2)]

# 标准化dt3结构
dt3[, `:=`(procedure_type = "procedure3", date_procedure = as.Date(date.procedure3))]
dt3 = dt3[, .(participant.id, procedure_type, date_procedure, repeat.instance = repeat.instance.procedure3)]

# 合并所有手术记录
all_procedures = rbind(dt1, dt2, dt3)

步骤2：关联感染表并筛选相关手术 #

转换感染日期格式，关联手术记录后筛选出感染前1年内的手术，再取每个参与者的最后一次符合条件的手术：

# 转换感染日期为日期格式
dt4[, date_infection := as.Date(date.infection)]

# 关联感染表与手术表，筛选感染前1年内的手术
merged_data = dt4[all_procedures, on = "participant.id", allow.cartesian = TRUE]
merged_data = merged_data[date_procedure < date_infection & date_infection - date_procedure <= 365]

# 按参与者分组，取感染前最后一次手术
relevant_procedures = merged_data[order(participant.id, date_procedure), 
                                 .SD[.N], by = participant.id]

步骤3：判定真实感染 #

根据预设标准添加感染判定结果：

# 计算术后发病天数
relevant_procedures[, days_post_procedure := date_infection - date_procedure]

# 执行真实感染判定
relevant_procedures[, is_true_infection := 
                      (days_post_procedure <= 365) & 
                      (fever == "yes") & 
                      (discharge == "yes") & 
                      (culture == "positive")]

最终结果示例 #

运行上述代码后，relevant_procedures表会输出每个参与者的关联手术信息及感染判定：

#    participant.id procedure_type date_procedure repeat.instance date_infection fever discharge culture pain days_post_procedure is_true_infection
# 1:              1     procedure1     2000-11-19               1     2001-05-10   yes       yes positive  yes                172              TRUE
# 2:              2     procedure3     2006-10-02               1     2007-02-10    no        no positive  yes                131             FALSE
# 3:              3     procedure1     2002-03-08               2     2002-03-25   yes       yes negative  yes                17             FALSE

关键说明 #

• 先统一手术表结构再合并，避免全量merge导致的数据冗余，提升筛选效率；
• 用data.table的.SD[.N]语法，按日期排序后直接取每组最后一条记录，精准定位感染前最后一次符合条件的手术；
• 直接用日期对象相减获取天数，比as.duration更直观，便于判断是否在1年有效期内。

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