R语言基于条件分组统计：诊断数据分组计数需求

问题背景 #

给定如下data.table数据集：

dt <- data.table(ID = c(1,1,2,3,4,5,5,6,7, 8, 9,9, 10, 10, 11, 12, 13,14, 15),
                 diagnosis = c("A", "B", "A", "B", "C", "A", "B", "A", "B", "C", "A", "A", "A", "B", "C", "A", "A", "A"),
                 Date = c("2013-01-01","2013-01-01","2010-01-12", "2013-01-12", "2013-01-26", "2010-01-02", "2010-01-02", "2010-01-14", "2011-01-12", "2012-01-12", "2010-01-12", "2013-01-02","2013-01-02","2010-01-12", "2013-01-12", "2013-01-26", "2013-01-14", "2013-01-28", "2020-01-01"))

需按以下分组统计各诊断（A、B、C）的人数：

• only_2013：仅在2013年有诊断记录的患者
• only_10_12：仅在2010-2012年有诊断记录的患者
• both：在上述两个时间段均有诊断记录的患者
• none：在2013年及2010-2012年都无诊断记录的患者（本例仅1人）

诊断优先级规则 #

同一患者在同一天有多个诊断时：

1. 若包含诊断A，则统计为A
2. 若仅包含B和C，则统计为B

期望输出格式 #

实现代码 #

library(data.table)

# 转换日期格式并划分时间段
dt[, Date := as.Date(Date)]
dt[, year := year(Date)]
dt[, period := fifelse(year == 2013, "2013", 
                       fifelse(between(year, 2010, 2012), "10_12", "other"))]

# 按ID和日期处理同天多诊断的优先级
dt[, final_diag := if ("A" %in% diagnosis) "A" else if (all(diagnosis %in% c("B", "C"))) "B" else diagnosis, by = .(ID, Date)]

# 去重：每个ID+日期保留最终诊断
dt_unique <- unique(dt[, .(ID, final_diag, period)])

# 确定每个患者所属的分组
patient_groups <- dt_unique[, .(
  has_2013 = any(period == "2013"),
  has_10_12 = any(period == "10_12")
), by = ID][, group := fifelse(
  has_2013 & !has_10_12, "only_2013",
  fifelse(!has_2013 & has_10_12, "only_10_12",
          fifelse(has_2013 & has_10_12, "both", "none"))
)]

# 合并患者分组与最终诊断，统计各分组的诊断人数
result <- dt_unique[patient_groups, on = "ID"][, .N, by = .(final_diag, group)]

# 转换为宽表并填充缺失值为0
result_wide <- dcast(result, final_diag ~ group, value.var = "N", fill = 0)

# 重命名列名匹配期望格式
setnames(result_wide, "final_diag", "诊断")

# 输出结果
print(result_wide)

运行结果 #

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