How to find value in a column based on min value from another column per group pandas-CodePudding

I have a dataframe like this

I need to generate a new column which hold the quota of min scale_qty of each group created by plant, material. This is the expected result:

All that I can do so far is just to get the rows which contain the min scale_qty or find the minimum of scale_qty itself, I'm stucking at how the get the quota for each of that

g = df.groupby(['plant', 'material']) df['min_scale_qty'] = g['scale_qty'].transform(min)

I need your help on this. Thank you!

CodePudding user response：

Use pandas.Series.idxmin combined with transform to get the new column.

df['quota_of_min_scale_qty'] = (
    df.loc[
        df
        .groupby(['plant', 'material'])['scale_qty']
        .transform(lambda x: x.idxmin()), 'quota']
    .values
)