Python 使用 groupby 获取组中具有最大计数的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15705630/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the Row(s) which have the max count in groups using groupby
提问by jojo12
How do I find all rows in a pandas dataframe which have the max value for countcolumn, after grouping by ['Sp','Mt']columns?
count在按['Sp','Mt']列分组后，如何在 Pandas 数据框中找到具有列最大值的所有行？
Example 1:the following dataFrame, which I group by ['Sp','Mt']:
示例 1：以下数据帧，我将其分组['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**
1 3 MM2 S3 mk **8**
4 MM2 S4 bg **10**
8 MM4 S2 uyi **7**
Example 2:this dataframe, which I group by ['Sp','Mt']:
示例 2：此数据框，我将其分组为['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
For the above example, I want to get allthe rows where countequals max, in each group e.g :
对于上面的示例，我想获取每个组中等于 max 的所有行，count例如：
MM2 S4 bg 10
MM4 S2 cb 8
MM4 S2 uyi 8
采纳答案by Zelazny7
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
To get the indices of the original DF you can do:
要获取原始 DF 的索引，您可以执行以下操作：
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.
请注意，如果每个组有多个最大值，则将全部返回。
Update
更新
On a hail mary chance that this is what the OP is requesting:
万一这是 OP 所要求的：
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
回答by landewednack
Having tried the solution suggested by Zelazny on a relatively large DataFrame (~400k rows) I found it to be very slow. Here is an alternative that I found to run orders of magnitude faster on my data set.
在相对较大的 DataFrame（~400k 行）上尝试了 Zelazny 建议的解决方案后，我发现它非常慢。这是我发现在我的数据集上运行速度快几个数量级的替代方法。
df = pd.DataFrame({
'sp' : ['MM1', 'MM1', 'MM1', 'MM2', 'MM2', 'MM2', 'MM4', 'MM4', 'MM4'],
'mt' : ['S1', 'S1', 'S3', 'S3', 'S4', 'S4', 'S2', 'S2', 'S2'],
'val' : ['a', 'n', 'cb', 'mk', 'bg', 'dgb', 'rd', 'cb', 'uyi'],
'count' : [3,2,5,8,10,1,2,2,7]
})
df_grouped = df.groupby(['sp', 'mt']).agg({'count':'max'})
df_grouped = df_grouped.reset_index()
df_grouped = df_grouped.rename(columns={'count':'count_max'})
df = pd.merge(df, df_grouped, how='left', on=['sp', 'mt'])
df = df[df['count'] == df['count_max']]
回答by PAC
For me, the easiest solution would be keep value when count is equal to the maximum. Therefore, the following one line command is enough :
对我来说，最简单的解决方案是当计数等于最大值时保持值。因此，以下一行命令就足够了：
df[df['count'] == df.groupby(['Mt'])['count'].transform(max)]
回答by Rani
You can sort the dataFrame by count and then remove duplicates. I think it's easier:
您可以按计数对数据帧进行排序，然后删除重复项。我认为这更容易：
df.sort_values('count', ascending=False).drop_duplicates(['Sp','Mt'])
回答by Surya
Easy solution would be to apply : idxmax() function to get indices of rows with max values.This would filter out all the rows with max value in the group.
简单的解决方案是应用 : idxmax() 函数来获取具有最大值的行的索引。这将过滤掉组中具有最大值的所有行。
In [365]: import pandas as pd
In [366]: df = pd.DataFrame({
'sp' : ['MM1', 'MM1', 'MM1', 'MM2', 'MM2', 'MM2', 'MM4', 'MM4','MM4'],
'mt' : ['S1', 'S1', 'S3', 'S3', 'S4', 'S4', 'S2', 'S2', 'S2'],
'val' : ['a', 'n', 'cb', 'mk', 'bg', 'dgb', 'rd', 'cb', 'uyi'],
'count' : [3,2,5,8,10,1,2,2,7]
})
In [367]: df
Out[367]:
count mt sp val
0 3 S1 MM1 a
1 2 S1 MM1 n
2 5 S3 MM1 cb
3 8 S3 MM2 mk
4 10 S4 MM2 bg
5 1 S4 MM2 dgb
6 2 S2 MM4 rd
7 2 S2 MM4 cb
8 7 S2 MM4 uyi
### Apply idxmax() and use .loc() on dataframe to filter the rows with max values:
In [368]: df.loc[df.groupby(["sp", "mt"])["count"].idxmax()]
Out[368]:
count mt sp val
0 3 S1 MM1 a
2 5 S3 MM1 cb
3 8 S3 MM2 mk
4 10 S4 MM2 bg
8 7 S2 MM4 uyi
### Just to show what values are returned by .idxmax() above:
In [369]: df.groupby(["sp", "mt"])["count"].idxmax().values
Out[369]: array([0, 2, 3, 4, 8])
回答by blueear
Use groupbyand idxmaxmethods:
用途groupby及idxmax方法：
transfer col
datetodatetime:df['date']=pd.to_datetime(df['date'])get the index of
maxof columndate, aftergroupyby ad_id:idx=df.groupby(by='ad_id')['date'].idxmax()get the wanted data:
df_max=df.loc[idx,]
将 col 转移
date到datetime：df['date']=pd.to_datetime(df['date'])获取
max列的索引date，之后groupyby ad_id：idx=df.groupby(by='ad_id')['date'].idxmax()获取想要的数据：
df_max=df.loc[idx,]
Out[54]:
出[54]：
ad_id price date
7 22 2 2018-06-11
6 23 2 2018-06-22
2 24 2 2018-06-30
3 28 5 2018-06-22
回答by George Liu
df = pd.DataFrame({
'sp' : ['MM1', 'MM1', 'MM1', 'MM2', 'MM2', 'MM2', 'MM4', 'MM4','MM4'],
'mt' : ['S1', 'S1', 'S3', 'S3', 'S4', 'S4', 'S2', 'S2', 'S2'],
'val' : ['a', 'n', 'cb', 'mk', 'bg', 'dgb', 'rd', 'cb', 'uyi'],
'count' : [3,2,5,8,10,1,2,2,7]
})
df.groupby(['sp', 'mt']).apply(lambda grp: grp.nlargest(1, 'count'))
回答by YOBEN_S
You may not need to do with group by , using sort_values+ drop_duplicates
您可能不需要使用 group by ，使用sort_values+drop_duplicates
df.sort_values('count').drop_duplicates(['Sp','Mt'],keep='last')
Out[190]:
Sp Mt Value count
0 MM1 S1 a 3
2 MM1 S3 cb 5
8 MM4 S2 uyi 7
3 MM2 S3 mk 8
4 MM2 S4 bg 10
Also almost same logic by using tail
通过使用也几乎相同的逻辑 tail
df.sort_values('count').groupby(['Sp', 'Mt']).tail(1)
Out[52]:
Sp Mt Value count
0 MM1 S1 a 3
2 MM1 S3 cb 5
8 MM4 S2 uyi 7
3 MM2 S3 mk 8
4 MM2 S4 bg 10
回答by joh-mue
I've been using this functional style for many group operations:
我一直在将这种功能风格用于许多组操作：
df = pd.DataFrame({
'Sp' : ['MM1', 'MM1', 'MM1', 'MM2', 'MM2', 'MM2', 'MM4', 'MM4', 'MM4'],
'Mt' : ['S1', 'S1', 'S3', 'S3', 'S4', 'S4', 'S2', 'S2', 'S2'],
'Val' : ['a', 'n', 'cb', 'mk', 'bg', 'dgb', 'rd', 'cb', 'uyi'],
'Count' : [3,2,5,8,10,1,2,2,7]
})
df.groupby('Mt')\
.apply(lambda group: group[group.Count == group.Count.max()])\
.reset_index(drop=True)
sp mt val count
0 MM1 S1 a 3
1 MM4 S2 uyi 7
2 MM2 S3 mk 8
3 MM2 S4 bg 10
.reset_index(drop=True)gets you back to the original index by dropping the group-index.
.reset_index(drop=True)通过删除组索引让您回到原始索引。
回答by Surya
Realizing that "applying" "nlargest"to groupby objectworks just as fine:
意识到“应用”“nlargest”到groupby 对象同样有效：
Additional advantage - also can fetchtop n valuesif required:
额外的优势 -如果需要，还可以获取前 n 个值：
In [85]: import pandas as pd
In [86]: df = pd.DataFrame({
...: 'sp' : ['MM1', 'MM1', 'MM1', 'MM2', 'MM2', 'MM2', 'MM4', 'MM4','MM4'],
...: 'mt' : ['S1', 'S1', 'S3', 'S3', 'S4', 'S4', 'S2', 'S2', 'S2'],
...: 'val' : ['a', 'n', 'cb', 'mk', 'bg', 'dgb', 'rd', 'cb', 'uyi'],
...: 'count' : [3,2,5,8,10,1,2,2,7]
...: })
## Apply nlargest(1) to find the max val df, and nlargest(n) gives top n values for df:
In [87]: df.groupby(["sp", "mt"]).apply(lambda x: x.nlargest(1, "count")).reset_index(drop=True)
Out[87]:
count mt sp val
0 3 S1 MM1 a
1 5 S3 MM1 cb
2 8 S3 MM2 mk
3 10 S4 MM2 bg
4 7 S2 MM4 uyi
