用python实现多个表格合并后按指定字段去重后求和某一列

需求概述

1、读取某文件夹中的N张报表（格式一致），将其合并为一张汇总表。源表格样式如下图：

2、将合并后的表根据组合名称去重，并将其所对应的余额相加，最后将余额列单元格格式设置为会计专用（余额保留小数后两位，且余额为0时显示为-），用底红色高亮显示余额小于0的行。如下图：

需求实现

import glob
import os

import pandas as pd


def merge_form(base_dir: str, write_file: str):
    """
    合并表格数据
    :param base_dir: 多张表格存放目录
    :param write_file: 生成Excel存放路径
    :return: None
    """
    # 路径格式化
    base_dir = f"{base_dir}{os.sep}*"

    balance_file_list = glob.glob(base_dir)
    concat_df = pd.DataFrame()
    for file in balance_file_list:
        if '汇总表' not in file:
            df = pd.read_excel(file, header=5, usecols=[0, 1, 2, 3, 4], dtype={'托管户账号': str})
            concat_df = pd.concat([concat_df, df])

    # 剔除无用空行
    concat_df = concat_df[concat_df['托管户账号'].notna().values]

    # 根据组合名称分组后求和余额列
    group_and_sum_df = concat_df.groupby(by='组合名称')['余额'].sum()

    # 去重，保留唯一数据行
    drop_duplicate_df = concat_df.drop_duplicates(subset=['组合名称'], keep='first')

    # 合并分组表和去重表
    merge_df = pd.merge(drop_duplicate_df, group_and_sum_df, how='left', on='组合名称')

    # 余额列重新赋值
    merge_df['余额_x'] = merge_df['余额_y']
    # 删除多余列
    merge_df = merge_df.drop(labels=['余额_y'], axis=1)
    # 修改列名
    merge_df.rename(columns={'余额_x': '余额'}, inplace=True)

    # 获取余额小于0的数据，并拿到行索引
    less_than_zero = merge_df[merge_df['余额'] < 0.0]
    idx = less_than_zero.index.values

    # 写入Excel
    writer = pd.ExcelWriter(write_file, engine='xlsxwriter')
    merge_df.to_excel(writer, index=None, sheet_name='汇总表')

    workbook = writer.book
    worksheet = writer.sheets['汇总表']

    # 将余额小于0的行设为底红色
    bg_color_format = workbook.add_format({'bg_color': 'red'})
    for i in idx:
        worksheet.conditional_format(i + 1, 0, i + 1, 4, {'type': 'text', 'criteria': 'containing', 'value': '',
                                                          'format': bg_color_format})
    # 将余额列格式设置为 会计专用
    balance_format = workbook.add_format({'num_format': '_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_ '})
    worksheet.set_column('A:E', width=20)
    worksheet.set_column('D:D', width=20, cell_format=balance_format)
    writer.save()