My data has –
Acc_id Crt_id start_date end_date
80924 SXD 10/15/11 11/29/11
80924 HZH 8/15/14 9/28/14
80924 FYT 2/1/16 1/31/18
73982 YYU 5/30/11 6/28/11
15456 26V 5/31/11 5/30/12
12909 FUY 10/15/17 10/31/19
80911 TJ5 8/1/11 8/13/11
75419 KAV 11/1/13 3/4/14
75419 MNF 11/1/13 12/13/13
75419 QU3 3/1/14 3/30/14
I need it as-
Acc_id Crt_id start_date end_date Acc_end_date
80924 SXD 10/15/11 11/29/11 1/31/18
80924 HZH 8/15/14 9/28/14 1/31/18
80924 FYT 2/1/16 1/31/18 1/31/18
73982 YYU 5/30/11 6/28/11 6/28/11
15456 26V 5/31/11 5/30/12 5/30/12
12909 FUY 10/15/17 10/31/19 10/31/19
80911 TJ5 8/1/11 8/13/11 8/13/11
75419 KAV 11/1/13 3/4/14 3/30/14
75419 MNF 11/1/13 12/13/13 3/30/14
75419 QU3 3/1/14 3/30/14 3/30/14
75419 JJK 1/2/12 6/3/12 3/30/14
acc_id end date as the latest crt_id end date in a new column.
Please advice any approaches, I tried group by and pivot but not not getting anything.
>Solution :
IIUC
df['end_date'] = pd.to_datetime(df['end_date'], infer_datetime_format=True)
df['Acc_end_date'] = df.groupby('Acc_id')['end_date'].transform(max)