时间戳

首页 > 解决方案 > 从R中的行开始合并多个Excel文件

r - 从R中的行开始合并多个Excel文件

问题描述

我有多个 Excel 文件需要合并为一个，但只有某些行。Excel文件看起来像这样......

所有文件的列标题相同。我还需要在新生成的文件中添加一个新列 A，因此我创建了一个单独的 Excel 文件，其中只有标题和新列 A。我的脚本首先读取此文件（如下）并将其写入工作簿。 .

接下来，我需要读取每个文件，从第 9 行开始，并一个接一个地合并所有数据。所以最终结果应该是这样的（减去成员站点列，我还没有尝试过逻辑，但认为它将是样本 ID 值的子字符串）......

但是，我目前的结果是......

我目前只使用 3 个文件，每个文件有几十行，但最终目标是合并大约 15-30 个文件，每个文件有 25 到 200 行，给或取。所以...

1）我知道我的代码不正确，但不确定如何获得预期的结果。一方面，我的循环正在覆盖数据，因为它在写入时总是从第 2 行/第 2 列开始。但是，我想不出如何重写它。

2) 日期以通用格式返回（“43008”而不是“9/30/2017”）

3）某些列数据被放置在不同的列下（如核酸浓度具有组织含量日期的值）。

任何建议或帮助将不胜感激！

我的代码...

library(openxlsx)   # Excel and csv files
library(svDialogs)   # Dialog boxes

setwd("C:/Users/Work/Combined Manifest")

# Create and load Excel file
wb <- createWorkbook()

# Add worksheet
addWorksheet(wb, "Template")

# Read in & write header file
df.headers <- read.xlsx("headers.xlsx", sheet = "Template")

writeData(wb, "Template", df.headers, colNames = TRUE)

# Function to get user path
getPath <- function() { 
  # Ask for path
  path <- dlgInput("Enter path to files: ", Sys.info()["user"])$res
  if (dir.exists(path)) {
    # If path exists, set the path as the working directory
    return(path)
  } else {
    # If not, issue an error and recall the getPath function
    dlg_message("Error: The path you entered is not a valid directory. Please try again.")$res
    getPath()
  }
}

# Call getPath function
folder <- getPath()

setwd(folder)

# Get list of files in directory
pattern.ext <- "\\.xlsx$"
files <- dir(folder, full=TRUE, pattern=pattern.ext)

# Get basenames and remove extension 
files.nms <- basename(files)
files.nms <- gsub(pattern.ext, "", files.nms)

# Set the names
names(files) <- files.nms

# Iterate to read in files and write to new file
for (nm in files.nms) {

  # Read in files 
  df <- read.xlsx((files[nm]), sheet = "Template", startRow = 9, colNames = FALSE)

  # Write data to sheet
  writeData(wb, "Template", df, startCol = 2, startRow = 2, colNames = FALSE)
}

saveWorkbook(wb, "Combined.xlsx", overwrite = TRUE)

编辑： 所以通过下面的循环，我成功地读取文件并合并它们。感谢所有的帮助！

for (nm in files.nms) {

  # Read in files 
  df <- read.xlsx(files[nm], sheet = "Template", startRow = 8, colNames = TRUE, detectDates = TRUE, skipEmptyRows = FALSE,
                  skipEmptyCols = FALSE)

  # Append the data
  allData <- rbind(allData, df)
}

编辑：最终解决方案 感谢大家的帮助！

library(openxlsx)   # Excel and csv files
library(svDialogs)   # Dialog boxes

# Create and load Excel file
wb <- createWorkbook()

# Add worksheet
addWorksheet(wb, "Template")

# Function to get user path
getPath <- function() { 
  # Ask for path
  path <- dlgInput("Enter path to files: ", Sys.info()["user"])$res
  if (dir.exists(path)) {
    # If path exists, set the path as the working directory
    return(path)
  } else {
    # If not, issue an error and recall the getPath function
    dlg_message("Error: The path you entered is not a valid directory. Please try again.")$res
    getPath()
  }
}

