Use dictionary to replace a string within a string in Pandas columns

Question

I am trying to use a dictionary key to replace strings in a pandas column with its values. However, each column contains sentences. Therefore, I must first tokenize the sentences and detect whether a Word in the sentence corresponds with a key in my dictionary, then replace the string with the corresponding value.

However, the result that I continue to get it none. Is there a better pythonic way to approach this problem?

Here is my MVC for the moment. In the comments, I specified where the issue is happening.

import pandas as pd

data = {'Categories': ['animal','plant','object'],
    'Type': ['tree','dog','rock'],
        'Comment': ['The NYC tree is very big','The cat from the UK is small','The rock was found in LA.']
}

ids = {'Id':['NYC','LA','UK'],
      'City':['New York City','Los Angeles','United Kingdom']}


df = pd.DataFrame(data)
ids = pd.DataFrame(ids)

def col2dict(ids):
    data = ids[['Id', 'City']]
    idDict = data.set_index('Id').to_dict()['City']
    return idDict

def replaceIds(data,idDict):
    ids = idDict.keys()
    types = idDict.values()
    data['commentTest'] = data['Comment']
    words = data['commentTest'].apply(lambda x: x.split())
    for (i,word) in enumerate(words):
        #Here we can see that the words appear
        print word
        print ids
        if word in ids:
        #Here we can see that they are not being recognized. What happened?
            print ids
            print word
            words[i] = idDict[word]
            data['commentTest'] = ' '.apply(lambda x: ''.join(x))
    return data

idDict = col2dict(ids)
results = replaceIds(df, idDict)

Results:

None

I am using python2.7 and when I am printing out the dict, there are u' of Unicode.

My expected outcome is:

Categories

Comment

Type

commentTest

  Categories  Comment  Type commentTest
0 animal  The NYC tree is very big tree The New York City tree is very big 
1 plant The cat from the UK is small dog  The cat from the United Kingdom is small 
2 object  The rock was found in LA. rock  The rock was found in Los Angeles.

jezrael · Accepted Answer · 2017-09-21T11:50:33.530

42

You can create dictionary and then replace:

ids = {'Id':['NYC','LA','UK'],
      'City':['New York City','Los Angeles','United Kingdom']}

ids = dict(zip(ids['Id'], ids['City']))
print (ids)
{'UK': 'United Kingdom', 'LA': 'Los Angeles', 'NYC': 'New York City'}

df['commentTest'] = df['Comment'].replace(ids, regex=True)
print (df)
  Categories                       Comment  Type  \
0     animal      The NYC tree is very big  tree   
1      plant  The cat from the UK is small   dog   
2     object     The rock was found in LA.  rock   

                                commentTest  
0        The New York City tree is very big  
1  The cat from the United Kingdom is small  
2        The rock was found in Los Angeles.

edited Sep 21 '17 at 11:50

answered Sep 21 '17 at 11:18

jezrael

822,522
95
1,334
1,252

1

Why is `regex=True`? From the docs I though it should be False: "Whether to interpret to_replace and/or value as regular expressions. If this is True then to_replace must be a string. Otherwise, to_replace must be None because this parameter will be interpreted as a regular expression or a list, dict, or array of regular expressions." – pceccon Feb 28 '18 at 18:48
2

@pceccon - In my opinion in docs should be notice it is more common used for replace substrings, what is totaly not clear from docs now. – jezrael Feb 28 '18 at 19:11

tdy · Answer 2 · 2021-06-20T09:45:23.963

It's actually much faster to use str.replace() than replace(), even though str.replace() requires a loop:

ids = {'NYC': 'New York City', 'LA': 'Los Angeles', 'UK': 'United Kingdom'}

for old, new in ids.items():
    df['Comment'] = df['Comment'].str.replace(old, new, regex=False)

#   Categories  Type                                   Comment
# 0     animal  tree        The New York City tree is very big
# 1      plant   dog  The cat from the United Kingdom is small
# 2     object  rock         The rock was found in Los Angeles

The only time replace() outperforms a str.replace() loop is with small dataframes:

The timing functions for reference:

def Series_replace(df):
    df['Comment'] = df['Comment'].replace(ids, regex=True)
    return df

def Series_str_replace(df):
    for old, new in ids.items():
        df['Comment'] = df['Comment'].str.replace(old, new, regex=False)
    return df

Note that if ids is a dataframe instead of dictionary, you can get the same performance with itertuples():

ids = pd.DataFrame({'Id': ['NYC', 'LA', 'UK'], 'City': ['New York City', 'Los Angeles', 'United Kingdom']})

for row in ids.itertuples():
    df['Comment'] = df['Comment'].str.replace(row.Id, row.City, regex=False)

This is indeed true. I am handling 1.5 mil lines and the improvement of running replace vs str.replace for a dict with 40 values (changing some characters) is quite important. I believe that the performance also depends on the number of columns/rows/changes but this solution is much faster than the accepted one for me. Thanks! — Svestis, Aug 20 '22 at 10:15

Use dictionary to replace a string within a string in Pandas columns

2 Answers2

Linked

Related