Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Replace Values in an Excel Column with pandas

Recode values in an Excel column with pandas — replace versus map, mapping tables kept in a workbook, regex replacements, per-column rules, numeric bands with case_when, and a report of values that no rule covered.
Data cleaningpandas

Excel exports carry codes and spellings that are not what the report should show: region "N" in one system and "north" in another, status 1, 2 and 3 instead of Open, Closed and Cancelled, "n/a" and "-" typed in place of blanks. Recoding these is one of the most common cleaning jobs, and pandas offers several tools that look alike but behave differently when a value is not in your list. This guide, part of Cleaning Excel Data with pandas, shows which one to use, how to keep the mapping in a workbook where business users can maintain it, and how to catch the values nobody planned for.

replace and map treat unlisted values differently With the mapping N, north, S and W, replace turns known codes into region names and leaves E with a trailing space and X unchanged. map turns the same known codes into names but returns missing values for E with a space and X, which makes the gaps visible. Series.replace(mapping) N and north become North S becomes South, W becomes West "E " stays "E " X stays X, silently Series.map(mapping) N and north become North S becomes South, W becomes West "E " becomes NaN X becomes NaN, visibly

Prerequisites

Bash
pip install pandas openpyxl

The examples use a small orders sheet with the usual problems:

Python
import numpy as np
import pandas as pd

orders = pd.DataFrame({
    "order": [101, 102, 103, 104, 105, 106],
    "region": ["N", "north", "S", "E ", "W", "X"],
    "status": [1, 2, "2", 3, 1, 9],
    "notes": ["n/a", "ok", "N/A", "-", "ok", "tbc"],
    "amount": [120, 45, 980, 15, 300, 60],
})
orders.to_excel("orders.xlsx", index=False)
df = pd.read_excel("orders.xlsx")

Step 1 — Corrections with replace

Series.replace with a dictionary changes the listed values and leaves every other value alone. That makes it the right tool for fixing known mistakes in a column that is otherwise fine:

Python
fixes = {"N": "North", "north": "North", "S": "South", "E": "East", "W": "West"}
print(df["region"].replace(fixes).tolist())
# ['North', 'North', 'South', 'E ', 'West', 'X']

Two values slipped through. "E " has a trailing space, so it does not equal "E"; replace matches whole cell values exactly. And "X" is not in the dictionary at all, so it is left as it was — silently. For corrections that is what you want. For a recode, where every value must end up in the new scheme, it hides problems.

Step 2 — Complete recodes with map

Series.map looks every value up and returns NaN for anything missing. Normalise the text first so that trivial differences in spacing and case do not count as missing:

Python
key = df["region"].str.strip().str.lower()
regions = {"n": "North", "north": "North", "s": "South", "e": "East", "w": "West"}
df["region_name"] = key.map(regions)
print(df["region_name"].tolist())
# ['North', 'North', 'South', 'East', 'West', nan]

Now the only gap is the genuinely unknown code, "X", and it shows up as a missing value you can count and report. Normalising text before comparing is covered in depth in Strip Whitespace and Normalise Text Columns with pandas.

Step 3 — Keep the mapping in a workbook

Hard-coded dictionaries go stale. When the codes belong to the business — region codes, product families, cost centres — keep the mapping in a sheet the owners can edit, and load it at run time:

Python
mapping = pd.DataFrame({
    "code": ["n", "north", "s", "e", "w", "s"],
    "region": ["North", "North", "South", "East", "West", "Southern"],
})
mapping.to_excel("region_map.xlsx", index=False)

table = pd.read_excel("region_map.xlsx", dtype=str)
table["code"] = table["code"].str.strip().str.lower()
dupes = table[table["code"].duplicated(keep=False)]
if not dupes.empty:
    print("conflicting rules:\n", dupes)
lookup = table.drop_duplicates("code", keep="first").set_index("code")["region"]
df["region_name"] = key.map(lookup)

map accepts a Series directly, so there is no need to build a dictionary. The duplicate check matters: someone has added a second rule for "s", and dict(zip(...)) would quietly keep the last one. Report conflicts and decide which rule wins rather than letting row order decide.

Recoding a column from a mapping sheet Read the mapping sheet as text, normalise the codes, report duplicate codes, map the normalised column through the lookup, and list values that received no mapping before the file is written. Read mapping sheet dtype=str Normalise codes strip and lower Check duplicates conflicting rules Map the column key.map(lookup) Report unmapped before writing

Step 4 — Report the values no rule covered

After a recode, list the source values that ended up empty, with counts, so the mapping sheet can be extended:

Python
unmapped = df.loc[df["region_name"].isna(), "region"].value_counts()
print(unmapped.to_dict())                 # {'X': 1}
if len(unmapped):
    unmapped.rename("rows").to_frame().to_excel("unmapped_regions.xlsx")

Write the report next to the output, or fail the run if the column must be complete. Either way, the owner of the mapping sees exactly which codes to add — far better than discovering a blank region in a published report.

Step 5 — Match the key type to the column type

The status column in the workbook mixes real numbers with a text cell, "2". By default read_excel infers a type for each column and turns numeric-looking text into numbers, so a plain read gives integers and an integer-keyed dictionary works. Many pipelines, though, read code columns with dtype=str to protect leading zeros in IDs — and then every key misses, without an error:

