I have a dataframe with repeat values in column A. I want to drop duplicates, keeping the row with the highest value in column B.
So this:
A B 1 10 1 20 2 30 2 40 3 10
Should turn into this:
A B 1 20 2 40 3 10
I’m guessing there’s probably an easy way to do this—maybe as easy as sorting the DataFrame before dropping duplicates—but I don’t know groupby’s internal logic well enough to figure it out. Any suggestions?
Advertisement
Answer
This takes the last. Not the maximum though:
In [10]: df.drop_duplicates(subset='A', keep="last") Out[10]: A B 1 1 20 3 2 40 4 3 10
You can do also something like:
In [12]: df.groupby('A', group_keys=False).apply(lambda x: x.loc[x.B.idxmax()]) Out[12]: A B A 1 1 20 2 2 40 3 3 10