Chapter 2 — DataFrames

Learning Statistics with Python

Prof. Xuhu Wan

Chapter 2 · Learning Statistics with Python

DataFrames

DataFrame methods end to end: axes and alignment, agg and apply, missing and duplicated values, filtering, groupby, pivot, melt, stack, merging and concatenation, and the modern method-chaining style.

Prof. Xuhu Wan

ISOM, HKUST Business School · 2026 Edition

What This Chapter Builds

• A Series had one axis; a DataFrame has two. Every method now needs an axis decision, and every arithmetic operation aligns on labels before it computes.
• The cleaning toolkit: audit and fill missing cells in the real grid-load file (one column a fifth empty, another 99.99 %), find the duplicated hour that daylight-saving time creates, filter with masks and query, slice with loc / iloc.
• The reshaping toolkit: groupby (agg, transform, filter), pd.merge (inner / left / outer), pivot_table, melt, stack/unstack, pd.concat — with the row count predicted before every step.
• The modern style: one readable method chain instead of a pile of temporary variables — and the copy-versus-view bug it protects you from.

Why this matters

Every quant workflow is reshape → test → decide. This chapter is the reshape step; Chapter 3 is the test. Reshaping mistakes (a duplicated timestamp, a silent NaN alignment, a merge that dropped half the keys, a file that stopped loading at row 65 536) are invisible in the final number; you only catch them if you predicted what the intermediate shape should be. That is the habit this chapter trains.

Roadmap

Section	Concept	Tool
2.1	Axes, sorting, index alignment, aggregation	axes, sort_values, sort_index, + alignment, agg, apply
2.2	Missing and duplicated values, filtering, subsets	isna, fillna, ffill, dropna(subset=), drop_duplicates, masks, query, isin, loc/iloc
2.3	Groupby, merge, pivoting, melting, stacking, concatenation	groupby().agg/transform/filter, pd.merge, pivot_table, pd.cut, melt, stack, pd.concat
2.4	Method chaining and pandas idioms	query → assign → groupby → agg, pipe, one .loc[rows, cols] =, vectorise

DataFrame Axes, Sorting, Index Alignment, agg and apply

A DataFrame is a dict of Series sharing one index — two axes, labelled both ways. Every method you learnt in Chapter 1 now needs one more decision, which axis?, and every arithmetic operation silently aligns on labels before it computes. In this section you predict the shape of each result before pandas shows it.

Does read_csv know that dates are dates?

PJM is the grid operator for the eastern United States; each column is a utility’s hourly load in megawatts. We load five utilities with parse_dates=True and index_col="Datetime".

After loading, what does raw.loc[“2017-07-04 15:00”, “AEP”] return?

• An error — the index holds strings, not dates
• The AEP load (one number) at 15:00 on 4 July 2017
• Every row of July 2017
• A boolean mask

Which axis is which?

df.axes is a list of two Index objects. Commit before you run.

What does df.axes[1] return?

• The DatetimeIndex of 8 760 timestamps
• The column Index(['AEP', 'COMED', 'DAYTON', 'DEOK', 'DOM'])
• The second row of the frame
• An integer, 5

Sum down or sum across?

df.sum(axis=0) and df.sum(axis=1) are both legal. Predict the length of each.

How many numbers does df.sum(axis=1) return?

• 5 — one per column
• 8 760 — one per row (the total load in that hour)
• 1 — the grand total
• It raises: you cannot sum a DatetimeIndex

Read axis=1 as “collapse the columns”. The row-wise sum is the combined load of the five utilities in that hour. The default is always column-wise; getting this orientation right is most of what makes DataFrame code correct.

Your turn: interquartile range of each row

Row-wise statistics use the same axis=1 switch. quantile accepts it too.

Compute the interquartile range of each row (across the five utilities) into iqr_row. Hint: df.quantile(q, axis=1) gives one quantile per row.

Sorting rows by a column — what happens to the index?

sort_values(by="AEP", ascending=False) puts the peak-load hours first.

After df.sort_values(by=“AEP”, ascending=False), what is the DatetimeIndex?

• Unchanged — still 1 Jan 00:00 … 31 Dec 23:00
• Reset to 0, 1, 2, …
• Reordered with the rows — the first label is the peak-load hour
• Dropped; sorting requires a RangeIndex

