← All BAN 6003 lab companions

Module 8 Lab Companion

BAN 6003: Data Management and Analytics Integration

Data Integration with Pandas Merge

Experimental pilot. Use the Ask about this lab panel (on the right, or the Ask a question button on small screens) to ask about the section you are reading, or open Common questions for saved answers. Answers explain the companion in plain language and may add general background. Each answer is labelled: instructor-approved answers were approved by your instructor; AI-generated answers are new and not yet reviewed. The notebook is still the authoritative source. Graded work (Part 10: Final Practice: Build an Integrated Flight Table; Part 11: Offline Assignment: Aircraft Match Audit) is not answered for you. Questions about grades, deadlines or extensions go to your instructor.

How to use this companion

Open the Module 8 notebook and this companion at the same time. Work through the companion from top to bottom and run the matching notebook cells as you go. The notebook is the authoritative source for the lab sequence, code and required work. This companion explains what each step means, what to notice in the output and why the step matters.

Read aloud: controls and directions

Before you start: choose a reading mode, speed, and voice below. Select Read Aloud from Beginning to hear the full companion, or use Read Aloud from This Section beside any major section to continue from that point. While audio is playing, a floating control bar remains visible at the bottom of the page. Use Pause to pause temporarily, Resume to continue, and Stop to end the current reading.

Study mode reads explanations, results, tables, and notes while skipping exact Python code. Full mode also reads Python code and verbalizes common programming symbols.

Adjust text-to-speech speed from one half to two times normal speed.

Ready to read.

The read-aloud feature uses your browser's speech engine. A screen reader can access the page independently of these controls.

What is in the notebook, and what this companion adds

Notebook contentCode, official prompts, required tasks and the lab sequence come from the notebook. Each code block shows its notebook cell number.
Companion-added instructor noteThese clearly labeled notes are explanations, cautions or study advice added by this companion, many of them from the in-person lab session. They are not extra required notebook steps unless the note says so.

This companion is designed for keyboard and screen-reader access, but it is not a separate version for students who need accessibility features. It is the same learning guide for everyone. You can read it visually, use a screen reader, or use the built-in read-aloud controls while you work in the notebook.

Use either course environment

↑ Back to Lab Contents

Module 8 Learning Goals

Until now you worked inside one table. In this lab you combine related tables from nycflights13: flights, airlines, airports and planes. The flights table uses short codes such as UA or LAX; the other tables explain those codes. By the end of the lab you should be able to:

The main idea: integration is not just putting tables together. The combined table must still mean what you think it means.

↑ Back to Lab Contents

Before You Start: Repository and Setup

Module 8 uses the same repository as Module 7: your copy of ban6003-week-07-08-aggregation-integration-template. Do not create a new one.

  1. Open your Week 7–8 repository in GitHub Codespaces (or locally in Visual Studio Code with the base conda environment).
  2. Open notebooks/module8_pandas_integration_guided_lab.ipynb.
  3. Run the cells from the top. If you restart the kernel, run from the top again.
import nycflights13
import pandas as pd
from pathlib import Path

Make short working copies of the five tables

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

↑ Back to Lab Contents

A First Look at the Tables

Before any merge, look at each table and ask: what does one row mean, and which column could identify a row?

flights.head()

flights: one row is one flight (336,776 rows).

First rows of airlines

airlines.head()
airports.head()

airports: one row per airport, identified by its FAA code (faa), with name, latitude, longitude, altitude and time zone. The dst column records the daylight saving time rule: A for the standard US rule, U unknown, N no daylight saving time. This lab uses mainly faa and name.

planes.head()

planes: one row per aircraft, identified by its tail number (tailnum), with year built, manufacturer, model and seats.

↑ Back to Lab Contents

Part 1: Keys and Relationships

Before merging, ask two questions: which column connects the tables? and is that key unique in the table you merge from?

1.1 Is carrier unique in airlines? (duplicated)

airlines["carrier"].duplicated().sum()

1.2 Is carrier unique in airlines? (nunique)

airlines["carrier"].nunique(), airlines.shape[0]

1.3 Carrier codes repeat in flights

flights["carrier"].duplicated().sum()

1.4 Flights per carrier

flights["carrier"].value_counts().head()

↑ Back to Lab Contents

Your Turn 1: Airport Keys

Try first

Copy the pattern from Part 1 and change the table and column.

  1. Check whether faa is unique in airports.
  2. Check how often each destination code appears in flights["dest"].
  3. Write one sentence explaining the relationship between flights["dest"] and airports["faa"].
Show sample solution for Your Turn 1A

Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.

airports["faa"].duplicated().sum()

What it prints:

np.int64(0)
Show sample solution for Your Turn 1B

Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.

