在R中合并同调查多版本数据集：标准化相似变量的方法问询

我正在处理同一调查的多版本个体层面年度数据集，各波次的问题覆盖范围存在变化，且相似问题的变量ID各不相同。需要提取目标问题并标准化变量名，将所有波次数据合并为一个累积数据集。

已制作变量映射表，其中Edition列为调查波次，其余列是标准化后的目标变量名，各行是对应年份的原始变量名，缺失问题需填充NA值：

Edition P           S        A       E     F
1995    p33         s1       s2     s17   s20
1996    p40         s1       s2     s14   s16a
1997    sp58        s1       s2     s10   s12a
1998    sp53        s1       s2     s11   s14a
2000    P54ST       S1       S2      S6  reeduc1
2001    p55st       s1       s2      s6  reeduc1
2002    p45st       s1       s2      s6  reeduc1
2003    p54st       s1       s2      s6    s18
2004    p30st       s1       s2      s6   reeduc1
2005    p48st       s6       s7     s11   reeduc1
2006    p38st       s6       s7     s11   reeduc1
2007    p64st       s10      s11    s15   reeduc1
2008    p61st       s8       s9     s15   reeduc1
2009    P35ST       s5       s6     s12   reeduc1
2010    P29ST       S7       S8     S14   REEDUC1
2011    P38ST       S16      S17    S21   REEDUC1
2013    P22TGBSM    S10      S11    S17   REEDUC_1
2015    P23TGBSM    S12      S13    S19   REEDUC_1
2016    P15STGBS    SEXO    EDAD    S13   REEDUC_1
2017    P16STGBS    SEXO    EDAD    S14   REEDUC.1
2018    P21STGBS.A  SEXO    EDAD    S10 
2020    P50STGBS.A  SEXO    EDAD    S16   REEDUC.1

所有数据文件命名格式为surveyXXXX.csv（XXXX为年份），附2020年数据集片段：

structure(list(numinves = c(2020L, 2020L, 2020L, 2020L, 2020L
), idenpa = c(32L, 32L, 32L, 32L, 32L), numentre = 3200001:3200005,
    reg = c(32002L, 32001L, 32002L, 32002L, 32211L), ciudad = c(32301917L,
    32001001L, 32301955L, 32301932L, 32211004L), tamciud = c(-4L,
    -4L, -4L, -4L, -4L), comdist = c(2L, 1L, 280L, 230L, 926L
    ), edad = c(63L, 24L, 20L, 54L, 38L), sexo = c(2L, 1L, 1L,
    2L, 1L), codigo = c(-4L, -4L, -4L, -4L, -4L)), row.names = c("1",
"2", "3", "4", "5"), class = "data.frame")

核心逻辑是批量读取文件→按映射表提取并重命名变量→合并所有数据集，用dplyr和purrr实现高效批量处理，步骤如下：

1. 准备工作 #

加载依赖包并导入变量映射表：

library(dplyr)
library(purrr)
library(readr)

# 导入变量映射表（直接转换为R数据框）
var_map <- tribble(
  ~Edition, ~P,           ~S,        ~A,       ~E,     ~F,
  1995,    "p33",         "s1",       "s2",     "s17",   "s20",
  1996,    "p40",         "s1",       "s2",     "s14",   "s16a",
  1997,    "sp58",        "s1",       "s2",     "s10",   "s12a",
  1998,    "sp53",        "s1",       "s2",     "s11",   "s14a",
  2000,    "P54ST",       "S1",       "S2",      "S6",  "reeduc1",
  2001,    "p55st",       "s1",       "s2",      "s6",  "reeduc1",
  2002,    "p45st",       "s1",       "s2",      "s6",  "reeduc1",
  2003,    "p54st",       "s1",       "s2",      "s6",    "s18",
  2004,    "p30st",       "s1",       "s2",      "s6",   "reeduc1",
  2005,    "p48st",       "s6",       "s7",     "s11",   "reeduc1",
  2006,    "p38st",       "s6",       "s7",     "s11",   "reeduc1",
  2007,    "p64st",       "s10",      "s11",    "s15",   "reeduc1",
  2008,    "p61st",       "s8",       "s9",     "s15",   "reeduc1",
  2009,    "P35ST",       "s5",       "s6",     "s12",   "reeduc1",
  2010,    "P29ST",       "S7",       "S8",     "S14",   "REEDUC1",
  2011,    "P38ST",       "S16",      "S17",    "S21",   "REEDUC1",
  2013,    "P22TGBSM",    "S10",      "S11",    "S17",   "REEDUC_1",
  2015,    "P23TGBSM",    "S12",      "S13",    "S19",   "REEDUC_1",
  2016,    "P15STGBS",    "SEXO",    "EDAD",    "S13",   "REEDUC_1",
  2017,    "P16STGBS",    "SEXO",    "EDAD",    "S14",   "REEDUC.1",
  2018,    "P21STGBS.A",  "SEXO",    "EDAD",    "S10",   NA,
  2020,    "P50STGBS.A",  "SEXO",    "EDAD",    "S16",   "REEDUC.1"
)

2. 编写单年份数据处理函数 #

该函数负责读取指定年份的csv文件，按映射表提取目标变量并标准化命名，缺失变量自动填充NA，同时添加年份标识：

process_survey <- function(year) {
  # 读取数据并统一变量名为小写，避免大小写不匹配问题
  df <- read_csv(paste0("survey", year, ".csv"), show_col_types = FALSE) %>%
    rename_with(tolower)
  
  # 获取当前年份的变量映射，同时转小写
  current_map <- var_map %>% 
    filter(Edition == year) %>% 
    select(-Edition) %>%
    mutate(across(everything(), tolower))
  
  # 遍历目标变量，提取或填充NA
  extracted_data <- map_dfc(names(current_map), function(target_var) {
    orig_var <- current_map[[target_var]]
    if (!is.na(orig_var) && orig_var %in% colnames(df)) {
      df %>% select(all_of(orig_var)) %>% rename(!!target_var := all_of(orig_var))
    } else {
      tibble(!!target_var := NA)
    }
  })
  
  # 添加年份列和个体ID（如果需要保留numentre作为标识）
  extracted_data %>% 
    mutate(
      Edition = year,
      individual_id = df$numentre,
      .before = 1
    )
}

3. 批量处理并合并所有数据 #

获取映射表中的所有年份，批量处理后合并为一个累积数据集：

# 获取所有待处理的调查年份
survey_years <- var_map$Edition

# 批量处理并合并
cumulative_dataset <- map_dfr(survey_years, process_survey)

关键细节说明 #

• 大小写兼容：通过统一转换变量名为小写，解决了映射表与原始数据中变量名大小写不一致的问题（比如2020年的SEXO对应数据中的sexo）。
• 缺失变量处理：如果某年份缺少目标变量，函数会自动为该变量填充NA值，保证合并后数据集的列一致性。
• 个体标识：代码中默认保留了numentre作为个体ID，可根据实际需求调整或删除该部分逻辑。

内容的提问来源于stack exchange，提问作者flâneur