Python Pandas透视表小计、总计及百分比计算问题求助

需求与问题 #

• 在A列维度添加分组小计（W和X各自的合计）
• 生成D到K列的总计
• 正确计算总计行的百分比值（不能直接用sum聚合），当前运行代码报错

原始数据 #

A   B   C   D   E   F   G   H   %I     J    K
W   P   E   6   0   0   0   0   0%     0    0
W   P   F   3   0   0   0   0   0%     0    0
W   P   G   1   0   0   0   0   0%     0    0
X   P   H   23  2   2   0   0   76%    0    0
X   P   I   8   0   0   0   0   0%     0    0
X   P   J   1   0   0   0   0   0%     0    0
X   P   E   5   2   0   1   0   78%    0    0
X   P   K   36  0   0   0   0   0%     0    0
X   P   F   35  5   5   0   0   89%    1    0
X   P   G   40  9   6   2   1   22%    4    0
X   P   L   6   1   0   1   0   45%    0    0

现有代码 #

import pandas as pd
import numpy as np

# 假设C是已加载的DataFrame
tbl = pd.pivot_table(
    C,
    values=['D', 'E', 'F', 'G', 'H', '% I', 'J', 'K'],
    index=['A', 'B', 'C'],
    aggfunc=[{
        'D': np.sum,
        'E': np.sum,
        'F': np.sum,
        'G': np.sum,
        'H': np.sum,
        '% I': np.sum,
        'J': np.sum,
        'K': np.sum}],
    fill_value=0,
    margins=True,
    margins_name='Total')

预期输出 #

A   B   C      D    E   F   G  H  %I     J  K
W   P   E      6    0   0   0  0  0%     0  0
W   P   F      3    0   0   0  0  0%     0  0
W   P   G      1    0   0   0  0  0%     0  0
Sub Total (W)  10   0   0   0  0  Auto%  0  0
X   P   H      23   2   2   0  0  76%    0  0
X   P   I      8    0   0   0  0  0%     0  0
X   P   J      1    0   0   0  0  0%     0  0
X   P   E      5    2   0   1  0  78%    0  0
X   P   K      36   0   0   0  0  0%     0  0
X   P   F      35   5   5   0  0  89%    1  0
X   P   G      40   9   6   2  1  22%    4  0
X   P   L      6    1   0   1  0  45%    0  0
Sub Total (X)  154  19  13  4  1  Auto%  5  0
Grand Total    164  19  13  4  1  Auto%  5  0

解决方案 #

步骤说明 #

1. 先处理%I列，将其转换为数值类型以便后续计算
2. 按A列分组生成小计行，合并到原数据中
3. 计算总计行
4. 针对小计和总计行，根据业务逻辑计算百分比（示例规则为(E+F+G+H)/D * 100%，可按需调整）
5. 格式化百分比列并调整行结构，匹配预期输出

完整代码 #

import pandas as pd
import numpy as np

# 加载原始数据（实际使用时替换为你的数据加载逻辑）
data = [
    ['W', 'P', 'E', 6, 0, 0, 0, 0, '0%', 0, 0],
    ['W', 'P', 'F', 3, 0, 0, 0, 0, '0%', 0, 0],
    ['W', 'P', 'G', 1, 0, 0, 0, 0, '0%', 0, 0],
    ['X', 'P', 'H', 23, 2, 2, 0, 0, '76%', 0, 0],
    ['X', 'P', 'I', 8, 0, 0, 0, 0, '0%', 0, 0],
    ['X', 'P', 'J', 1, 0, 0, 0, 0, '0%', 0, 0],
    ['X', 'P', 'E', 5, 2, 0, 1, 0, '78%', 0, 0],
    ['X', 'P', 'K', 36, 0, 0, 0, 0, '0%', 0, 0],
    ['X', 'P', 'F', 35, 5, 5, 0, 0, '89%', 1, 0],
    ['X', 'P', 'G', 40, 9, 6, 2, 1, '22%', 4, 0],
    ['X', 'P', 'L', 6, 1, 0, 1, 0, '45%', 0, 0]
]
df = pd.DataFrame(data, columns=['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', '%I', 'J', 'K'])

# 转换%I列为数值类型
df['%I_num'] = df['%I'].str.replace('%', '').astype(float) / 100

def add_subtotals(df):
    result = []
    # 按A列分组生成小计
    for group_val, group_df in df.groupby('A'):
        result.append(group_df)
        # 计算分组小计
        subtotal = group_df[['D', 'E', 'F', 'G', 'H', 'J', 'K']].sum().to_frame().T
        subtotal['A'] = f'Sub Total ({group_val})'
        subtotal['B'] = ''
        subtotal['C'] = ''
        subtotal['%I'] = 'Auto%'
        subtotal = subtotal[df.columns]
        result.append(subtotal)
    
    # 合并组内数据与小计
    combined = pd.concat(result, ignore_index=True)
    
    # 添加总计行
    grand_total = combined[['D', 'E', 'F', 'G', 'H', 'J', 'K']].sum().to_frame().T
    grand_total['A'] = 'Grand Total'
    grand_total['B'] = ''
    grand_total['C'] = ''
    grand_total['%I'] = 'Auto%'
    grand_total = grand_total[df.columns]
    combined = pd.concat([combined, grand_total], ignore_index=True)
    
    # 计算小计/总计的百分比（按业务逻辑调整）
    for idx in combined.index:
        row = combined.loc[idx]
        if row['A'].startswith('Sub Total') or row['A'] == 'Grand Total':
            d_val = row['D']
            if d_val != 0:
                total_parts = row['E'] + row['F'] + row['G'] + row['H']
                pct = round(total_parts / d_val * 100)
                combined.loc[idx, '%I'] = f'{pct}%'
            else:
                combined.loc[idx, '%I'] = '0%'
    
    return combined.drop('%I_num', axis=1)

# 生成最终结果
final_df = add_subtotals(df)
# 打印对齐后的结果
print(final_df.to_string(index=False))

代码说明 #

• 先将百分比列转为数值，避免字符串聚合错误
• 通过groupby遍历A列分组，生成对应小计行并合并到原数据
• 单独计算总计行并追加
• 针对小计/总计行，按照自定义逻辑计算百分比（可根据实际业务规则修改）
• 最后格式化输出，匹配预期表格样式

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