在Pandas DataFrame中查找列的值最大的行

问题:在Pandas DataFrame中查找列的值最大的行

如何找到特定列的值最大的行

df.max() 会给我每列的最大值,我不知道如何获取对应的行。

How can I find the row for which the value of a specific column is maximal?

df.max() will give me the maximal value for each column, I don’t know how to get the corresponding row.


回答 0

使用熊猫idxmax功能。很简单:

>>> 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'].argmax()
3
>>> df['B'].argmax()
4
>>> df['C'].argmax()
1
  • 或者,您也可以使用numpy.argmax,例如numpy.argmax(df['A'])-它提供相同的功能,并且至少与idxmax粗略观察中的显示速度一样快。

  • idxmax() 返回索引标签,而不是整数。

    • 示例”:如果您将字符串值用作索引标签,例如行“ a”至“ e”,则可能要知道最大值出现在第4行(而不是“ d”行)。
    • 如果您希望该标签在其中的整数位置,则Index必须手动获取它(由于允许使用重复的行标签,因此可能很棘手)。

历史记录:

  • idxmax()以前在0.11之前被调用argmax()
  • argmax 在1.0.0之前弃用,并在1.0.0中完全删除
  • 早于Pandas 0.16,argmax曾经存在并执行相同的功能(尽管运行速度比慢idxmax)。
    • argmax函数返回最大元素的行位置的索引内的整数位置
    • 熊猫开始使用行标签代替整数索引。位置整数索引曾经很常见,比标签更常见,尤其是在重复行标签很常见的应用程序中。

例如,考虑以下DataFrame带有重复行标签的玩具:

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

因此,单单使用idxmax不足以达到此目的,而旧形式的argmax可以正确提供最大行的位置(在这种情况下为位置9)。

这恰恰是动态类型语言中那些容易发生错误的令人讨厌的行为之一,这种行为使这种事情非常不幸,值得一搏。如果您正在编写系统代码,而系统突然被用于某些在加入之前未正确清理的数据集,则很容易以重复的行标签结尾,尤其是字符串标签,例如金融资产的CUSIP或SEDOL标识符。您无法轻松地使用类型系统来帮助您,并且可能无法在索引中意外丢失数据而无法对索引实施唯一性。

因此,您只希望单元测试能够覆盖所有内容(它们没有,或者很可能没有人编写任何测试)-否则(很可能)您只需要等待,看看是否碰巧遇到了这个问题运行时错误,在这种情况下,你可能不得不去从你输出结果,碰你的头反对IPython的墙试图手动重现问题数据库中删除多个小时的工作价值,终于搞清楚,这是因为idxmax可以报告最大行的标签,然后感到失望的是,没有标准函数会自动为您获取最大行的位置,您自己编写一个有问题的实现,编辑代码,并祈祷您不再遇到问题。

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'].argmax()
3
>>> df['B'].argmax()
4
>>> df['C'].argmax()
1
  • Alternatively you could also use numpy.argmax, such as numpy.argmax(df['A']) — it provides the same thing, and appears at least as fast as idxmax 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 called argmax() prior to 0.11
  • argmax 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 than idxmax).
    • 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.


回答 1

您也可以尝试idxmax

In [5]: df = pandas.DataFrame(np.random.randn(10,3),columns=['A','B','C'])

In [6]: df
Out[6]: 
          A         B         C
0  2.001289  0.482561  1.579985
1 -0.991646 -0.387835  1.320236
2  0.143826 -1.096889  1.486508
3 -0.193056 -0.499020  1.536540
4 -2.083647 -3.074591  0.175772
5 -0.186138 -1.949731  0.287432
6 -0.480790 -1.771560 -0.930234
7  0.227383 -0.278253  2.102004
8 -0.002592  1.434192 -1.624915
9  0.404911 -2.167599 -0.452900

In [7]: df.idxmax()
Out[7]: 
A    0
B    8
C    7

例如

In [8]: df.loc[df['A'].idxmax()]
Out[8]: 
A    2.001289
B    0.482561
C    1.579985

You might also try idxmax:

In [5]: df = pandas.DataFrame(np.random.randn(10,3),columns=['A','B','C'])

