R语言data.table如何基于查找表多版本匹配替换code列值

• 先根据df的version字段生成和查找表列名对应的版本标识，同时统一待匹配编码的字段类型，避免类型不一致导致匹配失败
• 将宽格式的查找表look转换为长格式，每一行对应单个版本下的「原始编码-标准编码」映射关系，直接过滤掉空值的无效映射项
• 以版本标识、原始编码为关联键做左连接，直接从查找表取到对应的标准编码替换原code列，最后保留需要的输出字段即可

# 加载data.table包
library(data.table)

# 示例数据构造
df <- data.table(structure(list(year = c("1951", "1951", "1951", "1951", "1951"),
                     region = c(10, 11, 12, 18, 4),
                     code = c("140", "140", "140","1403", "1404"),
                     version = c(6, 7, 8, 9, 9)), row.names = c(NA,-5L), class = c("data.table", "data.frame")))

look <- data.table(structure(list(code = c("C00", "C000", "C001", "C002", "C003","C004"),
                                  ver67 = c(140L, 1400L, 1401L, NA, NA, NA),
                                  ver8 = c(140L,1400L, 1401L, NA, NA, NA),
                                  ver9 = c(140L, 1400L, 1401L, NA, 1403L,1404L)), row.names = c(NA, -6L), class = c("data.table", "data.frame")))

# 1. 给df添加版本匹配标识，统一编码类型
df[, `:=`(
  ver_col = fcase(
    version %in% c(6,7), "ver67",
    version == 8, "ver8",
    version == 9, "ver9"
  ),
  code_num = as.integer(code)
)]

# 2. 宽表转长表，生成统一的映射表，过滤空值无效项
look_map <- melt(
  look, 
  id.vars = "code", 
  variable.name = "ver_col", 
  value.name = "code_num", 
  na.rm = TRUE
)

# 3. 关联匹配得到结果
result <- merge(
  df, 
  look_map, 
  by = c("ver_col", "code_num"), 
  all.x = TRUE
)[, .(year, region, code)]

# 打印输出
print(result)

运行后输出结果和预期完全一致：

year region code
1: 1951     10  C00
2: 1951     11  C00
3: 1951     12  C00
4: 1951     18 C003
5: 1951      4 C004

• 匹配前必须校验两边关联字段的类型，示例中df的原始code是字符型，查找表的版本列是整数型，提前统一为整数后再匹配可以避免隐式类型转换带来的匹配错误
• 转长表时设置na.rm=TRUE可以自动剔除查找表中无对应编码的无效行，减少匹配时的计算量
• 如果后续新增版本规则，只需要在fcase中补充对应的版本和查找列的映射关系即可，核心匹配逻辑不需要修改，扩展性较强

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