replace in pandas — practice examples (Bamboo Weekly)

Swap whole values for other values — one at a time, by list, by dictionary, or by regular expression.

Have you ever written .replace('St', 'Street') on a column of addresses, run it, and found that every single row came back unchanged? I have, and the reason is the one thing you need to know about this method before anything else: replace matches whole values. It looked at 'Main St', asked whether that value is 'St', decided that it is not, and moved on. It was never searching inside the string.

That is the line between the two methods people mix up constantly. str.replace reaches inside each string and edits part of it — a find-and-replace within the text. replace treats every cell as an indivisible unit and asks only "is this value equal to the thing I named?" Both are useful, neither is a substitute for the other, and choosing the wrong one usually produces no error at all. Your data just comes back exactly as it went in.

Once you have that straight, replace is a pleasure. It works on a series or on an entire data frame, it takes several shapes of instruction, and it returns something new rather than modifying what you gave it, so it drops into a method chain without a fuss.

Official documentation: DataFrame.replace and Series.replace

The arguments that earn their keep

The signature is short. On Pandas 3.0.5:

import inspect
import pandas as pd

inspect.signature(pd.DataFrame.replace)

(self, to_replace=None, value=<no_default>, *, inplace: 'bool' = False, regex: 'bool' = False)

Two positional arguments and two keyword-only ones. If you learned this method a few years ago, notice what is missing: limit and method were both removed in Pandas 3, and passing either now raises TypeError: NDFrame.replace() got an unexpected keyword argument.

Nearly all of the expressiveness lives in to_replace, which accepts five different shapes:

s.replace('n.a.', 0)                          # one value for another
s.replace(['n.a.', 'none', '--'], 0)          # several values, one replacement
s.replace(['A', 'B'], ['Alpha', 'Beta'])      # two lists, matched by position
s.replace({'A': 'Alpha', 'B': 'Beta'})        # a dict, which is the same thing
df.replace({'grade': {'A': 4}, 'year': {0: pd.NA}})   # a dict per column

The last form is the one worth memorizing. On a data frame, a plain dict is applied to every column; a dict of dicts says "in this column, make this change," and leaves the other columns alone. That is the difference between a blanket edit and a scalpel, and on a wide frame it matters.

regex=True changes the rules in a way that is easy to miss: it switches replace from whole-value matching to substring matching, because Pandas hands your pattern to re.sub. Compare the two:

t = pd.Series(['Other', 'Some Other Thing'])

t.replace('Other', 'X')                  # ['X', 'Some Other Thing']
t.replace('Other', 'X', regex=True)      # ['X', 'Some X Thing']

So regex=True is not only "now my pattern may contain metacharacters." It is also "now I am matching parts of values." Anchor with ^ and $ when you mean the whole thing.

inplace=True mutates the object rather than returning a new one. I never use it, and the section below explains why.

A worked example, on real data

Bamboo Weekly #52 looked at US Customs and Border Protection's nationwide encounters data, which is a good specimen because it was published for humans to read and needs a half dozen small corrections before it will behave:

import pandas as pd

url = ('https://www.cbp.gov/sites/default/files/assets/documents/'
       '2024-Jan/nationwide-encounters-fy21-fy24-dec-aor.csv')

df = pd.read_csv(url, storage_options={'User-Agent': 'Mozilla/5.0'})

That gives 47,583 rows and 12 columns. Start with the fiscal year:

df['Fiscal Year'].value_counts()

Fiscal Year
2023           16522
2022           14829
2021           12437
2024 (FYTD)     3795
Name: count, dtype: int64

Three tidy years and one that carries an editorial note. '2024 (FYTD)' is a whole value, which makes this the simplest possible case — one scalar in, one scalar out:

df['Fiscal Year'].replace('2024 (FYTD)', '2024').value_counts()

Fiscal Year
2023    16522
2022    14829
2021    12437
2024     3795
Name: count, dtype: int64

That single change is what lets the column become an integer. Inside a chain, I would write it with assign so nothing is modified along the way:

(
    df
    .assign(**{'Fiscal Year':
               pd.col('Fiscal Year').replace('2024 (FYTD)', '2024').astype(int)})
    ['Fiscal Year'].value_counts().sort_index()
)

Fiscal Year
2021    12437
2022    14829
2023    16522
2024     3795
Name: count, dtype: int64

The Demographic column has a different problem — two of its four values are internal jargon. FMUA is CBP's abbreviation for family units, and UC / Single Minors covers unaccompanied children. Two lists, matched up by position, fix both:

(
    df['Demographic']
    .replace(['FMUA', 'UC / Single Minors'],
             ['Family units', 'Unaccompanied minors'])
    .value_counts()
)

Demographic
Single Adults           28076
Family units            12037
Unaccompanied minors     5224
Accompanied Minors       2246
Name: count, dtype: int64

Paired lists work, but I almost always write that as a dictionary instead. It says the same thing, it cannot fall out of alignment when you add a pair, and you can read each replacement without counting positions:

