从不同结构的多个Excel工作簿中读取所有工作表(作为数据帧)。
从不同结构的多个Excel工作簿中读取所有工作表(作为数据帧)。
我了解到readxl
可以用来从一个工作簿中读取多个工作表。然而,我在将其扩展并在许多工作簿上使用不同的工作表名称、工作表数量和其中的数据方面遇到了困难。\n我使用了我下载的一堆.xlsx文件的Enron电子表格数据进行演示。\n为了使其可管理,我们进行了抽样。\n# 在此处设置Enron电子表格目录的路径\nenron_path <- \"../data/enron_spreadsheets/\"\n# 在此处设置测试的样本大小\nsample_size <- 100\nall_paths <- list.files(enron_path,\n full.names = TRUE)\n# 为了测试,随机查看n(sample_size)个工作簿。\nset.seed(1337)\nsample_paths <- sample(all_paths, sample_size)\npaths <- sample_paths\n检查这些工作簿并计算其中的工作表数量,可以发现它们具有不同的工作表数量,并且包含不同的数据。\n# purr软件包\n# https://jennybc.github.io/purrr-tutorial/index.html\nsheet_count <- purrr::map(paths, readxl::excel_sheets) %>%\n purrr::map(length) %>%\n unlist()\nhist(sheet_count, main = \"\")\n然而,为了将工作簿中的所有工作表加载到一个数据框列表中,我们需要:\n
- \n
- 将工作表名称作为自我命名的字符向量获取(这些名称可以很好地传播)。
- 使用
purrr::map()
进行迭代读取工作表。\nbooks <- 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
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 , # sheet_name - , id
\n
\n
\n在这里,我们在books
中每个工作簿一行,将工作簿的工作表名称存储在一个列表列中。我们希望每个工作表一行,将工作表的数据内容存储在一个列表列中,以便我们可以根据工作表数据添加额外的特征(工作表是实验单元)。问题在于它没有按照预期进行向量化,我漏掉了什么吗?\n这个错误...\n
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
\n当没有传递工作簿路径和工作表名称的向量时,该代码可以工作,但显然数据不来自正确的工作表,如下例所示:\n
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"MLP's", "DJ SP15", "newpower-p... $ path "../data/enron_spreadsheets//ke... $ filename "kenneth_lay__19485__Mlp_1109.x... $ sheet_data [<# A tibble: 57 x 46, ... $ id
"1", "2", "3", "4", "5", "6", "...
\n如何将多个工作簿中的多个工作表的数据读取到一个数据框的列表列中?\n我对读取混乱的电子表格和使用purrr
是新手,任何帮助或指导将不胜感激。
问题的原因是需要从多个不同结构的Excel工作簿中读取所有工作表(作为数据帧)。提供的代码只能适用于xls和xlsx文件,无法适用于xlsb文件。
解决方法是使用readxl包中的函数来读取Excel文件。首先,通过excel_sheets函数获取工作簿中的所有工作表的名称。然后,使用read_excel函数在循环中逐个读取每个工作表,并将其存储在一个列表中。最后,使用工作表的名称作为列表的名称。
要调用这个函数,只需提供要读取的Excel文件的文件名作为参数即可。在这个例子中,可以使用read_excel_allsheets("foo.xls")来读取名为"foo.xls"的Excel文件中的所有工作表。
需要注意的是,这个解决方法只适用于xls和xlsx文件,对于xlsb文件无效。
问题的原因是在读取多个不同结构的Excel工作簿中的所有工作表时,出现了一个潜在的bug。这个bug可能是由于一些工作表为空或不包含数据而导致的。这个问题可能是由于一些xls-to-xlsx转换工具中的真正bug引起的,所以我们可能永远不会知道。该问题可以在github.com/tidyverse/readxl/issues/408上进行跟踪。
为解决这个问题,可以使用以下方法:
1. 使用openxlsx
包创建包含多个工作表的Excel文件。
library(openxlsx) # 创建包含两个工作表(内置的iris和mtcars数据集)的文件 write.xlsx(list(iris, mtcars), "two_sheets.xlsx")
2. 列出文件名,并将其传递给readxl::excel_sheets()
函数获取每个文件中的工作表名。
(paths <- list.files(pattern = "*.xlsx")) # [1] "two_sheets.xlsx" (x <- tibble::data_frame(path = paths)) # # A tibble: 1 x 1 # path ## 1 two_sheets.xlsx
3. 使用purrr::map()
函数将readxl::excel_sheets()
函数应用于每个文件,并将结果存储在一个新的列表列中。
(x <- dplyr::mutate(x, sheet_name = purrr::map(path, readxl::excel_sheets))) # # A tibble: 1 x 2 # path sheet_name ## 1 two_sheets.xlsx
4. 使用tidyr::unnest()
函数将每个文件和工作表名展开为一个数据框。
(x <- tidyr::unnest(x)) # # A tibble: 2 x 2 # path sheet_name ## 1 two_sheets.xlsx Sheet 1 # 2 two_sheets.xlsx Sheet 2
5. 使用purrr::map2()
函数将每个文件和工作表名传递给readxl::read_excel()
函数,将数据导入到数据框中。
(x <- dplyr::mutate(x, data = purrr::map2(path, sheet_name, ~ readxl::read_excel(.x, .y)))) # # A tibble: 2 x 3 # path sheet_name data ## 1 two_sheets.xlsx Sheet 1
# 2 two_sheets.xlsx Sheet 2
现在每个数据集都在data
列的单独一行中。可以通过对该列进行子集操作来查看其中一个数据集。
x$data[2] # [[1]] # # A tibble: 32 x 11 # mpg cyl disp hp drat wt qsec vs am gear carb ## 1 21 6 160 110 3.9 2.62 16.5 0 1 4 4 # 2 21 6 160 110 3.9 2.88 17.0 0 1 4 4 # 3 22.8 4 108 93 3.85 2.32 18.6 1 1 4 1 # 4 21.4 6 258 110 3.08 3.21 19.4 1 0 3 1 # 5 18.7 8 360 175 3.15 3.44 17.0 0 0 3 2 # 6 18.1 6 225 105 2.76 3.46 20.2 1 0 3 1 # 7 14.3 8 360 245 3.21 3.57 15.8 0 0 3 4 # 8 24.4 4 146. 62 3.69 3.19 20 1 0 4 2 # 9 22.8 4 141. 95 3.92 3.15 22.9 1 0 4 2 # 10 19.2 6 168. 123 3.92 3.44 18.3 1 0 4 4 # # ... with 22 more rows