如何用Python合并多个Excel文件中名称后缀相同的工作表

场景说明 #

现有多个Excel文件，每个文件包含多个工作表，工作表名称示例如下：

• 工作表1：2021_q1_bj

a  b   c  d
0  1  2  23  2
1  2  3  45  5

• 工作表2：2021_q2_bj

a  b   c  d
0  1  2  23  6
1  2  3  45  7

• 工作表3：2019_q1_sh

a  b   c
0  1  2  23
1  2  3  45

• 工作表4：2019_q2_sh

a  b   c
0  1  2  23
1  2  3  40

需求描述 #

将所有Excel文件中，工作表名称按下划线_拆分后最后一段相同的工作表数据合并为一个表，例如上述示例中名称后缀为bj的工作表1和2合并为一个表，名称后缀为sh的工作表3和4合并为一个表，若其他Excel文件中也存在后缀为bj或sh的工作表，也对应追加到对应合并表中。

预期输出结果 #

• bj合并结果：

a  b   c  d
0  1  2  23  2
1  2  3  45  5
2  1  2  23  6
3  2  3  45  7

• sh合并结果：

a  b   c
0  1  2  23
1  2  3  45
2  1  2  23
3  2  3  40

实现代码 #

import os, glob
import pandas as pd

# 读取当前目录下所有xlsx格式文件
files = glob.glob("*.xlsx")

for each in files:
    # 读取单个Excel内全部工作表，保留原索引
    dfs =  pd.read_excel(each, sheet_name=None, index_col=[0])
    # 合并单个Excel内所有工作表，用原工作表名作为外层索引标记来源
    df_out = pd.concat(dfs.values(), keys=dfs.keys())
    # 按工作表名拆分后的后缀分组，相同后缀的表合并
    for n, g in df_out.groupby(df_out.index.to_series().str[0].str.rsplit('_', n=1).str[-1]):
        # 清理多余层级索引、全空列，重置行索引后导出为单独文件
        g.droplevel(level=0).dropna(how='all', axis=1).reset_index(drop=True).to_excel(f'Out_{n}.xlsx', index=False)

内容的提问来源于stack exchange，提问作者ah bon