python - 获取与组内最小和最大日期相关的值的差异 - Python

Question

这是数据框:

ID     Date1         Date2  weight
123 1/1/2018    12/31/2018  147
123 1/1/2018    11/30/2018  136
123 1/1/2018    10/30/2018  128
123 1/1/2018    4/30/2000   150
123 5/5/2017    4/4/2017    160
123 5/5/2017    1/1/2016    170
524 4/4/2017    4/3/2017    180
524 4/4/2017    4/1/2017    150
524 4/4/2017    3/31/2017   130
524 3/3/2017    2/2/2017    210
524 3/3/2017    1/1/2017    250
524 2/3/2017    1/3/2017    230

对于每个 ID 和 Date1 组，我想要与最小和最大 Date2 关联的权重差异。预期输出为:

ID  Date1   Weight_Diff
123 1/1/2018     -3
123 5/5/2017    -10
524 4/4/2017     50
524 3/3/2017    -40
524 2/3/2017      0

我尝试了以下方法，但没有成功:

maxdate = df.groupby(['ID','Date1'])['Date2'].idxmax()
mindate = df.groupby(['ID','Date1'])['Date2'].idxmin()

df['diff'] = df.iloc[maxdate]['weight'] - df.iloc[mindate]['weight']

Answer 1

我认为我能想到的最易读的东西是:

g = df.groupby(['ID','Date1'])
diff = g.nth(0)['weight'] - g.nth(-1)['weight']
print(diff.reset_index())

假设:您已经对 date1 和 date2 列进行了排序

返回:

    ID     Date1  weight
0  123  1/1/2018      -3
1  123  5/5/2017     -10
2  524  2/3/2017       0
3  524  3/3/2017     -40
4  524  4/4/2017      50