Data Cleaning

Understanding Data Cleaning

Data cleaning makes recorded values usable for a specific question; it does not make uncertain information true. Before changing anything, define what one row represents, which fields identify it, and what values are allowed. For a customer snapshot, one row might represent one customer on one date. For transactions, repeated customers are expected. These definitions determine whether apparently repeated records are errors or legitimate observations.

Start by profiling the original data: inspect column types, missing counts, category frequencies, and numeric ranges. Preserve an unchanged copy. A column containing digits may still be text, so arithmetic can fail or behave unexpectedly. Conversely, identifiers such as postal codes should often remain strings because leading zeros matter. Conversion must follow meaning, not appearance. Explicit date formats and measurement units are equally important.

Missingness needs interpretation. An unanswered question, a failed numeric conversion, and a genuinely inapplicable field may all appear as missing, but they describe different problems. In pandas, use missing-value methods such as isna() rather than equality comparisons with missing markers. Removing incomplete rows changes which observations contribute to results. Filling ages with the median preserves row counts but creates estimated values, reduces apparent variation, and does not automatically remove bias.

Duplicates also require a rule. Exact repeated rows may be accidental copies; repeated identifiers with conflicting values need investigation. Normalizing whitespace and case can reveal equivalent labels, but normalization is not universally harmless: case-sensitive identifiers must retain their distinctions. Similarly, an outlier is unusual, not necessarily wrong. A negative age violates a basic constraint, whereas an unusually high purchase amount might be genuine. Flag suspicious values before deciding whether to correct, exclude, or retain them.

Finally, validate and document the result. Record each rule, its justification, affected rows, and changes to important summaries. The pandas guide organizes relevant tools under missing data, text operations, types, and duplicate data; these tools implement decisions rather than choose them. If cleaning supports predictive modeling, estimate medians or other learned preprocessing only from training data, then reuse them on held-out data. Scikit-learn pipelines help maintain that separation. For descriptive analysis, report denominators and limitations so readers know whose data actually contributed to each statistic.

Practical walkthrough

Consider a small customer snapshot used to summarize recorded ages and regional coverage. Assume each customer ID should appear once, region labels are case-insensitive, and ages must fall between zero and 120 inclusive. This upper limit is a declared screening rule, not a universal biological boundary. The example requires pandas but reads no files and downloads nothing.

First, construct the raw table and copy it. Inspecting these six input rows reveals one exact repeated record, one missing age, one nonnumeric age, and one negative age. Remove only the exact duplicate and print the removal count with its reason. Then check identifier uniqueness. If conflicting records shared an ID, this check would stop the workflow instead of arbitrarily choosing one.

Next, strip surrounding whitespace from region labels and lowercase them. This unifies labels without guessing that different region names are synonyms. Parse ages with to_numeric using errors='coerce': unparseable entries become missing. Count conversion failures separately from values that were already missing. That distinction identifies whether a source field was absent or contained unusable text.

Apply the declared range rule to parsed ages. Keep the customer rows, but replace invalid ages with missing values and retain separate flags explaining the problems. No median filling is needed for this descriptive task: the report can show the mean among usable ages alongside its denominator.

Finally, assert that all retained ages satisfy the rule, and print an audit summary plus selected cleaned fields. In a larger workflow, preserve this audit with the cleaning script and raw dataset version.

Worked example

import pandas as pd

raw = pd.DataFrame({
    'id': [1, 2, 2, 3, 4, 5],
    'region': [' North ', 'SOUTH', 'SOUTH', 'north', ' South ', 'NORTH'],
    'age': ['20', None, None, 'oops', '-5', '40']
})

df = raw.copy().drop_duplicates()
print(f'Rows removed (exact duplicates): {len(raw) - len(df)}')
assert df['id'].is_unique, 'Conflicting customer IDs require review'

df['region'] = df['region'].str.strip().str.lower()
df['age_missing_source'] = df['age'].isna()
parsed = pd.to_numeric(df['age'], errors='coerce')
df['age_parse_failed'] = df['age'].notna() & parsed.isna()
df['age_out_of_range'] = parsed.notna() & ~parsed.between(0, 120)
df['age'] = parsed.mask(df['age_out_of_range'])

assert df['age'].dropna().between(0, 120).all()
print(f"Missing ages in source after deduplication: {int(df['age_missing_source'].sum())}")
print(f"Age conversion failures: {int(df['age_parse_failed'].sum())}")
print(f"Ages rejected by range rule: {int(df['age_out_of_range'].sum())}")
print(f'Customers retained: {len(df)}')
print(f"Usable ages: {int(df['age'].count())}")
print(f"Mean usable age: {df['age'].mean():.2f}")
print('id | region | age')
for row in df.itertuples(index=False):
    age_text = 'missing' if pd.isna(row.age) else f'{row.age:.0f}'
    print(f'{row.id} | {row.region} | {age_text}')

Expected output

Rows removed (exact duplicates): 1
Missing ages in source after deduplication: 1
Age conversion failures: 1
Ages rejected by range rule: 1
Customers retained: 5
Usable ages: 2
Mean usable age: 30.00
id | region | age
1 | north | 20
2 | south | missing
3 | north | missing
4 | south | missing
5 | north | 40

The mean is (20 + 40) / 2 = 30, not a mean over all five customers. Both usable ages belong to northern customers, so this number cannot describe southern customers' ages from these records. Filling every missing age with 30 would leave the overall mean unchanged while concealing the lack of observed southern ages. Retaining rows preserves regional customer counts: three north and two south. The three issue flags preserve why ages became unavailable. The raw table also remains intact. Assertions verify selected structural rules, but they cannot establish whether a plausible recorded age is accurate or representative.

Common mistakes and how to avoid them

• Treating median filling as an automatic repair.

  Choose a missing-data policy based on the analysis. Report observed counts, preserve an imputation flag when filling, and compare conclusions under plausible alternatives. A filled value is not a recovered observation.

• Dropping every repeated customer ID.

  Define the row's meaning first. Repeated IDs may represent different visits. For a snapshot, inspect conflicting records and apply an explicit reconciliation rule rather than silently keeping the first.

• Coercing types without measuring the damage.

  Count newly missing values and retain original entries for inspection. A sudden increase in conversion failures may indicate changed units, formatting, or an upstream export problem.

• Learning cleaning thresholds from the entire modeling dataset.

  Split before estimating medians, scaling parameters, or data-derived outlier thresholds. Fit them on training data only, including within each cross-validation fold, and apply unchanged transformations to validation data.

Review questions

Further reading

• pandas user guide
• scikit-learn user guide