Pandas find duplicates with reversed values between columns

Question

What is the fastest way to find duplicates where value from Column A have been reversed with value from Column B?

For example, if I have a DataFrame with :

     Column A           Column B
0           C                  P
1           D                  C
2           L                  G
3           A                  D
4           B                  P
5           B                  G
6           P                  B
7           J                  T
8           P                  C
9           J                  T

The result will be :

     Column A           Column B
0           C                  P
8           P                  C
4           B                  P
6           P                  B

I tried :

df1 = df
df2 = df
for i in df2.index:
     res = df1[(df1['Column A'] == df2['Column A'][i]) & (df1['Column B'] == df2['Column B'][i])]

But this is very slow and it iterates over the same values...

What have you tried so far?

DirtyBit
– DirtyBit

2019-01-22 14:00:48 +00:00
Commented Jan 22, 2019 at 14:00 — DirtyBit
– DirtyBit, Commented Jan 22, 2019 at 14:00
I edited for what I tried

ldoe
– ldoe

2019-01-22 14:03:51 +00:00
Commented Jan 22, 2019 at 14:03 — ldoe
– ldoe, Commented Jan 22, 2019 at 14:03

jezrael · Accepted Answer · 2019-01-22 14:12:53Z

5

Use merge with renamed columns DataFrame:

d = {'Column A':'Column B','Column B':'Column A'}
df = df.merge(df.rename(columns=d))
print (df)
  Column A Column B
0        C        P
1        B        P
2        P        B
3        P        C

answered Jan 22, 2019 at 14:12

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

Sumanth · Accepted Answer · 2019-01-22 14:06:17Z

0

You could try using reindex for inversion.

column_Names=["B","A"]
df=df.reindex(columns=column_Names)

Or you could just do this:

col_list = list(df)   # get a list of the columns
col_list[0], col_list[1] = col_list[1], col_list[0]
df.columns = col_list   # assign back

answered Jan 22, 2019 at 14:06

Sumanth

5075 silver badges13 bronze badges

2 Comments

ldoe Over a year ago

Thanks but this is not really what I'm after. This will indeed reverse the columns but I would like to know if there is duplicates in my df where the values between two columns have been reversed.

ldoe Over a year ago

Thanks to you and @jezrael that lead me on the way to the solution. Just reverse the columns and merge ! Thanks.

Collectives™ on Stack Overflow

Pandas find duplicates with reversed values between columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related