r - 在 R 中将多个数据集与多个工作表绑定

Question

我有 4 个 Excel 数据集，每个数据集 15 张。

首先，我想将所有数据集作为列表导入 R，以便列表包含每个数据集（df1、df2、df3、df4），每个数据集包含所有 15 张工作表（sheets1、sheets2、sheets3、...、sheets15 ）。工作表在每个数据集中具有相同的名称。数据集都以同一个词开头，比如说“咖啡”。数据集“coffee_1.xlsx”、“coffee_2.xlsx”、“coffee_3.xlsx”和“coffee_4.xlsx”也是如此。有没有办法一次导入所有数据集？

其次，我想按表格 rbind 所有数据集。因此，例如，df1 的 sheet1 应与 df2、df3 和 df4 的 sheet1 组合。

我不想手动进行，因为我必须对 100 个数据集重复该过程，每个数据集有 15 张。

到目前为止，我已经尝试分别导入所有数据集并将它们组合到一个更大的列表中，如下所示：

df.list<-list(df.list1,df.list2,df.list3,df.list4,df.list5)

每个列表都包含 15 张纸。然后我尝试使用 rbind 来绑定它们do.call：

df.list.big<-do.call(rbind,df.list)

但我无法逐张绑定数据表。对此，我非常感谢您的帮助。谢谢！

标签： rlistrbinddo.call

Answer 1

我将使用以下命令创建一些示例 xlsx 文件openxlsx：

wb <- openxlsx::createWorkbook()
openxlsx::addWorksheet(wb, "tab1")
openxlsx::writeData(wb, "tab1", data.frame(a = 1101:1103, b = 1111:1113))
openxlsx::addWorksheet(wb, "tab2")
openxlsx::writeData(wb, "tab2", data.frame(a = 1201:1203, b = 1211:1213))
openxlsx::addWorksheet(wb, "tab3")
openxlsx::writeData(wb, "tab3", data.frame(a = 1301:1303, b = 1311:1313))
openxlsx::saveWorkbook(wb, "book1.xlsx")

wb <- openxlsx::createWorkbook()
openxlsx::addWorksheet(wb, "tab1")
openxlsx::writeData(wb, "tab1", data.frame(a = 2101:2103, b = 2111:2113))
openxlsx::addWorksheet(wb, "tab2")
openxlsx::writeData(wb, "tab2", data.frame(a = 2201:2203, b = 2211:2213))
openxlsx::addWorksheet(wb, "tab3")
openxlsx::writeData(wb, "tab3", data.frame(a = 2301:2303, b = 2311:2313))
openxlsx::saveWorkbook(wb, "book2.xlsx")

wb <- openxlsx::createWorkbook()
openxlsx::addWorksheet(wb, "tab1")
openxlsx::writeData(wb, "tab1", data.frame(a = 3101:3103, b = 3111:3113))
openxlsx::addWorksheet(wb, "tab2")
openxlsx::writeData(wb, "tab2", data.frame(a = 3201:3203, b = 3211:3213))
openxlsx::addWorksheet(wb, "tab3")
openxlsx::writeData(wb, "tab3", data.frame(a = 3301:3303, b = 3311:3313))
openxlsx::saveWorkbook(wb, "book3.xlsx")

一般流程

我不确定您为什么喜欢每张纸保留一帧；如果你对不同的数据组做同样的事情，拥有一个单一的框架仍然很有意义，尽可能多地保留上下文，这样分组就很自然了。

虽然 base R 确实进行了分组操作，但我发现它们比使用data.tableordplyr包时稍微不那么直观/灵活，所以我会坚持使用这两个来进行处理（如果你想使用哪个，你可以决定使用哪个） ，然后调整您的处理以按组进行）。

无论哪种方式，这是我的流程：

1. 我们需要一个函数来读取工作簿中的所有工作表，然后遍历文件名向量；
2. 我将演示将所有数据放入一个框架中（我的建议）；接着
3. 我将演示按工作表对它们进行分组。

我将从 开始data.table，但稍后会提供等价物dplyr。

基本阅读所有表格功能

readOneBook <- function(fn) {
  shtnms <- openxlsx::getSheetNames(fn)
  sheets <- lapply(setNames(nm = shtnms), openxlsx::readWorkbook, xlsxFile = fn)
  sheets
}
readOneBook("book1.xlsx")
# $tab1
#      a    b
# 1 1101 1111
# 2 1102 1112
# 3 1103 1113
# $tab2
#      a    b
# 1 1201 1211
# 2 1202 1212
# 3 1203 1213
# $tab3
#      a    b
# 1 1301 1311
# 2 1302 1312
# 3 1303 1313

