在R的data.table中按条件从映射表生成exercise列

需求说明 #

现有两个data.table对象df和map，需为df新增缺失的exercise列，匹配规则如下：

• 基于map与df的对应列匹配，将map的exercise值映射到df
• 按types字段区分匹配逻辑：
  • 当types为x时，使用up和vehicle两列进行匹配
  • 当types为y时，使用class列进行匹配
  • 当types为z时，使用cap和down两列进行匹配
• 所有条目映射唯一，无冲突
• 预期生成的exercise向量：exercise<-c("k","cf","","ty","",45,"","th","sh","cf","th","sh")

解决代码 #

library(data.table)

# 修正原map数据中down列长度不匹配的问题
map <- data.table::as.data.table(structure(list(types=c("x","x","x","y","z","z"),
                                               up = c("d", "y", 4,"","",""), 
                                               vehicle = c("yh", "er", "we","","",""), 
                                               exercise = c("ty", 45, "k","cf","th","sh"),
                                               class = c("","","","c","",""),
                                               cap = c("","","","",6,"e"),
                                               down = c("","","","","1","2")), 
                                          class = c("data.table", "data.frame"), 
                                          row.names = c(NA, -6L)))

# 初始化df的exercise列为空字符串
df[, exercise := ""]

# 处理types=x的匹配：up + vehicle
df[types == "x", exercise := map[types == "x"][.SD, on = .(up, vehicle), x.exercise]]

# 处理types=y的匹配：class
df[types == "y", exercise := map[types == "y"][.SD, on = .(class), x.exercise]]

# 统一cap列类型为字符，避免匹配失败
df[, cap := as.character(cap)]
# 处理types=z的匹配：cap + down
df[types == "z", exercise := map[types == "z"][.SD, on = .(cap, down), x.exercise]]

# 查看最终生成的exercise列
df$exercise

结果验证 #

运行上述代码后，df$exercise的输出与预期完全一致：
[1] "k" "cf" "" "ty" "" "45" "" "th" "sh" "cf" "th" "sh"

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