flights["dest"].value_counts().head()

What it prints:

dest
ORD    17283
ATL    17215
LAX    16174
BOS    15508
MCO    14082
Name: count, dtype: int64

↑ Back to Lab Contents

Part 2: The Basic pd.merge() Pattern

The pattern is pd.merge(left_table, right_table, how="left", left_on="column_in_left", right_on="column_in_right").

The main arguments of pd.merge()
ArgumentMeaning
left_tableThe main table you want to keep
right_tableThe table you add information from
howThe type of join: left (the default), inner, outer or right
left_on / right_onThe key column in each table, when the names differ (dest and faa)
onA shortcut when the key has the same name in both tables (carrier)

↑ Back to Lab Contents

Part 3: Left Join: Keep All Rows from the Main Table

Business question: can we attach the airline name to every flight? Keep all flights and add name from airlines: a classic left join.

3.1 Add airline names to flights

flights_with_airline = pd.merge(
    flights,
    airlines,
    how="left",
    on="carrier"
)

flights_with_airline[["carrier", "name", "flight", "origin", "dest"]].head()

3.2 Check 1: row count before and after

flights.shape[0], flights_with_airline.shape[0]

3.3 Check 2: new missing values

flights_with_airline["name"].isna().sum()

↑ Back to Lab Contents

Your Turn 2: Attach Destination Airports

Try first

The key names differ this time, so use left_on and right_on. Give the result the requested name.

  1. Left join flights (left) with airports (right) using dest and faa. Name it flights_with_dest_airport.
  2. Compare the row counts before and after.
  3. Count the rows with a missing airport name.
Show sample solution for Your Turn 2

Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.

flights_with_dest_airport = pd.merge(
    flights,
    airports,
    how="left",
    left_on="dest",
    right_on="faa"
)

flights_with_dest_airport[["dest", "faa", "name", "lat", "lon"]].head()
Show sample solution for the Your Turn 2 checks

Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.

print(flights.shape[0], flights_with_dest_airport.shape[0])
print(flights_with_dest_airport["name"].isna().sum())

What it prints:

336776 336776
7602

↑ Back to Lab Contents

Part 4: Inner Join: Keep Only Matching Rows

Business question: which flights have aircraft details in the planes table? An inner join keeps only rows whose key appears in both tables. A flight whose tailnum is missing, or not listed in planes, has nothing to match, so the inner join removes it. A left join keeps it and leaves the aircraft columns empty.

4.1 Left and inner joins with planes

flights_planes_left = pd.merge(
    flights,
    planes,
    how="left",
    on="tailnum",
    suffixes=("_flight", "_plane")
)

flights_planes_inner = pd.merge(
    flights,
    planes,
    how="inner",
    on="tailnum",
    suffixes=("_flight", "_plane")
)

flights.shape[0], flights_planes_left.shape[0], flights_planes_inner.shape[0]

4.2 How many flights did the inner join remove?

flights_planes_left.shape[0] - flights_planes_inner.shape[0]

4.3 The aircraft details

flights_planes_left[["tailnum", "carrier", "flight", "manufacturer", "model", "seats"]].head()

↑ Back to Lab Contents

Your Turn 3: Inner Join with Airports

Try first

Reuse your Your Turn 2 code and change one argument.

  1. Inner join flights with airports using dest and faa.
  2. Compare the row count with the original flights table.
  3. In the notebook, answer: did the inner join remove rows, and what does that suggest?
Show sample solution for Your Turn 3

Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.

flights_inner_dest_airport = pd.merge(
    flights,
    airports,
    how="inner",
    left_on="dest",
    right_on="faa"
)

flights.shape[0], flights_inner_dest_airport.shape[0]

What it prints:

(336776, 329174)

↑ Back to Lab Contents

Part 5: Outer Join: Keep Everything from Both Tables

Business question: which airport codes appear in one table but not the other? An outer join keeps all rows from both tables, so it is a good audit tool.

5.1 Audit destination codes against airports

flight_dest_codes = flights[["dest"]].drop_duplicates().rename(columns={"dest": "faa"})

airport_audit = pd.merge(
    flight_dest_codes,
    airports[["faa", "name", "lat", "lon"]],
    how="outer",
    on="faa",
    indicator=True
)

airport_audit["_merge"].value_counts()
airport_audit.head()

5.2 Rows that did not match

airport_audit.query("_merge != 'both'").head(10)

↑ Back to Lab Contents

Your Turn 4: Audit the Origin Airports

Try first

Copy cell 48 and change dest to origin. Use a new table name.

  1. Create the unique origin airport codes from flights and rename the column to faa.
  2. Outer join it with airports using indicator=True, then count the _merge categories.
Show sample solution for Your Turn 4

Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.

