Chapter 2: DataFrames

Chapter Introduction

A Series, the object of Chapter 1, has one axis. A DataFrame has two, and that single extra dimension changes the character of almost everything you do. Every method you learnt for a Series now needs one more decision — which axis? — and every arithmetic operation between two frames silently aligns on labels before it computes. The two-axis world also brings the two-axis diseases: a column that is a fifth empty, a timestamp that appears twice because the clock went back, a merge that quietly dropped eight rows, a pivot that averaged when you wanted a count. None of these failures announces itself. The final number comes out, it looks plausible, and it is wrong. The only defence is a habit: predict the shape of the result before pandas shows it, and treat any surprise as a bug until proven otherwise. This chapter trains that habit on real data — the hourly load of five utilities on the eastern US grid, the Times Higher Education rankings for 2011–2016, a retail inventory of twenty thousand SKUs, a daily panel of five stocks, and the closing prices of Microsoft and Facebook.

The chapter has four sections, and they move from the anatomy of a frame to the style in which you should write about it. §2.1 is about the frame itself: its two axes, how sorting behaves on each of them, what index alignment does to df1 + df2 when the labels only partly overlap, and the agg / apply pair that computes several statistics at once or runs your own function down a column or across a row. §2.2 is the cleaning cycle — audit, fill, drop, deduplicate — on a file with 3 624 missing hours and a doubled 02:00 that daylight-saving time created, followed by the vocabulary of filtering and subset selection: boolean masks, query, isin, loc and iloc, and the time-ordered split that keeps an out-of-sample test honest. §2.3 is the reshaping toolkit — groupby with its three output shapes, merge with its three survival rules, pivot_table, melt, stack, pd.cut and pd.concat — the verbs that turn the table you were given into the table you actually need. §2.4 closes with style: the method chain that replaces a pile of temporary variables, assign and pipe as its chain-friendly verbs, the copy-versus-view bug that the chain protects you from, and the order of preference — vectorise, then np.where / pd.cut, then apply, and a for loop last.

These sections are practical, but they are not mere syntax. groupby is the split–apply–combine idea that underlies every summary table you have ever seen; merge with how= is the question “who survives?”; pivot_table is groupby on two keys followed by unstack; and index alignment is the reason pandas can be trusted with time series at all. Each verb is introduced by predicting its output shape — rows, columns, labels — and then checking. When the prediction is wrong, the chapter stops to explain why, because that is where the understanding lives: the 4 × 4 frame with only three real numbers that df1 + df2 produces when the labels overlap in one row, the 528 NaN that appear when a one-year series is set beside a four-year one, the 393 universities that survive an inner join of 401 against 800.

Why does a statistics course spend a whole chapter on data manipulation? Because every quantitative workflow is reshape → test → decide, and reshaping mistakes are invisible in the final number. The duplicated 02:00 hour every November would, unnoticed, bias any groupby("hour").mean() you built on it. A left merge that fills eight universities with NaN is either missing data or the answer to your question, depending on what you asked. A pivot_table that is left on its default aggfunc="mean" reports an average where you meant a count, and the table looks perfectly reasonable. Chapter 3 turns clean frames into statistics — distributions, tests, measures of association, and the extreme-value theory of the tails — and every one of those numbers is only as good as the frame it runs on. The frame, in turn, is only as good as the predictions you made about its shape along the way.

