如何用xlsxwriter在DataFrame按产品求和并写入Excel小计行下方

问题背景 #

需要使用xlsxwriter计算DataFrame的产品维度总和，并将结果展示在Excel表格的Sub Total行下方。目前Sub Total行及之前的内容展示正常，但产品维度合计部分显示异常，无法达到预期格式。

输入DataFrame #

import pandas as pd
import numpy as np

final_fact = pd.DataFrame({'factory': ['kerala', 'kerala', 'kerala', 'delhi', 'delhi', 'goa', 'goa'],
           'plant': ['', '', '', '', '', '', ''],
           'market': ['', '', '', '', '', '', ''],
           'product': ['A', 'B', 'C', 'A', 'B', 'A', 'B'],
           'uom': ['l', 'l', 'l', 'l', 'l', 'l', 'l'],
           'BP4-2023': [4, 4, 5, 6, 4, 5, 5],
           'RE4-2023': [7, 7, 8, 8, 7, 8, 8],
           'BP5-2023': [4, 4, 5, 6, 4, 5, 5],
           'RE5-2023': [7, 7, 8, 8, 7, 8, 8]})

期望输出 #

在Excel的Sub Total行下方展示如下格式的产品合计：

Product wise Total                                 
 A                            l    15        23       15        23
 B                            l    13        22       13        22
 C                            l    5         8        5         8

现有代码问题分析 #

原代码中产品合计部分存在以下问题：

• groupby后仅提取了数值部分，丢失了product和uom的索引信息，导致Excel中无法显示产品名称和单位
• 写入Excel时循环逻辑错误，每个单元格写入后就换行，导致所有数值分散在不同行，无法对应到正确列
• 缺少"Product wise Total"标题行的写入逻辑
• 存在列名错误：原代码中使用了不存在的planttype列

修复后的完整代码 #

import pandas as pd
import numpy as np
import xlsxwriter

# 初始化Excel工作簿与工作表
workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet()
header_right_format = workbook.add_format({'bold': True, 'align': 'right'})
row_num = 0  # 初始行号，可根据实际表头位置调整

# --- Sub Total行之前的原有逻辑（保留不变）---
factory_value = final_fact['factory'].unique()
total_dataframe = pd.DataFrame(columns=final_fact.columns)
total = []
for fact_val in factory_value:
    factory_data = final_fact[final_fact['factory'] == fact_val]
    factory_data = factory_data.sort_values(by=['product'], ascending=True)
    factory_data.loc['plant_total'] = factory_data.select_dtypes(include=['float64']).sum()
    factory_data["product"] = factory_data["product"].replace(np.nan, "Plant Total")
    factory_data = factory_data.fillna('')
    factory_data = factory_data.round(2)

    plant_total_row = factory_data.loc[factory_data['product'] == "Plant Total"]
    total.append(plant_total_row)
    output = factory_data.values.tolist()

    row_num += 1
    for data_item in output:
        plant_total = "Plant Total"
        if plant_total in data_item:
            for col_num in range(len(data_item)):
                worksheet.write(row_num, col_num, data_item[col_num], header_right_format)
            row_num += 1
        else:
            for col_num in range(len(data_item)):
                worksheet.write(row_num, col_num, data_item[col_num])
            row_num += 1

for i in total:
    total_dataframe = pd.concat([total_dataframe, i], ignore_index=True)
ignore = ['factory', 'plant', 'market', 'product', 'uom']  # 修正原代码的列名错误
total_dataframe = (total_dataframe.set_index(ignore, append=True).astype(float).reset_index(ignore))
total_dataframe.loc['sub_total'] = total_dataframe.select_dtypes(include=['float64']).sum()
total_dataframe["product"] = total_dataframe["product"].replace(np.nan, "Sub Total")
total_dataframe = total_dataframe.fillna('')
total_dataframe = total_dataframe.round(2)
output = total_dataframe.values.tolist()

for data_item in output:
    total_production = "Sub Total"
    if total_production in data_item:
        for col_num in range(len(data_item)):
            worksheet.write(row_num, col_num, data_item[col_num], header_right_format)
        row_num += 1

# --- 修复后的产品维度合计写入逻辑 ---
# 1. 写入产品合计标题行
worksheet.write(row_num, 0, "Product wise Total")
row_num += 1

# 2. 计算产品维度合计，保留完整列结构
product_wise_total = final_fact.groupby(['product', 'uom']).sum().reset_index()
# 补充空列对齐原表格结构
product_wise_total['factory'] = ''
product_wise_total['plant'] = ''
product_wise_total['market'] = ''
# 调整列顺序与原DataFrame一致
product_wise_total = product_wise_total[final_fact.columns]

# 3. 逐行写入产品合计数据
for _, row in product_wise_total.iterrows():
    for col_num, value in enumerate(row):
        worksheet.write(row_num, col_num, value)
    row_num += 1

# 关闭工作簿
workbook.close()

关键修改说明 #

1. 修复groupby结果处理：通过reset_index()将分组索引转为普通列，同时补充空列保证与原表格列结构一致，确保产品名称、单位能正常显示
2. 添加标题行：先写入"Product wise Total"标题，再写入合计数据
3. 修正写入逻辑：遍历每行数据，一次性写入当前行所有列，写完一行后再递增行号，确保数据对应正确位置
4. 修正列名错误：将原代码中不存在的planttype改为原DataFrame中的plant列

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