flight_origin_codes = flights[["origin"]].drop_duplicates().rename(columns={"origin": "faa"})

origin_audit = pd.merge(
    flight_origin_codes,
    airports[["faa", "name"]],
    how="outer",
    on="faa",
    indicator=True
)

origin_audit["_merge"].value_counts()

What it prints:

_merge
right_only    1455
both             3
left_only        0
Name: count, dtype: int64

↑ Back to Lab Contents

Part 6: Right Join: Keep All Rows from the Right Table

A right join is a left join in reverse: it keeps every row of the right table.

6.1 A right join on 100 flights

right_join_example = pd.merge(
    flights[["carrier", "flight", "origin", "dest"]].head(100),
    airlines,
    how="right",
    on="carrier"
)

right_join_example.head()

6.2 Size of the result

right_join_example.shape

In practice, put the main table on the left and use how="left": the main table is then always obvious.

↑ Back to Lab Contents

Part 7: Duplicate Keys Can Expand Rows

If the right table repeats a key, one left row matches several right rows, and the merge creates extra rows.

fake_airline_regions = pd.DataFrame({
    "carrier": ["AA", "AA", "DL", "UA"],
    "region_label": ["legacy", "large", "legacy", "legacy"]
})

fake_airline_regions
small_flights = flights[["carrier", "flight", "origin", "dest"]].query("carrier in ['AA', 'DL', 'UA']").head(10)

small_flights

7.1 Merge and compare row counts

merged_fake = pd.merge(
    small_flights,
    fake_airline_regions,
    how="left",
    on="carrier"
)

small_flights.shape[0], merged_fake.shape[0]

7.2 See the copies

merged_fake.head(10)

7.3 The cause, in one line

fake_airline_regions["carrier"].duplicated().sum()

↑ Back to Lab Contents

Your Turn 5: Can planes Expand the Flights?

Try first

Check the table that should be unique, then think about which table is on the left.

  1. Check whether tailnum is unique in planes.
  2. Optional: compare row counts after a left join of flights with planes.
  3. In the notebook, explain whether this merge is likely to create unexpected row expansion.
Show sample solution for Your Turn 5A

Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.

planes["tailnum"].duplicated().sum()

What it prints:

np.int64(0)
Show sample solution for Your Turn 5B (optional)

Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.

flights_planes_check = pd.merge(
    flights,
    planes,
    how="left",
    on="tailnum",
    suffixes=("_flight", "_plane")
)

flights.shape[0], flights_planes_check.shape[0]

What it prints:

(336776, 336776)

↑ Back to Lab Contents

Part 8: pd.concat(): Stacking Similar Tables

pd.merge() puts tables side by side using a key. pd.concat() stacks tables that have the same columns.

8.1 Two monthly tables

jan_flights = flights.query("month == 1")
feb_flights = flights.query("month == 2")

jan_flights.shape, feb_flights.shape

8.2 Stack them

jan_feb_flights = pd.concat([jan_flights, feb_flights], axis=0)

jan_feb_flights.shape

8.3 Check the months

jan_feb_flights["month"].value_counts().sort_index()
Arguments of pd.concat()
ArgumentWhat it does
[table1, table2]The tables to stack, inside a list (square brackets)
axis=0Stack rows (the default). The tables should have the same columns.
axis=1Put columns side by side. Rows are aligned by index, so the tables should have the same rows.
ignore_index=TrueNumber the rows 0, 1, 2… again after stacking
jan_feb_flights_clean_index = pd.concat(
    [jan_flights, feb_flights],
    axis=0,
    ignore_index=True
)

jan_feb_flights_clean_index.head()

↑ Back to Lab Contents

Your Turn 6: Stack March and April

Try first

Follow Part 8 with months 3 and 4. Remember the double equal sign in query() and the square brackets in pd.concat().

  1. Create March and April tables and stack them with pd.concat().
  2. Check the row count and the month counts.
Show sample solution for Your Turn 6

Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.

mar_flights = flights.query("month == 3")
apr_flights = flights.query("month == 4")

mar_apr_flights = pd.concat([mar_flights, apr_flights], axis=0)

mar_apr_flights.shape

What it prints:

(57164, 19)
Show sample solution for the Your Turn 6 check

Use this only after you have made your own attempt. Compare the structure and logic, not just the final line of code. This sample solution is written by the companion; it is not part of the notebook.

mar_apr_flights["month"].value_counts().sort_index()

What it prints:

month
3    28834
4    28330
Name: count, dtype: int64

↑ Back to Lab Contents

Part 9: Integration Checklist

  1. What is the main table?
  2. What is the lookup or secondary table?
  3. What key connects the tables?
  4. Is the key unique where it should be unique?
  5. What type of join is appropriate?
  6. Did the row count change as expected?
  7. Did the merge create missing values?
  8. Can you explain the result in business language?