Your turn: sort the columns by the first row

sort_values also takes axis=1: then by= names a row label and the columns are reordered.

Reorder the columns of df so that the values in the first row are ascending, storing the result in by_first. Hint: df.sort_values(by=df.index[0], axis=1).

Making changes: add, derive, drop

New columns are assignments. drop returns a new frame — predict what happens to the original.

After df2 = df.drop([“Month”, “Season”], axis=1), does df still contain Month?

• Yes — drop returns a copy; df is unchanged
• No — drop always edits in place
• Only the Season column survives
• It raises unless inplace=True

rename, assign, replace

Three ways to change content without touching the row axis.

assign is chainable and never edits in place; df["new"] = … is faster (roughly 15× on the full file) but mutates. replace takes a scalar, a list, or a dict of old → new.

Index alignment: predict the shape of df1 + df2

df1 is rows 0–2 × columns 0–2. df2 is rows 2–3 × columns 0–3. Their labels overlap in exactly one row.

What is the shape of df1 + df2, and how many cells are non-NaN?

• (3, 3), all filled
• (2, 3), all filled
• (4, 4), only 3 cells filled
• It raises — shapes differ

Duplicated index labels

Stacking df1 on df2 with pd.concat keeps every row — including the one whose timestamp both frames share.

df1 covers 00:00–02:00 and df2 covers 02:00–03:00. What does this print?

print(pd.concat([df1, df2]).index.duplicated().sum())

1

.index.duplicated() is a boolean mask; keep="last" marks every copy except the last as a duplicate. In §2.2 you will meet a real duplicated timestamp that no concat created.

agg: several statistics in one call

agg takes a list of function names, or a dict mapping columns to their own lists. Predict the shape of the dict version.

What does num.agg({“AEP”: [“sum”,“count”], “DEOK”: [“mean”,“count”]}) return?

• A Series of 4 numbers
• A 3 × 2 frame with two NaN cells
• A 2 × 2 frame, fully filled
• An error — the lists have different functions

apply: your own function, row by row or column by column

The notebook’s example: build \(Y = 10 + 0.8X + \text{noise}\) with a function that receives one row at a time. Predict the column means.

X is np.arange(100). What is the first number of newdf.apply(np.mean).round(2)?

newdf.apply(np.mean)["X"]

49.5

apply(..., axis=1) is how you build a feature from several other features. It is a Python loop under the hood — fine for thousands of rows, slow for millions, where the vectorised 10 + 0.8*newdf.X + newdf.Noise wins by 10–100×. §2.4 returns to this.

Your turn: row range two ways

Compute the range (max − min) of each row of num twice: rng_agg via .agg(["max","min"], axis=1) and a subtraction, and rng_apply via .apply with a lambda. The two must agree.

What you discovered

• read_csv(parse_dates=True, index_col=…) arrives date-typed: a string like "2017-07-04 15:00" or "2017" indexes straight in.

• axis=0 collapses rows (one result per column, the default); axis=1 collapses columns (one result per row). sum, quantile, agg, apply, drop, sort_index all obey it.

• sort_values carries the index with the rows; sort_index() restores time order; sort_values(by=<row>, axis=1) reorders columns.

• drop, rename, assign, replace return new frames — nothing changes unless you re-assign or say inplace=True.

• Arithmetic aligns on the union of labels: df1 + df2 was 4 × 4 with only 3 real cells. pd.concat keeps duplicated labels; ~index.duplicated(keep="last") removes them.

Next: §2.2 — the real file has holes and a duplicated hour. Audit first, then decide.

Missing and Duplicated Values, Filtering, Subset Selection

Real data arrive with holes and repeats. You will audit the notebook’s original grid file (one column is a fifth empty, another 99.99 %), decide between filling and dropping — surgically — discover why one November hour appears twice every year, and then carve out exactly the rows and columns a model needs, predicting each row count before you run.

Audit first: count NaN per column

AEP_hourly.csv is the notebook’s energy(Clean=False): 178 258 hours, 12 utilities, lots of NaN. We keep four utilities and 2011–2012.

What does load.isna().sum().sum() / load.size measure?

• The number of rows with at least one NaN
• The fraction of all cells that are NaN
• The fraction of columns that contain a NaN
• The mean of the non-missing values

