更新：自动手动Excel读取和清理多个电子表格的标题和子标题在R;我更喜欢使用dplyr来处理杂乱的数据

w6lpcovy 于 2023-05-11 发布在其他

关注(0)|答案(2)|浏览(99)

我每年都有多个Excel文件需要读取。每个文件都有多个与我的分析相关的电子表格，我只想保留这些电子表格。问题是，前3行是标题和子标题，我需要折叠成一个单一的列名。我一直无法找到一个解决方案，到目前为止，因为工作表可能是不同的列长度，我需要捕捉所有的数据。我也想将一个文件中的所有工作表/dfs合并到一个数据框架中。
我的目标是：

读入电子表格
只保留相关的表单
从每张图纸读入数据
取前三行（标题）并折叠成一个列名，应用于每个相关工作表以创建列表中的数据

这就是我想要的数据的最终输出。我附上了一个样本电子表格。
| 副标题1_id|副标题1_name|副标题1_...|团队A1_subheading1_a1|等等等等|
| --------------|--------------|--------------|--------------|--------------|
| 1|蓝色|等等|五百|废话|
| 二|橙子|等等|七百|废话|
| 三|紫色|等等|九百|废话|
示例代码-第一次尝试，只要每个电子表格的列具有相同的长度，就可以很好地工作

library(tidyverse)
library(readxl)
path <- "C:/Example Spreadsheet.xlsx"
#read the sheets and only keep the cost share sheets
sheets <- excel_sheets(path)[grep("Data of interest",excel_sheets(path) )] # simplified to a single    line
#read the data, as a list
excel_data <- lapply(sheets, read_excel, path = path, skip=0)

#convert to DF
temp_df <- excel_data %>%
 bind_rows()

第二次尝试-这得到了适当的输出，但是，我最终不得不为每个单独的df重复相同的代码

#Loop below converts each list element to a df
for (i in 1:length(excel_data)) {
assign(paste0("group_", i), as.data.frame(excel_data[[i]]))
}

#extract each row element 
names1 <- str_remove(names(group_1), "All fields .+|[.]+[:digit:]+") %>% na_if("")
names2 <- str_remove(group_1[1,], "[.]+[:digit:]+") %>% na_if("")
names3 <- str_remove(group_1[2,], "[.]+[:digit:]+") %>% na_if("") 
#combine each row element
temp_names <- tibble(n1 = c(names1, rep(NA, length(names3) - length(names1))), 
                     n2 = c(names2, rep(NA, length(names3) - length(names2))), 
                     n3 = names3) %>%
  fill(n1, n2) %>%
  replace_na(list(n1 = "")) %>%
  mutate(full_name = paste(n1,n2,n3, sep = "_"))

#add the full name
temp_names <- temp_names$full_name
#convert to df and combine elements
names(group_1) <- temp_names[1:ncol(group_1)]

group_1 <- group_1[-c(1,2),]

**更新：**为了添加更多的上下文，源数据有一堆带有空数据字段的头部，这些字段实际上与已提供的数据不一致。更新后的代码将列名限制为仅表示其中包含数据的字段。下面的代码根据包含信息的数据列的数量对有效列进行子集化。我也更新了图像，以更好地显示问题。

sample file

来源：https://stackoverflow.com/questions/76131577/update-automating-manual-excel-read-and-clean-for-multiple-spreadsheets-with-he

2条答案

按热度按时间

gwo2fgha1#

除非我从您的问题中遗漏了什么，否则这段代码应该可以为任何列或表集获得所需的结果。

library(tidyverse)
library(readxl)
path <- "C:/Example Spreadsheet.xlsx"
sheets <- excel_sheets(path)[grep("Data of interest",excel_sheets(path) )]

colname_data <- sapply(lapply(sheets, read_excel, path = path, skip =0, col_names = F), function(x) x[1:3,])
excel_data <- lapply(sheets, read_excel, path = path, skip=3, col_names = F)

for (i in 1:length(excel_data)) {
  colname_data[[i]] <- colname_data[[i]] %>% 
                       t() %>% 
                       as_tibble() %>% 
                       fill(V1, V2, V3)
  colname_data[[i]] <- colname_data[[i]] %>% 
                       replace(is.na(colname_data[[i]]), '') %>% 
                       mutate(col_name = paste(V1, V2, V3, sep='_'))
  col_names <- gsub('^_', '', colname_data[[i]]$col_name)
  
  colnames(excel_data[[i]]) <- col_names
}

bind_rows(excel_data)

然后，您可以按照自己的意愿对列进行排序。

赞(0）回复(0）举报 2023-05-11

cwtwac6a2#

工作答案这解决了问题，并为真实的数据集修剪了多余的数据。

library(tidyverse)
library(openxlsx)

file <- "Example Spreadsheet.xlsx"

read_all_sheets = function(xlsxFile, ...) {
  sheet_names = openxlsx::getSheetNames(xlsxFile)
  sheet_names = sheet_names[grepl("Data of interest", sheet_names)]
  sheet_list = as.list(rep(NA, length(sheet_names)))
  names(sheet_list) = sheet_names
  for (sn in sheet_names) {
    sheet_list[[sn]] = openxlsx::read.xlsx(xlsxFile, sheet=sn, startRow =4, skipEmptyCols = FALSE, colNames = FALSE, ...)
  }
  return(sheet_list)
}

read_all_headers = function(xlsxFile, ...) {
  sheet_names = openxlsx::getSheetNames(xlsxFile)
  sheet_names = sheet_names[grepl("Data of interest", sheet_names)]
  sheet_list = as.list(rep(NA, length(sheet_names)))
  names(sheet_list) = sheet_names
  for (sn in sheet_names) {
    sheet_list[[sn]] = openxlsx::read.xlsx(xlsxFile, sheet=sn, rows = 1:3, skipEmptyCols = FALSE, skipEmptyRows = FALSE, colNames = FALSE, fillMergedCells = TRUE, ...)
  }
  return(sheet_list)
}

test <- read_all_sheets(file)

test2 <- read_all_headers(file)

col_new <- map(test2, ~ .x %>%
  t() %>%
  as_tibble() %>%
  replace(is.na(.), '') %>%
  mutate(col_name = paste(`1`, `2`, `3`, sep='_')) %>%
    select(col_name))

for (i in 1:length(test)) {
  cols <- list()
  cols[[i]] <- col_new[[i]]$col_name[1:ncol(test[[i]])] 
  col_names <- cols[[i]]
  colnames(test[[i]]) <- col_names
}

clean_names <- map_df(test, ~ .x %>%
                        rename_with(.fn = ~ str_replace_all(.x, "^_", ""), .x, .col = starts_with("_")) %>%
                        mutate(across(everything(), ~ as.character(.))))

赞(0）回复(0）举报 2023-05-11

我来回答

更新：自动手动Excel读取和清理多个电子表格的标题和子标题在R;我更喜欢使用dplyr来处理杂乱的数据

2条答案

相关问题

热门标签

最新问答