Cleaning Missing, Duplicate, and Invalid Data in Pandas

Welcome back. In the previous lesson, you audited a newly loaded DataFrame without changing it: checking schema, missingness, duplicates, ranges, categories, and cross-column rules. That audit produces the evidence needed for this lesson.

Now you will turn selected findings into explicit cleaning rules. The aim is not to make a table merely look tidy. It is to produce a defensible dataset in which missing values are represented consistently, duplicates are handled according to the row’s business meaning, and invalid values are either corrected under a documented rule or rejected. These same habits later protect model-training data and inference inputs.

---

Cleaning is a policy decision, not a sequence of convenience methods

A DataFrame can contain four importantly different kinds of problematic values:

Problem	Example	Appropriate response depends on
Truly missing	empty cell in plan	Whether the feature is optional and what absence means
Encoded missing	"unknown", "timeout", -999	The source-system contract
Invalid	latency_ms = -12, converted = 4	Domain constraints
Duplicate	repeated event or repeated identifier	The table’s grain and update semantics

The central question is always: what should one row represent?

For the running example, suppose each row represents one prediction-related product event:

• request_id must be present and uniquely identify one event.
• latency_ms is optional for some failed requests but, when present, must be non-negative.
• plan may be absent, but valid values are free, premium, and enterprise.
• converted is the training target and must be either $0$ or $1$.

A common failure is to apply generic cleaning globally:

df = df.fillna("")

This may be reasonable when preparing a human-facing contact list, but it is usually a poor default for ML data. It mixes an empty string into numeric columns, erases the distinction between “not observed” and “intentionally blank,” and can create type inconsistencies that surface much later in training or inference.

Instead, preserve the input and make each change traceable:

raw = df
clean = raw.copy()

From this point onward, use clean for transformations. raw remains available for investigation, comparison, and reproducibility.

---

Missing values: normalize first, decide treatment second

pandas has several internal representations for absence: NaN is common in floating-point columns, NaT in datetime-like columns, and pd.NA in pandas nullable types such as string, Int64, and boolean. You do not need to memorize the representation before cleaning. The stable interface is isna() and notna().

Study the official pandas documentation for the behavior of missing values and the core cleaning methods.

Treat source placeholders as data-contract rules

pandas detects blank CSV cells as missing in many situations, but source systems often encode absence with ordinary strings or special numeric values:

plan:        "unknown", "not provided", " "
latency_ms:  "timeout", "not recorded", -999

Those are not universally missing values. For example, "unknown" could be a meaningful product category, and -999 could be a legitimate measurement in an unusual domain. Convert them only after confirming their meaning with source documentation or the responsible team.

Here is a column-specific approach:

missing_tokens = {
    "plan": {"", "unknown", "not provided"},
    "latency_ms": {"", "timeout", "not recorded", "-999"},
}

for column, tokens in missing_tokens.items():
    values = clean[column].astype("string").str.strip()
    is_missing_token = values.str.lower().isin(tokens)
    clean[column] = values.mask(is_missing_token, pd.NA)

Several details matter:

• astype("string") uses pandas’ nullable string dtype, which preserves missing values cleanly.
• str.strip() converts whitespace-only entries to empty strings before they are tested.
• The replacement is column-specific. The word "unknown" in a free-text comment should not automatically be treated as missing.
• .mask(condition, pd.NA) replaces only values for which the condition is true.

After normalization, reassess missingness:

missing_summary = (
    clean.isna()
    .mean()
    .mul(100)
    .round(2)
    .sort_values(ascending=False)
)

print(missing_summary)

This count is now more truthful than the initial audit because source placeholders have been converted into pandas-recognized missing values.

Choose among dropping, retaining, and filling

There is no universal “best” missing-data strategy. Use the semantic role of each field.

Drop a row when it cannot serve its required purpose. A supervised-learning training row with no target is a standard example:

required_columns = ["request_id", "converted"]

rows_missing_required = clean[
    clean[required_columns].isna().any(axis="columns")
].copy()

clean = clean.dropna(subset=required_columns).copy()

The subset argument is essential. Plain clean.dropna() drops a row if any column is missing, including an optional field, and may discard a surprising amount of usable data.

Retain missingness when it is meaningful. A missing referrer may itself describe direct traffic or an instrumentation gap. At this stage, keeping pd.NA is often safer than inventing a value.

