如何基于最近上一日期在Pandas DataFrame中创建新列

原始数据 #

import pandas as pd
import numpy as np

data = {
'Date': ['2024-07-14','2024-07-14','2024-07-14','2024-07-14','2024-07-14','2024-03-14','2024-03-14','2024-03-14','2024-02-14','2024-02-10','2024-02-10','2024-02-10','2024-04-13','2024-04-13','2023-02-11','2023-02-11','2023-02-11','2011-10-11','2011-05-02','2011-05-02'],
'Test_Number': [5,4,3,2,1,3,2,1,4,3,2,1,2,1,3,2,1,1,2,1],
'Student_ID': [2,2,2,2,2,2,2,2,2,2,2,2,1,1,1,1,1,1,1,1],
'Place': [3,5,7,3,1,9,6,3,7,8,2,1,3,4,2,1,5,6,2,7]
}
df = pd.DataFrame(data)

需求 #

为每个Student_ID添加三个新列：

• student_rec_1：该学生当前日期更早的最近测试的Place值，无则为np.nan
• student_rec_2：当前日期更早的倒数第二个测试的Place值，无则为np.nan
• student_rec_3：当前日期更早的倒数第三个测试的Place值，无则为np.nan

注：需将同一日期的所有测试视为一个组，提取该组之后（更早）的连续三个测试值，填充到当前组的所有行中。

问题分析 #

原代码仅对Place做整体位移，未按日期组划分，导致跨日期的测试被错误提取，无法满足需求。

解决方案 #

# 转换日期类型并按学生、日期降序、测试编号降序排序
df['Date'] = pd.to_datetime(df['Date'])
df = df.sort_values(['Student_ID', 'Date', 'Test_Number'], ascending=[True, False, False]).reset_index(drop=True)

# 为每个学生的日期组分配组号（最新日期组为0）
df['date_group'] = df.groupby('Student_ID')['Date'].transform(lambda x: x.factorize(sort=False)[0])

def fill_records(group):
    all_places = group['Place'].tolist()
    group_indices = group.groupby('date_group').indices
    sorted_groups = sorted(group_indices.keys())
    
    # 初始化新列
    group[['student_rec_1', 'student_rec_2', 'student_rec_3']] = np.nan
    
    for g in sorted_groups:
        idx = group_indices[g]
        # 获取当前组之后的所有测试Place值
        start_pos = idx[-1] + 1
        next_places = all_places[start_pos:]
        # 取前三个，不足补NaN
        recs = next_places[:3] + [np.nan]*(3 - len(next_places))
        # 填充当前组的所有行
        group.loc[idx, 'student_rec_1'] = recs[0]
        group.loc[idx, 'student_rec_2'] = recs[1]
        group.loc[idx, 'student_rec_3'] = recs[2]
    
    return group

# 应用到每个学生组
df_new = df.groupby('Student_ID').apply(fill_records).reset_index(drop=True)
# 移除临时列并恢复排序
df_new = df_new.drop('date_group', axis=1).sort_values(['Student_ID', 'Date', 'Test_Number'], ascending=[True, False, False]).reset_index(drop=True)

print(df_new)

说明 #

1. 排序：先按学生分组，再按日期从新到旧、测试编号从大到小排序，确保后续提取的是更早的测试记录。
2. 日期分组：为每个学生的不同日期分配组号，方便批量处理同一日期的所有行。
3. 填充记录：对每个日期组，提取该组之后的连续三个测试值，填充到当前组的所有行中，不足三个则补np.nan。

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