使用Pandas合并excel文件

原创2023-03-17 23:31·一点鑫得

#头条创作挑战赛#

有一个这样的需求，有很多数据格式相同的excel文件分散在不同的目录，需要把它们的数据合到一个excel文件中，excel文件不多的情况下也许可以手工整理，假如有成百上千和文件要合并，手工合并将是很麻烦的事情，可以使用python语言的第三方库pandas来轻松解决这个问题。

先来梳理下实现的思路：

  1. 首先，遍历整个目录及子目录，识别出需要合并的excel文件，添加到合并文件列表。
  2. 接下来，使用pandas的read_excel方法读取所有的excel，将读取的数据对象添加到列表中。
  3. 最后，使用pandas的concat方法将pandas读取的excel数据对象列表合并，使用to_excel方法生成新的excel文件。

遍历文件

├─示例文件夹01
│  │  data_001.xlsx
│  │
│  └─文件夹001
│          data_002.xlsx
│
└─示例文件夹02
        data_003.xlsx

本次示例文件目录结构如上所示，需要合并的excel文件名拥有"data_"前缀，可以使用os.walk方法遍历目录及子目录下的文件，我们在交互式命令中演示os.walk方法返回的结果。

>>> import os
>>> paths = os.walk("./")
>>> for item in paths:
...   print(item)
...
('./', ['示例文件夹01', '示例文件夹02'], [])
('./示例文件夹01', ['文件夹001'], ['data_001.xlsx'])
('./示例文件夹01\\文件夹001', [], ['data_002.xlsx'])
('./示例文件夹02', [], ['data_003.xlsx'])

os.walk方法返回要遍历的目录及其子目录下的所有文件，它返回的是一个三元组(root, dirs, files)，root表示的是当前正在遍历的文件夹，dirs是一个列表，内容是该文件夹下的目录（不包括子目录），files是一个列表，内容是root所表示的文件夹下的文件。那么，当找到files中一个匹配的excel文件，该文件的完整路径就可以通过root+file名拼接得到。

Pandas上手

pandas不是python标准库，需要使用pip install pandas进行安装，如果操作excel还需要安装openpyxl库，即pip install openpyxl
data_001.xlsx:
name  age
a     11
b     12

data_002.xlsx:
name  age
c     13
d     14

data_003.xlsx:
name  age
e     15
f     16

本次示例中各excel文件的内容如上所示，虽然本文不会使用pandas的很多功能，这里还是在交互式命令中演示一些pandas的基本操作。另外，Pandas是非常强大的数据分析工具，其功能远远不止这里提到的一些皮毛，值得深入学习掌握。

>>> import pandas as pd

# DataFrame是一种表格型的数据结构，类似于二维数组
>>> df = pd.DataFrame([["a",11],["b",12]],columns=["名字","年龄"])
>>> df
  名字  年龄
0  a  11
1  b  12

# 过滤名字为a的行
>>> df[df["名字"]=="a"]
  名字  年龄
0  a  11

# 过滤年龄为12的行
>>> df[df["年龄"]==12]
  名字  年龄
1  b  12

# 筛选名字这一列数据
>>> df["名字"]
0    a
1    b
Name: 名字, dtype: object

# 取单元格0行、1列的数据
>>> df.iloc[0,1]
11

# 取单元格1行、0列的数据
>>> df.iloc[1,0]
'b'

# 将数据输出为excel文件
>>> df.to_excel("excel_demo.xlsx")

上面to_excel方法生成excel文件内容如下图所示

合并excel文件

有了上面的基础，就可以开始实现合并excel文件的脚本了，参考代码如下

import os

import pandas as pd

# 合并当前目录及子目录下前缀为data_的excel文件
def mergeExcel(prefix="data_"):
	# 遍历当前目录及子目录
    paths = os.walk("./")
    excel_list = []
    for root, dirs, files in paths:
        for file_name in files:
            if file_name.startswith(prefix):
	            # 将满足data_前缀的excel文件路径+文件名添加到excel_list列表
                excel_list.append(os.path.join(root, file_name))
            
    dataframe_lst = []
    print("开始合并excel文件...")
    # 遍历每一个符合要求的excel文件
    for excel_file in excel_list:
	    # pandas读取excel内容，并添加到dafaframe_lst列表
        dataframe_lst.append(pd.read_excel(excel_file))
    # 将dataframe_lst中的excel数据合并为一个DataFrame数据结构
    dataframe_merged = pd.concat(dataframe_lst)
    # 将合并的DataFrame生成新的excel文件
    dataframe_merged.to_excel("合并文件.xlsx", index=False)

if __name__ == "__main__":
    mergeExcel()

合并后的文件内容如下

更进一步，显示进度条

上面的脚本已经满足了最初的需求，但是在执行脚本的过程中看不到任何反馈，不知道什么时候脚本会执行结束，接下来为脚本添加进度条显示。可以使用使用tqdm包，执行命令pip install tqdm进行安装，tqdm包非常容易使用，增加了进度条的脚本执行结果显示如下，它是动态显示进度的，这里只是展示最终的结果。

> python .\merge.py
开始合并excel文件...
100%|██████████████████████████████████████████████████████████████████████████████████████████████████████████| 3/3 [00:00<00:00,  3.24it/s]

下面是增加了进度条功能的代码，可以发现除了导入tdqm的语句之外，只有一行代码与之前不同，使用tqdm非常简单，只需要将需要展示进度的任意可迭代的对象传给tqdm方法即可。

import os

import pandas as pd
from tqdm import tqdm

# 合并当前目录及子目录下前缀为data_的excel文件
def mergeExcel(prefix="data_"):
	# 遍历当前目录及子目录
    paths = os.walk("./")
    excel_list = []
    for root, dirs, files in paths:
        for file_name in files:
            if file_name.startswith(prefix):
	            # 将满足data_前缀的excel文件路径+文件名添加到excel_list列表
                excel_list.append(os.path.join(root, file_name))
            
    dataframe_lst = []
    print("开始合并excel文件...")
    # 遍历每一个符合要求的excel文件
    for excel_file in tqdm(excel_list):
	    # pandas读取excel内容，并添加到dafaframe_lst列表
        dataframe_lst.append(pd.read_excel(excel_file))
    # 将dataframe_lst中的excel数据合并为一个DataFrame数据结构
    dataframe_merged = pd.concat(dataframe_lst)
    # 将合并的DataFrame生成新的excel文件
    dataframe_merged.to_excel("合并文件.xlsx", index=False)

if __name__ == "__main__":
    mergeExcel()

参考文献

[1]. 《流畅的python》17.5 显示下载进度并处理错误，第429页；

举报
评论 0
    热门：