在Pandas中计算组均值与总均值差值的实现方法问询

场景与现有代码 #

我正在用Pandas计算分组统计量并存入新DataFrame，目前通过groupby+agg针对group1和group2分组，计算var1、var2的均值、中位数和计数，代码如下：

import pandas as pd

df = pd.DataFrame([["A","C",2,5],
                   ["A","C",4,3],
                   ["B","C",3,1],
                   ["B","D",1,2],
                   ["A","D",5,1],
                   ["B","D",3,4]], columns=["group1", "group2", "var1", "var2"])

# 现有分组统计代码
df.groupby(["group1", "group2"]).agg(["mean", "median", "count"])

运行后得到的结果：

var1              var2             
              mean median count mean median count
group1 group2                                    
A      C       3.0    3.0     2  4.0    4.0     2
       D       5.0    5.0     1  1.0    1.0     1
B      C       3.0    3.0     1  1.0    1.0     1
       D       2.0    2.0     2  3.0    3.0     2

需求说明 #

需要给agg（或apply）添加自定义函数，计算每个变量的组均值与总均值的差值，并将生成的mean_diff列添加到上述结果中。

当前数据的总均值为：

df[["var1", "var2"]].mean()

输出：

var1    3.000000
var2    2.666667
dtype: float64

预期输出 #

var1                        var2             
              mean mean_diff median count mean  mean_diff  median count
group1 group2                                                        
A      C       3.0       0.0    3.0     2  4.0   1.333333     4.0     2
       D       5.0       2.0    5.0     1  1.0  -1.666667     1.0     1
B      C       3.0       0.0    3.0     1  1.0  -1.666667     1.0     1
       D       2.0      -1.0    2.0     2  3.0   0.333333     3.0     2

解决方案 #

方法1：在agg中直接定义自定义统计量

先计算全局总均值，再通过字典配置agg的每个变量需要计算的统计量，包括自定义的mean_diff：

import pandas as pd

df = pd.DataFrame([["A","C",2,5],
                   ["A","C",4,3],
                   ["B","C",3,1],
                   ["B","D",1,2],
                   ["A","D",5,1],
                   ["B","D",3,4]], columns=["group1", "group2", "var1", "var2"])

# 计算全局总均值
total_means = df[["var1", "var2"]].mean()

# 自定义计算均值差值的函数
def calc_mean_diff(group_series, total_mean):
    return group_series.mean() - total_mean

# 配置agg的统计量规则
result = df.groupby(["group1", "group2"]).agg(
    var1={
        "mean": "mean",
        "mean_diff": lambda x: calc_mean_diff(x, total_means["var1"]),
        "median": "median",
        "count": "count"
    },
    var2={
        "mean": "mean",
        "mean_diff": lambda x: calc_mean_diff(x, total_means["var2"]),
        "median": "median",
        "count": "count"
    }
)

# 调整列层级顺序，与预期输出一致
result = result.reorder_levels([1, 0], axis=1).sort_index(axis=1)
print(result)

方法2：先算基础统计，再合并差值列

这种方式更灵活，先得到基础分组统计结果，再单独计算mean_diff后合并：

import pandas as pd

df = pd.DataFrame([["A","C",2,5],
                   ["A","C",4,3],
                   ["B","C",3,1],
                   ["B","D",1,2],
                   ["A","D",5,1],
                   ["B","D",3,4]], columns=["group1", "group2", "var1", "var2"])

total_means = df[["var1", "var2"]].mean()

# 第一步：计算基础分组统计
base_stats = df.groupby(["group1", "group2"]).agg(["mean", "median", "count"])

# 第二步：计算组均值与总均值的差值
mean_diff_df = base_stats.xs("mean", level=1, axis=1) - total_means
# 给差值列添加层级标签，匹配基础统计的MultiIndex结构
mean_diff_df.columns = pd.MultiIndex.from_tuples([(col, "mean_diff") for col in mean_diff_df.columns])

# 第三步：合并结果并调整列顺序
result = pd.concat([base_stats, mean_diff_df], axis=1)
# 按变量排序，并调整每个变量下的列顺序为mean→mean_diff→median→count
result = result.sort_index(axis=1).reindex(columns=["mean", "mean_diff", "median", "count"], level=1)
print(result)

两种方法都能得到符合预期的输出，方法2在变量较多时更易维护。

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