Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 76 ยท Cleaning and Preparing Data

Cleaning Text and Categories

Clean messy labels in pandas: whitespace, case and accents, fuzzy duplicates, typo mapping, rare-category grouping, and parsing messy prices, units and dates.

Free text and categories look harmless until you count them. "Lisbon", "lisbon " and "LISBON" are one city to a human and three groups to a computer. A group-by on such a column gives wrong totals, a chart shows three bars for one place, and a model treats each spelling as a separate feature. Many "interesting patterns" in raw data turn out to be spelling variants.

Cleaning text is mostly a sequence of small, boring, safe steps, followed by a few judgement calls. In this lesson you will normalize whitespace, case and accents, map typos to a reference list with fuzzy matching, group rare categories, and parse messy prices, units and dates into real numbers and datetimes. The pandas lesson on text data covers the .str methods one by one; here we put them together into a cleaning routine.

A cleaning pipeline for labels

Work from the safest step to the riskiest, and keep the original column so you can always compare:

flowchart LR
    A["Raw label"] --> B["Trim and collapse spaces"]
    B --> C["Lower case, remove accents"]
    C --> D["Strip punctuation, null markers"]
    D --> E["Map typos to canonical list"]
    E --> F["Group rare values"]
    F --> G["Clean category"]

Early steps are mechanical and safe; the later steps involve choices and need review.

Whitespace, case, accents and null markers

The mechanical steps are always safe because they change how a value is written, not what it means:

  • .str.strip() removes leading and trailing spaces (including a non-breaking space in many exports).
  • .str.lower() makes the case consistent. (Use casefold for international text where you need an exact comparison.)
  • Replace runs of spaces inside a value with one space.
  • Remove accents if you want "Sรฃo Paulo" and "Sao Paulo" to be the same key. Do this only for a matching key; keep the pretty original for display.
  • Turn placeholders such as "", "N/A", "none" and "-" into real missing values, so that isna() finds them.
Python From 16 spellings to a handful

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Sixteen labels collapse to five, and four values are now properly missing (N/A, n/a, the empty string and None). One problem is left: os lo is not the same as oslo, and no mechanical rule should fix it, because removing all spaces would also merge genuinely different values. That kind of error is a typo, and typos need the next technique.

Warning: Test regular expressions on a few examples before running them on a column. A pattern that removes "all punctuation" will also turn "O'Brien" into "OBrien" and "e-mail" into "email". If the original value matters, clean into a new column.

Fuzzy duplicates and typos

A typo cannot be fixed with a rule, but it can usually be matched to a short list of valid values. Fuzzy matching measures how similar two strings are. Python's standard library has difflib.get_close_matches, which returns the closest candidates above a similarity cutoff between 0 and 1. For large jobs there are faster dedicated libraries, but the idea is identical.

The recipe is worth following exactly:

  1. Build a canonical list of valid values (from a reference table, or the most frequent values).
  2. Compute a mapping from each distinct raw value to its closest canonical value; work on unique() values, not on every row.
  3. Inspect the mapping, especially the matches that changed. A cutoff that is too low merges different things; one that is too high misses typos.
  4. Send unmatched values to a human, instead of guessing.
  5. Group rare categories into "other" if they are too sparse to analyze.
Python Map typos with difflib and group rare categories

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Twelve raw labels become five valid ones. Every misspelling was matched to the right country, narnia matched nothing and is reported for review (it stays missing in fixed), and canada (only two rows after fixing) was grouped into other because its count is below 3. That threshold is arbitrary here; in real projects choose it by asking how many rows a category needs before you would trust a statistic about it. Grouping makes rare values safe for models that would otherwise create hundreds of nearly empty columns.

Be careful with fuzzy matching on short or similar names. "Niger" and "Nigeria", or "Guinea" and "Guinea-Bissau", are different countries but close strings. The mapping review in step 3 is not optional.

Parsing messy numbers, units and dates

Text often hides numbers and dates. The strategy is to write a small function for each pattern, test it on the formats you actually see, and make failures visible instead of guessing.

  • Prices: remove currency symbols and thousands separators. The tricky part is the decimal mark: "1.299,00" (European) and "1,299.00" (US) mean the same amount. A reasonable rule is that when both separators appear, the last one is the decimal mark.
  • Units: capture the number and the unit with a regular expression (str.extract), then convert to one unit with a lookup table. Keep the unit table in one place.
  • Dates: try a list of explicit formats one by one with pd.to_datetime(..., format=..., errors="coerce") and keep the first success. Values that match none stay NaT so you can inspect them.
