CODEWITHSUNDEEP
pythonpandas
Alex
Alex

Reputation: 53

Return boolean indicator if row value is contained in a column of a separate dataframe

I have a column full of identifiers

df1.['ID1']
    aaa
    aab
    aac
    ...
    zzx
    zzy
    zzz

And another column in a separate dataframe with identifiers that are contained in the ID1 column

df2.['ID2']
    aab
    ...
    zzy

I need to add a column to df1 that indicates whether or not the identifier in each row is contained in the column in df2. So something like this:

df1.['shared']
aaa   False
aab   True
aac   False
...
zzx   False
zzy   True
zzz   False

Upvotes: 1

Views: 73

Answers (3)

BENY
BENY

Reputation: 323376

Or you can try map

df2['Bool']=True
df2.index=df2.Col2
df1.Col1.map(df2.Bool).fillna(False)

Out[239]: 
0    False
1     True
2    False
3    False
4     True
5    False

Upvotes: 0

cs95
cs95

Reputation: 403120

Option 1

Use df.isin:

In [233]: df
Out[233]: 
  Col1
0  aaa
1  aab
2  aac
3  zzx
4  zzy
5  zzz

In [234]: df2
Out[234]: 
  Col2
0  aab
1  zzy

In [235]: df.Col1.isin(df2.Col2)
Out[235]: 
0    False
1     True
2    False
3    False
4     True
5    False
Name: Col1, dtype: bool

Option 2

You can use np.in1d:

In [344]: pd.Series(np.in1d(df.Col1, df2.Col2), name='Col1')
Out[344]: 
0    False
1     True
2    False
3    False
4     True
5    False
Name: Col1, dtype: bool

Timings:

In [245]: len(df_test)
Out[245]: 60000

In [246]: %timeit df_test.Col1.isin(df2.Col2)  # in this post
1000 loops, best of 3: 1.44 ms per loop

In [247]: %timeit df_test.Col1.apply(lambda x: x in df2.Col2) # df.apply solution
1 loop, best of 3: 601 ms per loop

In [349]: %timeit pd.Series(np.in1d(df_test.Col1, df2.Col2), name='Col1')
100 loops, best of 3: 2.92 ms per loop

Upvotes: 1

Egal
Egal

Reputation: 1474

df1['shared'] = df1.ID1.apply(lambda x: x in df2.ID2)

Upvotes: 0

Related Questions