Normalize Text and Work with Categorical Domains

Module 3: Cleaning Real-World Data · Lecture 11 of 32

Small text differences can fragment a report into misleading groups. Normalize only the differences your domain considers equivalent, retain unknown categories, and use categoricals when the valid vocabulary is finite.

What you will learn

Normalize whitespace and case in controlled category fields.
Validate categories without silently converting new values to missing.
Use observed categorical groups deliberately.

Understand the problem

Normalization must preserve meaning

The channel values paid, PAID, and padded PAID represent the same category in this teaching source. Strip surrounding whitespace and lowercase the field before grouping. That policy would be inappropriate for a case-sensitive external identifier, a password, or text where spacing carries meaning. Write field-specific normalization rules instead of applying a blanket transformation to every column.

Separate unknown from unexpected

A blank channel becomes unknown because the source did not provide attribution. A new nonblank value such as affiliate is unexpected under the current allowed set. Do not map it to unknown automatically: doing so conceals a schema change. Inspect novel values and decide whether to extend the contract, quarantine the rows, or publish a separate unmapped category.

Categories define a domain

A categorical dtype stores a finite vocabulary and can reduce repeated-string storage. Ordered categories can also encode a meaningful ranking, such as low, medium, high. Marketing channels have no natural ranking, so leave them unordered. Converting an unexpected string directly to a restricted categorical can turn it into missing; validate vocabulary before conversion.

Observed versus possible groups

A report can display only categories present in the data or all allowed categories, including zero-activity groups. Use observed=True for observed combinations and explicitly reindex to a full reporting vocabulary when zero rows are intended. This is clearer than relying on library defaults that may differ across versions. An unobserved category is not proof of a missing export or of genuine zero activity without coverage checks.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 11 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

raw = load_raw()
normalized = raw['channel'].str.strip().str.lower().replace('', pd.NA).fillna('unknown')
allowed = ['organic', 'paid', 'email', 'unknown']
unexpected = set(normalized.dropna().unique()) - set(allowed)
assert not unexpected
kind = pd.CategoricalDtype(categories=allowed, ordered=False)
table = pd.DataFrame({'channel': normalized.astype(kind)})
counts = table.groupby('channel', observed=True).size().reindex(allowed, fill_value=0)
print(counts.to_string())
assert int(counts.sum()) == len(raw)
assert table['channel'].isna().sum() == 0

Verified output

channel
organic    84
paid       79
email      80
unknown     1

Walk through the result

Normalization happens before the categorical cast. That order prevents padded PAID from becoming a missing category. The counts include duplicate and invalid raw records because this example studies ingestion vocabulary, not clean business totals. Reindex establishes a stable display order. The total-count assertion ensures that normalization and grouping did not silently discard observations.

Common mistakes and diagnosis

Avoid broad substring matching when exact normalized categories are available; paid can appear inside unrelated text. Do not use astype(str) to “clean” nullable values if it turns absence into the literal text nan or <NA>. Category codes are internal encodings, not stable business IDs. If category order changes, the numeric codes may change even though the labels remain the same.

Practice assignment

Add a source value affiliate to the normalized Series and detect it before casting. Then create a status categorical ordered as completed, returned, cancelled solely for display. Explain why that display order is not a severity scale.

Worked solution and explanation

Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.

new_values = pd.concat([normalized, pd.Series(['affiliate'], dtype='string')], ignore_index=True)
novel = set(new_values.unique()) - set(allowed)
assert novel == {'affiliate'}
orders = load_orders()
status_order = pd.CategoricalDtype(['completed', 'returned', 'cancelled'], ordered=True)
display = orders.assign(status=orders['status'].astype(status_order)).sort_values('status')
assert len(display) == len(orders)

Check your understanding

Why validate categories before converting to a categorical dtype?

Answer guide

Values outside the declared vocabulary can become missing during conversion. Without a pre-conversion check, a new legitimate source category looks like absent data and may disappear from a group. Detecting novel labels first preserves evidence of source evolution and lets you update the contract intentionally.

Reference and next step

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.

Course index · Previous · Next