Pandas DataFrames: Command Center
1 · The lesson
readNumPy is fast and homogeneous. Real data is messy and heterogeneous — a column of strings next to a column of dates next to a column of floats, with missing values scattered through. Pandas wraps NumPy in a labelled, mixed-dtype, group-aware container and adds the operational vocabulary of a database: join, filter, aggregate, pivot, resample. A DataFrame is the working unit of analysis in Python.
You've seen the basics in ds-pandas. This lesson is the deep dive — the mental model, the indexing rules that catch everyone, groupby internals, the merge taxonomy, reshape primitives, and the performance traps that turn a 30-second job into a 30-minute job.
1. The Mental Model — Ordered Dict of Aligned Series
A DataFrame is an ordered dict of Series, all sharing a single Index. Every column is a Series — a NumPy array plus a labelled index. The DataFrame's columns are themselves an Index (of column names), and every row is identified by the row Index.
import pandas as pd df = pd.DataFrame({ "name": ["Alice", "Bob", "Carol", "Dan"], "age": [30, 25, 28, 40], "salary": [80000, 65000, 72000, 95000], }) print(df) # name age salary # 0 Alice 30 80000 # 1 Bob 25 65000 # 2 Carol 28 72000 # 3 Dan 40 95000 print(df.index) # RangeIndex(start=0, stop=4, step=1) print(df.columns) # Index(['name', 'age', 'salary'], dtype='object') print(type(df["age"])) # <class 'pandas.core.series.Series'>
Internally, each column is stored as a separate NumPy array (or in modern pandas, possibly a PyArrow-backed array). Operations are vectorised per column. Mixed-dtype operations stay efficient because pandas never widens an int column just because another column is a str.
The Index is not optional. Every Series and DataFrame has one. Use it deliberately — set it to your actual key (date, user_id, ticker) and joins become trivial.
2. Index Essentials
import pandas as pd df = pd.DataFrame({ "user_id": [101, 102, 103, 104], "name": ["Alice", "Bob", "Carol", "Dan"], "score": [88, 92, 75, 81], }) # Promote a column to the index df = df.set_index("user_id") print(df.loc[102]) # row whose user_id is 102 — Bob, 92 # Reset back to a default RangeIndex df = df.reset_index() # user_id comes back as a regular column # Multi-index — composite key panel = pd.DataFrame({ "year": [2024, 2024, 2025, 2025], "region": ["EU", "US", "EU", "US"], "sales": [100, 200, 130, 250], }).set_index(["year", "region"]) print(panel) # sales # year region # 2024 EU 100 # US 200 # 2025 EU 130 # US 250 print(panel.loc[(2024, "US")]) # 200 print(panel.loc[2024]) # all 2024 rows panel.xs("US", level="region") # cross-section: all US rows across years
A multi-index is how pandas does N-dimensional labelled data with a 2-D structure. You can also build one explicitly:
idx = pd.MultiIndex.from_tuples( [(2024, "EU"), (2024, "US"), (2025, "EU")], names=["year", "region"], )
setup added so this can run · defines pd
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) pd = _AutoMock('pd')
xs, unstack, and stack (covered later) all rely on multi-indices.
3. Selecting — .loc, .iloc, .at, .iat, .query
Four ways to pick rows and columns. They are not interchangeable.
import pandas as pd df = pd.DataFrame({ "A": [10, 20, 30, 40], "B": [1.1, 2.2, 3.3, 4.4], "C": ["x", "y", "z", "w"], }, index=["a", "b", "c", "d"]) # .loc — by LABEL df.loc["b"] # row labelled "b" (a Series) df.loc["b", "A"] # scalar: 20 df.loc["a":"c"] # rows "a" through "c" — INCLUSIVE of "c" df.loc[:, ["A", "C"]] # all rows, columns A and C df.loc[df["A"] > 15] # rows where A > 15 — boolean mask # .iloc — by INTEGER POSITION df.iloc[1] # second row (a Series) df.iloc[1, 0] # scalar at row 1, col 0: 20 df.iloc[0:2] # first two rows — HALF-OPEN like Python df.iloc[:, [0, 2]] # all rows, columns at positions 0 and 2 # .at / .iat — scalar access, fastest df.at["b", "A"] # 20 — equivalent to .loc but only for one cell df.iat[1, 0] # 20 — equivalent to .iloc but only for one cell # .query — string syntax, often clearer for compound conditions df.query("A > 15 and C != 'w'") df.query("A in [10, 30]")
The critical rule: .loc is inclusive at the upper bound ("a":"c" includes "c"), .iloc is half-open like normal Python slicing (0:2 excludes 2). Mixing them up is a common off-by-one bug.
.query is your friend for compound filters — df[(df["A"] > 15) & (df["B"] < 4)] becomes df.query("A > 15 and B < 4"). Easier to read, no operator-precedence parens.
4. The Chained-Indexing Trap
This is the #1 pandas footgun.
import pandas as pd df = pd.DataFrame({"A": [1, 2, 3], "B": [10, 20, 30]}) # WRONG — chained indexing on assignment df[df["A"] > 1]["B"] = 999 # SettingWithCopyWarning print(df["B"].tolist()) # [10, 20, 30] — unchanged!
df[df["A"] > 1] returns a new DataFrame (a copy, in this case). ["B"] = 999 then mutates that copy and throws it away. The original is untouched. Pandas warns you (SettingWithCopyWarning), but it doesn't error.
The fix — single .loc call:
df.loc[df["A"] > 1, "B"] = 999 print(df["B"].tolist()) # [10, 999, 999] — correct
setup added so this can run · defines df
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) df = _AutoMock('df')
Rule of thumb: any assignment to a filtered DataFrame goes through a single .loc[row_mask, col_label] = value. Never df[mask][col] = value. Never df[col][mask] = value. Always one .loc.
The same applies to reads when you intend to mutate later: subset = df.loc[mask].copy() if you're about to modify subset and don't want pandas to be ambiguous about whether you're aliasing the original.
5. Missing Data
NaN (float) is the historical missing-value sentinel; NA (pandas-native) is the modern unified one. Both are detected by .isna() / .notna().
import pandas as pd import numpy as np df = pd.DataFrame({ "x": [1.0, 2.0, np.nan, 4.0, np.nan], "y": ["a", None, "c", "d", "e"], }) df.isna() # element-wise True/False df.isna().sum() # missing count per column: x=2, y=1 df.dropna() # drop any row with any NaN df.dropna(subset=["x"]) # drop rows missing x only df.dropna(thresh=2) # keep rows with >= 2 non-null values df.fillna(0) # all NaN -> 0 df.fillna({"x": df["x"].mean(), # different fill per column "y": "unknown"}) # Forward-fill (carry last observation forward) — common for time series df.fillna(method="ffill") # pandas <= 2.0 syntax df.ffill() # modern equivalent df.bfill() # backward-fill
Be principled about NaN handling. Imputing the mean changes the distribution; dropping rows changes the sample. Whatever you do, do it once at a documented stage of the pipeline — not silently scattered across notebooks.
6. Apply, Map, Transform, Agg
Four functions, four contracts. Pick by input shape → output shape.
| Method | Input | Output | Use when |
|---|---|---|---|
Series.map(fn) | one element | one element | element-wise transform of a Series |
DataFrame.apply(fn, axis=1) | a row Series | scalar or Series | row-wise computation that needs all cols |
DataFrame.apply(fn, axis=0) | a column Series | scalar or Series | column-wise reduction or transform |
groupby.transform(fn) | a group's column | same-shape array (per group) | broadcast group-level value back to rows |
groupby.agg({...}) | each group's columns | one scalar per agg per group | multi-column aggregations |
import pandas as pd df = pd.DataFrame({ "name": ["Alice", "Bob", "Carol"], "salary": [80000, 65000, 72000], "bonus": [5000, 3000, 4500], }) # map — Series element-wise df["name_upper"] = df["name"].map(str.upper) # apply axis=1 — row-wise function df["total_comp"] = df.apply(lambda r: r["salary"] + r["bonus"], axis=1) # apply axis=0 — column reduction (rare; built-ins are faster) df[["salary", "bonus"]].apply(lambda col: col.max() - col.min()) # When a vectorised op exists, USE IT df["total_comp"] = df["salary"] + df["bonus"] # 100x faster than apply
apply(axis=1) is the slowest legitimate way to compute a column. It runs a Python function per row. Reach for it only when the operation genuinely can't be vectorised — calling a regex, parsing a JSON cell, hitting an API. For arithmetic, always use vectorised operators.
7. GroupBy In Depth
groupby is the database GROUP BY clause, exposed as a chainable Python object. The split-apply-combine paradigm:
1. Split the DataFrame by some key.
2. Apply an operation to each group.
3. Combine the results into a single output.
import pandas as pd df = pd.DataFrame({ "team": ["A", "A", "B", "B", "B", "C"], "player": ["P1", "P2", "P3", "P4", "P5", "P6"], "points": [10, 15, 8, 12, 20, 5], "games": [3, 5, 4, 6, 5, 2], }) g = df.groupby("team") g.size() # rows per group # team # A 2 # B 3 # C 1 g["points"].mean() # mean points per team g["points"].agg(["mean", "sum", "max"]) # multiple aggs in one shot # Multi-column aggs with custom names g.agg( total_pts=("points", "sum"), avg_pts=("points", "mean"), games_played=("games", "sum"), ) # total_pts avg_pts games_played # team # A 25 12.5 8 # B 40 13.3 15 # C 5 5.0 2 # Custom aggregation function g["points"].agg(lambda s: s.max() - s.min()) # range per group
transform — broadcast per-group results back
agg collapses each group to one row. transform returns the same shape as the input — useful for "subtract the group mean from each row":
df["pts_vs_team_avg"] = df["points"] - g["points"].transform("mean") print(df) # team player points games pts_vs_team_avg # 0 A P1 10 3 -2.5 # 1 A P2 15 5 2.5 # 2 B P3 8 4 -5.33 # ...
setup added so this can run · defines df, g
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) df = _AutoMock('df') g = _AutoMock('g')
filter — drop entire groups by a predicate
# Keep only teams with at least 3 players big_teams = df.groupby("team").filter(lambda g: len(g) >= 3)
setup added so this can run · defines df
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) df = _AutoMock('df')
apply — escape hatch
When neither agg, transform, nor filter fits, groupby.apply(fn) passes each group's DataFrame to fn. Slow (Python per group) but maximally flexible.
8. Merging Tables
pd.merge joins two DataFrames on a key, like SQL JOIN. Four how modes:
import pandas as pd orders = pd.DataFrame({ "order_id": [1, 2, 3, 4], "user_id": [101, 102, 101, 999], "amount": [50, 80, 30, 25], }) users = pd.DataFrame({ "user_id": [101, 102, 103], "name": ["Alice", "Bob", "Carol"], }) # INNER — rows where the key exists in BOTH (default) pd.merge(orders, users, on="user_id", how="inner") # order_id user_id amount name # 1 101 50 Alice # 2 102 80 Bob # 3 101 30 Alice # Order 4 dropped — user 999 not in users. Carol dropped — has no orders. # LEFT — keep all rows from left, fill missing right-side with NaN pd.merge(orders, users, on="user_id", how="left") # Includes order 4 with name=NaN # RIGHT — keep all rows from right pd.merge(orders, users, on="user_id", how="right") # Includes Carol with order_id=NaN # OUTER — keep everything from both sides pd.merge(orders, users, on="user_id", how="outer") # Includes order 4 (no name) AND Carol (no order)
Different key names? Use left_on and right_on:
pd.merge(orders, users, left_on="user_id", right_on="user_id")
setup added so this can run · defines orders, users, pd
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) orders = _AutoMock('orders') users = _AutoMock('users') pd = _AutoMock('pd')
Joining on the index? Use df.join(other) — convenient when both tables are indexed by the join key. Multiple keys? Pass a list: on=["user_id", "date"].
Diagnose join blow-ups. A "1-to-many" join can multiply rows; a "many-to-many" can explode them. merge(..., validate="one_to_one") (or "one_to_many", "many_to_one") asserts the relationship up front — catches the bug at the join, not three steps downstream.
9. Concatenating
When you have multiple DataFrames of the same shape (or columns), pd.concat stacks them.
import pandas as pd a = pd.DataFrame({"x": [1, 2], "y": [3, 4]}) b = pd.DataFrame({"x": [5, 6], "y": [7, 8]}) pd.concat([a, b], axis=0) # stack vertically — new rows # x y # 0 1 3 # 1 2 4 # 0 5 7 — duplicate index! Pass ignore_index=True to reset # 1 6 8 pd.concat([a, b], axis=0, ignore_index=True) pd.concat([a, b], axis=1) # stack horizontally — new columns # x y x y # 0 1 3 5 7 # 1 2 4 6 8
pd.concat([df1, df2], axis=0) is how you load N CSV files into one DataFrame:
import glob import pandas as pd dfs = [pd.read_csv(f) for f in glob.glob("data/*.csv")] combined = pd.concat(dfs, ignore_index=True)
For the "stream too big to fit in memory" case, use read_csv(..., chunksize=10_000) — yields chunks; process and discard each before reading the next.
10. Reshaping — Pivot, Melt, Stack, Unstack
Data comes in two flavours: long (one observation per row, many rows) and wide (one entity per row, observations across columns). Reshape between them constantly.
import pandas as pd # LONG format — one row per (region, month, metric) long = pd.DataFrame({ "region": ["EU", "EU", "US", "US"], "month": ["Jan", "Feb", "Jan", "Feb"], "revenue": [100, 120, 200, 250], }) # pivot — long to wide (one row per region, months as columns) wide = long.pivot(index="region", columns="month", values="revenue") # month Feb Jan # region # EU 120 100 # US 250 200 # pivot_table — pivot + aggregate (handles duplicates) long2 = pd.concat([long, long]) # duplicated rows long2.pivot_table(index="region", columns="month", values="revenue", aggfunc="sum") # Same shape as pivot, but sums duplicates # melt — wide back to long wide.reset_index().melt(id_vars="region", var_name="month", value_name="revenue") # region month revenue # EU Feb 120 # EU Jan 100 # US Feb 250 # US Jan 200 # stack / unstack — operate on the index level multi = long.set_index(["region", "month"]) multi.unstack("month") # last level of index becomes columns multi.unstack("month").stack() # round-trip
When to use which — pivot for unique index/column pairs; pivot_table when duplicates need aggregating; melt for going wide-to-long; stack/unstack when you already have a multi-index and want to move one level between rows and columns.
11. Time Series — A Brief Tour
import pandas as pd df = pd.DataFrame({ "ts": ["2025-01-01 09:00", "2025-01-01 09:30", "2025-01-02 10:00", "2025-01-02 11:00"], "value": [10, 12, 8, 14], }) df["ts"] = pd.to_datetime(df["ts"]) # parse to datetime64 df = df.set_index("ts") # time series ops want a DatetimeIndex df.resample("D").sum() # daily sum: Jan 1 -> 22, Jan 2 -> 22 df.resample("h").mean() # hourly mean (NaN for empty hours) df.resample("D").agg(["min", "max", "mean"]) # Date arithmetic with offsets df.index + pd.Timedelta(days=1) df.index + pd.offsets.BDay(1) # next business day df["value"].rolling("1h").mean() # 1-hour rolling mean — needs DatetimeIndex
resample is groupby for time. rolling is the windowed equivalent — used for moving averages, Bollinger bands, lag features. With a DatetimeIndex, both understand calendar offsets ("M" for month-end, "W-MON" for week-Monday, etc.).
12. Performance — eval, query, Categorical
Three accelerators worth knowing.
Categorical dtype for low-cardinality strings
import pandas as pd df = pd.DataFrame({"country": ["US", "UK", "US", "FR", "US"] * 1_000_000}) print(df.memory_usage(deep=True).sum()) # ~30 MB — strings everywhere df["country"] = df["country"].astype("category") print(df.memory_usage(deep=True).sum()) # ~5 MB — codes + tiny lookup table
If a column has < ~10% unique values, astype("category") typically cuts memory by 5–10× and speeds up groupby on that column.
df.eval and df.query — string expressions, evaluated in C
df = pd.DataFrame({"a": range(1_000_000), "b": range(1_000_000)}) # Vectorised Python — creates intermediate arrays df["c"] = df["a"] * 2 + df["b"] ** 2 # Same result, no intermediates — faster + lower memory on large frames df.eval("c = a * 2 + b ** 2", inplace=True) # query — predicate evaluated the same way df.query("a > 500 and b < 1000")
setup added so this can run · defines pd
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) pd = _AutoMock('pd')
eval parses the string, builds a single fused expression, and evaluates without allocating intermediate arrays. Wins on > 1M rows; below that, the parsing overhead can dominate.
Read efficiently
# Bad — reads everything as object dtype, takes 4 minutes for 10 GB df = pd.read_csv("big.csv") # Good — declare dtypes up front df = pd.read_csv( "big.csv", dtype={"user_id": "int32", "country": "category", "amount": "float32"}, parse_dates=["created_at"], usecols=["user_id", "country", "amount", "created_at"], # only what you need ) # For truly huge files — stream chunks for chunk in pd.read_csv("big.csv", chunksize=100_000): process(chunk)
setup added so this can run · defines pd, process
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) pd = _AutoMock('pd') def process(*_a, **_kw): print('-> process() called') return _AutoMock('process()')
Modern alternative — pd.read_parquet is dramatically faster and preserves dtypes. Convert once: df.to_parquet("data.parquet"). Subsequent loads are 10–100× faster than CSV.
Common Mistakes
1. Chained-indexing assignment
df[df["A"] > 1]["B"] = 999 # SettingWithCopyWarning; mutation goes nowhere df.loc[df["A"] > 1, "B"] = 999 # correct — single .loc
setup added so this can run · defines df
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) df = _AutoMock('df')
Already covered, worth repeating: one .loc, never two indexing operations.
2. iterrows instead of vectorised ops
# 100x slower than necessary for i, row in df.iterrows(): df.at[i, "total"] = row["a"] + row["b"] # Vectorised — milliseconds df["total"] = df["a"] + df["b"]
setup added so this can run · defines df
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) df = _AutoMock('df')
iterrows is the slowest legitimate API in pandas. Each iteration constructs a Series. For a million rows, that's a million transient objects. Use itertuples if you genuinely need per-row Python (10× faster than iterrows), but first try harder to vectorise.
3. apply over a column when a vectorised op exists
df["upper"] = df["name"].apply(lambda x: x.upper()) # Python per row df["upper"] = df["name"].str.upper() # vectorised
setup added so this can run · defines df
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) df = _AutoMock('df')
Every string method has a .str.* accessor that runs in C: .str.lower, .str.contains, .str.split, .str.replace, .str.strip. Dates have .dt.* (.dt.year, .dt.month, .dt.day_name()). Reach for the accessor before reaching for .apply.
4. Reading huge CSVs without dtype or chunksize
A 5 GB CSV with pd.read_csv(path) and no hints will: scan twice (once to infer dtypes, once to read), default every string column to object, eat 15–20 GB of RAM, and take 10+ minutes. Always declare dtype=, parse_dates=, and usecols=. For workloads that touch the file repeatedly, convert to Parquet once.
5. Forgetting that groupby preserves the index
g = df.groupby("team")["points"].mean() g.loc["A"] # works — team is the index now # To get a flat DataFrame back g = df.groupby("team")["points"].mean().reset_index()
setup added so this can run · defines df
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) df = _AutoMock('df')
After groupby(...).agg(...), the group keys become the index. If you want a flat table for downstream code that expects integer indexing, .reset_index() afterwards.
6. Ignoring SettingWithCopyWarning
If pandas prints that warning, it has lost track of whether your assignment hit the original frame or a copy. Don't suppress it — fix it. Almost always means a chained-indexing assignment or a missing .copy() on a slice you intend to mutate.
🎯 Your Turn — Monthly Revenue by Region
Given a sales DataFrame with columns date, product, region, revenue, compute monthly revenue per region, returned as a DataFrame where:
- Rows are months (a
Periodor month-end date), - Columns are regions,
- Cells are total revenue for that month/region combination.
Expected shape — if there are 12 months and 3 regions, the result is (12, 3).
import pandas as pd sample = pd.DataFrame({ "date": ["2025-01-15", "2025-01-20", "2025-02-05", "2025-02-12", "2025-03-01", "2025-03-08"], "product": ["A", "B", "A", "C", "B", "A"], "region": ["EU", "US", "EU", "US", "EU", "US"], "revenue": [100, 200, 150, 250, 120, 300], }) monthly_by_region(sample) # region EU US # month # 2025-01 100 200 # 2025-02 150 250 # 2025-03 120 300
setup added so this can run · defines monthly_by_region
# Lightweight mock for objects whose attributes/methods aren't critical class _AutoMock: def __init__(self, name='mock'): self._name = name def __getattr__(self, k): return _AutoMock(self._name + '.' + k) def __call__(self, *a, **kw): print('-> ' + self._name + '() called') return _AutoMock(self._name + '()') def __repr__(self): return '<mock ' + self._name + '>' def __str__(self): return '<mock ' + self._name + '>' def __bool__(self): return True def __iter__(self): return iter([]) def __len__(self): return 0 def __getitem__(self, k): return _AutoMock(self._name + '[...]') def __setitem__(self, k, v): pass def __enter__(self): return self def __exit__(self, *a): return False async def __aenter__(self): return self async def __aexit__(self, *a): return False def __add__(self, o): return self def __radd__(self, o): return self def __sub__(self, o): return self def __mul__(self, o): return self def __rmul__(self, o): return self def __truediv__(self, o): return self def __eq__(self, o): return isinstance(o, _AutoMock) def __hash__(self): return hash(self._name) def __lt__(self, o): return True def __le__(self, o): return True def __gt__(self, o): return False def __ge__(self, o): return False def __mro_entries__(self, bases): return (object,) def monthly_by_region(*_a, **_kw): print('-> monthly_by_region() called') return _AutoMock('monthly_by_region()')
Constraints:
- Parse
dateto datetime withpd.to_datetime. - Extract the month as a
Period(.dt.to_period("M")) — cleaner than a date string. - Use
groupby+sum, thenunstackto pivot region into columns. - Fill any missing region/month combinations with 0 (some months may have no sales in a region).
Skeleton:
import pandas as pd def monthly_by_region(df): # TODO 1: convert df["date"] to datetime # TODO 2: create a "month" column via .dt.to_period("M") # TODO 3: groupby ["month", "region"], sum revenue # TODO 4: unstack the "region" level so it becomes columns # TODO 5: fill NaN with 0 (months with no sales in some region) ...
Hint 1 — Extracting the month as a Period
Afterdf["date"] = pd.to_datetime(df["date"]), you can do df["month"] = df["date"].dt.to_period("M"). A Period like 2025-01 is more compact than a date string and sorts correctly. Alternatively, df["date"].dt.strftime("%Y-%m") gives a string — sortable as text, but loses date semantics.
Hint 2 — Group then unstack
df.groupby(["month", "region"])["revenue"].sum() returns a Series with a MultiIndex of (month, region). Calling .unstack("region") moves the region level from the index to the columns — exactly the wide shape you want. Chain .fillna(0) at the end so missing combinations show as 0 rather than NaN.
Show full solution
import pandas as pd def monthly_by_region(df): """Monthly revenue per region, regions as columns, months as rows.""" df = df.copy() # don't mutate caller's frame df["date"] = pd.to_datetime(df["date"]) df["month"] = df["date"].dt.to_period("M") return ( df.groupby(["month", "region"])["revenue"] .sum() .unstack("region") .fillna(0) ) sample = pd.DataFrame({ "date": ["2025-01-15", "2025-01-20", "2025-02-05", "2025-02-12", "2025-03-01", "2025-03-08"], "product": ["A", "B", "A", "C", "B", "A"], "region": ["EU", "US", "EU", "US", "EU", "US"], "revenue": [100, 200, 150, 250, 120, 300], }) print(monthly_by_region(sample)) # region EU US # month # 2025-01 100.0 200.0 # 2025-02 150.0 250.0 # 2025-03 120.0 300.0 # Stress test — month/region combos with gaps gappy = pd.DataFrame({ "date": ["2025-01-15", "2025-02-20", "2025-03-01"], "region": ["EU", "US", "EU"], "revenue": [100, 200, 300], }) print(monthly_by_region(gappy)) # region EU US # month # 2025-01 100.0 0.0 # 2025-02 0.0 200.0 # 2025-03 300.0 0.0
Why .copy() at the top — without it, df["month"] = ... mutates the caller's DataFrame. A function that silently adds a column to its input is a function that causes 2am incidents. Defensive copy is a one-line price for predictability.
Why Period over strftime — Period is a real time type. You can do arithmetic on it (period + 1 = next month), sort it numerically, and resample to it. A string is just text — sorts alphabetically (mostly correct, but "2025-12" < "2025-2" if you forget zero-padding) and can't be incremented.
Generalising the pattern — groupby([time_bucket, category]).agg(...).unstack(category) is the universal recipe for "show me X over time, broken down by Y". Swap sum for mean, count, nunique as the question demands. Swap unstack for pivot_table if you have duplicates within a (time, category) cell that need aggregating. The skeleton is the same: bucket the time, group on the categorical, aggregate the metric, unstack to pivot.
This same idea, scaled up — partition by date, group by category, aggregate, pivot — is what every BI tool, every data warehouse, and every dashboard layer does under the hood. Pandas just gives you the primitives in three lines.
What You Learned
- A DataFrame is an ordered dict of Series sharing one Index — every operation is per-column vectorised under the hood.
- The Index is real —
set_index,reset_index, and multi-indexes (pd.MultiIndex.from_tuples) make joins and pivots trivial. .loc(labels, inclusive),.iloc(positions, half-open),.at/.iat(scalar, fastest),.query(string predicate). Mixing them is a top source of bugs.- The chained-indexing trap —
df[mask][col] = vsilently fails. Always one.loc[mask, col] = v. - Missing data via
.isna,.dropna,.fillna,.ffill/.bfill. Be principled about the strategy. - Pick the right apply:
Series.map(element),DataFrame.apply(axis=1)(row),groupby.transform(per-group broadcast),groupby.agg(per-group reduce). groupbyis split-apply-combine. Multi-column aggs via the kwarg form:g.agg(total=("col", "sum"), ...).- Four merge modes — inner, left, right, outer. Use
validate=to catch row-count blow-ups. - Reshape:
pivot(unique pairs),pivot_table(with aggregation),melt(wide → long),stack/unstack(move multi-index levels). - Time series:
pd.to_datetime→DatetimeIndex→resampleandrollingunderstand calendar offsets. - Performance:
categoricaldtype for low-cardinality strings;eval/queryfor big-frame expressions; declaredtype=onread_csv; prefer Parquet for repeated reads. - Never
iterrowswhen you can vectorise. Never.applywhen there's a.str.*or.dt.*accessor.
Next: Visualisation with Matplotlib (and a Touch of Seaborn) — turning the DataFrame you just shaped into a chart someone can read.
Practice this
on practicepython.inShort exercises that run in your browser and tell you what your code actually did, not just whether a test passed.