Fix the Capitalisation of Names with Exceptions in pandas

Customer lists collect names in every possible case. One system exports everything in capitals — MARY O'BRIEN, JOHN MCDONALD — a web form keeps whatever people typed — anna-lena schmidt — and the CRM holds the correctly written Ludwig van Beethoven. Mail merges and reports need consistent, correct capitalisation, and pandas' obvious tool, str.title(), gets the hard cases wrong: it produces O'Brien as O'Brien only by luck, turns McDonald into Mcdonald, van into Van, III into Iii and ACME UK LLC into Acme Uk Llc. This page writes a small name-casing function with explicit rules for these cases, applies it only to values that are clearly in the wrong case, and produces a list of every change for a person to skim before the data is used. It belongs to Cleaning Excel Data with pandas.

Prerequisites

pip install pandas openpyxl

contacts.xlsx lists twelve contacts with a person's name and a company name, in a mix of capitals, lower case and correct mixed case, including names with apostrophes, Mc and Mac prefixes, hyphens, particles, a generational suffix, accented letters and one "Surname, Forename" entry.

Step 1 — See what str.title gets wrong

import pandas as pd

df = pd.read_excel("contacts.xlsx")
df["title()"] = df["Name"].str.title()
print(df[["Name", "title()"]].to_string(index=False))

str.title() capitalises the first letter after every non-letter, which is right for most names and wrong in predictable ways: Mcdonald instead of McDonald, Van Gogh where Dutch usage writes "van Gogh", Iii for the Roman numeral, and D'Arcy correct only because the apostrophe happens to start a new "word". Worse, it damages names that were already right: Ludwig van Beethoven becomes Ludwig Van Beethoven. Any fix must therefore do two things — apply better rules, and leave correctly cased names alone.

Step 2 — Decide which values to touch

A name written in mixed case — some capitals, some lower case — was almost certainly typed deliberately, and the person knows how their name is spelt. Only values that are entirely upper case or entirely lower case are candidates for fixing:

def needs_fixing(text):
    letters = [ch for ch in str(text) if ch.isalpha()]
    return bool(letters) and (all(ch.isupper() for ch in letters) or all(ch.islower() for ch in letters))

df["fix?"] = df["Name"].map(needs_fixing)
print(df[["Name", "fix?"]].to_string(index=False))

Ludwig van Beethoven is mixed case and is left exactly as it is. José ÁLVAREZ is mixed too — the first name is fine, the surname is shouting — so the whole-value test leaves it alone; Step 4 shows a per-word refinement for such cases. Testing letters only, with str.isupper and str.islower, means punctuation, digits and accented letters are handled correctly: Á is upper case, é lower case.

Step 3 — A casing function with explicit rules

Lower-case the value, then rebuild each word with rules for the known exceptions. Words are split on spaces, and each word is further split on hyphens and apostrophes so anna-lena and o'brien are handled piece by piece:

import re

PARTICLES = {"van", "von", "der", "den", "de", "la", "le", "da", "di", "du", "del", "dos", "das", "ter"}
SUFFIXES = {"ii": "II", "iii": "III", "iv": "IV", "jr": "Jr", "sr": "Sr"}
NOT_MAC = {"mack", "macy", "mace", "machin", "macho"}

def case_piece(piece):
    if not piece:
        return piece
    low = piece.lower()
    if low.startswith("mc") and len(low) > 2:
        return "Mc" + low[2].upper() + low[3:]
    if low.startswith("mac") and len(low) > 4 and low not in NOT_MAC:
        return "Mac" + low[3].upper() + low[4:]
    return low[0].upper() + low[1:]

def case_name(text):
    words = str(text).lower().split()
    out = []
    for i, w in enumerate(words):
        core = w.rstrip(",.")
        tail = w[len(core):]
        if core in SUFFIXES:
            out.append(SUFFIXES[core] + tail)
        elif core in PARTICLES and 0 < i < len(words) - 1:
            out.append(core + tail)
        else:
            parts = re.split(r"([-'’])", core)
            out.append("".join(case_piece(p) if p not in "-'’" else p for p in parts) + tail)
    return " ".join(out)

df["Name fixed"] = [case_name(n) if needs_fixing(n) else n for n in df["Name"]]
print(df[["Name", "Name fixed"]].to_string(index=False))

MARY O'BRIEN becomes Mary O'Brien, john mcdonald John McDonald, ANNA-LENA SCHMIDT Anna-Lena Schmidt, and HENRY FORD III Henry Ford III. Particles stay lower case between other words — VINCENT VAN GOGH becomes Vincent van Gogh, maria de la cruz Maria de la Cruz — but are capitalised at the start, so DE SOUZA, PAULO becomes De Souza, Paulo. The Mac rule is deliberately cautious: Sarah MacIntyre is cased as Mac + capital, but DEREK MACK stays Mack, because "mack" is on the exception list. No rule set is perfect — some families write MacIntyre, others Macintyre — which is why the next step lists every change for review.

Step 4 — Per-word repair for half-shouted names

José ÁLVAREZ is mixed case overall but has one shouting word. Applying the test per word fixes only the offending part:

def case_name_per_word(text):
    words = str(text).split()
    return " ".join(case_name(w) if needs_fixing(w) and len(w) > 1 and w.isupper() else w for w in words)

