Data wrangling transforms raw, messy datasets into clean, analysis-ready structures. This cheat sheet covers the full spectrum of wrangling operations across pandas (v3.0+, with Copy-on-Write enabled by default), SQL (PostgreSQL / DuckDB), PySpark, Polars, and specialized tools such as Great Expectations, pandera, Soda Core, RapidFuzz, Splink, lakeFS, DVC, ydata-profiling, and OpenRefine. Techniques are ordered from foundational tasks every analyst performs daily to advanced probabilistic and distributed workflows.
What This Cheat Sheet Covers
This topic spans 23 focused tables and 146 indexed concepts, 101 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
A jump-to index of every table row in this cheat sheet.
An interactive map of every table and concept in this topic.
1. Missing Data Handling
| Technique | Example | Description | |
|---|---|---|---|
df.dropna(subset=["col"]) | • Drop rows (or columns with axis=1) where specified fields are null &bull• how="all" drops only if every value is missing &bull• thresh=n keeps rows with at least n non-null values | ||
df["col"].fillna(df["col"].median()) | • Replace NaN with a scalar, dict, Series, or method &bull• method="ffill" / "bfill" propagates last valid value forward / backward | ||
df["col"].interpolate(method="linear") | • Fill gaps using interpolation &bull • Methods include "linear", "time", "polynomial", "spline" &bull• Best for ordered numeric / time-series data | ||
df = df.convert_dtypes() | • Pandas 3.0 uses pd.NA (not np.nan) as the canonical missing sentinel for nullable dtypes &bull• Propagates correctly through boolean and integer operations | ||
COALESCE(a, b, 0) | • SQL &bull • Returns the first non-null argument &bull • Use to provide fallback defaults in SELECT or WHERE clauses | ||
NULLIF(col, '') | • SQL &bull • Returns NULL when two expressions are equal &bull • Commonly converts empty strings or sentinel values to NULL for consistent null handling | ||
df.with_columns(pl.col("c").fill_null(0)) | • Polars &bull • Replace null values with a literal, expression, or strategy ( "forward", "backward", "mean", "min", "max") | ||
sdf.na.fill({"col": 0}) | • PySpark &bull • na.fill replaces nulls per-column &bull• na.drop removes rows with nulls &bull• Operates at distributed scale |