Python(Pandas):基于两列删除重复项，并在另一列中保留具有最大值的行 [英] Python(pandas): removing duplicates based on two columns keeping row with max value in another column

问题描述

我有一个数据框，其中包含根据两列(A和B)重复的值:

I have a 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

这篇关于Python(Pandas):基于两列删除重复项，并在另一列中保留具有最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！