df['Demographic'].replace({'FMUA': 'Family units',
                           'UC / Single Minors': 'Unaccompanied minors'})

Now the nested form, which handles both columns in one call — and this is exactly what Reuven did in #52, where a dict of dicts corrected the fiscal year without touching anything else:

(
    df
    .replace({'Fiscal Year': {'2024 (FYTD)': '2024'},
              'Demographic': {'FMUA': 'Family units',
                              'UC / Single Minors': 'Unaccompanied minors'}})
    .pivot_table(index='Fiscal Year', columns='Demographic',
                 values='Encounter Count', aggfunc='sum')
)

Demographic  Accompanied Minors  Family units  Single Adults  Unaccompanied minors
Fiscal Year
2021                       3024        483846        1321674                147975
2022                       5985        614023        1993694                152880
2023                       7482        993947        2061723                137992
2024                       1561        384665         564641                 37952

Single adults doubled between 2021 and 2023 while unaccompanied minors barely moved. The 2024 row covers only October through December, so read it as a quarter rather than a year.

replace on a whole frame, or on one column

The Area of Responsibility column holds 41 distinct values covering 33 places, because eight of those places appear twice — once as a Border Patrol sector and once as a port-of-entry field office. El Paso's numbers are therefore split in two. A regular expression that strips the suffix merges them:

(
    df
    .replace(r' (Field Office|Sector)$', '', regex=True)
    .groupby('Area of Responsibility')['Encounter Count'].sum()
    .nlargest(8)
)

Area of Responsibility
Rio Grande Valley    1424630
Del Rio              1285703
El Paso              1109729
Tucson               1075668
San Diego             945250
Laredo                656457
Yuma                  617957
Miami                 337681
Name: Encounter Count, dtype: int64

Called on the data frame, that expression was tried against every value in every column. Here it is harmless, because no other column ends in "Sector" or "Field Office" — and when junk really is scattered across a whole table, that sweep is the point. Wikipedia footnote markers are the classic case, which is why #96 clears them with a single .replace(r'\s*\[\w+\]\s*', '', regex=True) on the entire frame.

But a value that is junk in one column can be meaningful in another. In Citizenship, 'OTHER' means a country outside the top twenty. In Land Border Region, 'Other' is a real category — every encounter that did not happen at the northern or southwestern land border. A tidy-looking case-insensitive sweep would flatten both into the same word and quietly destroy one of them. So when the change belongs to one column, say so — either with a nested dict, or by naming the column:

(
    df
    .assign(place=pd.col('Area of Responsibility')
                    .replace(r' (Field Office|Sector)$', '', regex=True))
    .groupby('place')['Encounter Count'].sum()
    .nlargest(5)
)

place
Rio Grande Valley    1424630
Del Rio              1285703
El Paso              1109729
Tucson               1075668
San Diego             945250
Name: Encounter Count, dtype: int64

Five mistakes people make

Expecting replace to find substrings. This is the big one, and it is worth watching fail. Suppose I want every sector spelled out in full:

df['Area of Responsibility'].replace('Sector', 'Border Patrol Sector').value_counts().head()

Area of Responsibility
San Diego Field Office    2450
Yuma Sector               2361
Buffalo Field Office      2281
El Paso Sector            2237
San Diego Sector          2223
Name: count, dtype: int64

Nothing changed, and nothing complained. Not one value in that column is equal to the string 'Sector', so nothing matched. There are two fixes, and they produce identical results here:

df['Area of Responsibility'].replace(r'Sector$', 'Border Patrol Sector', regex=True)
df['Area of Responsibility'].str.replace('Sector', 'Border Patrol Sector')

I would pick str.replace. If the job is editing text inside a string, the string method announces that, and keeping plain replace for whole values keeps the distinction from blurring again next month. The exception is when the same edit has to sweep many columns at once — .str works on one series at a time, so a whole-frame cleanup is a job for replace(regex=True).

Turning on regex=True and forgetting what metacharacters mean. Every special character wakes up at once, and an unescaped . is the fastest way to lose a column. The Component column holds 'U.S. Border Patrol' and 'Office of Field Operations'; say I want the periods gone:

df['Component'].replace('.', '', regex=True).value_counts()

Component
    47583
Name: count, dtype: int64

Every value in the column is now an empty string, because . matched every character. r'\.' is what I meant. A $ misbehaves in the opposite direction — it anchors to the end of the value instead of matching a currency symbol, so it matches nothing and you notice much later. #100 gets this right, with a backslash: .replace(r'\$(\d)', r'\1', regex=True).

The subtler version is forgetting the anchors. Because regex=True matches substrings, a pattern that names a whole value will happily hit a longer one:

(
    df['Area of Responsibility']
    .replace('El Paso', 'El Paso BP', regex=True)
    .value_counts()
    .filter(like='El Paso')
)

Area of Responsibility
El Paso BP Sector          2237
El Paso BP Field Office    1583
Name: count, dtype: int64

