Find the column name which has the maximum value for each row
You can use idxmax
with axis=1
to find the column with the greatest value on each row:
>>> df.idxmax(axis=1)
0 Communications
1 Business
2 Communications
3 Communications
4 Business
dtype: object
To create the new column 'Max', use df['Max'] = df.idxmax(axis=1)
.
To find the row index at which the maximum value occurs in each column, use df.idxmax()
(or equivalently df.idxmax(axis=0)
).
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.
Get (row,col) indices of max value in dataframe
Use unstack() and extract the top MultiIndex as a tuple using idxmax()
import pandas as pd
data = [[5, 7, 10], [7, 20, 4,], [8, 1, 6,]]
cities = ['Boston', 'Phoenix', 'New York']
df = pd.DataFrame(data, columns=cities, index=cities)
print df.unstack().idxmax()
returns:
('Phoenix', 'Phoenix')
get row with max values in all columns in R
The easiest way to do it is to sort the d
column, and them remove duplicates in the A1
column:
df2 <- df[order(df$d,decreasing=T),]
df2[!duplicated(df2$A1),]
This does assume that there is a single unique maximum, you would lose some results if there were more than 1.
how to get all rows with max value of a variable
A reproducible data simulating your matrix:
set.seed(123)
x <- matrix(sample(1:9, 30, T), 10, 3)
row.names(x) <- 1:10
colnames(x) <- LETTERS[1:3]
# A B C
# 1 3 9 9
# 2 8 5 7
# 3 4 7 6
# ...
In matrix objects, you need to use a binary way to extract element such as data[a, b]
. Take the above data for example, x["C"]
will return NA
and x[, "C"]
will return all elements in column C. Therefore, the following two codes are going to generate different outputs.
subset(x, x["C"] == max(x["C"]))
# A B C (Empty)
subset(x, x[, "C"] == max(x[, "C"]))
# A B C
# 1 3 9 9
# 4 8 6 9
Get max value from dataframe with other information
You can use idxmax()
it will give you the index for maximum value.
index = data['VALUE'][data['PARAMETER'] == 'Param1'].idxmax()
Now you can use this index to get any columns or the entire row.
data.loc[index, 'Object']
Sample Run:
>>import pandas as pd
>>df = pd.DataFrame({'Object': ['A', 'B','C','D', 'E'], 'PARAMETER': ['Param1', 'Param2', 'Param3', 'Param2', 'Param1'], 'VALUE':[1, 2, 3, 4, 5]})
>>df
Object PARAMETER VALUE
0 A Param1 1
1 B Param2 2
2 C Param3 3
3 D Param2 4
4 E Param1 5
OUTPUT:
The masking:
>>df[df['PARAMETER'] == 'Param1']
Object PARAMETER VALUE
0 A Param1 1
4 E Param1 5
The idxmax()
:
>>df[df['PARAMETER'] == 'Param1']['VALUE'].idxmax()
4
From the masking, as you can notice, maximum index is 4, that's what idxmax()
yields. Now you can use this index to access any column like this:
>>index = df['VALUE'][df['PARAMETER'] == 'Param1'].idxmax()
>>df.loc[index, 'Object']
'E'
or the entire row:
>>df.loc[index]
Object E
PARAMETER Param1
VALUE 5
Name: 4, dtype: object
Find row-index of highest value in given column of dataframe
Try df['number'].argmax()
import pandas
import numpy as np
df = pandas.DataFrame(np.random.randn(10,3),columns=['Col1','Col2','Col3'])
print df
print df['Col1'].argmax()
output
Col1 Col2 Col3
0 0.583251 -0.014694 1.516529
1 0.274758 0.438513 0.994992
2 0.601611 1.753035 0.864451
3 -0.971775 -1.461290 0.121570
4 2.239460 -1.099298 -1.953045
5 2.314444 0.215336 0.470668
6 -0.138696 0.422923 -0.624436
7 0.602329 -0.015627 0.023715
8 0.594784 0.739058 1.094646
9 -0.104579 0.557339 1.977929
5
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()
Extract rows with maximum values in pandas dataframe
Close! Groupby the id, then use the value column; return the max for each group.
In [14]: df.groupby('id')['value'].max()
Out[14]:
id
a 0
b 1
c 2
Name: value, dtype: int64
Op wants to provide these locations back to the frame, just create a transform and assign.
In [17]: df['max'] = df.groupby('id')['value'].transform(lambda x: x.max())
In [18]: df
Out[18]:
id value max
0 a 0 0
1 b 1 1
2 b 1 1
3 c 0 2
4 c 2 2
5 c 1 2
How to extract a particular column in python dataframe with Max values
I think you need set_index
for index from column product name
and idxmax
for product name
by max value of column unit price
:
a = Stores.set_index('product name')['unit price'].idxmax()
Sample:
Stores = pd.DataFrame({'product name':['a','b','c'], 'unit price':[3,8,6]})
print (Stores)
product name unit price
0 a 3
1 b 8
2 c 6
a = Stores.set_index('product name')['unit price'].idxmax()
print (a)
b
Related Topics
R - Replace Specific Value Contents with Na
Plot Title at Bottom of Plot Using Ggplot2
Tidy Data.Frame with Repeated Column Names
Install.Packages R on Ubuntu 12.04 Downloads But Does Not Install Packages
How to Print Double Quotes (") in R
Data.Table := Assignments When Variable Has Same Name as a Column
Testing a Function That Uses Enquo() for a Null Parameter
R Shiny Error: Object of Type 'Closure' Is Not Subsettable
Month Language in the As.Date Function
How to Modify Unexported Object in a Package
Smart Way to Chain Ifelse Statements
Shortcut Using Lm() in R for Formula
How to Detect That a Vector Is Subset of Specific Vector
How to Apply Dplyr's Select(,Starts_With()) on Rows, Not Columns