Four different diseases in one frame: AEP and COMED each missing one hour, FE missing 3 624 (the first five months of 2011), NI missing all but one of 17 540. df.isna().sum() is the first line you run on any new dataset.

Fill forward, fill backward, fill a constant

Zoom in on AEP’s single hole (04:00 on 6 December 2012). Four options — one of them is a crime in time series. Decide which before running.

Which fill is look-ahead bias when the rows are ordered in time?

• fillna(0)
• ffill() — carry the last value forward
• bfill() — pull the next value backward
• fillna(median)

Drop surgically, or fill and pay the price

dropna() removes any row with any NaN. With NI 99.99 % empty, predict how many rows survive.

How many rows does load.dropna() keep?

• 0
• 13 916
• 17 539
• All 17 540 — NaN are ignored

Rule of thumb from the lecture

When not too many values are missing: fillna(median) for numerical columns, fillna(mode) for categorical ones. Filling five months of FE with one number keeps the rows but shrinks the spread — a fifth of the column now sits exactly at the centre. When most of a column is missing (NI), drop the column: a filled column is a fiction.

Your turn: a surgical clean-up

Build clean from load in two moves: drop the hopeless NI column (drop(columns=…)), then drop the rows that still have a NaN. Result: no NaN, three columns, more than 13 000 rows.

Your turn: fill a column with its mean

The notebook asks you to fill COMED with its mean; here COMED has one hole and FE has 3 624, so fill FE.

Fill the NaN in load["FE"] with the column mean, into fe_mean. It must contain no NaN and its mean must equal the pre-fill mean (why is that automatic?).

A duplicated hour that nobody typed

Switch to the smaller pjm_hourly.csv (the notebook’s cleaned new.csv, 2014–2017). It has no NaN — but some timestamps appear twice. Predict how many, then look at which hours they are.

One duplicated timestamp per year, four years. What prints?

print(hours.index.duplicated().sum())

4

Every one is 02:00 on the first Sunday of November — the hour that repeats when US clocks fall back from daylight-saving time. Two different loads, one label. Nothing is “wrong” with the file; the calendar did it.

keep=“first”, keep=“last”, keep=False

drop_duplicates(subset=…) decides which copy survives. Predict the row counts.

hours has 4 duplicated timestamps. How many rows does keep=False remove?

• 0 — nothing is exactly identical
• 4 — one per duplicated label
• 8 — both copies of every pair
• 2

Your turn: drop both copies

Using the boolean mask form hours[~hours.index.duplicated(keep=…)], remove both copies of every duplicated hour, into nodup. Check: 8 rows fewer than hours.

Mask one column, slice the whole table

A mask built from one column selects rows of the entire frame, all columns intact. Two conditions need & / | and parentheses.

What happens with hours[hours.AEP > 15000 & hours.AEP < 16000]?

• Works — returns the band
• Raises — & binds before >; you need parentheses
• Returns an empty frame silently
• Returns every row

Same filter, more readable: query

.query() writes the filter as a string: and / or instead of & / |, and @name splices in a Python variable. Predict whether it matches the mask.

hours.query(“AEP > 15000 and AEP < 16000”) returns…

• A SyntaxError — you cannot use and in pandas
• Every row
• The same rows as the boolean mask band
• Only the first matching row

isin: membership filters

isin tests each value against a list. Combine with day_name() to split weekends from weekdays.

~ negates a boolean mask. The weekday/weekend gap (about 1 400 MW, 15 240 vs 13 795) is the industrial load that switches off on Saturday — a fact you will exploit with groupby in §2.3.

What type is a single column?

A DataFrame is a dict of Series sharing one index. Pull one column out and ask what it is.

What does hours[“AEP”] return?

• A new one-column DataFrame
• A Series
• A NumPy array
• A Python list

loc is right-inclusive, iloc is right-exclusive

.loc label slices include the end label; .iloc position slices stop before the end position. What two numbers print?

print(len(hours.loc["2017-01-01 22:00":"2017-01-02 00:00"]), len(hours.iloc[1:4]))

3 3

Filtering with loc: rows by condition, columns by name

loc[mask, columns] does both selections in one call.

Select the last two columns of hours, keeping only the rows where the last column (DEOK) is above its own mean, into sel.

