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

Standardise Category Values with a Mapping Table in pandas

Turn "N. America", "north america ", "NA" and "USA" into one region name using a mapping table kept in Excel, where the people who own the categories can maintain it. Normalise keys, check the table for conflicts, report unmapped values with counts and suggestions, and append new ones for review.
Data cleaningJoins, lookups & comparisonspandas

Free-text categories drift. One region is entered as "North America", "N. America", "north america " with a trailing space, "NA", and once as "Nroth America". Every variant becomes a separate group in a pivot table, and the totals by region are wrong in a way that is easy to overlook. Fixing it with a chain of replace calls in code works once and then rots, because the people who know that "NA" means the Americas — and who will invent the next variant — are not the people who edit the code. This page, part of Cleaning Excel Data With Pandas, keeps the mapping in an Excel table the business can maintain, and builds a loop that catches every new variant.

Many spellings, one standard category Five raw region values — North America, N. America, north america with a trailing space, NA and the typo Nroth America — fan in to a single standard value, Americas. The first four are matched through the mapping table after normalising; the typo is reported with a suggestion for review. Americas the standard value North America mapped N. America mapped "north america " normalised NA mapped Nroth America suggested

Prerequisites

Bash
pip install pandas openpyxl

sales.xlsx has ten orders with ten spellings of three regions. region_mapping.xlsx has a Mapping sheet with a raw column and a standard column — the table a sales operations person would own.

Step 1 — Stop pandas turning "NA" into a missing value

Before any cleaning, look at order 4. Its region is the code NA, meaning North America — and read_excel does not return it:

Python
import re
import pandas as pd

default = pd.read_excel("sales.xlsx")
print(default.loc[3, "region"])
# nan

pandas treats a list of strings as missing by default — NA, N/A, NULL, nan, #N/A and others — and NA is on it. A region code, a country code for Namibia or a product code can disappear this way without a warning. For category columns, switch the default list off and treat only genuinely empty cells as missing, for the data and the mapping table:

Python
READ = dict(keep_default_na=False, na_values=[""])
sales = pd.read_excel("sales.xlsx", **READ)
print(sales.loc[3, "region"])
# NA

Without this, the mapping table's own NA row would also be read as missing, and a missing key would match every missing region — a coincidence that looks like success.

Step 2 — Normalise the key on both sides

Most variants differ only in case, spacing and punctuation. Normalise them to one key before looking anything up, on both the data and the mapping table, so the table needs one row per genuinely different spelling rather than one per typing habit:

Python
def key(value):
    if pd.isna(value):
        return value
    s = str(value).casefold().strip()
    s = re.sub(r"[^\w\s]", " ", s)          # punctuation to spaces: "N. America" -> "n  america"
    return re.sub(r"\s+", " ", s).strip()

print(sales["region"].map(key).value_counts().to_dict())

"North America" and "north america " now share a key, and so do "EMEA" and "emea". "Asia-Pacific" becomes asia pacific, matching the mapping table's "Asia Pacific". Normalisation is intentionally mechanical — it never decides that two different words mean the same thing. That decision belongs to the mapping table.

Step 3 — Load the mapping table and check it for conflicts

Read the table, build the same keys, and check the one thing that makes a mapping table unusable: a key that maps to two different standard values. Duplicates that agree are harmless; duplicates that disagree must be resolved by whoever owns the table:

Python
mapping = pd.read_excel("region_mapping.xlsx", sheet_name="Mapping", **READ)
mapping["key"] = mapping["raw"].map(key)

conflicts = mapping.groupby("key")["standard"].nunique()
conflicts = conflicts[conflicts > 1]
if not conflicts.empty:
    raise ValueError(f"mapping keys with more than one standard value: {list(conflicts.index)}")

lookup = mapping.drop_duplicates("key").set_index("key")["standard"]
print(lookup.to_dict())

Failing loudly on a conflict is the right behaviour: a table that says "NA" is both "Americas" and "Not applicable" cannot be applied, and guessing which row wins would silently misfile orders.

The mapping table the business maintains The Mapping sheet in region_mapping.xlsx with raw and standard columns: North America, N. America and NA map to Americas; EMEA and Europe map to EMEA; APAC and Asia Pacific map to APAC. A note explains that keys are normalised before lookup, so case and spacing variants need no extra rows. region_mapping.xlsx — Mapping A B 1 raw standard 2 North America Americas 3 N. America Americas 4 NA Americas 5 EMEA EMEA 6 Europe EMEA 7 APAC APAC 8 Asia Pacific APAC Mapping One row per spelling case and spaces are normalised first

Step 4 — Map, and let unmapped values show

Use Series.map with the lookup. Unlike replace, which leaves unknown values untouched and so hides them among the cleaned ones, map returns NaN for anything not in the table:

Python
sales["region_key"] = sales["region"].map(key)
sales["region_std"] = sales["region_key"].map(lookup)
print(sales[["order", "region", "region_std"]].to_string(index=False))
print(sales.groupby("region_std", dropna=False)["amount"].sum())

Nine of the ten orders now carry a standard region. The tenth, "Nroth America", has none, and the grouped totals show a separate missing-region line rather than quietly folding its 40 into nothing. That line is the signal that the table needs a new row.

