Drop unordered duplicates across separate columns

Question

I am trying to return a df where duplicate values have been removed. I have tried to use drop.duplicates() but the values in the columns which have been subset aren't ordered. As in, the values are duplicates but they aren't in the same order.

For instance, using the df below, if I try to remove duplicate values from Item_X and Item_Y it will return the same df. Where the intended output will remove the second row.

import pandas as pd

d = ({
    'Item_X' : ['Foo','Bar','Bot','Bot','Bar','Foo'],                 
    'Item_Y' : ['Bar','Foo','Foo','Bot','Bar','Foo'],                 
    'Value' : [1,2,3,4,5,6],                      
     })

df = pd.DataFrame(data = d)

df.drop_duplicates(subset=['Item_X','Item_Y'])

Expected Result:

  Item_X Item_Y  Value
0    Foo    Bar      1
2    Bot    Foo      3
3    Bot    Bot      4
4    Bar    Bar      5
5    Foo    Foo      6

Actual Output (Incorrect):

  Item_X Item_Y  Value
0    Foo    Bar      1
1    Bar    Foo      2
2    Bot    Foo      3
3    Bot    Bot      4
4    Bar    Bar      5
5    Foo    Foo      6

What would be the most efficient way to tackle this problem?

cs95 · Accepted Answer · 2019-05-28 02:47:52Z

4

You'll need to sort the columns along the horizontal axis, then get a mask to subset the original frame. Here's how you can use np.sort and df.duplicated to do that:

df[~pd.DataFrame(np.sort(df2[['Item_X', 'Item_Y']], axis=1)).duplicated()]

  Item_X Item_Y  Value
0    Foo    Bar      1
2    Bot    Foo      3
3    Bot    Bot      4
4    Bar    Bar      5
5    Foo    Foo      6

answered May 28, 2019 at 2:47

cs95

406k106 gold badges744 silver badges797 bronze badges

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

2 Comments

anky Over a year ago

We are on the same track here. :D +1

cs95 Over a year ago

@anky_91 Yup, the answer is identical (!) except I specify axis=1 so it is obvious what it is doing.

anky · Accepted Answer · 2019-05-28 02:49:47Z

3

IIUC, use:

m=pd.DataFrame(np.sort(df[['Item_X','Item_Y']])).duplicated()
df[~m]

  Item_X Item_Y  Value
0    Foo    Bar      1
2    Bot    Foo      3
3    Bot    Bot      4
4    Bar    Bar      5
5    Foo    Foo      6

answered May 28, 2019 at 2:49

anky

75.3k11 gold badges46 silver badges76 bronze badges

Collectives™ on Stack Overflow

Drop unordered duplicates across separate columns

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related