# Call getPath function
folder <- getPath()

# Set working directory
setwd(folder)

# Get list of files in directory
pattern.ext <- "\\.xlsx$"
files <- dir(folder, full=TRUE, pattern=pattern.ext)

# Get basenames and remove extension 
files.nms <- basename(files)

# Set the names
names(files) <- files.nms

# Create empty dataframe
allData <- data.frame()

# Create list (reserve memory)
f.List <- vector("list",length(files.nms))

# Look and load files
for (nm in 1:length(files.nms)) {

  # Read in files
  f.List[[nm]] <- read.xlsx(files[nm], sheet = "Template", startRow = 8, colNames = TRUE, detectDates = TRUE, skipEmptyRows = FALSE,
                  skipEmptyCols = FALSE)
}

# Append the data
allData <- do.call("rbind", f.List)

# Add a new column as 'Member Site'
allData <- data.frame('Member Site' = "", allData)

# Take the substring of the Specimen.ID column for Memeber Site
allData$Member.Site <- sapply(strsplit(allData$Specimen.ID, "-"), "[", 2)

# Write data to sheet
writeData(wb, "Template", startCol = 1, allData)

# Save workbook
saveWorkbook(wb, "Combined.xlsx", overwrite = TRUE)

标签： rexcelmerge

解决方案

首先，您在问题中提供了大量信息，这通常是一件好事，但我想知道您是否可以通过使用越来越少的文件重新创建问题来使您的问题更容易解决。你能弄清楚如何合并两个文件，每个文件都先包含少量数据吗？

关于您提出的第一个挑战：

1）是的，您正在覆盖每个循环中的工作簿。我建议您加载数据并将其附加到 data.frame，然后在加载所有文件后存储最终结果。看看下面的例子。请注意，此示例使用 rbind，如果您要组合大量文件，则效率低下。因此，如果您有很多文件，您可能需要使用不同的结构。

# Create and empty data frame
allData <- data.frame()

# Loop and load files
for(nm in files.nms) {

    # Read in files 
    df <- read.xlsx((files[nm]), sheet = "Template", startRow = 9, colNames = FALSE)

    # Append the data
    allData <- rbind(allData, df)

}

# Write data to sheet
writeData(wb, "Template", df, startCol = 2, startRow = 2, colNames = FALSE)

希望这能让你更接近你所需要的！

编辑：更新答案以解决所发表的评论

如果你有更多的文件，rbind 会像@Parfait 提到的那样变慢，因为要制作多个数据副本。避免这种情况的方法是，首先通过创建一个具有足够空间来保存数据的空列表来保留内存空间，然后填充列表，最后使用 do.call("rbind" 将所有数据合并在一起, ...) 。我在下面编译了一些示例代码，这些代码与您在问题中提供的内容一致。

# Create list (reserve memory)
f.List <- vector("list",length(files.nms))

# Loop and load files
for(eNr in 1:length(files.nms)) {

    # Read in files 
    f.List[[eNr]] <- read.xlsx((files.nms[eNr]), sheet = "Template", startRow = 9)

}

# Append the data
allData <- do.call("rbind", f.List)

下面进一步说明这一点，一个可重现的小例子。它只使用了几个数据框，但它说明了创建列表、填充该列表以及合并数据作为最后一步的过程。

# Sample data
df1 <- data.frame(x=1:3, y=3:1)
df2 <- data.frame(y=4:6, x=3:1)
df.List <- list(df1,df2)

# Create list
d.List <- vector("list",length(df.List))

# Loop and add data
for(eNr in 1:length(df.List)) {
    d.List[[eNr]] <- df.List[[eNr]] 
}

# Bind all at once
dfAll <- do.call("rbind", d.List)
print(dfAll)

希望这有帮助！谢谢！

推荐阅读