Function to replace multiple strings in dataframe column - "TypeError: expected string or bytes-like object"

Question

I have a large dataframe in the following form:

FundManager | AsOfDate | InvestmentDesc | Amount  
ManagerA | 6/1/2020 | Four Seasons 1st lien TL | 25500.86  
ManagerA | 6/1/2020 | Arden Group First Lien TL | 28731.00  
ManagerB | 6/1/2020 | Four Seasons 1L Term Loan | 16853.00  
ManagerB | 6/1/2020 | Arden 1st Lien Term Loan | 50254.30

The same underlying financial instrument will often be held by multiple asset managers but described in a slightly different manner as shown in the table above (e.g. "Four Seasons 1st Lien TL" vs. "Four Seasons 1L Term Loan").

I am trying to figure out the best way to make certain replacements to the "InvestmentDesc" column in the pandas dataframe (e.g. replace "Term Loan" with "TL", replace "1st Lien" with "1L"), ideally (i) in a way that doesn't have to repeatedly loop through several million rows for each term, and (ii) in a way that can be used on a handful of other columns in the dataframe but using a separate list of terms to replace.

I currently have the following function:

def replace_all(dictReplace, text):
    rep = dict((re.escape(k), v) for k, v in dictReplace.items())
    pattern = re.compile("|".join(rep.keys()))
    text = pattern.sub(lambda m: rep[re.escape(m.group(0))], text)
    return text

Into which I am then trying to pass a list of terms to replace (what I assume is a dictionary?) and the existing dataframe column to be modified:

dictRepStrings = {"1st lien": "1l", "first lien": "1l", "2nd lien": "2l", "second lien": "2l", "term loan": "tl"}
df['NewCol'] = df['InvestmentDesc'].apply(lambda x: replace_all(dictRepStrings, df['InvestmentDesc']))
df

However upon doing so, I end up with "TypeError: expected string or bytes-like object" as shown below:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
 in 
     23 
     24 dictRepStrings = {"1st lien": "1l", "first lien": "1l", "2nd lien": "2l", "second lien": "2l", "term loan": "tl"}
---> 25 df['NewCol'] = df['InvestmentDesc'].apply(lambda x: replace_all(dictRepStrings, df['InvestmentDesc']))
     26 df

~\anaconda3\lib\site-packages\pandas\core\series.py in apply(self, func, convert_dtype, args, **kwds)
   3846             else:
   3847                 values = self.astype(object).values
-> 3848                 mapped = lib.map_infer(values, f, convert=convert_dtype)
   3849 
   3850         if len(mapped) and isinstance(mapped[0], Series):

pandas\_libs\lib.pyx in pandas._libs.lib.map_infer()

 in (x)
     23 
     24 dictRepStrings = {"1st lien": "1l", "first lien": "1l", "2nd lien": "2l", "second lien": "2l", "term loan": "tl"}
---> 25 df['NewCol'] = df['InvestmentDesc'].apply(lambda x: replace_all(dictRepStrings, df['InvestmentDesc']))
     26 df

 in replace_all(dictReplace, text)
     18     rep = dict((re.escape(k), v) for k, v in dictReplace.items())
     19     pattern = re.compile("|".join(rep.keys()))
---> 20     text = pattern.sub(lambda m: rep[re.escape(m.group(0))], text)
     21     return text
     22 

TypeError: expected string or bytes-like object

After two days of googling-until-frustration I simply cannot figure out what I am doing wrong or how to fix. Any advice or thoughts as to how I should proceed would be greatly appreciated!

user_10140310 · Answer

If you have a large dataset, I'd try using numpy.select and pandas.str.contains:

import numpy as np
import pandas as pd


df["NewCol"] = np.select(
    condlist=[
        df["InvestmentDesc"].str.contains("1st lien|first lien", case=False, na=False),
        df["InvestmentDesc"].str.contains("2nd lien|second lien", case=False, na=False),
        df["InvestmentDesc"].str.contains("term loan", case=False, na=False),
    ],
    choicelist=[
        "1l", "2l", "tl"
    ],
    default=df["InvestmentDesc"]
)

user_4755954 · Answer

There is a syntax issue with your apply function. Instead of passing a single text and dictionary as a parameter, you are passing the whole series (InvestmentDesc) column in your apply. Thus the function fails during a call with lambda.

Required: replace_all(dictReplace, text)
Given: replace_all(dictRepStrings, df['InvestmentDesc'])

You can fix just this yourself, but I would recommend a few more small changes for readability-sake. Try using it with args parameter.

def replace_all(text, dictReplace): #Made dictReplace as second parameter
    rep = dict((re.escape(k), v) for k, v in dictReplace.items())
    pattern = re.compile("|".join(rep.keys()))
    text = pattern.sub(lambda m: rep[re.escape(m.group(0))], text) 
    return text

dictRepStrings = {"1st lien": "1l", "first lien": "1l", "2nd lien": "2l", "second lien": "2l", "term loan": "tl"}
df['NewCol'] = df['InvestmentDesc'].apply(replace_all, args=[dictRepStrings]) #modified apply function with args
df

Notice that I changed the apply function's structure by removing lambda, added args parameter AND made the dict as the second parameter in the function, so apply passes each row of the dict as the first parameter and second parameter is defined in args

This works for me, let me know if you still face an issue.

user_10818128 · Answer

you could also do something relatively simple like this:

def replacer(desc, replacers):
    for key in replacers.keys():
        if key in desc.lower():
            desc = desc.lower().replace(key, replacers[key]).title()
    return desc

replacers = {'1st lien': '1l', 'first lien': '1l', '2nd lien': '2l', 'second lien': '2l', 'term loan': 'tl'}

df['InvestmentDesc'].apply(replacer, replacers=replacers)

output:

0    Four Seasons 1L Tl
1     Arden Group 1L Tl
2    Four Seasons 1L Tl
3           Arden 1L Tl

not sure if the capitalization matters, or maybe you could tweak it slightly to get the capitalization you want. but I think this is a pretty simple solution, and will also account for multiple matches in every string

could perhaps modify this for a case insensitive regex search/replace, but same principle

Function to replace multiple strings in dataframe column - "TypeError: expected string or bytes-like object"

3 Answers