Find row where values for column is maximal in a pandas DataFrame
Use the pandas idxmax
function. It's straightforward:
>>> import pandas
>>> import numpy as np
>>> df = pandas.DataFrame(np.random.randn(5,3),columns=['A','B','C'])
>>> df
A B C
0 1.232853 -1.979459 -0.573626
1 0.140767 0.394940 1.068890
2 0.742023 1.343977 -0.579745
3 2.125299 -0.649328 -0.211692
4 -0.187253 1.908618 -1.862934
>>> df['A'].idxmax()
3
>>> df['B'].idxmax()
4
>>> df['C'].idxmax()
1
Alternatively you could also use
numpy.argmax
, such asnumpy.argmax(df['A'])
-- it provides the same thing, and appears at least as fast asidxmax
in cursory observations.idxmax()
returns indices labels, not integers.Example': if you have string values as your index labels, like rows 'a' through 'e', you might want to know that the max occurs in row 4 (not row 'd').
if you want the integer position of that label within the
Index
you have to get it manually (which can be tricky now that duplicate row labels are allowed).
HISTORICAL NOTES:
idxmax()
used to be calledargmax()
prior to 0.11argmax
was deprecated prior to 1.0.0 and removed entirely in 1.0.0- back as of Pandas 0.16,
argmax
used to exist and perform the same function (though appeared to run more slowly thanidxmax
). argmax
function returned the integer position within the index of the row location of the maximum element.- pandas moved to using row labels instead of integer indices. Positional integer indices used to be very common, more common than labels, especially in applications where duplicate row labels are common.
For example, consider this toy DataFrame
with a duplicate row label:
In [19]: dfrm
Out[19]:
A B C
a 0.143693 0.653810 0.586007
b 0.623582 0.312903 0.919076
c 0.165438 0.889809 0.000967
d 0.308245 0.787776 0.571195
e 0.870068 0.935626 0.606911
f 0.037602 0.855193 0.728495
g 0.605366 0.338105 0.696460
h 0.000000 0.090814 0.963927
i 0.688343 0.188468 0.352213
i 0.879000 0.105039 0.900260
In [20]: dfrm['A'].idxmax()
Out[20]: 'i'
In [21]: dfrm.iloc[dfrm['A'].idxmax()] # .ix instead of .iloc in older versions of pandas
Out[21]:
A B C
i 0.688343 0.188468 0.352213
i 0.879000 0.105039 0.900260
So here a naive use of idxmax
is not sufficient, whereas the old form of argmax
would correctly provide the positional location of the max row (in this case, position 9).
This is exactly one of those nasty kinds of bug-prone behaviors in dynamically typed languages that makes this sort of thing so unfortunate, and worth beating a dead horse over. If you are writing systems code and your system suddenly gets used on some data sets that are not cleaned properly before being joined, it's very easy to end up with duplicate row labels, especially string labels like a CUSIP or SEDOL identifier for financial assets. You can't easily use the type system to help you out, and you may not be able to enforce uniqueness on the index without running into unexpectedly missing data.
So you're left with hoping that your unit tests covered everything (they didn't, or more likely no one wrote any tests) -- otherwise (most likely) you're just left waiting to see if you happen to smack into this error at runtime, in which case you probably have to go drop many hours worth of work from the database you were outputting results to, bang your head against the wall in IPython trying to manually reproduce the problem, finally figuring out that it's because idxmax
can only report the label of the max row, and then being disappointed that no standard function automatically gets the positions of the max row for you, writing a buggy implementation yourself, editing the code, and praying you don't run into the problem again.
Find maximum value of a column and return the corresponding row values using Pandas
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()
Find the max value at each row - Pandas data frame
Can you try the code below:
mask = df.filter(regex=r'[A-Z]1').stack().between(1, 5).values
df['MAX'] = df.filter(regex=r'[A-Z]2').stack()[mask].groupby(level=0).max()
The expected result for the first row: 0,9 (1 and 5 values are between A1 and E2). The expected result for the second row: 0,85 (1 and 5 values are between A1 and J2). The expected result for the third row: 0,86 (1 and 5 values are between A1 and C2)
>>> df
Type ID A1 A2 ... J1 J2 MAX
0 X 1212 1 0.300 ... 9 0.0030 0.90
1 Y 2342 1 0.004 ... 5 0.0001 0.85
2 Z 2421 1 0.860 ... 17 0.3000 0.86
[3 rows x 21 columns]
Find row with min/max value for each day in Pandas DataFrame
Use DataFrameGroupBy.idxmax
andDataFrameGroupBy.idxmin
for indices by minimal and maximal values, convert to Series
and select original DatetimeIndex
by DataFrame.loc
:
df1 = (df.loc[df.groupby(df.index.day)['Charge']
.agg(['idxmin', 'idxmax']).stack()].sort_index())
print (df1)
Charge
2022-01-03 13:19:02 99.5
2022-01-03 13:21:02 64.2
If need aggregate new columns:
df2 = df.groupby(df.index.day)['Charge'].agg(['min','max', 'idxmin', 'idxmax'])
print (df2)
min max idxmin idxmax
3 64.2 99.5 2022-01-03 13:21:02 2022-01-03 13:19:02
How to find the max, min value of ALL Dataframe [ not values by column neither rows ]
You can try:
max_val = df.max().max()
Getting the max value from a list of columns by their index in Pandas
You can find the 'date' columns using a list comprehension which will return the columns that contain /
. Then you can use max(axis=1)
to create the column which will show the highest value per row, of your date like columns:
date_cols = [c for c in list(df) if '/' in c]
df['max_per_row'] = df[date_cols].max(axis=1)
prints:
index ID Cost ... 08/01/2021 00:00 15/01/2021 00:00 max_per_row
0 0 1 4000 ... 50.55 60.99 60.99
1 0 1 500 ... 80.55 160.99 160.99
2 0 1 4000 ... 530.55 1660.99 1660.99
3 0 1 5000 ... 90.55 18860.99 18860.99
4 0 1 9000 ... 590.55 73760.99 73760.99
Pandas: sum DataFrame column for max value only
where
df.append(
df.where( # only look at values that are max for the row
df.eq( # compare max values to all values in row just
# in case there are more than 1
df.max(axis=1), # actually get max values
axis=0
)
).sum().rename('count')
)
a b c d
0 0.28 0.50 0.33 0.00
1 0.00 0.50 0.70 0.25
2 0.25 0.00 0.25 0.20
3 0.85 0.75 0.20 0.66
4 0.10 0.10 0.50 0.10
count 1.10 0.50 1.45 0.00
Related Topics
Finding the Two Closest Numbers in a List Using Sorting
How to Set the Default Python Path for Anaconda on Linux
Python Opencv - Add Alpha Channel to Rgb Image
Pandas Concat: Valueerror: Shape of Passed Values Is Blah, Indices Imply Blah2
Test If Dictionary Key Exists, Is Not None and Isn't Blank
How to Pass a Dictionary Object as Parameter for a Function in Python
Python - Converting a List of 2 Digit String Numbers to a List of 2 Digit Integers
Beautifulsoup: Get the Contents of a Specific Table
How to Create a Common Function to Execute a Python Script in Jenkins
Python Strftime - Date Without Leading 0
Using Regex to Find All Phrases That Are Completely Capitalized
Count Duplicates Between 2 Lists
How to Periodically Execute a Function With Asyncio
How to Get a Value from a Cell of a Dataframe