How do I find the maximum value in specific columns and return the corresponding value in another column of my data frame?

Question

I have a data frame that's something like this:

Keyword  A B C D ... X Y Z
First    1 2 3 4 ... 8 5 3
Second   2 6 2 9 ... 6 1 2
Third    3 3 2 3 ... 5 3 4

I've also got a list letters which just has the column names from A to Z.

I want to get the keyword of the maximum value in each column.

If I want to get it for just one column, something like this works:

max = df['A'].max()
df.loc[df['A'] == max, 'Keyword']

A Third

How would I go about getting it for all the columns?

So that would be:

A Third
B Second
C First
D Second
...
X First
Y First
Z Third

BENY · Accepted Answer · 2019-04-28 04:00:13Z

2

1st set_index then idxmax

df.set_index('Keyword').idxmax()
A     Third
B    Second
C     First
D    Second
X     First
Y     First
Z     Third
dtype: object

answered Apr 28, 2019 at 4:00

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Taco Over a year ago

Would this work if I have other columns I don't want considered? Like for example a column Misc between Keyword and A

BENY Over a year ago

df.set_index('Keyword').drop(['Columns1','Columns2'],1).idxmax() @Taco

BENY Over a year ago

@Taco happy coding :-)

Taco Over a year ago

Thanks! Additional question if you don't mind. Let's say the Misc column has a bunch of values from 0 to 1. Now if I only want to consider rows where Misc is between 0.6 and 0.8, how would I go about that?

Taco Over a year ago

Thank you, you're a lifesaver! :)

|

Collectives™ on Stack Overflow

How do I find the maximum value in specific columns and return the corresponding value in another column of my data frame?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related