根据两列 A、 B 从数据框中删除重复项，在另一列 C 中保持行的最大值

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

A B C
1 2 1
1 2 4
2 7 1
3 4 0
3 4 8

我想删除保持 C 列中最大值的行的重复项。这将导致:

A B C
1 2 4
2 7 1
3 4 8

我不知道如何做到这一点。我应该使用 drop_duplicates()，其他东西？

170632 次浏览
小开

I think groupby should work.

df.groupby(['A', 'B']).max()['C']

If you need a dataframe back you can chain the reset index call.

df.groupby(['A', 'B']).max()['C'].reset_index()
小开
最佳答案

You can do it using group by:

c_maxes = df.groupby(['A', 'B']).C.transform(max)
df = df.loc[df.C == 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.

Another approach using drop_duplicates would be 

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.

EDIT: From pandas 0.18 up the second solution would be 

df.sort_values('C').drop_duplicates(subset=['A', 'B'], keep='last')

or, alternatively,

df.sort_values('C', ascending=False).drop_duplicates(subset=['A', 'B'])

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
小开

You can do it with drop_duplicates as you wanted

# initialisation
d = pd.DataFrame({'A' : [1,1,2,3,3], 'B' : [2,2,7,4,4],  'C' : [1,4,1,0,8]})


d = d.sort_values("C", ascending=False)
d = d.drop_duplicates(["A","B"])

If it's important to get the same order 

d = d.sort_index()
小开

You can do this simply by using pandas drop duplicates function

df.drop_duplicates(['A','B'],keep= 'last')

提交答案