In [6]: df
Out[6]: 
          A         B         C
0  2.001289  0.482561  1.579985
1 -0.991646 -0.387835  1.320236
2  0.143826 -1.096889  1.486508
3 -0.193056 -0.499020  1.536540
4 -2.083647 -3.074591  0.175772
5 -0.186138 -1.949731  0.287432
6 -0.480790 -1.771560 -0.930234
7  0.227383 -0.278253  2.102004
8 -0.002592  1.434192 -1.624915
9  0.404911 -2.167599 -0.452900

In [7]: df.idxmax()
Out[7]: 
A    0
B    8
C    7

e.g.

In [8]: df.loc[df['A'].idxmax()]
Out[8]: 
A    2.001289
B    0.482561
C    1.579985

回答 2

如果有多行取最大值,上述两个答案都只会返回一个索引。如果要所有行,似乎没有功能。但这并不难。以下是系列的示例;对于DataFrame也可以这样做:

In [1]: from pandas import Series, DataFrame

In [2]: s=Series([2,4,4,3],index=['a','b','c','d'])

In [3]: s.idxmax()
Out[3]: 'b'

In [4]: s[s==s.max()]
Out[4]: 
b    4
c    4
dtype: int64

Both above answers would only return one index if there are multiple rows that take the maximum value. If you want all the rows, there does not seem to have a function. But it is not hard to do. Below is an example for Series; the same can be done for DataFrame:

In [1]: from pandas import Series, DataFrame

In [2]: s=Series([2,4,4,3],index=['a','b','c','d'])

In [3]: s.idxmax()
Out[3]: 'b'

In [4]: s[s==s.max()]
Out[4]: 
b    4
c    4
dtype: int64

回答 3

df.iloc[df['columnX'].argmax()]

argmax()将提供对应于columnX最大值的索引。iloc可用于获取此索引的DataFrame df行。

df.iloc[df['columnX'].argmax()]

argmax() would provide the index corresponding to the max value for the columnX. iloc can be used to get the row of the DataFrame df for this index.


回答 4

直接的“ .argmax()”解决方案对我不起作用。

@ely提供的上一个示例

>>> 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'].argmax()
3
>>> df['B'].argmax()
4
>>> df['C'].argmax()
1

返回以下消息:

FutureWarning: 'argmax' is deprecated, use 'idxmax' instead. The behavior of 'argmax' 
will be corrected to return the positional maximum in the future.
Use 'series.values.argmax' to get the position of the maximum now.

所以我的解决方案是:

df['A'].values.argmax()

The direct “.argmax()” solution does not work for me.

The previous example provided by @ely

>>> 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'].argmax()
3
>>> df['B'].argmax()
4
>>> df['C'].argmax()
1

returns the following message :

FutureWarning: 'argmax' is deprecated, use 'idxmax' instead. The behavior of 'argmax' 
will be corrected to return the positional maximum in the future.
Use 'series.values.argmax' to get the position of the maximum now.

So that my solution is :

df['A'].values.argmax()

回答 5

mx.iloc[0].idxmax()

这段代码将为您提供如何从数据帧中的一行中找到最大值的方法,这里mx是数据帧,它iloc[0]指示第0个索引。

mx.iloc[0].idxmax()

This one line of code will give you how to find the maximum value from a row in dataframe, here mx is the dataframe and iloc[0] indicates the 0th index.


回答 6

idmax数据帧的返回与最大值和行为的行的标记指数argmax取决于版本pandas(现在它返回警告)。如果要使用位置索引,可以执行以下操作:

max_row = df['A'].values.argmax()

要么

import numpy as np
max_row = np.argmax(df['A'].values)

请注意,如果您使用的np.argmax(df['A'])行为与相同df['A'].argmax()

The idmax of the DataFrame returns the label index of the row with the maximum value and the behavior of argmax depends on version of pandas (right now it returns a warning). If you want to use the positional index, you can do the following:

max_row = df['A'].values.argmax()

or

import numpy as np
max_row = np.argmax(df['A'].values)

Note that if you use np.argmax(df['A']) behaves the same as df['A'].argmax().