df["Name fixed"] = [case_name(n) if needs_fixing(n) else case_name_per_word(n) for n in df["Name"]]
print(df.loc[df["Name"].str.contains("José"), ["Name", "Name fixed"]].to_string(index=False))

Only words written entirely in capitals inside an otherwise mixed-case value are recased, so José Álvarez is repaired while Ludwig van Beethoven remains untouched — its words are already mixed or lower case. Single capital letters such as middle initials are left alone. This refinement is optional; it suits sources where people tend to type their surname in capitals, a common habit on forms.

Step 5 — Companies: suffixes and acronyms

Company names add legal forms and acronyms that must stay in capitals — LLC, UK, GmbH, PLC — and connector words that are conventionally lower case:

UPPER = {"llc", "uk", "us", "plc", "it", "bv", "sa", "sl", "ag", "nv"}
FIXED = {"gmbh": "GmbH", "ltd": "Ltd", "co": "Co", "inc": "Inc"}
LOWER = {"and", "of", "the", "&"}

def case_company(text):
    words = str(text).lower().split()
    out = []
    for i, w in enumerate(words):
        if w in UPPER:
            out.append(w.upper())
        elif w in FIXED:
            out.append(FIXED[w])
        elif w in LOWER and i > 0:
            out.append(w)
        else:
            out.append(case_name(w))
    return " ".join(out)

df["Company fixed"] = [case_company(c) if needs_fixing(c) else c for c in df["Company"]]
print(df[["Company", "Company fixed"]].to_string(index=False))

ACME UK LLC becomes Acme UK LLC, SCHMIDT GMBH Schmidt GmbH, mcdonald & sons ltd McDonald & Sons Ltd, and VAN GOGH AND CO Van Gogh and Co — the company name starts with "Van" because it is the first word, and "and" stays lower case. Reusing case_name for ordinary words means McDonald and D'Arcy get the same treatment in company names as in personal names. The acronym list will need entries for your own data — sector abbreviations, country codes — and is the part of the code to extend over time.

Step 6 — Review the changes and write them out

Never overwrite names silently. Write the original and the suggestion side by side, highlight changed rows, and let someone skim the list:

from openpyxl.styles import PatternFill

changes = df[(df["Name"] != df["Name fixed"]) | (df["Company"] != df["Company fixed"])]
with pd.ExcelWriter("contacts_cased.xlsx", engine="openpyxl") as xw:
    df[["Name", "Name fixed", "Company", "Company fixed"]].to_excel(xw, sheet_name="Contacts", index=False)
    ws = xw.sheets["Contacts"]
    for r, changed in enumerate(df.index.isin(changes.index), start=2):
        if changed:
            for c in ("B", "D"):
                ws[f"{c}{r}"].fill = PatternFill("solid", fgColor="FFF2CC")
    for col in "ABCD":
        ws.column_dimensions[col].width = 26
print(f"{len(changes)} of {len(df)} rows changed; unchanged: {list(df.loc[~df.index.isin(changes.index), 'Name'])}")

Changed rows are highlighted in the suggestion columns; only the correctly written Beethoven row is untouched. A reviewer can overwrite any suggestion they disagree with — a family that really is "Macintyre" — before the fixed columns replace the originals. Keeping the original columns also preserves the evidence of what the source system sent. Whitespace and other text clean-up that usually comes first is covered in Strip Whitespace and Normalise Text Columns with pandas.

Common pitfalls

• str.title() on everything. It breaks Mc names, particles, suffixes and correctly cased names.
• Recasing mixed-case names. People know how their names are spelt; leave them alone.
• Over-eager Mac rules. Mack, Macy and many others are not Mac + name; keep an exception list.
• Acronyms in company names. Keep a list of words that stay upper case.
• Overwriting without review. Write suggestions beside the originals.

Conclusion

Correct name capitalisation needs rules that str.title() lacks: capitals after apostrophes and hyphens, Mc and cautious Mac prefixes, lower-case particles between other words, upper-case generational suffixes, and for companies a list of acronyms and legal forms. Apply the rules only to values that are entirely upper or lower case — or per shouting word — so correctly written names are never damaged, and write the suggestions beside the originals with changed rows highlighted for a quick human review.

Frequently asked questions

Why does pandas str.title() give wrong names? It capitalises the first letter after every non-letter and lower-cases the rest, so it produces Mcdonald, Van for the Dutch particle and Iii for III, and it changes names that were already written correctly.

How do I capitalise names like O'Brien and McDonald in Python? Lower-case the name, split each word on apostrophes and hyphens, capitalise each piece, and add rules for prefixes such as Mc followed by a capital letter.

How do I avoid changing names that are already correct? Only recase values whose letters are all upper case or all lower case. Mixed-case names were probably typed deliberately.

How do I keep acronyms like LLC and UK in capitals? Keep a set of words that must stay upper case and check each word against it before applying the general rule.

Related

• Up one level: Cleaning Excel Data with pandas — tidy data before analysis.
• Split Full Names into First and Last Names with pandas — the next step for many name columns.
• Remove Accents and Normalise Unicode in Excel Text — accents and look-alike characters.
• Find Near-Duplicate Names Within One Excel Column — after case is fixed, duplicates show up.