I have the following dataframe in pandas
target A B C
0 cat bridge cat brush
1 brush dog cat shoe
2 bridge cat shoe bridge
How do I test whether df.target is in any of the columns ['A','B','C', etc.], where there are many columns to check?
I have tried merging A,B and C into a string to use df.abcstring.str.contains(df.target) but this does not work.
You can use drop, isin and any.
drop the target column to have a df with your A, B, C columns onlyisin the target columnany hits are presentThat's it.
df["exists"] = df.drop("target", 1).isin(df["target"]).any(1)
print(df)
target A B C exists
0 cat bridge cat brush True
1 brush dog cat shoe False
2 bridge cat shoe bridge True
OneHotEncoder approach:
In [165]: x = pd.get_dummies(df.drop('target',1), prefix='', prefix_sep='')
In [166]: x
Out[166]:
bridge cat dog cat shoe bridge brush shoe
0 1 0 0 1 0 0 1 0
1 0 0 1 1 0 0 0 1
2 0 1 0 0 1 1 0 0
In [167]: x[df['target']].eq(1).any(1)
Out[167]:
0 True
1 True
2 True
dtype: bool
Explanation:
In [168]: x[df['target']]
Out[168]:
cat cat brush bridge bridge
0 0 1 1 1 0
1 0 1 0 0 0
2 1 0 0 0 1
Another approach using index difference method:
matches = df[df.columns.difference(['target'])].eq(df['target'], axis = 0)
# A B C
#0 False True False
#1 False False False
#2 False False True
# Check if at least one match:
matches.any(axis = 1)
#Out[30]:
#0 True
#1 False
#2 True
In case you wanted to see which columns meet the target, here is a possible solution:
matches.apply(lambda x: ", ".join(x.index[np.where(x.tolist())]), axis = 1)
Out[53]:
0 B
1
2 C
dtype: object