Data Wrangling
Data wrangling is the work that sits between raw data and analysis -- the unglamorous, indispensable practice of making data trustworthy. Estimates vary, but practitioners consistently report that 60-80% of analysis time is spent wrangling. This skill covers the principles and techniques of data cleaning, transformation, reshaping, and integration, grounded in Hadley Wickham's tidy data framework and extended to the realities of messy real-world datasets.
Agent affinity: tukey (EDA-driven cleaning), nightingale (routing wrangling tasks)
Concept IDs: data-data-sources, data-data-quality, data-sampling-bias
The Wrangling Pipeline
| Stage | Goal | Key operations |
|---|---|---|
| 1. Ingestion | Get data into a working environment | Read CSV/JSON/Parquet/SQL, handle encodings, parse dates |
| 2. Profiling | Understand what you have | Shape, dtypes, nulls, distributions, cardinality |
| 3. Cleaning | Fix structural problems | Dedup, type coercion, standardize categories, fix encodings |
| 4. Missing data | Handle gaps | Detect patterns (MCAR/MAR/MNAR), impute or flag |
| 5. Transformation | Derive analysis-ready features | Normalize, bin, log-transform, create indicators |
| 6. Reshaping | Match the analysis structure | Melt, pivot, tidy form, denormalize |
| 7. Integration | Combine sources | Joins (inner/left/right/full/cross), concatenation, dedup post-join |
| 8. Validation | Confirm readiness | Schema checks, assertion tests, row-count reconciliation |
Tidy Data Principles
Hadley Wickham (2014) formalized "tidy data" as three rules:
- Each variable forms a column. A single column should contain values of exactly one variable.
- Each observation forms a row. A single row should contain all values for exactly one observational unit.
- Each type of observational unit forms a table. Mixing patient demographics and lab results in one table violates this rule.
Most messy datasets violate one or more of these rules in predictable ways:
| Violation | Example | Fix |
|---|---|---|
| Column headers are values, not variable names | Columns: income_2020, income_2021, income_2022 | Melt to columns: year, income |
| Multiple variables stored in one column | "M-25" encodes both sex and age | Split into sex and age columns |
| Variables stored in both rows and columns | Pivot table with row headers as categories | Melt and re-pivot to tidy form |
| Multiple types in one table | Patient info mixed with visit records | Normalize into two related tables |
| One type spread across multiple tables | Monthly CSV files with identical schema | Concatenate with a month column |
Tidy data is not the only valid structure -- wide formats are sometimes more efficient for computation or display. But tidy form is the canonical starting point for analysis, and most tools (ggplot2, pandas groupby, SQL aggregation) assume it.
Cleaning Techniques
Type Coercion
Raw data arrives as strings. Coercion converts to the correct type:
- Numeric: Strip currency symbols, commas, whitespace. Handle locale-specific decimals (
,vs.). Flag non-numeric values rather than silently converting to NaN. - Dates: Parse with explicit format strings, never rely on automatic detection. Time zones matter -- store in UTC, display in local.
- Categorical: Standardize case, strip whitespace, map synonyms (
"USA","US","United States"->"US"). Use controlled vocabularies where possible. - Boolean: Map common representations (
"yes"/"no","1"/"0","true"/"false","Y"/"N") to a single canonical form.
Deduplication
Exact duplicates are trivial to detect. The hard cases are near-duplicates:
- Record linkage: When the same entity appears with slight variations (
"John Smith"vs"J. Smith"vs"SMITH, JOHN"). Use fuzzy matching (Levenshtein distance, phonetic encoding) with a human-reviewed threshold. - Temporal duplicates: The same event recorded at slightly different timestamps. Define a dedup window and keep the first/last/most-complete record.
- Key discipline: Always define what constitutes a unique observation before deduplication. A table of purchases has a different uniqueness key than a table of customers.
Outlier Detection
Outliers are not errors -- they are values that warrant investigation:
- Statistical: Values beyond 1.5 * IQR (Tukey's fences), or beyond 3 standard deviations. These thresholds are guidelines, not laws.
- Domain-based: A human age of 150 is an error. A human age of 95 is unusual but valid. Domain knowledge trumps statistical rules.
- Multivariate: A value can be normal on each variable individually but extreme in combination (e.g., age 25 with 40 years of work experience). Mahalanobis distance or isolation forests detect these.
Action on outliers: Investigate first. If the value is a data entry error, correct it. If it is a measurement error, flag it. If it is a genuine extreme value, keep it and note its influence on summary statistics.
Missing Data
Missing Data Mechanisms
Rubin (1976) classified three mechanisms:
| Mechanism | Definition | Example | Implication |
|---|---|---|---|
| MCAR | Missingness is unrelated to any variable | Lab sample randomly dropped | Safe to drop or impute; no bias |
| MAR | Missingness depends on observed variables | High-income respondents skip income question less often | Imputation using observed predictors is valid |
| MNAR | Missingness depends on the missing value itself | People with depression less likely to report depression severity | No imputation is fully valid; requires sensitivity analysis |
Handling Strategies
| Strategy | When to use | Trade-off |
|---|---|---|
| Listwise deletion | MCAR, small fraction missing (<5%) | Simple but loses observations |
| Pairwise deletion | MCAR, different analyses need different subsets | Keeps more data but correlation matrices may not be positive-definite |
| Mean/median imputation | Quick exploration only | Reduces variance, biases correlations toward zero |
| Regression imputation | MAR, continuous variables | Better than mean but inflates R-squared |
| Multiple imputation | MAR, formal inference | Gold standard; accounts for imputation uncertainty |
| Indicator method | Any mechanism, tree-based models | Add a binary "was_missing" column; let the model learn missingness patterns |
| Domain-specific fill | Known defaults | "No response" for surveys, 0 for counts that should exist |
Joins and Integration
Join Types
| Join | Keeps | Use when |
|---|---|---|
| Inner | Rows matching in both tables | You only want complete matches |
| Left | All rows from left, matching from right | Left table is the primary; right is enrichment |
| Right | All rows from right, matching from left | Symmetric to left join |
| Full outer | All rows from both | You need the complete picture of both sources |
| Cross | Every combination of left and right rows | Generating all pairs (e.g., all product-store combinations) |
| Anti | Left rows with NO match in right | Finding orphans or gaps |
Join Hazards
- Many-to-many joins: Produce a Cartesian product of matching rows. Row count explodes. Always check cardinality before joining.
- Key mismatches: Different key formats (
"001"vs1), trailing whitespace, case differences. Standardize keys before joining. - Null keys: NULLs never match other NULLs in standard SQL joins. Decide how to handle null-keyed rows explicitly.
- Post-join dedup: Joins can introduce duplicates when key relationships are not 1:1. Validate row counts after every join.
Transformation Techniques
Normalization and Scaling
| Method | Formula | Use when |
|---|---|---|
| Min-max | (x - min) / (max - min) | Need values in [0, 1]; distribution shape preserved |
| Z-score | (x - mean) / std | Need zero-centered data; as |