使用 groupby 获取组中具有最大值的行 [英] Get the row(s) which have the max value in groups using groupby
问题描述
在按 ['Sp','Mt']
列分组后,如何在 Pandas 数据框中查找具有 count
列最大值的所有行?
How do I find all rows in a pandas data frame which have the max value for count
column, after grouping by ['Sp','Mt']
columns?
示例 1: 以下数据帧,我按 ['Sp','Mt']
分组:
Example 1: the following dataFrame, which I group by ['Sp','Mt']
:
Sp Mt Value count
0 MM1 S1 a **3**
1 MM1 S1 n 2
2 MM1 S3 cb **5**
3 MM2 S3 mk **8**
4 MM2 S4 bg **10**
5 MM2 S4 dgd 1
6 MM4 S2 rd 2
7 MM4 S2 cb 2
8 MM4 S2 uyi **7**
预期输出:获取组间计数最大的结果行,如:
Expected output: get the result rows whose count is max between the groups, like:
0 MM1 S1 a **3**
2 MM1 S3 cb **5**
3 MM2 S3 mk **8**
4 MM2 S4 bg **10**
8 MM4 S2 uyi **7**
示例 2: 这个数据框,我按 ['Sp','Mt']
分组:
Example 2: this dataframe, which I group by ['Sp','Mt']
:
Sp Mt Value count
4 MM2 S4 bg 10
5 MM2 S4 dgd 1
6 MM4 S2 rd 2
7 MM4 S2 cb 8
8 MM4 S2 uyi 8
对于上面的例子,我想在每个组中获取所有 count
等于 max 的行,例如:
For the above example, I want to get all the rows where count
equals max, in each group e.g :
MM2 S4 bg 10
MM4 S2 cb 8
MM4 S2 uyi 8
推荐答案
In [1]: df
Out[1]:
Sp Mt Value count
0 MM1 S1 a 3
1 MM1 S1 n 2
2 MM1 S3 cb 5
3 MM2 S3 mk 8
4 MM2 S4 bg 10
5 MM2 S4 dgd 1
6 MM4 S2 rd 2
7 MM4 S2 cb 2
8 MM4 S2 uyi 7
In [2]: df.groupby(['Mt'], sort=False)['count'].max()
Out[2]:
Mt
S1 3
S3 8
S4 10
S2 7
Name: count
要获取原始 DF 的索引,您可以执行以下操作:
To get the indices of the original DF you can do:
In [3]: idx = df.groupby(['Mt'])['count'].transform(max) == df['count']
In [4]: df[idx]
Out[4]:
Sp Mt Value count
0 MM1 S1 a 3
3 MM2 S3 mk 8
4 MM2 S4 bg 10
8 MM4 S2 uyi 7
请注意,如果每个组有多个最大值,则将全部返回.
Note that if you have multiple max values per group, all will be returned.
更新
万一这是 OP 所要求的:
On a hail mary chance that this is what the OP is requesting:
In [5]: df['count_max'] = df.groupby(['Mt'])['count'].transform(max)
In [6]: df
Out[6]:
Sp Mt Value count count_max
0 MM1 S1 a 3 3
1 MM1 S1 n 2 3
2 MM1 S3 cb 5 8
3 MM2 S3 mk 8 8
4 MM2 S4 bg 10 10
5 MM2 S4 dgd 1 10
6 MM4 S2 rd 2 7
7 MM4 S2 cb 2 7
8 MM4 S2 uyi 7 7
这篇关于使用 groupby 获取组中具有最大值的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!