Module 5 Lab Companion

↑ Back to Lab Contents

Module 5 Learning Goals

In this lab you use the nycflights13 flights to practice the core transformation moves analysts use every day. By the end of the lab you should be able to:

• Filter rows with query().
• Sort rows with sort_values().
• Select columns with double brackets [[...]], .loc[] and .filter().
• Rename columns with .rename().
• Create derived columns with .assign().
• Explain what a transformation changes, and export a transformed dataset with to_csv().

The main idea: transformation changes the form or usefulness of row-level data without changing what one row represents. Throughout this lab, one row is still one scheduled flight. This week there is no aggregation, reshaping or joining; those come in Modules 7 and 8.

↑ Back to Lab Contents

Before You Start: Repository and Setup

Weeks 5 and 6 use a new repository. Both the Module 5 and the Module 6 notebooks are in it.

1. Open the template repository tianhaiz/ban6003-week-05-06-pandas-transformation-template on GitHub (the link is in the Canvas lab guide). Make sure you pick the week 5–6 template, not an older one.
2. Select Use this template, then Create a new repository. Use a clear name, for example ban6003-week-05-06- 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.
5. Open notebooks/module5_basic_transformation_guided_lab.ipynb and run the cells from the top.

Local path (optional): clone the repository with GitHub Desktop, select Open in Visual Studio Code, trust the folder when VS Code asks, and choose the base conda environment as the kernel.

Load the packages

import nycflights13
import pandas as pd

print("Setup complete.")

↑ Back to Lab Contents

Part 1: Load and Understand the Flights Dataset

1.1 Make a working copy with a short name

flights = nycflights13.flights.copy()

flights.head()

flights.shape

flights.columns

1.2 The columns this lab uses

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

↑ Back to Lab Contents

Part 2: Filter Rows with query()

Filtering keeps only the rows that meet a business condition, such as flights in January. query() lets you write the condition almost like a sentence, inside quotation marks.

Start with one condition

# Flights in January
january_flights = flights.query("month == 1")

january_flights.head()

month == 1 asks an equality question. Use two equal signs to compare; one equal sign assigns a value, and three do not work.

Combine conditions

# Flights on February 2
feb_2_flights = flights.query("month == 2 & day == 2")

feb_2_flights.head()

# Flights on February 2 that departed late
feb_2_late_departures = flights.query("month == 2 & day == 2 & dep_delay > 0")

feb_2_late_departures.head()

& means AND: every condition joined by & must be true for a row to stay.

>, < and their variants only make sense for numbers. in works for numbers and for text, such as airport codes.

Lists, very late arrivals and missing values

# Flights in November or December
holiday_season_flights = flights.query("month in [11, 12]")

holiday_season_flights.head()

# Flights that arrived more than two hours late
very_late_arrivals = flights.query("arr_delay >= 120")

very_late_arrivals.head()

The comment says “more than two hours”, but the code uses >=, which also keeps flights exactly 120 minutes late. The two give different counts: 10,200 flights with >= 120 and 10,034 with > 120. Always match the operator to the words of the question.

# Flights with missing tail numbers
missing_tailnum = flights.query("tailnum.isna()", engine="python")

missing_tailnum.head()

query() versus Boolean indexing

feb_2_late_departures_alt = flights[
    (flights["month"] == 2) &
    (flights["day"] == 2) &
    (flights["dep_delay"] > 0)
]

feb_2_late_departures_alt.head()

Both give the same 225 rows. Boolean indexing builds a True/False value for every row and keeps the True rows; it is longer and harder to read, but it can use any custom Python logic. In this course, prefer query() because readability matters.

↑ Back to Lab Contents

Your Turn 1: Filtering

# 1. Flights with arrival delay of two or more hours
late_arrivals = flights.query("arr_delay >= 120")

late_arrivals.shape[0]

10200

# 2. Flights to Houston: IAH or HOU
houston_flights = flights.query("dest in ['IAH', 'HOU']")

houston_flights.shape[0]

9313

# 3. Arrived more than two hours late but did not leave late
late_arrival_not_late_departure = flights.query("arr_delay > 120 & dep_delay <= 0")

late_arrival_not_late_departure.shape[0]

29

↑ Back to Lab Contents

