Drop duplicates of one column based on value in another column, Python, Pandas

Question

I have a dataframe like this:

Date                PlumeO      Distance
2014-08-13 13:48:00  754.447905 5.844577 
2014-08-13 13:48:00  754.447905 6.888653
2014-08-13 13:48:00  754.447905 6.938860
2014-08-13 13:48:00  754.447905 6.977284
2014-08-13 13:48:00  754.447905 6.946430 
2014-08-13 13:48:00  754.447905 6.345506
2014-08-13 13:48:00  754.447905 6.133567
2014-08-13 13:48:00  754.447905 5.846046 
2014-08-13 16:59:00  754.447905 6.345506 
2014-08-13 16:59:00  754.447905 6.694847 
2014-08-13 16:59:00  754.447905 5.846046 
2014-08-13 16:59:00  754.447905 6.977284 
2014-08-13 16:59:00  754.447905 6.938860 
2014-08-13 16:59:00  754.447905 5.844577 
2014-08-13 16:59:00  754.447905 6.888653 
2014-08-13 16:59:00  754.447905 6.133567 
2014-08-13 16:59:00  754.447905 6.946430

I'm trying to keep the date with the smallest distance, so drop the duplicates dates and keep the with the smallest distance.

Is there a way to achieve this in pandas' df.drop_duplicates or am I stuck using if statements to find the smallest distance?

user2285236 · Accepted Answer · 2017-07-12 13:58:58Z

13

Sort by distances and drop by dates:

df.sort_values('Distance').drop_duplicates(subset='Date', keep='first')
Out: 
                   Date      PlumeO  Distance
0   2014-08-13 13:48:00  754.447905  5.844577
13  2014-08-13 16:59:00  754.447905  5.844577

answered Jul 12, 2017 at 13:58

user2285236

Sign up to request clarification or add additional context in comments.

1 Comment

piRSquared Over a year ago

Despite having to sort, this answer is also really fast (-:

piRSquared · Accepted Answer · 2017-07-12 14:07:39Z

7

The advantage of these approaches is that it does not require a sort.

Option 1
You can identify the index values for the minimum values with idxmin and you can use it within a groupby. Use these results to slice your dataframe.

df.loc[df.groupby('Date').Distance.idxmin()]

                   Date      PlumeO  Distance
0   2014-08-13 13:48:00  754.447905  5.844577
13  2014-08-13 16:59:00  754.447905  5.844577

Option 2
You can use pd.DataFrame.nsmallest to return the rows associated with the smallest distance.

df.groupby('Date', group_keys=False).apply(
    pd.DataFrame.nsmallest, n=1, columns='Distance'
)

                   Date      PlumeO  Distance
0   2014-08-13 13:48:00  754.447905  5.844577
13  2014-08-13 16:59:00  754.447905  5.844577

edited Jul 12, 2017 at 14:07

answered Jul 12, 2017 at 14:01

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Comments

Zach O · Accepted Answer · 2017-07-12 14:20:17Z

1

I would say sort the data first and then drop the duplicate dates:

stripped_data = df.sort_values('distance').drop_duplicates('date', keep='first')

answered Jul 12, 2017 at 14:20

Zach O

112 bronze badges

Collectives™ on Stack Overflow

Drop duplicates of one column based on value in another column, Python, Pandas

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related