Module 11 Lab Companion

↑ Back to Lab Contents

Module 11 Learning Goals

Module 11 is the last preparation step before modeling. There are few new functions this week; you reuse what you already know (groupby, merge, isna, describe, .loc) to answer one question: is this ABT ready for modeling, handoff and business interpretation?

1. Run final sanity checks on a prepared ABT: row meaning and duplicate keys, missing values, impossible or suspicious values, and target variable issues.
2. Create a data dictionary: one dictionary row for each ABT column.
3. Write a transformation and integration summary, with findings and limitations.
4. Gather your checks into a validation checklist and prepare a handoff package.
5. Apply the same process to your semester project ABT.

↑ Back to Lab Contents

Before You Start: Repository and Setup

Start from the Week 11 template repository, create your own repository with Use this template > Create a new repository, and open notebooks/module11_abt_documentation_guided_lab.ipynb in Codespaces or local VS Code. The notebook builds its own demo ABT from nycflights13, so no data files or database connection are needed.

# Codespaces: nycflights13 is installed from requirements.txt.
# Local only: if the import below fails, uncomment and run this line:
# %pip install nycflights13 "setuptools<81"
# (setuptools<81 is needed because nycflights13 uses pkg_resources, which newer setuptools removed.)

import nycflights13
import pandas as pd
from pathlib import Path

repo_root = Path.cwd()
if not (repo_root / "README.md").exists() and (repo_root.parent / "README.md").exists():
    repo_root = repo_root.parent

output_dir = repo_root / "outputs"
output_dir.mkdir(parents=True, exist_ok=True)

flights = nycflights13.flights.copy()
airports = nycflights13.airports.copy()
airlines = nycflights13.airlines.copy()

↑ Back to Lab Contents

Section 1: Build a Demo ABT

The demo ABT has one row per origin-destination route. It is built in two steps you already know: aggregate the flight-level table to the route level, then add destination details with a left merge.

1.1 Aggregate flights to one row per route

route_abt = (
    flights
    .groupby(["origin", "dest"])
    .agg(
        flight_count=("flight", "count"),
        avg_dep_delay=("dep_delay", "mean"),
        avg_arr_delay=("arr_delay", "mean"),
        max_arr_delay=("arr_delay", "max"),
        avg_distance=("distance", "mean"),
        avg_air_time=("air_time", "mean")
    )
    .reset_index()
)

route_abt.head()

1.2 Add destination airport details with a left merge

dest_airports = airports[["faa", "name", "lat", "lon"]].rename(
    columns={
        "faa": "dest",
        "name": "dest_airport_name",
        "lat": "dest_lat",
        "lon": "dest_lon"
    }
)

route_abt = route_abt.merge(dest_airports, how="left", on="dest")

route_abt.head()

↑ Back to Lab Contents

Section 2: Row Meaning and Shape

Before checking any values, confirm what one row means. For this ABT, one row should be one origin-destination route, so the key columns are origin and dest.

2.1 Shape and key columns

route_abt.shape

route_abt[["origin", "dest"]].head()

2.2 Check duplicate entity rows

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

↑ Back to Lab Contents

Your Turn 1: A Different Grain

route_carrier = (
    flights
    .groupby(["origin", "dest", "carrier"])
    .size()
    .reset_index(name="flight_count")
)

print("rows:", len(route_carrier))
print("duplicate origin-dest keys:", route_carrier[["origin", "dest"]].duplicated().sum())

↑ Back to Lab Contents

Section 3: Missing Values

Missing values are not always wrong, but they must be understood: they affect modeling, interpretation, business recommendations and whether a column should be used at all.

missing_summary = route_abt.isna().sum().reset_index()
missing_summary.columns = ["column_name", "missing_count"]
missing_summary["missing_percent"] = (missing_summary["missing_count"] / len(route_abt) * 100).round(1)

missing_summary.sort_values("missing_count", ascending=False)

Questions to ask

