主页 / coding / 问题 / 79447714
Accepted
Ishigami
Asked: 2025-02-18 17:23:36 +0800 CST

根据 Pandas 数据框中最接近的最后一个日期创建新列

  • 772

我有一个熊猫数据框，看起来像

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_rec_1”、“student_rec_2”、“student_rec_3”：

对于每个 Student_ID，student_rec_1 等于该学生在最近的最后一次考试中的成绩，如果不存在则等于 np.nan。

类似地，student_rec_2 等于该学生在最近一次日期的倒数第二次考试中的成绩，如果不存在则等于 np.nan，

student_rec_3 等于该学生在最近日期的倒数第三次考试中的排名，如果不存在则等于 np.nan。因此，期望的结果如下

data_new = {
'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],
'student_rec_1': [9,9,9,9,9,7,7,7,8,np.nan,np.nan,np.nan,2,2,6,6,6,2,np.nan,np.nan],
'student_rec_2': [6,6,6,6,6,8,8,8,2,np.nan,np.nan,np.nan,1,1,2,2,2,7,np.nan,np.nan],
'student_rec_3': [3,3,3,3,3,2,2,2,1,np.nan,np.nan,np.nan,5,5,7,7,7,np.nan,np.nan,np.nan]
}
df_new = pd.DataFrame(data_new)

这就是我尝试过的：

df['日期'] = pd.to_datetime(df['日期'])

df = df.sort_values(['日期', '测试编号'], 升序=[False, False])

def get_last_n_records(group，n):返回group['Place'].shift(-n)

df['student_rec_1'] = df.groupby('学生ID').apply(get_last_n_records, 1).reset_index(level=0, drop=True) df['student_rec_2'] = df.groupby('学生ID').apply(get_last_n_records, 2).reset_index(level=0, drop=True) df['student_rec_3'] = df.groupby('学生ID').apply(get_last_n_records, 3).reset_index(level=0, drop=True)

但它只是改变了每个学生的位置，并没有考虑到“最后一天”的因素，而且无论如何都会改变位置。

pandas

1 个回答

  1. Best Answer

    首先Date按转换列to_datetime，创建DataFrame带有重命名列的辅助程序df_cand，以便可以使用左连接到原始列（为避免使用删除原始索引rename）。然后按日期时间过滤，排序并按 创建计数器以GroupBy.cumcount获取3最后的值，这些值将合并到原始列df：

    df['Date'] = pd.to_datetime(df['Date'])

df = df.reset_index().rename(columns={'index':'orig_index'})

df_cand = (df.rename(columns={'Date':'cand_Date',
                             'Test_Number':'cand_Test_Number',
                             'Place':'cand_Place'})
             .drop(['orig_index'], axis=1))

merged = df.merge(df_cand, on='Student_ID', how='left')

merged = merged[merged['cand_Date'].lt(merged['Date'])]
merged = merged.sort_values(['Student_ID','orig_index','cand_Date','cand_Test_Number'],
                             ascending=[True,True,False,False])

merged['cand_rank'] = merged.groupby('orig_index').cumcount().add(1)

pivot = (merged[merged['cand_rank'].le(3)]
          .pivot(index='orig_index',columns='cand_rank',values='cand_Place')
          .add_prefix('student_rec'))

out = df.join(pivot).drop('orig_index', axis=1)

    print(out)

         Date  Test_Number  Student_ID  Place  student_rec_1  student_rec_2  \
