Python - 通过 pandas 将一个表中的数据按照分组创建新表，根据某列进行分类，将数据复制到不同 Sheet-526互联

import pandas as pd
import os

excel_file = "C:\\Users\\root\\Downloads\\脊柱三方协议签订及配送点选情况_吐鲁番_20231124.xlsx"
data = pd.read_excel(excel_file)

status_mapping = {
    '签订已失效': '签订已失效',
    '三方未签订': '三方未签订',
    '未生成三方协议': '未生成三方协议',
    '医疗机构&生产/代理已签订、配送未签订': '医疗机构&生产或代理已签订、配送未签订',
    '医疗机构已签订、生产/代理&配送未签订': '医疗机构已签订、生产或代理&配送未签订'
}

hospital_grouped = data.groupby('医院名称')

for hospital_name, hospital_group in hospital_grouped:
    new_excel_file = f"C:\\Users\\root\\Downloads\\脊柱三方协议签订及配送点选情况_吐鲁番_20231124\\{hospital_name}.xlsx"
    writer = pd.ExcelWriter(new_excel_file, engine='xlsxwriter')

    count = 0

    for status, sheet_name in status_mapping.items():
        for s in hospital_group['签订状态']:

            if s == status:
                subset = hospital_group[hospital_group['签订状态'] == status]
                if not subset.empty:
                    subset.to_excel(writer, sheet_name=sheet_name, index=False)
                    worksheet = writer.sheets[sheet_name]
                    for i, width in enumerate(subset.apply(lambda col: col.astype(str).map(len)).max()):
                        worksheet.set_column(i, i, max(width + 2, 10))
                    count += 1

    writer.close()

    if count == 0:
        os.remove(new_excel_file)