Your turn: a time-ordered 80/20 split

For time series you never shuffle: the first 80 % of hours train, the last 20 % test.

Split hours by position: train = first 80 % of rows, test = the remaining rows, in time order.

What you discovered

• isna().sum() per column is the first line on any dataset: in 2011–2012 the real file had 1 / 1 / 3 624 / 17 539 holes in four columns.

• ffill looks back, bfill looks forward — a leak in any time-ordered frame. fillna(median) keeps rows but shrinks the spread; dropna() kept no rows because of NI — use subset=, thresh=, or drop the column.

• The four duplicated timestamps are all 02:00 on the first Sunday of November: daylight-saving fall-back. keep="first"/"last" drops one copy, keep=False drops both.

• A mask from one column slices the whole frame; parentheses around every comparison; query("… and …") reads naturally and @var injects variables; isin handles membership.

• [] gives a Series, [[]] a DataFrame; loc slices are right-inclusive, iloc right-exclusive; loc[mask, cols] filters and selects at once.

Next: §2.3 — split the frame into groups, join two frames on a key, reshape long ↔︎ wide, and glue frames together.

Groupby, Pivoting, Melting, Stacking, Concatenation

Split → apply → combine. Every summary table you have ever seen is a groupby or a pivot_table; every “tidy” dataset is a melt away from the wide one your plotting code wants; and almost every real analysis ends with a merge, where how= decides what survives. You will predict the shape of each reshape, then confirm it on grid load, university rankings, a retail inventory and a five-stock panel.

groupby: one number per group

Group two years of hourly load by weekday. Predict how many rows the result has.

How many rows does pjm.groupby(“WeekDay”)[“AEP”].mean() return?

• 7 — one per weekday
• 17 544 — one per hour
• 24 — one per hour of the day
• 2 — one per year

Several aggregations at once, your own functions, named columns

pjm.groupby(“WeekDay”)[“AEP”].agg([“mean”, “std”, iq]) returns a…

• Series with 3 entries
• DataFrame: 7 rows (groups) × 3 columns (aggregations)
• DataFrame: 3 rows × 7 columns
• Single number

transform: same shape as the input

Standardise AEP within each weekday. Predict the length of the result.

What is the length of pjm.groupby(“WeekDay”)[“AEP”].transform(lambda x: (x - x.mean()) / x.std())?

• 7
• 17 544 — the same as pjm
• 1
• 24

The last hour of 2017 is a Sunday: against all hours it is 1.7 sd above the mean, against Sundays 2.4 sd — a bigger surprise once you condition on the day. Group-wise standardisation is how you build seasonally-adjusted features.

filter: keep whole groups that pass a test

Group means of B: foo → 3, bar → 4. Which rows survive filter(lambda g: g["B"].mean() > 3)?

small.groupby("A").filter(lambda g: g["B"].mean() > 3).index.tolist()

[1, 3, 5]

Three shapes, one verb: agg → one row per group; transform → one row per input row; filter → the input rows of the groups that pass.

Your turn: interquartile range by weekday

Use groupby("WeekDay")["AEP"].apply(...) with a lambda to compute the interquartile range (75 % − 25 % quantile) of AEP for each weekday, into iq_day.

A second frame: Times university rankings 2011–2016

Text columns hide numbers ('-' for missing income). pd.to_numeric(errors="coerce") turns the junk into NaN.

agg(["mean", "size"]) gives you the count next to the mean so you can filter out countries with two universities before ranking them.

Joins: inner, left, outer — who survives?

Two tables → one. The 2015 list has 401 universities, the 2016 list 800. pd.merge(u15, u16, on="university_name", how=…). Predict the row counts.

Row counts for how=“left” and how=“outer”?

• left → 800, outer → 800
• left → fewer than 401, outer → 401
• left → 401, outer → more than 800
• left → 401, outer → 401

Your turn: count the universities that dropped out

How many 2015 universities have no 2016 row? Use the left merge: count the NaN in its research_16 column, into dropped (an integer). Check it against len(u15) - len(inner).

pivot_table: two grouping keys become rows × columns

What does pivot_table(index=“country”, columns=“year”, values=“research”) put in each cell?

• The mean research score of that country in that year
• The sum
• The number of universities
• It raises — several universities share each (country, year)