Part 3: Sort Rows with sort_values()

Sorting does not remove rows. It changes their order so the cases you care about rise to the top. By default the smallest value comes first (ascending); ascending=False puts the largest first.

3.1 Smallest departure delays first

# Flights with the smallest departure delays first
flights.sort_values("dep_delay").head()

3.2 Largest departure delays first

# Flights with the largest departure delays first
flights.sort_values("dep_delay", ascending=False).head()

Sort by more than one column

# Sort by multiple variables
# First by departure delay, then by arrival delay
flights.sort_values(["dep_delay", "arr_delay"]).head()

Put the column names in a list, inside brackets. The first column is the main sort key; the second column only matters when two rows tie on the first. Columns such as month and day have many ties, so they are good for trying this.

# Mixed sort directions:
# departure delay descending, arrival delay ascending
flights.sort_values(["dep_delay", "arr_delay"], ascending=[False, True]).head()

↑ Back to Lab Contents

Your Turn 2: Sorting

# 1. Largest departure delays
flights.sort_values("dep_delay", ascending=False).head()

# 2. Earliest departure times
flights.sort_values("dep_time").head()

# 3. Longest distances
flights.sort_values("distance", ascending=False).head()

# 4. Shortest distances
flights.sort_values("distance").head()

↑ Back to Lab Contents

Part 4: Select Columns

Column selection keeps the variables you need and removes clutter. A smaller working table is easier to read, debug and explain.

Single brackets versus double brackets

one_column_series = flights["year"]

type(one_column_series)

one_column_dataframe = flights[["year"]]

type(one_column_dataframe)

Select several columns

date_delay_columns = flights[["year", "month", "day", "dep_delay", "arr_delay"]]

date_delay_columns.head()

# Equivalent selection using .loc[row_selection, column_selection]
date_delay_columns_loc = flights.loc[:, ["year", "month", "day", "dep_delay", "arr_delay"]]

date_delay_columns_loc.head()

.loc[rows, columns] (“location”) takes a row selection and a column selection. The colon : means all rows. Both cells give the same five columns. As a beginner, start with double brackets; mixing many styles only adds confusion.

Find columns by keyword

flights.filter(like="dep").head()

Select from a requested list safely

requested_columns = ["MONTH", "month", "day", "dep_delay", "arr_delay"]

available_columns = flights.columns.intersection(requested_columns)

available_columns

flights[available_columns].head()

Why does single brackets give a DataFrame here? available_columns is already a list-like set of names, so flights[available_columns] works like double brackets.

↑ Back to Lab Contents

Your Turn 3: Selecting Columns

# 1. Include a variable multiple times
repeated_columns = flights[["month", "month", "day"]]

repeated_columns.head()

# 2. Select only available variables from the list
var_list = ["MONTH", "month", "day", "dep_delay", "arr_delay"]

flights[flights.columns.intersection(var_list)].head()

# 3. Select variables that contain "time" in the name
flights.filter(like="time").head()

↑ Back to Lab Contents

Part 5: Rename Columns

5.1 A smaller working table

flight_sml = flights[["year", "month", "day", "arr_delay", "dep_delay", "air_time", "distance"]].copy()

flight_sml.head()

5.2 Rename two columns

flight_sml = flight_sml.rename(columns={
    "arr_delay": "arrival_delay",
    "dep_delay": "departure_delay"
})

flight_sml.head()

rename() takes a dictionary in columns=: each key is an old name and each value is the new name. One dictionary can rename many columns at once.

↑ Back to Lab Contents

Part 6: Create Derived Columns with assign()

A derived column is a new column calculated from existing ones. assign() adds it to the table: the new name goes on the left of = and the calculation on the right. You can create several columns in one call.

6.1 Delay change and air time in hours

flight_sml = flights[["year", "month", "day", "arr_delay", "dep_delay", "air_time", "distance"]].copy()

flight_sml = flight_sml.assign(
    delay_change_minutes = flight_sml["arr_delay"] - flight_sml["dep_delay"],
    air_time_hours = flight_sml["air_time"] / 60
)

flight_sml.head()

6.2 Speed in miles per hour

flight_sml = flight_sml.assign(
    speed_mph = flight_sml["distance"] / flight_sml["air_time_hours"]
)

