Section 2.3 · Chapter 2 · Learning Statistics with Python
DataFrames
Prof. Xuhu Wan
ISOM, HKUST Business School · 2026 Edition
| 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? |
Question: Should the result have one row per group or per observation?
Result: A grouped result with its output shape checked.
A selected sample can be divided into meaningful groups. Identify what one output row represents.
df.groupby("sector")["feature"] divides observations by a grouping column and selects the values to process.
| Final operation | Result shape | Use |
|---|---|---|
.agg("mean") |
One summary per group | Compare sectors |
.transform("mean") |
One aligned value per input row | Subtract each row’s sector mean |
.filter(function) |
Original rows of qualifying groups | Retain groups passing a rule |
A feature to assign back to the original rows normally needs transform. State whether the output represents groups or individual observations.
Group two years of hourly load by weekday. Check that the result has one row for each observed weekday.
groupby splits the rows into seven groups, applies mean() to each group’s AEP, and combines the seven results into a Series indexed by weekday. That is aggregation. transform (two slides on) is the other shape — it keeps every original row.
Before running: How many rows does pjm.groupby("WeekDay")["AEP"].mean() return?
One row per group (7 weekdays) and one column per aggregation (mean, std, iq = 3). agg([…]) turns a list of functions into a summary table; the named form agg(name=(column, function)) is the cleanest way to get well-labelled columns from different source columns.
Before running: pjm.groupby("WeekDay")["AEP"].agg(["mean", "std", iq]) returns a…
Standardise AEP within each weekday. Compare the input and output indexes and lengths.
transform applies the 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.
Before running: What is the length of pjm.groupby("WeekDay")["AEP"].transform(lambda x: (x - x.mean()) / x.std())?
The final Sunday hour is 1.7 sd above the overall mean and 2.4 sd above the Sunday mean. These are full-sample descriptive statistics. For prediction, estimate weekday means and scales on training data only.
Before running: Group means of B: foo → 3, bar → 4. Which rows survive filter(lambda g: g["B"].mean() > 3)?
Use groupby("WeekDay")["AEP"].apply(...) with a lambda to compute the interquartile range (75 % − 25 % quantile) of AEP for each weekday, into iq_day.
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.
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.
Question: Which rows survive the join, and can repeated matches multiply rows?
Result: A validated join with unmatched rows accounted for.
Check for missing values in the columns used for matching. State which columns to match and how many rows each match may produce.
Text columns hide numbers ('-' for missing income). pd.to_numeric(errors="coerce") turns the junk into NaN.
size counts university-year records, not distinct institutions. Print it beside the research-score mean and state the sample used to rank countries.
pd.merge(..., on=["ticker", "date"], how=...) matches values in both named columns. validate checks whether each match has the expected number of rows; indicator=True reports unmatched rows.
Two tables → one. The 2015 list has 401 universities, the 2016 list 800. pd.merge(u15, u16, on="university_name", how=…). Compare the retained rows and the reported counts.
inner keeps only names present in both years — fewer than 401. left keeps every 2015 row (401) and fills NaN for the ones that left the 2016 list. outer keeps the union: 401 + 800 − 393 matched = 808. Rule of thumb: left to enrich a spine, inner for matched-only, outer to audit gaps.
Before running: Compare the unmatched rows below. Which year does each join preserve, and where do missing values appear?
How many 2015 universities have no 2016 row? Use the left merge: count the NaN in its research_16 column, into dropped (an integer). Check it against len(u15) - len(inner).
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.
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.
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 concatenates a list of Series or DataFrames. Use axis=1 for columns and axis=0 for rows. Use pd.concat for appending rows; DataFrame.append() has been removed.
Concatenating two Close series with different date ranges. Inspect the unmatched dates in the combined table.
concat(axis=1) performs an outer join on the index values: every date from either series appears, and a series that has no value for that date gets NaN. The keys= parameter names the resulting columns.
Before running: msft covers 2015–2018, fb only 2016. What does pd.concat([msft, fb], axis=1) contain for 2015 rows?
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.
pair holds MSFT and FB prices on the same 2016 dates. Set rho to the Pearson correlation of their daily percentage changes (a plain float).
Question: How should the same observations be arranged?
Result: A reshaped table that retains the stock and date for every observation.
After checking that each stock/date pair occurs once, move those values between index levels and columns.
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.
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_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_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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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() 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.
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.
.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.
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.
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.
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.
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(..., 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.
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.
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.
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.
The full selected university sample produces 24 country/year means. Keep record counts beside the means: changing sample composition can change a country average.
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.
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.
inventory.csv (Kaggle retail inventory, 20 000 SKUs). Use pivot_table(index="ReleaseYear", columns="New_Release_Flag", values="PriceReg") for releases from 2010 on, then compare its index and column names with the source rows.
Before running: Compare the seven release-year index values and the two flag columns with the source table. What does one cell summarise?
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.
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.
.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 assigns numeric values to intervals. bins supplies the interval boundaries; labels supplies one name per interval. By default, right=True gives right-closed intervals, such as \((150, 300]\).
Before running: pd.cut([100, 200, 300, 400, 50, 175], bins=[0, 150, 300, 1000], labels=["low", "mid", "high"]) — note 300 sits on the 150–300 edge.
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.
Keep the 2016 universities with world_rank ≤ 100 in top100 (exactly 100 rows), then plot citations against rank.
Plotly Express (px) creates interactive figures; §2.5 explains its interfaces.
In Colab
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: Times university rankings 2011–2016; melt and pivot: long ↔ wide; Your turn: citations vs rank, top 100 of 2016
# Previously introduced: groupby: one number per group
import numpy as np, pandas as pd
url = "https://statpython.pages.dev/data/"
# Previously introduced: A second frame: Times university rankings 2011–2016
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: Your turn: citations vs rank, top 100 of 2016
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)ISOM 5650 · Prof. Xuhu Wan · HKUST Business School