DataFrame

What I want to do is groupby ID and create columns for the Min and Max date for each column. But I'm not sure where to start. I know there's aggregate functions out there that work well with one column but I'm wondering is there a straight forward solution when dealing with multiple columns?

Desired Output

CodePudding user response：

First convert columns by list to datetimes in DataFrame.apply and to_datetime, then correct aggregation min and max, flatten MultiIndex with capitalize:

cols = ['DateMade','DelDate','ExpDate']
df[cols] = df[cols].apply(pd.to_datetime, dayfirst=True)

df1 = df.groupby('ID')[cols].agg(['min','max'])
df1.columns = df1.columns.map(lambda x: f'{x[0]}_{x[1].capitalize()}')
df1 = df1.reset_index()
print (df1)
   ID DateMade_Min DateMade_Max DelDate_Min DelDate_Max ExpDate_Min  \
0   1   2020-01-01   2020-01-01  2020-06-05  2020-06-07  2022-05-06   
1   2   2020-04-03   2021-01-01  2020-08-07  2020-08-31  2022-12-15   

  ExpDate_Max  
0  2022-09-09  
1  2023-01-09  

For orginal format of datetimes add lambda function with Series.dt.strftime:

cols = ['DateMade','DelDate','ExpDate']
df[cols] = df[cols].apply(pd.to_datetime, dayfirst=True)

df1 = df.groupby('ID')[cols].agg(['min','max'])
df1.columns = df1.columns.map(lambda x: f'{x[0]}_{x[1].capitalize()}')

df1 = df1.apply(lambda x: x.dt.strftime('%d/%m/%Y'))

df1 = df1.reset_index()
print (df1)
   ID DateMade_Min DateMade_Max DelDate_Min DelDate_Max ExpDate_Min  \
0   1   01/01/2020   01/01/2020  05/06/2020  07/06/2020  06/05/2022   
1   2   03/04/2020   01/01/2021  07/08/2020  31/08/2020  15/12/2022   

  ExpDate_Max  
0  09/09/2022  
1  09/01/2023