Module 16 · Core

Group, summarize, and compare

Distinguish group summaries, row-aligned transformations, and group filters.

Before you start: You can select columns, use summary methods, and create new columns.

Python loads from this site on your first run.

Each run starts with fresh data. Download any exported files before another run. Your written answers and code drafts are saved on this device; export your work to keep a backup.

M16C01 · Explore · predict · practice

One question for each group

groupby() splits rows according to a label, then a summary method calculates one result per group. Named aggregation lets you choose readable output names. Here each campus receives its own row count and mean score.

ReadWhat does each row of the result represent?

PredictHow many rows should result contain?

ExplainWhy use as_index=False?

Read the example

df = students()
result = df.groupby("Campus", as_index=False).agg(Students=("Student_ID", "size"), Mean_score=("Score", "mean"))
print(result.round(2))

Verified example output · pandas 2.2.3

  Campus  Students  Mean_score
0  Dubai         6       76.83
1    RAK         6       81.00

Watch for this. A mean without its supporting count can hide a very small group.

Your turn

Group by Program instead. Store a DataFrame named result with Program as a normal column and named summary columns Students, Median_score, and Highest_score.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

df = students()
result = df.groupby("Campus", as_index=False).agg(Students=("Student_ID", "size"), Mean_score=("Score", "mean"))
print(result.round(2))

Show a hint

Use named aggregations with size, median, and max.

Show one worked solution

df = students()
result = df.groupby("Program", as_index=False).agg(Students=("Student_ID", "size"), Median_score=("Score", "median"), Highest_score=("Score", "max"))
print(result)

Both programs have six students. AI has median 88.5 and maximum 95; IT has median 74.0 and maximum 82.

Compare your explanations

Read. One campus, with its number of student records and average score.
Predict. Two, one for Dubai and one for RAK.
Explain. It keeps Campus as an ordinary column rather than placing it in the result index.

One quick check

How many values does an ordinary mean aggregation return for one group?

What you should be able to explain. Summarize each group with readable labels and counts.

Your own assessment, separate from code checks

M16C02 · Explore · predict · practice

Multiple keys and missing group labels

Grouping by a list of columns forms one group for each observed combination. By default, rows with a missing group key are excluded. dropna=False keeps those rows in a separate missing-label group so the totals can be audited.

ReadWhat does one result row represent now?

PredictHow many student records are represented in total?

ExplainWhat would the default dropna=True do to S001?

Read the example

df = students()
df.loc[0, "Campus"] = None
result = df.groupby(["Campus", "Program"], dropna=False, as_index=False).agg(Students=("Student_ID", "size"), Mean_score=("Score", "mean"))
print(result.round(2))
print("Total represented:", result["Students"].sum())

Verified example output · pandas 2.2.3

  Campus Program  Students  Mean_score
0  Dubai      AI         2       92.00
1  Dubai      IT         4       69.25
2    RAK      AI         3       81.00
3    RAK      IT         2       77.50
4    NaN      AI         1       88.00
Total represented: 12

Watch for this. When group keys are categorical, set observed explicitly if you need predictable inclusion of unused categories.

Your turn

Run the same grouping with dropna=True. Compare its represented row total with len(df), and store the grouped table in result.

For the code check, keep these result variable names: df, result. Different valid approaches are welcome.

Your Python code

df = students()
df.loc[0, "Campus"] = None
result = df.groupby(["Campus", "Program"], dropna=False, as_index=False).agg(Students=("Student_ID", "size"), Mean_score=("Score", "mean"))
print(result.round(2))
print("Total represented:", result["Students"].sum())

Show a hint

Keep the original table; add the group sizes and compare the sum.

Show one worked solution

df = students()
df.loc[0, "Campus"] = None
result = df.groupby(["Campus", "Program"], dropna=True, as_index=False).agg(Students=("Student_ID", "size"))
print(result)
print("Represented:", result["Students"].sum(), "Original:", len(df))

The grouped counts sum to 11, while the original table still has 12 rows. S001 is excluded only from the grouping output.

Compare your explanations

Read. One Campus-Program combination, including a combination whose Campus is missing.
Predict. All 12, because dropna=False retains the missing-campus record.
Explain. It would exclude S001 from this grouped result because Campus is missing.

One quick check

Which option retains rows whose grouping key is missing?

What you should be able to explain. Reconcile grouped totals with the original table.

Your own assessment, separate from code checks

M16C03 · Explore · predict · practice

A summary for every original row

