如何基于双条件批量替换R数据框中多组code列对应值？

数据定义 #

data <- data.frame(ID = c(1, 2, 3, 4, 5,6),
            name1 = c('Triamcinolonacetonide', 'Triamcinolonacetonide', 'nifedipine', 'Nystatine', 'Ethinylestradiol/​levonorgestrel', "Nystatine"),
           quantity1 = c(10, 10, 15, 30, 20, 10),
           dose1 = c(5, 5, 15, 0.5, 2, 10), 
           code1 = c('D', 'S', 'C', 'A', 'G', "A"),
          name2 = c('Pantoprazol', 'levothyroxine', 'Fusidinezuur', 'Ethinylestradiol/​levonorgestrel', 'Nystatine', "Nystatine"),
           quantity2 = c(5, 10, 10, 20, 20, 5),
           dose2 = c(0.1, 15, 15, 7, 5, 12), 
           code2 = c('A', 'H', 'D', 'G', 'A', "A"),
          name3 = c('Pantoprazol', 'levothyroxine', 'nifedipine', 'Pantoprazol', 'Fusidinezuur', "Triamcinolonacetonide"),
           quantity3 = c(10, 10, 15, 30, 20, 10),
           dose3 = c(5, 5, 15, 0.5, 2, 10), 
           code3 = c('A', 'H', 'C', 'A', 'D', "S")
 )

code <- data.frame(med_name = c('Triamcinolonacetonide','Triamcinolonacetonide', 'nifedipine', 'Nystatine', 'Ethinylestradiol/​levonorgestrel', 'Pantoprazol', 'levothyroxine', 'Fusidinezuur'),
atc_code = c("D07AB09", "S02CA04", "C08CA05", "A07AA02", "G03AB03","A02BC02", "H03AA01", "D06AX01"),
first_code = c("D", "S", "C", "A", "G", "A", "H", "D"))

需求 #

将data中所有codeN列（最多77组，如code1、code2...code77）的值，替换为code数据框中对应的atc_code，匹配条件为：

• data的nameN列值 == code的med_name
• data的codeN列值 == code的first_code

尝试过的失败方法 #

方法一 #

未关联first_code匹配条件，且无法正常运行：

data %>%
  select(grep("^code", names(data))) %>%
  mutate(case_when(. == code$med_name~ code$atc_code))

方法二 #

运行报错：

for(i in 1:77){
  case_when(
    data[,paste0("data$code", i)] == code$name && data[,paste0("data$name", i)] == code$first_code ~ code$atc_code
  )
}

错误信息：

Error: Error in `case_watch()`:
! Failed to evaluate the left-hand side of formula 1.
Caused by error in `data[,paste0("data$code", i)] == code$name && data[,paste0("data$name", i)] == code$first_code ~ code$atc_code`:
! 'length = 152' in coercion to 'logical(1)'
Run `rlang::last_trace()` to see where the error occurred.

正确解决方案 #

推荐使用长格式转换+匹配+转宽格式的方式，无需手动循环，自动适配所有分组列：

代码实现 #

library(tidyverse)

# 宽格式转长格式，拆分分组列的前缀与序号
data_long <- data %>%
  pivot_longer(
    cols = -ID,
    names_to = c(".value", "group"),
    names_pattern = "(.*)(\\d+)"
  )

# 关联code表，替换code值为对应的atc_code
data_long_with_atc <- data_long %>%
  left_join(code, by = c("name" = "med_name", "code" = "first_code")) %>%
  mutate(code = atc_code) %>%
  select(-atc_code)

# 长格式转回宽格式，恢复原列结构
data_final <- data_long_with_atc %>%
  pivot_wider(
    names_from = group,
    names_glue = "{.value}{group}",
    values_from = c(name, quantity, dose, code)
  )

# 查看结果
print(data_final)

效果说明 #

运行后data_final中的所有codeN列会被替换为对应ATC编码：

• 第一行code1从D变为D07AB09
• 第二行code1从S变为S02CA04
• 第三行code2从D变为D06AX01

该方案自动识别所有带数字后缀的分组列，无需修改代码即可适配77组的场景。

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