For any column with missing values: Is this missingness expected? Did it come from the raw data? Did it come from an integration step? Should the column be kept, dropped or imputed later? Should the limitation be documented?

↑ Back to Lab Contents

Your Turn 2: One Column with Missing Values

route_abt[route_abt["dest_airport_name"].isna()][
    ["origin", "dest", "flight_count", "dest_airport_name"]
]

↑ Back to Lab Contents

Section 4: Impossible or Suspicious Values

Some values are not missing but still impossible or suspicious. Not every extreme value is wrong, but every one should be noticed and explained.

4.1 Summary statistics

route_abt.describe()

4.2 Basic impossible-value checks

# Basic impossible-value checks
checks = {
    "negative_flight_count": (route_abt["flight_count"] < 0).sum(),
    "zero_or_negative_distance": (route_abt["avg_distance"] <= 0).sum(),
    "zero_or_negative_air_time": (route_abt["avg_air_time"] <= 0).sum(),
}

checks

4.3 Inspect the routes with the highest average arrival delay

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

↑ Back to Lab Contents

Your Turn 3: A Negative Maximum Delay

route_abt[route_abt["max_arr_delay"] < 0][["origin", "dest", "flight_count", "max_arr_delay"]]

↑ Back to Lab Contents

Section 5: Target Variable Issues

If the ABT will be used for modeling, the target must be clearly defined. The notebook sets up a business question: an airline planning team wants to estimate, before a new schedule season starts, how late flights on each route will arrive on average, so it can add time buffers to the schedule.

• Target: avg_arr_delay, in minutes. This is the value a model would predict.
• Predictors (features): route information known when the schedule is planned, such as avg_distance, flight_count and the destination location.

5.1 Missing values and distribution of the target

route_abt["avg_arr_delay"].isna().sum()

route_abt["avg_arr_delay"].describe()

5.2 Questions to ask about the target

• What exactly does the target measure, and in which unit?
• Is it missing for some rows?
• When is it known? Only after the flights have arrived. The target is never known at the time of prediction; that is why we predict it.
• Is every predictor known at the time of prediction? A predictor that is not yet known then is data leakage.
• Are there extreme values to review?
• Does the target support the business question?

5.3 Which flights does the target cover?

arr_delay_counts = (
    flights.groupby(["origin", "dest"])["arr_delay"].count()
    .rename("flights_with_arr_delay").reset_index()
)
target_coverage = route_abt[["origin", "dest", "flight_count", "avg_arr_delay"]].merge(
    arr_delay_counts, how="left", on=["origin", "dest"]
)
target_coverage["flights_without_arr_delay"] = (
    target_coverage["flight_count"] - target_coverage["flights_with_arr_delay"]
)

print("Flights without an arrival delay, all routes:", target_coverage["flights_without_arr_delay"].sum())
target_coverage.sort_values("flights_without_arr_delay", ascending=False).head()

5.4 Columns that are not known at the time of prediction

The planning team estimates before the season starts. max_arr_delay and avg_dep_delay are measured on the same flights as the target, so they exist only after those flights have flown.

route_abt[["avg_arr_delay", "avg_dep_delay", "max_arr_delay"]].corr().round(2)

↑ Back to Lab Contents

Your Turn 4: What the Target Covers

↑ Back to Lab Contents

Section 6: Create a Data Dictionary

A data dictionary explains what each ABT column means. Each row of the data dictionary describes one ABT column, so the 11-column route ABT needs 11 dictionary rows. Each row has the column name, data type, description, source, transformation or derivation, and notes or concerns.

6.1 Create the dictionary template

data_dictionary = pd.DataFrame({
    "column_name": route_abt.columns,
    "data_type": [str(route_abt[col].dtype) for col in route_abt.columns],
    "description": "",
    "source": "",
    "transformation_or_derivation": "",
    "notes_or_concerns": ""
})

data_dictionary

6.2 Fill in five dictionary rows as examples

