Standardise Country Names and Codes with Python

A country column typed by people never has one spelling per country. Sales by country comes out with separate rows for "Germany", "germany", "DE", "Deutschland" and "Federal Republic of Germany"; the UK appears as "UK", "U.K.", "United Kingdom", "Great Britain" and "England"; the Netherlands as "Netherlands" and "Holland". A mapping table maintained by hand never quite keeps up. The pycountry package contains the ISO 3166 list of countries with their official names and two- and three-letter codes, so most values can be resolved by lookup, and a small override table handles the rest — nicknames, names in other languages, and countries whose official name has changed. This page builds that standardiser, uses fuzzy matching only where it is safe, and reports every value it could not resolve with confidence. It belongs to Cleaning Excel Data with pandas.

Prerequisites

pip install pandas openpyxl pycountry

orders_by_country.xlsx has 25 orders whose country was typed freehand: official names, codes, nicknames, native-language names, a renamed country, abbreviations with dots, an ambiguous "Korea", a typo and a placeholder. The examples were run with pycountry 26.2.

Step 1 — Exact lookups first

pycountry.countries.lookup matches a value exactly — case-insensitively — against official names, common names and the alpha-2 and alpha-3 codes:

import pandas as pd
import pycountry

df = pd.read_excel("orders_by_country.xlsx", keep_default_na=False)
df["clean"] = df["Country"].astype(str).str.strip()

def exact(value):
    try:
        c = pycountry.countries.lookup(value)
        return c.alpha_2
    except LookupError:
        return None

df["iso2"] = df["clean"].map(exact)
print(df[["Country", "iso2"]].to_string(index=False))
print("resolved exactly:", df["iso2"].notna().sum(), "of", len(df))

Exact lookup resolves 11 of the 25 values — "Germany", "germany", "DE", "DEU", "United Kingdom", "USA", "United States of America", "Netherlands", "Türkiye", "Czech Republic" and "france" — the Czech one because pycountry also stores former names that are still widely used. It deliberately does not guess: "UK", "U.K.", "Holland", "Deutschland", "Espana" and "Turkey" are not ISO names or codes, so they stay unresolved. "UK" in particular is not the ISO code for the United Kingdom, which is GB — a frequent source of confusion. Stripping spaces first lets " france " match. keep_default_na=False matters here: by default read_excel turns placeholder text such as "n/a" into a missing value, and "NA" — Namibia's ISO code — would disappear the same way. Reading the column exactly as typed keeps every value visible, so the placeholder can be reported rather than silently dropped.

Step 2 — An override table for what ISO does not cover

The values exact lookup misses are predictable: nicknames, parts of a country, native-language names, renamed countries and punctuation. Keep them in one override table, mapped to the ISO alpha-2 code:

OVERRIDES = {
    "uk": "GB", "u.k.": "GB", "great britain": "GB", "england": "GB", "scotland": "GB", "wales": "GB",
    "u.s.a.": "US", "u.s.": "US", "united states of america": "US", "america": "US",
    "holland": "NL", "the netherlands": "NL",
    "deutschland": "DE", "espana": "ES", "españa": "ES",
    "turkey": "TR", "ivory coast": "CI",
}

def resolve(value):
    key = value.strip().lower()
    if key in OVERRIDES:
        return OVERRIDES[key], "override"
    code = exact(value)
    return (code, "exact") if code else (None, None)

df[["iso2", "how"]] = df["clean"].apply(lambda v: pd.Series(resolve(v)))
print(df.loc[df["how"] == "override", ["Country", "iso2"]].to_string(index=False))
print("unresolved:", df.loc[df["iso2"].isna(), "Country"].tolist())

The overrides are checked before the ISO lookup, so they can also correct values that ISO would interpret differently. England, Scotland and Wales map to GB because ISO 3166 has no separate entries for them; if your business needs the distinction, use ISO 3166-2 subdivision codes (GB-ENG, GB-SCT) in a separate column instead. "Turkey" maps to TR because the country's ISO name changed to Türkiye in 2022, and older systems keep sending the former name. This table is the part of the code that grows: every new spelling reported in Step 4 gets one line here.

Step 3 — Fuzzy search, used cautiously

What remains are typos and genuinely ambiguous values. search_fuzzy returns candidates ranked by similarity; accept a suggestion only when there is exactly one candidate, and report the rest:

def fuzzy(value):
    try:
        matches = pycountry.countries.search_fuzzy(value)
    except LookupError:
        return None, "unmatched", []
    codes = [m.alpha_2 for m in matches]
    return (codes[0], "fuzzy", codes) if len(codes) == 1 else (None, "ambiguous", codes[:4])

