python - 在 Pandas 数据框中过滤和分配值

Question

我有一个如下所示的数据框:

Date    Student    Subject    Score
10/1    Luke       English    65
10/1    Alex       English    75     
10/2    Luke       Maths      50     
10/3    Lily       Maths      45     
10/3    Alex       Maths      90     
10/4    Lily       English    60     
10/5    Alex       English    70     
10/6    Luke       English    55     
10/6    Luke       Maths      65     
10/7    Lily       Maths      80     
10/8    Alex       Maths      75     
10/8    Lily       English    90     

我试图在这里完成两个任务，输出应该是数据框中的三个新列:

1. Date of last/next test:

对于每一行，我想查看同一学生和主题的下一次测试日期。例如，第 1 行和第 2 行的“下一次测试日期”应分别为“10/6”和“10/5”。

同样，对于最后一行，“previous test date”的值应为“10/4”。

2. Change in score:

对于本专栏，我希望返回基于学生最后一次考试的分数变化。例如，第 6-8 行的值应为“nan”、“-5”和“-10”。

提前感谢您的帮助!

Answer 1

您可以使用 groupby 和 shift 来解决您的问题，如下所示。使用 fillna() 替换 Nan 值。

问题 1:查找上次和下一次考试日期:

In [941]: df['next_test_dt'] = df.groupby(['Student','Subject'])['Date'].shift(-1).fillna(df['Date'])

In [943]: df['last_test_dt'] = df.groupby(['Student','Subject'])['Date'].shift(1).fillna(df['Date'])

In [944]: df
Out[944]: 
    Date Student  Subject  Score next_test_dt last_test_dt
0   10/1    Luke  English     65         10/6         10/1
1   10/1    Alex  English     75         10/5         10/1
2   10/2    Luke    Maths     50         10/6         10/2
3   10/3    Lily    Maths     45         10/7         10/3
4   10/3    Alex    Maths     90         10/8         10/3
5   10/4    Lily  English     60         10/8         10/4
6   10/5    Alex  English     70         10/5         10/1
7   10/6    Luke  English     55         10/6         10/1
8   10/6    Luke    Maths     65         10/6         10/2
9   10/7    Lily    Maths     80         10/7         10/3
10  10/8    Alex    Maths     75         10/8         10/3
11  10/8    Lily  English     90         10/8         10/4

问题 2:找出与上次考试分数的差异:

In [954]: df['score_diff'] = df['Score'] - df.groupby(['Student','Subject'])['Score'].shift(1)

In [957]: df
Out[957]: 
    Date Student  Subject  Score next_test_dt last_test_dt  score_diff
0   10/1    Luke  English     65         10/6         10/1         NaN
1   10/1    Alex  English     75         10/5         10/1         NaN
2   10/2    Luke    Maths     50         10/6         10/2         NaN
3   10/3    Lily    Maths     45         10/7         10/3         NaN
4   10/3    Alex    Maths     90         10/8         10/3         NaN
5   10/4    Lily  English     60         10/8         10/4         NaN
6   10/5    Alex  English     70         10/5         10/1        -5.0
7   10/6    Luke  English     55         10/6         10/1       -10.0
8   10/6    Luke    Maths     65         10/6         10/2        15.0
9   10/7    Lily    Maths     80         10/7         10/3        35.0
10  10/8    Alex    Maths     75         10/8         10/3       -15.0
11  10/8    Lily  English     90         10/8         10/4        30.0