Get rows corresponding to the minimum with pandas groupby

user_9510696 0
Viewed 163

I have a table need to groupby by condition:

R_num ORG name level
13    Dm   Ad   17
13    Dm   Af   16

When i use it gives me 13 Dm Ad 16, which is like data being manipulated.

df1=df.reset_index().groupby(['R_num','ORG']).agg({'name':'first','level':['min']})

The result I want is 13 Dm Af 16, I know probably something wrong with 'name':'first' but how do i fix this please?

Thank you

pandas-groupby pandas dataframe group-by python
2 Answers

IIUC, you should use groupby and idxmin:

# df.loc[df.groupby(['R_num','ORG'])['level'].agg('idxmin')]
df.loc[df.groupby(['R_num','ORG'])['level'].idxmin()]

   R_num ORG name  level
1     13  Dm   Af     16
user_49090870

Using drop_duplicates after sort_values

newdf = df.sort_values('level').drop_duplicates(['R_num','ORG'])
newdf
   R_num ORG name  level
1     13  Dm   Af     16
user_79645270
Related