Python
status_names = {1: "Open", 2: "Closed", 3: "Cancelled"}
as_text = pd.read_excel("orders.xlsx", dtype={"status": str})["status"]
print(as_text.replace(status_names).tolist())
# ['1', '2', '2', '3', '1', '9']
status = pd.to_numeric(as_text.str.strip(), errors="coerce").astype("Int64")
print(status.map(status_names).tolist())
# ['Open', 'Closed', 'Closed', 'Cancelled', 'Open', nan]

Python treats 2 and "2" as different values, so decide on one type for the column, convert it explicitly, and write the mapping keys in that type. errors="coerce" turns anything that is not a number into a missing value, which then shows up in the unmapped report alongside the unknown status 9. If the codes are genuinely text — "007" must stay distinct from "7" — keep the column as text and write the keys as strings instead. The general problem is covered in Convert Excel Text Columns to Numbers with pandas.

Step 6 — Placeholders and partial text with regex

Placeholder text such as "n/a", "N/A", "-" and "tbc" is best turned into real missing values with one pattern. Pass regex=True and anchor the pattern so it matches whole cells only:

Python
df["notes"] = df["notes"].replace(r"(?i)^\s*(n/?a|-|tbc)\s*$", np.nan, regex=True)
print(df["notes"].tolist())               # [nan, 'ok', nan, nan, 'ok', nan]

Without ^ and $, the pattern would also match "-" inside "re-order". To change text within a cell — "Ltd." to "Limited" in company names — use Series.str.replace, which works on substrings, and pass regex=False so the dot is literal:

Python
names = pd.Series(["Acme Ltd.", "Birch Ltd", "Ltd. Holdings"])
print(names.str.replace("Ltd.", "Limited", regex=False).tolist())
# ['Acme Limited', 'Birch Ltd', 'Limited Holdings']

Step 7 — Different rules per column

DataFrame.replace accepts a nested dictionary keyed by column name, so one call can apply separate rules to separate columns without touching the others:

Python
cleaned = df.replace({
    "region": {"N": "North", "north": "North"},
    "notes": {"ok": "OK"},
})
print(cleaned[["region", "notes"]].head(2).values.tolist())
# [['North', nan], ['North', 'OK']]

This is safer than a flat dictionary on the whole frame, which would also replace a stray "N" in any other text column.

Step 8 — Numeric bands with case_when

Replacing ranges of numbers — order size bands, age groups — is not a lookup at all. Series.case_when (pandas 2.2 and later) applies conditions in order, like nested IF formulas in Excel:

Python
amount = df["amount"]
df["size"] = pd.Series("large", index=df.index).case_when([
    (amount < 50, "small"),
    (amount < 500, "medium"),
])
print(df["size"].tolist())   # ['medium', 'small', 'large', 'small', 'medium', 'medium']

The first matching condition wins, so list them from narrowest to widest. Excel-style IF logic in general is covered in Excel IF Formulas as pandas Conditional Columns.

Which pandas tool replaces the values you have Fix a few known mistakes with replace. Recode every value with map and report the missing ones. Replace text inside cells with str.replace. Turn placeholder text into missing values with an anchored regex. Assign numeric bands with case_when. What kind of replacement is it? fix known mistakes replace others untouched recode every value map gaps become NaN text inside cells str.replace regex=False number ranges case_when first match wins

Common pitfalls

  • Expecting chained rules. replace({"A": "B", "B": "C"}) turns A into B and B into C in a single pass; it does not turn A into C. If you need chaining, call replace twice.
  • Replacing on the whole frame. A flat dictionary passed to DataFrame.replace changes matching values in every column. Target columns explicitly.
  • Unanchored regexes. A pattern without ^…$ replaces fragments of longer values — and with regex=True on replace, only the matched part is substituted.
  • Case-only differences. "North" and "NORTH" are different keys. Lower-case both the column and the mapping codes, as in Step 2, rather than listing every spelling.
  • Leaving the result as text. A recoded status column read by another tool can still be sorted alphabetically. Use an ordered categorical when the order matters.

Conclusion

Use replace for corrections that should leave everything else alone, map for complete recodes where a missing rule must show up as a gap, str.replace for text inside cells and case_when for numeric bands. Normalise text and types before matching, keep business mappings in a workbook with a duplicate check, and always report the values no rule covered before the output is written.

Frequently asked questions

What is the difference between replace and map in pandas? replace changes only the values that appear in your dictionary and leaves everything else untouched. map looks every value up and returns NaN for anything missing from the dictionary. Use replace for corrections and map for complete recodes where unmapped values should stand out.

Why did replace not change part of a string? Series.replace matches whole cell values by default. To replace text inside a cell, use Series.str.replace, or pass regex=True to replace with a pattern.

Does replace apply rules one after another? No. All the pairs in one dictionary are applied at the same time to the original values, so {"A":"B","B":"C"} turns A into B and B into C, not A into C.

How do I replace values in only some columns? Pass a nested dictionary to DataFrame.replace, keyed by column name, or call replace on each column you want to change.

More on data cleaning

More on data cleaning →