Get lowest value after groupby - Pandas

user_8199334 0
Viewed 4434

I have a table with the following format:

data = {'City' : ['London', 'Paris', 'Paris','NY' 'London'], 'Distance' : [5, 1, 7, 2, 6]}
df = pd.DataFrame(data)
df

    City    Distance
0   London  5
1   Paris   1
2   Paris   7
3   NY      2
4   London  6

I want to create a table with all rows with a unique 'City', and whenever there are 2 or more rows with the same 'City' value, I would like it to return the one with the lowest 'Distance'. So in this case I wanted a table like this:

City    Distance
London  5
Paris   1
NY      2

I know I can use:

df.groupby('City')

But I don't know what to add to it to return the smallest 'Distance'.

Best, Rosa

pandas python
5 Answers

You need DataFrameGroupBy.idxmin for indexes of minimal Distance per group and then select rows by loc:

df1 = df.loc[df.groupby('City', sort=False)['Distance'].idxmin()]
print (df1)
     City  Distance
0  London         5
1   Paris         1
3      NY         2

Detail:

print (df.groupby('City', sort=False)['Distance'].idxmin())
City
London    0
Paris     1
NY        3
Name: Distance, dtype: int64
user_29010020

Sometime groupby is unnecessary, try drop_duplicates

df.sort_values('Distance').drop_duplicates('City')

Out[377]: 
     City  Distance
0  London         5
1   Paris         1
3      NY         2
user_79645270

You can use 

>>> df.groupby(['City'], sort=False)['Distance'].min()
City
London    5
Paris     1
NY        2
Name: Distance, dtype: int64
user_45591100

My opinion is that @jezrael offers the most idiomatic approach within a groupby. I've offered the same solution myself on other answers. However, here are some other alternatives.

Option 1
Use pd.DataFrame.nsmallest within an apply
This offers clean logic even if the api is a bit clumsy. I think this version of nsmallest should be available to the groupby object. But as of pandas 0.20.3, it is not. So we use it within the general purpose apply method. Make sure to use group_keys=False in the call to groupby in order to avoid awkward additional indices.

df.groupby('City', group_keys=False).apply(
    lambda d: d.nsmallest(1, columns='Distance'))

     City  Distance
0  London         5
3      NY         2
1   Paris         1

Option 2
Was taken by @Wen so I deleted.

user_23366540

It´s an old question but for the sake of completion:

df.sort_values(by=['Distance'], ascending=True).groupby('City').first().reset_index(drop=False)

provides the same output.

user_110911480
Related