如何按列名匹配字典表，替换数据集同编码异义的响应值

处理问卷大数据集时，部分列是编码形式的响应值，需要替换为对应的实际含义。编码与实际值的映射存储在另一张表中，不同列的相同编码含义不同，需依据映射表记录的列名完成精准替换。

示例数据（R语言）：

original <- data.frame(
  name = c('Jane','Mary','John', 'Billy'),
  home = c(1,3,4,2),
  car = c('b','b','a','b'),
  shirt = c(3,2,1,1),
  shoes = c('Black','Black','Black','Brown')
)

keymap <- data.frame(
  column_name=c('home','home','home','home','car','car','shirt','shirt','shirt'),
  value_old=c('1','2','3','4','a','b','1','2','3'),
  value_new=c('Single family','Duplex','Condo','Apartment','Sedan','SUV','White','Red','Blue')
)

# 预期结果
result <- data.frame(
  name = c('Jane','Mary','John', 'Billy'),
  home = c('Single family','Condo','Apartment','Duplex'),
  car = c('SUV','SUV','Sedan','SUV'),
  shirt = c('Blue','Red','White','White'),
  shoes = c('Black','Black','Black','Brown')
)

数据预览：

> original
   name home car shirt shoes
1  Jane    1   b     3 Black
2  Mary    3   b     2 Black
3  John    4   a     1 Black
4 Billy    2   b     1 Brown

> keymap
  column_name value_old     value_new
1        home         1 Single family
2        home         2        Duplex
3        home         3         Condo
4        home         4     Apartment
5         car         a         Sedan
6         car         b           SUV
7       shirt         1         White
8       shirt         2           Red
9       shirt         3          Blue

> result
   name          home   car shirt shoes
1  Jane Single family   SUV  Blue Black
2  Mary         Condo   SUV   Red Black
3  John     Apartment Sedan White Black
4 Billy        Duplex   SUV White Brown

尝试过dplyr的mutate/join方法，但未成功实现需求。

核心思路是将原数据转为长格式，通过column_name和value_old双条件与映射表匹配替换，最后转回宽格式恢复原结构，适合列数较多的场景。

代码实现 #

library(dplyr)
library(tidyr)

# 1. 转长格式：把需要替换的列拆成列名、编码值两列，统一编码值为字符型
original_long <- original %>%
  pivot_longer(cols = c(home, car, shirt), # 指定需要替换的列
               names_to = "column_name",
               values_to = "value_old",
               values_transform = list(value_old = as.character))

# 2. 左连接映射表：双条件匹配确保编码含义随列变化
merged_data <- original_long %>%
  left_join(keymap, by = c("column_name", "value_old")) %>%
  # 处理无映射的情况（示例中无此场景，保留逻辑兼容更多情况）
  mutate(value_final = ifelse(is.na(value_new), value_old, value_new))

# 3. 转宽格式：恢复原数据的列结构
final_result <- merged_data %>%
  pivot_wider(id_cols = c(name, shoes),
              names_from = "column_name",
              values_from = "value_final") %>%
  select(name, home, car, shirt, shoes) # 调整列顺序与原数据一致

# 查看结果
final_result

简化写法（列数较少时） #

如果需要替换的列不多，可以直接用recode批量替换，更直观：

library(dplyr)

final_result <- original %>%
  mutate(
    # 提取home列的映射，转成命名向量后传入recode
    home = recode(as.character(home), !!!deframe(filter(keymap, column_name == "home"))),
    car = recode(car, !!!deframe(filter(keymap, column_name == "car"))),
    shirt = recode(as.character(shirt), !!!deframe(filter(keymap, column_name == "shirt")))
  )

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