Pandas - Check if column of strings in column of lists row wise
You have a column of lists, of course pandas doesn't have any functions that natively support operations on data structured this badly. If you want the most performance, I'd recommend a list comprehension:
df['checkisin'] = [c in l for c, l in zip(df['check'], df['checklist'])]
df
check checklist checkisin
0 123 [abc, qw3, 123] True
1 ab12 [3e4r5, 12we3, asd23, q2w3] False
You might consider implementing try-except error handling if you're worried about NaNs and type mismatches:
def check_isin(check, checklist):
try:
return check in checklist
except TypeError:
return np.NaN
df['checkisin'] = [
check_isin(c, l) for c, l in zip(df['check'], df['checklist'])
]
Evidence suggests list comprehensions are the most desirable option for operations that cannot be vectorized.
PS, consider converting your column of lists into a column of sets if you plan to do membership tests a lot.
Here's an example of how this operation can be vectorized.
from itertools import chain
cl = df.pop('checklist')
df = (pd.DataFrame(df.reset_index().values.repeat(cl.str.len(), axis=0),
columns=['group', *df.columns])
.assign(checklist=list(chain.from_iterable(cl))))
df
group check checklist
0 0 123 abc
1 0 123 qw3
2 0 123 123
3 1 ab12 3e4r5
4 1 ab12 12we3
5 1 ab12 asd23
6 1 ab12 q2w3
7 1 ab12 123
(df['check'] == df['checklist']).groupby(df.group).any()
group
0 True
1 False
dtype: bool
I will using isin
with Series
, isin with Series will match the index
first which is different from list
pd.DataFrame(df.checklist.tolist(),index=df.index).isin(df.check).any(1)
Out[496]:
0 True
1 False
dtype: bool
Or
pd.DataFrame(df.checklist.tolist(),index=df.index).eq(df.check,0).any(1)
map
df.assign(checkisin=[*map(lambda s, x: s in x, *map(df.get, df))])
check checklist checkisin
0 123 [abc, qw3, 123] True
1 ab12 [3e4r5, 12we3, asd23, q2w3] False
If your dataframe has more columns, you can be more explicit
cols = ['check', 'checklist']
df.assign(checkisin=[*map(lambda s, x: s in x, *map(df.get, cols))])