利用R语言脚本实现批量合并Excel表格

在整理数据的时候遇到一个问题：假如有很多个excel表，分别存放了一部分数据，现在想要快速把这些表格的数据汇总到一起，如何用R语言快速完成呢？本文分享一个脚本，能够自动完成类似的工作。

输入文件与背景介绍

假如每个材料（样本）的ID编号是从N001开始依次递增，现在有很多excel表格，第一列是ID信息，之后的每一列代表一个变量（性状、表型），以下用两个表格（多个表格方法同理）举栗子：

细心的朋友肯定发现了ID这一列不是连续的，而且有缺失，也就是说有些样品的数据是空缺的。在统计的时候，需要将空缺值设为NA，有数据的值按位置提取，最终想要如下样式的数据：

> df_out_660
     ID type year name
1  N001    D 2015 小王    # 来自B表
2  N002 <NA>   NA <NA>
3  N003 <NA>   NA <NA>
4  N004 <NA>   NA <NA>
5  N005 <NA>   NA 大壮    # 来自A表
6  N006 <NA>   NA <NA>
7  N007 <NA>   NA <NA>
8  N008    F 2017 小张
9  N009 <NA>   NA <NA>

解决思路与逻辑关系

• R语言tidyverse、xlsx包
• 读入样品ID序列信息，用于后续生成结果文件
• 迭代读取每个子文件，然后进行左连接
• 对左连接后的数据判断回原有位置看是否为空
• 若原有位置为空，则替换为新值
• 保存最终结果

操作步骤

载入R包和数据

library(xlsx)
library(tidyverse)
# 以下示例仅用两个表格
df_info <- read.xlsx("test.xlsx",sheetName = "info",header = T)
df_A <- read.xlsx("test.xlsx",sheetName = "dataA",header = T)
df_B <- read.xlsx("test.xlsx",sheetName = "dataB",header = T)

所有样品的ID序列按顺序保存在df_info中，另外将每个小表格读入，需要保证第一行信息一致。

数据左连接

df_B_out <- left_join(df_sample,df_A,by="ID")
df_A_out <- left_join(df_sample,df_B,by="ID")

分别将原始样品序列表格和每个子表做左连接，类似于excel中的VLOOKUP函数，得到单个结果。

数据汇总与保存

接下来，对连接后的单个结果做合并处理，通过迭代判断每个单元格的值是否为NA，假如空缺的话将下一个子表的该单元格值替换到这里，达到使不同单元格的值都转移到一张总表的效果，通过这种方式可以将不同子表叠放在一起，获得一张大表，然后将结果输出保存。

for (i in 1:nrow(df_B_out)){
      sample <- df_B_out$ID[i]
      print(sample)
      for (m in 4:ncol(df_B_out)){
            phe <- colnames(df_B_out)[m]
            if (is.na(df_B_out[i,m])){
                  if (!is.na(df_A_out[i,m])){
                        df_B_out[i,m] <- df_A_out[i,m]
                  }
            }
      }
}
write.csv(df_B_out,"./all.csv",quote = F,row.names = F)

灵感小记

谢谢你有耐心看到这里，如果上文中的步骤理解起来比较抽象，我用更通俗易懂的方式说明一下：

假如某项工作需要合作完成最后进行汇总，比如你想做一个调查，每个人只调查一小部分。方法是先用A4纸打印出空模板，然后分发给很多人去同时做，每个人可能只需要填其中的指定某几行。

最后，你辛苦的收集起来了很多张A4纸，每张上都记录了某部分信息，现在一个问题困扰着你：怎么把这些东一块西一块的数据快速合并到你最初的A4纸上？最快的方法是做梦，我梦到我把一大摞A4纸整齐的摞在一起，这时每张A4值的相同单元格处于空间位置的同一维度，只要我大力出奇迹把纸压的特别紧（直到成为二维平面），那么此时我就得到了一张汇总了所有数据的表格（因为不同子表中非空值均被映射到一维状态）

本文就是梦照进现实。