R:使用正则表达式从多个 Excel 文件导入特定工作表 [英] R: Use Regex to Import Specific Sheets from Multiple Excel Files

问题描述

我有一组包含多个工作表的 Excel 文件，这些文件不遵循标准命名约定.我想从包含关键字 'frame' 的特定工作表创建单个数据框.

I have a group of Excel files with multiple sheets which don’t follow a standard naming convention. I want to create a single data frame from specific sheets containing the keyword 'frame'.

library(tidyverse)
library(openxlsx)

# Sample Excel File 1
df1 <- data.frame(replicate(10,sample(0:1,10,rep=TRUE)))
data_frame2 <- data.frame(replicate(10,sample(0:1,10,rep=TRUE)))
list_of_datasets1 <- list("df" = df1, "date_frame" = data_frame2)
write.xlsx(list_of_datasets1, file = "writeXLSX1.xlsx")

# Sample Excel File 2
df3 <- data.frame(replicate(10,sample(0:1,10,rep=TRUE)))
data_frame4 <- data.frame(replicate(10,sample(0:1,10,rep=TRUE)))
list_of_datasets2 <- list("date_frames" = df3, "dfs" = data_frame4)
write.xlsx(list_of_datasets2, file = "writeXLSX2.xlsx")

# Create List of Excel Files
excel_file_list <- list.files(pattern = "writeXLSX\\d*.xlsx", full.names = T)

我希望能够使用带有 purr 的正则表达式来做到这一点:

I'd like to be able to do this using a regex with purr like this:

df_bind <- excel_file_list %>%
  map_dfr(~read_excel(.x, sheet = grepl("frame", .x)))

我发现最接近的答案适用于单个文件.但是，我不太清楚如何在列表中正确提取工作表名称.

The closest answer I found works fine with a single file. However, I can't quite figure out how to extract the sheet names correctly when they're in a list.

推荐答案

我们可以使用str_detect

library(readxl)
library(dplyr)
library(purrr)
excel_file_list %>% 
      map_dfr(~ read_excel(.x, sheet = which(str_detect(excel_sheets(.x), 'frame'))))

这篇关于R:使用正则表达式从多个 Excel 文件导入特定工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！