从R中不同文件夹中的Excel文件导入图纸 [英] Import sheets from Excel files located in different folder in R

问题描述

基本上，我在两个不同的文件夹中有两个具有相同名称"Checklist"的Excel文件(一个是2018，另一个是2019).清单有不同的工作表，每个工作表一个月:一月"，二月"等...当然，所有工作表的变量都完全相同. 我想将两个Excel文件中的所有工作表放在同一数据框中. 现在，我可以使用以下命令从一个Excel文件中收集工作表:

Basically I have two Excel files with the same name "Checklist" in two different folder (one is 2018 and the other one is 2019). Checklist has different sheets, one for each month : "January", "February" etc... Of course, all the sheets have exactly the same variables. I would like to put in the same data frame all the sheets from both Excel files. For now, I can gather the sheets from one Excel File with :

library(readxl)   
library(tibble)
read_excel_allsheets <- function(filename, tibble = TRUE) {
  sheets <- readxl::excel_sheets(filename)
  x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
  if(!tibble) x <- lapply(x, as.data.frame)
  names(x) <- sheets
  x
}

mysheets <-read_excel_allsheets("C:/Users/Thiphaine/Documents/2018/Checklist.xlsx")
library(dplyr)
mysheets<-bind_rows(mysheets, .id = "column_label")

我只是不知道如何创建一个循环，该循环将穿过文件夹2018和2019从两个Excel文件中收集所有工作表.这个想法还将是，在2020年，我将有另一个文件夹"2020"应包括在内...有任何想法吗?谢谢

I just don't know how to create a loop that will go through the folder 2018 and 2019 to gather all the sheets from both Excel file. The idea will also be that in 2020, I will have another folder "2020" that should be included... Any idea? Thanks

推荐答案

尝试一下:

library(dplyr)
allsheets <- list()
for(file in list.files(path = "PATH/TO/DCUMENTS/", 
    recursive = TRUE, pattern = "*.xlsx", full.names = TRUE)) {
  mysheets <- read_excel_allsheets(file)
  mysheets <- bind_rows(mysheets, .id = "column_label") 
  allsheets[[file]] <- mysheets
}

其中PATH/TO/DOCUMENTS可能与您喜欢的"C:/Users/Thiphaine/Documents/类似.

where PATH/TO/DOCUMENTS is probably something like "C:/Users/Thiphaine/Documents/ for you.

这篇关于从R中不同文件夹中的Excel文件导入图纸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！