create another column based on matching of two data frames columns

Question

I have two data frames df1 and df2 with a common column ID. Both the data frames have different number of rows and columns. I want to compare these two dataframe ID’s. I want to create another column y in df1 and for all the common id’s present in df1 and df2 the value of y should be 0 else 1. For example df1 is

Id col1 col2

1   Abc  def

2   Geh  ghk 

3   Abd  fg

1   Dfg  abc

And df2 is

Id  col3 col4

1    Dgh gjs 

2    Gsj aie

The final dataframe should be

Id  col1  col2 y

1   Abc   def  0

2   Geh   ghk  0

3   Abd   fg   1 

1   Dfg   abc  0

Mahdi Ghelichi · Accepted Answer · 2019-12-18 15:32:28Z

1

Lets create df1 and df2 first:

df1=pd.DataFrame({'ID':[1,2,3,1], 'col1':['A','B','C', 'D'], 'col2':['C','D','E', 'F']})
df2=pd.DataFrame({'ID':[1,2], 'col3':['AA','BB'], 'col4':['CC','DD']})

Here, pandas lambda function comes handy:

df1['y'] = df1['ID'].map(lambda x:0 if x in df2['ID'].values else 1)

df1
   ID col1 col2  y
0   1    A    C  0
1   2    B    D  0
2   3    C    E  1
3   1    D    F  0

answered Dec 18, 2019 at 15:32

Mahdi Ghelichi

1,17014 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

create another column based on matching of two data frames columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related