Step 5 — Report unmapped values with suggestions

Collect the unmapped raw values with how often each occurs — a variant used a hundred times deserves attention before one used once — and suggest the closest known key with the standard library's difflib:

Python
from difflib import get_close_matches

unmapped = (sales[sales["region_std"].isna() & sales["region"].notna()]
            .groupby("region").size().rename("rows").reset_index())
unmapped["suggested_key"] = unmapped["region"].map(
    lambda r: (get_close_matches(key(r), lookup.index, n=1, cutoff=0.8) or [None])[0])
unmapped["suggested_standard"] = unmapped["suggested_key"].map(lookup)
print(unmapped.to_string(index=False))

"Nroth America" is suggested as north america → Americas. The suggestion is only that: a person confirms it, because the closest spelling is not always the right meaning — "Austria" is one letter from "Australia". cutoff=0.8 keeps weak matches out of the list; lower it to see more candidates.

Step 6 — Append new values to the table for review

Close the loop by writing unmapped values into the mapping workbook, in a Review sheet with the suggestion filled in and the decision left for the owner:

Python
review = unmapped.rename(columns={"region": "raw", "suggested_standard": "standard"})[["raw", "standard", "rows"]]
review["approved"] = ""

with pd.ExcelWriter("region_mapping.xlsx", mode="a", engine="openpyxl", if_sheet_exists="replace") as writer:
    review.to_excel(writer, sheet_name="Review", index=False)
print(pd.ExcelFile("region_mapping.xlsx").sheet_names)

The owner moves approved rows into the Mapping sheet, and the next run maps them. Over a few weeks the Review sheet empties: new variants still appear, but they are caught the first time. Add Dropdown Data Validation to Excel with openpyxl can put a dropdown of the allowed standard values on the Review sheet's standard column, so approvals cannot introduce a new spelling of their own.

The standardisation loop A cycle of five steps. Normalise keys in data and table. Check the table for conflicting mappings. Map values, leaving unknown ones as NaN. Report unmapped values with counts and difflib suggestions to a Review sheet. The owner approves rows into the mapping table, and the next run maps them. Normalise keys case, spaces, punctuation Check the table no conflicting rows Map unknown values become NaN Report and suggest Review sheet with counts Owner approves next run maps them

Step 7 — Fail the job when too much is unmapped

A report built on data that is largely unmapped should not go out. Set a threshold on the share of rows — or of the amount — without a standard value:

Python
share = sales["region_std"].isna().mean()
amount_share = sales.loc[sales["region_std"].isna(), "amount"].sum() / sales["amount"].sum()
print(f"{share:.0%} of rows, {amount_share:.1%} of amount unmapped")
if amount_share > 0.05:
    raise SystemExit("too much revenue has no standard region — review region_mapping.xlsx")

Here one order in ten is unmapped but it carries only 3.4% of the revenue, so the run continues and the Review sheet tells the owner what to add. Measuring by amount as well as by row count matters: one unmapped enterprise customer can carry more revenue than a hundred unmapped small orders.

Common pitfalls

  • Default NA strings. NA, N/A and NULL become missing on read; use keep_default_na=False for code and category columns.
  • replace for standardisation. It hides unknown values; use map and look at the NaNs.
  • Mapping raw spellings without normalising. The table fills with case and spacing variants.
  • Auto-applying fuzzy matches. Close spelling is not the same meaning.
  • Editing the mapping in two places. Once the table lives in Excel, remove any leftover replace calls from the code, or the two will disagree the first time someone updates only one of them.
  • Silently picking one of two conflicting rows. Fail and let the owner decide.

Performance notes

A normalised-key lookup is a dictionary lookup per row, which pandas' map performs quickly even on millions of rows; the normalisation function is the slower part and can be applied to the column's unique values only (sales["region"].drop_duplicates()), then joined back. difflib is slow on very large vocabularies but only ever runs on the handful of unmapped values.

Conclusion

Keep category mappings in an Excel table the business owns, normalise keys on both sides so the table only needs genuinely different spellings, and fail on conflicting rows. Map with Series.map so unknown values surface as NaN, report them with counts and difflib suggestions to a Review sheet for approval, and stop the job when an unacceptable share of rows or revenue is unmapped.

Frequently asked questions

Should the mapping live in code or in a spreadsheet? In a spreadsheet when the people who decide what the categories mean are not programmers. A small Excel table that the sales team can edit keeps the code stable and puts decisions with the people who own them.

What is the difference between map and replace? Series.map returns NaN for any value not in the mapping, which makes unmapped values visible. Series.replace leaves unmapped values unchanged, which hides them among the cleaned ones. For standardisation, map and then report the NaNs.

How do I match "north america " to "North America"? Normalise both sides before looking up: strip spaces, collapse repeated spaces, casefold and remove punctuation. Build the lookup on the normalised key, so the mapping table only needs one row per genuinely different spelling.

Can pandas suggest the right category for a typo? The standard library's difflib.get_close_matches finds the closest known keys, so "nroth america" suggests "north america". Use it to propose mappings for a person to accept, not to apply them automatically.

More on data cleaning

More on data cleaning →