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

Map Free-Text Job Titles to Standard Categories

Turn hundreds of free-text job titles from an HR export or a contact list into a handful of standard job families and seniority levels with pandas: normalise the text, expand abbreviations, apply an ordered keyword rule table kept in Excel, fall back to fuzzy matching against known titles, separate seniority from function, and report every title that still needs a human decision.
Data cleaningJoins, lookups & comparisons

A headcount report by job family needs every employee in a family: Finance, Engineering, Sales, Operations. The HR export, though, has a free-text title field, and three hundred people have two hundred different titles — Sr. Software Eng., Software Engineer II, Snr Developer, Head of FP&A, Accounts Payable Clerk, Warehouse op. A pivot table on that column is useless. Mapping titles to standard categories by hand works once and then rots, because new titles keep arriving. A small rule-based mapper in pandas does the bulk of the work, is easy to correct, and says clearly which titles it could not place. This page builds one: it normalises the text and expands common abbreviations, applies an ordered table of keyword rules that lives in an Excel sheet the HR team can edit, falls back to fuzzy matching against titles mapped before, pulls seniority out as a separate column, and lists whatever is left for a person to decide. It belongs to Cleaning Excel Data with pandas.

Prerequisites

Bash
pip install pandas openpyxl

Two inputs: the staff list with a free-text Title column, and a rules sheet with one row per rule — a priority, a regular expression and the job family it maps to. Fuzzy matching uses the standard library's difflib, so nothing else needs installing.

Step 1 — Normalise the text first

Python
import re
import pandas as pd

staff = pd.read_excel("staff.xlsx")
ABBREV = {r"\bsr\b|\bsnr\b": "senior", r"\bjr\b|\bjnr\b": "junior", r"\beng\b": "engineer", r"\bmgr\b": "manager",
          r"\brep\b": "representative", r"\bop\b": "operative", r"\bvp\b": "vice president",
          r"\bchief financial officer\b": "cfo"}

def normalise(title):
    t = title.lower()
    t = re.sub(r"[.\-–,/]", " ", t)                    # punctuation to spaces (keep & for fp&a)
    for pattern, full in ABBREV.items():
        t = re.sub(pattern, full, t)
    return re.sub(r"\s+", " ", t).strip()

staff["Normalised"] = staff["Title"].map(normalise)
print(staff[["Title", "Normalised"]].head(8).to_string(index=False))

Every later step is simpler if the titles are in one consistent form. Lower-casing, turning punctuation into spaces and collapsing runs of spaces deal with Sr. versus sr and Warehouse op. versus warehouse op. A small dictionary expands the abbreviations people actually use — Sr, Snr, Eng, Mgr, Rep, op — with word boundaries so op inside operations is not touched. Long titles that have well-known short forms, such as Chief Financial Officer, can be mapped to a single token that a rule can match. The normalised title is kept as a separate column; the original stays as it was for the report.

Step 2 — Seniority as its own column

Python
SENIORITY = [(r"\b(chief|cfo|vice president|head of|director)\b", "Executive"),
             (r"\b(senior|lead|principal|manager|supervisor)\b|\bii\b|\biii\b", "Senior"),
             (r"\b(junior|graduate|assistant|trainee|clerk|coordinator)\b", "Junior")]

def seniority(t):
    for pattern, level in SENIORITY:
        if re.search(pattern, t):
            return level
    return "Mid"

staff["Seniority"] = staff["Normalised"].map(seniority)
print(staff["Seniority"].value_counts().to_dict())

Job titles mix two things: the function (software, payroll, warehouse) and the level (senior, head of, junior). Separating them gives two clean dimensions for reporting — headcount by family, and by level within each family — and stops the family rules from needing a variant for every level. The seniority rules are checked in order, executive first, so Head of Sales is an executive even though it also contains no senior. Anything without a level word is treated as Mid. Grade suffixes such as II and III count as senior here; organisations with formal grade ladders usually map those to their own levels instead, which is another short rule list.

From a free-text title to family and level A title is normalised, abbreviations expanded, then seniority is taken from level words. The family comes from the first matching keyword rule in priority order; if none matches, the title is compared with titles mapped before; if that is not close enough, it goes to the review list. Free-text title e.g. Sr. Software Eng. Normalise senior software engineer Seniority Senior Keyword rules → Engineering Fuzzy match / review if no rule fits

Step 3 — Ordered keyword rules from an Excel sheet

Python
rules = pd.read_excel("title_rules.xlsx").sort_values("Priority")
compiled = [(re.compile(p), fam) for p, fam in zip(rules["Pattern"], rules["Family"])]

def family_by_rule(t):
    for rx, fam in compiled:
        if rx.search(t):
            return fam
    return None

staff["Family"] = staff["Normalised"].map(family_by_rule)
staff["Method"] = staff["Family"].notna().map({True: "rule", False: None})
print(staff.groupby("Family", dropna=False).size().to_dict())

