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

我理解 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", "...

如何将多个工作簿中的多个工作表中的数据读入tibble中的列表列?

我对阅读凌乱的电子表格和使用 purrr 任何帮助或指点都将不胜感激。