Search code examples
pythonpandasdataframemax

Find maximum value of a column and return the corresponding row values using Pandas

Country       Place  Value
US       NewYork     562
US       Michigan    854
US       Illinois    356
UK       London      778
UK       Manchester  512
Spain    Madrid      509
India    Mumbai      196
US       Kansas      894
UK       Liverpool   796
Spain    Barcelona   792

Using Pandas I am trying to find the Country and Place with the maximum value.

This returns the maximum value:

data.groupby(['Country','Place'])['Value'].max()

But how do I get the corresponding Country and Place name?

Solution

  • Assuming df has a unique index, this gives the row with the maximum value:

    In [34]: df.loc[df['Value'].idxmax()]
Out[34]: 
Country        US
Place      Kansas
Value         894
Name: 7

    Note that idxmax returns index labels. So if the DataFrame has duplicates in the index, the label may not uniquely identify the row, so df.loc may return more than one row.

    Therefore, if df does not have a unique index, you must make the index unique before proceeding as above. Depending on the DataFrame, sometimes you can use stack or set_index to make the index unique. Or, you can simply reset the index (so the rows become renumbered, starting at 0):

    df = df.reset_index()