Grouping by Several Keys
Overview
Two keys give a row per combination, and an index with two levels.
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Group by more than one column.
- Read a two-level index.
- Flatten the result back into a plain table.
- Say what happens to combinations that never occur.
Why This Matters
"Per subject per year" is one group-by, not two. The result carries an index with two levels, which is the first genuinely unfamiliar object in pandas — and knowing how to flatten it is most of what you need.
Step 1: A list of columns
Pass a list and you get a row per combination that actually occurs in the data.
One row per combination
The index now has two levels, printed as two columns down the left.
import pandas as pd
df = pd.DataFrame({"subject": ["m", "m", "a"], "year": [10, 11, 10], "score": [90, 80, 70]})
out = df.groupby(["subject", "year"])["score"].mean()
print(out)
print(out.index.nlevels)Reaching into it
A tuple indexes both levels at once; naming just the first gives you everything under it.
import pandas as pd
df = pd.DataFrame({"subject": ["m", "m", "a"], "year": [10, 11, 10], "score": [90, 80, 70]})
out = df.groupby(["subject", "year"])["score"].mean()
print(out[("m", 10)])
print(out["m"])Mini Practice #1: Two keys
Try It YourselfGroup by both columns, print the result, and print how many levels its index has.
Step 2: Flattening the result
Most of the time you want a plain table with the keys as ordinary columns, which is one call away.
reset_index
Both levels become columns and the index goes back to 0, 1, 2. This is the form you would write to a file or hand to a chart.
import pandas as pd
df = pd.DataFrame({"subject": ["m", "m", "a"], "year": [10, 11, 10], "score": [90, 80, 70]})
flat = df.groupby(["subject", "year"])["score"].mean().reset_index()
print(flat)
print(list(flat.columns))Combinations that never happened
A pair with no rows does not appear — the result has one row per combination that exists, not per combination that is possible. If the missing ones matter, unstack lays the grid out and shows them as NaN.
import pandas as pd
df = pd.DataFrame({"subject": ["m", "a"], "year": [10, 11], "score": [90, 70]})
out = df.groupby(["subject", "year"])["score"].mean()
print(len(out), "rows for", df["subject"].nunique() * df["year"].nunique(), "possible pairs")
print(out.unstack())Mini Practice #2: Flatten it
Try It YourselfTurn the same result into a plain table with three ordinary columns.
Step 3: When to Use It
Combinations of categories
Team and term, region and month: one row per pair that exists.
df.groupby(["team", "term"])["score"].mean()Drilling down
A two-level index lets you select one outer group and see its inner breakdown.
When Not to Use It
Do not forget reset_index when the next step expects ordinary columns. And remember combinations with no rows do not appear at all.
End-of-Lesson Exercises
Exercise 1: Count every pair
Write pairs_counted(df) returning a dictionary keyed by the (subject, year) tuple, holding how many rows each pair has.
Group by both columns and ask for size. to_dict() on a two-level index gives tuple keys, and the counts want int() to shed their numpy type.
Exercise 2: Flatten it back to a table
Write flat_means(df) that groups by subject and year, takes the mean score, and returns the column names of the result as a plain table — keys as ordinary columns, not as an index.
Group by the two columns, take the mean of score, then reset_index() to push both levels back out as columns. Without it the keys stay in the index and the columns list is just ["score"].