Python自动化小技巧10——excel文件批量多sheet表合并_python将多个sheet合成一个

本次案例背景是博主之前实习时遇到的问题，就是有很多个excel表：

 每个工作簿里面又有很多sheet子表：

 想要把这些子表合并在一起，多个excel工作簿也要合并到一起，手工肯定慢，这种重复性的劳动交给代码脚本是最合适了。

思路大概就是两个循环，第一个循环文件夹下的不同excel工作簿，第二个循环同一个excel文件里面的不同sheet子表，一一合并就行。

---

代码基础：

首先我们看看怎么获取excel表里面的所有sheet名称。

import pandas as pd
df = pd.ExcelFile('test1.xlsx')
print(df.sheet_names) # 查看所有sheet 名字

 成功获取！

pandas库在处理各种形式的数据文件都还是很好用的。

如果只有一个excel工作簿多sheet合并可以用这个列表解析式来写：

df_concat= pd.concat([ pd.read_excel(df, sheet) for sheet in df.sheet_names])
#将所有sheet中数据合并到一个df中

但是我们有很多个excel工作簿，而且每个工作簿的子表sheet情况都不太一样，所以我下面手写两层循环，加了一点条件判断，进行数据合并。 

---

所有表格合并：

import pandas as pd
 
#开始合并
test_df=pd.DataFrame()           #创建一个空数据框
for j in range(1,6):              #文件个数
    df = pd.ExcelFile('test%d.xlsx'%j)  
    for sheet in df.sheet_names:
        row_df = pd.read_excel('test%d.xlsx'%j, sheet)
        if not int(row_df.size) > 0: # 空白内容
            continue
        else:                        # 有内容
            test_df = pd.concat([test_df, row_df], axis=0) # 合并
   
    test_df['序号'] = test_df.index + 1
    print('已经合并到第%d个'%j)
    
print('合并完成')
test_df.to_excel('合并数据.xlsx',index=False)

整体思路就是先创建一个空的数据框，然后循环文件个数，我的文件都是用test来命名，后面加数字表示顺序。我这里5个文件，所以循环从（1,6）。然后获取每个excel工作簿里面的sheet子表名称的列表，然后一一读取，判断是否为空。因为我的sheet表里面有空白的情况，所以加了一个条件判断，不为空白才进行合并。合并进入test_df里面。最后储存。

这里使用的是concat合并方法，就是如果每个sheet表里面的列名称对不上的话，它会自动判断，如果两个数据框的列名称是一样的，就把内容追加到后面，列名称不一样的就新增一列，没有的列就为空值。

这个方法就很稳健，表格名称不一样都不会报错。

运行结果：

最后就生成一个总文件就完成了：

 里面就有所有的数据。

---

写入不同的sheet表

有的同学会问我不想写入一个表里面，我要写入excel里面不同的sheet表，那应该怎么操作呢：

import pandas as pd 
writer = pd.ExcelWriter('多sheet表.xlsx')
df1.to_excel(writer,sheet_name='AAA')
df2.to_excel(writer,sheet_name='BBB')

使用pd的ExcelWriter对象就行，将自己想写的数据框一个个存入不同的sheet表里面。

---

更新：

目录下所有execl表合并

有同学最近问我，如果文件夹里面的excel表没有像我上面这样名称这么规范，怎么快速读取呢，也有办法，使用glob包：

import numpy as np
import pandas as pd
import glob
files=glob.glob('./*.xlsx')
files

可以找到该目录下的xlsx文件。同理，找到word或者ppt也是可以的。

然后一行代码读取所有excel文件，并且合并，修改名称：

Battery_list = ['CS2_35', 'CS2_36', 'CS2_37', 'CS2_38']
Battery=pd.concat([pd.read_excel(i,usecols=['capacity']) for i in files],axis=1).set_axis(Battery_list,axis=1)
Battery.head()

 

 是不是很简洁方便？