The same table by groupby + unstack

A two-key groupby returns a MultiIndex Series. unstack(level) moves one index level into the columns — the default is the last level.

pivot_table = groupby + unstack. Use unstack when you already have a grouped result; stack is its inverse (columns → an inner index level).

The notebook’s inventory data: price by release year and flag

inventory.csv (Kaggle retail inventory, 20 000 SKUs). Predict the shape of pivot_table(index="ReleaseYear", columns="New_Release_Flag", values="PriceReg") for releases from 2010 on.

Release years 2010–2016 (7 values) × flag 0/1. Shape?

pt_inv.shape

(7, 2)

stack, unstack, transpose

groupby on two keys → a MultiIndex Series; unstack names which level goes to the columns; stack puts columns back into the index; .T swaps axes.

From the lecture

unstack() pivots an index level (by default the last one) into columns. stack() is the exact inverse. Neither aggregates — they only move labels between the two axes.

When apply is not needed: pd.cut

To bin prices into tiers people reach for apply(bucket). pd.cut does it vectorised — and its bins are right-closed. Predict the labels.

pd.cut([100, 200, 300, 400, 50, 175], bins=[0, 150, 300, 1000], labels=["low", "mid", "high"]) — note 300 sits on the 150–300 edge.

tier.tolist()

['low', 'mid', 'mid', 'high', 'low', 'mid']

Order of preference: vectorised arithmetic → np.where / pd.cut → apply → a for loop. apply walks the rows in Python; reserve it for logic with no built-in.

melt and pivot: long ↔︎ wide

stockdata2.csv is long (one row per date × stock). Wide is what plotting and correlation want. Predict the row count after melting back.

The wide frame has 2 305 dates × 5 stocks. How many rows does melt produce?

wide.reset_index().melt(id_vars="Date", var_name="stock", value_name="value").shape[0]

11525

melt(id_vars=…) keeps the identifiers and turns every other column into (variable, value) pairs — the notebook’s var_name="Course", value_name="Score" example is the same call.

pd.concat: side by side, or on top

Concatenating two Close series with different date ranges. Predict what fills the gap.

msft covers 2015–2018, fb only 2016. What does pd.concat([msft, fb], axis=1) contain for 2015 rows?

• Only MSFT rows — 2015 is dropped from the result
• An error: lengths differ
• MSFT values with NaN in the FB column
• FB values repeated from 2016

Your turn: assemble a pair and correlate it

pair holds AAPL and the S&P 500 (GSPC) prices side by side. Set rho to the Pearson correlation of their daily percentage changes (a plain float).

Plotting a frame

The notebook draws these with Plotly; in the browser we use pandas’ matplotlib backend.

In Colab

import plotly.express as px
fig = px.line(wide / wide.iloc[0], title="growth of $1")          # interactive, hover shows values
fig.show()
fig = px.scatter(top100, x="world_rank", y="citations", color="country")
fig.show()

Your turn: citations vs rank, top 100 of 2016

Keep the 2016 universities with world_rank ≤ 100 in top100 (exactly 100 rows), then plot citations against rank.

What you discovered

• groupby(...).agg → one row per group (named form agg(col=("src", "fn"))); .transform → one row per input row; .filter → the rows of groups that pass.

• pd.merge(how=): inner = matched only, left = keep the spine and NaN-fill, outer = the union. Count rows after every merge.

• pivot_table(index, columns, values) aggregates (mean by default) and equals groupby([a, b]).mean().unstack(b); pd.crosstab is the same thing spelled differently.

• pivot (no aggregation) needs unique pairs; melt is its inverse — 2 305 × 5 wide became 11 525 long. stack/unstack move labels between the axes; .T swaps them.

• pd.concat(axis=1) outer-joins on the index; axis=0 keeps duplicated labels. pd.cut bins vectorised, right-closed.

Next: §2.4 — everything above, written as one readable chain.

Method Chaining and Pandas Idioms

Most pandas methods return a new DataFrame. That one fact lets you chain them with . — a pipeline, like a Unix pipe. You will check that a chain and a pile of temporary variables give the same answer on the five-stock panel, provoke the most common pandas bug, and replace a slow loop.

Spaghetti vs chain — same result?