doc_fields = ["description", "source", "transformation_or_derivation", "notes_or_concerns"]

# One entry per ABT column (= one dictionary row). The four values follow the order of doc_fields.
example_entries = {
    "origin": [
        "Origin airport code for the route", "flights table",
        "Used as part of the route-level grouping key",
        "Forms the route key together with dest; only the three New York City airports (EWR, JFK, LGA)"
    ],
    "dest": [
        "Destination airport code for the route", "flights table",
        "Used as part of the route-level grouping key",
        "Includes one unusual route, EWR to LGA, with a single cancelled flight"
    ],
    "flight_count": [
        "Number of flights observed for the route", "flights table",
        "Count of flight records grouped by origin and destination",
        "Includes cancelled flights; ranges from 1 to 11,262; 10 routes have fewer than 10 flights, so their "
        "averages are unstable"
    ],
    "avg_arr_delay": [
        "Average arrival delay for flights on the route, in minutes (target)", "flights table",
        "Mean of arr_delay grouped by origin and destination",
        "Averages only flights with a recorded arrival delay; missing for EWR to LGA (its only flight was "
        "cancelled); negative means early on average"
    ],
    "dest_airport_name": [
        "Full name of the destination airport", "airports table",
        "Joined from airports using dest = faa",
        "Missing for 7 routes whose destination (BQN, PSE, SJU, STT) is not in the airports table"
    ],
}

for abt_column, values in example_entries.items():
    data_dictionary.loc[data_dictionary["column_name"] == abt_column, doc_fields] = values

data_dictionary

↑ Back to Lab Contents

Your Turn 5: Document One ABT Column

your_turn_entries = {
    "avg_dep_delay": [
        "Average departure delay for flights on the route, in minutes",
        "flights table",
        "Mean of dep_delay grouped by origin and destination",
        "Missing for EWR to LGA (its only flight was cancelled); measured on the same flights as the target, "
        "so not known before the season and must not be used as a predictor (correlation 0.83 with avg_arr_delay)"
    ],
}

for abt_column, values in your_turn_entries.items():
    data_dictionary.loc[data_dictionary["column_name"] == abt_column, doc_fields] = values

data_dictionary[data_dictionary["column_name"] == "avg_dep_delay"]

↑ Back to Lab Contents

Section 7: Transformation and Integration Summary

The data dictionary documents columns; the transformation summary documents the process: the raw data, cleaning and filtering, transformations, aggregations that changed the unit of analysis, joins, checks, and remaining assumptions or limitations.

The notebook's example lists eight steps for the demo ABT, from starting with flights to naming avg_arr_delay as the possible target.

↑ Back to Lab Contents

Your Turn 6: Findings and Limitations

↑ Back to Lab Contents

Section 8: Prepare a Handoff Package

A complete handoff package usually has five parts: the final ABT file, a complete data dictionary, a transformation and integration summary, a validation checklist, and a README or short handoff note.

8.1 What a validation checklist is

A validation checklist gathers the validation code you already ran into one table: one row per check, with the number the check found (result), a status and a short note. Each check from Sections 2–6 becomes one row, for example the Section 2 duplicate check becomes "Duplicate route keys". The three statuses:

• Pass: the check found no problem (the result is 0).
• Documented: the check found something real, and the data dictionary notes explain it.
• Needs attention: a problem that must be fixed or explained before handoff.

8.2 The first two checklist rows

checklist_rows = [
    {
        "check": "Duplicate route keys (origin + dest)",
        "result": route_abt[["origin", "dest"]].duplicated().sum(),
        "status": "Pass",
        "notes": "Each route appears once",
    },
    {
        "check": "Missing key values (origin, dest)",
        "result": route_abt[["origin", "dest"]].isna().sum().sum(),
        "status": "Pass",
        "notes": "Every row has both key values",
    },
]

pd.DataFrame(checklist_rows)

↑ Back to Lab Contents

Section 9: Offline Assignment: Handoff Package

