根据两列A,B从数据框中删除重复项,并在另一列C中保留具有最大值的行 [英] Remove duplicates from dataframe, based on two columns A,B, keeping row with max value in another column C
问题描述
我有一个pandas数据框,其中包含根据两列(A和B)重复的值:
I have a pandas dataframe which contains duplicates values according to two columns (A and B):
A B C
1 2 1
1 2 4
2 7 1
3 4 0
3 4 8
我想删除重复项,使行在C列中保持最大值.这将导致:
I want to remove duplicates keeping the row with max value in column C. This would lead to:
A B C
1 2 4
2 7 1
3 4 8
我不知道该怎么做.我应该使用drop_duplicates()
,还是其他?
I cannot figure out how to do that. Should I use drop_duplicates()
, something else?
推荐答案
您可以使用分组依据:
c_maxes = df.groupby(['A', 'B']).C.transform(max)
df = df.loc[df.C == c_maxes]
c_maxes
是每个组中C
的最大值的Series
,但长度与df
相同且具有相同的索引.如果您未使用.transform
,则打印c_maxes
可能是一个不错的主意,以了解其工作原理.
c_maxes
is a Series
of the maximum values of C
in each group but which is of the same length and with the same index as df
. If you haven't used .transform
then printing c_maxes
might be a good idea to see how it works.
使用drop_duplicates
的另一种方法是
df.sort('C').drop_duplicates(subset=['A', 'B'], take_last=True)
不确定哪种方法更有效,但我猜第一种方法不涉及排序.
Not sure which is more efficient but I guess the first approach as it doesn't involve sorting.
从pandas 0.18
开始,第二个解决方案将是
From pandas 0.18
up the second solution would be
df.sort_values('C').drop_duplicates(subset=['A', 'B'], keep='last')
或者,或者,
df.sort_values('C', ascending=False).drop_duplicates(subset=['A', 'B'])
在任何情况下,groupby
解决方案的性能似乎都明显更高:
In any case, the groupby
solution seems to be significantly more performing:
%timeit -n 10 df.loc[df.groupby(['A', 'B']).C.max == df.C]
10 loops, best of 3: 25.7 ms per loop
%timeit -n 10 df.sort_values('C').drop_duplicates(subset=['A', 'B'], keep='last')
10 loops, best of 3: 101 ms per loop
这篇关于根据两列A,B从数据框中删除重复项,并在另一列C中保留具有最大值的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!