Extract common words between 2 columns in different tables python

user_7007525 0
Viewed 494

I want to extract all the words in df1 that match with df2.

df1 = pd.DataFrame(['Dog has 4 legs.It has 2 eyes.','Fish has fins','Cat has paws.It eats fish','Monkey has tail'],columns=['Description'])

df2 = pd.DataFrame(['Fish','Legs','Eyes'],columns=['Parts'])


 Df1                                             Df2
|---------------------------------|             |---------------------------------|
|         **Description**         |             |          Parts                  |     
|---------------------------------|             |---------------------------------|
|  Dog has 4 legs.It has 2 eyes.  |             | Fish                            |
|---------------------------------|             |---------------------------------|
|  Fish has fins                  |             | Legs                            | 
|---------------------------------|             |---------------------------------|
|  Cat has paws.It eats fish.     |             | Tail                            |  
|---------------------------------|             |---------------------------------|

Desired Output:

|---------------------------------|-----------|
|         **Description**         |Parts      |
|---------------------------------|-----------|
|  Dog has 4 legs.It has 2 eyes.  |Legs,Tail  |
|---------------------------------|-----------|
|  Fish has fins                  |Fish       |   
|---------------------------------|-----------|
|  Cat has paws.It eats fish.     |Fish       | 
|---------------------------------|-----------|
|  Monkey has tail                |           |   
|---------------------------------|-----------|
pandas match dataframe text python
2 Answers

IIUC str.extractall to gather all the matches, then groupby the index to create a list or aggregation. 

import re

pat = '|'.join(df2['Parts'].tolist())
#Fish|Legs|Eyes

df1['Parts'] = df1['Description'].str.extractall(f"({pat})"
                                  ,flags=re.IGNORECASE)\
                            .groupby(level=0)[0].agg(','.join)

print(df1)
                     Description      Parts
0  Dog has 4 legs.It has 2 eyes.  legs,eyes
1                  Fish has fins       Fish
2      Cat has paws.It eats fish       fish
3                Monkey has tail        NaN
user_93751020

@Datanovice's solution is better, as everything is within Pandas. This is an alternative, and faster (string manipulations are not so fast within Pandas) : 

from itertools import product
from collections import defaultdict
res = df2.Parts.str.lower().array
d = defaultdict(list)
for description, word in product(df1.Description, res):
    if word in description.lower():
        d[description].append(word)

d

defaultdict(list,
            {'Dog has 4 legs.It has 2 eyes.': ['legs', 'eyes'],
             'Fish has fins': ['fish'],
             'Cat has paws.It eats fish': ['fish']})

df1['parts'] = df1.Description.map(d).str.join(',')
       Description                    parts
0   Dog has 4 legs.It has 2 eyes.   legs,eyes
1   Fish has fins                   fish
2   Cat has paws.It eats fish       fish
3   Monkey has tail
user_71757130
Related