Compare multiple columns of two data frames using pandas

Question

I have two data frames; df1 has Id and sendDate and df2 has Id and actDate. The two df's are not the same shape - df2 is a lookup table. There may be multiple instances of Id.

ex.

df1 = pd.DataFrame({"Id": [1, 1, 2, 3, 2],
                     "sendDate": ["2019-09-24", "2020-09-11", "2018-01-06", "2018-01-06", "2019-09-24"]})

df2 = pd.DataFrame({"Id": [1, 2, 2],
                     "actDate": ["2019-09-24", "2019-09-24", "2020-09-11"]})

I want to add a boolean True/False in df1 to find when df1.Id == df2.Id and df1.sendDate == df2.actDate.

Expected output would add a column to df1:

df1 = pd.DataFrame({"Id": [1, 1, 2, 3, 2],
                         "sendDate": ["2019-09-24", "2020-09-11", "2018-01-06", "2018-01-06", "2019-09-24"],
"Match?": [True, False, False, False, True]})

I'm new to python from R, so please let me know what other info you may need.

Can you include what your expected output would be?

Henry Ecker
– Henry Ecker ♦

2021-04-29 01:08:35 +00:00
Commented Apr 29, 2021 at 1:08 — Henry Ecker
– Henry Ecker ♦, Commented Apr 29, 2021 at 1:08

Henry Ecker · Accepted Answer · 2021-04-29 01:16:53Z

1

Use isin and boolean indexing

import pandas as pd

df1 = pd.DataFrame({"Id": [1, 1, 2, 3, 2],
                    "sendDate": ["2019-09-24", "2020-09-11",
                                 "2018-01-06", "2018-01-06",
                                 "2019-09-24"]})

df2 = pd.DataFrame({"Id": [1, 2, 2],
                    "actDate": ["2019-09-24", "2019-09-24", "2020-09-11"]})

df1['Match'] = (df1['Id'].isin(df2['Id'])) & (df1['sendDate'].isin(df2['actDate']))
print(df1)

Output:

   Id    sendDate  Match
0   1  2019-09-24   True
1   1  2020-09-11   True
2   2  2018-01-06  False
3   3  2018-01-06  False
4   2  2019-09-24   True

answered Apr 29, 2021 at 1:16

Henry Ecker♦

35.8k19 gold badges48 silver badges67 bronze badges

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

Comments

Randy · Accepted Answer · 2021-04-29 01:43:23Z

1

The .isin() approaches will find values where the ID and date entries don't necessarily appear together (e.g. Id=1 and date=2020-09-11 in your example). You can check for both by doing a .merge() and checking when df2's date field is not null:

df1['match'] = df1.merge(df2, how='left', left_on=['Id', 'sendDate'], right_on=['Id', 'actDate'])['actDate'].notnull()

answered Apr 29, 2021 at 1:43

Randy

14.9k3 gold badges40 silver badges44 bronze badges

Comments

Nk03 · Accepted Answer · 2021-04-29 03:27:51Z

1

A vectorized approach via numpy -

import numpy as np
df1['Match'] = np.where((df1['Id'].isin(df2['Id'])) & (df1['sendDate'].isin(df2['actDate'])),True,False)

edited Apr 29, 2021 at 3:27

answered Apr 29, 2021 at 2:18

Nk03

15k2 gold badges11 silver badges24 bronze badges

Comments

Yehuda · Accepted Answer · 2021-04-29 01:12:03Z

0

You can use .isin():

df1['id_bool'] = df1.Id.isin(df2.Id)
df1['date_bool'] = df1.sendDate.isin(df2.actDate)

Check out the documentation here.

answered Apr 29, 2021 at 1:12

Yehuda

1,8933 gold badges24 silver badges55 bronze badges

Collectives™ on Stack Overflow

Compare multiple columns of two data frames using pandas

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related