pandas 按公司合并成多个工作簿的 Excel

看过来

《pandas 教程》持续更新中，提供建议、纠错、催更等加作者微信: gairuo123（备注：pandas教程）和关注公众号「盖若」ID: gairuo。跟作者学习，请进入 Python学习课程。欢迎关注作者出版的书籍：《深入浅出Pandas》和《Python之光》。

本需求是一个办公自动化方面的需求，解决我们日常的文档整理需求。大致是这样，有一个文件夹下有两类文件，一类是资产表，里边可能会有多个公司的资产信息，一类是人员表，里边可能会有多个公司的人员部门情况。需求希望将这两类文档进行合并，一个公司一个文件，每个文件里有这个公司的人员和资产表，分别用两个标签页（sheet）显示。

数据与需求

以下是文件的目录结构，其中 py_script_001.ipynb 是我们写 Python 脚本的文件名，「合并」目录是我们要存放合并后的文件的目录，这里我们手动先创建好。

- py_script_001.ipynb
- 各公司人员及资产表/
  - a公司资产表1.xlsx
  - b公司资产表2.xlsx
  - b公司人员表1.xlsx
  - a公司人员表2.xlsx
  - a公司人员表1.xlsx
  - b公司人员表2.xlsx
  - b公司资产表1.xlsx
  - a公司资产表2.xlsx
- 合并/
  - <空>

两种表的内容格式如：

# 资产表
'''
  规格  数量 公司
0  大   1  a
1  小   2  a
'''

# 人员表
'''
   姓名  部门 公司
0  张三  一部  b
1  李四  二部  b
'''

以上数据 Demo 文件包下载：excel-data.zip，供供练习。

最终结果为同一公司的合并成一个 Excel 工作簿，工作簿内按表格类型分成工作表，一个是人员汇总表，一个是资产汇总表。

合并后的 Excel 文件最终命名：

- a公司人员及资产汇总表.xlsx
  - 工作表1：人员汇总表
  - 工作表2：资产汇总表
- b公司人员及资产汇总表.xlsx
  - 工作表1：人员汇总表
  - 工作表2：资产汇总表

思路

将人员表和资产表分别合并在一起，形成两个总报表，然后定义一个函数，这个函数传入公司名，再由公司名分别从两个总表里筛选出该公司的数据保存到一个文件的不同 sheet 里。

最后一步，任意一个公司（或者两个总表中公司最多的表）做分组操作，调用以上函数生成目标 Excel。

代码实现

写读取要处理的文件：

import pandas as pd
import glob


# 读取文件夹下所有 excel 文件
files = glob.glob('各公司人员及资产表/*.xlsx')
files
'''
['各公司人员及资产表/a公司资产表1.xlsx',
 '各公司人员及资产表/b公司资产表2.xlsx',
 '各公司人员及资产表/b公司人员表1.xlsx',
 ...
 '各公司人员及资产表/b公司资产表1.xlsx',
 '各公司人员及资产表/a公司资产表2.xlsx']
'''

接下来我们要把资产表和人员表认别出来，用以下方法测试下：

'资产表' in '各公司人员及资产表/b公司资产表1.xlsx'.split('/')[1]
# True

以上我们对文件名（包含路径）提取出文件名（分拆切片取索引 1），然后判断资产表字样是否包含。以上方法可以用来下边的列表解析式中，用于合并文件。先将按类型读取 Excel 定义一个函数：

dflist_by_type = lambda type_: [pd.read_excel(i) for i in files if type_ in i.split('/')[1]]

由于功能比较简单，我们用 lambda，实现把两类表读取为 DataFrame 列表。

接下来我们分别合并两类报表：

# 所有人员表合并
staff = pd.concat(dflist_by_type('人员表'), ignore_index=True)
staff
'''
   姓名  部门 公司
0  张三  一部  b
1  李四  二部  b
2  王五  一部  a
...
6  张三  一部  b
7  李四  二部  b
'''

# 所有资产表合并
assets = pd.concat(dflist_by_type('资产表'), ignore_index=True)
assets
'''
   规格  数量 公司
0   大   1  a
1   小   2  a
2   大  99  b
...
8   小   2  a
9   中   3  a
'''

接下来定义生成 Excel 的函数：

def to_company_excel(d):
    company = d.name # 公司名
    with pd.ExcelWriter(f'合并/{company}公司人员及资产汇总表.xlsx') as writer:
        staff.loc[staff.公司==company].to_excel(writer, index=False, sheet_name='人员汇总表')
        assets.loc[assets.公司==company].to_excel(writer, index=False, sheet_name='资产汇总表')
        print(f'{company}公司报表生成完毕！')

解析一下这个函数的功能：

传入的参数 d 是后边我们分组后的子 DataFrame，但是我们不打算用这个子 DataFrame，只用到它的分组名 d.name 就是每个公司的名字
with as 上下文管理器用于定义一个 pd.ExcelWriter Excel 文件对象，别名 writer 是这个文件对象标识符
接下来后边两行分别是生成两个 Sheet，先对各自部表筛选当前公司的数据，然后生成 Excel 到文件对象，sheet_name 是我们指定的标签名
最后一行 print 帮我们打印出生成过程

最后来调用这个函数：

staff.groupby('公司').apply(to_company_excel)
'''
a公司报表生成完毕！
b公司报表生成完毕！
'''

完成后可以到「合并」文件夹中查看成果，就得到了我们想要的各公司 Excel。

（完）

pandas 按公司合并成多个工作簿的 Excel

数据与需求

思路

代码实现

相关内容