Find and replace loop in Pandas

Question

I have a dataframe which has a lot of characters that need changing.

I can do this line by line but I couldn't figure out how to loop through these characters to replace with a new character.

This is my code so far:

df_media_input['MediaChannel']=df_media_input['MediaChannel'].map(lambda x: str.replace(x, "Direct Mail","DM"))
df_media_input['MediaChannel']=df_media_input['MediaChannel'].map(lambda x: str.replace(x, "DR TV","DRTV"))
df_media_input['MediaChannel']=df_media_input['MediaChannel'].map(lambda x: str.replace(x, "Affilliates","Affiliates"))
df_media_input['MediaChannel']=df_media_input['MediaChannel'].map(lambda x: str.replace(x, "DRTV","TV"))
df_media_input['MediaChannel']=df_media_input['MediaChannel'].map(lambda x: str.replace(x, "All Time TV","TV"))
df_media_input['MediaChannel']=df_media_input['MediaChannel'].map(lambda x: str.replace(x, "Peak TV","TV"))
df_media_input['MediaChannel']=df_media_input['MediaChannel'].map(lambda x: str.replace(x, "Regional Press","Press"))
df_media_input['MediaChannel']=df_media_input['MediaChannel'].map(lambda x: str.replace(x, "National Press","Press"))

but I feel something like this should be possible:

dic= {Direct Mail:DM}


for i and j in dic:

df_media_input['MediaChannel']=df_media_input['MediaChannel'].map(lambda x: str.replace(x, i,j))

where Direct Mail is i and DM is j

jezrael · Answer 1 · 2018-11-07T10:28:26.863

2

First create dictionary for replacing:

d = {"Direct Mail":"DM", 
     "DR TV":"DRTV",
     ...}

If want replace substrings use replace with regex=True:

df_media_input['MediaChannel'] = df_media_input['MediaChannel'].replace(d, regex=True)

If want replace values faster way use map with fillna:

df_media_input['MediaChannel'] = df_media_input['MediaChannel'].map(d)
                                     .fillna(df_media_input['MediaChannel'])

Check difference in sample:

df_media_input = pd.DataFrame({'MediaChannel':['Direct Mail','DR TV new','val']})
print (df_media_input)
  MediaChannel
0  Direct Mail
1    DR TV new
2          val

d = {"Direct Mail":"DM", "DR TV":"DRTV"}


df_media_input['MediaChannel1'] = df_media_input['MediaChannel'].replace(d, regex=True)

df_media_input['MediaChannel2'] = (df_media_input['MediaChannel'].map(d)
                                     .fillna(df_media_input['MediaChannel']))
print (df_media_input)
  MediaChannel MediaChannel1 MediaChannel2
0  Direct Mail            DM            DM
1    DR TV new      DRTV new     DR TV new
2          val           val           val

edited Nov 07 '18 at 10:28

answered Nov 07 '18 at 10:22

jezrael

822,522
95
1,334
1,252

df_media_input['MediaChannel'] = df_media_input['MediaChannel'].replace(d, regex=True) – fred.schwartz Dec 03 '18 at 09:15
should the above change from Fre&d to Fred? – fred.schwartz Dec 03 '18 at 09:16
I'm getting this; error: nothing to repeat at position 0 – fred.schwartz Dec 03 '18 at 09:16
what is `d` ? like `d = {"Fre&d":"Fred"}` ? Then `regex=True` not working – jezrael Dec 03 '18 at 09:18
d being a dictionary with find values : replace values – fred.schwartz Dec 03 '18 at 09:19
basically I want to find a all values in a dictionary and replace these values with replacements. for all rows in a column – fred.schwartz Dec 03 '18 at 09:20
@fred.schwartz - hmmm, so another solution not working? Because is necessary repalce by substring? – jezrael Dec 03 '18 at 09:20
yeah the values might be with in a string of text, rather that the full value – fred.schwartz Dec 03 '18 at 09:21
I can do it like this df['name'] = df['name'].map(lambda x: str.replace(x, "&","")) – fred.schwartz Dec 03 '18 at 09:22
but would quite like to do that for multiple values and not repeat that line of code – fred.schwartz Dec 03 '18 at 09:22
1

@fred.schwartz - Ya, I think best is create new question about it. – jezrael Dec 03 '18 at 09:23

Henry Woody · Accepted Answer · 2018-11-07T10:29:08.160

The Pandas DataFrame replace methods accepts a dictionary where the keys correspond to existing strings and the values correspond to the string to replace each with.

So in your example:

replacements = {
    "Direct Mail": "DM",
    "DR TV": "DRTV",
    # and so on...
}

df_media_input['MediaChannel'].replace(replacements, inplace=True)

Assuming that the values in the 'MediaChannel' column are just the strings to replace and do not contain those strings. For example, "Direct Mail" will be changed to "DM", however "I like Direct Mail" will not be changed to "I like DM". To handle this case with substrings, you'll need to set the regex keyword argument of replace to True.

@fred.schwartz - If performance is important, rather use [map + fillna](https://stackoverflow.com/q/49259580/2901002) — jezrael, Nov 07 '18 at 10:33

score 2 · Answer 3 · answered Nov 07 '18 at 10:31

Since you need to iterate, you could do something like this.

for i in range(len(df)):

    d = {"Direct Mail":"DM",
         "DR TV":"DRTV",
         "DRTV":"TV",
         "All Time TV":"TV",
         "Peak TV":"TV",
         "Regional Press":"Press",
         "National Press":"Press"
     }
    for x,y in d.items(): 
        df['MediaChannel'].values[i] = df['MediaChannel'].values[i].replace(x, y)

Find and replace loop in Pandas

3 Answers3