Data Analysis / Basic: Questions and Trustworthy Tables / Lesson 3

Cleaning and Validation Rules

Transform records deliberately, preserve raw evidence, and distinguish repairing representation from changing the underlying claim.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Design validation before mutation.
Normalize categories with an explicit mapping.
Audit rejected records and unusual values.

Cleaning is a set of analytical decisions

Cleaning is not making every cell look tidy. It is applying justified rules so the table represents its declared concepts consistently. Separate deterministic representation repairs from assumptions about reality. Trimming an accidental outer space can differ fundamentally from replacing an implausible measurement with a guessed normal value.

Keep raw input immutable and derive cleaned fields with rule names or versions. This permits rollback and comparison when a rule is questioned. Record counts affected by each transformation. A cleaning pipeline that silently changes many records can produce a smooth chart while erasing a real operational problem.

Map categories without hiding unknowns

The first program strips outer whitespace and normalizes case before a small allowed-category mapping. Unknown tokens remain rejected rather than becoming an arbitrary default. The output reports the accepted categories and the number rejected. This rule is valid only because case and outer spacing are explicitly irrelevant for these synthetic status labels.

Do not apply the same transformation automatically to passwords, case-sensitive identifiers, or labels where spacing carries meaning. Normalization can collapse distinct categories. Keep original tokens available and review a frequency table of raw-to-clean mappings to detect an overly broad rule.

python An auditable category rule

raw = [" Complete ", "CANCELLED", "mystery"]
allowed = {"complete", "cancelled"}
clean = []
rejected = []
for token in raw:
    normalized = token.strip().casefold()
    (clean if normalized in allowed else rejected).append(normalized)
print("accepted", ",".join(clean))
print("rejected", len(rejected))

Expected output

accepted complete,cancelled
rejected 1

Two known tokens normalize successfully, while mystery remains a rejected unknown.

Reject bad values before calculating

The second program validates a required count field under a strict integer and range rule, recording the source row number on rejection. It distinguishes accepted zero from missing text and out-of-domain input. Only validated counts contribute to the total. Exceptions from parsing are handled as expected record failures rather than swallowed for the whole dataset.

A row identifier or source position makes an error actionable without dumping sensitive content. Prefer a structured rejection report containing rule, location, and a controlled sample. Separate a fatal schema failure from a row-level rejection policy; continuing after a missing required column may make every later result meaningless.

python Validate count syntax and range

tokens = ["0", "12", "", "-1", "101"]
accepted = []
rejected = []
for row_number, token in enumerate(tokens, start=1):
    if not token.isascii() or not token.isdecimal():
        rejected.append(row_number)
        continue
    value = int(token)
    if not 0 <= value <= 100:
        rejected.append(row_number)
        continue
    accepted.append(value)
print("total", sum(accepted))
print("rejected rows", ",".join(map(str, rejected)))

Expected output

total 12
rejected rows 3,4,5

Zero and twelve are accepted; rows3,4,5 fail the declared required nonnegative0..100 contract.

An unusual value is a question, not automatic garbage

An outlier can be a recording error, a different unit, a rare genuine event, or evidence that the population contains several groups. A threshold can flag records for review, but deleting flagged points changes the dataset and may bias the result. Examine source context, units, and the intended population before deciding a correction or exclusion.

If a sensitivity analysis excludes a flagged group, present both the original and alternative result with the rule. Do not choose a cutoff solely because it produces a preferred conclusion. Robust summaries can reduce sensitivity to extreme values while preserving the observations for inspection.

Test cleaning with invariants and reconciliation

Useful checks include accepted plus rejected equals input, required keys remain unique, normalized categories belong to the dictionary, and values remain within domain bounds. An idempotent representation rule should give the same cleaned result when applied again; this does not mean every analytical transformation should be repeated blindly.

Write small cases for empty text, Unicode variants, exact boundaries, repeated identifiers, and mixed units. Compare before-and-after distributions and subgroup retention rates. If one group is rejected much more often, investigate rather than hiding the difference inside a single overall quality percentage. Cleaning is part of the analysis, not invisible preparation.

Common errors and fixes

Unknown labels become complete records.

Why it happens: A default category hides mapping failure.
How to fix it: Keep unknowns explicit and review them.
Prevent it next time: Test unmapped tokens.

Every parse failure becomes zero.

Why it happens: Invalid data is confused with a valid measurement.
How to fix it: Record rejection or missingness under a defined policy.
Prevent it next time: Test zero separately from blank and malformed text.

Deleting outliers improves a chart without explanation.

Why it happens: A visual preference replaced a data rule.
How to fix it: Investigate and report sensitivity under an explicit criterion.
Prevent it next time: Keep raw observations and exclusion reasons.

Check your understanding

1. A status cleaner maps every unrecognized token to active. Explain the analytical damage and a repair.

Show worked solution

It converts uncertainty or source errors into positive activity and changes both numerator and denominator. Keep a separate unknown/rejected state, count affected records, inspect original tokens, and update a reviewed mapping only when evidence supports it. Report how excluding or retaining unknown records changes the metric rather than silently choosing the favorable version.

2. Give three checks for a rule converting kilogram measurements to grams.