从 Excel 工作表中的多个选项卡中提取数据

Pulling data out of multiple tabs in excel sheets

提问人:helpneeder 提问时间:11/2/2023 更新时间:11/2/2023 访问量:64

问:

我有近 300 个格式不整洁的 excel 工作表,因此很难导入数据。在下图中,您将看到一个包含三个选项卡的 Excel 工作表。每个选项卡的格式与图中所示的选项卡相同。

enter image description here

我需要完成以下三个步骤:

步骤1:我需要从以下单元格中的每个选项卡中提取数据:

  • 列名:A3、C6、C7、C9、C10、C12、C14、C17、C19。
  • 行值:B3、F6、F7、F9、F10、F12、F14、F17、F19。

然后在数据框中移动该数据,使其如下所示

对象 ID 一个 b c d e f g
10 1 2 2 2 2 2 1

第 2 步:我需要在 Excel 工作表中的所有三个选项卡上重复步骤 1,然后合并从每个选项卡收集的数据以生成如下所示的数据框:

选项卡名称 对象 ID 一个 b c d e f g
表1 10 1 2 2 2 2 2 1
表2 10 2 1 2 2 1 2 1
表3 10 1 1 2 1 1 2 1

步骤3

然后,我需要对工作目录中的所有 300 个 excel 工作表重复步骤 1 和 2,并将所有 300 个文件中的所有数据合并到一个数据框中,如下所示。

选项卡名称 对象 ID 一个 b c d e f g
表1 10 1 2 2 2 2 2 1
表2 10 2 1 2 2 1 2 1
表3 10 1 1 2 1 1 2 1
表1 20 1 2 2 2 2 2 1
表2 20 2 1 2 2 1 2 1
表3 20 1 1 2 1 1 2 1
表1 30 1 2 2 2 2 2 1
表2 30 2 1 2 2 1 2 1
表3 30 1 1 2 1 1 2 1

有谁知道我应该如何处理这个问题?我真的害怕必须手动完成这项工作。因此,如果我可以使用任何代码来简化此过程,那就太棒了!

非常感谢!

R Excel 循环 导入 Tidyverse

评论

0赞 Golem 11/2/2023
根据屏幕截图有点难以理解,您可以分享您的excel文件吗
0赞 Silentdevildoll 11/2/2023
这在 R 中绝对是可行的,但你尝试过什么?作为一个起点,我建议使用 openxlsx 或 readxl 包来阅读 excel 工作表。我会读取每个工作表两次,一次用于获取 objectID 和值,然后第二次用于读取 C 和 F 列。然后是循环、绑定、透视、组合等问题,以获得您需要的最终表。
2赞 r2evans 11/2/2023
首先,将其读入普通的 R 中,因为它在 xlsx 文件中看起来。这意味着例如,将在连续的两行中,接下来的两行将具有 .删除了空白行。只有在按预期工作之后,您才会考虑下一个任务,即......其次,将数据从图片格式重塑为您想要的样子。这是两个完全不同的任务。第二项任务几乎可以肯定是 SO 处理透视/重塑数据的 stackoverflow.com/q/5890584/3358272 或其他几个问题的重复。data.framePlant 1Plant 2
1赞 r2evans 11/2/2023
鉴于此,您尝试过什么?请尝试任务 1,显示您的代码和结果,然后从那里继续。我建议从诸如 、 或其近乎配套的包开始。readxlopenxlsxopenxlsx2
0赞 Jon Spring 11/2/2023
您可能会发现此包有助于处理这些类型的数据形状。github.com/luisDVA/unheadr

答:

0赞 Rui Barradas 11/2/2023 #1

这是一个带有包的解决方案。
主要功能是。它获取工作表的名称,读取每个工作表,在删除 后提取相关数据,并将这些值放在 data.frame 中。然后是所有这些df。
此函数在每个 .xlsx 文件的循环中调用。
readxlread_all_sheetsNArbindlapply

read_all_sheets <- function(x, col_types) {
  cnames <- c(1L, 3L) |> rev()
  cvalues <- c(2L, 4L) |> rev()
  sheets <- readxl::excel_sheets(x)
  lapply(sheets, \(s) {
    df1 <- readxl::read_excel(
      path = x,
      sheet = s,
      col_names = FALSE, 
      col_types = col_types, 
      skip = 2L
    ) |> suppressMessages()
    nms <- NULL
    for(i in cnames) {
      tmp <- df1[[i]]
      tmp <- tmp[!is.na(tmp)]
      nms <- c(tmp, nms)
    }
    vls <- NULL
    for(i in cvalues) {
      tmp <- df1[[i]]
      tmp <- tmp[!is.na(tmp)]
      vls <- c(tmp, vls)
    }
    m <- matrix(vls, nrow = 1L, dimnames = list(NULL, nms))
    data.frame(TabName = s) |> cbind(m)
  }) |> 
    do.call(rbind, args = _)
}
read_all_xlsx <- function(path = ".", pattern = "\\.xlsx", col_types) {{
  list.files(pattern = "\\.xlsx") |>
    lapply(read_all_sheets, col_types) |> 
    do.call(rbind, args = _)
}}

col_types <- c("text", "numeric", "text", "numeric")
# use the function's defaults path and file name pattern
read_all_xlsx(col_types = col_types)