在R语言中使用lookup数据框映射变量值配对的实现方法

要实现根据映射规则生成新变量的需求，推荐使用tidyverse工具链处理，核心思路是先将长格式的就诊数据转换为宽格式（每个患者每次就诊的所有变量在同一行），再通过映射表匹配生成目标变量，具体步骤如下：

1. 加载依赖包并准备数据 #

library(tidyverse)

# 原始就诊数据
df <- data.frame(ID = c(34, 34, 34, 34, 89, 89, 89, 89), 
                 visit_number = c(1, 1, 1, 1, 1, 1, 2, 2), 
                 variable = c("height", "weight", "eye_color", "hair_color", "weight", "height", "height", "weight"), 
                 value = c("short", "over", "brown", "brown", "normal", "short", "short", "over"))

# 映射规则表
mapping <- data.frame(var_1= c("height", "height", "eye_color", "eye_color"), 
                      val_1 = c("short", "short", "brown", "blue"), 
                      var_2 = c("weight", "weight", "hair_color", "hair_color"), 
                      val_2 = c("normal", "over", "brown", "blonde"), 
                      new_var_name = c("health", "health", "complexion", "complexion"), 
                      new_val = c("average", "warn", "monochrome", "contrast"))

2. 将长格式数据转换为宽格式 #

转宽后，每个患者的单次就诊记录会合并为一行，方便后续匹配变量值对：

df_wide <- df %>%
  pivot_wider(names_from = variable, values_from = value)

转换后的df_wide结构如下：

3. 根据映射表匹配生成新变量 #

我们可以按新变量类型（health和complexion）分别处理映射，通过表连接高效匹配变量值对：

生成health变量 #

# 提取health相关的映射规则
health_map <- mapping %>%
  filter(new_var_name == "health") %>%
  select(val_1, val_2, new_val)

# 匹配height和weight的值对，生成health
df_wide <- df_wide %>%
  left_join(health_map, by = c("height" = "val_1", "weight" = "val_2")) %>%
  rename(health = new_val)

生成complexion变量 #

# 提取complexion相关的映射规则
complexion_map <- mapping %>%
  filter(new_var_name == "complexion") %>%
  select(val_1, val_2, new_val)

# 匹配eye_color和hair_color的值对，生成complexion
df_wide <- df_wide %>%
  left_join(complexion_map, by = c("eye_color" = "val_1", "hair_color" = "val_2")) %>%
  rename(complexion = new_val)

处理后的df_wide会包含新生成的变量：

4. 按需转回长格式（可选） #

如果需要回到原始的长格式结构，可执行以下代码：

df_final <- df_wide %>%
  pivot_longer(
    cols = c(height, weight, eye_color, hair_color, health, complexion),
    names_to = "variable",
    values_to = "value",
    values_drop_na = TRUE  # 移除缺失值对应的行
  )

最终长格式数据示例：

方案优势 #

• 效率高：表连接是向量级操作，比逐行判断的方式更快，适合处理大规模数据
• 扩展性强：如果后续新增映射规则，只需调整映射表并重复对应变量的匹配步骤即可
• 可读性好：代码逻辑清晰，每一步操作的目的明确

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