How to filter rows based on comparing another column's value-CodePudding

I want to produce a new dataframe from the old dataframe.

>>> old_df
   id name  value
0  aa    A     10
1  bb    B     20
2  aa    C      7
3  cc    D     30
4  aa    E     25
5  bb    F     12
6  dd    G    100

I only want those rows where value is lowest.

>>> new_df
   id name  value
0  aa    C      7
1  bb    F     12
2  cc    D     30
3  dd    G    100

Can anyone help me??

CodePudding user response：

This will find the min of each id based on the value column

df.loc[df.groupby('id')['value'].idxmin()]

CodePudding user response：

Here is working example:

idx = old_df.groupby(['id'])['value'].transform(min) == old_df['value']
new_df = old_df[idx].sort_values('id')