r:通过将 Excel 文件作为列表传递到 dplyr 来编译它们

Question

我有两个 Excel 文件，我想将它们编译成 r 中的单个数据帧。

首先，创建要重现的 Excel 文件:

df1<- cbind.data.frame(var1= rnorm(10,4,1), var2= rnorm(10,5,1), var3= rnorm(10,7,4)) # create df1
df2<- cbind.data.frame(var1= rnorm(10,4,1), var2= rnorm(10,5,1), var3= rnorm(10,7,4)) # create df2

wb1<- openxlsx::createWorkbook() # create empty workbook1
wb2<- openxlsx::createWorkbook() # create empty workbook2

openxlsx::addWorksheet(wb1, "df1") # add sheet 1 to wb1
openxlsx::addWorksheet(wb1, "df2") # add sheet 2 to wb1

openxlsx::addWorksheet(wb2, "df1") # add sheet 1 to wb2
openxlsx::addWorksheet(wb2, "df2") # add sheet 2 to wb2

openxlsx::writeData(wb1, "df1", df1) # write df1
openxlsx::writeData(wb1, "df2", df2) # write df2

openxlsx::writeData(wb2, "df1", df1) # write df1
openxlsx::writeData(wb2, "df2", df2) # write df2

openxlsx::saveWorkbook(wb1, 'wb1.xlsx') # save wb1
openxlsx::saveWorkbook(wb2, 'wb2.xlsx') # save wb2

下面的函数将单个指定的 Excel 文件编译到 Dataframe 中，但我想获取所有文件并以编程方式编译它们:

dfCompiled <- 'wb1.xlsx' %>% # rename DF and input your file name here
  getSheetNames() %>%
  set_names() %>%
  map(read.xlsx, xlsxFile = 'wb1.xlsx', # file name here
      colNames = TRUE) %>% 
  as.data.frame() 

调用数据框以验证其是否有效:

> dfCompiled
    df1.var1 df1.var2    df1.var3 df2.var1 df2.var2   df2.var3
1  3.356598 4.441104  7.95931350 3.968744 3.349242  2.1997116
2  3.151004 4.822166  0.39571905 4.679021 6.230923 12.8589661
3  3.581085 6.367498 -0.06415929 5.810634 4.207270  9.9430692
...

通过这些语句运行以下列表以便将所有工作表编译成一个数据帧的最佳方法是什么？

filelist<- list("wb1.xlsx", "wb2.xlsx" )

Answer 1

类似于:

library(tidyverse)

df_compile <- function(file){
  getSheetNames(file) %>%
  set_names() %>%
  map(read.xlsx, xlsxFile = file,
      colNames = TRUE) %>% 
  as.data.frame() 
}

filelist<- list("wb1.xlsx", "wb2.xlsx")

# Assuming same column names per xlsx file
map(filelist, df_compile) %>%
map_df(bind_rows)