By the end of the chapter you will be able to load a file that knows its dates, audit it, fill or drop with intent, find and resolve duplicates, filter with masks and query, aggregate by group, join two tables with the right how=, pivot and melt between long and wide, concatenate on either axis, and write the whole thing as one readable chain that never lands on a copy. The companion slide deck (Chapter 2 at the Slides link in the navbar, or directly at https://statpython.pages.dev/topic2.html) follows exactly the same sections, datasets and printed numbers; the book explains, the slides rehearse.

2.1 DataFrame Axes, Sorting, Index Alignment, agg and apply
2.2 Missing and Duplicated Values, Filtering, Subset Selection
2.3 Groupby, Pivoting, Melting, Stacking, Concatenation
2.4 Method Chaining and Pandas Idioms

DataFrame Axes, Sorting, Index Alignment, agg and apply

A DataFrame is a dictionary of Series that share one index. That sentence is the whole design: the columns are Series, so everything from Chapter 1 applies to each of them; the shared index is what lets pandas line them up, so arithmetic between columns — or between frames — never needs a loop. Two axes, labelled both ways. axis=0 runs down the rows and is the default for every aggregation; axis=1 runs across the columns. Getting this orientation right is most of what makes DataFrame code correct, and the way to get it right is to say, before you run, how many numbers the result will contain.

The data for this section are hourly electricity loads from PJM, the grid operator for the eastern United States. Each column is one utility’s demand in megawatts; each row is one hour. Load is a good teaching series because it has structure at every scale — a daily cycle, a weekly cycle (factories close on Saturday), a seasonal cycle (air-conditioning in July, heating in January) — and because the file is real: it is not in time order, one November hour appears twice, and the larger version of it has holes. We will meet each of those facts in turn.

Loading a frame that knows its dates

pd.read_csv has two arguments that do most of the clean-up at load time. index_col="Datetime" makes that column the row label; parse_dates=True converts it from text into datetime64. The payoff is that a plain string indexes straight into the frame — "2017-07-04 15:00" selects one hour, "2017" selects a year — with no strptime, no set_index afterwards. usecols keeps only the columns you asked for, which matters when the file is wide. The .sort_index() at the end is not decoration: the file is not in chronological order, and a DatetimeIndex that is not monotonic cannot be sliced by label range.

The index element is a Timestamp, and the American Electric Power (AEP) load at three in the afternoon on Independence Day 2017 was 16 836 MW. The 2017 slice has 8 760 rows — 365 days of 24 hours — and five columns. df.axes is a list of the two Index objects: axes[0] is the DatetimeIndex of row labels, axes[1] the column Index(['AEP', 'COMED', 'DAYTON', 'DEOK', 'DOM']). The same numbering — 0 for rows, 1 for columns — is what you pass to sum, drop, sort_index and agg. Because the index is date-typed, it also exposes calendar attributes: .year, .month, .day_name() — 1 January 2017 was a Sunday — which we will use to build features and groups without ever touching a string.

The .copy() after the slice deserves a word. raw.loc["2017"] may return a view onto raw; adding columns to a view is exactly the situation in which pandas raises its SettingWithCopyWarning and, worse, sometimes writes and sometimes does not. Taking an explicit copy costs a few milliseconds and removes the ambiguity. §2.4 returns to this bug in detail.

Two axes, one decision: sum down or sum across?

df.sum(axis=0) and df.sum(axis=1) are both legal and produce results of completely different lengths. The mental picture that works is collapse: axis=0 collapses the rows, so one number per column survives; axis=1 collapses the columns, so one number per row survives. Every aggregation — sum, mean, std, quantile, max — takes the same switch, and the default is always axis=0.

Column-wise, AEP delivered 126.9 million MWh over 2017 and DAYTON, a much smaller utility, 17.3 million. Row-wise, the five utilities together drew 38 552 MW in the first hour of the year, 37 376 MW in the second — the combined load in that hour, which is what a system operator watches. The third computation shows that quantile obeys axis=1 too: the interquartile range across the five utilities in the first hour of 2017 was 7 810 MW, a measure of how unequal the five loads are at that moment rather than of how variable any one of them is over time. Notice that the row-wise statistics inherit the DatetimeIndex and the column-wise ones inherit the column names: the surviving axis always labels the result.

Sorting rows by a column, and columns by a row

sort_values(by="AEP", ascending=False) reorders the rows so the peak-load hours come first. The question worth predicting is what happens to the index. It travels with the rows: the first label of the sorted frame is the timestamp of the peak, and the frame is no longer chronological. That is why sort_index() exists — to put it back. Less often used, but symmetric, is sort_values(..., axis=1): then by= names a row label and the columns are reordered.

AEP’s peak hour of 2017 was 17:00 on 19 July, at 21 678 MW — a heat-wave afternoon — and the two runners-up are the hour before it and 08:00 on 9 January, a cold-snap morning. The index is no longer monotonic after the sort and is restored by sort_index(). Sorting the columns by name in descending order gives DOM, DEOK, DAYTON, COMED, AEP; sorting them by the values in the first row of the year (by=df.index[0], axis=1) gives DAYTON, DEOK, DOM, COMED, AEP — smallest utility to largest at midnight on 1 January. The symmetry is exact: by names a label on the other axis from the one being reordered.

Making changes: add, derive, drop, rename, assign, replace

New columns are assignments: df["Month"] = df.index.month creates a column from the index. A list comprehension over that column builds a categorical Season. The method to predict carefully is drop: it returns a new frame and leaves the original untouched unless you re-assign the result or pass inplace=True. Forgetting this is the most common “my column is still there” bug in student code.

The four seasons hold 2 159 to 2 209 hours each (the quarters are unequal in days, and autumn as defined here includes 31-day months). After df2 = df.drop([...], axis=1), df2 has five columns and df still has seven: drop copied. The same non-mutating contract holds for rename, which here takes a function and lower-cases every column name, for assign, which added AEP_GW and was captured by re-assigning df, and for replace, which maps old values to new ones through a dictionary. assign is chainable and never edits in place; the bracket form df["new"] = … is faster (roughly fifteen times on the full 178 000-row file) but mutates. Choose by intent: mutate when you are building the working frame, assign when you are inside a chain.

Index alignment: the union of labels

This is the mechanism that makes pandas different from NumPy, and it is worth a slow look. When you add two frames, pandas does not add position to position; it aligns on the union of row labels and the union of column labels, computes where both operands have a value, and writes NaN everywhere else. Take df1 as rows 0–2 × columns 0–2 and df2 as rows 2–3 × columns 0–3. Their labels overlap in exactly one row (02:00) and three columns. Predict the shape of df1 + df2 and the number of filled cells before you reveal.

Four rows in the union, four columns in the union — how many cells carry a number?

The sum is 4 × 4 with only three real numbers — the 02:00 row for AEP, COMED and DAYTON, each exactly double the original because the same hour was added to itself. Every other cell is NaN because one operand or the other had no such label. Two consequences follow. First, this is a feature: it is why you can add a return series and a factor series with different date ranges and get NaN rather than a silently misaligned answer. Second, it is a trap: a NaN that appears after arithmetic is usually an alignment failure — a stray index level, a timezone, a string date on one side — not a missing value in the data.

Stacking the same two frames with pd.concat keeps every row, including the one whose timestamp both frames share. The result has a duplicated index label, which is legal in pandas and dangerous in practice: .loc["2017-01-01 02:00"] on such a frame returns two rows, and any alignment against it becomes ambiguous.

.index.duplicated() returns a boolean mask that is True for every copy after the first (with keep="first") or before the last (keep="last"); negating it with ~ and indexing keeps one copy per label. The alternative route — reset_index, drop_duplicates(subset="Datetime"), set_index — reaches the same 4 × 4 frame. Both idioms will be needed in §2.2, where a real duplicated timestamp appears that no concat created.

agg and apply: several statistics, or your own function

agg accepts a list of function names and returns one row per function, one column per input column — a compact summary table. It also accepts a dictionary that maps each column to its own list of functions; then the result has one row per distinct function name and NaN where a statistic was not requested for a column. apply is the escape hatch: it passes each column (axis=0, the default) or each row (axis=1) to a Python function you wrote. The lecture notebook’s example builds $Y = 10 + 0.8X + \varepsilon$ with a function that receives one row at a time — the pattern for deriving a feature from several other features.

The list form gives a 4 × 5 table: AEP’s 2017 load ranged from 9 698 to 21 678 MW around a mean of 14 484; DAYTON’s from 1 151 to 3 204. The dictionary form is 3 × 2 with two NaN cells — the mean of AEP and the sum of DEOK were never asked for. In the synthetic frame the column means are 49.5 for X (the mean of 0…99), −2.24 for the noise with seed 5, and 47.36 for Y, which is $10 + 0.8 \times 49.5 - 2.24$ to rounding: apply on the columns confirms the construction. The final two lines compute the range of each row two ways — agg with a list and axis=1, then a subtraction; apply with a lambda — and they agree: the five utilities’ loads spanned 11 545 MW in the first hour of the year.

apply(..., axis=1) is a Python loop under the hood. It is fine for thousands of rows and slow for millions, where the vectorised 10 + 0.8 * newdf.X + newdf.Noise wins by a factor of ten to a hundred. §2.4 makes that the last rule of the chapter: vectorise first, apply only when nothing built-in will do.

The habit this section trains

Before every DataFrame operation, say the shape. sum(axis=1) on 8 760 × 5 → 8 760 numbers. agg(["max","min"]) on 8 760 × 5 → 2 × 5. df1 + df2 with one shared row → union × union, mostly NaN. If the printed shape disagrees with your prediction, you have learnt something about pandas or about your data; either way, stop and find out which.

8 760 — one per row. axis=1 collapses the columns, so each number is the sum across the five utilities in that hour (the combined load). axis=0, the default, would collapse the rows and return 5 annual totals.

4 × 4 — the union of row labels by the union of column labels — with only 3 non-NaN cells (the shared row, in the three shared columns). Arithmetic aligns on labels; any label missing from either operand yields NaN.

Missing and Duplicated Values, Filtering, Subset Selection

Real data arrive with holes and repeats. The lecture notebook’s original grid file — the full AEP_hourly.csv, twenty years of hourly load for twelve utilities — is a case study in both. One column is a fifth empty; another is 99.99 % empty; two are missing a single hour each. A second file, the cleaned 2014–2017 version, has no NaN at all and yet four of its timestamps appear twice, for a reason that has nothing to do with data entry. This section is the cycle you will run on every dataset you ever load: audit, then decide — fill or drop, and which copy of a duplicate to keep — and then carve out exactly the rows and columns a model needs, predicting each row count before you run.

The order matters. Students who fill first and audit later never learn what they filled. The first line on any new frame is df.isna().sum(); the second is df.index.duplicated().sum(). Everything else waits until you know those two numbers.

Audit first: count NaN per column

We load four utilities for 2011–2012 from the large file. isna() returns a boolean frame the same shape as the data; summing it once counts True per column; summing again adds those up; dividing by .size (rows × columns) gives the fraction of all cells that are missing. The per-column counts are the numbers you act on.

Four different diseases in one 17 540 × 4 frame. AEP and COMED are each missing exactly one hour — a glitch. FE (FirstEnergy) is missing 3 624 hours, and they are contiguous: the column simply starts on 1 June 2011, five months late. NI (Northern Illinois) is present in exactly one hour out of 17 540. Thirty per cent of all cells are NaN, but that aggregate hides the structure: the right treatment is different for each column, and only the per-column audit tells you which is which.

Fill forward, fill backward, fill a constant

Zoom in on AEP’s single hole, at 04:00 on 6 December 2012. There are four common fills. ffill() carries the last observed value forward; bfill() pulls the next observed value backward; fillna(0) writes a constant; fillna(median) writes a typical value. One of these is a crime in time series, and it is worth deciding which before running.

At 04:00 the raw value is NaN; ffill writes 14 711 (the 03:00 load), bfill writes 15 192 (the 05:00 load), and the constant writes 0. For an hourly load series, forward-fill is the honest choice: at 04:00 the most recent thing you knew was the 03:00 value. Back-fill is look-ahead bias: it copies a value from the future into the past, and if you later build a forecast on the filled series you have leaked 05:00’s load into 04:00’s row. The lecture’s rule is blunt — never use bfill on a time series — and the reasoning is the same as for every leak in this course: a model evaluated on data that contained tomorrow’s answer will look better than it is. Filling with zero is not a leak, but it is a lie of a different kind: 0 MW at four in the morning is an outage, not a missing value, and any statistic that touches it (the mean, the minimum, a rolling standard deviation) will be distorted by a number that never happened.

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. The answer is a small shock: NI is present in exactly one hour — and in that hour COMED and FE happen to be missing — so a plain dropna() keeps nothing. Two refinements save the day. subset=["FE"] drops only the rows missing the column you actually intend to model; dropna(axis=1, thresh=...) drops columns that fall below a minimum count of present values.

dropna() keeps 0 of 17 540 rows. dropna(subset=["FE"]) keeps 13 916 — every hour from June 2011 on. The threshold rule keeps AEP, COMED and FE and discards NI, which is what any analyst would do by hand. The surgical version — drop the hopeless column first, then drop rows with any remaining NaN — yields 13 915 × 3 with no NaN (one fewer row than the subset version, because the hour AEP or COMED is missing goes too).

The two fill lines quantify the price of the alternative. Filling FE’s 3 624 holes with its median keeps every row but cuts the standard deviation from 1 422.5 to 1 267.7: a fifth of the column now sits exactly at the centre, and any downstream statistic that depends on spread — a variance, a confidence interval, a VaR — is biased towards calm. Filling with the mean instead has a small algebraic charm: the post-fill mean equals the pre-fill mean (7 806.3 both times), because adding copies of the mean cannot move it. That is why mean-filling is popular, and also why it is dangerous — it preserves the one statistic people check and corrupts the ones they do not.

Rule of thumb from the lecture

When not too many values are missing, fillna(median) for numerical columns and fillna(mode) for categorical ones; the rows are worth more than the small distortion. When most of a column is missing — NI here — drop the column: a filled column is a fiction. When the missing block is long and contiguous, as FE’s five months are, prefer dropping those rows (subset=) over inventing them; the seasonal structure you would be filling is exactly what a model needs to learn.

A duplicated hour that nobody typed

Switch to the smaller pjm_hourly.csv — the notebook’s cleaned new.csv, four utilities, 2014–2017. It has no NaN. But some timestamps appear twice. The habit is to count them, and then to look at which hours they are, because the pattern is the diagnosis.

Four duplicated labels in 35 063 rows, one per year: 2 November 2014, 1 November 2015, 6 November 2016, 5 November 2017 — every one at 02:00 on the first Sunday of November. That is the hour that happens twice when US clocks fall back from daylight-saving time. The two rows carry different loads (12 994 and 13 190 MW in 2014) because they are genuinely different hours of electricity demand; they share a label because the local clock read 02:00 twice. Nothing is “wrong” with the file. The calendar did it. keep=False in index.duplicated marks every copy, which is what you want when the task is to inspect rather than to delete.

The choice of which copy to keep is a modelling decision, not a cleaning one. drop_duplicates(subset=…) on the reset frame — or the boolean-mask idiom on the index — offers three policies. Predict the row counts before revealing.

hours has 4 duplicated timestamps (4 pairs). How many rows does each keep= policy leave?

keep="first" and keep="last" each delete one copy per duplicated label, leaving 35 059 rows. keep=False deletes both copies of every pair — eight rows gone, 35 055 left — and the mask form hours[~hours.index.duplicated(keep=False)] reaches the same count. Which policy is right depends on what you will do next: for a groupby("hour").mean() either single-copy policy is fine and both copies would double-weight one hour; for a model of hourly changes the honest choice may be to drop both, since the change across a repeated clock hour is not a real one-hour change. The lecture’s discussion question — drop one, average the two, or keep both with a flag — has no universal answer, only the requirement that you decide on purpose.

Masks, `query` and `isin`

A boolean mask built from one column selects rows of the entire frame, all columns intact. Two conditions need & for and, | for or, and parentheses around each comparison, because & binds more tightly than > in Python: hours.AEP > 15000 & hours.AEP < 16000 is parsed as hours.AEP > (15000 & hours.AEP) < 16000 and raises a TypeError. .query() sidesteps the precedence problem by parsing a string in which you write and/or, and @name splices in a Python variable. isin tests membership against a list and pairs naturally with day_name().

The 15 000–16 000 MW band holds 4 359 hours; the two-sided “either very low or very high” filter holds 5 903. query returns exactly the same 4 359 rows — equals is True — and reads better for long conditions; on the full notebook file the mask took 2.8 ms and query 9 ms, a difference nobody notices. The @hot line shows a mask defined outside the string being reused inside it: 1 105 hours had AEP above 20 000 MW and COMED above 12 000, both utilities under stress at once. The weekend split is the first real finding of the chapter: 10 032 weekend hours average 13 795 MW against 15 240 MW for the 25 031 weekday hours — a gap of about 1 400 MW, which is the industrial load that switches off on Saturday. ~ negates a mask. §2.3 will get the same number from groupby in one line.

One column or two, `loc` or `iloc`, and the time-ordered split

A DataFrame is a dict of Series; pull one column out with hours["AEP"] and you get a Series, the object of Chapter 1. Ask for a list of columns — hours[["AEP"]], note the double brackets — and you get a DataFrame, which is what a model expecting a two-dimensional X wants even for one feature. Then the two indexers: .loc selects by label and its slices are right-inclusive; .iloc selects by position and its slices are right-exclusive, like every Python slice. loc[mask, columns] filters rows and selects columns in one call.

The label slice from 22:00 on 1 January 2017 to and including 00:00 on 2 January returns three rows; the position slice 1:4 also returns three, positions 1, 2 and 3. Selecting a single label returns a Series keyed by column name: the last hour of 2017 saw AEP at 18 877 MW. loc[hours.AEP > 24000, ["COMED", "DAYTON"]] finds the nine hours in four years when AEP exceeded 24 000 MW — all in the polar-vortex week of late January 2014 — and shows what the other two utilities were doing at the time. The last line combines a condition on DEOK with a positional choice of columns (hours.columns[-2:]): 15 138 of the 35 063 hours had DEOK above its mean of 3 104 MW.

For time series there is one more selection you will make constantly: the train/test split. You never shuffle. The first 80 % of hours train, the last 20 % test, and the boundary is a position, not a random draw.

28 050 training hours end at 19:00 on 14 March 2017 and 7 013 test hours begin the following hour. The two frames abut with no overlap and no gap, and every test timestamp is later than every training timestamp — the property that makes an out-of-sample evaluation honest. A random 80/20 split, the default in most machine-learning tutorials, would put Tuesday 14:00 in the test set and Tuesday 13:00 and 15:00 in the training set; on an hourly load series with 0.99 autocorrelation that is not a test at all.

It copies a value from the future into an earlier row — look-ahead bias. A model fitted on the filled series has seen tomorrow’s value in today’s row. ffill() only looks backward; a constant or a median is at least honest about knowing nothing.

Daylight-saving fall-back: US clocks go from 02:00 back to 01:00, so the hour 02:00 occurs twice. Two genuinely different loads share one label. The file is not corrupt — the calendar did it — but a groupby on the hour would double-weight it unless you choose a keep= policy.

Groupby, Pivoting, Melting, Stacking, Concatenation

Split → apply → combine. That three-word recipe, named by Hadley Wickham in 2011 but implemented in every database since the 1970s, is what groupby does: split the rows into groups by a key, apply a function to each group, combine the results into one object. Every summary table you have ever seen — sales by region, returns by sector, load by weekday — is a groupby or its two-key cousin the pivot_table. Every “tidy” dataset is a melt away from the wide table your plotting code wants. And almost every real analysis ends with a merge, where the how= argument decides which rows survive. This section’s discipline is the same as the last two: predict the shape of each reshape — how many rows, how many columns, what labels on each axis — 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 name. groupby("WeekDay") produces a lazy object that knows how the rows partition; selecting ["AEP"] and calling .mean() applies the function to each of the seven groups and combines the seven answers into a Series indexed by weekday. That is aggregation: the output has one row per group, and the group key becomes the index.

Seven rows. Sunday is the lightest day at 13 474 MW and Thursday the heaviest at 15 198; Saturday sits with Sunday, the five working days sit together 1 000–1 700 MW higher — the weekend gap of §2.2, now decomposed by day. The second call passes agg a list containing two string names and one user-defined function: the result is a 7 × 3 table (groups × aggregations), and iq — the interquartile range — is applied group by group exactly like the built-ins. The third form is the one to memorise for reports: named aggregation, agg(new_name=("source_column", "function")), which lets each output column draw on a different input column and carry a label you chose. Friday averaged 14 987 MW of AEP load, had a COMED standard deviation of 2 128 MW, and contributed 2 520 hours (105 Fridays × 24).

transform and filter: the other two shapes

Aggregation is one shape of output. There are two more, and confusing them is a classic bug. transform applies a function to each group and then broadcasts the result back to the original rows, so the output has exactly len(pjm) entries aligned to pjm’s index — ready to be assigned as a new column. Standardising within each weekday is the canonical use: it produces a z-score that answers “how unusual is this hour for a Sunday?” rather than “how unusual is it for any hour?”. filter keeps or discards whole groups according to a test on the group, returning the surviving input rows unchanged.

z_day has 17 544 entries, the same as pjm. The last hour of 2017 — a Sunday evening — is 1.74 standard deviations above the mean of all hours but 2.40 above the mean of Sundays: a bigger surprise once you condition on the day, because Sundays are usually quiet. Group-wise standardisation is how seasonally adjusted features are built; a model that sees z_by_weekday does not have to learn the weekly cycle itself. In the toy frame, the group means of B are 3 for foo and 4 for bar; filter(lambda g: g["B"].mean() > 3) keeps the three bar rows — index 1, 3, 5 — with their original values intact. The last line uses apply with a lambda on each group and gets the interquartile range of AEP by weekday: 2 940 MW on Tuesday, 3 290 on Friday, the same numbers agg([..., iq]) produced. Three shapes, one verb: agg → one row per group; transform → one row per input row; filter → the input rows of the groups that pass.

A second frame, and the three kinds of join

The Times Higher Education world rankings for 2011–2016 are a different kind of table: 2 603 rows, one per university-year, with text columns that hide numbers — total_score uses '-' where a score is missing, and world_rank uses '=201' for ties. pd.to_numeric(errors="coerce") turns the junk into NaN so the column can be averaged. The frame is also the right place to see joins, because the same universities appear in several years and the list grows over time: 401 in 2015, 800 in 2016.

The grouped agg(["mean", "size"]) puts the count next to the mean so that countries with two universities can be filtered out before ranking: among countries with at least twenty entries, the Netherlands leads on research score at 51.9 (75 rows), Hong Kong is second at 46.8 (34 rows), and the United States averages 45.2 across 659 rows — the US figure is lower because its long tail of ranked universities pulls the mean down, a composition effect the count makes visible.

Now the merge. Both year-slices have unique names (no duplicates, so the join is one-to-one). pd.merge(u15, u16, on="university_name") with the default how="inner" keeps only names present in both years: 393 rows. how="left" keeps every 2015 row — 401 — and fills NaN in the _16 columns for the ones that left the list. how="outer" keeps the union: 401 + 800 − 393 = 808. The suffixes argument names the two copies of world_rank and research. The last two lines answer a question with the join itself: eight 2015 universities have no 2016 row (NaN in research_16 after the left merge), and 401 − 393 = 8 confirms it. The rule of thumb is by intent — left to enrich a spine you want to keep whole, inner for matched-only analysis, outer to audit what is missing on either side — and the discipline is to print the row count after every merge, because 401 → 393 is either an inner join or lost data and only you know which.

pivot_table: two keys become rows × columns

A groupby on two keys returns a Series with a MultiIndex — two levels of labels, one per key. pivot_table(index=, columns=, values=) is the same computation laid out as a grid: the first key’s values down the rows, the second key’s values across the columns, an aggregate (mean by default) in each cell. It is the executive-summary shape. pivot without _table is the aggregation-free version and raises if any (row, column) pair occurs more than once — which is exactly the case for countries with many universities per year.

The 4 × 6 grid holds the mean research score of each country in each year. Hong Kong’s mean rises from 45.4 in 2012 to 52.0 in 2013 and settles near 44–46; the United States falls from 61.9 in 2011 to 37.4 in 2016. The fall is not a decline in American research — it is the list doubling from 200 to 800 universities, which adds lower-ranked institutions to every country’s mean. A pivot table shows you the composition effect; it does not correct it. The second computation proves an identity worth remembering: pivot_table equals groupby([a, b]).mean().unstack(b). The grouped Series has a 24-entry MultiIndex with level names ['country', 'year']; unstack("year") moves the year level from the index into the columns and produces the same 4 × 6 frame (equals is True). The count table, unstacked the other way, shows the list growing: 72 US universities in 2011, 113 in 2012.

The lecture’s inventory data give a second, commercial pivot. inventory.csv is a Kaggle retail file of 20 000 SKUs with a regular price, a release year and a flag for new releases. The pivot of price by release year and flag is a 7 × 2 table for releases from 2010 on; pd.crosstab is the same computation under a different name.

Of 20 000 SKUs, 4 972 sold in the observation window and 15 028 did not. Within releases from 2010 on, new-release items are priced higher in every year — $128.62 against $100.58 in 2010, widening to $169.13 against $111.55 in 2013 — and the 2016 cell for new releases ($287.66) rests on a handful of items, which a count pivot would reveal. crosstab returns an identical table. The stack/unstack pair is the general machinery under all of this: groupby on two keys → MultiIndex Series; unstack(level) moves the named level into the columns (2 591 new-release SKUs unsold, 1 556 sold); stack() is the exact inverse and puts columns back as an inner index level; .T swaps the axes. None of these aggregates — they only move labels between the two axes.

When apply is not needed: pd.cut

To bin a numeric column into tiers, the reflex is apply(bucket) with a hand-written if chain. pd.cut does it vectorised, and its bins are right-closed by default: an edge value falls into the lower bucket. The check that makes this stick is a value sitting exactly on an edge.

300 lands in 'mid', because the interval is $(150, 300]$. Applied to the inventory, pd.cut plus crosstab gives a contingency table in two lines: 2 417 of the new-release SKUs are priced above $100 against 348 of the others. The order of preference for building a column is 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) pair, with the price in a value column. Long is the tidy shape for storage and for groupby. Wide — one column per stock, dates down the rows — is what plotting and correlation want. pivot (no aggregation; the pairs are unique) goes long → wide; melt(id_vars=…) goes back, keeping the identifier columns and turning every other column into (variable, value) pairs. Predict the row count after melting back.

