利用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值的相同单元格处于空间位置的同一维度，只要我大力出奇迹把纸压的特别紧（直到成为二维平面），那么此时我就得到了一张汇总了所有数据的表格（因为不同子表中非空值均被映射到一维状态）
