基于R语言清洗TB药物登记数据：提取特定药物多疗程信息
2026-8-5
问题
我正在清洗结核病（TB）药物登记数据，之前在Stata中完成了相关工作，现在想迁移到R语言并优化生成的新变量。
现有数据包含60组描述药物方案的变量，每组由drugnameN、drugstartdateN、drugenddateN、drugdoseN（例如"500"）、drugunitN（例如"mg"）组成，其中N从1到60。drugnameN未标准化，比如贝达喹啉（bdq）可能出现在任意drugnameN列中。
最终需求是生成与特定药物关联的变量，以bdq为例，需要生成onbdq1、bdqstartdate1、bdqenddate1、bdqdose1、onbdq2……这类变量，对应患者的多段用药疗程。
示例输入数据
# 示例输入数据 mre <- as.data.frame(structure(list(drugname1 = c("Bedaquiline", "Bedaquiline", "Bedaquiline", NA, "Amikacin"), drugstartdate1 = structure(c(18875, 18383, 18795, NA, 16743), class = "Date"), drugenddate1 = structure(c(NA, NA, 18808, NA, NA), class = "Date"), drugdose1 = c(NA, "400", "200", NA, NA), drugunit1 = c(NA, "mg", "mg", NA, NA), drugname2 = c("Levofloxacin", "Levofloxacin", "Levofloxacin", "p-Aminosalicylic Acid", "Terizidone" ), drugstartdate2 = structure(c(18875, 18383, 18795, 16709, 16743 ), class = "Date"), drugenddate2 = structure(c(NA, NA, 19139, NA, NA), class = "Date"), drugdose2 = c(NA, "750", "1000", NA, NA), drugunit2 = c(NA, "mg", "mg", "mg", NA), drugname3 = c("Linezolid", "Linezolid", "Linezolid", "Ethionamide", "Ethambutol"), drugstartdate3 = structure(c(18875, 18383, 18795, 16709, 16743), class = "Date"), drugenddate3 = structure(c(NA, 18438, 19139, NA, NA), class = "Date"), drugdose3 = c(NA, "600", "600", NA, NA), drugunit3 = c(NA, "mg", "mg", "mg", NA)), row.names = c(NA, 5L), class = "data.frame"))
期望输出（以bdq为例）
# 期望输出示例 output <- as.data.frame(structure(list( onbdq1 = c(TRUE, TRUE, TRUE, FALSE, FALSE), bdqstartdate1 = structure(c(18875, 18383, 18795, NA, NA), class= "Date"), bdqenddate1 = structure(c(NA, NA, 18808, NA, NA), class = "Date"), bdqdose1 = c(NA, "400mg", "200mg", NA, NA), # 若有第二疗程则生成onbdq2等变量 onbdq2 = c(FALSE, FALSE, FALSE, FALSE, FALSE), bdqstartdate2 = c(NA, NA, NA, NA, NA), bdqenddate2 = c(NA, NA, NA, NA, NA), bdqdose2 = c(NA, NA, NA, NA, NA) )))
解决方案
通过数据重塑+分组提取的方式可以高效实现需求，步骤如下：
1. 转换为长格式数据
先将宽格式的药物数据转为长格式，便于按药物筛选和分组：
library(tidyr) library(dplyr) # 添加患者ID，保留原始行的对应关系 long_data <- mre %>% mutate(patient_id = row_number()) %>% pivot_longer( cols = -patient_id, names_to = c(".value", "drug_seq"), names_pattern = "(drugname|drugstartdate|drugenddate|drugdose|drugunit)(\\d+)" ) %>% filter(!is.na(drugname)) # 剔除无药物记录的行
2. 标准化药物名称并筛选目标药物
统一药物名称的写法，避免因大小写、别名导致的识别错误，再筛选出目标药物的记录：
# 标准化药物名称，支持多种bdq的写法 long_data <- long_data %>% mutate( std_drugname = case_when( grepl("Bedaquiline|bdq|贝达喹啉", drugname, ignore.case = TRUE) ~ "bdq", # 可扩展添加其他药物的标准化规则 TRUE ~ drugname ) ) # 筛选bdq记录，并为每个患者的用药疗程编号 bdq_data <- long_data %>% filter(std_drugname == "bdq") %>% group_by(patient_id) %>% mutate(treatment_seq = row_number()) %>% ungroup()
3. 转换回宽格式生成目标变量
将筛选后的bdq数据转回宽格式，生成所需的onbdqN、bdqstartdateN等变量：
# 合并剂量和单位字段 bdq_data <- bdq_data %>% mutate(bdqdose = ifelse(!is.na(drugdose) & !is.na(drugunit), paste(drugdose, drugunit, sep = ""), NA)) # 转换为宽格式，生成按疗程编号的变量 bdq_wide <- bdq_data %>% pivot_wider( id_cols = patient_id, names_from = treatment_seq, values_from = c(drugstartdate, drugenddate, bdqdose), names_prefix = "bdq" ) %>% # 生成onbdqN标记变量，标记该疗程是否使用bdq mutate(across(starts_with("bdqdrugstartdate"), ~!is.na(.), .names = "onbdq{gsub('bdqdrugstartdate', '', col)}")) %>% # 调整变量顺序，让标记变量对应到疗程字段前 select(patient_id, starts_with("onbdq"), everything()) %>% # 移除不需要的原始药物名称变量（可选） select(-starts_with("bdqdrugname")) # 合并回原始数据，保留所有患者记录 final_output <- mre %>% mutate(patient_id = row_number()) %>% left_join(bdq_wide, by = "patient_id") %>% select(-patient_id)
4. 批量处理所有药物
如果需要处理多种药物，可将上述步骤封装为函数，实现批量处理：
process_drug <- function(data, drug_pattern, std_name) { # 转换长格式并标准化药物名称 long_data <- data %>% mutate(patient_id = row_number()) %>% pivot_longer( cols = -patient_id, names_to = c(".value", "drug_seq"), names_pattern = "(drugname|drugstartdate|drugenddate|drugdose|drugunit)(\\d+)" ) %>% filter(!is.na(drugname)) %>% mutate( std_drugname = case_when( grepl(drug_pattern, drugname, ignore.case = TRUE) ~ std_name, TRUE ~ drugname ) ) # 筛选目标药物并生成疗程编号 drug_data <- long_data %>% filter(std_drugname == std_name) %>% group_by(patient_id) %>% mutate(treatment_seq = row_number()) %>% ungroup() %>% mutate(drug_dose_unit = ifelse(!is.na(drugdose) & !is.na(drugunit), paste(drugdose, drugunit, sep = ""), NA)) %>% # 转换宽格式 pivot_wider( id_cols = patient_id, names_from = treatment_seq, values_from = c(drugstartdate, drugenddate, drug_dose_unit), names_prefix = std_name ) %>% # 生成用药标记变量 mutate(across(starts_with(paste0(std_name, "drugstartdate")), ~!is.na(.), .names = "on{std_name}{gsub(paste0(std_name, 'drugstartdate'), '', col)}")) %>% # 调整变量顺序并重命名剂量字段 select(patient_id, starts_with(paste0("on", std_name)), everything()) %>% rename_with(~gsub("drug_dose_unit", "dose", .x), starts_with(paste0(std_name, "drug_dose_unit"))) return(drug_data) } # 批量处理bdq和左氧氟沙星 bdq_processed <- process_drug(mre, "Bedaquiline|bdq", "bdq") lvx_processed <- process_drug(mre, "Levofloxacin|lvx", "lvx") # 合并所有药物处理结果 final_data <- mre %>% mutate(patient_id = row_number()) %>% left_join(bdq_processed, by = "patient_id") %>% left_join(lvx_processed, by = "patient_id") %>% select(-patient_id)
内容的提问来源于stack exchange，提问作者Brian Brummer
相关产品推荐
相关产品推荐
