如何基于Date列使用groupby为DataFrame添加指定统计列？

问题背景 #

现有如下DataFrame：

Sr.No        Date         Tag    score
01         10-02-2022    pass     10
02         10-02-2022    fail     5
03         10-02-2022    pass     10
04         11-02-2022    grace    3
05         11-02-2022    pass     15
06         11-02-2022    pass     15

需要基于Date列分组，添加以下统计列：

• no_of_records：当日记录总数
• pass_count：当日pass标签的记录数
• fail_count：当日fail标签的记录数
• grace_count：当日grace标签的记录数
• pass_score_total：当日pass标签的score总和

期望生成的DataFrame格式如下（仅每个日期组首行显示统计值，其余行留空）：

Sr.No   Date       Tag  score no_of_records pass_count fail_count grace_count pass_score_total 
01    10-02-2022  pass   10         3           2          1          0             20
02    10-02-2022  fail   5          
03    10-02-2022  pass   10
04    11-02-2022  grace  3          3           2          0          1             30
05    11-02-2022  pass   15
06    11-02-2022  pass   15

实现方案 #

方法1：用groupby.transform直接生成统计列 #

这种方式会给每行填充对应日期的统计值，后续可按需将非首行的统计值置空，匹配期望格式：

import pandas as pd

# 构造原始DataFrame
data = {
    'Sr.No': ['01', '02', '03', '04', '05', '06'],
    'Date': ['10-02-2022', '10-02-2022', '10-02-2022', '11-02-2022', '11-02-2022', '11-02-2022'],
    'Tag': ['pass', 'fail', 'pass', 'grace', 'pass', 'pass'],
    'score': [10, 5, 10, 3, 15, 15]
}
df = pd.DataFrame(data)

# 计算当日总记录数
df['no_of_records'] = df.groupby('Date')['Sr.No'].transform('count')

# 计算各标签当日记录数
df['pass_count'] = df.groupby('Date')['Tag'].transform(lambda x: (x == 'pass').sum())
df['fail_count'] = df.groupby('Date')['Tag'].transform(lambda x: (x == 'fail').sum())
df['grace_count'] = df.groupby('Date')['Tag'].transform(lambda x: (x == 'grace').sum())

# 计算当日pass标签的score总和
df['pass_score_total'] = df.groupby('Date').apply(
    lambda x: x[x['Tag'] == 'pass']['score'].sum()
).reset_index(drop=True).repeat(df.groupby('Date').count()['Sr.No'].values)

# 将每个日期组除首行外的统计列置空
mask = df.duplicated('Date', keep='first')
df.loc[mask, ['no_of_records', 'pass_count', 'fail_count', 'grace_count', 'pass_score_total']] = ''

print(df)

方法2：先分组聚合再合并 #

先按日期计算所有统计指标，再合并回原DataFrame，同样可处理非首行空值：

import pandas as pd

data = {
    'Sr.No': ['01', '02', '03', '04', '05', '06'],
    'Date': ['10-02-2022', '10-02-2022', '10-02-2022', '11-02-2022', '11-02-2022', '11-02-2022'],
    'Tag': ['pass', 'fail', 'pass', 'grace', 'pass', 'pass'],
    'score': [10, 5, 10, 3, 15, 15]
}
df = pd.DataFrame(data)

# 分组聚合统计指标
agg_df = df.groupby('Date').agg(
    no_of_records=('Sr.No', 'count'),
    pass_count=('Tag', lambda x: (x == 'pass').sum()),
    fail_count=('Tag', lambda x: (x == 'fail').sum()),
    grace_count=('Tag', lambda x: (x == 'grace').sum()),
    pass_score_total=('score', lambda x: x[df.loc[x.index, 'Tag'] == 'pass'].sum())
).reset_index()

# 合并回原DataFrame
df = df.merge(agg_df, on='Date', how='left')

# 将每个日期组除首行外的统计列置空
mask = df.duplicated('Date', keep='first')
df.loc[mask, ['no_of_records', 'pass_count', 'fail_count', 'grace_count', 'pass_score_total']] = ''

print(df)

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