Module 7 Lab Companion

↑ Back to Lab Contents

Module 7 Learning Goals

In this lab you return to the nycflights13 flights and answer manager questions with two kinds of moves. Aggregation summarizes many rows into fewer rows, for example one row per carrier. Reshaping rearranges the same values between a wide layout and a long layout, without changing them. By the end of the lab you should be able to:

• Summarize flights with groupby() and agg(), including named aggregation.
• Turn a grouped result back into a regular table with reset_index().
• Say what one row means after each summary: a carrier, a month, a route or a carrier-month.
• Reshape wide to long with melt(), and long to wide with pivot() or pivot_table().
• Run three light validation checks after a transformation: row count, unique keys and missing values.

The main idea: aggregation and reshaping can change what one row means. Say the new meaning out loud, then check it.

You will use only four main tools: groupby() with agg(), and melt() with pivot(). Combined, they can do a lot.

↑ Back to Lab Contents

Before You Start: Repository and Setup

Weeks 7 and 8 use a new repository. Both the Module 7 and the Module 8 notebooks are in it.

1. Open the template repository tianhaiz/ban6003-week-07-08-aggregation-integration-template on GitHub (the link is in the Canvas lab guide).
2. Select Use this template, then Create a new repository. Use a clear name, for example ban6003-week-07-08- plus your name.
3. Keep it public if you can. If you make it private, invite the instructor account zzz1990771 as a collaborator, or the instructor cannot grade it.
4. Open your new repository in GitHub Codespaces. A new Codespace usually needs 4–5 minutes the first time; reopening it later is much faster.
5. Open notebooks/module7_aggregation_reshaping_guided_lab.ipynb and run the cells from the top. If you restart the kernel, run from the top again.

Optional local path: clone your repository, open the folder in Visual Studio Code and trust the folder when VS Code asks. Choose the base conda environment as the kernel and install requirements.txt once.

Load the packages

import nycflights13
import pandas as pd
from pathlib import Path

↑ Back to Lab Contents

Part 1: What Does One Row Mean Now?

Before you transform anything, be crystal clear about the current unit of analysis: what one row represents. Every later step changes it, so this is your starting point.

flights = nycflights13.flights.copy()
flights.head()

flights.shape

flights.columns

flights[["year", "month", "day", "carrier", "flight", "origin", "dest", "dep_delay", "arr_delay", "distance", "air_time"]].head()

↑ Back to Lab Contents

Part 2: Aggregation with groupby() and agg()

Business question: which airline carrier had the highest average departure delay?

Before you write code, answer two questions. What should one row of the answer be? One carrier, not one flight. Which column do you summarize, and how? dep_delay, with the mean.

The pattern is data.groupby("grouping_column").agg({"value_column": "summary_function"}). The column in groupby() sets the new row level. The dictionary in agg() says which column to summarize (the key) and how (the value). Common summary functions are "mean", "median", "count", "max", "min" and "std".

2.1 Average departure delay by carrier

carrier_delay = flights.groupby("carrier").agg({
    "dep_delay": "mean"
})

carrier_delay.head()

2.2 Turn the result back into a regular table

carrier_delay = flights.groupby("carrier").agg({
    "dep_delay": "mean"
}).reset_index()

carrier_delay.head()

2.3 Sort to find the highest average delay

carrier_delay.sort_values("dep_delay", ascending=False).head()

↑ Back to Lab Contents

What Changed in the Row?

The original table had one row per flight. The summary has one row per carrier. So dep_delay no longer means “the delay of one flight”. It now means “the average departure delay of that carrier”. The column kept its name, but its meaning changed. That is why you should always write down what one row means after a summary.

↑ Back to Lab Contents

Your Turn 1: Lowest Average Arrival Delay

carrier_arr_delay = flights.groupby("carrier").agg({
    "arr_delay": "mean"
}).reset_index()

carrier_arr_delay.sort_values("arr_delay").head()

   carrier  arr_delay
2       AS  -9.930889
8       HA  -6.915205
1       AA   0.364291
4       DL   1.644341
13      VX   1.764464

↑ Back to Lab Contents

Part 3: Multiple Aggregations and Named Aggregation

Business question: for each carrier, what are the average departure delay, the maximum departure delay and the number of flights?

An average alone can hide risk. A carrier can keep a good average and still have a few extreme delays, and those few delays hurt customers a lot. A very small carrier can also get an extreme average from only a few flights. So you summarize several things at once.