flight_sml.head()

↑ Back to Lab Contents

Your Turn 4: Create Derived Columns

# Create flight_efficiency with air_time and distance
flight_efficiency = flights[["air_time", "distance"]].copy()

# Add distance_km
flight_efficiency = flight_efficiency.assign(
    distance_km = flight_efficiency["distance"] * 1.61
)

# Add minutes_per_km
flight_efficiency = flight_efficiency.assign(
    minutes_per_km = flight_efficiency["air_time"] / flight_efficiency["distance_km"]
)

flight_efficiency.head()

   air_time  distance  distance_km  minutes_per_km
0     227.0      1400      2254.00        0.100710
1     227.0      1416      2279.76        0.099572
2     160.0      1089      1753.29        0.091257
3     183.0      1576      2537.36        0.072122
4     116.0       762      1226.82        0.094553

↑ Back to Lab Contents

Part 7: Put the Transformation Steps Together

Scenario: an operations analyst needs a reusable row-level file of flights departing JFK in June, July and August, for a later delay review. The workflow is filter → select → rename → derive → sort, and each row is still one flight.

7.1 The full workflow in one cell

# Step 1: filter to the business scope and usable flight records
transformed_flights = flights.query(
    "origin == 'JFK' & month in [6, 7, 8] & air_time.notna() & distance.notna()",
    engine="python",
)

# Step 2: select useful columns
transformed_flights = transformed_flights[[
    "year", "month", "day", "carrier", "origin", "dest",
    "dep_delay", "arr_delay", "air_time", "distance"
]]

# Step 3: rename columns for clarity
transformed_flights = transformed_flights.rename(columns={
    "dep_delay": "departure_delay",
    "arr_delay": "arrival_delay"
})

# Step 4: create derived columns with explicit units and direction
transformed_flights = transformed_flights.assign(
    delay_change_minutes=(
        transformed_flights["arrival_delay"] - transformed_flights["departure_delay"]
    ),
    air_time_hours=transformed_flights["air_time"] / 60,
    speed_mph=(
        transformed_flights["distance"] / (transformed_flights["air_time"] / 60)
    ),
)

# Step 5: put the most delayed arrivals first
transformed_flights = transformed_flights.sort_values(
    "arrival_delay", ascending=False
).reset_index(drop=True)

transformed_flights.head()

Short reflection

Write 3–4 sentences in the notebook: what one row in transformed_flights represents and which flights the business filter excludes; what a positive versus a negative delay_change_minutes means; and why speed_mph is only an approximate airborne speed. The notes in Parts 6 and 7 point you to the evidence.

↑ Back to Lab Contents

Part 8: Export the Transformed Dataset

Save the file

from pathlib import Path

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

output_path = output_dir / "flights_transformed_module5.csv"
transformed_flights.to_csv(output_path, index=False)

print(f"Saved {len(transformed_flights):,} rows to: {output_path}")

↑ Back to Lab Contents

Part 9: Offline Assignment: Operational Delay Review

Offline assignment reflection

Write 4–5 sentences: what one row represents, why this file is not representative of all flights, what delay_change_minutes adds to the review, and one question the data cannot answer by itself.

↑ 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 short reflection, the offline assignment and its reflection.
2. Restart the kernel and run all cells from top to bottom. Confirm there are no errors.
3. Confirm that both files exist in data/processed: flights_transformed_module5.csv and jfk_summer_delay_review_top50.csv.
4. Save the notebook, then commit and push (Source Control panel in Codespaces, or GitHub Desktop locally).
5. Open your repository on GitHub and check that the notebook and both files are there, then submit the repository link in Canvas.

↑ Back to Lab Contents

Lab Wrap-Up

You used the core pandas transformation moves as one connected grammar:

• query(): decide which rows belong in the business scope.
• sort_values(): decide which cases appear first.
• [[...]], .loc[], .filter(): decide which variables belong in the working view.
• rename(): make variable names clearer.
• assign(): create variables with an explicit meaning and unit.
• to_csv(): turn the result into a reusable file.

Before submitting, make sure you can explain what each transformation changed and what one row still means afterward. Module 6 applies the same habits to text and category columns.