How to add new column based on a match condition on two pandas DataFrames

Question

I have two dataframe d1 and d2 as follows:
d1

A   B   C    D
X   6   7   23
Y   3   4   35
B   5   8   45
D   11  7   15
L   10  1   5

d2

and want new column in d1 E as follow:

A   B   C   D   E
X   6   7   23  0
Y   3   4   35  1
B   5   8   45  0
D  11   7   15  1
L  10   1   5   1

new column E is added based on the condition of data in column A & C of d2 matched with data in column A & C of d1, number of rows are different in booth dataframe.

https://stackoverflow.com/questions/48647534/python-pandas-find-difference-between-two-data-frames/48647840#48647840 almost same:-) — BENY, Jun 28 '19 at 18:16

cs95 · Accepted Answer · 2019-06-28T18:41:24.667

5

I'll be using merge (for a LEFT OUTER JOIN) with an indicator:

d1['E'] = (d1.merge(d2, on=['A', 'C'], how='left', indicator=True)
             .eval('_merge == "both"')
             .astype(int))
d1
   A   B  C   D  E
0  X   6  7  23  0
1  Y   3  4  35  1
2  B   5  8  45  0
3  D  11  7  15  1
4  L  10  1   5  1

Here's an inefficient approach using tuples conversion with isin checking:

d1['E'] = (
    d1[['A', 'C']].apply(tuple, axis=1).isin(d2.apply(tuple, axis=1)).astype(int))
d1
   A   B  C   D  E
0  X   6  7  23  0
1  Y   3  4  35  1
2  B   5  8  45  0
3  D  11  7  15  1
4  L  10  1   5  1

I'd rather post this myself, that way I get to tell you I don't recommend its use.

edited Jun 28 '19 at 18:41

answered Jun 28 '19 at 18:10

cs95

379,657
97
704
746

how can we do? if we want to add yes or no instead of 1 and 0. – Rahul Semwal Jun 28 '19 at 18:39
After running this code, use `df['E'] = np.where(df['E'] == 1, 'yes', 'no')`. – cs95 Jun 28 '19 at 18:47

How to add new column based on a match condition on two pandas DataFrames

1 Answers1