Level 2 · Fix the state codes

Why text gets messy

Text entered by people, imported from several systems or typed into a free-form field drifts: 'CA', 'ca', ' CA', 'CA '. To a computer those are four different values, so a groupby counts four states. Look at what you have before cleaning:

customers["state"].nunique()          # far more than the number of real states
customers["state"].value_counts().tail(20)   # rare values are often the messy ones

The .str accessor

String methods for whole columns live under .str. They work on every value at once and pass NaN through untouched:

customers["state"].str.strip()        # remove spaces at both ends
customers["state"].str.upper()        # 'ca' -> 'CA'
customers["city"].str.strip().str.title()   # '  dallas ' -> 'Dallas'
customers["email"].str.strip().str.lower()

Other useful ones: .str.replace("St.", "Saint", regex=False), .str.len(), .str.split("@").str[1] (the email domain), .str.contains(...) and .str.zfill(5) for ZIP codes that lost their leading zeros.

Choose a canonical form

Decide what “clean” looks like for each column and apply it consistently:

column	canonical form	code
state	2 uppercase letters	.str.strip().str.upper()
city	title case	.str.strip().str.title()
email	lowercase	.str.strip().str.lower()

title() has quirks: it would turn “McAllen” into “Mcallen”. For a handful of exceptions, follow it with .replace({"Mcallen": "McAllen"}).

Replace the column, then group

cleaned = customers.assign(state=customers["state"].str.strip().str.upper())
cleaned.groupby("state", as_index=False).agg(n_customers=("customer_id", "count"))

assign with an existing column name replaces that column in the new DataFrame. The original customers keeps its messy values, which is useful: you can always compare raw and cleaned values to check your cleaning did what you expected:

changed = customers["state"] != cleaned["state"]
customers.loc[changed, "state"].value_counts()

Seeing exactly which raw values changed, and what they became, is the best test of a cleaning step.

Check after cleaning

cleaned["state"].nunique()            # now a sensible number
cleaned["state"].str.len().value_counts()   # every code should have length 2

That second check catches values the cleaning didn’t fix, such as a full state name or a typo.

Clean once, early

In real projects, cleaning happens in one place, right after loading, so every later step uses the same clean values. Doing it inside individual reports leads to two reports that disagree about how many customers live in California.

Common mistakes

• Calling customers["state"].strip() without .str: Series has no strip method.
• Cleaning only casing or only spaces, and still counting ' CA' separately.
• Forgetting that .str methods return a new Series; the column only changes when you assign it.

If you know SQL

This is GROUP BY UPPER(TRIM(state)).