01 / Reproduce
Start with three ticket IDs
Extract the practice ZIP and open PowerShell in the extracted folder. Use Python 3.13 to match the lab. The installation step downloads pandas and its dependencies. The two scripts then run locally using the supplied CSVs.
python -m venv .venv
.\.venv\Scripts\python.exe -m pip install pandas==3.1.1
.\.venv\Scripts\python.exe .\merge_lab.py
.\.venv\Scripts\python.exe .\verify_lab.py
- Run the unchecked left join. Tickets
T-101andT-102each match two records.T-103has no match and still appears once. The join returns five rows with three distinct ticket IDs. - Run the audited join on the same inputs. Pandas raises
MergeErrorfor the repeated account key. The lab catches that expected exception and records it in the output. - Use the separate clean account fixture. The join returns three rows. The first two tickets are marked
bothandT-103is markedleft_only. The lab prints its ticket ID for review.
Compare the output with observed-results.json. The two account fixtures represent the original export and a correction from its fictional owner. The script leaves the choice between Standard and Premium to that owner.
02 / Understand
Tell pandas which side must be unique
Each row on the left represents a ticket. Several tickets can belong to one account. Each row on the right should represent one account. Set validate="many_to_one" to require unique account keys on that side. The lookup_status indicator shows whether the key appeared in both inputs or only in the ticket export. See the pandas merge API.
Save this shorter example as quick_join.py in the extracted folder. Run it with .\.venv\Scripts\python.exe .\quick_join.py. It prints one line, T-103. Change the account filename to accounts-duplicate.csv and run it again to see the exception.
import pandas as pd
options = {"dtype": "string", "keep_default_na": False}
tickets = pd.read_csv("tickets.csv", **options)
accounts = pd.read_csv("accounts-clean.csv", **options)
for table in (tickets, accounts):
ids = table["account_id"]
if (ids.isna() | ids.str.strip().eq("")).any():
raise ValueError("An input has a missing or blank account ID")
joined = tickets.merge(
accounts, how="left", on="account_id",
validate="many_to_one", indicator="lookup_status",
)
for ticket_id in joined.loc[joined["lookup_status"] == "left_only", "ticket_id"]:
print(ticket_id)
03 / Inspect the edges
Separate blank keys from missing matches
Account 009 is a usable key with no lookup record, so its ticket goes to review. A blank account ID has no key to look up, so the example rejects the input. Pandas can match a null key to another null key, even with many_to_one if the lookup has only one null key. The eight-case check reproduces that behavior.
These fixtures read IDs as text to retain leading zeroes, and turn off the default missing-value strings so a value such as NA stays text. The blank-key check also catches whitespace-only IDs. If your export uses a marker such as NULL for missing IDs, set that policy explicitly. See the CSV parser options.
04 / Make it your own
Review the join before you summarize
- Write down what one row represents on each side. If the lookup contains account history, work out which record should apply to each ticket date before choosing the key.
- On a disposable sample, add a duplicate lookup key and a ticket with an unknown account. Run both cases. The first should stop the join. The second should leave a reviewable row.
- In an AI-assisted workflow, ask the assistant to state the row-count rule and show the unmatched count before drafting prose. Run the checks yourself and decide whether to pause when unmatched rows remain. The kit includes an optional review prompt.
Boundaries
A unique lookup can still be wrong
These checks cover the supplied cases. A unique row can carry an incorrect plan, a ticket export can already contain duplicate ticket IDs, and an exact text match can reflect a bad ID assigned upstream. We used fictional CSVs and did not test a model, the optional prompt, production data, or throughput.
Primary sources / Checked November 8, 2026
Read the pandas contract
- pandas.merge reference for join keys, validation, the indicator, and null behavior.
- pandas merging guide for duplicate-key checks.
- pandas.read_csv reference for text types and missing values.
- pandas 3.1.1 on PyPI for the package version used in the lab.
Downloads / Practice files

