lesson 215 pandas Transformations · advanced

Data quality checks

Duplicates hide behind different capital letters and stray spaces. Normalise text first, then find duplicates on the columns that identify a record, and report every rule a row breaks as a count.

You'll be able to

Normalise text with .str.strip(), .str.lower() and .str.title() before comparing
Find duplicates by key with duplicated(subset=..., keep=...) and remove them with drop_duplicates
Count rule violations (missing, duplicated, wrong format) in a report instead of silently fixing them

The idea

Runtime Market's raw customers file has the same e-mail as Noah.scott51@outlook.com and NOAH.SCOTT51@OUTLOOK.COM, cities such as MIAMI and Houston, and the same customer exported twice. Every count and join built on that data is quietly wrong. Two steps fix most of it: normalise text (strip spaces, one case) so equal things compare equal, then deduplicate on a key: duplicated(subset=[...]) marks every row whose key appeared earlier (keep="first") or later (keep="last"), and drop_duplicates removes them.

Run each example to see what it prints. Running examples doesn't count toward progress.

example-1.py · Duplicates that only appear after normalising

import pandas as pd

customers = pd.DataFrame({
    "customer_id": ["C02264", "C02308", "C04614", "C08994"],
    "email": ["Noah.scott51@outlook.com", "theowilliams@yahoo.com", "ana.novak45@goldencorp.com ", "NOAH.SCOTT51@OUTLOOK.COM"],
    "city": ["MIAMI", " Houston", "New York", "Raleigh"],
})
print(customers["email"].duplicated().sum())
customers["email"] = customers["email"].str.strip().str.lower()
customers["city"] = customers["city"].str.strip().str.title()
print(customers["email"].duplicated().sum(), customers["city"].tolist())
print(customers[customers["email"].duplicated(keep=False)]["customer_id"].tolist())

example-2.py · A subset column with the wrong name

import pandas as pd

customers = pd.DataFrame({"customer_id": ["C02308", "C02308"], "email": ["theowilliams@yahoo.com", "theowilliams@yahoo.com"]})
print(customers.drop_duplicates(subset=["Email"]))

example-3.py · A quality report

import pandas as pd

customers = pd.DataFrame({
    "customer_id": ["C02264", "C02308", "C02308", "C06310", "C08994"],
    "email": ["noah.scott51@outlook.com", "theowilliams@yahoo.com", "theowilliams@yahoo.com", None, "noahpatel@willowlabs.com"],
    "state": ["FL", "TX", "TX", "OR", "nc"],
})
report = {
    "rows": len(customers),
    "duplicate_rows": int(customers.duplicated().sum()),
    "missing_email": int(customers["email"].isna().sum()),
    "bad_state": int((~customers["state"].str.fullmatch(r"[A-Z]{2}")).sum()),
}
print(report)
clean = customers.drop_duplicates(subset=["customer_id"], keep="last")
print(clean["customer_id"].tolist())

When to use it: on every new file, before joins and aggregates. Mistakes to avoid: deduplicating before normalising (case and spaces hide copies); deduplicating on the whole row when the key is what identifies a record; keeping the wrong copy (sort first, then choose keep="first" or "last" on purpose); and "fixing" data without counting what you fixed.

Review

Normalise text (str.strip, str.lower, str.title) before comparing or deduplicating.
duplicated(subset, keep) marks repeats; drop_duplicates removes them; sort first to choose which copy survives.
A quality report turns each rule into a count.
Next (see Visualization & EDA): charts that show what these tables mean.

Practise this

Tidy a customer export