2.3 — Groupby, Pivoting, Melting, Stacking, Concatenation

Chapter 2 · DataFrames

Prof. Xuhu Wan

Section 2.3 · Chapter 2 · Learning Statistics with Python

Groupby, Pivoting, Melting, Stacking, Concatenation

DataFrames

Prof. Xuhu Wan

ISOM, HKUST Business School · 2026 Edition

Groupby, Pivoting, Melting, Stacking, Concatenation

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

groupby: one number per group

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

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

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

Several aggregations at once, your own functions, named columns

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

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

transform: same shape as the input

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

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

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

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

filter: keep whole groups that pass a test

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

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

[1, 3, 5]

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

Your turn: interquartile range by weekday

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

A second frame: Times university rankings 2011–2016

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

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

Joins: inner, left, outer — who survives?

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

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

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

Your turn: count the universities that dropped out

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

pivot_table: two grouping keys become rows × columns

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

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

The same table by groupby + unstack

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

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

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

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

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

pt_inv.shape

(7, 2)

stack, unstack, transpose

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

From the lecture

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

When apply is not needed: pd.cut

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

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

tier.tolist()

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

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

melt and pivot: long ↔︎ wide

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

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

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

11525

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

pd.concat: side by side, or on top

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

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

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

Your turn: assemble a pair and correlate it

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

Plotting a frame

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

In Colab

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

Your turn: citations vs rank, top 100 of 2016

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

What you discovered

  • groupby(...).agg → one row per group (named form agg(col=("src", "fn"))); .transform → one row per input row; .filter → the rows of groups that pass.
  • pd.merge(how=): inner = matched only, left = keep the spine and NaN-fill, outer = the union. Count rows after every merge.
  • pivot_table(index, columns, values) aggregates (mean by default) and equals groupby([a, b]).mean().unstack(b); pd.crosstab is the same thing spelled differently.
  • pivot (no aggregation) needs unique pairs; melt is its inverse — 2 305 × 5 wide became 11 525 long. stack/unstack move labels between the axes; .T swaps them.
  • pd.concat(axis=1) outer-joins on the index; axis=0 keeps duplicated labels. pd.cut bins vectorised, right-closed.

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

Prof. Xuhu Wan · HKUST ISOM · Learning Statistics with Python