在R中对数据框列表批量应用select、pivot_longer等函数的问题

核心流程 #

先对列表中每个数据框统一完成年份列转码，再批量转换为长表，最后合并所有结果得到目标格式。

1. 优化读取Excel工作表的代码 #

把原来的for循环换成purrr::map，更简洁高效：

library(readxl)
library(purrr)

excel_path <- "C:/Users/.../df_list.xlsx"
mysheetlist <- excel_sheets(excel_path)

# 读取所有工作表并命名列表元素
mysheets_fromexcel <- map(mysheetlist, ~ read_excel(excel_path, sheet = .x, skip = 3, col_names = TRUE))
names(mysheets_fromexcel) <- mysheetlist

2. 批量转码年份列 #

用dplyr::across批量处理所有年份列（匹配以20开头的列），不用逐个写列名：

library(dplyr)

convert_year_cols <- function(df) {
  df %>%
    mutate(across(starts_with("20"), ~ case_when(
      .x == "Y" ~ 1,
      .x == "N" ~ 0,
      .x == "9999" | .x == 9999 ~ NA_real_, # 处理字符/数值类型的9999
      TRUE ~ as.numeric(.x) # 兜底转换其他数值
    ))) %>%
    rename_with(~ paste0("y", .x), starts_with("20")) # 给年份列加y前缀，匹配目标格式
}

# 对列表所有数据框执行转码
mylist_converted <- map(mysheets_fromexcel, convert_year_cols)

3. 批量转长表并清理列 #

用purrr::imap同时获取数据框和它的列表名称，自动将值列命名为原工作表名（比如df_1、df_2）：

library(tidyr)

mylist_long <- imap(mylist_converted, function(df, df_name) {
  df %>%
    pivot_longer(
      cols = !c(country, type, company, sector, name), # 排除不需要转的列
      names_to = "year",
      values_to = df_name
    ) %>%
    select(-name) # 删除冗余的name列
})

4. 批量合并所有数据框 #

用purrr::reduce自动依次左连接所有处理后的长表，无需手动逐个合并：

final_df <- mylist_long %>%
  reduce(left_join, by = c("country", "type", "company", "sector", "year"))

完整整合代码 #

library(readxl)
library(dplyr)
library(tidyr)
library(purrr)

# 读取所有工作表
excel_path <- "C:/Users/.../df_list.xlsx"
mysheetlist <- excel_sheets(excel_path)
mysheets_fromexcel <- map(mysheetlist, ~ read_excel(excel_path, sheet = .x, skip = 3, col_names = TRUE))
names(mysheets_fromexcel) <- mysheetlist

# 批量转码年份列
convert_year_cols <- function(df) {
  df %>%
    mutate(across(starts_with("20"), ~ case_when(
      .x == "Y" ~ 1,
      .x == "N" ~ 0,
      .x == "9999" | .x == 9999 ~ NA_real_,
      TRUE ~ as.numeric(.x)
    ))) %>%
    rename_with(~ paste0("y", .x), starts_with("20"))
}
mylist_converted <- map(mysheets_fromexcel, convert_year_cols)

# 批量转长表
mylist_long <- imap(mylist_converted, function(df, df_name) {
  df %>%
    pivot_longer(
      cols = !c(country, type, company, sector, name),
      names_to = "year",
      values_to = df_name
    ) %>%
    select(-name)
})

# 合并数据框
final_df <- mylist_long %>%
  reduce(left_join, by = c("country", "type", "company", "sector", "year"))

# 查看结果
print(final_df)

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