Pandas. Check if any of the split strings from one column are in another

user_11932905 0
Viewed 97

I have a data frame with 2 columns with strings comma-separated.
I'm trying to make speed-efficient solution to calculate 3-d column indicating if any of split strings from column A present in column B.
For example:

df = pd.DataFrame({'A':['apple', 'cucamber', 'tomato,mellon', 'tomato,potato'], 
                   'B':['apple,banana', 'pineapple', 'tomato juice', 'cheese,tomato,onion']})

Result should be

  A              B                  C
apple          apple,banana         1
cucamber       pineapple            0
tomato,mellon  tomato juice         0
tomato,potato  cheese,tomato,onion  1

It could be > 1mln rows.
If it's crucial for speed, even without splitting second column is fine, just searchingsplitted strings from frist column in second.

  A              B                  C
apple          apple,banana         1
cucamber       pineapple            0
tomato,mellon  tomato juice         1
tomato,potato  cheese,tomato,onion  1

Appreciate any help.

pandas python
1 Answers

One idea with lsit comprehension and any for test if match at least one string:

df['C'] = [any(z in y for z in x.split(',')) for x, y in df[['A','B']].to_numpy()]
df['C'] = df['C'].astype(int)
print (df)
               A                    B  C
0          apple         apple,banana  1
1       cucamber            pineapple  0
2  tomato,mellon         tomato juice  1
3  tomato,potato  cheese,tomato,onion  1

EDIT:

One possible solution for working with missing values are replaced them first, if dont need match between them replace each column NaNs by different values like:

df = pd.DataFrame({'A':[None, 'cucamber', 'tomato,mellon', 'tomato,potato'], 
                   'B':['apple,banana', None, 'tomato juice', 'cheese,tomato,onion']})


d = {'A':'missing1', 'B':'missing'}
df['C'] = [any(z in y for z in x.split(',')) for x, y in df[['A','B']].fillna(d).to_numpy()]
df['C'] = df['C'].astype(int)
print (df)
               A                    B  C
0           None         apple,banana  0
1       cucamber                 None  0
2  tomato,mellon         tomato juice  1
3  tomato,potato  cheese,tomato,onion  1
user_29010020
Related