3.1 Average, maximum and count in one summary

carrier_summary = flights.groupby("carrier").agg(
    avg_dep_delay=("dep_delay", "mean"),
    max_dep_delay=("dep_delay", "max"),
    flight_count=("flight", "count")
).reset_index()

carrier_summary.head()

3.2 Sort by average departure delay

carrier_summary.sort_values("avg_dep_delay", ascending=False).head(10)

↑ Back to Lab Contents

Your Turn 2: A Monthly Delay Summary

month_summary = flights.groupby("month").agg(
    avg_arr_delay=("arr_delay", "mean"),
    median_arr_delay=("arr_delay", "median"),
    flight_count=("flight", "count")
).reset_index()

month_summary.sort_values("avg_arr_delay", ascending=False)

    month  avg_arr_delay  median_arr_delay  flight_count
6       7      16.711307              -2.0         29425
5       6      16.481330              -2.0         28243
11     12      14.870355               2.0         28135
3       4      11.176063              -2.0         28330
...

↑ Back to Lab Contents

Part 4: Aggregating at Two Levels: Carrier and Month

Business question: do some carriers have worse delays in certain months? Now one row should be one carrier in one month. To group by two columns, put both names in a list: groupby(["carrier", "month"]).

4.1 Group by carrier and month

carrier_month_delay = flights.groupby(["carrier", "month"]).agg(
    avg_arr_delay=("arr_delay", "mean"),
    flight_count=("flight", "count")
).reset_index()

carrier_month_delay.head()

4.2 Sort by average arrival delay

carrier_month_delay.sort_values("avg_arr_delay", ascending=False).head(10)

Light validation after aggregation

After a groupby(), run three quick checks. Each one catches a different mistake.

Check 1: the number of rows

carrier_month_delay.shape

Check 2: unique keys

carrier_month_delay[["carrier", "month"]].duplicated().sum()

Check 3: missing values

carrier_month_delay.isna().sum()

↑ Back to Lab Contents

Your Turn 3: A Route-Level Summary

route_summary = flights.groupby(["origin", "dest"]).agg(
    flight_count=("flight", "count"),
    avg_arr_delay=("arr_delay", "mean"),
    avg_distance=("distance", "mean")
).reset_index()

route_summary.sort_values("flight_count", ascending=False).head()

    origin dest  flight_count  avg_arr_delay  avg_distance
117    JFK  LAX         11262      -0.480599        2475.0
156    LGA  ATL         10263      11.322478         762.0
204    LGA  ORD          8857       1.833431         733.0
...

route_summary[["origin", "dest"]].duplicated().sum()

np.int64(0)

↑ Back to Lab Contents

Part 5: Wide and Long Data

Aggregation summarizes. Reshaping keeps the same values and changes only how they are arranged.

• A wide table uses several columns for categories or time periods, for example one column per month. It is easy for people to read, which is why reports and pivot tables are usually wide.
• A long table keeps those categories in one column, for example a month column. It is easier to plot, group and model.

To go long to wide you pivot. To go wide back to long you “melt” the pivot table, with melt().

↑ Back to Lab Contents

Part 6: Wide to Long with melt()

Business question: a manager sends a small report with one column per month. How can you reshape it so each row is one carrier-month?

The next three cells only build that small wide “manager report”, so you have something to melt. Run them; you do not need to study the code yet.

monthly_carrier = flights.groupby(["carrier", "month"]).agg(
    avg_arr_delay=("arr_delay", "mean")
).reset_index()

monthly_carrier.head()

small_summary = monthly_carrier.query("carrier in ['AA', 'DL', 'UA'] and month in [1, 2, 3]")
small_summary

6.1 The manager's wide report

wide_delay = small_summary.pivot(
    index="carrier",
    columns="month",
    values="avg_arr_delay"
).reset_index()

wide_delay

6.2 Melt the wide report

long_delay = wide_delay.melt(
    id_vars="carrier",
    var_name="month",
    value_name="avg_arr_delay"
)

long_delay

↑ Back to Lab Contents

Your Turn 4: Practice melt()

long_delay_practice = wide_delay.melt(
    id_vars="carrier",
    var_name="delay_month",
    value_name="average_delay"
)

long_delay_practice

  carrier delay_month  average_delay