Drop the index (GSPC), flag up-days, then per stock count days, average the daily change and count up-days. Once with temporary variables, once as a single chain.

The temporary-variable version and the chained version produce…

• Identical output — chaining is the same steps without temp names
• Different output — chaining drops a step
• An error — you cannot chain groupby after assign
• The same rows but different column order

assign does not mutate

assign adds columns and returns a new frame. Predict: after new = msft.assign(ret=…), does the original msft gain a ret column?

After new = msft.assign(ret=lambda d: d.Close.pct_change()), the original msft…

• Now has a ret column too (assign mutates in place)
• Is unchanged — assign returns a new frame
• Is deleted
• Raises a SettingWithCopyWarning

Worked example: daily prices → a monthly summary in one chain

Label each AAPL row by month, group, summarise (last price, trading days, volatility of the daily change), flag the volatile months. Predict how many months January 2007 – February 2016 contains.

Nine full years plus January and February 2016. How many rows does monthly have?

len(monthly)

110

Five verbs, one chain — filter, label, group, summarise, flag. Wrap it in a function and you have a reusable pipeline; pipe(f, …) lets you drop that function into the middle of another chain.

Why the chain wins

• No temporary variables to misname or leave stale.
• Linear, top-to-bottom — each line is one transformation.
• Easy to comment out a step while exploring.
• Composable — wrap it in a function; insert it anywhere with .pipe.

Tip

