Module 6: Data Cleaning & Wrangling

Module 6: Data Cleaning & Wrangling

1. Handling Missing Data — Deletion vs. Imputation Strategies

First, the question that precedes every technique: why is the data missing? This is the single highest-leverage distinction in this entire topic, and it’s a direct interview differentiator.

Missingness type Definition Example Implication
MCAR (Missing Completely at Random) Missingness has no relationship to any variable, observed or not A sensor randomly drops 1% of readings due to network blips Safe to delete or simple-impute — won’t bias results
MAR (Missing at Random) Missingness depends on observed data, not the missing value itself Older survey respondents are less likely to report income, but age is recorded Deletion biases results; imputation conditioned on the observed variable (age) can correct for it
MNAR (Missing Not at Random) Missingness depends on the unobserved value itself High earners are less likely to report income because it’s high The most dangerous case — neither deletion nor standard imputation fully fixes this; the missingness itself is informative

Talking point: “Before choosing a missing-data strategy, I try to diagnose which of these three I’m dealing with — usually by checking whether missingness correlates with other observed variables. If income is missing more often for high-job-title respondents, that’s a strong MNAR signal, and no amount of mean imputation will fix the resulting bias — I’d need to either model the missingness explicitly or flag the limitation rather than paper over it.”

Deletion strategies:

  • Listwise deletion (complete-case analysis) — drop any row with a missing value in any analyzed column. Simple, but can drastically shrink sample size and introduces bias if missingness isn’t MCAR.
  • Pairwise deletion — use all available data for each specific calculation (e.g., a correlation matrix uses different row subsets per pair). Preserves more data but can produce mathematically inconsistent results across related statistics.

Imputation strategies:

Method When appropriate Risk
Mean/median imputation Quick, MCAR, low % missing Artificially shrinks variance, distorts distribution shape, weakens correlations
Mode imputation Categorical data Same variance-shrinking issue
Forward/backward fill Time series with slow-moving values Wrong for volatile series — assumes false stability
Regression/model-based imputation MAR, when missingness relates to other known variables Can overstate confidence — the imputed value looks “real” but inherits model uncertainty that’s easy to forget about downstream
Multiple imputation Rigorous statistical work, MAR Computationally heavier, but properly propagates the uncertainty of imputation into final estimates rather than treating imputed values as ground truth
Flag + impute Any case where missingness might itself be predictive Add a was_missing boolean column alongside the imputed value — preserves the MNAR-style signal that “this was missing” might matter

Talking point on the most common production mistake: “The most common mistake I see is mean imputation applied uncritically — it keeps the mean unchanged, which looks safe, but it artificially deflates variance and weakens any correlation or regression analysis run afterward, because you’ve replaced real variability with a constant. If I impute, I usually also add a flag column so any signal in ‘this was missing’ isn’t silently destroyed.”

2. Outlier Detection and Treatment

Detection methods — know the trade-offs, not just the formulas:

Method Formula / approach Best for
Z-score Flag if ` z
IQR method Flag if outside [Q1 - 1.5×IQR, Q3 + 1.5×IQR] Robust to skew — doesn’t depend on the mean, so it’s the safer default for business data (revenue, latency) which is rarely normal
Visual Box plots, scatter plots Quick sanity check, catches multivariate outliers a single-variable rule would miss
Domain-rule-based Hardcoded business logic (age > 120, negative price) Catches data-entry errors specifically — distinct from statistical outliers, which may be legitimate extreme-but-real values

The critical judgment call this section is really testing: an outlier is not automatically an error. This distinction matters more than any detection formula:

  • Error outliers — a data-entry mistake, sensor malfunction, unit conversion bug (e.g., age = 999, negative inventory count). These should generally be corrected or removed.
  • Legitimate extreme values — a real whale customer, a real Black Friday spike, a genuine fraud case. Removing these destroys the signal you’re trying to study — fraud detection and whale-customer analysis are entirely about the outliers.

Talking point: “Before I treat anything as an outlier to be removed, I ask whether the analysis I’m doing is trying to understand outliers or trying to summarize typical behavior despite them. If I’m building a ‘typical user’ segmentation, I’d cap or exclude the whales. If I’m building a fraud or anomaly detection model, the outliers are the entire point, and removing them would defeat the purpose.” This framing — outlier treatment depends on the question, not a universal rule — is a strong senior-level point.

Treatment options once you’ve decided something needs handling:

  • Removal — only when confirmed as an error.
  • Capping/winsorizing — clip values beyond a percentile threshold (e.g., cap at 99th percentile) rather than deleting — preserves the row while limiting distortion.
  • Transformation — log transform compresses right-skewed data with extreme values into something closer to normal, often a better fix than removing data points outright.
  • Separate modeling/flagging — keep the outlier but flag it, so downstream analysis can choose to include/exclude as needed rather than baking the decision in upstream.

3. Data Type Issues, Deduplication, Normalization/Standardization

Data type issues — the unglamorous bugs that quietly break everything downstream:

  • Numbers stored as strings ("1,200" with a comma, or leading/trailing whitespace) — breaks aggregation and sorting silently or throws cast errors.
  • Dates stored inconsistently (MM/DD/YYYY vs DD/MM/YYYY vs ISO 8601) — a classic source of silent misinterpretation, especially in datasets combining US and international sources. 03/04/2026 is genuinely ambiguous without knowing the source convention.
  • Boolean inconsistency ("Yes"/"No" vs 1/0 vs TRUE/FALSE vs "Y"/"N" across merged sources) — needs explicit standardization before any filtering logic.
  • Encoding issues — categorical values like "N/A", "None", "null", empty string "", and an actual NULL are four different things to most tools unless explicitly unified — a frequent silent source of “why is my missing-data count wrong.”

Deduplication — harder than it sounds because duplicates aren’t always exact matches:

  • Exact duplicates — straightforward, DISTINCT/drop_duplicates().
  • Fuzzy duplicates — "Jon Smith" vs "Jonathan Smith" vs "J. Smith" referring to the same person; "Acme Inc." vs "ACME, Inc" vs "Acme Incorporated". Requires fuzzy matching (Levenshtein distance, phonetic matching like Soundex) or a deterministic business key (email, customer ID) rather than name-matching.
  • Talking point: “True deduplication usually isn’t a string-equality problem — it’s an entity-resolution problem. I’d rather match on a stable business key like email or customer ID where one exists, and only fall back to fuzzy name/address matching when no reliable key is available, because fuzzy matching always trades off false merges against missed merges.”

Normalization vs. Standardization — frequently confused, genuinely different purposes:

Formula Result Use when
Normalization (Min-Max scaling) (x - min) / (max - min) Rescales to a fixed range, usually [0,1] Algorithms sensitive to absolute scale bounds (e.g., neural nets); when you want a bounded, interpretable 0-1 range
Standardization (Z-score scaling) (x - mean) / std_dev Centers at 0, scales to unit variance, unbounded range Algorithms assuming roughly normal-ish input (regression, PCA, clustering with Euclidean distance); more robust to new data exceeding the training range than min-max

Talking point: “Normalization is bounded but fragile — a single new extreme value after deployment can compress the rest of your scaled data unpredictably, since min/max shift. Standardization is unbounded but more stable in production because it’s based on mean and variance, which move less under a single new outlier than a min or max does.” This is a good technical-judgment point bridging into any ML-adjacent interview question.

There’s also database normalization (1NF/2NF/3NF — reducing redundancy via table design) — worth distinguishing explicitly from statistical normalization above, since the same word means two unrelated things, and interviewers sometimes test whether you catch the ambiguity.

4. Data Quality Frameworks — Completeness, Consistency, Accuracy, Timeliness

A clean way to present this as a structured framework (commonly extended with Validity and Uniqueness to make six dimensions):

Dimension Question it answers Example check
Completeness Is all the expected data present? % of required fields that are non-null; are all expected dates/regions represented?
Consistency Does the data agree with itself across sources/time? Does total revenue in the data warehouse match the source transactional system? Same customer, same ID across all tables?
Accuracy Does the data reflect reality? Does recorded shipping address match what’s verifiably correct? (Harder to check than completeness — often requires an external source of truth)
Timeliness Is the data current enough for the decision being made? Is the dashboard showing yesterday’s data, or is it three days stale due to a broken pipeline?
Validity Does the data conform to defined formats/rules? Email fields actually contain valid email formats; age is a positive integer
Uniqueness Is each real-world entity represented exactly once? No duplicate customer records (ties directly back to deduplication above)

Talking point that operationalizes the framework rather than just listing it: “I treat this less as a checklist to recite and more as a set of automated checks that should run continuously, not just at analysis time — e.g., a daily check that row counts haven’t dropped to zero (completeness), that yesterday’s revenue total reconciles against the source system (consistency), and that the data landed within an expected SLA window (timeliness). This is exactly the same principle as ‘data validation at entry’ from the Excel module — catch quality problems as close to the source as possible, rather than discovering them three steps downstream in a stakeholder’s dashboard.” (Good explicit callback to your Excel module’s data validation point — ties the whole arc together.)

5. Python (pandas) Core Operations for Wrangling

Given your stack and the rest of this curriculum, I’ll go with pandas as the primary (most architect/interview-relevant for Java/AWS-adjacent data roles) — happy to add an R/dplyr parallel column if you want it for the document version.

import pandas as pd
import numpy as np

# --- Missing data ---
df.isna().sum()                                  # count missing per column
df.dropna(subset=['customer_id'])                # listwise deletion on specific column
df['income'] = df['income'].fillna(df['income'].median())   # median imputation
df['income_was_missing'] = df['income'].isna()   # flag BEFORE imputing — order matters

# --- Outliers (IQR method) ---
Q1, Q3 = df['amount'].quantile([0.25, 0.75])
IQR = Q3 - Q1
lower, upper = Q1 - 1.5*IQR, Q3 + 1.5*IQR
df['is_outlier'] = ~df['amount'].between(lower, upper)
df['amount_capped'] = df['amount'].clip(lower, upper)   # winsorize rather than drop

# --- Data types ---
df['order_date'] = pd.to_datetime(df['order_date'], format='%Y-%m-%d', errors='coerce')  
# errors='coerce' turns unparseable dates into NaT instead of crashing the pipeline —
# but ALWAYS check how many got coerced before trusting the result:
df['order_date'].isna().sum()

df['amount'] = pd.to_numeric(df['amount'].str.replace(',', ''), errors='coerce')

# --- Deduplication ---
df.duplicated(subset=['email']).sum()            # count exact dupes on a business key
df = df.drop_duplicates(subset=['email'], keep='last')   # keep most recent record

# --- Standardization ---
from sklearn.preprocessing import StandardScaler
df['amount_scaled'] = StandardScaler().fit_transform(df[['amount']])

# --- Data quality check pattern (completeness + validity in one pass) ---
quality_report = pd.DataFrame({
    'completeness_pct': df.notna().mean() * 100,
    'unique_count': df.nunique(),
    'dtype': df.dtypes
})

Talking point on errors='coerce': “This is a small detail that matters a lot in production — errors='coerce' silently converts unparseable values to NaN/NaT instead of crashing, which is convenient, but if you don’t explicitly check how many rows got coerced afterward, you can lose data silently and never notice. I always pair a coerce step with an immediate count of what just got nulled out.” This is exactly the kind of “small thing that bites people in production” detail that signals real hands-on experience rather than textbook knowledge.

Method chaining style (worth showing fluency in, since it’s idiomatic modern pandas and often discussed in code-review-style interview rounds):

clean_df = (
    df
    .dropna(subset=['customer_id'])
    .assign(order_date=lambda d: pd.to_datetime(d['order_date'], errors='coerce'))
    .assign(amount=lambda d: d['amount'].clip(lower, upper))
    .drop_duplicates(subset=['email'], keep='last')
)

This module completes the practical core of the Data Analyst track: Orientation → SQL → Excel → Statistics → A/B Testing → Data Cleaning. A natural next step is Data Visualization & Dashboard Design (chart selection, dashboard anti-patterns — the “Visualize” lifecycle stage that’s still open), or pivoting into Python/pandas for Analysis (groupby, merge, reshape — the analytical operations beyond just cleaning, building directly on the pandas syntax introduced here).