Unit 10: Data cleanup

I. Orientation — The Data-Cleanup Process

Data cleanup is the systematic detection and correction of inaccurate, incomplete, inconsistent, duplicated, or poorly represented data. In Python, cleanup normally occurs after data collection and before analysis, visualization, or storage: raw data is investigated, values are matched against rules or related records, and the resulting dataset is formatted consistently.

• Governing principle — preserve meaning: Cleaning should improve data quality without silently changing what the observations mean. For example, converting "1,250" to the integer 1250 preserves meaning, whereas replacing an unknown income with 0 invents information.
• Core quality dimensions:
  • Validity: Values obey defined rules, such as 0 <= percentage <= 100.
  • Completeness: Required fields are present rather than None, NaN, or blank.
  • Consistency: Equivalent values use one representation, such as "UK" rather than a mixture of "UK", "U.K.", and "United Kingdom".
  • Uniqueness: One real-world entity is not represented by unintended duplicate records.
  • Accuracy: Values agree with the real entities or events they represent.
• Typical pipeline: Cleanup should proceed in a controlled order so that later operations receive predictable input.

load → inspect → define rules → match anomalies → correct → format → validate

• Non-destructive workflow: Keep raw data unchanged and produce a cleaned copy. In pandas, clean = raw.copy() prevents routine transformations from being applied directly to the original DataFrame.
• Explicit missingness: Python commonly represents absence with None; pandas also uses NaN and pd.NA. The empty string "", the text "N/A", and the number 0 are not automatically missing unless the data specification defines them that way.
• Reproducibility: Cleanup rules belong in functions or scripts, not undocumented manual edits. The same input and rules should produce the same cleaned output.
• Validation convention: Compare measurements before and after cleaning, including row count, missing-value count, duplicate count, data types, and permitted ranges.
• Auditability: Record what changed and why. A correction table can retain a row identifier, original value, cleaned value, rule name, and timestamp.
• Cautious automation: Deterministic corrections may be automated; uncertain matches should be flagged for review rather than forced into a category.

II. Investigation — Discovering Data-Quality Problems

A. Investigation

Investigation establishes the structure, content, and defects of a dataset before any values are altered.

• Initial inspection: Examine dimensions, column names, sample records, and data types rather than relying on assumptions.

import pandas as pd

df = pd.read_csv("customers.csv")
print(df.shape)
print(df.head())
df.info()

• df.shape returns (r, c), where r is the number of rows and c is the number of columns.
• df.info() reports non-null counts and inferred types, making a numeric field incorrectly stored as object visible.

• Descriptive profiling: Use df.describe(include="all") to inspect counts, distinct values, frequencies, means, quartiles, and extremes. A recorded age of 240 may appear as an implausible maximum even though it is a valid integer.
• Missing-value analysis: Count absence by column instead of merely observing scattered blanks.

missing_count = df.isna().sum()
missing_rate = df.isna().mean() * 100

• missing_count gives the number of missing cells in each column.
• missing_rate gives the percentage missing; multiplication by 100 converts the proportion to a percentage.
• Patterns matter: if delivery dates are missing only for cancelled orders, the absence may be structurally valid.

• Type investigation: Inferred types must be compared with semantic types. A postcode such as "00123" is categorical text, not a quantity, because arithmetic and removal of leading zeros would be inappropriate.
• Category inspection: df["status"].value_counts(dropna=False) reveals unexpected spellings, capitalization, rare labels, and missing entries. Values "Complete", "complete ", and "COMPLETED" may represent one category.
• Duplicate detection: df.duplicated() detects entirely repeated rows, while df.duplicated(subset=["customer_id"]) tests uniqueness according to a chosen key. Repeated customer identifiers are not automatically errors if each row represents a separate order.
• Range and rule checks: Boolean conditions expose records violating domain constraints.

invalid_age = df.loc[~df["age"].between(0, 120)]
invalid_total = df.loc[df["order_total"] < 0]

• between(0, 120) includes both endpoints by default.
• ~ negates the Boolean result, selecting ages outside the permitted interval.
• Negative totals may be errors or valid refunds, so context determines the rule.

• Cross-field checks: Values can be individually valid but jointly impossible. The condition df["end_date"] < df["start_date"] identifies records whose chronology is reversed.
• Outlier investigation: For a numeric variable, the interquartile range method uses

IQR = Q3 − Q1
lower bound = Q1 − 1.5 × IQR
upper bound = Q3 + 1.5 × IQR

• Q1 is the first quartile, Q3 is the third quartile, and IQR measures the middle 50% spread.
• An outlier is a value outside the bounds, but it is a candidate for investigation—not proof of an error.

• Worked example: Suppose age contains [19, 22, None, 220]. Investigation identifies one missing value and one range violation. It does not justify replacing either automatically; the original source or an approved correction rule is needed.

B. Applications and Limitations

Investigation supports reliable cleanup by converting vague suspicions into measurable quality findings.

• Data-quality report: A useful profile records each column’s type, null rate, unique count, minimum, maximum, and violated rules.
• Sampling limitation: head() shows only the first rows and can miss defects elsewhere; aggregate checks must cover the complete dataset.
• Inference limitation: Python detects patterns, not real-world truth. A value can satisfy every computational test and still be inaccurate.
• Privacy constraint: Investigation output should avoid exposing unnecessary personal data in logs, screenshots, or exception messages.

III. Matching — Recognizing Equivalent or Related Values

A. Matching

