如何合并多个Excel文件 [英] How to merge multiple excel files

问题描述

我有多个Excel文件，每个都有两列。一个是相同的，另一个对于不同的文件是不同的。

如何通过其常用列合并所有文件并将其另存为新文件？

注意：

我不希望将它们组合在一起其他（使用 rbind ）。相反，我想根据公共栏来 merge 他们。

对于R：

我有以下格式的文件名。

  percent- 15 knt 03_01__00_51.csv 
 percent- 20 knt 03_01__00_54.csv 
 percent-25 knt 03_01__00_57.csv 
  

由于文件名格式，我无法编写脚本来逐个读取每个文件。我不知道如何编写一个循环，迭代只有15，20 ....并以某种方式离开结束部分。

解决方案

您可以执行以下操作：

 ＃如果这些是文件夹中唯一的文件，您甚至不需要一个模式
 filelist<  -  list.files（pattern =^ percent。* \\.csv $）＃根据模式读取所有文件名
文件<  -  lapply（filelist，read.csv，header = TRUE）＃读取filelist中的所有文件
文件<  -  lapply（files，function （x）x [-1]）＃删除每个文件的第一列
 DF = Reduce（function（...）merge（...，by =CommonColumn，all = T），files）＃合并所有文件
x<  -  sub（^（percent-）（\\d +）（\\s。*）$，\\2，filelist）＃获取文件名号
名称（DF [-1]）<  - 粘贴（名称（DF [-1]），x，sep = - ）＃将文件名号添加到DF $ b $中的列名称b write.csv（DF，myfile.csv）＃将数据写入新文件
  

code> Reduce（）部分是f rom here 。

I have multiple Excel files, each with two columns. One being the same and the other being different for different files.

How can I merge all the files by their common column and save them as a new file?

Note:

I don't wish to combine them one under the other (using rbind). Instead, I want to merge them based on the commun column.

For R:

I have filenames in the following format.

percent- 15  knt 03_01__00_51.csv
percent- 20  knt 03_01__00_54.csv
percent- 25  knt 03_01__00_57.csv

Due to the file names format I can't write a script to read each file individually. I don't know how to write a loop that iterates over just 15 ,20 .... and leaves the end part somehow.

解决方案

You can probably do something like:

# if those are the only files in the folder, you don't even need a pattern
filelist <- list.files(pattern = "^percent.*\\.csv$")    # read all file names according to pattern
files <- lapply(filelist, read.csv, header=TRUE)         # read all files in filelist
files <- lapply(files, function(x) x[-1])                # remove first column of each file
DF = Reduce(function(...) merge(..., by = "CommonColumn", all=T), files)   # merge all files
x <- sub("^(percent- )(\\d+)(\\s.*)$", "\\2", filelist)  # get the file name numbers 
names(DF[-1]) <- paste(names(DF[-1]), x, sep = "-")      # add file name numbers to column names in DF
write.csv(DF, "myfile.csv")                              # write data to new file

The Reduce() part is taken from here.

这篇关于如何合并多个Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！