从不同结构的多个 Excel 工作簿中读取所有工作表(作为数据框)

Question

我知道 readxl 可用于 read in multiple worksheets from a workbook 。但是，我正在努力扩展此功能，并在具有不同工作表名称以及其中的工作表和数据数量的许多工作簿中对其进行矢量化。

我演示了如何使用 Enron spreadsheet data，它是我下载的一堆 .xlsx 文件。

head(list.files("../data/enron_spreadsheets/"), 3)

[1] "albert_meyers__1__1-25act.xlsx"                           
[2] "albert_meyers__2__1-29act.xlsx"                           
[3] "andrea_ring__10__ENRONGAS(1200).xlsx"  

为了使其易于管理，我们进行了采样。

# Set the path to your directory of Enron spreadsheets here
enron_path <- "../data/enron_spreadsheets/"
# Set the sample size for testing here
sample_size <- 100
all_paths <- list.files(enron_path,
                    full.names = TRUE)

# For testing, look at n (sample_size) random workbooks.
set.seed(1337)
sample_paths <- sample(all_paths, sample_size)

paths <- sample_paths

检查这些工作簿并计算其中的工作表数量，发现它们具有不同的工作表数量并包含不同的数据。

# purr package
# https://jennybc.github.io/purrr-tutorial/index.html
sheet_count <- purrr::map(paths, readxl::excel_sheets) %>%
  purrr::map(length) %>%
  unlist()

hist(sheet_count, main = "")

但是，要将工作簿中的所有工作表加载到 list of data frames 中，我们需要:

• 获取工作表名称作为自命名字符向量(这些名称可以很好地传播)。

• 使用 purrr::map() 迭代工作表读取。

  books <-
    dplyr::data_frame(filename = basename(paths),
               path = paths,
               sheet_name = purrr::map(paths, readxl::excel_sheets)
               ) %>%  
    dplyr::mutate(id = as.character(row_number()))
  
    books
  
  # A tibble: 100 x 4
                               filename
                                  <chr>
   1  kenneth_lay__19485__Mlp_1109.xlsx
   2 kate_symes__18980__SP 15 pages.xls
   3 chris_germany__1821__newpower-purc
   4 john_griffith__15991__Forwards Det
   5   jane_tholt__13278__bid2001A.xlsx
   6 gerald_nemec__11481__EOLfieldnames
   7 stacey_white__39009__Power RT Serv
   8      eric_saibi__9766__012302.xlsx
   9 david_delainey__8083__ENA Status o
  10  daren_farmer__5035__HPLN0405.xlsx
  # ... with 90 more rows, and 3
  #   more variables: path <chr>,
  #   sheet_name <list>, id <chr>  
  

这里，books 中每个工作簿有一行，工作簿的工作表名称存储在列表列中。我们希望每个工作表一行，并将工作表的数据内容存储在列表列中，以便我们可以根据工作表数据添加额外的功能(工作表是实验单元)。问题是它没有按预期矢量化，我错过了什么吗？

这个错误...

sheets <-
  tibble::tibble("sheet_name" = unlist(books$sheet_name),
                 "path" = rep(paths,
                              times = unlist(
                                purrr::map_int(books$sheet_name, length))
                              ),
                 "filename" = basename(path),
                 "sheet_data" = tibble::lst(
                   readxl::read_excel(path = path[], 
                                      sheet = sheet_name[])
                   )
             ) %>% 
  dplyr::mutate(id = as.character(row_number()))

