Chapter 2: DataFrames
Chapter Introduction
Chapter 1 introduced labelled columns. A DataFrame puts those columns into a table with a shared row index. In this chapter, begin every task by stating what one row represents, which columns identify it and what shape the result should have. These checks connect the Python operation to the statistical question.
The learning path follows the local Topic 2 lecture structure. First understand the table, its two axes and column updates. Next investigate missing or repeated observations, decide which repairs are justified and select the analysis sample. Then learn the different shapes produced by grouped calculations, validated joins and long/wide reshaping. Each section contains numbered parts with a question, an expected result, worked code and checks.
The datasets have distinct roles. Hourly electricity load introduces axes, calendar order and genuine missingness. University rankings and the inventory file support group comparisons and joins. Stock tables connect these same methods to security/date columns and grouped features. When a new dataset appears, inspect its observation unit before carrying a method over from the previous example.
Section 2.4 combines these operations into a readable feature pipeline: organise stock/month rows, calculate grouped historical measurements and validate the resulting columns. The final section shows how to communicate a checked table through interactive charts.
By the end, you should be able to produce an audited feature table with stock and date columns and explain each change in its rows, columns and missing values. The companion Slides use the same section and part numbers, data slices and worked results. Read the explanations here, rehearse the examples there and use the checks to locate a misunderstanding.
Week 2 learning route
The 3.5-hour class combines guided examples, group discussion, repairs and the L1/L2 company notebook. Prepare the table foundations, data-quality investigation and grouped-table sections first.
The main engineering task produces an audited table, a validated join, a grouped feature and a labelled chart. Study §2.4.1–§2.4.3 to build that pipeline, checking input/output previews and row counts after each step.
Table of Contents
- 2.1 DataFrame Foundations
- 2.2 Data Quality: Audit, Repair and Selection
- 2.3 Grouped Tables, Joins and Reshaping
- 2.4 Feature Pipelines and Research Applications
- 2.5 Interactive Visualisation
Use the numbered parts within each section to locate concepts and worked examples. Read a part in sequence before attempting its application.
DataFrame Foundations
A DataFrame is a two-dimensional table whose columns can each be selected as a Series. The columns share one row index: 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.
| Part | Question |
|---|---|
| 2.1.1 Table structure and axes | What does each row and column represent? |
| 2.1.2 Ordering, column updates and export | How does the table change? |
| 2.1.3 Alignment and table summaries | Which index values, column names and axis does the calculation use? |
Table structure and axes
Question: What does each row and column represent?
Result: A DataFrame with explicit columns that identify observations and a checked output shape.
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 index; 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 index 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 index values, 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.
Construct a frame from named columns
An empty DataFrame can acquire named columns. A dictionary supplies the same columns in one call; each list must have the same length.
1. Start with two row positions
2. Add one column at a time
3. Build the same table from a dictionary
Both methods give rows 0, 1 and columns ticker, close. .equals(...) returns True when the tables match.
A dictionary of Series aligns their indexes, so unmatched index values introduce missing cells. Choose the construction that matches the source contract.
Choose and restore the row index
One row represents one illustrative security. set_index("ticker") makes its identifier the row index; verify_integrity=True rejects repeated index values.
1. Move ticker into the index
2. Select by an index value
.index.name reports the index name; .loc selects an index value and a column.
3. Restore ticker as a column
reset_index() preserves the identifiers. The restored table matches the original (True).
An index alone does not prove uniqueness. reset_index(drop=True) discards the identifiers and is appropriate only when they are expendable row positions.
Two axes, one decision: sum down or sum across?
For reductions, axis=0 collapses rows and retains column names; axis=1 collapses columns and retains index values. State the result’s length and units before running.
The full five-utility row sums are 38 552 and 37 376 MW in the first two hours; the first row’s IQR is 7 810 MW. Column sums are sums of recorded MW readings. Energy accounting also needs observation intervals and complete coverage.
Ordering, column updates and export
Question: How does the table change?
Result: A checked table update and a CSV round-trip that preserves identifiers.
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. Compare the index before and after sorting. 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 index value and the columns are reordered.
With axis=1, by= names a index value. The first row orders the columns as DAYTON, DEOK, DOM, COMED, AEP; descending column-name order gives DOM, DEOK, DAYTON, COMED, AEP.
Making changes: add, derive and drop
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. Inspect drop carefully: 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.
After drop, df2 has five columns and df still has seven. Keep the returned table when you intend to use the change.
Rename columns and replace values
rename(columns=...) changes column names; replace(...) changes matching values in a column. Both return a new result. assign is introduced with the complete pipeline in §2.4.1.
Export identifiers explicitly and check the CSV round trip
CSV stores text and values, without pandas dtype metadata. Save identifiers once; explicitly restore dates and dtypes when loading.
Keep ticker and date as columns and save with index=False. When reading the file, parse the date column explicitly; compare the restored table and its data types.
The file is saved in the current Python working directory. Reset a meaningful index first if its values must be saved as columns.
Alignment and table summaries
Question: Which index values, column names and axis does the calculation use?
Result: A labelled result with the aggregation axis explained.
Index alignment: match index values and column names
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 index values and the union of column names, 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 index values overlap in exactly one row (02:00) and three columns. Compare both printed inputs with their sum and identify the index values or column names responsible for each missing cell.
Four rows in the union, four columns in the union — how many cells carry a number?
A NaN introduced by arithmetic can indicate unmatched dates, timezones or index levels even when both source tables contain observed values. Inspect the index and column names before treating it as a raw-data gap.
agg and apply: several statistics, or your own function
With apply(..., axis=1), each row is a Series whose index is the DataFrame’s column names. Select its entries with row.loc["X"] and row.loc["Noise"].
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 full 100-row synthetic frame has means 49.5 for X, −2.24 for Noise and 47.36 for Y. The row ranges from agg and apply agree; the first full five-utility row spans 11 545 MW.
Prefer vectorised arithmetic when a built-in expression exists. apply runs the supplied function on each row or column; measure its cost on the intended data and runtime.
For every DataFrame operation, compare the input and output shapes. 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 violates the intended row or column contract, use the intermediate tables to locate the cause.
8 760 — one combined hourly load in MW per row. axis=1 collapses columns. axis=0 returns five sums of recorded MW readings; energy totals also require observation intervals and coverage checks.
4 × 4 — the union of index values by the union of column names — 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.
Data Quality: Audit, Repair and 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, checking each row count against the displayed table.
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.
| Part | Question |
|---|---|
| 2.2.1 Missingness and duplicate observations | What is missing, repeated or ambiguous? |
| 2.2.2 Rows, columns and time subsets | Which observations belong in the analysis? |
Missingness and duplicate observations
Question: What is missing, repeated or ambiguous?
Result: A defect report before any rows or values are changed.
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.
FE’s observed records start on 1 June 2011. Coverage patterns locate a defect; they do not establish its cause or justify a particular replacement.
Fill forward, fill backward, fill a constant
Inspect the missing 04:00 AEP observation on 6 December 2012. Compare a past value, a later value and a constant; distinguish availability from suitability.
A fill is a stated assumption. ffill uses earlier information; its suitability depends on the variable and gap. Statistical fills must be fitted on training data. Zero is an observed demand value only if the source confirms it.
Drop surgically, or fill and pay the price
dropna() removes any row with any NaN. Compare the retained tables and count their rows. The printed result shows why: 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.
A small missing fraction does not by itself justify median or mode filling. Fit statistical replacements on training rows, retain missingness flags and measure downstream effects. For FE’s long initial gap and NI’s near-total absence, define the required coverage before deciding what to retain.
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.
The four repeated local index values are 2 November 2014, 1 November 2015, 6 November 2016 and 5 November 2017, each at 02:00 in the archived file. In 2014 the AEP loads are 12 994 and 13 190 MW. These are different observations sharing a label. keep=False marks both for inspection; timezone and source clock conventions are needed to determine their identity.
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. Compare the printed rows retained by each policy.
hours has 4 duplicated timestamps (4 pairs). How many rows does each keep= policy leave?
A retention policy changes observation weights and coverage. Resolve whether the rows are separate hours, ingestion repeats or revisions before modelling. Document why the retained identity is appropriate for the task.
Rows, columns and time subsets
Question: Which observations belong in the analysis?
Result: An explicit subset with identities and chronological order preserved.
Masks, query and isin
A Boolean mask inside .loc[...] 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().
On the complete 2014–2017 source, the band has 4 359 hours and the outside-range screen 5 903. query matches the band exactly; @hot reuses an existing mask, finding 1 105 hours with AEP > 20 000 and COMED > 12 000. Weekend and weekday means are 13 795 and 15 240 MW across 10 032 and 25 031 rows. This observed difference does not by itself identify a cause; Section 2.3 computes it with groupby. ~ negates a mask.
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 index value 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 index 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 index value returns a Series indexed 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.
In a prediction task, bfill can copy information unavailable at the earlier decision time. ffill uses past observations. Fit statistical replacements on training data; zero or a carried value still needs a justified meaning.
The archived file has different loads sharing a 02:00 local label on fall-back dates. Standard US fall-back changes 02:00 to 01:00; the source’s timestamp convention and timezone need checking to explain its 02:00 labels. Resolve whether these are separate hours before choosing a retention or aggregation policy.
Grouped Tables, Joins and Reshaping
Group, join and reshape while tracking which columns identify each observation. Inspect rows, columns and index values and column names on hourly load, university rankings, retail inventory and the stock panel.
| Part | Question |
|---|---|
| 2.3.1 Grouped aggregation, transform and filter | Should the result have one row per group or per observation? |
| 2.3.2 Joins on multiple columns and validation | Which rows survive the join, and can repeated matches multiply rows? |
| 2.3.3 MultiIndex and long/wide representations | How should the same observations be arranged? |
Grouped aggregation, transform and filter
Question: Should the result have one row per group or per observation?
Result: A grouped result with its output shape checked.
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 name 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 column name 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 retains 17 544 hourly rows. The final Sunday hour is 1.74 standard deviations above the overall mean and 2.40 above the Sunday mean. This full-sample weekday standardisation describes the weekly mean/scale pattern. For prediction, estimate those statistics on training data only. apply computes weekday IQRs: Tuesday 2 940 MW, Friday 3 290 MW, matching agg(..., iq).
Weekly resampling groups clock time, not every seventh row
groupby(level=0).mean() groups repeated values of the first row-index level. Here we explicitly average repeated timestamps for a descriptive weekly report. Then choose the weekly statistic and inspect coverage. A mean load has units MW; energy over hourly readings has units MWh.
For this descriptive chart we explicitly average the two readings sharing a local timestamp. That removes the repeated index value but is not a substitute for timezone-aware accounting: retain UTC or an offset when every physical hour matters. A count reports coverage, and a partial boundary week must not be compared with a full week as a total. resample("W-SUN") supplies calendar buckets; .iloc[::168] supplies a position-based sample whose dates drift when rows are missing.
The first Sunday bucket has only 24 valid hours; the next has 168. Taking every 168th row keeps one observation per step and does not compute weekly mean load.
Joins on multiple columns and validation
Question: Which rows survive the join, and can repeated matches multiply rows?
Result: A validated join with unmatched rows accounted for.
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.
agg(["mean", "size"]) reports a pooled research-score mean and its university-year record count. Restricting to countries with at least 20 records gives Netherlands 51.9 (75), Hong Kong 46.8 (34) and United States 45.2 (659). These are records across 2011–2016, not distinct institutions or a matched cohort; sample composition affects comparisons.
University names are unique within each year. The joins return inner 393, left 401 and outer 401 + 800 − 393 = 808 rows. Eight 2015 universities lack a 2016 match: research_16 is missing and 401 − 393 = 8. suffixes distinguishes the two years’ columns. Print counts and matching status after every merge.
One research row means one security and one date
Load nine stocks from the public monthly panel: three each in Technology, Financial Services and Energy. Use the ticker and date columns together to identify each row. The stock selection and sector classifications are fixed for this teaching sample.
The sample contains 639 stock/month rows: nine stocks and 71 dates. It is a current-survivor teaching sample with unverified historical sector/availability metadata; use it to practise table operations and feature calculations.
Join on both stock and date and inspect unmatched rows
Joining only on ticker would pair observations from different months. Here the reference table deliberately omits one security-month so the coverage check has something to find.
Before running: A left join loses one reference match. How many candidate rows should remain?
The join on ticker and date retains 639 candidates: 638 matched rows and one unmatched row. Require the intended cardinality and an explicit unmatched-record policy.
Combine tables: matching columns or concatenating axes
merge matches rows using the chosen columns and combines their remaining columns. DataFrame.join normally matches indexes. pd.concat joins whole objects along the requested axis.
| Operation | What it does | Important keywords |
|---|---|---|
merge |
Match rows using the chosen columns | on, how, validate |
join |
Match row indexes by default | how, validate |
concat(axis=0) |
Append rows; align column names | axis, ignore_index |
concat(axis=1) |
Add columns; align row indexes | axis, join, keys |
concat uses join="outer" by default; join="inner" keeps only the common index values for axis=1. ignore_index=True replaces the concatenation-axis index, so retain meaningful identifiers elsewhere first.
pd.concat: side by side, or on top
pd.concat appends rows with axis=0 or adds columns with axis=1. The old DataFrame.append() method has been removed. Collect frames and concatenate once; use ignore_index=True only when the original index values can be discarded.
pd.concat glues frames along an axis. With axis=1 it performs an outer join on the index values: 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 repeated index values. The pair-and-correlate pattern at the end is what every factor analysis begins with.
The full side-by-side table has 780 dates and 528 missing FB values outside 2016. Vertical concatenation produces 293 rows with 22 repeated dates because December 2015 overlaps both slices. The exercise uses MSFT and FB on their common 2016 dates; correlate daily percentage changes, not price levels. fill_method=None avoids filling missing prices before computing a return.
Stop a join when a stock/date pair is repeated
Allowing repeated stock/date pairs in both tables would conceal this defect. Duplicate one reference row, then ask pandas to require at most one matching row on each side.
many_to_one requires unique stock/date pairs in the reference table; one_to_one requires both sides unique. Check missing values in the matching columns: pandas can match a missing value to another missing value.
MultiIndex and long/wide representations
Question: How should the same observations be arranged?
Result: A reshaped table that retains the stock and date for every observation.
A MultiIndex identifies each row using two index levels
AAPL appears on several dates; a date appears for several stocks. The (ticker, date) pair identifies a row. Two index levels belong to the same row axis; they are not the row and column axes.
| row index | ticker | date | ret_1_0 |
|---|---|---|---|
| 0 | AAPL | 2020-01-31 | 0.054010 |
| 1 | AAPL | 2020-02-29 | -0.114701 |
| 2 | MSFT | 2020-01-31 | 0.079455 |
| 3 | MSFT | 2020-02-29 | -0.045688 |
| ticker | date | ret_1_0 |
|---|---|---|
| AAPL | 2020-01-31 | 0.054010 |
| AAPL | 2020-02-29 | -0.114701 |
| MSFT | 2020-01-31 | 0.079455 |
| MSFT | 2020-02-29 | -0.045688 |
Teal cells form the row index. Each row still has one return. A MultiIndex can have more than two levels.
Six rows for the index examples
Use two stocks and three month-end dates from the validated research table. Keep the complete small input visible; the reverse date order will make the sorting step clear.
Set two columns as the index
set_index(["ticker", "date"]) uses the columns in that order: outer level ticker, inner level date. verify_integrity=True rejects duplicate complete pairs. By default, the selected columns leave the data columns.
index.names names the levels; index.nlevels is 2; index.is_unique checks the complete pair, not each level separately.
Sort a MultiIndex before selecting a range
sort_index() orders rows by the outer level first, then the inner level within it. It moves complete rows, so returns stay attached to their stock and date. sort_values() orders by data-column values instead.
Sort all levels before the date-range examples below. Selection using an exact index value does not itself require a sorted index.
Move one index level into a column
reset_index(level="date") moves only the named level into an ordinary column. The ticker level remains the row index. Here, level means which component of the MultiIndex, not a row number.
Use the name "date" rather than its position 1 when possible. A repeated ticker index is expected after this partial reset.
Add a column to the existing index
set_index("date", append=True) adds the date column as another index level. append=True retains the existing ticker index; the default append=False would replace that index.
The result matches panel (True). This append is a keyword of set_index, not the removed DataFrame method.
Restore all index levels as columns
reset_index() without level moves every row-index level into columns and creates a default integer index. This is useful before a column-based merge or export.
The default drop=False preserves the identifiers as columns. drop=True discards them instead; use it only when those identifiers may be lost.
Select one stock using the outer index level
panel.loc[rows, columns] still has two axes. With ticker first, "AAPL" selects all its dates. A single ticker removes that selected level from the result; a list retains the MultiIndex.
Both outputs are DataFrames with the same three returns. Compare their index names and structure.
Select one date using a named index level
xs means cross-section. level="date" selects the date level even though it is not first. drop_level=False keeps the selected date level in the result.
Both select AAPL and MSFT on 29 February. drop_level changes the returned index structure, not which observations are selected.
Select one index pair or one cell
A tuple gives both index values in level order: ("AAPL", "2020-02-29"). The comma outside that tuple separates row selection from column selection.
Left: a row returned as a Series. Right: a single number. loc selects by index values; iloc continues to select by integer positions.
What pd.IndexSlice means
pd.IndexSlice lets you combine lists, exact values and ranges inside a MultiIndex row selector. The slots follow the current index-level order.
First slot → ticker
Second slot → date: → every value of that level
["ret_1_0"] keeps a DataFrame.
The outer comma separates rows and columns.
idx = pd.IndexSlice is a convenience alias for writing slices across index levels. It does not create an index or change the table.
Use IndexSlice for a range or all stocks
Here idx = pd.IndexSlice makes a short name for the helper. The first example selects two stocks over a date range; the second uses : for every stock on one date. loc ranges include both endpoints.
These selectors retain both row-index levels. They select existing observations; they do not add missing stock/date combinations.
Swap index levels, then sort the new order
swaplevel("ticker", "date") exchanges the two levels. It does not sort the rows. Sorting afterwards puts dates first and tickers together within each date.
Choose ticker-first for a stock history, or date-first for a cross-section. The same six observations and returns remain.
Apply the index operations to the full research panel
The small example used six observations to expose the structure. Apply the same two-level index to all 639 validated stock/month rows. Selecting a stock or a range changes the sample, not the stored panel.
Reshape the return panel without aggregating repeated stock/date pairs
unstack(level="ticker") moves the ticker level from the row index to columns. Date stays in the row index. The values are rearranged, not calculated again.
| ticker | date | ret_1_0 |
|---|---|---|
| AAPL | 2020-01-31 | 0.054010 |
| AAPL | 2020-02-29 | -0.114701 |
| MSFT | 2020-01-31 | 0.079455 |
| MSFT | 2020-02-29 | -0.045688 |
| date / ticker | AAPL | MSFT |
|---|---|---|
| 2020-01-31 | 0.054010 | 0.079455 |
| 2020-02-29 | -0.114701 | -0.045688 |
Each return moves once. No averaging. level="ticker" names the level to move; an omitted level means the last level.
Run unstack and compare the Series with the table
Select the return column as a Series, then move its ticker level to columns. Print the input Series directly so its index, name and dtype remain visible.
Four indexed values become a 2 × 2 table. Repeated complete index pairs must be resolved before unstack; it cannot choose between two values for one cell.
Stack columns into an inner index level
stack() moves the column axis into the innermost row-index level. This frame has one column level, named ticker; it becomes the second row-index level.
| date / ticker | AAPL | MSFT |
|---|---|---|
| 2020-01-31 | 0.054010 | 0.079455 |
| 2020-02-29 | -0.114701 | -0.045688 |
| date | ticker | ret_1_0 |
|---|---|---|
| 2020-01-31 | AAPL | 0.054010 |
| 2020-01-31 | MSFT | 0.079455 |
| 2020-02-29 | AAPL | -0.114701 |
| 2020-02-29 | MSFT | -0.045688 |
The new level is appended after the existing date index. Compare date / ticker here with the original ticker / date.
Missing combinations after unstack
A wide table contains a cell for each row/column combination. If a pair was absent, unstack places NaN there. This example deliberately removes the last of the four observed returns.
| ticker | date | ret_1_0 |
|---|---|---|
| AAPL | 2020-01-31 | 0.054010 |
| AAPL | 2020-02-29 | -0.114701 |
| MSFT | 2020-01-31 | 0.079455 |
| date / ticker | AAPL | MSFT |
|---|---|---|
| 2020-01-31 | 0.054010 | 0.079455 |
| 2020-02-29 | -0.114701 | NaN |
NaN means no observed return for that pair. Replacing it with zero would assert a zero return without evidence.
Here dropna() explicitly keeps only non-missing observations after stacking. It would also remove originally recorded missing returns, so it is not a universal round-trip policy.
Transpose swaps the entire row and column axes
.T exchanges the two axes of a DataFrame. It does not move just one MultiIndex level and does not aggregate. Compare this with unstack("ticker") and stack().
The result still has four cells. Its row index is now ticker and its columns are dates.
Verify the full long–wide–long round trip
After stacking, swaplevel restores the original level order; sort_index restores row order. Check the data values and both index levels.
The complete sample is 71 dates × 9 stocks; both checks return True. With gaps, state the missing-combination policy before claiming that a round trip preserves the source.
melt, pivot and pivot_table: the structural difference
pivot rearranges unique pairs. pivot_table can aggregate repeated pairs. melt turns headers into ordinary columns containing names and values.
| ticker | date | ret_1_0 |
|---|---|---|
| AAPL | 2020-01-31 | 0.054010 |
| AAPL | 2020-02-29 | -0.114701 |
| MSFT | 2020-01-31 | 0.079455 |
| MSFT | 2020-02-29 | -0.045688 |
| date / ticker | AAPL | MSFT |
|---|---|---|
| 2020-01-31 | 0.054010 | 0.079455 |
| 2020-02-29 | -0.114701 | -0.045688 |
pivot(index="date", columns="ticker", values="ret_1_0") goes right.melt(id_vars="date", value_vars=["AAPL", "MSFT"], var_name="ticker", value_name="ret_1_0") goes left after restoring date as a column.
pivot: choose the row index, column headings and values
index="date" supplies the output row index; columns="ticker" supplies the headers; values="ret_1_0" supplies the cell contents. pivot does not aggregate; duplicate complete pairs raise an error.
The values and axes match the earlier unstack("ticker") result (True). The input representation differs: columns here, index levels there.
melt: retain identifiers and turn value columns into rows
id_vars keeps identifier columns; value_vars chooses which columns to lengthen. var_name names the column holding former headers; value_name names the column holding their values.
Two dates × two selected value columns become four rows. The output has ordinary columns and a default row index; melt does not itself create a MultiIndex.
pivot_table: two grouping columns become rows × columns
pivot_table(..., aggfunc="mean") combines scores for each country/year pair.
| country | year | research |
|---|---|---|
| HK | 2012 | 58.6 |
| HK | 2012 | 80.1 |
| HK | 2013 | 64.6 |
| HK | 2013 | 85.9 |
| UK | 2012 | 94.2 |
| UK | 2012 | 96.6 |
| UK | 2013 | 95.6 |
| UK | 2013 | 98.1 |
| country / year | 2012 | 2013 |
|---|---|---|
| HK | 69.35 | 75.25 |
| UK | 95.4 | 96.85 |
HK / 2012: (58.6 + 80.1) / 2 = 69.35.index="country" → rows; columns="year" → headers; values="research" → scores; aggfunc="mean" → how scores are combined.
Run pivot_table with explicit keyword arguments
The small sample uses HKU, HKUST, Oxford and Cambridge in 2012–2013. aggfunc defaults to "mean", but writing it explicitly states what each cell represents.
pivot would reject these repeated pairs. Aggregation loses individual university scores; melt cannot recover them. fill_value replaces missing output cells after aggregation; leave them visible unless a replacement is justified.
The same table by groupby + unstack
Two routes produce the same country/year means, shown in the final table.
Direct route: pivot_table(..., aggfunc="mean") → the same table on the right.
| country | year | research |
|---|---|---|
| HK | 2012 | 58.6 |
| HK | 2012 | 80.1 |
| HK | 2013 | 64.6 |
| HK | 2013 | 85.9 |
| UK | 2012 | 94.2 |
| UK | 2012 | 96.6 |
| UK | 2013 | 95.6 |
| UK | 2013 | 98.1 |
| country | year | mean research |
|---|---|---|
| HK | 2012 | 69.35 |
| HK | 2013 | 75.25 |
| UK | 2012 | 95.4 |
| UK | 2013 | 96.85 |
| country / year | 2012 | 2013 |
|---|---|---|
| HK | 69.35 | 75.25 |
| UK | 95.4 | 96.85 |
mean() calculates four values. unstack(level="year") only rearranges them. Both routes give the same four means.
Inspect the grouped Series before unstacking it
Read the intermediate result before reshaping it. level="year" selects a named index level; it does not mean a DataFrame column or a year value such as 2012.
The numerical check returns True. Use the same aggregation, grouping columns and missing-value rules before treating the two routes as equivalent.
Apply the comparison to four countries and six years
The full selected university sample produces 24 country/year means. Keep record counts beside the means: changing sample composition can change a country average.
melt and pivot on the five-stock price panel
Apply the same mappings to stockdata2.csv: one row per date/stock pair. pivot gives 2 305 dates × 5 stock columns; melt restores 11 525 rows. These are recorded price levels, not returns.
Inventory prices by release year: pivot_table and crosstab
pd.crosstab(index=..., columns=...) counts category combinations by default. To summarise a numeric column, also pass values=... and aggfunc="mean". Here it uses the same row groups, column groups and mean as pivot_table.
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 grouping columns → MultiIndex Series; unstack(level) moves the named level into the columns (2 591 new-release SKUs unsold, 1 556 sold); stack() moves column levels into the index; .T swaps axes. These operations do not aggregate. With missing combinations, specify the missing-value policy and verify stock/date columns, ordering and values in a round trip.
Inventory counts: unstack either named level
pd.crosstab counts combinations when no values or aggfunc is supplied. A grouped count is a MultiIndex Series; choose explicitly which level to move to columns.
Inventory prices: stack flag columns into the index
stack() moves the flag columns into an inner index level after release year. Each value is still a previously computed mean price; stacking does not calculate another mean.
Inventory prices: transpose the year and flag axes
.T exchanges the complete row and column axes. Flags become the row index; release years become the column headers. The mean prices do not change.
pd.cut: assign values to intervals
pd.cut assigns numeric values to intervals. bins gives the interval boundaries and labels gives one name per interval. With the default right=True, intervals exclude their left boundary and include their right boundary: \((150, 300]\) includes 300.
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. 300 belongs to mid, whose interval is \((150, 300]\). Values outside the supplied intervals and missing inputs receive a missing category. In the inventory example, the resulting price-tier Series becomes the row grouping for crosstab.
Plotting a frame
A DataFrame’s .plot() draws one line per column against the index. Dividing each price column by its first value produces a rebased price comparison; portfolio wealth also requires the appropriate dividend, adjustment and cost conventions.
Dividing by wide.iloc[0] broadcasts one row across all 2 305 — index alignment on the column names — 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; rank-number/citation-score correlation is −0.50. The top-ten citation scores include 91.1, and ranks 60–100 span 46.6–99.2: the scatter shows considerable variation at similar ranks. This is an association within the selected top-100 sample, not a causal relation. Chapter 3 §3.4 develops its interpretation.
Plotly Express (px) creates interactive figures; §2.5 explains its interfaces.
Previously introduced setup · 14 lines
Copy complete code includes this earlier setup and the visible code below. Run it in Colab or local Python.
Introduced earlier: groupby: one number per group; A second frame, and the three kinds of join; melt and pivot: long ↔ wide; Plotting a frame
# Previously introduced: groupby: one number per group
import numpy as np, pandas as pd
url = "https://statpython-book.pages.dev/data/"
# Previously introduced: A second frame, and the three kinds of join
uni = pd.read_csv(url + "university.csv")
uni["total_score"] = pd.to_numeric(uni["total_score"], errors="coerce")
# Previously introduced: melt and pivot: long ↔ wide
long = pd.read_csv(url + "stockdata2.csv", usecols=["Date", "stock", "value"])
wide = long.pivot(index="Date", columns="stock", values="value")
wide.index = pd.to_datetime(wide.index)
# Previously introduced: Plotting a frame
uni["world_rank"] = pd.to_numeric(uni["world_rank"].astype(str).str.replace("=", ""), errors="coerce")
top100 = uni[(uni.year == 2016) & (uni.world_rank <= 100)]
# Table display: retain every column and keep rows on one line.
import pandas as pd
pd.set_option('display.max_columns', None)
pd.set_option('display.max_colwidth', None)
pd.set_option('display.width', None)
pd.set_option('display.expand_frame_repr', False)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()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.
Feature Pipelines and Research Applications
Compose table-returning methods into readable pipelines. Validate intermediate stock/date columns, shapes and timing; use an explicit .loc write when the original table must change. Then organise stock/month rows and build checked, grouped features.
| Part | Question |
|---|---|
| 2.4.1 Readable chains and safe updates | Can another analyst follow every transformation? |
| 2.4.2 Organise a monthly stock table | How do we keep each stock’s rows in order? |
| 2.4.3 Feature definitions, neutralisation and validation | How is this feature defined and checked? |
Parts 2.4.1–2.4.3 build a readable feature pipeline: organise the observations, calculate grouped measurements and check the result.
Readable chains and safe updates
Question: How do daily prices become a checked feature table?
Result: Build the table step by step, then express the same steps as a short pipeline.
The task: prepare a daily movement report
A market-risk team receives Microsoft prices. It needs a daily return and a flag for moves larger than 3% in either direction, followed by a monthly count.
Date index
price
252 stock-day rows
price · ret · large_move
251 observed-return rows
observations · mean_return
large_moves
12 month rows
A feature is a derived measurement attached to an observation. A pipeline is an ordered sequence of transformations. Here, each daily row describes one stock on one date.
Start with the price table
Read the supplied prices, retain adjusted close and select 2015. Rename the column price so the calculation is easy to read. The date is the index; the single column contains prices in USD per share.
There are 252 rows and one column. Adjusted close accounts for the adjustments in this supplied historical file; this exercise does not establish when a vendor published each adjusted value.
First calculate the return, then inspect it
pct_change(fill_method=None) computes \(p_t/p_{t-1}-1\) without filling missing prices. The result is a decimal: 0.03 means 3%. .copy() creates a separate working table.
The first return is NaN: the selected table has no preceding price. This is the return ending on the row’s date, available after that close; it is not the following day’s return.
Flag only rows with an observed return
dropna(subset=["ret"]) retains rows with an observed return. subset names the columns checked for missing values.
1. Retain observed returns
2. Add the flag
abs() measures magnitude in either direction. Column arithmetic handles all rows without a Python loop. A missing return is excluded from this report, never treated as zero.
assign returns a new DataFrame
table.assign(new_column=values) returns a DataFrame with the added column. Save that return value to keep it. The original table retains its columns.
Create a new table
Inspect the original
assigned has price and ret; prices still has only price.
lambda names the table received by assign
A lambda is a short function written as lambda parameter: expression. In assign, pandas supplies the current DataFrame as that parameter.
| Part | Meaning in this example |
|---|---|
table |
A local parameter name for the DataFrame received by the function |
: |
Separates the parameter from the returned expression |
table["price"].pct_change(...) |
Calculates the new column from that table |
The two versions give the same result. The lambda is useful when an earlier step changes the table that the next step receives.
Connect the familiar steps into a short chain
Read from top to bottom: calculate returns → retain observed returns → add the flag. Each method receives the preceding method’s result. The parentheses allow line breaks.
Both versions produce the same index, columns and values. Use named intermediate tables when a step needs investigation; use a short chain when its sequence is already clear.
Prepare a month column before grouping
The daily features are ready. Now create a separate monthly report. index.to_period("M") converts each date to its calendar month; it keeps one value per daily row.
The observation unit is still a stock-day. Several dates now share the same month value; no rows have been aggregated yet.
Aggregate daily rows into a monthly report
groupby("month") collects the rows of each month. In named agg, each output is written as new_name=("input_column", "operation").
The output has 12 monthly rows. count counts observed returns; Boolean sum counts True flags. mean_return is the average daily return, not the compounded monthly return. January has 19 observed returns because the first selected day’s return is unavailable.
Give a repeated report step a function
A function gives a repeated transformation a name. Its first parameter is the input DataFrame; it returns the transformed table. nlargest(n, column) selects the n rows with the largest values of that column.
The function does not change the original monthly table. Tied counts follow nlargest’s default first-occurrence rule.
pipe passes the table to a named function
table.pipe(function, argument) calls function(table, argument): the table is supplied as the first argument. The function determines what is returned.
Here pipe is another way to call the function just taught. It is useful for inserting a named calculation into a longer sequence; ordinary function calls remain clear for a standalone step.
A separate subset has its own columns
Creating an independent subset and editing it does not add a column to its source table. .copy() makes the intended separation explicit.
Edit the separate table
Inspect the source table
The source still has no review column. Writing to the subset is appropriate when that separate table is the intended output.
Use one loc write to update the intended table
For frame.loc[rows, column] = value, the Boolean condition chooses rows and the column name identifies the destination. Both refer directly to frame.
There are 16 check rows and 235 normal rows. Avoid chained assignment such as frame.loc[rows]["review"] = value: under copy-on-write it cannot update the original. Warning behaviour depends on pandas settings and version.
Your turn: change the reporting threshold
The team now wants to review moves larger than 2%. Keep the same 251 observed returns and print the revised table.
Change the threshold in the supplied code from 3% to 2%. Do not change the price input or replace missing returns with zero.
Explain why the flag count changes while the number of daily observations stays the same.
Organise a monthly stock table
Question: How do we organise each stock’s rows before calculating historical values?
Result: A sorted monthly table, checked stock/month rows and grouped historical values.
The task: build a monthly research panel
The team compares nine stocks at each month-end. Each row represents one stock and one month.
Use the ticker and date columns together to identify each row. A stock can appear on several dates, and a date can contain several stocks.
This teaching file contains one row per month for every selected stock. The stock selection and sector classifications are fixed.
| Column | Meaning |
|---|---|
| ticker | Which stock? |
| date | Which month-end? |
| Column | Role |
|---|---|
| ret_1_0 | Return ending this month |
| Column | Role |
|---|---|
| momentum_3m | Compound three earlier months |
Read the source before selecting the research sample
parse_dates=["date"] creates a datetime column. Inspect the source before filtering. Returns in this file use decimals: 0.05 means 5%.
Select the sample and put each stock in time order
isin retains the named stocks. The date condition retains 2019 onwards. Sort by stock and then date before within-stock time calculations; reset_index(drop=True) supplies a simple unique row index.
The sample contains 639 rows: 9 stocks × 71 months. Ticker is sufficient for this fixed classroom file; it is not a permanent real-world security identifier.
Check stock/date columns before adding any feature
duplicated(id_columns) marks repeated stock/month pairs. Check that the ticker and date columns have no missing values and that each pair occurs once. assert stops execution when its condition is false.
An empty problem table is the expected result here. Investigate a failed check before merging, rolling or fitting.
Grouped shift cannot borrow the previous stock’s row
groupby("ticker")["ret_1_0"].shift(1) shifts separately inside each stock. The first row of every stock becomes missing. An ordinary whole-column shift could copy the previous stock’s last return.
| Stock / month | Return |
|---|---|
| A / Jan | rA1 |
| A / Feb | rA2 |
| B / Jan | rB1 |
| B / Feb | rB2 |
| Stock / month | Lag |
|---|---|
| A / Jan | NaN |
| A / Feb | rA1 |
| B / Jan | NaN |
| B / Feb | rB1 |
Inspect the first lag for each stock
Read the printed rows in date order: February takes January’s return; January has no earlier row in this sample. Compare the starts of AAPL and MSFT.
There are nine missing first lags: one for each stock.
A grouped rolling result has an extra index level
rolling(3, min_periods=3).mean() uses the current and previous two rows within a stock and requires three observed values. The result is a Series indexed by ticker and original row index.
The extra ticker level records group membership. Do not assign these two-level index values directly to a table with a one-level row index.
Restore row indexes, then shift the completed window
droplevel(0) removes only the added ticker level. reindex(research.index) puts the values in the original row order. Shift the completed mean by one within each stock to exclude the current month.
The first three rows per stock are warm-up rows: 27 missing shifted means. The fourth row uses the first three completed months.
Your turn: look back two rows within each stock
Use shift(2) to get the value two rows earlier within each stock. This teaching file has one row per month for every stock.
Change the supplied one-row shift to a two-row shift. Print the first four rows of each of the two stocks.
Feature definitions, neutralisation and validation
Question: What does a feature measure, and which rows and dates define it?
Result: Explicit feature definitions, intermediate calculations and dated validation checks.
Define the feature before choosing the pandas method
A feature name alone does not specify its calculation. Record the columns that identify an observation, formula, input, units, window endpoint, availability and missing-value policy.
| Feature | Definition used in this section |
|---|---|
| Lagged return | Previous completed month’s decimal return |
| Three-month momentum | Compound the previous three months, excluding the current month |
| Three-month volatility | Sample standard deviation of those months’ log returns |
| Sector-relative momentum | Subtract the same-date sector mean |
Use the same feature_lab table and momentum_3m column throughout the following comparisons.
Put the feature’s time window on the page
At the end of April, the three-month example uses January, February and March. April’s realised return is excluded by our definition; May’s outcome is future information.
Different strategies may include the current completed month. Either choice must be declared before calculation.
| Month | Use? |
|---|---|
| January | Yes |
| February | Yes |
| March | Yes |
| Month | Role |
|---|---|
| April | Decision; skip current month |
| May | Future target; never an input |
Compounded return is not a sum of returns
For simple returns, momentum is \(\prod_j(1+r_j)-1\). Two moves of +10% and −10% leave \(1.1\times0.9-1=-0.01\), a 1% loss.
np.log(x) means \(\ln(x)\); np.log1p(r) means \(\ln(1+r)\). A 5% return needs \(\ln(1.05)\). Sum the log returns, then use np.expm1(x) — meaning \(e^x-1\) — to recover the compound return. Require \(r>-1\).
Shift first so the window contains only earlier months
Use the same within-stock lag already taught. Keeping the shifted log return as a column makes the window inputs visible.
The missing first value is expected. Do not fill it with a return from another stock or an invented zero.
Calculate and inspect three-month momentum
Sum three shifted log returns within each stock, restore the original row index, then convert back to a simple cumulative return. min_periods=3 refuses an incomplete window.
The fourth monthly row is the first valid three-month value for each stock. Across nine stocks, 612 rows are valid and 27 are warm-up rows.
Volatility measures dispersion, not direction
The sample standard deviation is \(s=\sqrt{\sum_j(x_j-\bar x)^2/(n-1)}\). Here \(x_j\) is a monthly log return. ddof=1 uses the sample denominator; min_periods=3 requires three observations.
Both use the same past-only window. Momentum records compounded direction; volatility records dispersion. Annualising a monthly standard deviation by \(\sqrt{12}\) assumes uncorrelated-return scaling.
Sector demeaning compares stocks with their current peers
Stocks in the same sector can share a broad movement. Subtract the same-date sector mean to measure each stock relative to its peers: \(x_{i,t}-\bar x_{s,t}\).
The diagram uses an illustrative three-stock sector. The code on the next slide uses the actual research table.
| Stock | x |
|---|---|
| A | 0.1 |
| B | 0.2 |
| C | 0.3 |
| Stock | x − mean |
|---|---|
| A | −0.1 |
| B | 0 |
| C | 0.1 |
| Stock | z-score |
|---|---|
| A | −1 |
| B | 0 |
| C | 1 |
transform returns one peer mean for each original row
groupby(["date", "sector"]) creates the peer groups. transform("mean") repeats each group mean along its original rows, so subtraction aligns with the feature Series.
Use the first month with a complete historical window to inspect the comparison. Subtract each same-month sector mean from the three-month momentum just calculated.
A z-score also rescales by peer dispersion
A sector z-score is \((x-\bar x_s)/s_s\). transform("std") supplies the peer sample standard deviation. Replace zero dispersion with missing before division; a one-stock group also has no sample standard deviation.
Each sector in this fixed sample has three stocks. Never replace an undefined peer score with a neutral zero without an explicit policy.
A cross-sectional rank answers a different question
groupby("date").rank(pct=True) orders stocks against all stocks at that date. It does not subtract sector effects. With tied values, pandas uses average ranks by default.
A rank describes position within the same-month comparison. Larger momentum receives a larger percentile rank; ties receive their average rank.
Package the past-only momentum calculation for a time check
A causality check reruns exactly the declared past-only formula. The function sorts the input, lags by stock, forms a three-row window and restores the caller’s row index.
Change future data and compare earlier features
Change returns strictly after a fixed cutoff. A correctly past-only feature at or before the cutoff must remain unchanged. assert_series_equal checks the values and pandas structure, including the index.
Matching earlier outputs is evidence about code timing. It does not verify a vendor’s historical release times or the original universe.
Report feature coverage by date
A single whole-sample percentage can hide a broken month. count() reports valid values per feature and date; a missing-value fraction reports the share unavailable.
The first three dates have zero ready three-month features; the fourth has nine. Inspect peer counts and unusually large or small values as well. A valid extreme observation is not automatically a data defect.
Feature readiness belongs to a stated operation
A missing feature needs an explanation. The first three months of a three-month historical window are unavailable because there is not enough earlier data; filling them with zero would change the meaning.
Retain the stock and date columns, report coverage by month and investigate unexpected gaps. Compare the input and output tables before using a derived column.
Interactive Visualisation
This section reconnects the original Topic 2 visualisation material to the table pipeline. Plotly Express maps DataFrame columns to visual attributes; Graph Objects gives explicit control over traces, layout and subplots. The full lab remains available even though the 3.5-hour class uses only the chart needed to communicate the group result.
Run and edit the Plotly examples directly in the Python boxes below. Plotly is prepared automatically; hover, zoom and use the animation controls on the displayed figures. You can also open the exported chart links. Download the interactive visualisation lab for the complete code sequence.
| Part | Question |
|---|---|
| 2.5.1 Plotly Express and column mappings | Which columns control the chart? |
| 2.5.2 Graph Objects, traces and subplots | Which trace belongs on which panel? |
Matplotlib and Plotly: compare the interfaces
The same observations and variables can be drawn through different interfaces.
| Compare | Matplotlib | Plotly |
|---|---|---|
| Draw | plt.scatter(x, y) or ax.scatter(x, y) |
px.scatter(df, x="a", y="b") |
| Figure structure | Figure → Axes → drawing objects | Figure → data traces + layout |
| Inspect | Static image here; interactive backends also exist | Browser hover, zoom and toolbar |
| Share | PNG / SVG / PDF | Interactive HTML or static image |
Use the same sample, units and axis ranges. The library does not change what the data mean.
The Matplotlib figure guide explains Figures and Axes; its backend guide distinguishes static and interactive displays. Plotly Express builds a Graph Objects Figure from named columns.
Use the same observations in both libraries
Keep the first three university observations from 2016 and their two score columns.
Read the three score pairs before plotting
One point represents one university. Both examples use exactly these rows and the same axis ranges.
Matplotlib draws the three score pairs
scatter draws paired Series; Axes sets labels and limits.
Static output here; Matplotlib also has interactive backends.
Plotly retains university names on hover
Hover reveals the university for the same three score pairs.
Express returns a Graph Objects Figure; its traces and layout remain editable.
Plotly Express and column mappings
Question: Which columns control the chart?
Result: An interactive comparison with stable identities, scales and labels.
Choose Express for column mappings and Graph Objects for control
Prepare chart columns before drawing
Reuse the original university ranking file. Parse numeric columns, keep explicit names and select a stated sample.
Chart libraries do not repair schema problems. The original world_rank column contains exact ranks, tied ranks such as =39, and bands. Remove only the tied-rank = marker before numeric conversion; errors="coerce" then leaves bands as missing rather than inventing a position. The international column also arrives as text. We retain the original rank text alongside the numeric rank and select the exact top 100 for 2016. A cross-sectional association in this selected group is descriptive; it cannot establish the effect of a policy on university performance.
One row describes one university in one year. Inspect the original column names and values before changing them.
Convert the rank and international columns
Remove the tied-rank = marker. errors="coerce" leaves rank bands missing instead of inventing an exact rank.
Select the 2016 top 100
Keep rows satisfying both conditions, then inspect the selected table and missingness.
After removing the tied-rank = marker, the sample contains 100 universities and no missing rank, citations or teaching values. Rank bands elsewhere in the source become missing numeric ranks rather than invented exact positions.
Express draws a histogram from a named column
Map one numeric column to the horizontal axis and name the sample in the title.
A Plotly histogram is still an aggregation. Its vertical axis is a count here, unlike the density histograms in Chapter 1. Check sample selection and missingness before reading the bars. Changing the requested bin count can change apparent peaks without changing a single input observation.
Hover reveals the displayed bins. nbins guides automatic binning; use explicit xbins through Graph Objects when exact shared edges matter.
Colour and hover labels retain the row meaning
Map country to colour and retain university identity in hover information.
Country groups are descriptive; selection does not establish a country effect.
Country colour gives groups; numeric colour gives a continuous scale.
Text labels and annotations serve different purposes
Label a small subset of observations. Add a separate annotation for the sample definition.
Labelling all 100 university names produces overlap. Show a deliberate subset or keep identities in hover text. Paper coordinates describe positions relative to the plotting region, whereas data coordinates follow axis values. A chart note belongs outside the data cloud so it does not appear to be another observation.
text labels data rows. An annotation can explain the chart in data coordinates or normalised paper coordinates.
Multiple y columns create comparable lines on one scale
Express accepts a list of value columns. Here both scores use the same 0–100 scale.
A line connects observations in input order; it does not sort time or rank for you. Wide-form y=[…] creates one trace per value column. It is appropriate here because teaching and research scores are comparable units. Overlaying a dollar price and a return on one scale would obscure interpretation; use separately labelled subplots when units differ.
Sort the horizontal coordinate before connecting points. This is a rank profile, not a time trend.
A third coordinate adds a variable and a viewing problem
Rotate the 3D view to inspect teaching, research and citations together.
The original Topic 2 includes scatter_3d. It is useful for exploration, but perspective can hide observations behind others. Preserve exact scores in hover information and compare the result with the two-dimensional scatterplot. An additional coordinate does not remove selection bias or establish a model.
Rotation changes apparent distances and overlap. Use a 2D comparison or facets when a reader must compare values precisely.
Facets repeat the same chart for comparable groups
Keep the three countries with the most observations in this sample and share axis ranges.
Choosing countries by counts is a display rule, not a finding. State that it excludes other countries. A facet displays each group separately while preserving the same x/y meaning. With free axes, similar-looking slopes or spread can correspond to different numeric ranges; fixed 0–100 ranges avoid that ambiguity here.
Animation needs stable identities and stable axes
Compare the top 20 in each year. University name identifies the same institution across frames.
animation_frame determines the sequence; animation_group preserves identity across those frames. Fixing both ranges prevents axis rescaling from masquerading as change. This sample is not a balanced panel: membership follows each year’s top-20 ranking. Do not interpret movement as the experience of a fixed cohort without defining and validating that cohort first.
Use Play or the year slider to compare six frames, 2011–2016. The downloadable lab uses the same data.
Graph Objects, traces and subplots
Question: Which trace belongs on which panel?
Result: An editable figure with explicit trace and layout choices.
Graph Objects makes the trace and layout explicit
Build one trace and one layout, then combine them in a Figure.
Graph Objects exposes the structure that Express created for you. A Figure can hold several traces with shared layout. Choose markers for separate observations, lines for an ordered path, or lines+markers when both are meaningful. The data values still come from the validated frame; explicit chart objects do not replace data engineering.
The trace supplies data and its drawing mode. The layout supplies the shared chart presentation.
A figure can also be described as a dictionary
The same structure uses data and layout entries. Use documented properties, then let go.Figure validate them.
A dictionary is an alternative representation, not a separate plotting engine. Property names and nesting must still match the Plotly schema. Using go.Figure on the specification validates the chart structure. Avoid constructing JSON by hand or converting numeric columns into string concatenations; let the library serialise supported arrays and values.
One trace survives and the title is Dictionary figure. Dictionaries are useful for serialisation; they follow the same figure schema.
Add a trace and update the existing figure
Construct a rank profile, add the second score and refine titles without rebuilding the data.
These update methods operate on figures from either interface. Use selectors when only some traces need a change; applying a line property to every trace is appropriate here because both are lines. For a reproducible chart, keep the selected sample, ordering and display choices in code rather than relying on manual editing of an exported image.
add_trace changes the plotted data series. update_layout changes shared presentation. update_traces changes trace properties.
make_subplots assigns traces to named grid positions
Place the teaching and research profiles in separate panels with a shared score range.
Different units need separately labelled scales.
Change a column mapping and rerun the chart
Change x_column from "teaching" to "research", then press Run Code. Compare the horizontal axis and hover information.
The same 100 university rows remain in the chart; changing the mapping changes which score is compared with citations.
API references
Consult the official Plotly Express guide and figure construction and updates when adapting the examples.
Chapter Wrap-up
The monthly feature example contains 639 stock/month rows. State the formula, calculate within each stock and check feature coverage by date. The first three dates have no ready three-month historical features; the fourth has nine. Explain those expected gaps before investigating unexpected missing values.
Check rows, columns, index and information availability after each load, selection, join or reshape. Resolve missingness and repeated local index values according to the intended observation. Reproduce the chapter’s concrete checks: 393 matched universities from 401 left and 800 right rows; 528 missing cells after unequal date coverage; 16/251 observed Microsoft daily returns exceeding ±3% in the 2015 example.
PHE’s October 2020 transfer failure omitted 15 841 positive tests from daily reporting and delayed contact tracing. PHE statement. The mechanism below is illustrative; it shows why source row identifiers and counts must be reconciled before publication.
Chapter 3 uses clean frames for sample variability (variance divisor \(n - 1\)), densities, tests, dependence and extreme-tail risk. Chapter 4 uses a design matrix \(X\), target \(y\) and evaluation that respects the information boundary.
Use the Slides to run and compare the same examples; use this Book for the assumptions behind their results.
Compare the input and result tables. Explain changes in their rows, columns, index and missing values using the operation you ran. Investigate any difference from the stated data contract before continuing.
← Chapter 1: Data Structures and Methods of Series · Contents · Chapter 3: Reshaping Statistics →