Pandas — DataFrames you'll actually use

If you understand NumPy from yesterday, you almost understand pandas. A DataFrame is, mechanically, a dict mapping column-name to typed array, plus a row index.

That single sentence explains most of pandas' performance characteristics.

Reading a column — df['col'] — is O(1). It's a pointer to the underlying NumPy array. No copying.

Vectorised operations on columns — df['a'] + df['b'] — run in C, not Python. NumPy under the hood. Fast.

Reading a row by position — df.iloc[i] — has to construct a Series across all columns. Slower than reading a column. Still constant time per row, but multi-column access pays a per-row constructor cost.

Iterating row by row with df.iterrows() — disastrous. For each row, pandas constructs a new Series. The Python overhead per row dominates. On a million-row DataFrame, iterrows can take minutes for what vectorised code does in milliseconds.

The rule that drops out — operate on whole columns, not on individual rows. df['a'] + df['b'] (vectorised) instead of [a + b for a, b in zip(df['a'], df['b'])] (Python loop) instead of df.iterrows() with row.a + row.b (slowest).

Other consequences of the dict-of-arrays model:

Column types are uniform. Each column is one NumPy dtype. Mixed types in a column become 'object' dtype, which loses NumPy's speed advantages.

The row index is a separate first-class structure. It's not just a counter — it can be a date, a string, a multi-level tuple. df.loc[index_value] uses the index for lookup; df.iloc[position] uses the integer position.

Missing values exist via NaN (for floats) or pd.NA (newer, type-aware). Either way, special handling — most operations propagate missing values; some functions skip them.

Know the model. The performance and behaviour follow.

Pandas has hundreds of methods. Most of your real work uses five. Learn these five at depth — their kwargs, their edge cases, their performance characteristics — and you can wrangle most CSV-shaped data without reaching for SQL.

One — read_csv / to_parquet. The I/O entrypoints. read_csv has 50+ kwargs; the ones you actually use are sep, header, names, dtype, parse_dates, na_values, usecols, nrows. For large datasets, parquet is the better default — typed, compressed, column-oriented, fast to read partial files.

Two — query. df.query('col > 5 and tag == a'). String-based filter syntax that reads naturally and is often faster than chained boolean indexing. Especially shines on multi-condition filters where the bracket syntax becomes unreadable.

Three — groupby + agg. The split-apply-combine engine. df.groupby('city').agg({'sales': 'sum', 'orders': 'count'}). Modern syntax with named aggregations is even cleaner. Tomorrow we go deep on this.

Four — merge. SQL-style joins. df1.merge(df2, on='id', how='left'). Four 'how' modes (left, right, inner, outer). Always specify how= explicitly — defaults can silently drop rows.

Five — assign. Add or replace a column in a chain-friendly way. df.assign(total=lambda d: d.qty * d.price). Returns a new DataFrame, doesn't mutate, plays nicely with method chaining.

These five plus method chaining replace 80% of what you'd write in SQL or in a hand-rolled Python loop. Add a few utility methods (sort_values, reset_index, dropna, fillna) and you have most of the toolkit.

Get fluent in these five. The rest of pandas is reachable from any of them.

Method chaining is the secret to readable pandas. Each step is a clear transformation; the chain reads top-to-bottom like a recipe.

Look at the snippet. Eight lines wrapped in parentheses (so we can break across lines naturally). Each line is one operation; the data flows from top to bottom.

Line 1 — read_csv. Standard.

Line 2 — query for paid orders. Filters in one line.

Line 3 — assign a computed column. total = qty * price. The lambda receives the DataFrame at this point in the chain (post-filter), so we work with the filtered version.

Line 4 — groupby customer_id.

Line 5 — agg with named aggregations. orders is the count of id; revenue is the sum of total. Modern named-agg syntax — much cleaner than the older dict-of-dicts.

Line 6 — reset_index moves customer_id from the index back to a column. Optional, but makes downstream code easier when you treat the result as a flat DataFrame.

Line 7 — sort by revenue descending.

No intermediate variables to name and mistype. No re-binding of df. Each step's output flows into the next step's input.

The payoff — readability. A new colleague reads this and immediately understands the pipeline. There's no hidden state, no out-of-order operations, no possibility of accidentally using stale data from an earlier step.

The trade-off — debugging. If a step misbehaves, you can't easily inspect the DataFrame after that step without breaking the chain. The fix is to break the chain temporarily during debugging, store an intermediate variable, inspect, then re-chain.

For production data pipelines, method chaining is the cleanest pattern. For exploratory analysis, breaking and re-chaining is fine. Use both — the chain for the final code, breaks for the debugging path.

df.iterrows() is the pandas equivalent of looping in pure Python. It's slow, often catastrophically slow, and there's almost always a vectorised alternative.

Why iterrows is slow — for each row, pandas constructs a new Series object. The construction has overhead — copying values out of the underlying NumPy arrays into a new Series, registering an index, supporting the full Series API. Multiply by N rows and you're in trouble.

Real numbers — on a 100k-row DataFrame, summing two columns with iterrows takes about 5 seconds. Vectorised (df['a'] + df['b']) takes about 5 milliseconds. 1000x.

Replacements, in order of preference:

Vectorised arithmetic — df['x'] + df['y'], df['x'] * 2, df['x'] ** 2. Anywhere the operation is per-element, NumPy applies it column-wise.

Vectorised conditional — np.where(cond, a, b). Replaces 'if-else inside iterrows'.

Vectorised functions — df['x'].apply(fn). Slower than pure vectorised, but faster than iterrows because it doesn't construct a full Series per call. Used when fn can't be expressed as a vector op.

Full vectorisation with numpy — drop into NumPy: df['x'].values, do the op, write back. Sometimes faster than pandas' wrappers when you're hot.

For genuine perf — switch to polars. Lazy evaluation, query optimisation, multi-core by default. We cover it tomorrow.

iterrows has approximately one legitimate use — debugging on a small DataFrame, where the slowness doesn't matter and you want to inspect rows manually. Anywhere else, it's a code smell.

If you typed iterrows, you almost certainly have a faster vectorised alternative. Stop. Refactor. Ship the faster version.

End of Day 31. Three days into the data stack.

What we covered.

Morning, the foundational mental model — DataFrame is a dict of NumPy arrays plus an index. This single fact explains the performance profile (column access fast, row access slower, iterrows dreadful) and the API design (column-oriented operations everywhere).

Midday, the five pandas methods that cover 80% of real work. read_csv / to_parquet, query, groupby+agg, merge, assign. Get fluent in these and most data wrangling is solved.

Afternoon, an 8-line method-chained pipeline from CSV to per-customer summary. The chain reads top-to-bottom; no intermediate variables; the data flows. Cleaner than the equivalent SQL in many cases.

Evening, iterrows as the canonical pandas perf bug. 1000x slower than vectorised alternatives. Use vectorised arithmetic, np.where for conditionals, df.apply when truly needed, polars when scale demands. iterrows is a code smell.

A broader theme — pandas inherits NumPy's vectorisation philosophy and applies it to labelled, mixed-type, possibly-missing data. The patterns from yesterday (vectorise everything) carry over with extra dimensions for column names and indices.

Tomorrow, Day 32, groupby and merge in depth. Split-apply-combine in detail. The gotchas in pandas joins (default 'how' silently drops rows). When polars beats pandas (and when it doesn't).

See you in the morning.