如何匹配多组分隔字符串并返回对应列（R语言data.table）

需求说明 #

需要将input2中的OLD_NAME与input1中的名称及变体进行匹配：

• 匹配NAME2或NAME2_VAR，返回对应的NAME2及所属NAME1
• 若未匹配到NAME2相关，则匹配NAME1或NAME1_VAR，返回对应的NAME1
• 注意NAME2是NAME1的子类别，部分名称无对应NAME2

输入数据 #

input1 类别与变体表 #

input1 <- structure(list(NAME1 = c("A", "A", "A", "A", "J", "J", "M", "N"),
                         NAME1_VAR = c("AA; aa", "AA; aa", "AA; aa", "AA; aa", "JJ; jj", "JJ; jj", "MM; mmm; mm", ""),
                         NAME2 = c("B", "D", "E", "I", "KL", "L", "", ""),
                         NAME2_VAR = c("CB; CCB", "ED; EED", "", "ICH", "LK", "", "", "")),
                    row.names = c(NA, -8L), class = c("data.table", "data.frame"))

input2 待匹配表 #

input2 <- structure(list(ID = 1:7,
                         OLD_NAME = c("mmm", "M", "ED", "B", "A", "N", "LK")),
                    row.names = c(NA, -7L), class = c("data.table", "data.frame"))

解决方案代码 #

基于data.table包实现高效匹配，步骤如下：

1. 构建两类名称的匹配映射表，包含原名称及所有变体
2. 优先匹配子类别NAME2，再匹配父类别NAME1
3. 整理空值显示格式

library(data.table)

# 构建NAME1映射表：变体/原名称 -> NAME1
name1_map <- input1[, .(NAME1_VAR = strsplit(NAME1_VAR, ";\\s*")[[1]]), by = NAME1]
name1_map <- rbind(name1_map, input1[, .(NAME1, NAME1_VAR = NAME1)])
name1_map <- unique(name1_map[, .(match_val = trimws(NAME1_VAR), NAME1)])
name1_map <- name1_map[match_val != ""]

# 构建NAME2映射表：变体/原名称 -> NAME1 + NAME2
name2_map <- input1[NAME2 != "", .(NAME2_VAR = strsplit(NAME2_VAR, ";\\s*")[[1]]), by = .(NAME1, NAME2)]
name2_map <- rbind(name2_map, input1[NAME2 != "", .(NAME1, NAME2, NAME2_VAR = NAME2)])
name2_map <- unique(name2_map[, .(match_val = trimws(NAME2_VAR), NAME1, NAME2)])
name2_map <- name2_map[match_val != ""]

# 执行匹配：先匹配NAME2，再补全NAME1
output <- input2[name2_map, on = .(OLD_NAME = match_val), .(ID, OLD_NAME, NAME1, NAME2)]
missing_rows <- is.na(output$NAME1)
output[missing_rows, c("NAME1", "NAME2") := name1_map[.SD, on = .(match_val = OLD_NAME), .(NAME1, NA)], by = ID]

# 整理空值为空白字符
output[, NAME1 := fifelse(is.na(NAME1), "", NAME1)]
output[, NAME2 := fifelse(is.na(NAME2), "", NAME2)]

# 查看结果
head(output)

输出结果 #

ID OLD_NAME NAME1 NAME2
   <int>   <char> <char> <char>
1:     1      mmm      M       
2:     2        M      M       
3:     3       ED      A      D
4:     4        B      A      B
5:     5        A      A       
6:     6        N      N       

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