Fill a categorical value only when the replacement has clear meaning. For example, if product semantics define missing plan information as “not captured,” you might use:

clean["plan"] = clean["plan"].fillna("not_captured")

"not_captured" is deliberately different from a subscription plan such as "free". It records an absence of information rather than claiming that the user had a free plan.

Fill a numerical value with a statistic only under a modeling-aware procedure. A median is often a more robust baseline than a mean for skewed measurements such as monetary amounts or latency:

# Illustrative pattern after a training subset exists:
latency_fill_value = train_df["latency_ms"].median()

train_df["latency_ms"] = train_df["latency_ms"].fillna(latency_fill_value)
validation_df["latency_ms"] = validation_df["latency_ms"].fillna(latency_fill_value)

The key rule is that latency_fill_value must be calculated from training data only. Otherwise validation or test information leaks into the training process. You will implement that safely in a scikit-learn preprocessing pipeline later; for now, normalize missing representations and avoid computing dataset-wide imputation statistics by reflex.

---

Invalid values: make bad data visible before replacing it

An invalid value is not merely missing. It is present but violates the column contract:

latency_ms: -12
converted: 2
plan: "enterprsie"

The safest workflow has three stages:

1. Identify invalid values.
2. Preserve the affected rows for review or a rejection report.
3. Apply the documented policy: correct, convert to missing, quarantine, or remove.

Numeric parsing and range checks

Suppose latency_ms was loaded as text because a few rows contain "slow" or "12ms".

latency_as_number = pd.to_numeric(
    clean["latency_ms"],
    errors="coerce",
)

With errors="coerce", unparseable values become missing. Before assigning the result, distinguish values that were already missing from values made missing by conversion:

newly_unparseable = (
    clean["latency_ms"].notna()
    & latency_as_number.isna()
)

unparseable_latency_rows = clean.loc[
    newly_unparseable,
    ["request_id", "event_time", "latency_ms"],
].copy()

Now enforce the non-negative latency rule:

negative_latency = latency_as_number.lt(0).fillna(False)

negative_latency_rows = clean.loc[
    negative_latency,
    ["request_id", "event_time", "latency_ms"],
].copy()

clean["latency_ms"] = latency_as_number.mask(negative_latency)

This policy converts negative latency to missing, but it is not the only possible choice. If negative values indicate a broken telemetry producer, a production-quality workflow may quarantine the affected records and alert the data owner instead.

Avoid silently clipping values unless the domain explicitly supports it:

# Do not assume this is correct:
# clean["latency_ms"] = clean["latency_ms"].clip(lower=0)

Changing -12 to 0 claims an instantaneous request occurred. That is a stronger and often unjustified assertion than “the recorded value is invalid.”

Categorical normalization and validation

Text values frequently vary in capitalization or whitespace:

"Premium"
"premium "
" PREMIUM"

If these variations are truly equivalent, normalize them:

clean["plan"] = (
    clean["plan"]
    .astype("string")
    .str.strip()
    .str.lower()
)

Then check against the allowed set:

allowed_plans = {"free", "premium", "enterprise"}

invalid_plan = (
    clean["plan"].notna()
    & ~clean["plan"].isin(allowed_plans)
)

invalid_plan_rows = clean.loc[
    invalid_plan,
    ["request_id", "plan"],
].copy()

clean.loc[invalid_plan, "plan"] = pd.NA

This makes a deliberate distinction:

• capitalization and accidental surrounding whitespace are formatting defects that can be normalized;
• an unrecognized value such as "enterprsie" is a data-quality issue.

Do not automatically correct typos with fuzzy matching in an ML ingestion path. A value that looks like a typo may represent a newly launched plan, a source-system change, or a genuinely different category. Record it, verify its meaning, then update the contract if appropriate.

For binary labels, an explicit mapping is clearer and safer than chained string replacements:

label_map = {
    "0": 0,
    "false": 0,
    "no": 0,
    "1": 1,
    "true": 1,
    "yes": 1,
}

target_text = (
    clean["converted"]
    .astype("string")
    .str.strip()
    .str.lower()
)

invalid_target = (
    target_text.notna()
    & ~target_text.isin(label_map)
)

invalid_target_rows = clean.loc[
    invalid_target,
    ["request_id", "converted"],
].copy()