0  2024-07-14            5           2      3            9.0            6.0   
1  2024-07-14            4           2      5            9.0            6.0   
2  2024-07-14            3           2      7            9.0            6.0   
3  2024-07-14            2           2      3            9.0            6.0   
4  2024-07-14            1           2      1            9.0            6.0   
5  2024-03-14            3           2      9            7.0            8.0   
6  2024-03-14            2           2      6            7.0            8.0   
7  2024-03-14            1           2      3            7.0            8.0   
8  2024-02-14            4           2      7            8.0            2.0   
9  2024-02-10            3           2      8            NaN            NaN   
10 2024-02-10            2           2      2            NaN            NaN   
11 2024-02-10            1           2      1            NaN            NaN   
12 2024-04-13            2           1      3            2.0            1.0   
13 2024-04-13            1           1      4            2.0            1.0   
14 2023-02-11            3           1      2            6.0            2.0   
15 2023-02-11            2           1      1            6.0            2.0   
16 2023-02-11            1           1      5            6.0            2.0   
17 2011-10-11            1           1      6            2.0            7.0   
18 2011-05-02            2           1      2            NaN            NaN   
19 2011-05-02            1           1      7            NaN            NaN   

    student_rec_3  
0             3.0  
1             3.0  
2             3.0  
3             3.0  
4             3.0  
5             2.0  
6             2.0  
7             2.0  
8             1.0  
9             NaN  
10            NaN  
11            NaN  
12            5.0  
13            5.0  
14            7.0  
15            7.0  
16            7.0  
17            NaN  
18            NaN  
19            NaN

    编辑：为了获得更好的性能，可以使用 numpy 按组工作的解决方案 - 比较所有之前的日期mask，按累计和创建顺序numpy.cumsum，因此可能获得N最高排序numpy.argmax。因为可能有些值不存在，所以有必要添加条件numpy.any并返回必要的列：

    df['Date'] = pd.to_datetime(df['Date'])

N = 3

def f(x):

    dates = x['Date'].to_numpy()        
    places = x['Place'].astype(float).to_numpy() 

    mask = dates < dates[:, None]  
    cs = np.cumsum(mask, axis=1) 
    targets = np.array(range(1, N+1))[None, :] 
    cs_ext = cs[..., None]

    cond = cs_ext == targets
    first_idx = np.argmax(cond, axis=1)
    m = np.any(cond, axis=1) 

    arr = places[first_idx]  
    arr[~m] = np.nan

    return pd.DataFrame(arr, 
                        index=x.index, 
                        columns=[f'student_rec_{i+1}' for i in range(N)])


out = df.join(df.groupby('Student_ID', group_keys=False)[['Place','Date']].apply(f))

    print(out)
         Date  Test_Number  Student_ID  Place  student_rec_1  student_rec_2  \
0  2024-07-14            5           2      3            9.0            6.0   
1  2024-07-14            4           2      5            9.0            6.0   
2  2024-07-14            3           2      7            9.0            6.0   
3  2024-07-14            2           2      3            9.0            6.0   
4  2024-07-14            1           2      1            9.0            6.0   
5  2024-03-14            3           2      9            7.0            8.0   
6  2024-03-14            2           2      6            7.0            8.0   
7  2024-03-14            1           2      3            7.0            8.0   
8  2024-02-14            4           2      7            8.0            2.0   
9  2024-02-10            3           2      8            NaN            NaN   
10 2024-02-10            2           2      2            NaN            NaN   
11 2024-02-10            1           2      1            NaN            NaN   
12 2024-04-13            2           1      3            2.0            1.0   
13 2024-04-13            1           1      4            2.0            1.0   
14 2023-02-11            3           1      2            6.0            2.0   
15 2023-02-11            2           1      1            6.0            2.0   
16 2023-02-11            1           1      5            6.0            2.0   
17 2011-10-11            1           1      6            2.0            7.0   
18 2011-05-02            2           1      2            NaN            NaN   
19 2011-05-02            1           1      7            NaN            NaN   

    student_rec_3  
0             3.0  
1             3.0  
2             3.0  
3             3.0  
4             3.0  
5             2.0  
6             2.0  
7             2.0  
8             1.0  
9             NaN  
10            NaN  
11            NaN  
12            5.0  
13            5.0  
14            7.0  
15            7.0  
16            7.0  
17            NaN  
18            NaN  
19            NaN
    • 3

相关问题