---
name: wrangle-tabular-data
description: Cleans, transforms, joins, reshapes, and aggregates tabular data (CSV/Parquet/DataFrames) with pandas, handling missing values, type coercion, dedup, and time-series resampling.
when_to_use: When the user needs to clean or transform a dataset in code — fix missing/dirty values, coerce types, dedup, join/merge tables, pivot/melt/reshape, group-and-aggregate, or resample a time series, typically producing a cleaned CSV/DataFrame for downstream use.
---

## When to Use

Use this when transforming a dataset **in code** and emitting a cleaned artifact (CSV/Parquet/DataFrame) for a downstream step.

Triggers: fix missing/dirty values, coerce dtypes, dedup, join/merge tables, pivot/melt/reshape, groupby-aggregate, rolling/window, or resample a time series.

Not this skill:
- Producing a formatted `.xlsx` for a human to open → use **build-spreadsheet** (this skill is a code-level transform, the output is data not presentation).
- Only inspecting/summarizing a dataset without changing it → use **profile-dataset** (transform vs. inspect).

## Steps

1. **Load with explicit dtypes — never trust the inferrer.**
   - CSV: `pd.read_csv(path, dtype={...}, parse_dates=[...], na_values=["", "NA", "null", "-", "N/A"], keep_default_na=True)`. Pass `dtype=str` for ID/code/zip columns to stop them becoming floats (`007` → `7.0`, leading zeros gone forever).
   - Parquet: `pd.read_parquet(path)` already carries a schema — read it, don't re-coerce blindly.
   - Capture `df.shape` and `df.dtypes` immediately; these are the baseline for the transformation log.

2. **Diagnose before touching anything.** Run `df.isna().sum()`, `df.nunique()`, and `df.dtypes`. For each `object` column you expect numeric/date, check why it's `object` (usually one stray non-numeric value or a thousands separator). Decide the strategy per column up front — do not clean ad hoc.

3. **Coerce types deliberately.**
   - Numeric: `pd.to_numeric(s, errors="coerce")` then inspect the new NaNs (those are your dirty rows — log how many). Strip `$`, `,`, `%` first: `s.str.replace(r"[,$%]", "", regex=True)`.
   - Datetime: `pd.to_datetime(s, errors="coerce", utc=True)` — force UTC so you never carry tz-naive timestamps into a resample/merge. If source is local time, localize then convert.
   - Categorical: convert low-cardinality string columns with `.astype("category")` (memory + faster groupby).

4. **Clean values.**
   - Missing: pick per column — `dropna(subset=[...])` for required keys, `fillna(value)` for known defaults, `ffill`/`bfill` only for ordered/time-series data. State which strategy and why in the log. Never blanket `fillna(0)` across the frame.
   - Strings: `s.str.strip()`, normalize case (`.str.lower()`), collapse whitespace (`.str.replace(r"\s+", " ", regex=True)`) before dedup/join — invisible trailing spaces silently break key matches.
   - Outliers: only act if asked; clip (`.clip(lower, upper)`) or flag, don't silently drop.

5. **Dedup with intent.** `df.duplicated(subset=[keys]).sum()` first to know the count. Then `df.drop_duplicates(subset=[keys], keep="first")`. Choosing `keep` matters when non-key columns differ — sort first if "latest wins" (`sort_values("ts").drop_duplicates(subset=keys, keep="last")`).

6. **Merge/join — always validate cardinality.** Use `validate=` to make pandas raise on a bad assumption: `df.merge(other, on="id", how="left", validate="m:1")`. Options: `1:1`, `1:m`, `m:1`, `m:m`. After merge, assert row count didn't explode (`assert len(out) <= len(left) * expected`). Check `indicator=True` to count unmatched keys (`how="left"` + `_merge == "left_only"`).

