Pandas分组后按日期计算累计统计量的优化实现咨询

我有如下DataFrame：

import pandas as pd
import numpy as np

df = pd.DataFrame({"group1":["A", "A", "A","B","A","B","B","B","B","B","A","A","B"], 
                    "group2":["1", "1", "2","1","2","2","2","1","2","1","1","1","2"],
                    "date":['2022-11-01', '2022-11-01', '2022-11-02', '2022-11-01', '2022-11-01', 
'2022-11-01', '2022-11-02', '2022-11-02','2022-11-01',  '2022-11-01', '2022-11-02', '2022-11-02', '2022-11-02'], 
"value":np.random.randint(10, high=50, size=13)})

需求是：按group1和group2分组，针对date计算累计计数、累计均值和累计方差。

我自己写了一段代码实现需求，但感觉比较繁琐，想知道有没有更简洁的实现方式：

# sort
tmp = df.sort_values(["date", "group1", "group2"])
# cum mean
tmp2 = tmp.groupby(["group1", "group2"])["value"].expanding().mean().reset_index() 
# cum var
tmp2["var"] = tmp.groupby(["group1", "group2"])["value"].expanding().var().values
# set old index in order to get the date from original df
tmp2 = tmp2.reset_index().set_index("level_2")
tmp2 = pd.concat([tmp["date"], tmp2], axis=1).drop(['index'], axis=1) # remove "index" col
# get the cum mean and cum var for each date
tmp2 = tmp2.groupby(["group1", "group2", "date"]).agg(cnt=("value", "count"), mean=("value", "last"), var=("var", "last")).reset_index()
# create cum count column
tmp2["cumcnt"] = tmp2.groupby(["group1", "group2"])["cnt"].cumsum()
# group by
tmp2.groupby(["group1", "group2", "date"]).last()

这段代码返回的结果结构示意如下：

可以通过先按日期聚合基础统计量，再分组计算累计值的方式简化代码，避免多次分组和索引操作：

import pandas as pd
import numpy as np

# 1. 按group1、group2、date分组，计算每日的计数、总和、平方和
daily_stats = df.groupby(["group1", "group2", "date"], as_index=False).agg(
    cnt=("value", "count"),
    sum_val=("value", "sum"),
    sum_sq=("value", lambda x: (x**2).sum())
)

# 2. 按group1、group2分组，计算累计计数、累计总和、累计平方和
daily_stats = daily_stats.sort_values("date").groupby(["group1", "group2"], as_index=False).apply(
    lambda g: g.assign(
        cumcnt=g["cnt"].cumsum(),
        cum_sum=g["sum_val"].cumsum(),
        cum_sum_sq=g["sum_sq"].cumsum()
    )
).reset_index(drop=True)

# 3. 推导计算累计均值和累计方差
daily_stats["mean"] = daily_stats["cum_sum"] / daily_stats["cumcnt"]
# 无偏方差公式：(Σx²/n - (Σx/n)²) * n/(n-1)，n=1时方差无意义设为NaN
daily_stats["var"] = (daily_stats["cum_sum_sq"] / daily_stats["cumcnt"] - daily_stats["mean"]**2) * daily_stats["cumcnt"] / (daily_stats["cumcnt"] - 1)
daily_stats.loc[daily_stats["cumcnt"] == 1, "var"] = np.nan

# 4. 整理保留需要的列
result = daily_stats[["group1", "group2", "date", "cnt", "mean", "var", "cumcnt"]]
print(result)

优化思路说明 #

• 先聚合每日统计量，避免逐行expanding计算的额外开销；
• 用cumsum直接计算累计值，逻辑更直观；
• 通过统计公式推导方差，避免重复调用expanding().var()，效率更高；
• 代码步骤更少，索引操作更简洁，可读性更强。

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