Python Parse prices, weights and dates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Every parseable price, weight and date now has a real type: 1.299,00 EUR became 1299.0, 3 lbs became 1.361 kg, and all five date styles landed in March 2025. The unparseable values (free, n/a, garbage) are <NA>, NaN and NaT, so the audit from Auditing Data Quality can count them.

Two judgement calls hide in that code, and they deserve a comment in real projects:

  • 04/03/2025 was read as 4 March (day first) because that format comes before any month-first format in the list. If your source is American, it means April 3. Dates like 04/03/2025 are genuinely ambiguous, so find out the source's convention rather than letting the parser pick one silently.
  • The price free became missing, not zero. If "free" really means 0, that is a business decision to make explicitly.

A few habits that save you later

  • Clean into new columns (city_clean) and keep the raw column.
  • Work on unique values when mapping; a column with a million rows may only have 500 distinct labels.
  • Keep the mapping as data (a CSV or dict in your repo) so it is reviewable and reusable on the next load.
  • Convert to the category dtype after cleaning. It saves memory and makes the allowed values explicit.
  • Re-check after cleaning: value_counts() should look like the business, with a sensible number of groups.

Once the labels are tidy, you can turn them into numbers a model understands; that comes in Encoding and Scaling Features after we build new features from them in Feature Engineering.

Method chaining and the pyjanitor package

Cleaning code reads best as a chain: each step takes a DataFrame and returns a new one, so the whole recipe is one expression you can read top to bottom. pandas already gives you assign, rename, query and pipe (which lets you put your own function in the chain).

pyjanitor (pip install pyjanitor, imported as import janitor) adds many ready-made cleaning verbs as DataFrame methods so chains stay short. The ones you meet most:

  • clean_names() lowercases column names, replaces spaces and symbols with underscores and strips junk, so "Order Value ($)" becomes order_value.
  • remove_empty() drops rows and columns that are entirely empty, such as the Unnamed: 3 column an Excel export leaves behind.
  • coalesce("a", "b", target_column_name="c") takes the first non-missing value across columns.
  • fill_empty, rename_column, filter_on, encode_categorical and transform_column cover filling, renaming, filtering with a query string, converting to categoricals and applying a function to one column.

The package is not installable in the in-browser runner, so this example writes tiny stand-ins for three of those verbs and joins them with pipe. The real clean_names(), remove_empty() and coalesce() do the same jobs with more options.

Python Cleaning helpers as a method chain

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that the original raw table is untouched; every step returned a copy. That makes the cleaning recipe easy to test (see Testing Data Code) and to rerun when fresh data arrives.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why do 'Lisbon', 'lisbon ' and 'LISBON' cause wrong results?

    Computers compare text exactly, so spelling variants become different categories unless you normalize them.
  2. 2. What is the safest first step when cleaning a text column?

    Trimming and lowering only change how a value is written, not what it means, so they are safe and reversible if you keep the raw column.
  3. 3. You fuzzy-match free-text countries to a canonical list. What should happen to a value that matches nothing?

    Guessing creates wrong data that looks right. Unmatched values should be surfaced for a human decision.
  4. 4. In '1.299,00 EUR', which character is the decimal mark?

    This European style uses a period for thousands and a comma for decimals. When both appear, the last separator is the decimal mark.
  5. 5. Why is the date '04/03/2025' risky to parse automatically?

    Day-first and month-first conventions give different dates. You need to know the source's convention.

Summary

  • Clean labels in order from safe to risky: trim, lower-case, remove accents, tidy punctuation and spaces, convert null markers to real missing values.
  • Keep the raw column and clean into a new one; keep mappings as reviewable data.
  • Use fuzzy matching against a canonical list for typos, review the mapping, and send unmatched values to a human.
  • Group categories that are too rare to analyze into "other".
  • Parse prices, units and dates with small tested functions; make failures visible (NaN, NaT) and never guess ambiguous formats silently.

Next: Feature Engineering.