如何将多个excel文件中的多个工作表导入一个列表 - readxl R. [英] How to import multiple sheets from multiple excel files into one list- readxl R

问题描述

我已经看过其他帖子，详细介绍了如何将单个excel文件中的多个工作表导入R，以及如何导入一系列excel文件，每个文件中只有一个工作表，但不能同时导入两个工作表。

I have seen other posts that detail how to import multiple sheets from a single excel file into R, and also how to import a series of excel files that have a single sheet in each, but not the two at the same time.

我能够使以下代码在单个文件级别上工作，但我想我可能会以某种简单的方式搞砸循环。

I am able to make the following code work in pieces at an individual file level, but I think I may be screwing up the loop somehow in some simple way.

当我执行循环时，我只从列表的第一个成员（文件[1]）获得输出，而不是从附加的循环的所有迭代中输出all_data列表对象。

When I execute the loop, I only get output from the first member of the list (files[1]) instead of output from all iterations of the loop appended in the all_data list object.

这是我的代码：

# name filepath of excel files to import
file_path ="..."

# load names of excel files 
files = list.files(path = file_path, pattern = ".xlsx", )

# create list to store data
all_data = list()

# create function to read multiple sheets per excel file
read_excel_allsheets <- function(filename, tibble = FALSE) {
  sheets <- readxl::excel_sheets(filename)
  x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
  x <- lapply(x, as.data.frame)
  names(x) <- sheets
  }

# execute function for all excel files in "files"
for (i in length(files)){
  filename = paste0(file_path,"/", files[i])
  read_excel_allsheets(filename)
  all_data = c(all_data, x)
  }

我怀疑我只是犯了一个基本的循环错误，但是已经四处寻找并且找不到h要解决。任何帮助真的很感激！

I suspect I'm just making a fundamental loop mistake, but have searched around and can't find out how to fix. any help is really appreciated!

推荐答案

对于多个Excel文件中的多个工作表，需要嵌套循环。考虑嵌套 lapply 调用。具体来说，返回适当的对象，因为它为每次调用函数返回 sheet 。然后，将转换为转换为 lapply 以获取对象列表：

For multiple sheets within multiple Excel files, you need nested looping. Consider nested lapply calls. Specifically, return the appropriate object as right now it returns sheets for every call of function. Then, convert for to lapply for a list of objects:

# load names of excel files 
files = list.files(path = "...", full.names = TRUE, pattern = ".xlsx")

# create function to read multiple sheets per excel file
read_excel_allsheets <- function(filename, tibble = FALSE) {
  sheets <- readxl::excel_sheets(filename)
  tibble_list <- lapply(sheets, function(sh) readxl::read_excel(filename, sheet = sh)
  df_list <- lapply(x, as.data.frame)
  names(df_list) <- sheets                 

  return(df_list)
}

# execute function for all excel files in "files"
all_data <- lapply(files, read_excel_allsheets)

甚至将每个文件命名为 all_data 列表basename：

Even name you all_data list by each file's basename:

# name outer list
xl_base_names <- lapply(files, basename)
all_data <- setNames(all_data, xl_base_names)

---

或者，缩短你的功能并使用 sapply（...，.. 。，simplify = FALSE ）默认情况下，按提供的字符向量列出名称：

---

Alternatively, shorten your function and use sapply(..., ..., simplify = FALSE) which by default names list by supplied character vector:

read_excel_allsheets <- function(filename, tibble = FALSE) {
  sheets <- readxl::excel_sheets(filename)
  sapply(sheets, function(f) as.data.frame(readxl::read_excel(filename, sheet = f)), 
         simplify = FALSE)
}

这篇关于如何将多个excel文件中的多个工作表导入一个列表 - readxl R.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！