rest = df["iso2"].isna()
for i in df.index[rest]:
    code, how, candidates = fuzzy(df.at[i, "clean"])
    df.at[i, "iso2"], df.at[i, "how"] = code, how
    print(f"{df.at[i, 'Country']!r:12} → {how:9} {candidates}")

The typo "Frnace" has no fuzzy match at all — pycountry's fuzzy search matches words and subdivision names rather than misspellings — so it is reported as unmatched. "Korea" returns two candidates, North and South Korea, and is reported as ambiguous instead of being silently assigned to the first. "n/a" is unmatched, as it should be. This conservative policy is deliberate: a wrong country is worse than a blank one, because it puts revenue in the wrong place without anyone noticing. For typos, a string-similarity match against the list of country names, as in Fuzzy Match Names Between Two Excel Files with Python, works better than search_fuzzy — and its suggestions should still go to a person for confirmation.

Step 4 — Add official names and report the leftovers

With codes resolved, add the official name and three-letter code for every row, and write the values that need attention to a review list:

def details(code):
    c = pycountry.countries.get(alpha_2=code) if isinstance(code, str) else None
    return (c.alpha_3, getattr(c, "common_name", c.name)) if c else (None, None)

df[["iso3", "country_name"]] = df["iso2"].apply(lambda c: pd.Series(details(c)))
review = df.loc[df["iso2"].isna(), ["Order", "Country", "how"]]
print(review.to_string(index=False))
print(df["how"].value_counts().to_dict())

Unresolved rows hold a missing value rather than a code, which is why the helper checks for a string before looking it up. Each resolved row now carries the ISO alpha-2 code, the alpha-3 code and a single display name — pycountry's common_name where one exists ("Bolivia" rather than "Bolivia, Plurinational State of"), otherwise the official name. The review list holds three rows: the typo, the ambiguous "Korea" and the placeholder. Once someone decides that "Frnace" is France, the fix is a line in the override table, so the same typo is handled automatically next month. The how column records whether each value was resolved by override, exact lookup or fuzzy match, which is useful evidence if a figure is questioned.

Step 5 — Summaries that finally add up

The payoff is a country summary with one row per country:

before = df.groupby("Country")["Revenue"].sum()
after = df.dropna(subset=["iso2"]).groupby(["iso2", "country_name"])["Revenue"].sum().sort_values(ascending=False)
print("groups before:", len(before), "| groups after:", len(after))
print(after.head(5).to_string())
df.drop(columns="clean").to_excel("orders_by_country_clean.xlsx", index=False)

Grouped by the raw column, 25 orders produce 25 separate "countries"; grouped by ISO code they collapse to nine real countries, with the UK and Germany each gathering five spellings into one line and the US and the Netherlands three each. Grouping by the code rather than the name is the safer habit: names change and differ by language, codes do not. Keep the original column in the output so the mapping can always be audited. The same codes are what joins to other data need: exchange rates, VAT rates, regions and sales territories are usually keyed by ISO code, so a clean code column is what makes those lookups work at all.

Common pitfalls

• Treating "UK" as an ISO code. The ISO code for the United Kingdom is GB.
• Accepting the first fuzzy match. Ambiguous values like "Korea" must go to a person.
• Forgetting renamed countries. "Turkey" is no longer the ISO name; add it to the overrides.
• Mapping England, Scotland and Wales without thinking. Use subdivision codes if the distinction matters.
• Grouping by name. Group by ISO code; display the name.

Conclusion

To standardise a country column, clean the values, check them first against a small override table for nicknames, native names and renamed countries, then resolve the rest by exact lookup in pycountry against official names and ISO codes. Use fuzzy search only when it returns a single candidate, and report ambiguous and unmatched values for review rather than guessing. Store ISO alpha-2 and alpha-3 codes alongside one display name, group summaries by the code, and keep the original values for audit.

Frequently asked questions

How do I convert country names to ISO codes in Python? Use pycountry.countries.lookup(name), which matches official names and alpha-2 and alpha-3 codes case-insensitively and returns an object with alpha_2, alpha_3 and name.

Why doesn't pycountry recognise UK or Holland? They are not ISO 3166 names or codes; the United Kingdom's code is GB and Holland is a region of the Netherlands. Map such values with an override table.

Is it safe to use fuzzy matching for country names? Only when there is a single clear candidate. Values such as Korea match several countries, and typos may not match at all, so report them for review.

Should I group sales by country name or code? By code. Country names vary by language and change over time, while ISO codes stay stable.

Related

• Up one level: Cleaning Excel Data with pandas — tidy data before analysis.
• Standardise Category Values with a Mapping Table — the general technique.
• Normalise Postcodes and ZIP Codes with pandas — the next address field.
• Localise an Excel Report for Other Languages and Locales — showing country names in other languages.