0      AA           1       0.982379
1      DL           1      -4.404651
2      UA           1       3.175599
...

expected_rows = wide_delay["carrier"].nunique() * (wide_delay.shape[1] - 1)
print("expected:", expected_rows, "| actual:", long_delay_practice.shape[0])

expected: 9 | actual: 9

↑ Back to Lab Contents

Part 7: Long to Wide with pivot() and pivot_table()

Business question: a manager wants a compact comparison with one row per carrier and one column per month. You move from long back to wide.

7.1 pivot() turns the long table back into the report

wide_again = long_delay.pivot(
    index="carrier",
    columns="month",
    values="avg_arr_delay"
).reset_index()

wide_again

7.2 pivot_table() gives the same table here

wide_with_pivot_table = small_summary.pivot_table(
    index="carrier",
    columns="month",
    values="avg_arr_delay",
    aggfunc="mean"
).reset_index()

wide_with_pivot_table

↑ Back to Lab Contents

Your Turn 5: A Wide Carrier-Month Table

carrier_month_wide = carrier_month_delay.pivot_table(
    index="carrier",
    columns="month",
    values="avg_arr_delay",
    aggfunc="mean"
).reset_index()

carrier_month_wide

print("rows:", carrier_month_wide.shape[0])
print("unique carriers:", carrier_month_delay["carrier"].nunique())

rows: 16
unique carriers: 16

↑ Back to Lab Contents

Part 8: Light Validation Checklist

After any aggregation or reshape, ask five questions:

1. What does one row mean now?
2. Did the number of rows change in a way that makes sense?
3. Are the intended keys unique?
4. Did missing values appear or disappear?
5. Do the column names still describe the values?

The notebook runs three of these checks on long_delay:

Repeated carrier-month pairs

long_delay[["carrier", "month"]].duplicated().sum()

Missing values

long_delay.isna().sum()

Number of unique carrier-month pairs

long_delay[["carrier", "month"]].drop_duplicates().shape[0]

↑ Back to Lab Contents

Part 9: Final Practice: Carrier-Month Dashboard Table

output_dir = Path("../data/processed")
output_dir.mkdir(parents=True, exist_ok=True)

if carrier_month_summary is None:
    print("Complete Final Practice Step 1 before exporting.")
else:
    module7_output_path = output_dir / "carrier_month_summary_module7.csv"
    carrier_month_summary.to_csv(module7_output_path, index=False)
    print(f"Saved {len(carrier_month_summary)} rows to: {module7_output_path}")

Final reflection

Write 3–5 sentences in the notebook: what one row means in carrier_month_summary; which transformation changed the row meaning the most; what validation evidence supports the table's structure; and how the table could support an operations decision.

↑ Back to Lab Contents

Part 10: Offline Assignment: Route Performance Summary

Route summary interpretation

Write 4–5 sentences: what one row represents, how the 500-flight filter changes the scope, what validation evidence supports the table, and one decision the summary can support. Do not claim that the table explains the cause of delays.

↑ Back to Lab Contents

Common Problems

If a cell fails, read the last line of the error message first. Then find it below.

Still stuck? Post the error message and the cell number in the course Q&A discussion. Do not post your full answers.

↑ Back to Lab Contents

Save and Submit

1. Complete every Your Turn, the final practice, the offline assignment and both reflections.
2. Restart the kernel and run all cells from top to bottom. Confirm there are no errors.
3. Confirm that both Module 7 files exist in data/processed: carrier_month_summary_module7.csv and route_performance_module7.csv.
4. Save the notebook, then commit and push: git add ., git commit -m "Complete Module 7 aggregation and reshaping lab", git push. In Codespaces you can also use the Source Control panel.
5. Submit your repository link in Canvas. The Module 8 notebook is in the same repository.

↑ Back to Lab Contents

Lab Wrap-Up

You used four tools as one connected pattern:

• groupby() + agg(): choose the new row level and summarize; name the results clearly.
• reset_index(): turn grouped results back into a regular table.
• melt(): wide to long, for plotting and grouping.
• pivot() / pivot_table(): long to wide, for compact reports; watch for duplicates.
• Light validation: row count, unique keys and missing values after every step.

Choose the shape that serves your purpose. A compact table for a report is usually wide; data for a chart is usually long. Your project report should include at least a few charts, so you will want long tables for those. Module 8 builds on this by joining tables with merge(), where the same checks on row counts and keys become even more important.