Download the scripts and tests (ZIP)
Python scripts, tests, and ready-to-run merge, join, and date practice files with expected results. No signup required.
You have an orders export and a customer directory. Each order needs its customer’s region, but the orders file can contain several orders for the same customer. Appending the files would stack unrelated rows. Comparing them would describe differences. What you need is a many-to-one left join: keep every order and add fields from at most one matching customer.
This guide provides a standard-library script that checks the customer key is unique, preserves order and text IDs, and writes an audit file for orders without a match. It refuses ambiguous joins before creating the output folder. The sample data is invented. Code and failure cases were tested with Python 3.10 on Windows on September 12, 2026.
Choose which file owns the output rows
Try a ready-to-run join
Use the Download the scripts and tests (ZIP) link on this page, extract everything, and open a terminal in the extracted folder containing examples and fixtures. Check python --version first. This downloadable practice was tested with Python 3.10 on Windows on September 18, 2026; no additional packages are required. On Windows you can substitute py -3.10 if that is your installed launcher, or python3 on systems that use that command.
python examples/join_csv.py fixtures/join/inputs/orders.csv fixtures/join/inputs/customers.csv practice-join --key customer_id
This small practice has its own invented input files. Expect rows=3 matched=2 unmatched=1. Compare practice-join/joined.csv and practice-join/unmatched.csv with the corresponding files in fixtures/join/expected. Read practice-join/COMPLETE.txt, which should contain the same counts. Two orders for customer 001 both receive Seoul. Order A03 for customer 003 remains in the joined output with an empty region and left_only, and appears in the unmatched report. Customer 002, which has no order, adds no output row.
Do not create practice-join beforehand or use an expected-results folder as the destination. An existing output folder is refused; choose another name when rerunning. Read fixtures/README.md for all practice steps, or run python -m unittest discover -s . -p test_examples.py -v from the extracted root to verify the command and expected records automatically on temporary copies. Those tests also check unchanged source files and protection against overwriting. The fuller explanation below shows why the key rules matter when replacing the sample with your own data.
The left file defines the rows you must keep. The right file is a lookup table. Repeated customer IDs on the left are expected; repeated IDs on the right are rejected, even when their other values are identical. That rule prevents accidentally multiplying an order when two customer records share its key.
For example, three orders for customer 001 and two customer-directory records for 001 could produce six rows in a general many-to-many join. This script stops instead. Resolving which customer record is authoritative is a data decision, not something the script can infer.
| Condition | Result |
|---|---|
| Several left rows share a key | Each remains one output row |
| Right key occurs twice | Error before creating output |
| Left key has no right match | Retained with left_only status and copied to audit |
| Right key has no left match | Not included in this left join |
| Blank key on either side | Error before creating output |
Keys match as exact strings. 001 and 1 are different. Surrounding spaces and case are preserved, so ACME and acme do not match. Whitespace-only keys are rejected. Normalize keys only when your source contract establishes that the transformation is valid.
Reproduce the example
Save these files as UTF-8 in a working directory. The key column must have the same name in both files, but its position can differ.
orders.csv:
order_id,customer_id,total
O-100,001,12.50
O-101,002,9.00
O-102,001,4.00
O-103,099,7.00
customers.csv:
customer_id,name,region
001,"Park, Mina",East
002,Renée,West
003,Sam,North
Save the complete script below as join_csv.py, then run:
python join_csv.py orders.csv customers.csv joined-run --key customer_id
The directory joined-run must not already exist. The success message and completion marker contain:
rows=4 matched=3 unmatched=1
The generated joined.csv is:
order_id,customer_id,total,right_name,right_region,_join_status
O-100,001,12.50,"Park, Mina",East,matched
O-101,002,9.00,Renée,West,matched
O-102,001,4.00,"Park, Mina",East,matched
O-103,099,7.00,,,left_only
unmatched.csv retains the original left header and the single O-103 row. Customer 003 is absent from the joined output because it has no order. A full outer join would answer a different question and is not implemented here.
Every added right-side field gets a right_ prefix. The right key is not duplicated. _join_status distinguishes an unmatched row from a matched customer whose attributes happen to be empty. If the resulting names collide with left columns, the script fails and asks you to rename the conflicting input columns. It never silently replaces a left value.
Complete script
"""Many-to-one left join of two UTF-8 CSV files using an exact text key."""
import argparse
import csv
from pathlib import Path
def read_table(path, key):
with Path(path).open(encoding='utf-8-sig', newline='') as handle:
reader = csv.reader(handle, strict=True)
header = next(reader, None)
if not header or any(not name.strip() for name in header):
raise ValueError(f'{path}: missing or blank header')
if len(set(header)) != len(header) or key not in header:
raise ValueError(f'{path}: duplicate header or missing key column')
rows = []
for number, row in enumerate(reader, 2):
if len(row) != len(header):
raise ValueError(f'{path}: record {number} has wrong field count')
item = dict(zip(header, row))
if not item[key].strip():
raise ValueError(f'{path}: record {number} has a blank key')
rows.append(item)
return header, rows
def join_csv(left_path, right_path, destination, key):
left_header, left = read_table(left_path, key)
right_header, right = read_table(right_path, key)
extra = [name for name in right_header if name != key]
added = ['right_' + name for name in extra]
header = left_header + added + ['_join_status']
if len(set(header)) != len(header):
raise ValueError('Output column collision: rename conflicting input columns')
lookup = {}
for row in right:
if row[key] in lookup:
raise ValueError(f'Duplicate right-side key: {row[key]!r}')
lookup[row[key]] = row
destination = Path(destination)
destination.mkdir() # Require a new folder; never overwrite existing results.
matched = missing = 0
with (destination/'joined.csv').open('x', encoding='utf-8', newline='') as joined, \
(destination/'unmatched.csv').open('x', encoding='utf-8', newline='') as unmatched:
writer = csv.writer(joined)
audit = csv.writer(unmatched)
writer.writerow(header)
audit.writerow(left_header)
for row in left:
other = lookup.get(row[key])
status = 'matched' if other is not None else 'left_only'
values = [other[name] for name in extra] if other is not None else [''] * len(extra)
writer.writerow([row[name] for name in left_header] + values + [status])
if other is None:
audit.writerow([row[name] for name in left_header])
missing += 1
else:
matched += 1
with (destination/'COMPLETE.txt').open('x', encoding='utf-8') as marker:
marker.write(f'rows={len(left)} matched={matched} unmatched={missing}\n')
return len(left), matched, missing
def main():
parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument('left', type=Path)
parser.add_argument('right', type=Path)
parser.add_argument('destination', type=Path)
parser.add_argument('--key', required=True)
args = parser.parse_args()
try:
total, matched, missing = join_csv(args.left, args.right, args.destination, args.key)
except (OSError, UnicodeError, ValueError, csv.Error) as exc:
parser.exit(1, f'Join failed: {exc}\nOutput without COMPLETE.txt is incomplete.\n')
print(f'rows={total} matched={matched} unmatched={missing}')
if __name__ == '__main__':
main()
The Python CSV documentation explains how the reader handles quoted fields and why files are opened with newline=''. This script uses that reader instead of splitting lines on commas, so a quoted customer name or embedded newline stays in one field. UTF-8 input with a byte-order mark is accepted. Output is UTF-8 CSV, with every input cell retained as text.
If you already use pandas, its merge reference documents validate='many_to_one' for checking right-key uniqueness and indicator for match provenance. Our downloadable example requires no pandas installation and applies its own blank-key rejection policy. Do not assume a different library’s missing-key handling is identical.
Verify before using the joined data
Run this beside the new output folder:
import csv
from pathlib import Path
out = Path('joined-run')
assert (out / 'COMPLETE.txt').is_file()
with (out / 'joined.csv').open(encoding='utf-8', newline='') as f:
rows = list(csv.DictReader(f))
assert len(rows) == 4
assert [row['order_id'] for row in rows] == ['O-100', 'O-101', 'O-102', 'O-103']
assert rows[0]['customer_id'] == '001'
assert rows[0]['right_name'] == 'Park, Mina'
assert rows[-1]['_join_status'] == 'left_only'
with (out / 'unmatched.csv').open(encoding='utf-8', newline='') as f:
assert [row['order_id'] for row in csv.DictReader(f)] == ['O-103']
print('Join sample verified')
The downloadable tests check those results, source preservation, duplicate lookup keys, header and output-name collisions, blank keys, malformed records, Unicode, quoted multiline cells, and empty tables. They also check that an existing output directory is refused. Tests use synthetic fixtures; they do not measure performance on your files.
Investigate missing matches without hiding them
Start with unmatched.csv. Confirm the lookup export covers the same reporting period, then inspect key spelling, leading zeros, and whitespace in a text editor. Do not immediately strip zeros or drop unmatched orders to improve a match percentage. Both can turn a visible data problem into a plausible-looking but incorrect report.
If the lookup contains repeated keys, inspect the competing records before deciding what to retain. Our deduplication guide writes an audit of conflicting values, but choosing the first record is appropriate only when that policy fits your source. To stack monthly exports with the same columns, use CSV concatenation. To track changed fields between two snapshots, use comparison by ID.
Reproduce and resolve a duplicate lookup key
Using a separate practice folder, save orders.csv as:
customer_id,order_id
001,A01
Save customers.csv as:
customer_id,region
001,Seoul
001,Busan
From the extracted download folder, use paths to those files and a destination that does not exist:
python examples/join_csv.py orders.csv customers.csv rejected-join --key customer_id
The observed Python 3.10 result on Windows, September 19, 2026, is exit code 1 and Join failed: Duplicate right-side key: '001'. No output folder is created. The following incomplete-output reminder is generic; it does not mean an output folder exists in this validation failure.
Do not solve this by automatically taking the first customer row. In this invented exercise, suppose the source owner confirms Seoul is current. Save a separate corrected lookup containing only the header and 001,Seoul, then rerun to repaired-join. Expect rows=1 matched=1 unmatched=0, a joined row with right_region equal to Seoul, and a header-only unmatched report. For real records, retain evidence of which source was authoritative.
| Other symptom | What to check | What to do next |
|---|---|---|
| Missing key column | Header spelling and delimiter | Match --key to the exact header; convert a known non-comma delimiter first |
| Blank key | Empty or whitespace-only values on either side | Obtain a corrected source; do not invent an ID |
| Output column collision | Existing right_... names or _join_status |
Rename conflicting input columns deliberately on a copy |
left_only with exit code 0 |
Keys in the unmatched report | Review coverage and exact text; successful processing does not mean every order matched |
| Existing destination | A prior run or manually created folder | Review it and choose a new output folder, rather than replacing its contents |
Limits and incomplete runs
Both tables are loaded into memory before writing. The dictionary lookup adds memory proportional to the right table, and output is written one row at a time. Use files that fit comfortably in memory; no large-file benchmark is claimed. Input must be comma-delimited UTF-8, and the default CSV field-size limit still applies. A single key column is supported; composite-key joins need a deliberate extension.
Validation errors create no output folder. A disk error or interruption during output can leave a partially written folder without COMPLETE.txt. Treat that run as incomplete and use a fresh directory after investigating the error. The marker indicates this script finished writing; it does not prove the input records are factually correct. The script does not modify the originals, upload files, infer number types, or remove spreadsheet-formula strings. Review any untrusted CSV before opening it in spreadsheet software.