pandas contains exact string from a list

Question

I have 2 dataframes df1 and df2.

I would like to get all rows in df1 that has exact string match in column B of df2 This is df1:

df1={"columnA":['apple,cherry','pineple,lemon','banana, pear','cherry, pear, lemon']} 
df1=pd.DataFrame(df1)

This is df2:

df2={"columnB":['apple','cherry']}
df2=pd.DataFrame(df2)

Below code output incorrect result:

df1[df1['columnA'].str.contains('|'.join(df2['columnB'].values))]

Pineapple is not supposed to appear as this is not exact match.

How can i get result like this:

Could you please post your dataframes as code, so it is easier for us to help you? — Juan C, Aug 04 '20 at 21:06

score 2 · Accepted Answer · answered Aug 04 '20 at 21:09

2

Without actual reproducible code it's harder to help you, but I think this should work:

words = [rf'\b{string}\b' for string in df2.columnB]
df1[df1['columnA'].str.contains('|'.join(words))]

answered Aug 04 '20 at 21:09

Juan C

3,992
1
10
33

Zesty Dragon · Answer 2 · 2020-08-04T21:42:54.543

df1={"columnA":['apple,cherry','pineple,lemon','banana, pear','cherry, pear, lemon']} 
df1=pd.DataFrame(df1)
    
df2={"columnB":['apple','cherry']}
df2=pd.DataFrame(df2)

Larger way of doing it ,but correct and simpler

list1=[]
for i in range(0,len(df1)):
    for j in range(0,len(df2)):
        if(df2["columnB"][j] in df1["columnA"][i]):
            list1.append(i)
            break
df=df1.loc[list1]

Answer

ColumnA
0   apple,cherry
3   cherry, pear, lemon

score -1 · Answer 3 · answered Aug 04 '20 at 21:16

-1

You were very close, but you will need to apply the word-operator of regex:

df1[df1['columnA'].str.contains("\b(" + '|'.join(df2['columnB'].values) + ")\b")]

This will look for the complete words.

answered Aug 04 '20 at 21:16

JarroVGIT

1,458
1
9
13

pandas contains exact string from a list

3 Answers3