# Offline Assignment, step 3 - export the three handoff files. Run this after the checklist cell.
unfinished = validation_checklist["result"].isna() | validation_checklist[["status", "notes"]].eq("TODO").any(axis=1)
if unfinished.any():
    print("Complete these checklist rows before exporting:", list(validation_checklist.loc[unfinished, "check"]))
else:
    handoff_files = {
        "route_abt_module11.csv": route_abt,
        "route_abt_data_dictionary_module11.csv": data_dictionary,
        "route_abt_validation_checklist_module11.csv": validation_checklist,
    }
    for file_name, table in handoff_files.items():
        table.to_csv(output_dir / file_name, index=False)
        print("Saved:", output_dir / file_name)

Handoff decision

Write 5–6 sentences that approve the handoff, approve it with conditions, or hold it for revision. Cite at least four results from your checklist, name one remaining limitation or target concern, and state what the receiving analyst should know before modeling.

↑ Back to Lab Contents

Section 10: Apply This to Your Project (After the Lab)

Not part of the lab grade. When you prepare your project ABT, use the same process: a clear statement of the unit of analysis, a summary of your validation checks (a checklist works well), a data dictionary, a transformation and integration summary, and a clean handoff folder in GitHub.

↑ Back to Lab Contents

Section 11: Final Reflection

This is a graded written answer about your own project ABT, so the companion will not write it for you. In 3–4 sentences, answer from what you know now; you do not need to build anything:

1. What does one row mean, and which columns are the key?
2. What is your likely target, and is it known for every row?
3. Which check from this lab will you run first on your project ABT, and which column will need the clearest dictionary entry?

Short, specific answers are enough. Leaving the reflection empty loses points.

↑ Back to Lab Contents

Optional Practice: Diagnose a Mini Handoff Package (Not Graded)

A teammate sends a four-row route ABT and its dictionary. Before repairing anything, build a validation checklist named practice_checklist with the columns check and result: duplicate keys, missing key values, missing target values, ABT columns without a dictionary row, and blank dictionary cells.

text_fields = ["description", "source", "transformation_or_derivation", "notes_or_concerns"]

practice_checklist = pd.DataFrame({
    "check": [
        "Duplicate route keys",
        "Missing key values",
        "Missing target values",
        "ABT columns without a dictionary row",
        "Blank dictionary cells",
    ],
    "result": [
        practice_abt.duplicated(["origin", "dest"]).sum(),
        practice_abt[["origin", "dest"]].isna().sum().sum(),
        practice_abt["avg_arr_delay"].isna().sum(),
        len(set(practice_abt.columns) - set(practice_dictionary["column_name"])),
        practice_dictionary[text_fields].fillna("").astype(str)
            .apply(lambda col: col.str.strip()).eq("").sum().sum(),
    ],
})
practice_checklist

↑ Back to Lab Contents

Save and Submit

1. Complete Your Turn 1–6, the offline assignment (dictionary, validation checklist, handoff decision) and the final reflection.
2. Restart the kernel and run all cells from top to bottom; confirm there are no errors.
3. Confirm the three files exist in outputs: route_abt_module11.csv, route_abt_data_dictionary_module11.csv and route_abt_validation_checklist_module11.csv.
4. Save, then commit and push: git add ., git commit -m "Complete Module 11 ABT documentation lab", git push. Submit the repository link in Canvas (the new repository, if you recreated it).

↑ Back to Lab Contents

Lab Wrap-Up

Before an ABT is used for modeling, check and document it:

• Grain: what one row means, its key columns, and a duplicate check on the key.
• Missing and suspicious values: where they come from, investigated and documented rather than silently removed.
• Target: what it measures, which records it covers, and which columns would not be known at the time of prediction.
• Documentation: a dictionary row for every column, a summary of the process with findings, and a checklist that gathers the checks.

Next, Module 12 starts modeling with linear regression, and the time-of-prediction question from Section 5 comes back as a central idea.