Summarising by category, joining two tables on a shared key, and pivoting data between long and wide — the moves behind real analysis.
Programming for Data Science · University of the Philippines Cebu
Split by category, apply a summary, combine the results.
Join two tables that share a key into one.
Pivot between long, tidy data and wide, readable tables.
Last week you kept the rows you wanted. This week you summarise, combine, and reshape them into answers.
If you have used Excel, you have done all three by hand. Part A is a PivotTable's "sum by region", Part B is VLOOKUP (fetch a value from another sheet by matching a name), and Part C is turning a long list into a grid and back.
Answer "what's the average per region?" in a single line.
Sort the rows into piles, one pile per distinct value of a column.
e.g. df.groupby("region") makes one pile per region
A summary that turns many values into one: mean, sum, count, max.
e.g. .agg(["mean", "count"])
Split into groups, summarise each group, stack the answers into one small table.
e.g. 34,079 project rows → 18 region rows
The column two tables share, used to decide which rows belong together.
e.g. city in both the population and the area table
Combine two tables side by side by matching their key values, like Excel's VLOOKUP.
e.g. pd.merge(pops, areas, on="city")
Which rows survive a merge: only matches, every row of the left table, or everything.
e.g. how="left"
Stack tables under (or beside) each other, without matching any key.
e.g. pd.concat([january, february])
Long: one measurement per row. Wide: a grid, with one category spread across the columns.
e.g. city, year, sales vs city, 2024, 2025
Turn long into wide; pivot_table also summarises repeats, like an Excel PivotTable.
e.g. pivot_table(index="region", columns="year", …)
Turn wide back into long: every cell of the grid becomes its own row.
e.g. wide.melt(id_vars="city")
You can keep the Visayan cities. But "the average population of each region" needs a summary per category. That's what grouping is for.
cities: a tiny table (city, region, pop) small enough to
check every row by eye. projects: the 34,079 flood-control projects from
week 6 (contract_id, region, year, budget, status). Both are called
df in the code; the column names tell you which one it is.
Filtering gives you one subset. Grouping gives you every subset at once.
Real answers often need two tables joined on a shared column.
Split, apply a summary, combine.
You already know how to take one summary of a whole column, like df["pop"].mean(). This part takes that same summary once per category, all in one line.
Group the rows by a category, apply a summary to each group, then combine those summaries into a small result table. One idea, endlessly reused.
To group by a column means to
sort the rows into piles, one pile per distinct value (every "Visayas" row in one pile). An
aggregation is a summary that turns many values into one —
mean, sum, count, max.
Tip a box of receipts onto a table, sort them into one pile per store (split), add up each pile (apply), and write the totals on one sheet (combine).
Rows fall into buckets by region.
Each bucket gets a summary — mean, sum, count.
One row per group, stacked into the answer.
The group column becomes the index of the result. One row per distinct value — no matter how many rows went in.
This is NumPy's axis idea again (week 5: the direction you summarise along): collapse many values to one, per group.
groupby then a summaryName the column to group on, the column to summarise, and the summary. pandas returns one value per group, indexed by the group name.
It is the spreadsheet PivotTable with "region" dragged to Rows and "Average of pop" dragged to Values — written as one line you can re-run.
df.groupby("region")split the cities table into one pile per region["pop"]in each pile, look only at the population column.mean()average each pile's populations.agg() for a small reportPass a list of summaries and get a column for each. One call turns raw rows into a compact per-group report — mean, max, and count together.
.agg() is short for
aggregate: "apply these summaries". Give it one name ("mean")
or a list of names in square brackets.
.agg(["mean", "max", "count"])run three summaries on every pile; each one becomes a columnPass a list of function names and every one is computed per group. Useful as the first thing you look at after any grouping.
A mean over 12 projects and a mean over 5,412 deserve very different amounts of trust, and only the count tells you which you have.
.agg(["count", …, "max"])seven summaries per region: how many, total, average, middle value, spread (std, standard deviation), smallest, largeststats.shape / stats.sort_valuessave the table as stats, then ask its size: (18, 7) = 18 regions by 7 statistics; ascending=False sorts biggest firstSame three words, a real table. The output has one row per region, and that is the whole point: the grain changed — grain means "what one row stands for". Before: one project per row. After: one region per row.
Named aggregation means writing new_name=(column, summary), so you choose the output column names. Without it you can get a MultiIndex: two-level column labels such as ("budget", "sum") that are awkward to use.
df.groupby("region")split the 34,079 project rows into one pile per region.agg(projects=("contract_id", "count"), …)for each pile, make three new columns: how many projects, the total budget, and the typical (median) budgetlen(df), len(by_region)len counts rows; showing two values side by side gives a pair in brackets(34079, 18)34,079 rows went in, 18 came out: one per region| Region | Projects | Total spend | Median project |
|---|---|---|---|
| Region III | 5,412 | ₱267.7B — highest | ₱46.3M |
| National Capital Region | 3,925 | ₱159.2B | ₱29.2M |
| Region V | 2,814 | ₱155.6B | ₱46.8M |
| Central Office | 180 | ₱43.6B | ₱144.5M — highest |
Region III receives the most money. Central Office runs the biggest projects — 180 of them, each typically three times a Region III project. Group by the wrong summary and you write the wrong headline.
"Total spend" answers "where did most of the money go?"; the median (the middle value once the projects are sorted by size) answers "how big is a normal project here?". Different questions, different winners.
Pass a list of column names and you get one row per combination that exists — not per possible combination.
18 regions × 11 years is 198. Eighteen of those pairs never occurred, and groupby does not create them.
groupby(["region", "year"])a list of two columns: one pile per (region, year) pair, e.g. Region III in 2021.budget.sum()add up the budget column in each pile (df.budget is a shortcut for df["budget"]).reset_index()move region and year out of the row labels and back into ordinary columns, so you get a plain table (the outer brackets let the chain continue on the next line)180 vs 198only 180 pairs actually have projects; pivot (Part C) lays them out as a grid, where the 18 missing pairs show as NaNBy default the group key becomes the index (the row labels), which is tidy until you want to merge or plot. One argument avoids the reset_index() that usually follows.
reset_index() moves the row labels back into an ordinary column and numbers the rows 0, 1, 2… again.
Same result. Use whichever keeps the chain readable — just be aware which one you did, because .loc behaves differently.
Aggregate without collapsing — so every row can be compared to its own group.
You now know groupby + a summary gives one row per group. transform computes the same per-group summary but copies it back onto every original row.
agg gives you one row per group. transform gives you one row per original row, filled with that row’s group value.
To ask "is this project big for its region?" you need the region median sitting next to every project.
.budget.median()an aggregation: one median per region, so 18 values come back.budget.transform("median")the same 18 medians, but each project row receives its own region's median, so 34,079 values come backagg writes one line per class on the report card; transform writes the class average next to every student's own mark.
Two lines turn an absolute number into a relative one — and relative is usually the interesting question.
₱279.8M in Region V, where the median is ₱46.8M. That is 5.98× a typical project there — a fact no single-column summary could give you.
df["reg_median"] = …create a new column holding each project's region median (the brackets spread the chain over two lines)df.budget / df.reg_mediandivide row by row: how many "typical projects" is this one worth?.iloc[0]show the first row by position (week 6)vs_region 5.98this project costs almost six times a typical project in its region17,036
projects
17,036
projects
That is what a median does — it splits each group in half (the other 7 projects sit exactly on their region’s median). Getting anything else would mean the transform misaligned.
This is a free correctness test. transform aligns results back to the original index (it puts each value on the row with the same row label); if it had not, the split would not be even. Check the shape (how many rows and columns) and a known property every time you use it.
Different from a boolean mask (week 6: a True/False per row that picks rows): here the test runs per group, and either the whole group stays or the whole group goes.
A lambda is a tiny function written in one line, with no name. lambda g: len(g) > 3000 means "given a group g, answer True if it has more than 3,000 rows".
Comparing regions with 12 projects against ones with 5,000 is rarely fair; this drops the thin ones in one line.
.filter(lambda g: …)ask the lambda about each region's pile; keep every row of the piles that answer True(4, 16291)4 regions survived (nunique = number of distinct values), holding 16,291 of the projectssorted(big.region.unique())the names of the regions that were kept, in alphabetical orderAnother transform-shaped tool: it returns one value per row, so you can ask "where does this project sit in its own region?"
Two projects at the same budget both get the lower rank, which is what "joint first" means everywhere outside pandas.
A class ranking per section: every student gets "3rd in my section", not "412th in the school".
.rank(ascending=False)number each project within its region, biggest budget = 1df[df.rank_in_region == 1]keep only the rank-1 rows: the biggest project in each region (18 rows).nlargest(2, "budget")of those, the two with the largest budget (week 6)Sort descending (biggest first), take a running total, divide by the sum. The result answers "how few projects make up half the money?"
A running total (cumsum, "cumulative sum") adds each value to everything above it: 5, 3, 2 becomes 5, 8, 10.
Its top 1,357 projects — 25.1% of them — account for 50% of its ₱267.7B. Concentrated, but not extreme.
r3.budget.cumsum() / r3.budget.sum()running total as a share of the whole: 0.5 means "half the money so far"(r3.cum_share <= .5).sum() + 1count the True values (True counts as 1): how many projects it takes to pass the halfway markWhat does
df.groupby("region")["pop"].sum() return?
B — one total per region.
Grouping produces one summary row per distinct
group value, indexed by that value. A plain df["pop"].sum() would give
the single grand total (A).
Group first → per-group answers. No group → one grand total.
One result row per group value.
Build by_region with projects, total and median. Confirm it has 18 rows.
Which region leads on total? Which leads on median? Explain in one sentence why they differ.
Use transform to add vs_region. Find the project with the highest ratio — the most outsized project for its area.
Count how many rows are above vs below their region median. If it is not an even split, your transform misaligned.
Step 3 is the deliverable: an absolute number ranks the biggest projects; a relative one finds the surprising ones.
You need each project compared against its own region’s median budget. Which tool?
groupby(...).agg("median") — then merge it backgroupby(...).transform("median") — it returns one value per original rowgroupby(...).filter(...)pivot_table with aggfunc="median"transform returns a result aligned to the original index — 34,079 values for 34,079 rows — so it drops straight into a new column. A works but takes an extra merge and a chance to misalign; C keeps or drops whole groups; D changes the grain entirely.Two tables, one shared key, joined into one.
So far every answer came from one table. This part brings in columns from a second table by matching rows on a column both tables share — the code version of Excel's VLOOKUP.
Population sits in one file, land area in another. Both have a
city column — the key. Merging lines them up so you can compute
density.
city)Excel's VLOOKUP: for each city in sheet 1, look up the same city in
sheet 2 and copy its area across. merge does that for every row and every
column at once.
city, region, pop
city, area_km2
city — matched row by row.
merge on the shared columnName the key with on=. pandas matches rows where the key agrees and
glues the columns together into one wider table.
pd.read_csv("areas.csv")load the second table (city, area_km2) from a filepd.merge(df, areas, on="city")for each row of df, find the row of areas with the same city and glue its columns onboth["density"] = …now pop and area sit in the same row, so dividing them gives people per km²how=how= | Keeps | Use when |
|---|---|---|
"inner" (default) | keys in both | you need complete matches only |
"left" | all of the left table | enrich a main table, keep every row |
"right" | all of the right table | same idea, other side |
"outer" | keys in either | you want to see what didn't match |
The join type (how=) decides what happens to rows
whose key appears in only one table. Unmatched rows fill with NaN. A surprise
NaN after a merge almost always means a key that didn't line up.
inner silently drops non-matches — check your row count after.
pops| city | pop |
|---|---|
| Cebu | 964000 |
| Iloilo | 457000 |
| Davao | 1777000 |
areas| city | area_km2 |
|---|---|
| Cebu | 315 |
| Iloilo | 78 |
| Manila | 43 |
how="inner" → 2 rows| city | pop | area_km2 |
|---|---|---|
| Cebu | 964000 | 315 |
| Iloilo | 457000 | 78 |
how="left" → 3 rows| city | pop | area_km2 |
|---|---|---|
| Cebu | 964000 | 315.0 |
| Iloilo | 457000 | 78.0 |
| Davao | 1777000 | NaN |
how="outer" → 4 rows| city | pop | area_km2 |
|---|---|---|
| Cebu | 964000.0 | 315.0 |
| Davao | 1777000.0 | NaN |
| Iloilo | 457000.0 | 78.0 |
| Manila | NaN | 43.0 |
innerkeeps only cities found in both tables; Davao and Manila disappear without a wordleftkeeps every row of pops; Davao has no area to look up, so it gets NaNouterkeeps every city from either table; each gap is NaNA left join is VLOOKUP dragged down your main sheet: every row stays, and a
failed lookup leaves a blank (NaN) instead of deleting the row. Notice 315 became
315.0: a number column that holds a NaN is stored as decimals (float).
The worst merge bug is the one that works: a duplicated key quietly doubles your rows and every total after it is wrong. One argument prevents it.
many_to_one, one_to_one, one_to_many. If reality disagrees, pandas raises instead of guessing.
many_to_one means "many project rows may share a region, but the lookup table regions lists each region only once". To raise means to stop with an error message (an exception, week 4).
MergeError is the error type (a merge problem), the words after the colon say whykeys are not unique in right datasetthe right table (the second one, regions) lists some region twice; check it with regions.region.duplicated().sum()A left join fills unmatched rows with NaN, which looks exactly like missing data. The indicator column tells you which it is.
indicator=True adds a column called _merge that says, for every row, where its key was found: both, left_only or right_only.
A merge that matched 60% of rows is usually a key-formatting problem — whitespace, case, or a type mismatch between int and str.
m._merge.value_counts()count how many rows fall in each of the three labelsboth 34079, left_only 0every project found its region: a clean mergeconcat (short for concatenate, "chain together") stacks tables without matching any key: like pasting last month's sheet under this month's. axis=0 stacks rows (one under the other); axis=1 puts them side by side.
With axis=1, pandas aligns on the index rather than row order. Two frames that "look lined up" but have different indexes produce a table full of NaN — reset_index(drop=True) on both first if you mean positional.
A merge you expected to be many-to-one returns 68,158 rows from a 34,079-row left frame. What would have caught this at the moment it happened?
how="inner" instead of how="left"validate="many_to_one", which raises MergeError on a duplicated right keyvalidate= checks the relationship you expect and raises immediately; indicator=True then tells you which rows matched. Without them, the first symptom is a total that is exactly double, three steps downstream.Back to turn tables sideways.
Long for analysis; wide for reading.
You can now summarise and combine tables. This part changes their layout — the same numbers arranged as a long list or as a grid — without changing a single value.
Long form has one measurement per row — ideal for grouping and plotting. Wide form spreads a category across columns — ideal for a human to scan.
A long table is a receipt roll: one line per sale, however many there are. A wide table is a calendar grid: cities down the side, years across the top, a number in each cell.
city, year, sales — one number per row.
city, 2023, 2024, 2025 — years as columns.
sales (4 rows)| region | year | sales |
|---|---|---|
| Visayas | 2024 | 10 |
| Visayas | 2025 | 14 |
| Luzon | 2024 | 22 |
| Luzon | 2025 | 25 |
pivot_table →meltwide (2 rows × 2 year columns)| region | 2024 | 2025 |
|---|---|---|
| Luzon | 22 | 25 |
| Visayas | 10 | 14 |
sales.pivot_table(index="region", columns="year", values="sales", aggfunc="sum")regions become rows, years become columns, sales fill the cellswide.reset_index().melt(id_vars="region", var_name="year", value_name="sales")back to four rows: one per cell of the gridNothing was added or lost: both tables hold the same four numbers. Long is easier for code (group it, plot it); wide is easier for eyes (compare 2024 with 2025 across a row). pandas sorts the regions alphabetically, so Luzon comes first.
pivot_table spreads a category outPick what labels the rows, what becomes columns, and which values fill the grid. It aggregates on the way, so duplicates collapse cleanly.
To pivot is to turn long into
wide: the values of one column (year) become the column headings.
pivot_table is exactly Excel's PivotTable — Rows, Columns, Values and
"Summarise by".
index="region"one row per region (Excel: Rows)columns="year"one column per year (Excel: Columns)values="sales"the numbers that fill the cells (Excel: Values)aggfunc="sum"if several rows land in one cell, add them up (Excel: Summarise by)pivot fails if a cell would hold more than one value. pivot_table takes an aggfunc (the aggregation to use) and summarises instead.
Without it, combinations that never occurred show as NaN — which is honest, but awkward to read in a count table.
aggfunc="count"each cell counts the projects with that region and that statusfill_value=0write 0, not NaN, where a combination never happenedpt.shapept is the table saved in a variable; (18, 5) means 18 rows by 5 columnsmelt folds columns into rowsThe reverse of a pivot. Keep an id column, and gather the rest into a name/value pair — the tidy shape most tools and plots expect.
To melt a table is to turn wide into long: each cell of the grid becomes its own row, labelled with the column heading it came from.
id_vars="city"keep city as it is, repeated on every new rowvar_name="year"the old column headings (2023, 2024, …) go into a new column called yearvalue_name="sales"the numbers from the cells go into a column called salesMost plotting libraries want long form: one row per observation, with the variable name in a column. melt is the inverse of pivot.
Everything not named there gets folded into the variable/value pair.
wide.reset_index()wide is the region × year grid; reset_index turns its row labels (the regions) back into an ordinary column so melt can keep itA shortcut for the count-only pivot: give it two columns and it counts every combination. crosstab is short for "cross-tabulation" — a table of counts with one category down the side and another across the top.
normalize="index" makes each row sum to 1, so you compare regions of very different sizes fairly.
pd.crosstab(df.region, df.status)rows = regions, columns = statuses, each cell = number of projectsnormalize="index"divide each row by its own total, so each row adds up to 1 (100%)0.81744381.7% of Region III's projects are Completed (... = columns not shown)| You want… | Use | Rows out |
|---|---|---|
| One summary per group | groupby().agg() | one per group (18) |
| Each row vs its group | groupby().transform() | same as input (34,079) |
| Position within group | groupby().rank() | same as input (34,079) |
| Keep or drop whole groups | groupby().filter() | a subset of input |
| Counts of two categories | pd.crosstab() | a grid (18×5) |
The question to ask before typing: do I want one row per group, or one row per original row? That single distinction picks the right tool almost every time.
Merge to get density, group by region, take the max. Merge, group, summarise — the whole week chained into a real answer.
df.merge(areas, on="city")Part B: bring each city's area into the cities table (df.merge(areas, …) is the same as pd.merge(df, areas, …))both["density"] = …people per km², one value per city.groupby("region")["density"].max()Part A: the highest density in each region — one number per regionYou merge two tables and want to keep
every row of the left one, matched or not. Which how=?
"inner""left""right""outer"B — "left".
A left join keeps all left rows, filling
unmatched right columns with NaN. "inner" would drop the
rows that didn't match.
Choose the join by which table you refuse to lose rows from.
Left join keeps the left; inner keeps only matches.
You'll summarise a dataset by category, join it to a second table on a key, and pivot the result between long and wide to answer set questions. ~45 minutes.
groupby.agg, merge(on=, how=), pivot_table, and
melt.
Group by two columns at once: groupby(["region", "year"]).
Split by category, apply a summary, combine — one row per group.
Join on a shared key; pick how= by which rows you keep.
pivot_table long→wide; melt wide→long.
One sentence: summarise by group, join in the facts you're missing, and reshape to fit the question or the reader.
Turn several raw tables into one clear, summarised answer.
new_name=(column, summary) inside .agg(): you pick the column nameslambda g: len(g) > 3000pivot_table also summarises repeats (Excel PivotTable)Finish the Week 7 lab and submit it. Be able to write a groupby.agg from
memory.
pandas "Group by: split-apply-combine" —
pandas.pydata.org/docs/user_guide/groupby.
Everything here is linked on the course page beside this deck.
Next week we turn these tables into charts people can read at a glance.
Turning a summarised table into a chart that makes the pattern obvious — and the handful of plot types you'll use again and again.
DS 208 · Programming for Data Science