按工作表将所有Excel文件读入R中，文件名作为列 [英] Read All Excel Files into R by Sheet with file name as column

问题描述

我有一个本地文件夹，其中包含相同格式的excel文件.每个Excel文件有10张纸.

I have a local folder with excel files in the same format. Each excel file has 10 sheets.

我希望能够执行以下操作:

I want to be able to do the following:

1)读取R中的所有excel文件

1) Read all the excel files in R

2)将所有结果绑定在一起，但按工作表.

2) Rbind all the results together but by sheet.

3)结果将是将所有excel文件绑定在一起的10个新数据框.

3) Result would be 10 new dataframes with all the excel files rbinded together.

4)将添加带有文件名的新列

4) New column will be added with file name

我已经查看了代码，我能找到的最好的方法是它，但是按工作表却没有做到:

I have looked up code and the best I could find is this but it doesn't do it by sheet:

files = list.files()
library(plyr)
library(readr)
library(readxl)
data2=lapply(files, read_excel)
for (i in 1:length(data2)){data2[[i]]<-cbind(data2[[i]],files[i])}
all_data <- do.call("rbind.fill", data2) 

有人能成功吗?

预先感谢

推荐答案

如果您愿意，也可以使用tidyverse方法对其进行矢量化.

If you'd like you can also vectorize it using the tidyverse approach.

require(tidyverse)

df <- list.files(path = "your_path",
                       full.names = TRUE,
                       recursive = TRUE,
                       pattern = "*.xls") %>% 
tbl_df() %>%
mutate(sheetName = map(value, readxl::excel_sheets)) %>%
unnest(sheetName) %>% 
mutate(myFiles = purrr::map2(value, sheetName, function(x,y) {
    readxl::read_excel(x, sheet = paste(y))})) %>% 
unnest(myFiles)

*以某种方式我无法对其进行标记，因此我正在从

*Somehow I was unable to flag it, so I'm copying my answer from here

这篇关于按工作表将所有Excel文件读入R中，文件名作为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！