pandas dataframe: replace (standalone) substring in cell based on dict

user_13019596 0
Viewed 72

I'm wondering if someone in the community could help with the following:

Aim to regex replace substrings in a pandas DataFrame (based on a dictionary I pass as argument). Though the key:value replacement should only take place, if the dict key is found as a standalone substring (not as part of a word). By standalone substring I mean it starts after a whitespace

e.x:

mapping = {

   "sweatshirt":"sweat_shirt",
   "sweat shirt":"sweat_shirt",
   "shirt":"shirts"

}

df = pd.DataFrame([
         ["men sweatshirt"]
         ["men sweat shirt"]
         ["yellow shirt"]
       ])

df = df.replace(mapping,regex=True)

expected result: substring "shirt" within sweatshirt should NOT be replaced with "shirts" as value is part of another string not a standalone value(\b)

NOTE: the dictionary I pass is rather long so ideally there is a way to pass the standalone requirement (\b) as part of the dict I pass onto df.replace(dict, regex=True)

Thanks upfront

pandas dictionary python regex
3 Answers

You can use

df[0].str.replace(fr"\b(?:{'|'.join([x for x in mapping])})\b", lambda x: mapping[x.group()])

The regex will look like \b(?:sweatshirt|shirt)\b, it will match sweatshirt or shirt as whole words. The match will be passed to a lambda and the corresponding value will be fetched using mapping[x.group()].

Multiword Search Term Update

Since you may have multiword terms to search in the mapping keys, you should make sure the longest search terms come first in the alternation group. That is, \b(?:abc def|abc)\b and not \b(?:abc|abc def)\b.

import pandas as pd

mapping = {
   "sweat shirt": "sweat_shirt",
   "shirt": "shirts"
}

df = pd.DataFrame([
         ["men sweatshirt"],
         ["men sweat shirt"]
       ])
rx = fr"\b(?:{'|'.join(sorted([x for x in mapping],key=len,reverse=True))})\b"
df[0].str.replace(rx, lambda x: mapping[x.group()])

Output:

0     men sweatshirt
1    men sweat_shirt
Name: 0, dtype: object
user_38329700

Include the white-space in your pattern! :)

mapping = {

   " sweatshirt":" sweat_shirt",
   " shirt":" shirts"

}

df = ([
         ["men sweatshirt"]
       ])

df = df.replace(mapping,regex=True)
user_141479960

Try this code-

mapping = {

   " sweatshirt":" sweat_shirt",
   " shirt":" shirts"
}

import pandas as pd
df = pd.DataFrame ({'ID':["men sweatshirt", "black shirt"]}
       )

df = df.apply(lambda x: ' '+x, axis=1).replace(mapping,regex=True).ID.str.strip()
print(df)
user_100974190
Related