The wide frame is 2 305 dates × 5 stocks. How many rows does melt produce, and does it match the original long file?

2 305 × 5 wide becomes 11 525 × 3 long — every cell of the grid becomes one row — and that is exactly the shape of the file we started from. The five tickers are AAPL, GSPC (the S&P 500), IBM, MSFT and SBUX, from 3 January 2007. The lecture’s var_name="Course", value_name="Score" example is the same call on a gradebook. The round trip is worth internalising as a shape identity: rows × columns of wide = rows of long.

pd.concat: side by side, or on top

pd.concat glues frames along an axis. With axis=1 it performs an outer join on the row labels: every date from either series appears, and a series with no value for that date gets NaN; keys= names the resulting columns. With axis=0 it stacks rows and, as in §2.1, keeps duplicated labels. The pair-and-correlate pattern at the end is what every factor analysis begins with.

msft covers 2015–2018 (780 days) and fb only 2016, so the side-by-side frame has 780 rows and 528 NaN in the FB column — the 2015, 2017 and 2018 dates for which FB has no value. Around the turn of 2015 you can see the join happen: 30 and 31 December 2015 carry MSFT values and NaN; 4 January 2016 carries both. Stacking December 2015 on top of the full year 2015 produces 293 rows with 22 duplicated dates — the December trading days that appear in both pieces — which is the §2.1 warning again. The final block builds a two-column frame and computes the Pearson correlation of daily percentage changes between Apple and the index over 2007–2016: 0.614. That is the number a beta comes from, and it took a concat, a pct_change and a corr.