The rules live in an Excel sheet — priority, pattern, family — because they are business knowledge that HR, not a developer, should maintain. The first rule that matches wins, which is why priority matters: data engineer belongs to Engineering, so the Engineering rule comes before the Data & Analytics rule that would otherwise claim anything with data in it. Patterns use word boundaries so hr matches HR Manager but not three. Reading the rules from the sheet at run time means a correction is an edit to the sheet and a rerun, with no code change. In this staff list the rules place 26 of the 31 titles straight away; the other five — an office manager, a facilities assistant, a customer success manager, a product designer and a brand ambassador — match no keyword and go on to the next step.

Rule order caught this example out the first time it was run. With Finance at priority 3 and Sales at 4, Account Executive and Key Account Manager both landed in Finance, because the Finance pattern includes account for Accounts Payable and it was checked first. Swapping the two priorities — the specific account executive and account manager before the general account — put them in Sales without touching any pattern. That is the typical maintenance task for a rule table: when a title lands in the wrong family, look for an earlier, broader rule that claimed it.

Why rule order matters With the broad Finance rule first, Account Executive and Key Account Manager match the word account and are mapped to Finance. With the specific Sales rule first, they match account executive and account manager and are mapped to Sales, while Accounts Payable Clerk still reaches Finance. Finance rule first Account Executive → Finance Key Account Manager → Finance wrong, via the word account Sales rule first Account Executive → Sales Key Account Manager → Sales Accounts Payable Clerk → Finance

Step 4 — Fuzzy matching for the rest, then a review list

Python
import difflib

known = {"office manager": "Facilities", "facilities assistant": "Facilities",
         "customer success manager": "Sales", "product designer": "Engineering"}     # titles mapped in earlier runs

def family_by_similarity(t, cutoff=0.85):
    match = difflib.get_close_matches(t, list(known), n=1, cutoff=cutoff)
    return (known[match[0]], match[0]) if match else (None, None)

todo = staff["Family"].isna()
found = staff.loc[todo, "Normalised"].map(family_by_similarity)
staff.loc[todo, "Family"] = found.map(lambda x: x[0])
staff.loc[todo & staff["Family"].notna(), "Method"] = "similar to known title"
review = staff[staff["Family"].isna()][["Employee", "Title", "Normalised", "Seniority"]]

print(staff[["Title", "Family", "Seniority", "Method"]].to_string(index=False))
print("for review:", review["Title"].tolist())

Titles no rule matches are compared with a dictionary of titles that were mapped in earlier runs — the institutional memory of the mapper — using difflib.get_close_matches, which scores how similar two strings are. Only close matches count: at a cutoff of 0.85, customer success manager matches the known title of the same name exactly, while an unfamiliar title matches nothing. The result records how each title was mapped — by rule or by similarity — which matters when someone asks why a person is counted in a family. Whatever is still unmapped goes on a review list — here a single title, Brand Ambassador, which no rule covers and which resembles no known title closely enough. Each decision taken there is added either as a rule, if it generalises, or as a known title, so the review list shrinks run by run.

A sample of mapped titles Sr. Software Eng. maps to Engineering at Senior level by rule. Head of FP&A maps to Finance at Executive level. Warehouse op. maps to Operations. Customer Success Mgr maps to Sales by similarity to a known title. Shift Supervisor - Warehouse maps to Operations at Senior level. A B C D 1 Title Family Seniority Method 2 Sr. Software Eng. Engineering Senior rule 3 Head of FP&A Finance Executive rule 4 Warehouse op. Operations Mid rule 5 Customer Success Mgr Sales Senior similar to known title 6 Shift Supervisor - Warehouse Operations Senior rule

Write the mapping and the review list

The output workbook should hold three things: the staff list with the original title, family, seniority and method; the review list; and a copy of the rules used, so a report can always be traced back to the rule set that produced it. Writing them with pd.ExcelWriter to separate sheets takes a few lines. When the mapping feeds a recurring headcount report, as in Build a Headcount and Attrition Report in Excel with Python, run the mapper first and stop the report if the review list is longer than an agreed threshold — a report that silently drops a dozen people into Other is worse than one that waits a day.

Common pitfalls

  • Matching raw titles. Normalise case, punctuation and abbreviations first.
  • Rules in the wrong order. Put specific rules (data engineer) before general ones (data).
  • Patterns without word boundaries. hr must not match three.
  • Mixing level and function. Extract seniority separately from the job family.
  • Silent "Other". Report unmapped titles and feed decisions back into the rules.

Conclusion

To map free-text job titles to standard categories, normalise them and expand abbreviations, take seniority from level words as its own column, apply an ordered table of keyword rules kept in Excel so the business can maintain it, fall back to close matches against titles mapped before, and send the remainder to a review list. Record how each title was mapped, and turn every review decision into a rule or a known title so the mapper improves with each run.

Frequently asked questions

How do I standardise job titles in pandas? Normalise the text, then apply an ordered list of regular-expression rules mapping keywords to job families, falling back to fuzzy matching for the rest.

How do I handle abbreviations like Sr and Mgr? Expand them with word-boundary regular expressions before matching, for example \bsr\b to senior.

How do I fuzzy match job titles without extra libraries? Use difflib.get_close_matches against a list of known titles, with a high cutoff such as 0.85.

Why keep the mapping rules in Excel? So the people who know the job families can correct and extend them without changing code.

More on data cleaning

More on data cleaning →