提问人:helpneeder 提问时间:11/2/2023 更新时间:11/2/2023 访问量:64
从 Excel 工作表中的多个选项卡中提取数据
Pulling data out of multiple tabs in excel sheets
问:
我有近 300 个格式不整洁的 excel 工作表,因此很难导入数据。在下图中,您将看到一个包含三个选项卡的 Excel 工作表。每个选项卡的格式与图中所示的选项卡相同。
我需要完成以下三个步骤:
步骤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 |
有谁知道我应该如何处理这个问题?我真的害怕必须手动完成这项工作。因此,如果我可以使用任何代码来简化此过程,那就太棒了!
非常感谢!
答:
0赞
Rui Barradas
11/2/2023
#1
这是一个带有包的解决方案。
主要功能是。它获取工作表的名称,读取每个工作表,在删除 后提取相关数据,并将这些值放在 data.frame 中。然后是所有这些df。
此函数在每个 .xlsx 文件的循环中调用。readxl
read_all_sheets
NA
rbind
lapply
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)
评论
data.frame
Plant 1
Plant 2
readxl
openxlsx
openxlsx2