Plotting a frame

The notebook draws its charts with Plotly; in the browser we use pandas’ matplotlib backend. A DataFrame’s .plot() draws one line per column against the index — which, for a wide price frame, is the growth-of-a-dollar chart once every column is rebased to its first value.

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()

Dividing by wide.iloc[0] broadcasts one row across all 2 305 — index alignment on the column labels — so each line starts at 1. Apple’s line ends near 10; the S&P 500 near 1.5. The scatter that follows uses the rankings frame after cleaning world_rank (strip the '=' that marks ties, coerce to numeric) and keeping the 2016 top 100.

Exactly 100 rows. Citation score falls as rank number rises — the correlation is −0.50 — but the scatter is a wedge, not a line: the top ten all score above 95 on citations, while institutions ranked 60–100 spread from the 60s to the high 90s. Rank is a composite; citations are one of its inputs; the scatter shows how loosely the two are coupled once the very top is excluded. Chapter 3 (§3.4, on association) returns to the question of what a correlation coefficient can and cannot see.

agg → one row per group (aggregation). transform → one row per input row, the group result broadcast back (e.g. within-group z-scores). filter → the input rows of the groups that pass a test on the whole group.

inner 393 (matched only); left 401 (every 2015 row, NaN where no 2016 match); outer 401 + 800 − 393 = 808 (the union). Print the row count after every merge.

