Module 6 Lesson: Grouping, Aggregation, Pivoting, and Reshaping

Let us begin

Raw rows are not the final answer. Often we need summaries.

Examples:

• average practice minutes by course;
• completion count by level;
• support tickets by topic and month;
• rating distribution by workshop.

Grouping and reshaping help us move from detailed records to meaningful summaries.

Grain

Before summarizing, ask:

> What will one row represent after this operation?

This is the grain of the result.

If one row starts as one activity event, a grouped summary may become one row per course, one row per learner, or one row per course-week.

Name the grain. It prevents confusion.

Groupby

Groupby follows a simple idea:

1. Split data into groups.
2. Apply a calculation to each group.
3. Combine results.

summary = (
    activity
    .groupby("course_name")["minutes_spent"]
    .mean()
)

This returns average minutes per course.

Named aggregations

Named aggregations make output columns readable.

course_summary = (
    activity
    .groupby("course_name")
    .agg(
        learner_count=("learner_id", "nunique"),
        total_minutes=("minutes_spent", "sum"),
        average_minutes=("minutes_spent", "mean"),
        activity_events=("activity_id", "count"),
    )
    .reset_index()
)

Now the output is easier to use in a report.

Group by multiple columns

weekly_summary = (
    activity
    .groupby(["course_name", "week_number"])
    .agg(total_minutes=("minutes_spent", "sum"))
    .reset_index()
)

The grain is:

> one row per course per week.

Transform

transform lets you calculate a group value and keep the original row count.

activity["course_average_minutes"] = (
    activity
    .groupby("course_name")["minutes_spent"]
    .transform("mean")
)

This adds the course average to each row. It is useful for comparisons.

Pivot tables

Pivot tables reshape summaries into a matrix.

pivot = pd.pivot_table(
    activity,
    values="minutes_spent",
    index="course_name",
    columns="week_number",
    aggfunc="sum",
    fill_value=0,
)

This creates one row per course and one column per week.

Pivot tables are useful for comparison. They can become hard to read if there are too many columns.

Crosstabs

Crosstab counts category combinations.

pd.crosstab(enrolments["course_level"], enrolments["status"])

This is useful for quick frequency checks.

Wide and long data

Wide data has many measurement columns.

Example:

Long data stores the same idea in rows.

Long data is often easier for plotting and grouping.

Use melt:

long = wide.melt(
    id_vars=["learner_id"],
    var_name="week",
    value_name="minutes"
)

Mini worked example

Question:

> Which course has the highest average weekly activity?

Plan:

1. Group by course and week.
2. Sum minutes.
3. Group again by course.
4. Average weekly totals.

course_week = (
    activity
    .groupby(["course_name", "week_number"])
    .agg(total_minutes=("minutes_spent", "sum"))
    .reset_index()
)

course_average = (
    course_week
    .groupby("course_name")
    .agg(avg_weekly_minutes=("total_minutes", "mean"))
    .reset_index()
)

Interpretation:

> The result is one row per course. It summarizes recorded activity, not all learning time.

Takeaway

Summaries are powerful only when the grain is clear. In the next module, we combine multiple tables and learn how to check that joins did not change the data by accident.