7. **Reshape.**
   - Long→wide: `df.pivot_table(index=..., columns=..., values=..., aggfunc=...)` (use `pivot_table` not `pivot` so duplicate index/column pairs aggregate instead of raising).
   - Wide→long: `df.melt(id_vars=[...], value_vars=[...], var_name=..., value_name=...)`.
   - Aggregate: `df.groupby(keys, dropna=False).agg(out_col=("src", "func"))` — named aggregation keeps column names clean; `dropna=False` so NaN keys aren't silently dropped.
   - Window: sort by the order column first, then `.rolling(window)` / `.expanding()` / `.shift()`.

8. **Time-series resample.** Index must be a tz-aware DatetimeIndex (`df.set_index("ts")`). Then `df.resample("1h").agg(...)` (downsample) or `.resample("1min").interpolate()` / `.asfreq()` (upsample). Per-group: `df.groupby("id").resample("1D").sum()`. Confirm the freq string is right (`"h"`, `"D"`, `"W"`, `"M"`, `"MS"` differ).

9. **Big files — stay in memory budget.**
   - Read in chunks: `for chunk in pd.read_csv(path, chunksize=500_000): ...` and reduce per chunk.
   - Set `dtype` + `category` at read time (biggest single win).
   - Select columns with `usecols=[...]` — never load columns you won't use.
   - If pandas still won't fit, switch the load+filter step to `pyarrow`/`polars` and hand a smaller frame back to pandas.

10. **Output + transformation log.** Write the artifact (`to_parquet` preferred — preserves dtypes; `to_csv(index=False)` if a CSV is required). Then emit a short log: rows in→out, columns dropped/added, dtype changes, NaNs coerced, duplicates removed, join match rate. This is the deliverable's audit trail, not optional.

## Common Errors

- **Silent 1:many join blow-up.** A left join on a non-unique right key multiplies rows. Symptom: output row count jumps, later aggregates double-count. Fix: always pass `validate="m:1"` (or the correct cardinality) so pandas raises instead of silently fanning out.
- **Mixed-type column read as `object`.** One `"N/A"` in a numeric column makes the whole column `object`; arithmetic then fails or coerces weirdly. Fix: `pd.to_numeric(errors="coerce")` and inspect the produced NaNs.
- **Leading zeros / big-int IDs corrupted on load.** `read_csv` infers `int64`/`float64` and drops leading zeros or rounds 19-digit IDs. Fix: `dtype=str` for any code/ID/zip/phone column.
- **Tz-naive timestamps breaking resample/merge.** Mixing naive and aware datetimes raises or aligns wrong across DST. Fix: `utc=True` on every `to_datetime`; localize source local-time explicitly.
- **`pivot` raising on duplicates.** `df.pivot` errors on duplicate index/column pairs. Fix: use `pivot_table` with an explicit `aggfunc`.
- **`groupby` silently dropping NaN keys.** Default `dropna=True` makes rows with NaN group keys vanish from the result. Fix: `groupby(..., dropna=False)` when NaN is a real category.
- **`fillna`/`ffill` on unsorted data.** Forward-fill before sorting propagates the wrong value. Fix: `sort_values` by the order/time column before any fill or rolling op.
- **Chained-assignment / `SettingWithCopyWarning`.** Edits on a slice don't stick. Fix: operate on `.copy()` or use `.loc[mask, col] = ...`.

## Verify

- Row count: `len(out)` matches expectation (joins didn't explode, dedup removed the diagnosed count, filters dropped what you intended).
- Dtypes: `out.dtypes` are final (no stray `object` where numeric/datetime expected); IDs still strings with leading zeros intact.
- Keys: `out.duplicated(subset=keys).sum() == 0` after dedup; join match rate logged (unmatched-key count is acceptable and known, not surprising).
- No accidental NaN: `out.isna().sum()` only shows NaNs you chose to keep.
- Time series: index is tz-aware DatetimeIndex, no gaps/duplicates in the resampled frequency.
- Round-trip: re-read the written artifact and confirm `shape` + `dtypes` survive (Parquet keeps them; CSV will re-infer — re-pass `dtype` if the CSV is the handoff format).
- Transformation log exists and reconciles in→out counts.