Reassigning substring using a dictionary object within pandas dataframe column

user_9522032 0
Viewed 103

The problem below has been simplified.

The solution should be applicable to larger data-sets and larger dictionaries.

Given a pandas.DataFrame

import pandas as pd

pd.DataFrame(data = {'foo': [1223, 2931, 3781], 
'bar': ["34 fake st, footown", "88 real crs, barrington", "28 imaginary st, bazington"]})

|    |   foo | bar                        |
|---:|------:|:---------------------------|
|  0 |  1223 | 34 fake st, footown        |
|  1 |  2931 | 88 real crs, barrington    |
|  2 |  3781 | 28 imaginary st, bazington |

and a dictionary object:

my_dictionary = {'st':'street', 'crs':'crescent'}

What is the best way to replace the sub-string contained within a column in a pandas.DataFrame with my_dictionary?

I expect to have a resulting pandas.DataFrame that looks like:

|    |   foo | bar                             |
|---:|------:|:--------------------------------|
|  0 |  1223 | 34 fake street, footown         |
|  1 |  2931 | 88 real crescent, barrington    |
|  2 |  3781 | 28 imaginary street, bazington  |

I have tried the following:

for key, val in my_dictionary.items():
    df.bar.loc[df.bar.str.contains(key)] = df.bar.loc[df.bar.str.contains(key)].apply(lambda x: x.replace(key,val))

df.bar

With the given output.

SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self._setitem_with_indexer(indexer, value)
0           34 fake street, footown
1      88 real crescent, barrington
2    28 imaginary street, bazington
Name: bar, dtype: object

How am I able to perform reassignment without getting the above warning message; and without using .copy()?

pandas dataframe python-3.x
2 Answers

You can use Series.replace:

df["bar"] = df["bar"].replace(my_dictionary, regex=True)

print (df)

    foo                             bar
0  1223         34 fake street, footown
1  2931    88 real crescent, barrington
2  3781  28 imaginary street, bazington
user_92844230

Do not use .bar.loc, that's chain indexing, which yields the warning. You should do instead:

df.loc[df.bar.str.contains(key), 'bar'] = ...

However, you can just do

for key, val in my_dictionary.items():
    df['bar'] = df['bar'].str.replace(key, val)

But I would be more cautious and make sure that the replacement happens where it should be

for key, val in my_dictionary.items():
    # this way, you don't replace `street` with `ststreet`
    df['bar'] = df['bar'].str.replace(fr'\b({key})\b', val)

Output:

    foo                             bar
0  1223         34 fake street, footown
1  2931    88 real crescent, barrington
2  3781  28 imaginary street, bazington
user_42384080
Related