Pandas merge disregarding common items

Question

Good day,

I wish to do pandas merge on in SQL fashion where I get items from set A which are not present in set B. How to do this with Pandas?

select * from a 
  left join 
 b
 on a.key1 = b.key1 and a.key2= b.key2
 where b.key1 is NULL or b.key2 is NULL

Closest what I've found is another SO question, yet feels 'unPandas' and gets quite lengthy with multiple joining keys.

My best 'work around': Inner join on A and B and then drop common elements from A. Something akin to:

 common = pd.merge(a, b, how='inner', left_on='key', right_on='key')
 new_a = a[~a['key']isin(common['key']]

So any neater way to accomplish this?

CAPSLOCK · Answer 1 · 2019-11-04T09:57:05.650

0

You want to merge doing a left excluding join.

Have a look at this, the image above is taken from this answer.

(a.merge(b, on='key', how='left', indicator=True)
     .query('_merge == "left_only"')
     .drop('_merge', 1))

edited Nov 04 '19 at 09:57

answered Nov 04 '19 at 09:26

CAPSLOCK

Thank you. I thought outer gets both A and B disregarding common, but I wish to have only A set. Or am I missing something? – pinegulf Nov 04 '19 at 09:29
Yes, sorry, mindfart. I'll update the answer in a sec =) – CAPSLOCK Nov 04 '19 at 09:31
Anyway, I strongly suggest again to have a look at the posted link. It is an extremely well written tutorial on merging in pandas – CAPSLOCK Nov 04 '19 at 09:33

1 Answers1