Getting latest end date in pandas-CodePudding

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.

CodePudding user response：

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)