Method Chaining and Pandas Idioms

Most pandas methods return a new DataFrame. That one fact — visible all through this chapter in drop, rename, assign, query, groupby().agg() — lets you chain them with . into a pipeline, like a Unix pipe: the output of one step is the input of the next, and no intermediate result needs a name. The chained style is not merely aesthetic. Temporary variables are where bugs live — t2 built from the wrong t1, a stale df_clean reused after df changed — and a chain has none. This section shows that a chain and a pile of temporaries give the same answer on the five-stock panel, provokes the most common pandas bug and shows the idiom that avoids it, and replaces a slow loop with one vectorised line.

Spaghetti versus 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 wrapped in parentheses so each method sits on its own line.

Identical — equals is True. Each stock has 2 305 trading days; Apple’s mean daily change was 0.118 % with 1 213 up-days, IBM’s 0.033 % with 1 171. The chain reads as a recipe: take the long file, keep the non-index rows, flag the up-days, group by stock, summarise. There is no t1 to misname and no t2 that silently refers to an older t1. The lambda d: inside assign is the piece of syntax that makes chains possible — it receives the frame as it is at that point in the chain, which has no name, so a lambda is the only way to refer to it. query, assign, pipe and loc are the four chain-friendly verbs; every one of them returns a new frame.

assign does not mutate

