基于Patient-Year组的诊断用药匹配及精度过滤的数据清洗需求

一、基础需求处理：剔除多诊断组并去重重复诊断 #

原始数据 #

df1 <- data.frame(
  Patient = c("John", "John", "John", "Jane", "Jane", "Mary", "Mary"),
  Year = c(2018, 2018, 2019, 2020, 2021, 2018, 2018),
  Diagnosis = c("a","b","a","b","a","a","a")
)

df2 <- data.frame(
  Patient = c("John", "John", "Jane", "Jane", "Mary"),
  Year = c(2018, 2019, 2018, 2021, 2018),
  Medication = c("Drug A", "Drug A", "Drug A", "Drug B", "Drug B")
)

处理规则 #

• 同一Patient-Year组内存在多个唯一诊断值 → 剔除该组所有记录
• 同一组内诊断重复 → 仅保留1条记录
• 保留无诊断的用药记录（如Jane 2018）

实现代码（dplyr） #

library(dplyr)

# 合并诊断与用药数据
df_merge <- df1 %>%
  full_join(df2, by = c("Patient", "Year"))

# 分组清洗数据
result_basic <- df_merge %>%
  group_by(Patient, Year) %>%
  mutate(
    # 统计组内非NA的唯一诊断数量
    unique_diagnosis = n_distinct(Diagnosis, na.rm = TRUE),
    # 标记组内是否有有效诊断
    has_valid_diag = any(!is.na(Diagnosis))
  ) %>%
  # 筛选符合条件的记录：要么是无诊断的用药，要么是组内仅1种诊断的记录
  filter(is.na(Diagnosis) | (has_valid_diag & unique_diagnosis == 1)) %>%
  # 去重，每组保留一条诊断记录
  distinct(Patient, Year, Diagnosis, Medication, .keep_all = TRUE) %>%
  ungroup() %>%
  # 移除辅助计算列
  select(-unique_diagnosis, -has_valid_diag)

# 输出结果
result_basic

输出结果 #

Patient Year Diagnosis Medication
1    John 2019         a     Drug A
2    Jane 2020         b       <NA>
3    Jane 2021         a     Drug B
4    Mary 2018         a     Drug B
5    Jane 2018      <NA>     Drug A

二、新增诊断精度列后的处理：优先保留高精度记录 #

原始数据（带诊断精度） #

df1_with_accuracy <- data.frame(
  Patient = c("John", "John", "John", "Jane", "Jane", "Mary", "Mary"),
  Year = c(2018, 2018, 2019, 2020, 2021, 2018, 2018),
  Diagnosis = c("a","b","a","b","a","a","a"),
  DiagnosticAccuracy = c("high", "low", "high", "high", "low", "high", "low")
)

新增处理规则 #

• 同一Patient-Year组内同时存在"high"和"low"精度的诊断 → 剔除所有"low"精度的记录
• 同时遵守基础需求的所有规则

实现代码（dplyr） #

# 合并带精度的诊断与用药数据
df_merge_accuracy <- df1_with_accuracy %>%
  full_join(df2, by = c("Patient", "Year"))

# 分组清洗数据
result_accuracy <- df_merge_accuracy %>%
  group_by(Patient, Year) %>%
  mutate(
    # 标记组内是否同时存在高低精度
    has_both_acc = all(c("high", "low") %in% DiagnosticAccuracy[!is.na(DiagnosticAccuracy)]),
    # 统计组内非NA的唯一诊断数量
    unique_diagnosis = n_distinct(Diagnosis, na.rm = TRUE),
    # 标记组内是否有有效诊断
    has_valid_diag = any(!is.na(Diagnosis))
  ) %>%
  # 筛选条件：
  # 1. 无诊断的用药记录保留；
  # 2. 有诊断的组：仅1种诊断，且（不同时存在高低精度 或 精度为high）
  filter(
    is.na(Diagnosis) | 
    (has_valid_diag & unique_diagnosis == 1 & 
     (!has_both_acc | DiagnosticAccuracy == "high"))
  ) %>%
  # 去重，保留唯一组合
  distinct(Patient, Year, Diagnosis, DiagnosticAccuracy, Medication, .keep_all = TRUE) %>%
  ungroup() %>%
  # 移除辅助计算列
  select(-has_both_acc, -unique_diagnosis, -has_valid_diag)

# 输出结果
result_accuracy

输出结果 #

Patient Year Diagnosis DiagnosticAccuracy Medication
1    John 2019         a               high     Drug A
2    Jane 2020         b               high       <NA>
3    Jane 2021         a                low     Drug B
4    Mary 2018         a               high     Drug B
5    Jane 2018      <NA>               <NA>     Drug A

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