因此，我们将为工作簿（工作表列表）创建一个列表

workbooks <- lapply(setNames(nm = list.files(pattern = "\\.xlsx$")), readOneBook)

数据表

这是一个列表，其中每个元素都是一个工作簿：

library(data.table)
lapply(workbooks, rbindlist, idcol = "sheet")
# $book1.xlsx
#    sheet    a    b
# 1:  tab1 1101 1111
# 2:  tab1 1102 1112
# 3:  tab1 1103 1113
# 4:  tab2 1201 1211
# 5:  tab2 1202 1212
# 6:  tab2 1203 1213
# 7:  tab3 1301 1311
# 8:  tab3 1302 1312
# 9:  tab3 1303 1313
# $book2.xlsx
#    sheet    a    b
# 1:  tab1 2101 2111
# 2:  tab1 2102 2112
# 3:  tab1 2103 2113
# 4:  tab2 2201 2211
# 5:  tab2 2202 2212
# 6:  tab2 2203 2213
# 7:  tab3 2301 2311
# 8:  tab3 2302 2312
# 9:  tab3 2303 2313
# $book3.xlsx
#    sheet    a    b
# 1:  tab1 3101 3111
# 2:  tab1 3102 3112
# 3:  tab1 3103 3113
# 4:  tab2 3201 3211
# 5:  tab2 3202 3212
# 6:  tab2 3203 3213
# 7:  tab3 3301 3311
# 8:  tab3 3302 3312
# 9:  tab3 3303 3313

然后将其组合成一个大框架：

rbindlist(
  lapply(workbooks, rbindlist, idcol = "sheet"),
  idcol = "workbook"
)
#       workbook sheet    a    b
#  1: book1.xlsx  tab1 1101 1111
#  2: book1.xlsx  tab1 1102 1112
#  3: book1.xlsx  tab1 1103 1113
#  4: book1.xlsx  tab2 1201 1211
#  5: book1.xlsx  tab2 1202 1212
# ---                           
# 23: book3.xlsx  tab2 3202 3212
# 24: book3.xlsx  tab2 3203 3213
# 25: book3.xlsx  tab3 3301 3311
# 26: book3.xlsx  tab3 3302 3312
# 27: book3.xlsx  tab3 3303 3313

工作表列表略有不同，需要一些“转置”功能。这可以防止 (1) 并非所有工作簿中都存在的工作表；(2) 不同的纸张顺序。

commonsheets <- Reduce(intersect, lapply(workbooks, names))
commonsheets
# [1] "tab1" "tab2" "tab3"
lapply(setNames(nm = commonsheets),
       function(sht) rbindlist(lapply(workbooks, `[[`, sht), idcol = "workbook"))
# $tab1
#      workbook    a    b
# 1: book1.xlsx 1101 1111
# 2: book1.xlsx 1102 1112
# 3: book1.xlsx 1103 1113
# 4: book2.xlsx 2101 2111
# 5: book2.xlsx 2102 2112
# 6: book2.xlsx 2103 2113
# 7: book3.xlsx 3101 3111
# 8: book3.xlsx 3102 3112
# 9: book3.xlsx 3103 3113
# $tab2
#      workbook    a    b
# 1: book1.xlsx 1201 1211
# 2: book1.xlsx 1202 1212
# 3: book1.xlsx 1203 1213
# 4: book2.xlsx 2201 2211
# 5: book2.xlsx 2202 2212
# 6: book2.xlsx 2203 2213
# 7: book3.xlsx 3201 3211
# 8: book3.xlsx 3202 3212
# 9: book3.xlsx 3203 3213
# $tab3
#      workbook    a    b
# 1: book1.xlsx 1301 1311
# 2: book1.xlsx 1302 1312
# 3: book1.xlsx 1303 1313
# 4: book2.xlsx 2301 2311
# 5: book2.xlsx 2302 2312
# 6: book2.xlsx 2303 2313
# 7: book3.xlsx 3301 3311
# 8: book3.xlsx 3302 3312
# 9: book3.xlsx 3303 3313

dplyr

相同的功能，相同的有效数据，所以我将只显示命令（实际上只是替换rbindlist为bind_cols和参数名称更改）。

library(dplyr)

# list, one workbook per element
lapply(workbooks, rbindlist, idcol = "sheet")

# one big frame
bind_rows(
  lapply(workbooks, bind_rows, .id = "sheet"),
  .id = "workbook"
)

# list, one common sheet per element
lapply(setNames(nm = commonsheets),
       function(sht) bind_rows(lapply(workbooks, `[[`, sht), .id = "workbook"))