pandas data cleaning is, bluntly, most of the actual job. This article works through a single, realistic messy dataset end-to-end — profiling it, handling missing values, standardizing text, fixing data types, handling duplicates and outliers, and building a reusable cleaning pipeline — tying together nearly every method covered individually across this pandas track into one coherent, worked example of how to clean messy data python actually deals with in practice.
Why Data Cleaning Is Most of the Job
The blunt reality
Data scientists spend 60–80% of their time cleaning data. Messy, incomplete, inconsistent data is the norm in the real world, not the exception — the clean, tidy datasets used in most tutorials (including most of the examples earlier in this series) are the exception, not the rule.
A grounding analogy
Cleaning data is like organizing a messy room before inviting friends over — or, put another way, like cooking: even the best recipe won't turn out right if the ingredients are spoiled. All the groupby(), merge(), and resample() skill in the world, covered in earlier articles in this series, won't produce a trustworthy result if the underlying data is full of silent inconsistencies.
The dataset this article uses, start to finish
import pandas as pd
import numpy as np
data = {
"full_name": ["Alex Chen", "sam Rivera", " Jordan Lee", "Alex Chen", "Casey Nguyen", None],
"email": ["[email protected]", "[email protected]", "[email protected]", "[email protected]", "", "[email protected]"],
"age": [30, -5, 200, 30, 28, 35],
"signup_date": ["2023-01-15", "2023/02/20", "2023-03-10", "2023-01-15", "2023-04-05", "not a date"],
"salary": ["65000", "58,000", "72000", "65000", "61000", "N/A"],
}
df = pd.DataFrame(data)This one dataset has nearly every common problem at once: inconsistent casing, stray whitespace, a duplicate record, an implausible negative age and an impossible age of 200, inconsistent date formats (including one outright invalid entry), numeric data stored as text with inconsistent formatting, and a missing value hiding as an empty string. Every section below works directly against this same dataset, building toward one complete, cleaned result.
What this article covers
Profiling the mess before touching it, handling missing values and duplicates in context, standardizing text and fixing data types, handling outliers, and finally chaining every step into one reusable, auditable pipeline.
Step One: Profiling the Mess Before Touching It
Deciding whether data is clean, messy, or dirty
Before fixing anything, the first job is profiling — figuring out exactly what's wrong, and how widespread each problem actually is, before deciding which techniques from the rest of this article are actually needed.
print(df.isna().sum())
# full_name 1
# email 0
# age 0
# signup_date 0
# salary 0
# dtype: int64This looks almost clean at first glance — but as covered in Section 3, several of these columns have missing values hiding in a form .isna() doesn't catch directly (an empty string, a text placeholder like "N/A").
Quick diagnostic habits worth running immediately
print(df.info())
# full_name object
# email object
# age int64
# signup_date object
# salary object
print(df.describe())
# age
# count 6.000000
# mean 53.000000
# std 68.087...
# min -5.000000
# max 200.000000.info() immediately reveals a genuine problem: signup_date and salary are both object (text) dtype, despite representing dates and numbers respectively — neither can be meaningfully aggregated, compared, or plotted in their current form. .describe() on age immediately reveals the implausible values directly in its own summary: a minimum of -5 and a maximum of 200, both genuinely impossible ages.
print(df.duplicated().sum())
# 0 — but this is misleading, covered in Section 3Why this step matters more than jumping straight to fixes
Understanding exactly which problems exist, and how widespread each one is, is what determines which specific techniques from the rest of this article are actually needed for this dataset — a dataset with mostly-clean numeric columns but messy text needs a very different cleaning approach than one with the reverse problem. Profiling first prevents wasted effort applying fixes to problems that don't actually exist, and catching problems (like the two below) that a surface-level glance would miss.
Handling Missing Values and Duplicates in Context
Choosing a fillna/dropna strategy, tied back to the earlier missing-data article
df["email"] = df["email"].replace("", np.nan)
print(df["email"].isna().sum())
# 1 — now correctly detectedThis is a genuinely important, commonly-skipped nuance: empty strings hide as "present but blank" values that .isna() won't catch on its own — an empty string "" is not the same as NaN as far as pandas' missing-value detection is concerned, even though both represent "no real value" conceptually. Converting them explicitly with .replace("", np.nan) before running any missing-value analysis is a genuinely essential first step that several thinner guides skip entirely.
df["full_name"] = df["full_name"].fillna("Unknown")For full_name, a placeholder like "Unknown" is a sensible categorical default, exactly as covered in the earlier missing data article. For a genuinely skewed numeric column, the column median (rather than mean) is generally the safer default, since it's far less sensitive to the kind of outliers covered in Section 5.
dropna(thresh=...) for rows missing too much to be usable
df_cleaned = df.dropna(thresh=3) # keep rows with at least 3 non-missing valuesFor rows missing too large a fraction of their fields to be genuinely usable — not just one gap, but several at once — dropna(thresh=...) is the right tool, exactly as covered in the earlier missing data article's discussion of the middle ground between dropping too aggressively and too leniently.
Removing duplicates, including near-duplicates
print(df.duplicated().sum())
# 0 — misleading! "Alex Chen" appears twice, but let's check why it wasn't caught
print(df[df["full_name"] == "Alex Chen"])The full-row .duplicated() check found zero duplicates — but "Alex Chen" genuinely appears twice, with identical email and age. The reason it wasn't caught: .duplicated() by default requires every column to match exactly, and in this dataset, both "Alex Chen" rows happen to match on every column shown — so this particular case actually was caught; the more common trap is the reverse:
df.loc[2, "full_name"] = " Jordan Lee " # imagine slightly different whitespace on a re-entry
print(df.duplicated(subset=["email"]).sum())Deduplicating on a subset of columns — email alone, here — rather than requiring every column to match exactly, catches real-world duplicates that differ slightly: extra whitespace, a re-typed name with different capitalization, a middle initial added on one entry but not the other. Requiring an exact full-row match misses exactly this kind of near-duplicate, which is genuinely the more common real-world pattern than a perfectly identical duplicate row.
df = df.drop_duplicates(subset=["email"], keep="first")Standardizing Messy Text and Fixing Data Types
Vectorized string cleaning
df["full_name"] = df["full_name"].str.strip().str.lower()
print(df["full_name"])
# alex chen, sam rivera, jordan lee, casey nguyen, unknown.str.strip() removes stray leading/trailing whitespace (like " Jordan Lee" and "Casey Nguyen"'s internal double space, which .strip() alone won't fix — covered next). .str.lower() normalizes inconsistent casing ("sam Rivera" versus "Alex Chen") so that string comparisons and grouping behave consistently, rather than treating differently-cased versions of the same name as distinct values.
df["full_name"] = df["full_name"].str.replace(r"\s+", " ", regex=True).str.replace() with a regex pattern — \s+ matching one or more whitespace characters — collapses "Casey Nguyen"'s internal double space down to a single space, a genuinely common real-world issue with user-entered text that plain .strip() alone doesn't address, since .strip() only touches the edges of a string, not internal whitespace.
Splitting a combined column
name_parts = df["full_name"].str.split(" ", n=1, expand=True)
df["first_name"] = name_parts[0]
df["last_name"] = name_parts[1]
print(df[["first_name", "last_name"]]).str.split(expand=True) splits a single combined column — a common real-world need when source data (like a "Full Name" field from an older system) wasn't captured in the shape actually needed for downstream analysis, where first and last name might need to be handled independently.
Fixing data types explicitly
df["salary"] = df["salary"].str.replace(",", "", regex=False).replace("N/A", np.nan)
df["salary"] = pd.to_numeric(df["salary"], errors="coerce")
print(df["salary"])
# 65000.0, 58000.0, 72000.0, 65000.0, 61000.0, NaNsalary needed two fixes at once: stripping the thousands-separator comma ("58,000" → "58000"), and converting the "N/A" placeholder to a genuine NaN before attempting numeric conversion. pd.to_numeric(..., errors="coerce") converts valid numeric strings to actual numbers, and — genuinely important — turns anything it can't parse into NaN rather than raising an error and halting the entire operation.
df["signup_date"] = pd.to_datetime(df["signup_date"], errors="coerce")
print(df["signup_date"])Exactly tying back to the earlier datetime article: pd.to_datetime(..., errors="coerce") converts genuine date strings (even in inconsistent formats, like "2023-01-15" versus "2023/02/20") into real datetime objects, and converts the outright invalid "not a date" entry into NaT — pandas' missing-datetime sentinel, covered in the earlier missing data article — rather than crashing the entire conversion over one bad row. A column stored as text can't be aggregated, compared, or plotted correctly, no matter how individually "clean" its values look — this conversion step is non-negotiable, not optional polish.
Handling Outliers and Building a Reusable Cleaning Pipeline
Identifying implausible values using domain knowledge
print(df[(df["age"] < 0) | (df["age"] > 120)])This is worth stating plainly: checking whether values make sense given domain knowledge — a human being cannot be -5 or 200 years old — catches errors that a purely statistical approach (an IQR or z-score-based outlier filter) might miss entirely, especially in a small dataset where a genuinely implausible value might not even register as a strong statistical outlier relative to the rest of the data. Domain-aware bounds checking and statistical outlier detection are complementary, not interchangeable — use both where they're each appropriate, rather than relying on just one.
df.loc[(df["age"] < 0) | (df["age"] > 120), "age"] = np.nanRather than silently leaving obviously wrong values in place, converting them to NaN marks them explicitly as "known bad, needs handling" — feeding directly back into the missing-value strategies covered in Section 3, rather than letting an impossible age of 200 silently corrupt any later average-age calculation.
Chaining every step into one readable pipeline
Rather than repeatedly reassigning the DataFrame line by line, method chaining produces a single, auditable transformation from raw to clean data:
def clean_employee_data(df):
return (
df
.replace("", np.nan)
.assign(
full_name=lambda d: d["full_name"].fillna("Unknown").str.strip().str.lower()
.str.replace(r"\s+", " ", regex=True),
salary=lambda d: pd.to_numeric(
d["salary"].str.replace(",", "", regex=False).replace("N/A", np.nan),
errors="coerce"
),
signup_date=lambda d: pd.to_datetime(d["signup_date"], errors="coerce"),
age=lambda d: d["age"].where((d["age"] >= 0) & (d["age"] <= 120)),
)
.drop_duplicates(subset=["email"], keep="first")
.dropna(subset=["email"], thresh=1)
)
cleaned_df = clean_employee_data(df)
print(cleaned_df)Each .assign() call, using a lambda (as covered in the earlier lambda functions article) referencing the DataFrame at that specific point in the chain, applies one clearly-named transformation. This structure is considerably more auditable than reassigning df repeatedly across many separate lines: the entire cleaning logic reads top to bottom as a single, coherent pipeline, and — genuinely important for real projects — it can be wrapped in a function, tested, and reused directly on the next dataset with the same underlying structure, rather than being manually retyped or copy-pasted each time.
Closing guidance: treat the pipeline as reusable and documented
The same messy patterns covered throughout this article — missing values hiding as blank strings, inconsistent text casing, numbers stored as text with formatting artifacts, implausible outlier values, near-duplicate records — recur across nearly every real dataset you'll encounter, not just this one specific example. A clean, well-structured, well-documented cleaning pipeline, built once and genuinely reusable, saves considerably more time on the next project than it costs to build carefully on this one. Treating data cleaning as a first-class, reusable engineering artifact — rather than a one-off, throwaway chore repeated from scratch every single time — is, in a very real sense, the actual skill this entire pandas track has been building toward.