pandas cheat sheet

Reading, selecting, grouping, joining, and reshaping DataFrames, plus the Copy-on-Write and dtype changes that break old code.

ProgrammingIntermediate7 sections

Reading and writing

pd.read_csv("orders.csv", usecols=["order_id", "amount"], dtype={"order_id": "int32"}): Column pruning and explicit dtypes at read time is the cheapest memory win available. Without dtype every integer column lands as int64.
pd.read_csv("big.csv", chunksize=100_000): Returns an iterator of DataFrames rather than a DataFrame. Aggregate inside the loop and concatenate the small results, never the raw chunks.
pd.read_parquet("s3://lake/orders/", columns=["dt", "amount"], filters=[("dt", ">=", "2026-01-01")]): Filters push predicates into the Parquet reader so whole row groups are skipped on disk. Only the pyarrow engine honors them.
pd.read_csv("orders.csv", dtype_backend="pyarrow"): Arrow-backed columns give real nulls in integer and boolean columns instead of silent promotion to float64. Available since pandas 2.0.
df.to_parquet("out.parquet", index=False, compression="zstd"): Drop the index unless it carries meaning, otherwise every downstream reader inherits a phantom __index_level_0__ column.

df.loc[df["amount"] > 100, ["order_id", "amount"]]: Row filter and column projection in one pass. loc slices include the end label while iloc excludes it, which is the usual off-by-one.
df.query("amount > 100 and region == 'EU'"): Readable for long boolean chains and avoids repeating the frame name. It is slower than a boolean mask on small frames because of the parse step.
df.loc[mask, "amount"] = 0: The only assignment form that is guaranteed to write through. Chained forms such as df[mask]["amount"] = 0 silently do nothing under Copy-on-Write.
df.at[idx, "amount"]: Scalar access that skips the alignment machinery loc carries. Roughly an order of magnitude faster inside a loop you could not avoid.
df[df["region"].isin(["EU", "APAC"])]: isin beats chained or comparisons and handles nulls predictably. Pass a set for large candidate lists to get hash lookups.

pd.options.mode.copy_on_write = True: Enables the pandas 3.0 behavior on 2.x releases. Every slice becomes a lazy copy, so chained assignment fails silently instead of working by accident.
df["region"] = df["region"].astype("category"): Categoricals cut memory by an order of magnitude on low-cardinality strings and speed up group by. Concatenating mismatched categories falls back to object.
df.isna().mean().sort_values(ascending=False): Null rate per column in one line. Run it before any join to spot the key column that is thirty percent empty.
df.convert_dtypes(dtype_backend="numpy_nullable"): Promotes object columns to nullable extension dtypes. pd.NA propagates through comparisons, so a null compared to a null is NA rather than False.
df["ts"] = pd.to_datetime(df["ts"], format="ISO8601", utc=True): An explicit format skips per-row inference, which is faster and immune to the mixed-format silent misparse. The errors="ignore" option was removed in pandas 3.0.

df.groupby("region", observed=True, dropna=False).agg( total=("amount", "sum"), orders=("order_id", "nunique"), ): Named aggregation produces flat column names instead of a MultiIndex. dropna=False keeps the null-key group that would otherwise vanish from your totals.
df.groupby("customer_id")["amount"].transform("sum"): Broadcasts the group aggregate back to every row, the pandas equivalent of a SQL window function. The result keeps the original index alignment.
df.groupby("region")["amount"].agg(["sum", "mean", "count"]): count ignores nulls while size counts rows. Mixing the two is the classic reason two dashboards built from the same table disagree.
df.groupby("region", sort=False): Sorting group keys is on by default and costs real time on high-cardinality keys. Turn it off whenever the output order does not matter.
df.groupby("region").apply(fn, include_groups=False): apply is the slow escape hatch at roughly one Python call per group. include_groups=False is required in pandas 3.0 so the key columns are not passed into fn.

df.merge(dim, on="customer_id", how="left", validate="many_to_one", indicator=True): validate raises instead of silently fanning out rows when the dimension has duplicate keys. indicator adds a _merge column showing which side matched.
pd.merge_asof(trades, quotes, on="ts", by="symbol", direction="backward", tolerance=pd.Timedelta("1s")): The point-in-time join for pricing and slowly changing dimensions. Both frames must be sorted on the on key or it raises immediately.
df.pivot_table(index="dt", columns="region", values="amount", aggfunc="sum", fill_value=0): pivot_table aggregates duplicate index and column pairs while pivot raises on them. Use pivot when you want that error as a data quality check.
df.melt(id_vars=["dt"], var_name="metric", value_name="value"): Wide to long, the shape most warehouses want. Mixed dtypes across the melted columns collapse into one object column.
pd.concat([a, b], ignore_index=True): Concatenating inside a loop is quadratic because each call copies everything. Collect frames in a list and concatenate once at the end.

df.set_index("ts").resample("1D").agg({"amount": "sum"}): Resample requires a DatetimeIndex. Empty periods appear as rows of NaN, which is normally what you want for a calendar-complete series.
df.resample("ME").sum(): pandas 2.2 renamed the offset aliases, so M became ME, Q became QE, Y became YE, and H became h. The old spellings now warn or fail.
df["ts"].dt.tz_convert("America/New_York"): tz_convert needs a tz-aware column while tz_localize creates one. Localizing an already-UTC timestamp as local time is the standard off-by-hours bug.
df["amount"].rolling("7D", min_periods=1).mean(): A time-based window respects gaps in the index, unlike rolling(7) which just counts rows. min_periods stops the leading edge from being all NaN.
df.groupby("region")["amount"].shift(1): Lag within group. groupby preserves existing row order inside each group rather than sorting, so sort by the time column first.

df.memory_usage(deep=True).sum() / 1e6: deep=True follows object pointers to the real string bytes. Without it a column of long strings reports a flat eight bytes per row.
df.itertuples(index=False): Roughly ten times faster than iterrows because it does not build a Series per row. Still a last resort behind any vectorized alternative.
np.where(df["amount"] > 100, "high", "low"): The vectorized branch that replaces apply over a lambda. For more than two outcomes use np.select with an explicit default.
df.eval("margin = revenue - cost", inplace=True): Evaluates through numexpr without materializing intermediates. Worth using above roughly 100k rows and slower than plain arithmetic below that.
df["id"] = pd.to_numeric(df["id"], downcast="unsigned"): Downcasting int64 keys to the narrowest safe width often halves frame size. Recheck the range after appending, since a wider value forces an upcast.

From DataLane — tutorials at/blog, practice SQL live in theplayground.