transform() returns values aligned with the original rows. A group mean can therefore be attached to every student in that group. This makes comparisons with a group benchmark possible without reducing the table to one row per group.

ReadHow many rows does result keep?

PredictWill all AI students have the same Program_mean?

ExplainWhy would assigning a two-row group mean Series directly be risky?

Read the example

df = students()
df["Program_mean"] = df.groupby("Program")["Score"].transform("mean")
df["Difference"] = df["Score"] - df["Program_mean"]
result = df[["Student_ID", "Program", "Score", "Program_mean", "Difference"]]
print(result.round(2))

Verified example output · pandas 2.2.3

   Student_ID Program  Score  Program_mean  Difference
0        S001      AI     88         85.83        2.17
1        S002      IT     72         72.00        0.00
2        S003      AI     91         85.83        5.17
3        S004      IT     65         72.00       -7.00
4        S005      IT     79         72.00        7.00
5        S006      AI     95         85.83        9.17
6        S007      AI     84         85.83       -1.83
7        S008      IT     58         72.00      -14.00
8        S009      IT     76         72.00        4.00
9        S010      AI     89         85.83        3.17
10       S011      AI     68         85.83      -17.83
11       S012      IT     82         72.00       10.00

Watch for this. A group-relative label and a fixed-threshold label answer different questions.

Your turn

Calculate the median score within each campus in a column named Campus_median. Keep only students scoring strictly above that median. Store Student_ID, Campus, Score, and Campus_median in result.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

df = students()
df["Program_mean"] = df.groupby("Program")["Score"].transform("mean")
df["Difference"] = df["Score"] - df["Program_mean"]
result = df[["Student_ID", "Program", "Score", "Program_mean", "Difference"]]
print(result.round(2))

Show a hint

Use groupby("Campus")["Score"].transform("median") to build the benchmark.

Show one worked solution

df = students()
df["Campus_median"] = df.groupby("Campus")["Score"].transform("median")
result = df.loc[df["Score"] > df["Campus_median"], ["Student_ID", "Campus", "Score", "Campus_median"]]
print(result)

Six students are above their campus median: S001, S003, S006, S007, S010, and S012.

Compare your explanations

Read. All 12 original student rows.
Predict. Yes. The same AI group mean is aligned with each AI student.
Explain. Pandas aligns by index labels, which may not match the original row index. transform is designed to return a row-aligned result.

One quick check

Which operation returns a group statistic aligned to every original row?

What you should be able to explain. Use transform when the result belongs beside the original records.

Your own assessment, separate from code checks

M16C04 · Explore · predict · practice

Keep whole groups and control unused categories

A group filter keeps or removes whole groups based on a group-level rule. Categorical columns can also contain labels with no rows. observed=True limits a group result to categories actually present in the data.

ReadDoes declaring CS as a category create CS student records?

PredictWill CS appear in this grouped result?

ExplainHow is filtering whole groups different from filtering scores >= 80?

Read the example

df = students()
df["Program"] = pd.Categorical(df["Program"], categories=["AI", "IT", "CS"])
result = df.groupby("Program", observed=True)["Score"].agg(["size", "mean"])
print(result.round(2))

Verified example output · pandas 2.2.3

         size   mean
Program             
AI          6  85.83
IT          6  72.00

Watch for this. Unused categories and missing labels are different: observed controls unused categories; dropna controls missing keys.

Your turn

Start from students(). Keep all students in programs whose mean Score is at least 80 using groupby().filter().

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

df = students()
df["Program"] = pd.Categorical(df["Program"], categories=["AI", "IT", "CS"])
result = df.groupby("Program", observed=True)["Score"].agg(["size", "mean"])
print(result.round(2))

Show a hint

The filter function receives each group DataFrame; compare its Score mean with 80.

Show one worked solution

df = students()
result = df.groupby("Program").filter(lambda group: group["Score"].mean() >= 80)
print(result[["Student_ID", "Program", "Score"]])

All six AI students remain, including S011 with score 68. The rule concerns the program mean, not each individual score.

Compare your explanations

Read. No. It declares a possible label without adding rows.
Predict. No, because observed=True reports only categories that occur in the data.
Explain. A group filter keeps all rows in an accepted group, including individual scores below 80.

One quick check

A program passes the group-mean rule. Which records are kept by groupby().filter()?

What you should be able to explain. Separate group inclusion rules from row inclusion rules.

Your own assessment, separate from code checks

Previous: Statistics you can explain Next: Merge related tables

Further detail: official documentation for this topic.