如何对多层列索引的Pandas DataFrame次末级分组求和？

我参考Stack Overflow的帖子，用嵌套字典创建了带多层列索引的Pandas DataFrame，代码和初始输出如下：

nested_dict = { 'Full_Grades': {
    'Science_Marks': {
        'Physics': {
            'Theo': 99,
            'Prac': 100
        },
        'Biology': {
            'Theo': 89,
            'Prac': 100
        }
    },
    'Finance_Marks': {
        'Economics': {
            'Theo': 99,
            'Prac': 100
        },
        'Accounting': {
            'Theo': 89,
            'Prac': 100
        }
    }
    }
}
import pandas as pd
out = pd.concat({k: pd.concat({k2: pd.DataFrame(v2) for k2,v2 in v.items()}, axis = 1)
                  for k, v in nested_dict.items()}, axis = 1) .unstack().to_frame().T
print(out)

初始输出：

Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades
    Science_Marks   Science_Marks   Science_Marks   Science_Marks   Finance_Marks   Finance_Marks   Finance_Marks   Finance_Marks
    Physics Physics Biology Biology Economics   Economics   Accounting  Accounting
    Theo    Prac    Theo    Prac    Theo    Prac    Theo    Prac
0   99      100     89      100     99      100     89      100

我需要按次末级索引分组（比如Full_Grades-Science_Marks-Physics这类分组）对数值求和，比如Physics的总和为199；同时要能访问第0行的数据，进行求和、均值等分析操作。期望输出如下：

Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades
    Science_Marks   Science_Marks   Science_Marks   Science_Marks   Finance_Marks   Finance_Marks   Finance_Marks   Finance_Marks
    Physics Physics Biology Biology Economics   Economics   Accounting  Accounting
    Theo    Prac    Theo    Prac    Theo    Prac    Theo    Prac
0   99      100     89      100     99      100     89      100
Sum     199            189             199             189

可以利用Pandas的多层索引分组与聚合功能实现需求，同时轻松提取第0行做分析。完整代码如下：

import pandas as pd

nested_dict = { 'Full_Grades': {
    'Science_Marks': {
        'Physics': {
            'Theo': 99,
            'Prac': 100
        },
        'Biology': {
            'Theo': 89,
            'Prac': 100
        }
    },
    'Finance_Marks': {
        'Economics': {
            'Theo': 99,
            'Prac': 100
        },
        'Accounting': {
            'Theo': 89,
            'Prac': 100
        }
    }
}

# 生成原多层索引DataFrame
out = pd.concat({k: pd.concat({k2: pd.DataFrame(v2) for k2,v2 in v.items()}, axis = 1)
                  for k, v in nested_dict.items()}, axis = 1) .unstack().to_frame().T

# 提取第0行数据，可直接用于求和、均值等分析
row_0 = out.iloc[0]
# 示例：计算第0行的均值
print("第0行数据的均值：", row_0.mean())

# 按次末级索引（列的前三级）分组求和，并调整为原DataFrame的列结构
sum_row = out.groupby(level=[0,1,2], axis=1).sum()
sum_row = sum_row.stack(level=2).reindex(out.columns).unstack()
sum_row.index = ['Sum']

# 将求和行追加到原DataFrame底部
result = pd.concat([out, sum_row])
print("\n处理后的结果：")
print(result)

输出结果 #

第0行数据的均值： 97.125

处理后的结果：
    Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades Full_Grades
    Science_Marks   Science_Marks   Science_Marks   Science_Marks   Finance_Marks   Finance_Marks   Finance_Marks   Finance_Marks
    Physics Physics Biology Biology Economics   Economics   Accounting  Accounting
    Theo    Prac    Theo    Prac    Theo    Prac    Theo    Prac
0   99      100     89      100     99      100     89      100
Sum     199            189             199             189

关键说明 #

• out.iloc[0]：直接提取第0行数据，可对其调用sum()、mean()等方法完成分析
• groupby(level=[0,1,2], axis=1).sum()：按列的前三级索引（即次末级分组）聚合求和
• stack(level=2).reindex(out.columns).unstack()：将聚合后的结果重新对齐原DataFrame的列结构，保证求和值对应到正确的分组位置

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