Both were rewritten. r'^El Paso Sector$' hits only the one I wanted.

Assuming the result gets a sensible dtype. Replace strings with numbers and you would hope for a numeric column. You do not get one:

codes = df['Title of Authority'].replace({'Title 8': 8, 'Title 42': 42})
codes.head(3)

0    42
1     8
2     8
Name: Title of Authority, dtype: object

Those are real Python integers sitting in an object column. Pandas 2.2 used to convert the column for you and raised a FutureWarning saying that the downcasting would stop. It has now stopped, and in Pandas 3 the warning is gone along with it, so nothing tells you anything at all. The column costs 1,713,120 bytes instead of 380,796, and it behaves like text where it counts:

codes.describe()

count     47583
unique        2
top           8
freq      39455
Name: Title of Authority, dtype: int64

That is the summary of a categorical column, not of numbers. Follow every type-changing replace with an explicit astype — which is exactly what #137 does with .astype(float) and #135 does with .astype(int) — or with .infer_objects() if you would rather let Pandas choose. More on that at astype.

Calling replace on a category column. A categorical stores its allowed values up front, and replace respects that literally:

demo = df['Demographic'].astype('category')
demo.replace({'FMUA': 'Family units'})

TypeError: Cannot setitem on a Categorical with a new category (Family units),
set the categories first

A loud error is the good outcome. The quiet one comes when your replacement is already a category, because then the change succeeds and leaves a ghost behind:

demo.replace({'Accompanied Minors': 'UC / Single Minors'}).value_counts()

Demographic
Single Adults         28076
FMUA                  12037
UC / Single Minors     7470
Accompanied Minors        0
Name: count, dtype: int64

The two groups merged correctly, and Accompanied Minors is still in the category list with zero rows in it — which will reappear in every groupby and every plot until you remove it. For renaming, use the tool built for the job:

demo.cat.rename_categories({'FMUA': 'Family units'})

That keeps the column categorical and keeps the category list honest, with no phantoms left over. See Series.cat.rename_categories.

Reaching for inplace=True. On a column of a data frame it does nothing whatsoever, though at least it says so:

df['Fiscal Year'].replace('2024 (FYTD)', '2024', inplace=True)

ChainedAssignmentError: A value is being set on a copy of a DataFrame or Series
through chained assignment using an inplace method.

The warning is accurate: the value does not stick, and df is unchanged. Nor does inplace save you the memory people imagine it saves, now that copy-on-write means an ordinary .replace() does not duplicate anything it does not have to.

Write df['Fiscal Year'] = df['Fiscal Year'].replace(...) if you want a lasting edit, or better, put the whole thing inside assign as in the example above and let the chain carry the new frame forward.

replace, map, or fillna?

Three other methods change values, and picking the right one saves an afternoon. Use map when the translation is meant to be total, since anything its lookup table misses becomes NaN, and replace when only some values should change and the rest must survive untouched. Use fillna when the problem really is missing data, because it knows forward and backward filling and replace only knows how to swap NaN for a constant. And use str.replace when you are editing text inside a value rather than exchanging the value itself.

Where it shows up in Bamboo Weekly

Thirty-one Bamboo Weekly solutions use this method. Four worth reading, all of them free:

Bamboo Weekly #52: Border encounters is the source of the data above, and the cleanest nested-dict example in the archive: .replace({'Fiscal Year': {'2024 (FYTD)': '2024'}}), one correction aimed at one column, in the middle of loading the file.

Bamboo Weekly #40: Sovereign bonds turns a table of credit ratings into numbers by replacing many things with one thing: .replace(to_replace=['^A', '^B{3}', '^Baa'], value=1, regex=True), followed by .replace('[A-Za-z]', 0, regex=True) to zero out everything else and a plain .replace('-', 0) for the stragglers. Three passes over a whole frame, each narrowing the mess.

Bamboo Weekly #66: Pittsburgh shows why whole-frame regex exists. The city's 311 data records a dozen variations on one reporting channel, and .replace(r'^Report2Gov.*', 'Report2Gov', regex=True) collapses them all before the pivot_table ever runs — which is much easier than fixing the pivot afterward.

Bamboo Weekly #77: Paris Olympics uses the dict form on a slice of columns, .replace({'': 'other', '-': 'other'}), to gather two kinds of blank into one label before grouping by continent.

Practice it

Work through a .replace() exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/replace/

Go deeper

Once regex=True is switched on, most of the difficulty is regular expressions rather than Pandas. If patterns are still a mystery, my free 14-part crash course is at RegexpCrashCourse.com.

Three pages sit next to this one: str.replace for substrings, map for total translations, and fillna for missing values. astype is usually the next call after this one.

More Pandas videos on Python and Pandas with Reuven Lerner.

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

.str.replace() — when the thing you want to change is inside a string rather than the whole value
.map() — when every value should go through the same lookup or function

See it on real data

Below are the 60 Bamboo Weekly exercises that use replace on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.

pandas replace