如何在Pandas DataFrame多列分组中获取各层级列求和结果

原始数据 #

首先定义目标DataFrame：

import pandas as pd

df = pd.DataFrame({
    'team': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
    'task': ['X', 'X', 'Y', 'Y', 'X', 'X', 'Z', 'Z'],
    'day': ['M', 'T', 'M', 'T', 'M', 'T', 'M', 'T'],
    'in': [60, 80, 91, 11, 32, 18, 28, 15],
    'out': [11, 12, 31, 21, 13, 33, 42, 15]
})

需求说明 #

执行df.groupby(['team', 'task','day']).sum()仅能得到最细粒度的每日分组求和结果，现在需要在同一输出中同时展示团队层级、任务层级和每日明细的汇总数据，最终输出格式如下：

in  out
team task day         
A              242   75
A    X         140   23
A    X    M    60   11
          T    80   12
A    Y         102   52
     Y    M    91   31
          T    11   21
B              93   103
B    X         50   46
B    X    M    32   13
          T    18   33
B    Z         43   57
     Z    M    28   42
          T    15   15

解决方案 #

通过生成各层级的汇总结果，合并后按索引排序即可实现需求：

完整实现步骤 #

1. 分别生成三个层级的求和结果，并统一索引结构
2. 合并所有结果并按多索引排序
3. 调整空索引的显示格式优化输出

# 1. 生成各层级汇总结果
# 每日明细层级
day_summary = df.groupby(['team', 'task', 'day']).sum()

# 任务汇总层级：添加空的day索引列，对齐索引结构
task_summary = df.groupby(['team', 'task']).sum()
task_summary['day'] = ''
task_summary = task_summary.set_index('day', append=True)

# 团队汇总层级：添加空的task、day索引列，对齐索引结构
team_summary = df.groupby(['team']).sum()
team_summary['task'] = ''
team_summary['day'] = ''
team_summary = team_summary.set_index(['task', 'day'], append=True)

# 2. 合并并排序结果
combined_result = pd.concat([team_summary, task_summary, day_summary]).sort_index()

# 3. 优化空索引显示：将空值替换为空白字符串
combined_result.index = combined_result.index.set_levels(
    [''] if level == 1 else combined_result.index.levels[level] 
    for level in range(len(combined_result.index.levels))
)
combined_result.index = combined_result.index.set_levels(
    [''] if level == 2 else combined_result.index.levels[level] 
    for level in range(len(combined_result.index.levels))
)

# 查看最终输出
print(combined_result)

简化版实现 #

如果不需要严格的格式优化，可直接合并后排序：

combined = pd.concat([
    df.groupby(['team']).sum().assign(task='', day='').set_index(['task', 'day'], append=True),
    df.groupby(['team', 'task']).sum().assign(day='').set_index('day', append=True),
    df.groupby(['team', 'task', 'day']).sum()
]).sort_index()

# 快速替换空索引显示
combined.index = combined.index.set_levels(['' if pd.isna(i) else i for i in combined.index.levels[1]], level=1)
combined.index = combined.index.set_levels(['' if pd.isna(i) else i for i in combined.index.levels[2]], level=2)

print(combined)

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