3.3 Pandas: groupby, joins, reshaping, time series

What this is and why it exists

Grouping, joining and reshaping are the verbs of real analysis: most business questions turn out to be a grouping plus a join away from an answer. This topic gives you those verbs, adds the time-series operations that come free once your index is dates, and spends real attention on the one operation that silently produces wrong numbers — a join on keys that were not as unique as you assumed.

The vocabulary

• groupby — split rows into groups, do something per group, combine the results.
• agg — reduce each group to one row per group.
• transform — return a value per original row, aligned back to the input.
• merge — combine two frames on matching keys; the documentation says it "performs join operations similar to relational databases like SQL".
• Join type — inner, left, right, outer or cross, deciding which keys survive.
• Long and wide — one row per observation, or one row per subject with observations across columns.
• Resample — regroup a time series to a coarser or finer interval.
• Rolling window — a calculation over the last n rows at every row.

The mental model

Grouping is split, apply, combine, and the useful distinction is what comes back. agg gives one row per group — an average per subject, a count per month. transform gives one value per original row, aligned back — each student's score minus their subject's mean, added as a column. Almost everybody reaches for agg and then merges the result back, when transform was the operation they wanted. apply is the general escape hatch and the slowest by a distance, so use it when nothing else fits and not before.

Then joins, and this is where the care goes. The join type decides which keys survive: inner keeps only keys present in both, left keeps everything on the left, outer keeps everything from either. That is the part people get right. What they get wrong is duplication, and the documentation states the mechanism exactly: "for a many-to-many join, if a key combination appears more than once in both tables, the DataFrame will have the Cartesian product of the associated data" — with the accompanying warning that "merging on duplicate keys significantly increase the dimensions of the result and can cause a memory overflow".

That is the trap, and it is silent in the worst way: nothing errors, the analysis completes, and every total is inflated because rows were multiplied. Check the row count before and after every join, every time. And better, use the guard pandas provides: the validate argument "checks whether the uniqueness of merge keys. Key uniqueness is checked before merge operations and can protect against memory overflows and unexpected key duplication". Declaring validate="one_to_many" states your assumption, and if the data disagrees you get an error instead of a wrong answer. A stated assumption that is checked is worth more than a careful person who is busy.

Reshaping moves between long and wide. Long — one row per observation — is what plotting and modelling libraries want. Wide — one row per subject, observations spread across columns — is what people want to read. pivot goes long to wide, melt goes wide to long, and knowing which shape a tool expects saves the half hour spent fighting a chart that will not draw.

Time series arrive when the index is dates, and then two operations become available. Resampling regroups to a different interval — daily readings to weekly means — and is grouping with the time axis doing the splitting. A rolling window computes over the last n rows at every row, which is how moving averages and trends are made. One caution matters for anything that will feed a model: a rolling window must look backwards only. A centred window uses future values, and a feature built from the future is the leakage the next topic is about.

In code

Checked against the pandas user guide.

import pandas as pd

# Split, apply, combine — and note which shape each returns.
per_subject = df.groupby("subject")["score"].agg(["mean", "count"])
df["vs_subject_mean"] = df["score"] - df.groupby("subject")["score"].transform("mean")

# Join with the assumption stated, so the data can contradict it.
before = len(df)
enriched = df.merge(
    students,
    on="student_id",
    how="left",
    validate="many_to_one",     # many scores, one student — say so
)
assert len(enriched) == before, f"join changed row count: {before} to {len(enriched)}"

# Reshape: wide for reading, long for plotting and modelling.
wide = df.pivot(index="student_id", columns="subject", values="score")
long = wide.reset_index().melt(id_vars="student_id", var_name="subject", value_name="score")

# Time series: the index does the grouping.
ts = df.set_index("taken_on").sort_index()
weekly = ts["score"].resample("W").mean()
trend = ts["score"].rolling(window=7).mean()      # backwards only, never centred

The validate argument and the assertion are doing the same job from two directions, and both are cheap. Between them, a join that quietly multiplies your rows becomes a loud failure at the moment it happens rather than a wrong number in a report a fortnight later.

What you should now be able to explain or do

Choose between agg, transform and apply for three described tasks. Say what a many-to-many join does to the row count and why nothing errors. State your key assumption to pandas and let it check it. Move a table between long and wide and say which shape a plotting library wants. Resample a time series and compute a rolling mean, and say why the window must look backwards.

Check yourself