You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

如何在Python的GroupBy聚合结果中添加总计行？

avatar
DevOps 小满
2026-7-5
问题

我有一份存储调查数据的DataFrame，每行对应一个个体，包含4个分类列['AGE_GROUP','STATE','MONTH', 'SEX']，还有多个识别个体就业/失业状态的指示变量，以及代表个体在总人口中对应数量的权重变量FINALWT。

目前我通过这4个分类列对数据分组，用自定义函数计算统计估计值，代码如下：

result = grouped_data.apply(lambda x: pd.Series({
    'Population': agg.weighted_frequency(x['POP'], x['FINALWT']),
    'LabourForce': agg.weighted_frequency(x['LABOURFORCE'], x['FINALWT']),
    'Employed': agg.weighted_frequency(x['EMPLOYED'], x['FINALWT']),
    'Employed_ft': agg.weighted_frequency(x['EMPLOYED_FT'], x['FINALWT']),
    'Employed_pt': agg.weighted_frequency(x['EMPLOYED_PT'], x['FINALWT']),
    'Unemployed': agg.weighted_frequency(x['UNEMPLOYED'], x['FINALWT']),
    'Unemployment_rate': agg.unemployment_rate(x['UNEMPLOYED'], x['LABOURFORCE'], x['FINALWT']),
    'Participation_rate': agg.participation_rate(x['LABOURFORCE'], x['FINALWT']),
    'Employment_rate': agg.employment_rate(x['EMPLOYED'], x['LABOURFORCE'], x['FINALWT'])
}))

result = result.reset_index()

现在需要为每个分组变量添加总计行，求可行的实现方法。

解决方案

方法1：按分组层级逐步拼接总计行

针对每个分组维度依次计算总计/小计，再与原结果合并排序，能实现全维度、多级维度的总计行需求。

示例代码：

import pandas as pd

# 原分组计算逻辑
grouped_data = df.groupby(['AGE_GROUP','STATE','MONTH', 'SEX'])
result = grouped_data.apply(lambda x: pd.Series({
    'Population': agg.weighted_frequency(x['POP'], x['FINALWT']),
    'LabourForce': agg.weighted_frequency(x['LABOURFORCE'], x['FINALWT']),
    'Employed': agg.weighted_frequency(x['EMPLOYED'], x['FINALWT']),
    'Employed_ft': agg.weighted_frequency(x['EMPLOYED_FT'], x['FINALWT']),
    'Employed_pt': agg.weighted_frequency(x['EMPLOYED_PT'], x['FINALWT']),
    'Unemployed': agg.weighted_frequency(x['UNEMPLOYED'], x['FINALWT']),
    'Unemployment_rate': agg.unemployment_rate(x['UNEMPLOYED'], x['LABOURFORCE'], x['FINALWT']),
    'Participation_rate': agg.participation_rate(x['LABOURFORCE'], x['FINALWT']),
    'Employment_rate': agg.employment_rate(x['EMPLOYED'], x['LABOURFORCE'], x['FINALWT'])
})).reset_index()

# 1. 全维度总计
total_all = df.apply(lambda x: pd.Series({
    'Population': agg.weighted_frequency(x['POP'], x['FINALWT']),
    'LabourForce': agg.weighted_frequency(x['LABOURFORCE'], x['FINALWT']),
    'Employed': agg.weighted_frequency(x['EMPLOYED'], x['FINALWT']),
    'Employed_ft': agg.weighted_frequency(x['EMPLOYED_FT'], x['FINALWT']),
    'Employed_pt': agg.weighted_frequency(x['EMPLOYED_PT'], x['FINALWT']),
    'Unemployed': agg.weighted_frequency(x['UNEMPLOYED'], x['FINALWT']),
    'Unemployment_rate': agg.unemployment_rate(x['UNEMPLOYED'], x['LABOURFORCE'], x['FINALWT']),
    'Participation_rate': agg.participation_rate(x['LABOURFORCE'], x['FINALWT']),
    'Employment_rate': agg.employment_rate(x['EMPLOYED'], x['LABOURFORCE'], x['FINALWT'])
}), axis=0).to_frame().T
total_all[['AGE_GROUP','STATE','MONTH', 'SEX']] = '总计'

