Get the date when it was 52 week low and high-CodePudding

I have yearly data like this below having date, sc_code and price

2021-08-16,542942,1.36
2021-08-16,542948,0.07
2021-08-16,542963,12.09
2021-08-16,542967,17.35
2021-08-16,542969,9.86
2021-08-16,543079,9.76
2021-08-16,543150,5.99
2021-08-16,543144,0.03
2021-08-16,543092,0.09
2021-08-16,936967,1149.88
2021-08-16,936720,1199.93
2021-08-16,937499,945.00
2021-08-16,935862,1182.10
2021-08-16,935319,1870.00

I get the high and low values using below

df_temp = df_data.groupby(['sc_code'], sort=False)['close'].agg([('L','min'),('H', 'max')]).add_prefix('close').reset_index().copy()

I would like to know dates when they were high and low.

Can you please help

CodePudding user response：

Use DataFrameGroupBy.idxmax DataFrameGroupBy.idxmin for indices by :

df_temp = (df_data.groupby(['sc_code'], sort=False).agg(Lmin=('close','min'),
                                                        Hmax=('close','max'),
                                                        Ldate=('close','idxmin'),
                                                        Hdate=('close','idxmax'))
                  .reset_index()
                  .copy())

EDIT: Solution for oldies pandas versions:

df_temp = (df_data.groupby(['sc_code'], sort=False)['close'].agg([('Lmin','min'),
                                                                  ('Hmax','max'),
                                                                  ('Ldate','idxmin'),
                                                                  ('Hdate','idxmax')])
                  .reset_index()
                  .copy())