Multiple matches and spaces variants (python lookup to return another column after match)

user_7961216 0
Viewed 104

Previously, I have matched values on a different list (this thread How to get a python lookup to return another column after match)

import pandas as pd
import numpy as np
df = pd.DataFrame({'Name':['a cat dog - multiple', 'grey puppy - narrow term', 'a cat puppy', 'reddog - single no spaces', 'acatdog - multiple no spaces']})
df2 = pd.DataFrame({'BroadTerm':['cat', 'cat', 'dog', 'dog'], 'NarrowTerm':['cat', 'kitten', 'puppy', 'dog']})

There are a couple of issues:

  1. Matching values where there are 1 or more values in a cell (eg row 1 of dataframe)
  2. Matching values that don't contain any spaces (eg rows 4 and 5 of df)

The base code is

df['Animal'] = df['Name'].str.extract(pat = f"({'|'.join(df2.NarrowTerm)})")[0].map(dict(df2.iloc[:,::-1].values))

But that only works for single hit cells / returns the first hit)

How do I modify the code to do this?

pandas taxonomy dataframe python
1 Answers

We can try findall then explode

df['step1'] = df['Name'].str.findall(pat = f"({'|'.join(df2.NarrowTerm)})")
df['animal'] = df['step1'].explode().map(dict(df2.iloc[:,::-1].values)).groupby(level=0).agg(list)
df
Out[63]: 
                           Name         step1      animal
0          a cat dog - multiple    [cat, dog]  [cat, dog]
1      grey puppy - narrow term       [puppy]       [dog]
2                   a cat puppy  [cat, puppy]  [cat, dog]
3     reddog - single no spaces         [dog]       [dog]
4  acatdog - multiple no spaces    [cat, dog]  [cat, dog]
user_79645270
Related