How to isolate the maximum value in a data frame in pandas python library

Question

I'm trying to isolate and print the maximum value in a pandas dataframe in python.

# Data frame:

df
>>     0   A   B   C
    0  0   0   0   0
    A  0  -3  -3   5
    B  0  -3  -6   2
    C  0   5   0  -3
    D  0   5   2  -3
    E  0   0  10   5
    F  0  -3   5  15

I have managed to isolate the value with the following code:

x = df.max(axis=0)
maxValue = max(x)

maxValue
>> 15

But how can I access this element? Is there a way to iterate through the elements of the data frame such that

for elements in df:
    if element == maxValue:
        m = element

Or something on those lines? I need to find the largest element, in this case 15, and retrieve its position i.e. (C,F) in this example. I then need to store this and then find the next largest element surrounding the first, along with its position.

# desired output
[(C,F), (B,E), (A,D)]

I hope this makes sense! Any advice on how I could implement this would be much appreciated! :)

Does this answer your question? Pandas max value index

Craicerjack
– Craicerjack

2020-03-05 14:35:04 +00:00
Commented Mar 5, 2020 at 14:35 — Craicerjack
– Craicerjack, Commented Mar 5, 2020 at 14:35

anky · Accepted Answer · 2020-03-05 14:37:24Z

2

You can use:

#replace 'df.iloc[:,1:]' with 'df' if first column isnt 0
out = [*df.iloc[:,1:][::-1].idxmax().items()] 
#[('A', 'D'), ('B', 'E'), ('C', 'F')]

answered Mar 5, 2020 at 14:37

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

5 Comments

Charlie Vagg Over a year ago

I tried this and I got a TypeError: reduction operation 'argmax' not allowed for this dtype ?

anky Over a year ago

@CharlieVagg can you try [*df.iloc[:,1:][::-1].astype(float).idxmax().items()] ?

Charlie Vagg Over a year ago

I got this as an output: [<itertools.izip object at 0x7f5ef0b76c80>]

anky Over a year ago

@CharlieVagg works fine for me, may be some version issues not sure try list(df.iloc[:,1:][::-1].astype(float).idxmax().items()) but really works if you test with the sample data if all columns are numeric

Charlie Vagg Over a year ago

ah I was using python 2 instead of 3, it has worked now. Thank you so much!

BENY · Accepted Answer · 2020-03-05 14:40:15Z

2

IIUC sort_values + stack

df.stack().sort_values().groupby(level=1).tail(1).index.tolist()
Out[229]: [('A', '0'), ('D', 'A'), ('E', 'B'), ('F', 'C')]

answered Mar 5, 2020 at 14:40

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

jezrael · Accepted Answer · 2020-03-05 14:43:05Z

2

I understand question is necessary sorting maximal values, so use if nedd omit first column DataFrame.iloc, then DataFrame.agg for positions of maximums with max for maximums, sorting them by DataFrame.sort_values, select it to Series and last convert to list of tuples:

L = (list(df.iloc[:, 1:]
            .agg(['idxmax','max'])
            .sort_values('max', axis=1, ascending=False)
            .loc['idxmax'].items()))
print (L)
[('C', 'F'), ('B', 'E'), ('A', 'C')]

For all columns remove iloc:

L = (list(df.agg(['idxmax','max'])
            .sort_values('max', axis=1, ascending=False)
            .loc['idxmax'].items()))
print (L)
[('C', 'F'), ('B', 'E'), ('A', 'C'), ('0', '0')]

edited Mar 5, 2020 at 14:43

answered Mar 5, 2020 at 14:37

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

2 Comments

Charlie Vagg Over a year ago

I get a KeyError: 'idxmax'. Do you know why this might be?

jezrael Over a year ago

@CharlieVagg - Yes, data are not numeric. Try

L = (list(df.select_dtypes(np.number).agg(['idxmax','max'])             .sort_values('max', axis=1, ascending=False)             .loc['idxmax'].items())) print (L)

Collectives™ on Stack Overflow

How to isolate the maximum value in a data frame in pandas python library

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related