DA-01-017-P · Primer — Cleaning Data for Analysis

The idea in one sentence

Cleaning data means fixing types, removing duplicate rows, and standardizing labels and units before you summarize, so that every number your chart reports is answering the question you actually asked.

Why it exists

A summary is only as honest as the rows underneath it. If a spend column arrived as text, sum() silently concatenates or errors instead of adding; if "North" and "north " are two different strings, your "regions" chart shows five bars when reality has three; if the same order got logged twice, your revenue is inflated and nobody notices. These aren't exotic failures — they are what raw exports from spreadsheets, forms, and CRMs look like every single day. The analytical question you're really defending is: "Am I counting each real thing exactly once, in the right units, under one consistent name?" Cleaning is the step where you make that true, deliberately and visibly, rather than hoping the data was born tidy. The goal is not perfection; it is trustworthy summaries — you'd rather see an honest NaN for a bad value than a confident wrong total.

How it works

Cleaning is three moves you can almost always name in advance: coerce types, normalize labels, then deduplicate. Coerce with pd.to_numeric(col, errors="coerce") so unparseable junk like "3.2k" becomes NaN — flagged, not silently dropped or crashed. Normalize text labels with the .str accessor: .str.strip() kills leading/trailing whitespace and .str.lower() collapses casing, so "North", "north", and "SOUTH " land in the categories you intend. Then drop_duplicates() removes rows that are now provably identical — note you do this after normalizing, because two rows only look identical once trimming and casing agree. Order matters: dedup before normalizing would keep "Bill" and "Bill " as separate people. The snippet below runs the full pipeline on six messy rows and charts spend by cleaned region, and the printed row counts prove the dedup fired.

import pandas as pd
import matplotlib.pyplot as plt

raw = pd.DataFrame({
    "customer": ["Ada ", "ada", "Bill", "Bill", "Cleo", "Cleo"],
    "region":   ["North", "north", "SOUTH", "SOUTH", "West", "West"],
    "spend":    ["120", "120", "85", "85", "3.2k", "40"],
})

# 1) type coercion: text -> number; bad "3.2k" becomes NaN (flagged, not faked)
raw["spend"] = pd.to_numeric(raw["spend"], errors="coerce")

# 2) trim + lowercase so labels group correctly
raw["customer"] = raw["customer"].str.strip().str.lower()
raw["region"]   = raw["region"].str.strip().str.lower()

# 3) deduplicate rows that are now provably identical
clean = raw.drop_duplicates()
print("rows before:", len(raw), " after dedup:", len(clean))  # before: 6  after dedup: 4

by_region = clean.groupby("region")["spend"].sum()
# by_region -> north 120.0, south 85.0, west 40.0  (the "3.2k" row is NaN, excluded from the sum)
by_region.plot(kind="bar")
plt.title("Spend by region (cleaned)")
plt.tight_layout()
plt.show()  # chart shows 3 honest bars: north=120, south=85, west=40

The one gotcha

errors="coerce" is a promise you must keep looking at. It turns bad values into NaN so your pipeline won't crash — but a NaN is a symptom, not a fix. If you never check how many you created (clean["spend"].isna().sum()), you've quietly deleted real money from the total and called it clean. The "3.2k" above was a genuine sale worth $3,200 that just vanished from the "west" bar. Coerce to survive the load, then always audit the wreckage before you trust the summary.

See it — order of operations: coerce, normalize, then dedup

flowchart TD
    A["Raw rows<br/>(mixed types,<br/>messy labels, dupes)"] --> B["Coerce types<br/>to_numeric coerce<br/>(bad -> NaN)"]
    B --> C["Normalize labels<br/>strip + lower<br/>(one name per thing)"]
    C --> D["Deduplicate<br/>drop_duplicates<br/>(count each once)"]
    D --> E["Audit NaNs<br/>then groupby + chart<br/>(trustworthy summary)"]

The one-line takeaway

Coerce types, normalize labels, deduplicate — in that order, auditing the NaNs you create — so every bar counts each real thing once, in the right units, under one name.

Where you'll meet it

Every raw export you ever load: spreadsheet CSVs where numbers arrived as text, survey forms with free-typed categories, CRM dumps with double-submitted rows, and log files where casing and stray spaces fracture what should be one label. Do this pass first, before any groupby, chart, or reported total.

Named concepts: type coercion, deduplication and trimming, consistent categories and units