如何为三级多索引列DataFrame插入分组小计列？

我有一个包含三级多索引列的DataFrame：

quarter           Q1                        Q2                        Totals
year              2021        2022           2021         2022                      
                 qty orders  qty orders    qty orders   qty orders   qty orders
month name                                       
January          40  2        5   1         1   2         0 0             46  5
February         20  8        2   3         4   6         0 0             26  17
March            2  10        7   4         3   3         0 0             12  17
Totals           62 20       14   8         8   11        0 0             84  39

通过按层级(0,2)分组后，得到如下小计DataFrame：

quarter           Q1           Q2          Totals                     
                 qty orders  qty orders    qty orders  
month name                                       
January          45  3        1   2         46   5     
February         22  10       4   6         26   16     
March            9  14        3   3         12   17   
Totals           76 28        8   11        84   39

需要将这个小计DataFrame插入原DataFrame中，不改变列层级、索引结构，得到如下目标DataFrame：

quarter       Q1                                   Q2                        Totals
year        2021        2022      Subtotal    2021        2022     Subtotal                 
            qty orders qty orders qty orders qty orders qty orders qty orders qty orders
month name                                       
January     40  2       5   1     45   3       1  2       0  0       1  2     46  5
February    20  8       2   3     22   10      4  6       0  0       4  6     26  16
March       2  10       7   4     9    14      3  3       0  0       3  3     12  17
Totals      62 20      14   8     76   28      8  11      0  0       8  11    84 39

请问该如何实现？

可以通过以下步骤实现需求：

1. 对齐小计DataFrame的列层级结构 #

原DataFrame的列是三级多索引：(quarter, year, metric)，而小计DataFrame只有两级：(quarter, metric)。需要给小计列添加中间的year层级，值设为"Subtotal"，让两者列结构完全匹配：

import pandas as pd

# 假设原DataFrame为df_original，小计DataFrame为df_subtotal
# 调整小计的列层级
df_subtotal.columns = pd.MultiIndex.from_tuples(
    [(q, "Subtotal", m) for q, m in df_subtotal.columns],
    names=["quarter", "year", "metric"]
)

2. 合并并调整列顺序 #

用pd.concat按列合并两个DataFrame，然后自定义排序规则，确保每个quarter下先展示各年份数据，再展示小计：

# 合并两个DataFrame
combined = pd.concat([df_original, df_subtotal], axis=1)

# 自定义排序键：让Subtotal在每个quarter的年份数据之后
sorted_columns = combined.columns.sort_values(
    key=lambda col: (col[0], col[1] if col[1] != "Subtotal" else "z")
)
df_final = combined[sorted_columns]

3. 完整可运行示例 #

import pandas as pd

# 构造原DataFrame
data_original = [
    [40,2,5,1,1,2,0,0,46,5],
    [20,8,2,3,4,6,0,0,26,17],
    [2,10,7,4,3,3,0,0,12,17],
    [62,20,14,8,8,11,0,0,84,39]
]
columns_original = pd.MultiIndex.from_tuples(
    [
        ("Q1","2021","qty"), ("Q1","2021","orders"),
        ("Q1","2022","qty"), ("Q1","2022","orders"),
        ("Q2","2021","qty"), ("Q2","2021","orders"),
        ("Q2","2022","qty"), ("Q2","2022","orders"),
        ("Totals","","qty"), ("Totals","","orders")
    ],
    names=["quarter","year","metric"]
)
df_original = pd.DataFrame(data_original, 
                          index=["January","February","March","Totals"],
                          columns=columns_original)
df_original.index.name = "month name"

# 构造小计DataFrame
data_subtotal = [
    [45,3,1,2,46,5],
    [22,10,4,6,26,16],
    [9,14,3,3,12,17],
    [76,28,8,11,84,39]
]
columns_subtotal = pd.MultiIndex.from_tuples(
    [("Q1","qty"), ("Q1","orders"),
     ("Q2","qty"), ("Q2","orders"),
     ("Totals","qty"), ("Totals","orders")],
    names=["quarter","metric"]
)
df_subtotal = pd.DataFrame(data_subtotal,
                          index=["January","February","March","Totals"],
                          columns=columns_subtotal)
df_subtotal.index.name = "month name"

# 调整小计列层级
df_subtotal.columns = pd.MultiIndex.from_tuples(
    [(q, "Subtotal", m) for q, m in df_subtotal.columns],
    names=["quarter", "year", "metric"]
)

# 合并并排序列
combined = pd.concat([df_original, df_subtotal], axis=1)
sorted_cols = combined.columns.sort_values(
    key=lambda col: (col[0], col[1] if col[1] != "Subtotal" else "z")
)
df_final = combined[sorted_cols]

# 打印结果
print(df_final.to_string())

运行上述代码后，得到的df_final结构和数据将与目标DataFrame完全一致。

内容的提问来源于stack exchange，提问作者Judy T Raj