CODEWITHSUNDEEP
python-3.xnumpypandas
user3682157
user3682157

Reputation: 1695

Check if ID in one DF exists in another DF (Python 3, Pandas)

I have two dataframes like this:

DF1

    ID
    10C
    25Y
    66B
    100W

DF2

    ID
    10C
    5
    25Y
    66B

I want to check to see if any of the values in DF1 appear in DF2 and if so, add either a 1 (if it exists) or 0 (if it doesn't) to a new column such as

  ID    Appears
  10C      1
  25Y      1
  66B      1
  100W     0

I know this is a really simple problem but it is giving me fits.

Been trying something like

df3 = df1.merge(df2, on='ID', how='left')
df3.fillna(0)
df3['Appear'][df3.ID_x > 0] = 1
df3['Appear'][df3.ID_x = 0] = 0

Upvotes: 1

Views: 939

Answers (1)

behzad.nouri
behzad.nouri

Reputation: 77971

You may simply use np.in1d:

>>> np.in1d(df1['ID'], df2['ID']).astype('int')
array([1, 1, 1, 0])
>>> df1['Appears'] = np.in1d(df1['ID'], df2['ID']).astype('int')
>>> df1
    ID  Appears
0  10C        1
1  25Y        1
2  66B        1
3  100        0

merge-kind solution, would be like below, but I think using np.in1d would be faster.

>>> df2['Appears'] = 1
>>> df1.merge(df2, on='ID', how='left').fillna({'Appears':0})
    ID  Appears
0  10C        1
1  25Y        1
2  66B        1
3  100        0

Upvotes: 1

Related Questions