Pandas merge removing duplicate rows

Question

i have a pandas df:

df = pd.DataFrame({'id':[1,1,2,2,3],
                   'type':['a','b','c','d','e'],
                   'value':[100,200,300,400,500]})

print(df)  

id  value type
1   100    a
1   200    b
2   300    c
2   400    d
3   500    e

I'am merging the same dataframe to get combinations of

df2 = pd.merge(df, df,on=['id'])

print(df2)

id  type_x  value_x type_y  value_y
1   a       100        a    100
1   a       100        b    200
1   b       200        a    100
1   b       200        b    200
2   c       300        c    300
2   c       300        d    400
2   d       400        c    300
2   d       400        d    400
3   e       500        e    500

but i don't want columns with value_x = value_y

e.g:

id  type_x  value_x type_y  value_y
1   a       100        a    100

i can select the columns after merging

df2 = df2[df2.value_x != df2.value_y]

but i dont want to do it like this,

is there any other way, by which i can remove these while merging itself?

my final output (desired):

id  type_x  value_x type_y  value_y
1   a       100      b      200
1   b       200      a      100
2   c       300      d      400
2   d       400      c      300

Are you building the DataFrame from scratch or is this just an example and you have to work with an existing frame? — Jon Clements
– Jon Clements, Commented Aug 9, 2017 at 12:58
@JonClements This is just an example, i've a similar dataframe with more columns. — Shubham R
– Shubham R, Commented Aug 10, 2017 at 5:14

Scott Boston · Accepted Answer · 2017-08-09 12:45:30Z

3

You can do it all in one statement, however, it is still much like you are doing, using query.

df2 = pd.merge(df, df,on=['id']).query('value_x != value_y')

Output:

   id type_x  value_x type_y  value_y
1   1      a      100      b      200
2   1      b      200      a      100
5   2      c      300      d      400
6   2      d      400      c      300

answered Aug 9, 2017 at 12:45

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

Collectives™ on Stack Overflow

Pandas merge removing duplicate rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related