Error in switch(ext, xls = "xls", xlsx = "xlsx", xlsm = "xlsx", if (nzchar(ext)) { : 
  EXPR must be a length 1 vector

当未传递工作簿路径和工作表名称的向量时，代码可以工作，但显然数据不是来自下面示例中的正确工作表:

sheets <-
  tibble::tibble("sheet_name" = unlist(books$sheet_name),
                 "path" = rep(paths,
                              times = unlist(
                                purrr::map_int(books$sheet_name, length))
                              ),
                 "filename" = basename(path),
                 "sheet_data" = tibble::lst(
                   readxl::read_excel(path = path[1], 
                                      sheet = sheet_name[1])
                   )
             ) %>% 
  dplyr::mutate(id = as.character(row_number()))

dplyr::glimpse(sheets)

Observations: 313
Variables: 5
$ sheet_name <chr> "MLP's", "DJ SP15", "newpower-p...
$ path       <chr> "../data/enron_spreadsheets//ke...
$ filename   <chr> "kenneth_lay__19485__Mlp_1109.x...
$ sheet_data <list> [<# A tibble: 57 x 46,        ...
$ id         <chr> "1", "2", "3", "4", "5", "6", "...

如何将多个工作簿中的多个工作表中的数据读入小标题中的列表列？

我刚开始阅读凌乱的电子表格并使用 purrr 任何帮助或指示将不胜感激。

Answer 1

既然你提到purrr包，其他一些 tidyverse 包值得考虑。

• dplyr 对于 mutate() ，申请时purrr::map()到数据框的列并将结果存储为列表列。
• tidyr 对于 unnest() ，它扩展列表列，使列表列内的每一行成为整个数据框中的一行。
• tibble 用于精美打印的嵌套数据框

需要示例文件来演示。此代码使用 openxlsx 包来创建一个包含两个工作表(内置 iris 和 mtcars 数据集)的文件，以及另一个包含三个工作表(添加内置 attitude 数据集)的文件。

library(openxlsx)

# Create two spreadsheet files, with different numbers of worksheets
write.xlsx(list(iris, mtcars, attitude), "three_sheets.xlsx")
write.xlsx(list(iris, mtcars),           "two_sheets.xlsx")

现在有一个解决方案。

首先，列出文件名，该文件名将传递给 readxl::excel_sheets()每个文件中工作表的名称，以及 readxl::read_excel()导入数据本身。

(paths <- list.files(pattern = "*.xlsx"))
#> [1] "three_sheets.xlsx" "two_sheets.xlsx"

(x <- tibble::data_frame(path = paths))
#> # A tibble: 2 x 1
#>   path             
#>   <chr>            
#> 1 three_sheets.xlsx
#> 2 two_sheets.xlsx

“映射”readxl::excel_sheets()对每个文件路径执行函数，并将结果存储在新的列表列中。 sheet_name 的每一行列是工作表名称的向量。正如预期的那样，第一个有三个工作表名称，而第二个有两个。

(x <- dplyr::mutate(x, sheet_name = purrr::map(path, readxl::excel_sheets)))
#> # A tibble: 2 x 2
#>   path              sheet_name
#>   <chr>             <list>    
#> 1 three_sheets.xlsx <chr [3]> 
#> 2 two_sheets.xlsx   <chr [2]>

我们需要将每个文件名和每个工作表名称传递到 readxl::read_excel(path=, sheet=) ，因此下一步是建立一个数据框，其中每一行给出一个路径和一个工作表名称。这是使用 tidyr::unnest() 完成的。

(x <- tidyr::unnest(x))
#> # A tibble: 5 x 2
#>   path              sheet_name
#>   <chr>             <chr>     
#> 1 three_sheets.xlsx Sheet 1   
#> 2 three_sheets.xlsx Sheet 2   
#> 3 three_sheets.xlsx Sheet 3   
#> 4 two_sheets.xlsx   Sheet 1   
#> 5 two_sheets.xlsx   Sheet 2

现在每个路径和工作表名称都可以传递到 readxl::read_excel() ，使用purrr::map2()而不是purrr::map()因为我们传递了两个参数而不是一个。

(x <- dplyr::mutate(x, data = purrr::map2(path, sheet_name,
                                          ~ readxl::read_excel(.x, .y))))
#> # A tibble: 5 x 3
#>   path              sheet_name data              
#>   <chr>             <chr>      <list>            
#> 1 three_sheets.xlsx Sheet 1    <tibble [150 × 5]>
#> 2 three_sheets.xlsx Sheet 2    <tibble [32 × 11]>
#> 3 three_sheets.xlsx Sheet 3    <tibble [30 × 7]> 
#> 4 two_sheets.xlsx   Sheet 1    <tibble [150 × 5]>
#> 5 two_sheets.xlsx   Sheet 2    <tibble [32 × 11]>

现在每个数据集都位于 data 的单独行中柱子。我们可以通过对该列进行子集化来仅查看其中一个数据集。

x$data[3]
#> [[1]]
#> # A tibble: 30 x 7
#>    rating complaints privileges learning raises critical advance
#>     <dbl>      <dbl>      <dbl>    <dbl>  <dbl>    <dbl>   <dbl>
#>  1   43.0       51.0       30.0     39.0   61.0     92.0    45.0
#>  2   63.0       64.0       51.0     54.0   63.0     73.0    47.0
#>  3   71.0       70.0       68.0     69.0   76.0     86.0    48.0
#>  4   61.0       63.0       45.0     47.0   54.0     84.0    35.0
#>  5   81.0       78.0       56.0     66.0   71.0     83.0    47.0
#>  6   43.0       55.0       49.0     44.0   54.0     49.0    34.0
#>  7   58.0       67.0       42.0     56.0   66.0     68.0    35.0
#>  8   71.0       75.0       50.0     55.0   70.0     66.0    41.0
#>  9   72.0       82.0       72.0     67.0   71.0     83.0    31.0
#> 10   67.0       61.0       45.0     47.0   62.0     80.0    41.0
#> # ... with 20 more rows