data-wrangling

# 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:

1. **Each variable forms a column.** A single column should contain values of exactly one variable.
2. **Each observation forms a row.** A single row should contain all values for exactly one observational unit.
3. **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 posit