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
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
I cannot figure out how to do that. Should I use drop_duplicates()
, something else?
This question is related to
python
pandas
dataframe
duplicates
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 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')
Source: Stackoverflow.com