Matching determines whether values or records correspond despite differences in spelling, layout, capitalization, or completeness.

• Exact matching: Direct equality is appropriate after basic normalization. "python" == "python" is deterministic, fast, and easy to audit, but "Python " fails because of case and trailing whitespace.
• Normalized matching: Convert superficial variants to a canonical form before comparison.

normalized = (
    df["city"]
    .astype("string")
    .str.strip()
    .str.casefold()
)

• strip() removes leading and trailing whitespace.
• casefold() performs stronger Unicode-aware case normalization than lower().
• Missing values remain missing under pandas’ nullable string type.

• Dictionary matching: Known variants can be mapped to an approved vocabulary.

country_map = {
    "u.k.": "United Kingdom",
    "uk": "United Kingdom",
    "great britain": "United Kingdom",
}
df["country"] = normalized.map(country_map).fillna(df["country"])

• Mapping is transparent and suitable for stable, finite variant sets.
• fillna() retains the original where no mapping exists, although unmatched values should still be reported.

• Pattern matching: Regular expressions recognize values with shared structural rules.

pattern = r"^[A-Z]{2}\d{4}$"
valid_code = df["code"].str.fullmatch(pattern, na=False)

• ^ and $ anchor the whole string, [A-Z]{2} requires two uppercase letters, and \d{4} requires four digits.
• na=False classifies missing entries as non-matches rather than propagating missing results.

• Record linkage: Records may be matched using several fields, such as normalized name, postcode, and date of birth. A stable identifier is preferable because names are neither unique nor immutable.
• Fuzzy matching: Similarity methods can identify likely typographical variants. A score may be defined as

similarity(a, b) ∈ [0, 1]

• a and b are two strings; 1 indicates identity under the chosen algorithm, while values nearer 0 indicate less similarity.
• A threshold such as similarity >= 0.90 is a policy decision, not a universal guarantee.
  • Worked example: Normalize " Acme Ltd. " and "ACME LTD." by trimming whitespace, applying casefold(), and optionally removing punctuation. Both become "acme ltd", permitting an exact normalized match without speculative fuzzy logic.

B. Applications and Limitations

Matching is valuable for deduplication and standardization, but false matches can be more damaging than unresolved records.

1. Deterministic matching:
   • Strength: Exact keys, lookup tables, and validated patterns produce explainable results.
   • Limitation: They miss genuine equivalents not anticipated by the rules.
2. Probabilistic or fuzzy matching:
   • Strength: It tolerates misspellings and incomplete records.
   • Limitation: Similar names may refer to different entities, so borderline scores need review.

• Duplicate resolution: After a match, define which record survives—newest, most complete, or source-authoritative—rather than dropping rows arbitrarily.
• Validation: Measure reviewed true matches and false matches at the chosen threshold before applying fuzzy decisions broadly.

IV. Formatting — Producing Consistent Representations

A. Formatting

Formatting converts cleaned values into consistent types and representations suitable for computation, exchange, storage, or display.

• String cleanup: Vectorized string methods standardize whitespace and case.

df["name"] = (
    df["name"]
    .astype("string")
    .str.strip()
    .str.replace(r"\s+", " ", regex=True)
    .str.title()
)

• The expression \s+ replaces one or more whitespace characters with one space.
• Title case improves presentation but may damage intentional forms such as "McDONALD" or "van Gogh"; names often require less aggressive rules.

• Numeric conversion: Remove permitted display symbols before conversion, then expose failures explicitly.

amount_text = df["amount"].str.replace(",", "", regex=False)
df["amount"] = pd.to_numeric(amount_text, errors="coerce")

• "1,250" becomes numeric 1250.
• errors="coerce" converts unparseable text to NaN, which must be counted and reviewed.
• Currency should be stored separately or clearly defined; 100 is ambiguous without a unit such as USD.

• Date formatting: Parse dates into datetime values before displaying them as strings.

df["date"] = pd.to_datetime(
    df["date"], format="%Y-%m-%d", errors="coerce"
)

• %Y means four-digit year, %m month, and %d day.
• An explicit format prevents ambiguity between dates such as 03/04/2026.
• ISO-style YYYY-MM-DD is sortable and widely interoperable.

• Categorical formatting: Approved labels should be stored consistently, for example "Pending", "Complete", and "Cancelled". Converting to category can reduce memory use when a column contains a limited repeated vocabulary.
• Identifier preservation: Telephone numbers, account codes, and postcodes should usually remain strings. Formatting the identifier "0074" as an integer produces 74 and destroys significant zeros.
• Output formatting: Display formatting must remain separate from stored values. Store 0.175 as a numeric proportion and display it as "17.5%"; storing the percent sign would obstruct arithmetic.
• Worked example: The values " $1,200.50 " and "950" can be stripped, have the dollar sign and commas removed, and be converted to floats 1200.50 and 950.00. The currency must still be represented by metadata or a separate column.

B. Applications and Limitations

Formatting completes cleanup only when the transformed dataset still passes structural and semantic validation.

• Schema validation: Confirm required columns, types, ranges, nullability, and uniqueness after conversion.
• Round-trip check: Save and reload a small output to ensure dates, Unicode text, leading zeros, delimiters, and missing values survive serialization.
• Precision limitation: Binary floating-point is unsuitable for exact monetary arithmetic; use decimal.Decimal or integer minor units such as cents where exactness is required.
• Final comparison: Recalculate row counts, missing counts, duplicate counts, and invalid-value counts, and reconcile every intentional difference with a cleanup rule.