How to filter rows and columns based on the maximum value in a Python DataFrame

Question

Shown below are few details on a DataFrame.

Below is the syntax that is been used and do not get the expected output.

df = df.sort_values(by=['country','Year','Value'], ascending=[True,True,False])
df = df.drop_duplicates('country')

how could I get the expected output shown below

What is the output you currently get? Where is your exact problem? — 9769953, May 01 '22 at 15:02
If you're after the highest value per country, then sorting on year is irrelevant, and you should remove that. — 9769953, May 01 '22 at 15:05

score 3 · Accepted Answer · answered May 01 '22 at 15:05

Try sorting by "Value" and keeping the last row for each country

>>> df.sort_values("Value").drop_duplicates("country",keep="last")
    Year country  Value
2   2003     USA   7000
6   2002   India   9000
10  2001   Japan  10000

Alternatively, you could use groupby:

>>> df[df["Value"].eq(df.groupby("country")["Value"].transform('max'))]
    Year country  Value
2   2003     USA   7000
6   2002   India   9000
10  2001   Japan  10000

score 0 · Answer 2 · answered May 01 '22 at 15:05

0

Try:

df.sort_values('Value',ascending=False).groupby('country').head(1)

answered May 01 '22 at 15:05

rhug123

7,893
1
9
24

How to filter rows and columns based on the maximum value in a Python DataFrame

2 Answers2