clean["converted"] = target_text.map(label_map).astype("Int64")

An explicit dictionary expresses the accepted source formats. It also avoids accidental partial replacements, such as replacing "Y" inside an unrelated value.

---

Duplicates: remove only what the table contract permits

duplicated() checks whether a row matches an earlier row over a selected set of columns. drop_duplicates() keeps one copy according to the keep policy.

This short demonstration shows the mechanics of removing exact duplicate rows.

Exact duplicate rows

If your audit established that fully identical event rows can only result from repeated ingestion, remove the later copies:

exact_duplicate = clean.duplicated(keep="first")

duplicate_rows = clean.loc[exact_duplicate].copy()

clean = clean.loc[~exact_duplicate].copy()

Keep duplicate_rows while developing or running the pipeline. It provides evidence for both debugging and reporting: which rows were removed, and how many?

Duplicate keys are not necessarily duplicate events

Now consider a duplicated request_id:

duplicate_request_id = (
    clean["request_id"].notna()
    & clean["request_id"].duplicated(keep=False)
)

request_id_conflicts = (
    clean.loc[duplicate_request_id]
    .sort_values("request_id")
    .copy()
)

This check intentionally uses keep=False, which marks every row in each duplicated group. That makes it possible to compare conflicting records rather than seeing only the later occurrence.

Do not write this without a documented policy:

# Potentially destructive:
# clean = clean.drop_duplicates(subset=["request_id"])

If two rows share an ID but differ in timestamp, status, or target, several interpretations are possible:

• repeated ingestion created a duplicate;
• one row is a legitimate event update;
• the identifier is not actually unique;
• the source system retried an event;
• one record corrected an earlier record.

Only the source contract can decide whether to keep the first record, keep the last record, select the newest version by timestamp, aggregate records, or reject the entire conflicting group. DataFrame row order is not reliable evidence that “first” or “last” is correct.

---

Verify the cleaned output as a new contract

Cleaning is incomplete until you verify that the output satisfies the rules you intended to enforce. Assertions are useful because they turn quiet data drift into an immediate and diagnosable failure.

After removing rows with missing required values, suppose model_rows is your accepted dataset:

model_rows = clean.dropna(
    subset=["request_id", "converted"]
).copy()

assert model_rows["request_id"].notna().all()
assert model_rows["converted"].isin([0, 1]).all()
assert model_rows["latency_ms"].dropna().ge(0).all()
assert model_rows["plan"].dropna().isin(allowed_plans).all()
assert not model_rows.duplicated().any()

These checks do not guarantee that the data is correct. They guarantee that the data satisfies the rules currently encoded in your program. If a new plan tier appears next month, the final plan assertion should fail loudly rather than silently turning a novel category into an arbitrary value.

Also record row counts at each major cleaning stage:

cleaning_summary = pd.Series({
    "input_rows": len(raw),
    "unparseable_latency_rows": len(unparseable_latency_rows),
    "negative_latency_rows": len(negative_latency_rows),
    "invalid_plan_rows": len(invalid_plan_rows),
    "invalid_target_rows": len(invalid_target_rows),
    "exact_duplicates_removed": int(exact_duplicate.sum()),
    "output_rows": len(model_rows),
})

print(cleaning_summary)

A summary like this is valuable in a notebook, a scheduled batch job, or a future production pipeline. A sudden jump from 0.2% to 18% invalid plan values is not just a cleaning concern; it is evidence that a source, schema, or product behavior changed.

---

Key takeaways

Cleaning is the conversion of an audited data contract into explicit, testable rules.

• Use isna() and notna() to detect missingness; do not compare values directly with NaN or pd.NA.
• Convert source-specific placeholders to pd.NA only when the source contract confirms their meaning.
• Drop rows only when fields required for the dataset’s purpose are missing; use subset rather than indiscriminate dropna().
• Separate invalid values from missing values. Inspect and record them before converting, dropping, or quarantining them.
• Remove exact duplicate rows only when repeated rows violate the table’s grain. Treat duplicate identifiers with conflicting fields as an investigation, not an automatic drop_duplicates() call.
• Preserve the raw input, maintain rejected-row evidence during development, and validate the cleaned output with assertions and row-count summaries.

Next, you will combine datasets and produce useful summaries with pandas joins, grouping, and aggregation.