# 2. AGE_GROUP+STATE+MONTH层级总计
total_3dim = df.groupby(['AGE_GROUP','STATE','MONTH']).apply(lambda x: pd.Series({
    'Population': agg.weighted_frequency(x['POP'], x['FINALWT']),
    'LabourForce': agg.weighted_frequency(x['LABOURFORCE'], x['FINALWT']),
    'Employed': agg.weighted_frequency(x['EMPLOYED'], x['FINALWT']),
    'Employed_ft': agg.weighted_frequency(x['EMPLOYED_FT'], x['FINALWT']),
    'Employed_pt': agg.weighted_frequency(x['EMPLOYED_PT'], x['FINALWT']),
    'Unemployed': agg.weighted_frequency(x['UNEMPLOYED'], x['FINALWT']),
    'Unemployment_rate': agg.unemployment_rate(x['UNEMPLOYED'], x['LABOURFORCE'], x['FINALWT']),
    'Participation_rate': agg.participation_rate(x['LABOURFORCE'], x['FINALWT']),
    'Employment_rate': agg.employment_rate(x['EMPLOYED'], x['LABOURFORCE'], x['FINALWT'])
})).reset_index()
total_3dim['SEX'] = '总计'

# 3. AGE_GROUP+STATE层级总计
total_2dim = df.groupby(['AGE_GROUP','STATE']).apply(lambda x: pd.Series({
    'Population': agg.weighted_frequency(x['POP'], x['FINALWT']),
    'LabourForce': agg.weighted_frequency(x['LABOURFORCE'], x['FINALWT']),
    'Employed': agg.weighted_frequency(x['EMPLOYED'], x['FINALWT']),
    'Employed_ft': agg.weighted_frequency(x['EMPLOYED_FT'], x['FINALWT']),
    'Employed_pt': agg.weighted_frequency(x['EMPLOYED_PT'], x['FINALWT']),
    'Unemployed': agg.weighted_frequency(x['UNEMPLOYED'], x['FINALWT']),
    'Unemployment_rate': agg.unemployment_rate(x['UNEMPLOYED'], x['LABOURFORCE'], x['FINALWT']),
    'Participation_rate': agg.participation_rate(x['LABOURFORCE'], x['FINALWT']),
    'Employment_rate': agg.employment_rate(x['EMPLOYED'], x['LABOURFORCE'], x['FINALWT'])
})).reset_index()
total_2dim[['MONTH', 'SEX']] = '总计'

# 4. AGE_GROUP层级总计
total_1dim = df.groupby(['AGE_GROUP']).apply(lambda x: pd.Series({
    'Population': agg.weighted_frequency(x['POP'], x['FINALWT']),
    'LabourForce': agg.weighted_frequency(x['LABOURFORCE'], x['FINALWT']),
    'Employed': agg.weighted_frequency(x['EMPLOYED'], x['FINALWT']),
    'Employed_ft': agg.weighted_frequency(x['EMPLOYED_FT'], x['FINALWT']),
    'Employed_pt': agg.weighted_frequency(x['EMPLOYED_PT'], x['FINALWT']),
    'Unemployed': agg.weighted_frequency(x['UNEMPLOYED'], x['FINALWT']),
    'Unemployment_rate': agg.unemployment_rate(x['UNEMPLOYED'], x['LABOURFORCE'], x['FINALWT']),
    'Participation_rate': agg.participation_rate(x['LABOURFORCE'], x['FINALWT']),
    'Employment_rate': agg.employment_rate(x['EMPLOYED'], x['LABOURFORCE'], x['FINALWT'])
})).reset_index()
total_1dim[['STATE', 'MONTH', 'SEX']] = '总计'

# 合并所有结果并排序，让总计行处于对应分组下方
final_result = pd.concat([result, total_1dim, total_2dim, total_3dim, total_all], ignore_index=True)
final_result = final_result.sort_values(by=['AGE_GROUP','STATE','MONTH', 'SEX'], key=lambda x: x != '总计')

方法2：利用groupby的margins参数快速添加全维度总计

