pandas match/compare multiple columns

Question

I want to compare two pandas-tables by two columns. Consider following example: I would like to get a boolean Series which indicates True ONLY if BOTH conditions match. I tried is.in() without much success. I could either loop over "One" or combine (add) both columns together in both dataframes, but is there some built-in functionality of pandas that would allow for such (nested) comparisons involving two pairs of columns?

tab1 = pd.DataFrame({"One": [1, 1, 2, 3],
                     "Two": ["A", "B", "C", "C"]})

tab2 = pd.DataFrame({"One": [1, 2, 2, 3, 3],
                     "Two": ["A", "A", "B",  "A","C"]})

# Desired Result (New column in tab1): [True, False, False, True]

EDIT: The second table is more like a lookup table. They do not have the same shape. Should have made that clearer.

halfer · Accepted Answer · 2020-10-22 22:08:45Z

2

tab1.eq(tab2).all(1)
0     True
1    False
2    False
3     True
dtype: bool

Update

tab1.merge(tab2,indicator=True,how='left')['_merge'].eq('both')
0     True
1    False
2    False
3     True
Name: _merge, dtype: bool

edited Oct 22, 2020 at 22:08

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Apr 27, 2020 at 21:05

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

Krisselack Over a year ago

I was not very clear in my example. The second dataframe is acutally a lookup df with a different shape.

Krisselack Over a year ago

Yes, this looks exactly like what I wanted. Coming from R, the pandas world is still a bit exotic for me. Thank you!

scnerd · Accepted Answer · 2020-04-27 21:06:24Z

1

When you do columnar comparison in Pandas, you get a column/vector of boolean values. You can do element-wise boolean operations between these results using Python's bit-wise operations (so, & instead of and and | instead of or). You just need to be careful about order of operations, since bitwise comparisons have higher precedence than comparisons. So,

(tab1.One == tab2.One) & (tab1.Two == tab2.Two)

answered Apr 27, 2020 at 21:06

scnerd

6,1452 gold badges24 silver badges38 bronze badges

2 Comments

Krisselack Over a year ago

I did not describe the problem correctly. The 2 dataframes do not have the same shape.

scnerd Over a year ago

Well that's really important. You'll need some way to align the rows, then, either using a key of some sort in another column or some other lookup/logic. You don't have a unique "primary key" column in the tables you show, so you'll need to define how the rows should align or else this is an undefined problem.

Collectives™ on Stack Overflow

pandas match/compare multiple columns

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related