如何在Pandas中按年度9月1日后分组累计统计学生满分次数？

现有如下结构的DataFrame：

Date            Student_ID  Exam_Score
2020-12-24      1           79
2020-12-24      3           100
2020-12-24      4           88
2021-01-19      1           100
2021-01-19      2           100
2021-01-19      3           99
2021-01-19      4           72
2022-09-30      3           100
2022-09-30      2           100
2022-09-30      1           100
2022-09-30      5           46
2023-04-23      3           100
2023-04-23      2           97
2023-04-23      1           100
2024-07-19      2           89  
2024-07-19      1           100
2024-07-19      4           93
2024-07-19      3           100
2024-09-19      1           100 
2024-09-19      2           80
2024-09-19      3           100
2024-09-19      4           80
2024-10-20      1           80
2024-10-20      3           99
2024-10-20      2           80

需要计算新列Recent_Full_Marks，逻辑为：针对每个Student_ID，统计该学生在当前日期之前、且在当前日期前一年的9月1日之后的考试中获得满分（100分）的次数。例如，2024-11-22时，学生1在2024-09-01至2024-11-22期间获得过2次满分，对应Recent_Full_Marks为2。期望结果如下：

Date            Student_ID  Exam_Score      Recent_Full_Marks
2020-12-24      1           79              0
2020-12-24      3           100             0
2020-12-24      4           88              0
2021-01-19      1           100             0
2021-01-19      2           100             0
2021-01-19      3           99              1
2021-01-19      4           72              0
2022-09-30      3           100             0
2022-09-30      2           100             0
2022-09-30      1           100             0
2022-09-30      5           46              0
2023-11-23      3           100             0
2023-11-23      2           97              0
2023-11-23      1           100             0
2024-07-19      2           89              0
2024-07-19      1           100             1
2024-07-19      4           93              0
2024-07-19      3           100             1
2024-09-19      1           100             0
2024-09-19      2           80              0
2024-09-19      3           100             0
2024-09-19      4           80              0
2024-10-20      1           100             1
2024-10-20      3           99              1
2024-10-20      2           80              0
2024-11-22      1           70              2
2024-11-22      3           100             1
2024-11-22      2           78              0

尝试过以下代码，但只能统计每年年初后的满分次数，无法以9月1日为起始点：

Date = pd.to_datetime(df['Date'], dayfirst=True)
full = (df.assign(Date=Date)
              .sort_values(['Student_ID','Date'], ascending=[True,True])
              ['Exam_Score'].eq(100))
df['Recent_Full_Marks']=(full.groupby([df['Student_ID'], Date.dt.year], group_keys=False).apply(lambda g: g.shift(1, fill_value=0).cumsum()))

核心思路是先为每条记录计算对应的统计起始日期（当前日期的前一年9月1日），再按学生分组，统计每个学生在当前记录日期之前、且在起始日期之后的满分次数。

基础实现代码 #

import pandas as pd

# 转换日期格式
df['Date'] = pd.to_datetime(df['Date'])

# 为每条记录计算统计起始日期：当前日期的前一年9月1日
def get_start_date(date):
    # 判断当前日期是否在9月1日之前，决定起始年份
    start_year = date.year - 1 if date.month < 9 else date.year
    return pd.Timestamp(f"{start_year}-09-01")

df['Start_Date'] = df['Date'].apply(get_start_date)

# 按Student_ID分组，计算每个学生的Recent_Full_Marks
def count_recent_full_marks(group):
    # 按日期排序
    group = group.sort_values('Date')
    # 初始化结果列
    group['Recent_Full_Marks'] = 0
    # 遍历每条记录，统计符合条件的满分次数
    for idx, row in group.iterrows():
        mask = (group['Date'] >= row['Start_Date']) & (group['Date'] < row['Date']) & (group['Exam_Score'] == 100)
        group.loc[idx, 'Recent_Full_Marks'] = mask.sum()
    return group

# 应用分组计算并重置索引
df = df.groupby('Student_ID').apply(count_recent_full_marks).reset_index(drop=True)

# 删除辅助列Start_Date
df = df.drop('Start_Date', axis=1)

高效优化版（适用于大数据量） #

如果数据集较大，可改用向量化操作提升效率：

import pandas as pd

df['Date'] = pd.to_datetime(df['Date'])
df['is_full'] = df['Exam_Score'].eq(100)

# 按学生和日期排序
df = df.sort_values(['Student_ID', 'Date'])

def compute_recent_full(group):
    # 计算每条记录的起始日期
    group['start_date'] = group['Date'].apply(lambda x: pd.Timestamp(f"{x.year - (x.month < 9)}-09-01"))
    # 遍历每条记录，统计区间内的满分次数
    group['Recent_Full_Marks'] = [
        group.loc[:i-1, 'is_full'][group.loc[:i-1, 'Date'] >= row.start_date].sum() 
        for i, row in enumerate(group.itertuples(), 1)
    ]
    return group

# 应用分组计算
df = df.groupby('Student_ID', group_keys=False).apply(compute_recent_full)
# 删除辅助列
df = df.drop(['is_full', 'start_date'], axis=1)

代码说明 #

1. 起始日期计算：根据当前日期所在月份，确定统计的起始年份——若当前日期在9月1日之前，起始年份为前一年；否则为当前年份，最终得到YYYY-09-01作为统计区间的左边界。
2. 分组统计：按学生分组后，对每个学生的记录按日期排序，遍历每条记录时，筛选出该学生在起始日期之后、当前日期之前的满分记录，统计数量即为Recent_Full_Marks的值。

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