The opening ( lets each .method(...) sit on its own line, PEP 8-style. Always wrap a multi-line chain in (...). The chain-friendly verbs: query, assign, pipe, loc.

The SettingWithCopy bug

You want a regime label wherever Close > 80. Two ways to write it — predict which one lands the write on the original frame.

Which write actually changes px?

• sub = px[px.Close > 80]; sub["regime"] = "high"
• px.loc[px.Close > 80, "regime"] = "high"
• Both
• Neither — DataFrames are immutable

Write it with one .loc[rows, cols] =. The wrong way silently evaporates and you only get a warning — the most common pandas bug there is.

Fix the slow loop

iterrows walks the frame one Python row at a time. On 11 525 rows it is already sluggish; on a million it is minutes. Replace it with one vectorised line.

The loop was cut to the first 100 rows because it is slow. Replace it with the vectorised product long["value"] * long["change"] / 100 over all rows, into dollar_move (the daily dollar move per share).

Both produce the same column; the vectorised line runs in C, about 100× faster and half the code. iterrows is almost always a code smell.

What you discovered

• A method chain (df.query().assign().groupby().agg()) gives the same result as temporary variables — with nothing to misname.

• assign is non-mutating and later columns can reference earlier ones — that is why chains are safe.

• query / assign / pipe / loc are the four chain-friendly verbs; pipe inserts your own function.

• Write with one .loc[rows, cols] = — never df[mask][col] =, which writes to a copy.

• Vectorise first; np.where / pd.cut next; apply only when no built-in works; iterrows almost never.

Next: Chapter 3 — the frames are clean; now the statistics: which distribution, which test, which measure of association, and how bad the worst day can be.

Working with an AI Copilot

Three prompts that make an LLM useful for this chapter — and the pitfall each one guards against.

1. “Before you aggregate, print df.index.duplicated().sum() and df.isna().sum(); if either is non-zero, stop and show me the offending rows.” A copilot will happily groupby a frame with a doubled 02:00 hour and a 99 %-empty column and report a clean-looking table.
2. “Show me the .shape after every load, reshape or merge (read_csv, pivot_table, melt, unstack, concat, pd.merge) and explain each change in row count.” The chapter’s discipline in one instruction: 2 305 × 5 → 11 525 × 3 is either a melt or a bug; 401 → 380 after a merge is either an inner join or lost data; a file that always has exactly 65 536 rows is a truncation.
3. “Write every modification as one .loc[rows, cols] = or as an assign inside a chain — never df[mask][col] = … — and tell me if pandas would raise SettingWithCopyWarning.” The copy-versus-view bug runs without error and leaves the original frame unchanged; an LLM that reproduces the idiom from old tutorials will reproduce the bug.

Mistakes Library: Public Health England’s missing 15 841 cases (October 2020)

Warning

On 4 October 2020 Public Health England announced that 15 841 positive COVID-19 tests taken between 25 September and 2 October had been left out of the United Kingdom’s daily case counts. Commercial laboratories sent results as CSV files; an automated pipeline loaded each file into a legacy Excel .xls template before it entered the national dashboard. The .xls format caps a sheet at 65 536 rows, and the way each result was laid out meant a template filled after about 1 400 cases. Every row beyond the cap was silently dropped — no error, a plausible-looking total every day for a week.

The consequence was not a wrong chart. The contacts of those 15 841 people — an estimated 48 000 — were not traced for up to eight days during the autumn wave, and the reported seven-day incidence for England was understated by roughly a fifth. The fix was a .xlsx template and a split of the incoming files; the real fix would have been a single line comparing the row count after the load with the row count in the source file.

Lesson for this chapter: a load, a concat and a merge each have a row count you can predict. Print it, assert it, and treat a count that never changes as a symptom. A pipeline that runs without error has proved only that it ran.

Decision Memo — Which utilities enter the 2011–2012 load model, and how

To: Head of Forecasting, grid analytics From: <Your name>, data engineering Subject: Handling of missing and duplicated hours in AEP_hourly.csv / pjm_hourly.csv before modelling Date: 2026-09-15

Recommendation: Model AEP, COMED and FE only; drop NI. Forward-fill the two single-hour holes; drop the rows of FE’s five-month gap rather than fill them; keep both copies of each November 02:00 hour with a DST flag and average them only when an hourly profile is needed.

Evidence: - load.isna().sum() on 2011–2012 (17 540 hours): AEP and COMED each miss 1 hour, FE misses 3 624 (January–May 2011), NI is present in 1 hour of 17 540. A plain dropna() keeps 0 rows; dropna(subset=["FE"]) keeps 13,916. - Filling FE’s gap with its median keeps every row but cuts the column’s standard deviation from 1,422.5 to 1,267.7 MW: a fifth of the series would sit at one value, understating the variance any risk or forecast model is built on. - bfill on a time-ordered frame copies a future hour into the past — look-ahead bias; ffill is the only defensible one-hour fill. - pjm_hourly.csv 2014–2017 has no NaN but 4 duplicated labels, all 02:00 on the first Sunday of November: two real loads, one clock label. keep="first" or "last" discards a real hour; keep=False discards two.

Caveats: - Dropping FE’s gap removes January–May 2011 for all three utilities if the model needs aligned rows; a per-utility model keeps 17 539 rows for AEP and COMED. - The 02:00 decision changes a groupby("hour").mean() by one part in 1 460 per year — immaterial for the profile, material for a peak-hour audit.

Next step: Add an assert on the row count and on index.duplicated().sum() at the end of the loading chain, so that the next file with a hole or a doubled hour fails loudly instead of averaging quietly.

Chapter Summary

Concept	Tool
Axes, sorting, alignment	df.axes, sum(axis=), sort_values, sort_index, df1 + df2
Aggregation	agg([...]), agg({col: [...]}), agg(name=(col, fn)), apply(f, axis=)
Missing and duplicates	isna().sum(), fillna, ffill, dropna(subset=), index.duplicated, drop_duplicates(keep=)
Filtering and subsets	(a) & (b), query, isin, loc[mask, cols], iloc
Reshaping and joining	groupby().agg/transform/filter, pd.merge(how=), pivot_table, crosstab, melt, stack/unstack, pd.concat, pd.cut
Idioms	(df.query().assign().groupby().agg()), pipe, one .loc[rows, cols] =, vectorise over iterrows

Next: Chapter 3 — Reshaping Statistics.

Discussion Questions

1. fillna(median) kept every row of FE but cut its standard deviation. For which downstream tasks is that harmless (a plot? a regression? a VaR?) and for which is it a material error?
2. The duplicated 02:00 hour is real data from a real clock change. Should you drop one copy, average the two, or keep both with a flag? What does each choice do to a groupby("hour").mean()?
3. A left merge of the 2015 rankings onto 2016 left some research_16 cells NaN. In a model of “did the university rise?”, is that NaN missing data, or is it the answer?
4. The Public Health England pipeline ran without error for a week and produced a plausible dashboard every day. Which single line from this chapter — a .shape print, an assert on a row count, a duplicated().sum() — would have caught it, and where in a method chain would you put it?