[FIXED] pandas dataframe map lookup table where key is in string

Issue

I know how to map a lookup table. But what if the key to find is hidden in a string:
source-df

ColA
"Pete eats apple."
"I like chocolate more."
"Orange from Carla."

lookup-df:

Col1      Col2
Orange    orange
chocolate brown
apple     green

should extend the source-df like so.

ColA                     Color
"Pete eats apple."       green
"I like chocolate more." brown
"Orange from Carla."     orange

Solution

Use Series.str.extract with Series.map:

d = lookupdf.set_index('Col1')['Col2'].to_dict()
pat = '|'.join(d)
#if words boundaries are important
#pat = '|'.join(r"\b{}\b".format(x) for x in d)
df['Color'] = df['ColA'].str.extract('('+ pat + ')', expand=False).map(d)

Answered By - jezrael