Text standardisation

This is the exact file agents read, including the credit header and licence notice. Save it as text-standardisation/SKILL.md.

---
name: text-standardisation
description: Standardise inconsistent text and categorical values so the same real thing becomes one value, across pandas, SQL and R. Covers trimming and collapsing whitespace, case folding, punctuation and accent (Unicode) normalisation, mapping variants to a canonical list, and fuzzy matching near-duplicate categories with similarity thresholds and human review for the uncertain band. Use it when someone says "standardise this column", "clean up these categories", "USA and U.S. and United States should be one", "inconsistent spelling", "fix the casing", "strip whitespace", "group similar text", "fuzzy match these labels", or "my group-by has too many groups". Do NOT use it for finding duplicate rows or linking records (see duplicate-detection), for converting the cleaned column to another type (see data-type-conversion), for parsing dates (see date-parsing), or for filling missing values (see handling-missing-data).
license: Free to use and adapt with credit to Afeez Adebayo (https://afeezadebayo.com). Link back to this skill at https://afeezadebayo.com/skills-md/data/data-cleaning-preparation/text-standardisation/ and to https://www.linkedin.com/in/afeez-adebayo/.
---

# Text standardisation

## 1. When to use this skill, and when not to

Use this skill when one text or categorical column holds several spellings of the same thing, and a group-by, a join or a filter is splitting what should be one value. The signs are a count distinct that is larger than the real number of categories, a bar chart with "Lagos", "lagos " and "Lagos " as three bars, or a join that misses rows because one side says "United Kingdom" and the other says "UK".

The job is to reduce the variants to a single canonical value per real thing, safely, and to send anything the rules cannot resolve to a person rather than guessing.

Do not use it for four neighbouring jobs. Finding duplicate rows or linking records that describe the same entity across a table belongs in duplicate-detection; this skill cleans the values inside one column, it does not decide which rows are the same record. Converting the cleaned column to a number, boolean or category belongs in data-type-conversion, and runs after this. Parsing dates belongs in date-parsing. Filling blanks belongs in handling-missing-data.

## 2. Inputs the agent must have before starting

Ask for anything missing. Do not merge values on assumption, because a merge is hard to reverse once the original is gone.

- The tool and version, and whether fuzzy-matching libraries (thefuzz, RapidFuzz) are available or must be installed.
- The canonical list, if one exists. A column of country, currency or status codes usually has an agreed set of valid values. Standardising towards a known list is safer than clustering blind.
- The meaning of case and punctuation for this column. For a country name, case does not matter; for a product code or a person's name, it can. Confirm before case-folding.
- How bold to be. A conservative pass fixes only exact-after-cleaning matches; an aggressive pass merges near-duplicates by similarity and needs a review step.
- Whether to overwrite the column or add a standardised one alongside. The default is to add a new column and keep the original until the person confirms.

If no canonical list exists, propose one from the most frequent values and confirm it before mapping the rest towards it.

## 3. The standardisation workflow, step by step

1. Profile the column first. List the distinct values with their counts, highest first. The frequent values are usually the canonical forms; the long tail of low-count values is where the variants and typos hide. Record how many distinct values there are before you start, so you can show the reduction afterwards.

2. Apply deterministic cleaning before any fuzzy step, in this order, because each one raises the match rate of the next.
   - Trim leading and trailing whitespace, then collapse runs of internal whitespace to a single space.
   - Normalise Unicode to a single form (NFKC), so visually identical characters, accents and full-width variants compare as equal.
   - Case-fold for comparison. Use case folding rather than a plain lower-case for international text. Keep a separately cased display version if the column is a name.
   - Standardise punctuation: remove or unify stray full stops, commas and hyphens ("U.S." and "US" should meet).
   - Expand or remove known tokens: legal suffixes ("Inc", "Ltd", "LLC"), common abbreviations, and agreed stop tokens. Do this with a reviewed list, never a generic stop-word list, because removing a word like "not" can change the meaning.
   These steps alone typically resolve a large share of the variants and, per practitioner guidance, can lift later match rates by roughly 15 to 25%.

3. Map against the canonical list. Build a dictionary from each cleaned variant to its canonical value and apply it. Every value that maps is resolved with certainty. Collect the values that do not map; they are the candidates for fuzzy matching, not the whole column.

4. Fuzzy-match only the unresolved values, and only against the canonical list, to keep it from comparing every value with every other (which is O(N times M) and does not scale). For a large set, block first: compare only values that share a cheap key, such as the first character or a phonetic code, which cuts the comparison count by roughly 90 to 95%.

5. Choose the scorer for the kind of difference you expect.
   - Reordered words ("Adebayo Afeez" versus "Afeez Adebayo"): a token-sort score.
   - Extra words or a substring ("Apple" inside "Apple Incorporated"): a partial or token-set score.
   - Small typos and reformatting: a plain edit-distance ratio.
   If you are unsure which scorer fits, a token-set score is the most forgiving default, because it compares the shared words regardless of order or extra tokens; just watch it on short strings, where accidental word overlap can push the score up.

6. Act on the score with three bands, never a single cut-off.
   - At 90 and above, accept the match automatically.
   - Below 60, reject it; a match this weak corrupts the data silently.
   - Between 60 and 89, send it to a person to confirm. This middle band is where the real decisions live, and it is also where near-miss false positives sit: "Austria" and "Australia" score about 88, "Apple Inc" and "Snapple Inc" about 90, yet they are different things. A high score is evidence, not proof.

7. Handle semantic equivalents that character similarity cannot catch. "MSFT", "Microsoft" and "Microsoft Corporation" do not look alike by edit distance. Resolve these with an alias dictionary you maintain, or, for a large open set, a text-embedding similarity, and still route the uncertain ones to review.

8. Flag, propose, confirm, then apply. Produce the proposed mapping as a table (original value, count, proposed canonical, score, band) for the person to approve. Apply the mapping to a new standardised column, keep the original, and never overwrite or merge values in place before the mapping is confirmed, because a wrong merge cannot be undone from the merged result.

9. Save the mapping so the next batch is consistent. The approved dictionary of variant to canonical value is the real asset, not the one cleaned file. Store it alongside the data (a small CSV or JSON) and reuse it on the next load, so the same spellings resolve the same way every time and the review work is not repeated. Add new variants to the saved mapping as they appear, rather than re-clustering from scratch, and treat the canonical list as the single source of truth for that column.

10. Verify and report. After applying, show the new distinct count against the old, the number of values resolved by exact mapping versus fuzzy, the number sent to review, and a sample of the merges. State it plainly: "country reduced from 214 distinct values to 38; 180 resolved by the canonical map, 26 by fuzzy match at 90+, 8 sent to you for review." Then spot-check the largest merged groups by eye, because the biggest groups carry the most rows and so the most risk if a merge was wrong.

## 4. Worked example, end to end

A sign-up table has a `country` column with 214 distinct values across 60,000 rows, against a real list of about 40 countries.

Profiling shows the top values are clean ("United Kingdom", "Nigeria", "United States"), and the tail holds "UK", "U.K.", "united kingdom ", "Great Britain", "USA", "U.S.", "Naija" and dozens of typos. The deterministic pass runs first:

```python
canon_key = (df["country"].astype("string")
             .str.normalize("NFKC")
             .str.strip()
             .str.replace(r"\s+", " ", regex=True)
             .str.replace(r"[.]", "", regex=True)
             .str.casefold())
```

A reviewed alias map resolves the known variants with certainty:

```python
aliases = {"uk": "United Kingdom", "great britain": "United Kingdom",
           "usa": "United States", "us": "United States",
           "naija": "Nigeria"}
df["country_std"] = canon_key.map(aliases)
resolved_exact = df["country_std"].notna().sum()
```

Values still unmapped go to fuzzy matching against the 40 canonical names only, with a token-sort scorer. Matches at 90 or above are accepted; "Austria" against "Australia" scores about 88 and lands in the 60 to 89 review band, where it belongs, so it is not merged automatically. The 8 values that reach review include one genuine oddity, "Antarctica", which is a real answer, not a typo. The handover shows the distinct count falling from 214 to 38, the split between exact and fuzzy resolution, and the review list, with the original column untouched.

## 5. Templates you can use verbatim

Deterministic cleaning key (pandas):

```python
key = (s.astype("string").str.normalize("NFKC").str.strip()
        .str.replace(r"\s+", " ", regex=True).str.casefold())
```

Proposed-mapping table prompt:

> Produce a table of every value that did not match the canonical list exactly: original value, row count, best canonical candidate, similarity score, and band (accept 90+, review 60 to 89, reject under 60). Do not apply anything yet. I will confirm the review band.

Review-band rule:

> Auto-apply matches scoring 90 or above. Reject anything under 60. For 60 to 89, list them for me with both strings side by side and wait for my decision. Never merge two values that could be different real things (for example two similar place names) without confirmation.

## 6. Failure modes and anti-patterns

- Fuzzy-matching with one low threshold and applying it blind. Scores in the 60 to 74 band merge distinct entities, and the data is quietly corrupted. Spot it when the distinct count collapses further than the real number of categories. Use the three bands and a review step.
- Trusting a high score as proof of sameness. "Austria" and "Australia" score about 88; a blind accept at 85 merges two countries. Spot it by reading the proposed merges before applying. Keep the review band and check near-misses by eye.
- Using a generic stop-word list. Removing a word like "not" or "no" changes the meaning of the value, so "not supplied" and "supplied" collapse together. Spot it when opposite categories merge. Use a reviewed, task-specific token list.
- Case-folding a column where case carries meaning. Product codes and some names depend on case, and folding them loses information. Spot it when distinct codes merge. Keep a cased display version and fold only the comparison key.
- Overwriting the original column before the mapping is confirmed. Once "USA" and "US" are both "United States" in place, you cannot recover which rows were which, and a wrong merge is permanent. Always write a new column and confirm first.

## 7. Quality bar before handover

- The distinct count before and after is reported, and the after count is not lower than the real number of categories.
- Exact-map resolutions and fuzzy resolutions are counted separately, and fuzzy matches below 90 were confirmed by a person.
- The proposed mapping was shown and approved before any merge was applied.
- The original column is preserved; standardised values are in a new column.
- Case folding was applied to the comparison key only, not to a column where case carries meaning.
- No generic stop-word list was used; any token removal came from a reviewed list.
- Values that could not be resolved are listed for a human, not silently assigned.

## 8. Guardrails

- Merging categories is a bulk, hard-to-reverse edit. Default to a proposed mapping and an explicit confirmation before applying, and always to a new column rather than in place.
- Never invent a canonical value. If a variant matches nothing with confidence, it goes to review; the skill does not assign it to the nearest guess.
- Do not fabricate similarity scores or thresholds. Report the actual score and the scorer used, and treat any figure as evidence for a decision, not the decision itself.
- PII: name and address columns are common standardisation targets and are personal data. Do not log the values, and do not send them to an external matching service without the person's agreement; prefer local libraries.
- Cost: deterministic cleaning is local and free. Fuzzy matching every value against every other is expensive at scale; block first and match only unresolved values against the canonical list, and reserve embeddings for the cases edit distance cannot handle.
- This skill standardises values; it does not decide the canonical set or the business meaning of a category. It proposes and surfaces those for a human.

## 9. Tool-specific appendix (pandas and libraries)

Fenced off from the tool-agnostic core above.

- Use the pandas `string` dtype, not `object`, for text cleaning, so missing values stay as `<NA>` and `.str` methods behave consistently.
- Core `.str` methods: `.str.strip()`, `.str.replace(r"\s+", " ", regex=True)` to collapse whitespace, `.str.normalize("NFKC")` for Unicode, `.str.casefold()` for case-insensitive comparison (stronger than `.str.lower()` for international text). Pass `na=False` to `.str.contains()` and `.str.startswith()` so missing values do not become unexpected matches. Pass `regex=False` to `.str.replace()` for literal swaps, which is faster and avoids accidental pattern matches.
- Fuzzy matching: RapidFuzz is a faster, C++-backed replacement for thefuzz and is worth it above a few thousand records; both expose `ratio`, `partial_ratio`, `token_sort_ratio` and `token_set_ratio`. For short strings such as personal names, Jaro-Winkler is a reasonable alternative.
- Scaling: for large sets, pre-select candidates with TF-IDF character n-grams or block on a shared key before running edit-distance scoring, and evaluate the matcher like a classifier (label a sample, measure precision and recall across thresholds).
- Compute a normalised key once and reuse it for both grouping and mapping rather than recomputing it. SQL equivalents: `TRIM`, `LOWER`, `REGEXP_REPLACE`, and a mapping table joined on the cleaned key. R equivalents: `stringr::str_squish`, `str_to_lower`, `stringi::stri_trans_nfkc`, and `stringdist` for fuzzy scoring.

## Sources

- Let's Data Science, Fuzzy Matching Guide: How to Fix Inconsistent Text Data in Python, last verified 1 November 2026: https://letsdatascience.com/blog/fuzzy-matching-guide-how-to-fix-inconsistent-text-data-in-python
- Kanaries, pandas string operations reference, last verified 1 November 2026: https://docs.kanaries.net/topics/Pandas/pandas-string-operations
- Let's Data Science, data wrangling articles (text preprocessing and category standardisation), last verified 1 November 2026: https://letsdatascience.com/blog/data/data-wrangling
- Anthropic, Equipping agents for the real world with Agent Skills, last verified 1 November 2026: https://www.anthropic.com/engineering/equipping-agents-for-the-real-world-with-agent-skills
- pandas, pandas.to_numeric API reference (for converting the standardised column afterwards), last verified 1 November 2026: https://pandas.pydata.org/docs/reference/api/pandas.to_numeric.html