python - pandas 在行之间移动增量

Question

我有一个看起来像这样的数据框:

Area    Sector  Date          Amt
FICC    GOV     31/01/2019    100
EQ      CORP    31/01/2019     50
FICC    GOV     31/03/2019    200
EQ      CORP    31/03/2019    100
FICC    GOV     31/12/2018     50
EQ      CORP    31/12/2018     25

我想根据日期获取增量。所以我期望的输出是

Area    Sector  Date          Amt  Delta
FICC    GOV     31/12/2018     50
EQ      CORP    31/12/2018     25
FICC    GOV     31/01/2019    100     50
EQ      CORP    31/01/2019     50     25
FICC    GOV     31/03/2019    200    100
EQ      CORP    31/03/2019    100     50

pandas 有没有一种有效的方法来实现上述目标？

Answer 1

排序日期，然后groupby + diff

import pandas as pd

df['Date'] = pd.to_datetime(df.Date)
df = df.sort_values('Date')
df['Delta'] = df.groupby(['Area', 'Sector']).Amt.diff()

   Area Sector       Date  Amt  Delta
4  FICC    GOV 2018-12-31   50    NaN
5    EQ   CORP 2018-12-31   25    NaN
0  FICC    GOV 2019-01-31  100   50.0
1    EQ   CORP 2019-01-31   50   25.0
2  FICC    GOV 2019-03-31  200  100.0
3    EQ   CORP 2019-03-31  100   50.0