Set a column in Pandas based on if another column contains a name from a list

user_15172987 0
Viewed 311

I have been struggling with this one for a bit so I figured it was time to ask.

I have a list of names:

names = ["john", "sally", "tom"]

I have a df and one of the columns is action. action has many different things, e.g.:

  • "Went for a walk with tom"
  • "Took sally to the store"
  • ...

I want to make a new column called partner and set that to the name that is in action. I already have the column set and it is filled for some logs but not all.

I tried:

for name in names:
    df['partner'] =  np.where(df.action.str.contains(name), name, df['partner'] )

But I get this error:

TypeError: first argument must be string or compiled pattern

Am I going about this the right way? Is there a better way to do this? Any help would be appreciated.

Edit: To make a sample of my df you could use:

names = ["john", "sally", "tom"]
d = {'name': ['mark','rick','mark','jon', 'lenny'], 'action': ['Went for a walk with tom', 'Took sally to the store', 'Went for a walk with john', 'Went racing with tom and lost', 'Took john to the store'],
    'partner': ['tom', '', 'john', '', 'john']}
df = pd.DataFrame(data=d)
df

the list 'names' has all the possible names that could be in the string, so I figured the easiest was to find which name was in the string and set that to the partners column.

Here is the full error i get:

TypeError                                 Traceback (most recent call last)
<ipython-input-68-ed79b0ff06a7> in <module>()
     11 
     12 for partner in partners:
---> 13     EscrowLogs.loc[EscrowLogs.action.str.contains(partner), 'partner'] = partner
     14 
     15 

~\Anaconda3\lib\site-packages\pandas\core\strings.py in contains(self, pat, case, flags, na, regex)
   2415     def contains(self, pat, case=True, flags=0, na=np.nan, regex=True):
   2416         result = str_contains(self._data, pat, case=case, flags=flags, na=na,
-> 2417                               regex=regex)
   2418         return self._wrap_result(result)
   2419 

~\Anaconda3\lib\site-packages\pandas\core\strings.py in str_contains(arr, pat, case, flags, na, regex)
    385             flags |= re.IGNORECASE
    386 
--> 387         regex = re.compile(pat, flags=flags)
    388 
    389         if regex.groups > 0:

~\Anaconda3\lib\re.py in compile(pattern, flags)
    232 def compile(pattern, flags=0):
    233     "Compile a regular expression pattern, returning a Pattern object."
--> 234     return _compile(pattern, flags)
    235 
    236 def purge():

~\Anaconda3\lib\re.py in _compile(pattern, flags)
    283         return pattern
    284     if not sre_compile.isstring(pattern):
--> 285         raise TypeError("first argument must be string or compiled pattern")
    286     p = sre_compile.compile(pattern, flags)
    287     if not (flags & DEBUG):

TypeError: first argument must be string or compiled pattern
pandas python
2 Answers

I would need a verifiable sample of your data to be sure, but using boolean indexing should work:

for name in names:
     df.loc[df.action.str.contains(name), 'partner'] = name
user_123994090

Following up on my comment, you could write a function to iterate over rows of your dataframe and catch values that produce an error/exception.

For example, you could use this function that returns a null value if the action field could not be parsed:

names = ["john", "sally", "tom"]

def get_partner(p, a):
    # if row already contains partner value, leave as is
    if p:
        return p
    # otherwise, extract partner name from the action column
    else:
        try:
            for name in names:
                if name in a:
                    return name
        # for any problematic action strings, return null value
        # (can be replaced with some other string that you can later check)
        except:
            return None

You could also use this function that does not require looping over names. It splits each sentence into a list of words and removes all words that are not found in the list of names, leaving you with just the name value. If there is more than one name, it uses a comma delimiter to separate them.

names = ["john", "sally", "tom"]

def get_partner(p, a):
    # if row already contains partner value, leave as is
    if p:
        return p
    # otherwise, extract partner name(s) from the action column
    else:
        try:
            return ",".join([i for i in a.split() if i in names])
        # for any problematic action strings, return null value
        # (can be replaced with some other string that you can later check)
        except:
            return None

You would then use .apply() to run the function on your dataframe:

df['partner'] = df.apply(lambda x: get_partner(x['partner'], x['action']), axis=1)
user_99876230
Related