如果只需要全维度的总计行，可直接使用pandas 1.3.0及以上版本支持的groupby(margins=True)参数，一步生成带总计的结果。

示例代码：

grouped_data = df.groupby(['AGE_GROUP','STATE','MONTH', 'SEX'], margins=True, margins_name='总计')
result_with_totals = grouped_data.apply(lambda x: pd.Series({
    'Population': agg.weighted_frequency(x['POP'], x['FINALWT']),
    'LabourForce': agg.weighted_frequency(x['LABOURFORCE'], x['FINALWT']),
    'Employed': agg.weighted_frequency(x['EMPLOYED'], x['FINALWT']),
    'Employed_ft': agg.weighted_frequency(x['EMPLOYED_FT'], x['FINALWT']),
    'Employed_pt': agg.weighted_frequency(x['EMPLOYED_PT'], x['FINALWT']),
    'Unemployed': agg.weighted_frequency(x['UNEMPLOYED'], x['FINALWT']),
    'Unemployment_rate': agg.unemployment_rate(x['UNEMPLOYED'], x['LABOURFORCE'], x['FINALWT']),
    'Participation_rate': agg.participation_rate(x['LABOURFORCE'], x['FINALWT']),
    'Employment_rate': agg.employment_rate(x['EMPLOYED'], x['LABOURFORCE'], x['FINALWT'])
})).reset_index()

方法3：手动插入指定维度的小计行

如果仅需要特定分组维度的小计（比如仅AGE_GROUP的类别小计），可单独计算该维度的小计后插入到对应位置。

示例代码：

# 原分组结果
result = grouped_data.apply(lambda x: pd.Series({
    'Population': agg.weighted_frequency(x['POP'], x['FINALWT']),
    'LabourForce': agg.weighted_frequency(x['LABOURFORCE'], x['FINALWT']),
    'Employed': agg.weighted_frequency(x['EMPLOYED'], x['FINALWT']),
    'Employed_ft': agg.weighted_frequency(x['EMPLOYED_FT'], x['FINALWT']),
    'Employed_pt': agg.weighted_frequency(x['EMPLOYED_PT'], x['FINALWT']),
    'Unemployed': agg.weighted_frequency(x['UNEMPLOYED'], x['FINALWT']),
    'Unemployment_rate': agg.unemployment_rate(x['UNEMPLOYED'], x['LABOURFORCE'], x['FINALWT']),
    'Participation_rate': agg.participation_rate(x['LABOURFORCE'], x['FINALWT']),
    'Employment_rate': agg.employment_rate(x['EMPLOYED'], x['LABOURFORCE'], x['FINALWT'])
})).reset_index()

# 计算AGE_GROUP维度的小计
subtotals = df.groupby('AGE_GROUP').apply(lambda x: pd.Series({
    'Population': agg.weighted_frequency(x['POP'], x['FINALWT']),
    'LabourForce': agg.weighted_frequency(x['LABOURFORCE'], x['FINALWT']),
    'Employed': agg.weighted_frequency(x['EMPLOYED'], x['FINALWT']),
    'Employed_ft': agg.weighted_frequency(x['EMPLOYED_FT'], x['FINALWT']),
    'Employed_pt': agg.weighted_frequency(x['EMPLOYED_PT'], x['FINALWT']),
    'Unemployed': agg.weighted_frequency(x['UNEMPLOYED'], x['FINALWT']),
    'Unemployment_rate': agg.unemployment_rate(x['UNEMPLOYED'], x['LABOURFORCE'], x['FINALWT']),
    'Participation_rate': agg.participation_rate(x['LABOURFORCE'], x['FINALWT']),
    'Employment_rate': agg.employment_rate(x['EMPLOYED'], x['LABOURFORCE'], x['FINALWT'])
})).reset_index()
subtotals[['STATE', 'MONTH', 'SEX']] = '小计'

# 合并并排序，让小计行处于对应AGE_GROUP分组下方
final_result = pd.concat([result, subtotals], ignore_index=True)
final_result = final_result.sort_values(by=['AGE_GROUP', 'STATE', 'MONTH', 'SEX'], key=lambda col: col != '小计' if col.name != 'AGE_GROUP' else True)

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.05 01:19:55