Groupby show max value and corresponding label - pandas

I'm trying to group specific values and return the max value of a separate column. I'm also hoping to return the corresponding label this max value is associated with. Using below, I'm grouping values by Item, Group, Direction and the max value is determined from Value. I'm hoping to return the corresponding Label with the respective max in Value.

df = pd.DataFrame({        'Item' : [10,10,10,10,10,10,10,10,10],        'Label' : ['X','V','Y','Z','D','A','E','B','M'],          'Value' : [80.0,80.0,200.0,210.0,260.0,260.0,300.0,300.0,310.0],        'Group' : ['Red','Green','Red','Green','Red','Green','Green','Red','Green'],                  'Direction' : ['Up','Up','Down','Down','Up','Down','Up','Down','Up'],                                              })    max_num = (df.groupby(['Item','Group','Direction'])['Value','Label']                     .max()                     .unstack([1, 2],                      fill_value = 0)                     .reset_index()                     )    max_num.columns = [f'{x[0]}_{x[1]}' for x in max_num.columns]  

intended output:

   Item  Red_Up_Value Red_Up_ID  Red_Down_Value Red_Down_ID  Green_Up_Value Green_Up_ID  Green_Down_Value  Green_Down_ID  0    10         260.0         D           300.0           B           310.0           M               0.0            NaN  

https://stackoverflow.com/questions/66667256/groupby-show-max-value-and-corresponding-label-pandas March 17, 2021 at 01:03PM