如何将多数据集的Key列合并到目标data.table的同一列？

首先是原始数据集代码：

library(data.table)
all_questions <- fread("Variable_codes_2022 Variables_2022
              Cat1_1              This_question
              Cat1_2              Other_question
              Cat2_1              One_question
              Cat2_2              Another_question
              Cat3_1              Some_question
              Cat3_2              Extra_question
              Cat3_3              This_question
              Cat4_1              One_question
              Cat4_2              Wrong_question")

# 包含问题及对应Key的额外数据集
dat1 <- fread("Variable_codes Variables        Key
              Cat1              This_question   A1
              Cat1              Other_question  B3")

dat2 <- fread("Variable_codes Variables        Key
              Cat2              One_question    A7
              Cat2              Another_question C8")

尝试分步合并时，第一次合并all_questions与dat1可正常匹配，但合并dat2后，Variable_codes和Key列会生成带.x/.y后缀的重复列，无法将所有Key值整合到同一列中。

解决方案 #

方法1：先合并键值数据集，再做一次连接 #

先把dat1和dat2纵向合并成完整的问题-Key映射表，再一次性与all_questions做左连接，避免重复列生成：

# 合并dat1和dat2得到完整映射表
combined_dat <- rbindlist(list(dat1, dat2))

# 与all_questions左连接
all_questions <- merge(all_questions, combined_dat, 
                       by.x = "Variables_2022", by.y = "Variables", 
                       all.x = TRUE)

执行后结果：

Variables_2022 Variable_codes_2022 Variable_codes  Key
1: Another_question              Cat2_2           Cat2   C8
2:   Extra_question              Cat3_2           <NA> <NA>
3:     One_question              Cat2_1           Cat2   A7
4:     One_question              Cat4_1           Cat2   A7
5:   Other_question              Cat1_2           Cat1   B3
6:    Some_question              Cat3_1           <NA> <NA>
7:    This_question              Cat1_1           Cat1   A1
8:    This_question              Cat3_3           Cat1   A1
9:   Wrong_question              Cat4_2           <NA> <NA>

方法2：分步合并后整合重复列 #

如果必须分步合并，可在每次合并后将重复列的非NA值整合到同一列，再删除临时后缀列：

# 第一步合并dat1
all_questions <- merge(all_questions, dat1, 
                       by.x = "Variables_2022", by.y = "Variables", 
                       all.x = TRUE)

# 第二步合并dat2，指定后缀区分重复列
all_questions <- merge(all_questions, dat2, 
                       by.x = "Variables_2022", by.y = "Variables", 
                       all.x = TRUE, suffixes = c("_dat1", "_dat2"))

# 整合Key列：优先取非NA值
all_questions[, Key := fcoalesce(Key_dat1, Key_dat2)]
# 整合Variable_codes列
all_questions[, Variable_codes := fcoalesce(Variable_codes_dat1, Variable_codes_dat2)]

# 删除临时后缀列
all_questions[, c("Variable_codes_dat1", "Key_dat1", "Variable_codes_dat2", "Key_dat2") := NULL]

处理后可得到与方法1一致的结果。

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