assign adds columns and returns a new frame; the original is untouched. That immutability is exactly what makes it safe inside a chain — a chain never clobbers its input. The lambda form also lets a new column reference one created earlier in the same call, so a derived feature and a flag on it can be built in one assign.

new has Close, ret and big; msft still has only Close. Thirty-one of 780 days moved more than 3 % in either direction — 4 % of days, against the 0.35 % a normal with Microsoft’s 1.4 % daily standard deviation would give, which is the fat tail that Chapter 3 (§3.1) will measure properly, here in one line.

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

Label each Apple row by month, group, summarise (last price, trading days, volatility of the daily change), flag the volatile months. dt.to_period("M") turns a timestamp into a month label; the five verbs are filter, label, group, summarise, flag. Predict how many months January 2007 to February 2016 contains.

110 rows — nine full years plus January and February 2016 — and four columns. January 2007 closed at $11.34 (split-adjusted) after 20 trading days with a daily-change standard deviation of 2.91 %, flagged as volatile; 37 of the 110 months clear the 2 % bar. Wrap the chain in a function and you have a reusable pipeline. pipe(f, …) is how you drop such a function into the middle of another chain: it passes the frame as f’s first argument and forwards the rest, so chain.pipe(top, 2, "mean_change") picks out Apple and Starbucks as the two best average movers, and monthly.pipe(top, 3, "vol") names the three most volatile months — October, September and November 2008, with daily standard deviations of 6.0 %, 5.5 % and 5.1 %. The chain-and-pipe style is the modern pandas idiom; the lecture’s recommendation is to always wrap a multi-line chain in (...).

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. The opening ( lets each .method(...) sit on its own line, PEP 8-style.

The SettingWithCopy bug

You want a regime label wherever Close > 80. There are two ways to write it, and one of them silently does nothing. This is the second debug-yourself: run the cell and explain why the first write vanished.

Both blocks below try to label the high-price rows. After the first block, does px have a regime column? After the second?

The chained form px[mask]["regime"] = ... — here spelt out with the intermediate sub — first evaluates px[mask], which returns a copy; the assignment writes into that copy, which is then discarded, and pandas only warns (SettingWithCopyWarning, and in this cell not even that, because the copy has a name). "regime" in px.columns is False. The second form, px.loc[px.Close > 80, "regime"] = "high", resolves rows and columns in a single indexing step and writes into px itself: 68 rows are labelled high and the other 712 are NaN. Write with one .loc[rows, cols] =. The wrong way evaporates silently, and it is the most common pandas bug there is — including in the lecture notebook, where old["Min20"] = ... on a slice of dji triggered exactly this warning, which is why the extreme-value section of Chapter 3 (§3.5) takes a .copy() first.

Fix the slow loop

iterrows walks the frame one Python row at a time, building a Series for each. On 11 525 rows it is already sluggish; on a million it is minutes. The lecture’s practice cell cuts the loop to 100 rows because the full loop is too slow to demonstrate; the vectorised product runs over all rows in a fraction of a millisecond.

Both produce the same column — the daily dollar move per share, −0.04, 0.86 and 0.02 for the first three rows — and the vectorised line, running in C over eleven times as many rows, is faster than the loop over a thousand by two orders of magnitude (the exact ratio depends on the machine, and on the browser’s WebAssembly build of NumPy, but the shape of the comparison does not). iterrows is almost always a code smell. The order of preference, for the last time: vectorised arithmetic → np.where / pd.cut → apply → a for loop.

Working with an AI copilot

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

“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.
“Show me the .shape after every reshape or 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 → 393 after a merge is either an inner join or lost data.
“State the how= of every pd.merge and the aggfunc= of every pivot_table explicitly, and tell me why that choice and not the default.” Both defaults — how="inner", aggfunc="mean" — run without complaint and return a plausible table: the inner join drops the eight 2015 universities that left the ranking, and the mean reports an average price where you wanted a count of SKUs. An LLM cannot know which rows you meant to keep or which statistic you meant to compute unless you make it say so.

df[mask] returns a copy; the assignment writes into that copy, which is discarded — pandas only warns. Write with one .loc[rows, cols] = value, which resolves rows and columns in a single indexing step and writes into df itself.

Chapter Wrap-up

You can now take a raw file and predict its way to a clean frame: load it date-typed, audit isna().sum() and index.duplicated().sum() before anything else, fill forward but never backward, drop surgically with subset= and thresh=, decide which copy of a duplicated hour to keep, and carve out rows and columns with masks, query, loc and iloc — including the time-ordered split that makes an out-of-sample test honest. You can reshape: groupby with its three output shapes, merge with its three survival rules, pivot_table and its identity with groupby().unstack(), melt and pivot as a round trip, concat on either axis, pd.cut for bins. You can write all of it as one chain, and you know which write lands on a copy and why one .loc[rows, cols] = is the fix. And you carry a set of numbers that anchor the habit: the 1 400 MW weekend gap that a mask found and groupby confirmed, the 393 universities that survive an inner join of 401 against 800, the 528 NaN that a concat of a four-year series beside a one-year one must produce, the 31 of 780 days on which Microsoft moved more than 3 %.

Chapter 3 turns these clean frames into statistics. It begins with the distinction between a population and a sample and why pandas divides by $n - 1$; it estimates a density two ways — a kernel density estimate with no formula and a Student-$t$ with one — on daily stock returns, tests normality with a Q-Q plot, Shapiro–Wilk and Kolmogorov–Smirnov, turns a two-sample test into a rolling regime detector, ranks Pearson, Spearman and distance correlation by what shape of dependence each can see, and ends with extreme value theory on the Dow Jones around Black Monday, where the block minima of a groupby become a fitted GEV and an extreme VaR. Every one of those computations starts with a frame built by the verbs of this chapter: a concat to set two stocks side by side, a pct_change on the aligned result, a groupby over 20-day blocks, a .loc slice of years taken with an explicit .copy(). Chapter 4 then makes the frame a design matrix $X$ and a target $y$ for predictive models, and the same shape-prediction habit becomes the discipline of checking the train–test split and the residuals before trusting a fit.

The slides for this chapter, at https://statpython.pages.dev/topic2.html, rehearse every section here with a prediction before every cell — same datasets, same printed numbers; use them to test yourself, and use this chapter to understand why the predictions came out as they did.

Predict the shape — rows, columns, labels, and for a statistic its rough size — before pandas shows it, and treat any surprise as a bug until you have found out whether it is pandas, your data, or your model that you misunderstood.

← Chapter 1: Data Structures and Methods of Series · Contents · Chapter 3: Reshaping Statistics →