↑ Back to Lab Contents

Part 10: Final Practice: Build an Integrated Flight Table

Business question: build a flight-level table of July departures from JFK that business users can read: add airline names and destination airport names. Use left joins so every flight stays.

10.1 The main table (given)

# Final Practice Step 1: create the main flight-level table.
flight_base = flights.query("origin == 'JFK' & month == 7")[[
    "year", "month", "day", "carrier", "flight", "origin", "dest",
    "dep_delay", "arr_delay", "distance", "air_time"
]].copy()

flight_base.head()

Complete independently

Steps 2–5 are graded, so the companion does not solve them. Here is what each step asks for.

  1. Step 2 (an empty cell): left join airlines onto flight_base with the key carrier. Call the result flights_with_names.
  2. Step 3: the cell already contains the line integrated_flights = None. None is only a placeholder that means “nothing yet”. Write your merge on the right side of the equals sign in place of None: a left join of airports onto flights_with_names, with left_on="dest" and right_on="faa". The export cell saves a file only once integrated_flights holds your merged table.
  3. Step 4: compare the row counts of flight_base, flights_with_names and integrated_flights.
  4. Step 5: count the missing airline names and destination airport names in integrated_flights.

Watch the column names. Both airlines and airports have a column called name. Rename it before each merge, so the final table has two clearly named columns instead of name_x and name_y. For example, airlines.rename(columns={"name": "airline_name"}) and airports[["faa", "name"]].rename(columns={"name": "dest_airport_name"}). The second one also keeps only the two airports columns you need.

What to check: all three row counts must be equal; both lookup keys are unique, so nothing should expand. Missing airline names should be 0. Missing airport names are expected for the four codes you met in Your Turn 2 (SJU, BQN, STT, PSE); you can list them with value_counts() on dest for the rows with a missing airport name.

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

if integrated_flights is None:
    print("Complete Final Practice Steps 2-3 before exporting.")
else:
    module8_output_path = output_dir / "integrated_jfk_july_flights_module8.csv"
    integrated_flights.to_csv(module8_output_path, index=False)
    print(f"Saved {len(integrated_flights):,} rows to: {module8_output_path}")

Final reflection

Write 4–6 sentences in the notebook: the main table and what one row represents; the lookup tables and keys; why left joins fit; the row-count and missing-value evidence; and the business value of the added names.

↑ Back to Lab Contents

Part 11: Offline Assignment: Aircraft Match Audit

Complete independently

This assignment is intentionally not solved in the companion. Use the exact instructions in the notebook.

  1. Check whether tailnum is unique in planes.
  2. Rebuild the July JFK base table with tailnum added to its list of columns (copy Step 1 and add "tailnum"), then repeat the airline and destination joins from Steps 2–3.
  3. Left join manufacturer, model and seats from planes on tailnum, with indicator=True.
  4. Compare row counts, count the merge statuses and inspect missing aircraft fields.
  5. Name the result aircraft_match_audit.

Before coding: say the workflow as base table with tailnum → airline join → airport join → select planes columns → aircraft join with indicator → checks. Select only tailnum, manufacturer, model and seats from planes before merging, so its year column does not clash with the flight's year.

What to check: the row count must not change. In _merge, left_only flights have no aircraft record; some of them have no tail number at all. Your recommendation should use these counts and explain what an inner join would remove.

Aircraft integration recommendation

Write 4–5 sentences: whether the left join kept one row per flight, the row-count and match evidence, why an inner join could bias the review, and how unmatched aircraft records should be handled.

↑ 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 aircraft match audit and both reflections.
  2. Restart the kernel and run all cells from top to bottom. Confirm there are no errors.
  3. Confirm that data/processed/integrated_jfk_july_flights_module8.csv exists.
  4. Save the notebook, then commit and push: git add ., git commit -m "Complete Module 8 pandas integration lab", git push.
  5. Open your repository on GitHub and check that the latest commit and the CSV file are there. Submit the repository link in Canvas.

↑ Back to Lab Contents

Lab Wrap-Up

You combined tables with one repeatable pattern:

Module 9 moves the same ideas into a database: SQL queries, and in Module 10, SQL joins to build an analytic base table.

↑ Back to Lab Contents

Notebook and accessibility note

The current Module 8 notebook is authoritative for Python code, sequence, prompts and required work. This companion adds explanation, sample solutions for the Your Turn tasks and accessible presentation without changing those requirements. Expected results come from running the notebook on the nycflights13 data.

The page is designed for keyboard navigation, screen readers, browser text-to-speech and ordinary visual reading. Important instructional information is presented as text rather than depending on a visual demonstration.