Capstone: workshop-order audit

Outcome: deliver a reproducible small-data summary with visible quality decisions. Prerequisite: Modules 0–9. Profile: Data for cleaning, Charts for visualization, or the optional local environment. Time: plan 5–7 hours including the bridge checkpoints; this estimate has not been learner-piloted.

The brief

A fictional workshop organizer exported a small set of stationery orders. They ask: “What can we reliably summarize by category, and which records still need review?” Your job is to preserve the source, apply explicit rules, explain exclusions and produce an honest chart. You are not building a machine-learning model or a financial reporting system.

Read datasets/README.md and inspect datasets/workshop_orders.csv before coding. One raw row represents one order line; for this simplified exercise each order ID is intended to have exactly one line. The file contains 16 rows. Do not assume this key would be sufficient for a real multi-line order system.

Define the contract before the transformation

The required fields are order_id, date, category, units and unit_price_cents. Preserve the raw text. Only exact repeated source rows count as duplicates, keeping one copy and reporting the extras. After exact duplicate removal, normalize order-ID outer whitespace and category whitespace/case. If one normalized ID occurs in different records, reject every record with that ID as a conflict rather than guessing which is correct.

Use this acceptance table as your contract. The bounds are deliberately small teaching limits, not inferred business rules.

Check source spelling before numeric conversion. 1.0000000000000001 can round to 1 in a floating-point representation, but that must not erase the fractional source text. Date bounds make the intended acceptance range consistent across the tested pandas versions. Preserve invalid source tokens for review.

CSV structure is checked before creating the table. Every record must have exactly five fields, and malformed quoting stops the run with a clear error. The reference reports the record position and original parsed fields for a width mismatch; it does not silently shift an extra field into an index. Preserve the original file and request a corrected export for structural errors. The accepted/rejected split below applies only after the CSV structure is valid. Blank records are structural errors under this teaching contract.

Keep a reason for every rejection, preserving all failed reasons if a row has more than one defect. Keep exact duplicates in a separate output. The accounting identity is:

raw rows = exact duplicate extras + rejected rows + accepted rows

Only after validation, compute revenue_cents = units * unit_price_cents and group by category. Use integer cents for the calculation and divide by 100 only for chart display. Call the result an accepted-order subtotal because unresolved records are excluded. The dataset omits currency, tax, shipping, returns and many real transaction rules.

Bridge checkpoint 1 · Can the record be interpreted safely?

Before beginning the independent project, work through these small cases. They use different records from the workshop fixture. Predict the intermediate outputs before running; compare them, then make the requested change. The follow-up answers are in the separate bridge answer guide.

The first toy CSV has only code and units. R1,unknown has two fields, so its structure is understandable. Its units still fail a numeric-value rule. extra,R1,2 has three fields: guessing which value belongs to which column could discard evidence. Stop ingestion in that case instead of choosing a field to ignore.

# Profile: Python basics. A two-column toy case, not the capstone answer.
import csv
from io import StringIO

def read_two_fields(text):
    try:
        records = list(csv.reader(StringIO(text), strict=True))
    except csv.Error as error:
        raise ValueError("malformed CSV quoting") from error
    if not records or records[0] != ["code", "units"]:
        raise ValueError("expected code,units header")
    for position, record in enumerate(records[1:], start=2):
        if len(record) != 2:
            raise ValueError(f"record {position}: expected 2 fields, got {record!r}")
    return records[1:]

sound = "code,units\nR1,unknown\n"
print("Structurally sound:", read_two_fields(sound))
try:
    int(read_two_fields(sound)[0][1])
except ValueError:
    print("R1 is a value rejection: units remain 'unknown'")
try:
    read_two_fields("code,units\nextra,R1,2\n")
except ValueError as error:
    print("Ingestion stopped:", error)

Download this bridge example

The sound read returns [["R1", "unknown"]]. Numeric conversion then reports a value rejection. The malformed-width example stops with record position 2 and the three original parsed fields. strict=True also makes an unclosed quoted field a CSV parsing error. The function catches that narrow parser error and translates it to a clear ValueError. It does not catch every exception.

Change unknown to 3: which message disappears, and which value can you now convert? Separately change the malformed row to R1,"2,3": CSV quoting makes this structurally sound with two fields, but the second field is not an integer. Explain why the disposition changes from stopping ingestion to rejecting a value. Do not alter the original capstone CSV for these experiments.

The capstone starter supplies a tested read_order_records helper because defensive CSV ingestion is not the main skill being assessed. Read its contract, predict these cases and reuse it. Its header test uses set difference: {"code", "units"} - {"code"} is {"units"}, the missing required field. You are not asked to infer this helper from a finished answer.

Bridge checkpoint 2 · A repeated row is not the same as a conflict

In the next four-row case, labels 0 and 1 are exact copies. Label 2 has the same ID after trimming but different units. Label 3 is unrelated. First ask whether the entire source row is repeated; only afterward ask whether normalized identifiers disagree across remaining records.

# Profile: Data · NumPy and pandas. Predict each mask before running.
import pandas as pd
raw = pd.DataFrame({"order_id": ["A01", "A01", " A01 ", "B01"],
                    "units": ["2", "2", "3", "4"]}, dtype="string")
print("Source rows:\n" + raw.to_string())
extra = raw.duplicated(keep="first")
print("Exact-copy extras:", extra.tolist())
duplicates = raw.loc[extra].copy()
work = raw.loc[~extra].copy()
work["order_id"] = work["order_id"].str.strip()
conflicts = work["order_id"].duplicated(keep=False)
print("Remaining labels:", work.index.tolist())
print("Conflict decisions:", conflicts.to_dict())
print("Duplicate labels:", duplicates.index.tolist())
print("Conflict source rows:\n" + raw.loc[work.index[conflicts]].to_string())
print("Still eligible for acceptance:", work.loc[~conflicts].index.tolist())

Download this bridge example

Trace these intermediate results:

• The exact-extra mask is [False, True, False, False]; label 1 is set aside.
• The working labels are [0, 2, 3]. They keep their source identity even though there are now three rows.
• After trimming, the conflict decisions are {0: True, 2: True, 3: False}. duplicated(keep=False) flags every occurrence of the repeated ID, not only the later one.
• Label 3 alone is still eligible for acceptance at this point. Every nonduplicate row, including conflicted labels 0 and 2, still receives the date/category/numeric field checks so that all of its failure reasons are retained. Labels 0 and 2 remain visible as conflict evidence; neither is chosen as the winner.

Before rerunning, change the ID at source label 2 to C01. Predict duplicate count, conflict count and the labels still eligible for acceptance. In a separate experiment on the original toy case, change label 1's units from 2 to 5: it is no longer an exact repeated row. Predict how many records now share A01. Write the reason order matters; sorting the output is not a substitute for this classification.

Bridge checkpoint 3 · Validate fields, then attach every reason to its source

A field can pass one check and fail another. In this small unit-count example, [0-9] means one ASCII digit and + means one or more. .str.fullmatch(...) requires the whole string to fit; it does not merely search for a digit somewhere. For dates, {4} and {2} require exact digit counts, while hyphens are literal separators. The r prefix creates a raw Python string; it keeps backslashes literal in patterns that use them. You only need these small patterns here, not a full regular-expression course.

# Profile: Data · NumPy and pandas. Syntax, conversion and policy are separate.
import pandas as pd
text = pd.Series(["2", "001", "1.0", "1.0000000000000001", "unknown", "1000001"], dtype="string")
digit_spelling = text.str.fullmatch(r"[0-9]+")
number = pd.to_numeric(text, errors="coerce")
valid = digit_spelling & number.notna() & number.gt(0) & number.le(1_000_000)
print("Digit spelling:", digit_spelling.tolist())
print("Valid unit counts:", valid.tolist())
print("Accepted source tokens:", text.loc[valid].tolist())
dates = pd.Series(["2026-02-28", "2026-02-30", "2026-2-3", "2500-04-01"], dtype="string")
shape = dates.str.fullmatch(r"[0-9]{4}-[0-9]{2}-[0-9]{2}")
parsed = pd.to_datetime(dates, format="%Y-%m-%d", errors="coerce")
in_range = parsed.between("2000-01-01", "2099-12-31")
valid_dates = shape & parsed.notna() & in_range
print("Date shape:", shape.tolist())
print("Accepted dates:", dates.loc[valid_dates].tolist())

Download this bridge example

The unit spelling decisions are [True, True, False, False, False, True], but the valid-unit decisions are [True, True, False, False, False, False]: 1,000,001 has valid spelling and still exceeds the stated bound. The two accepted original tokens are "2" and "001". For dates, all but the flexible spelling match the shape, but only 2026-02-28 passes shape, calendar and range together. Missing conversion results must fail validity; no zero is invented.

Now attach two failures to one source row. The toy working table keeps labels 0, 2 and 3 after a duplicate was removed. Source label 2 has both an invalid date and unknown units.

# Profile: Data · NumPy and pandas. Keep source labels while adding reasons.
import pandas as pd
raw = pd.DataFrame({"code": ["R1", "R1", "R2", "R3"],
                    "date": ["2026-04-01", "2026-04-01", "not-a-date", "2026-04-03"],
                    "units": ["2", "2", "unknown", "4"]}, dtype="string")
work = raw.loc[[0, 2, 3]].copy()  # label 1 was an exact duplicate in this toy case
work["units"] = pd.to_numeric(work["units"], errors="coerce")
work["date"] = pd.to_datetime(work["date"], format="%Y-%m-%d", errors="coerce")
checks = {"invalid_units": work["units"].isna(),
          "invalid_date": work["date"].isna()}
reasons = pd.Series("", index=work.index, dtype="string")
for name, mask in checks.items():
    reasons.loc[mask] = reasons.loc[mask] + name + ";"
reasons = reasons.str.rstrip(";")
valid = reasons.eq("")
rejected = raw.loc[work.index[~valid]].copy()
rejected["reason"] = reasons.loc[~valid]
print("Working labels:", work.index.tolist())
print("Reason labels:", reasons.index.tolist())
print("Reasons:", reasons.to_dict())
print("Untouched source row 2:", raw.loc[2].to_dict())
print("Rejected evidence:\n" + rejected.to_string(index=False))

Download this bridge example

pd.Series("", index=work.index, dtype="string") creates an empty reason at each of those same labels. In for name, mask in checks.items(), .items() supplies one dictionary key/value pair at a time: a reason name and its labelled Boolean mask. Unpacking assigns the pair to name and mask. reasons.loc[mask] selects the labels where that particular rule failed; the assignment appends its name to those rows only.

After the units rule, label 2 contains invalid_units;. After the date rule, the same label contains invalid_units;invalid_date;. Stripping the final separator gives {0: "", 2: "invalid_units;invalid_date", 3: ""}. The source row still contains not-a-date and unknown. raw.loc[2] retrieves that untouched evidence, whereas the working row contains missing converted values.

Do not renumber the working table halfway through this process. If its labels became 0, 1 and 2 while raw kept 0, 1, 2 and 3, a reason at label 2 could be attached to the wrong source record. Print the working and reason indexes side by side until you can trace their alignment confidently. work.index[~valid] selects failed source labels; raw.loc[...] uses those labels to retrieve evidence.

For the independent checkpoint, change R2's date to 2026-04-02 but leave its units unknown; give R3 a bad date. Predict the exact reason strings, failed source labels and original fields before running. Then explain why adding each rule's failure count can double-count a row with two defects.

Build your capstone one stage at a time

Both starters now supply ingestion and orchestration, but the data decisions remain yours. Edit the five named TODO functions in order, using the intermediate contracts below. Do not replace them with a call to the separate reference solution.

1. prepare_work(raw) returns working rows, exact duplicate extras and a labelled conflict mask. Use checkpoint 2. Preserve source indexes; normalize only the working copy.
2. validate_work(work, conflicts) returns converted working rows and an aligned reason Series. Use the policy table and checkpoint 3. Required reason names are missing_order_id, conflicting_order_id, invalid_date, invalid_category, invalid_units and invalid_price. Include every failed rule; their display order need not match the reference.
3. split_evidence(raw, converted, reasons) returns accepted converted rows and rejected original rows with reasons. On accepted rows, convert unit/price columns to integer dtype before calculating cents. This .astype("int64") conversion is safe here because validation already established bounded integer values. Reset accepted indexes only after evidence selection is complete.
4. summarize_accepted(accepted) groups units and cents by category, retaining category as a column and sorting its labels. This is the Module 8 groupby pattern with two numeric columns; a list of column names selects both. Empty input still returns an empty table with those column names.
5. make_audit(...) returns the five documented audit fields and checks count reconciliation. sum(int(value) for value in accepted["revenue_cents"]) converts each subtotal to an ordinary Python integer before adding, avoiding an overall fixed-width integer sum. It is the generator-expression sum introduced in Module 5.

The earlier two-/three-column toy tables isolate one idea; do NOT pass them unchanged to the five-field capstone functions. Use the full-schema STAGE_CSV already embedded in both starters, also downloadable as stage_checkpoint.csv:

order_id,date,category,units,unit_price_cents
Q01,2026-06-01,pens,2,80
Q01,2026-06-01,pens,2,80
 Q01 ,2026-06-01,pens,3,80
Q02,bad-date,markers,unknown,150
Q03,2026-06-03, NOTEBOOKS ,4,250

Leave CHECKPOINT_STAGE = 1 while implementing stage 1. The supplied preview driver runs only the completed prefix of the stages and prints its intermediate results; it does not fill in an answer. Change the switch to 2, then 3, 4 and 5 as each new stage is ready. Compare against this trace:

Only after those stages work, set CHECKPOINT_STAGE = 6 to run the full project. The browser route first runs the public contract checks. Locally, run python check_capstone.py separately against your exercise function. A failure in stage 2 is a reason to inspect its masks, not to debug the chart yet. The supplied clean_orders then calls the stages and returns (accepted, rejected, duplicates, summary, audit). These names and their order form the public interface; printing a table instead of returning it will not satisfy the caller.

Browser and local routes

Browser: start with exercises/10_browser_starter.py in the Data profile; the synthetic CSV is already embedded. Complete the five named TODO stages. At checkpoint switch 6, the provided driver calls public checks against your clean_orders, then prints the four output tables in labelled CSV sections. Copy each section into a saved text file if you need exports. File export from the lab's runtime is not assumed. Download the edited source. After checking the summary, use examples/10_capstone_chart.py in Charts and download its image. The sample chart values must match your computed summary; do not silently paste stale totals after changing input.

Local: complete exercises/10_capstone.py, then run python check_capstone.py. This adapter explicitly loads your exercise file and calls your function. After the checks pass, set CHECKPOINT_STAGE = 6 and run python exercises/10_capstone.py to read the fixture and print named outputs. Save your own generated exports under a separate folder such as outputs/my_submission/ so you do not confuse them with the reference results. The separate completed reference is solutions/capstone.py, run with python -m solutions.capstone. It exports CSVs and a PNG. Read it after attempting your own version.

The browser worked reference is examples/10_capstone_data.py. Its complete code remains a separate answer download. The bridge labs and staged starter are the preparation; do not use the completed pipeline as your first instruction. It is the same cleaning logic as the local reference with in-memory source and printed exports.

Expected results for the supplied fixture

• Raw rows: 16; exact duplicate extras: 1; rejected: 5; accepted: 10
• Notebooks: 14 units and 3,500 cents
• Pens: 23 units and 1,840 cents
• Markers: 9 units and 1,350 cents
• Accepted total: 46 units and 6,690 cents, displayed as 66.90 fictional currency units

Rejected IDs are O005 through O008 and O010. Their respective problems are missing units, negative units, invalid price, invalid date and missing category. O004 has one exact duplicate. O009 is accepted after category normalization. These are checks for this fixed fixture, not values your function should hard-code.

Check your implementation, not somebody else's answer

The public checker takes a function as its input: check_submission(clean_orders) passes your function's name so the checker can call it on fresh fixtures. Writing check_submission(clean_orders(CSV_TEXT)) would instead pass one already-computed result and is not the intended interface.

The checks cover the five-result structure, count reconciliation, category/value pairing, changed valid data, duplicates, normalized-ID conflicts, multiple invalid fields, untouched rejected evidence, empty input, malformed structure, precision/date limits and a valid zero price. They do not import solutions.capstone. An unfinished stage deliberately raises NotImplementedError; a failed assertion identifies an expectation to investigate. Keep the original check case and add your own matched input/expectation rather than weakening it.

Passing public checks is necessary evidence, not sufficient evidence of independent mastery. Ask a reviewer to choose one additional small change without telling you the answer: predict the counts and subtotal, explain the code path, and run it. For example, change a previously invalid quantity to a valid one while leaving its unrelated date defect in place. Explain why fixing one field need not make the row acceptable. The human review also inspects exports and chart readability, which numeric checks alone cannot judge.

Tests and submission

Test empty-but-correctly-headed input, a valid zero price, a fractional unit count, an impossible date, a missing required column, extra leading and trailing CSV fields, malformed quoting, a same-ID disagreement and a category with extra whitespace. For charts, test a subtotal above the original plot range, an all-zero summary and an empty summary. Add at least one changed valid row and predict the new total before running. A special-case script that returns the expected numbers for only the original file is not a solution.

Submit source code, unmodified input, accepted/rejected/duplicate tables, category summary, labelled chart, test results and a 150–250-word interpretation explaining policy, quality counts, result and limitations. Include Python/package versions and how to reproduce the run. Use only synthetic data.

Assessment rubric · 20 points

Use the highest score whose description is fully met in each row. Record the evidence beside the score; do not award points solely because a screenshot looks finished.

Recommended completion requires all public contract checks passing, a satisfactory changed-input explanation, at least 16/20, and no critical failure. Critical failures remain altering raw evidence to force an answer, hiding rejected records, a non-reproducible result or describing the incomplete subtotal as complete sales.

A borderline 16-point example is 4/4/3/3/2: complete source/classification work, passing checks and a sound changed-input explanation, but a missing environment detail, incomplete chart text alternative and a thin discussion of next steps. It reaches the suggested threshold if there is no critical failure, with those improvements recorded as feedback. A 17-point submission that silently removes rejected records still fails the critical condition. A green reference test suite is never substituted for checks of the learner's submitted function.

Beyond this course

You now have foundations for working with small, inspectable data. Before later AI courses, be able to explain types, loops, functions, table grain, missing values, reproducibility and the limits of a chart. Model development will add new concepts and evaluation responsibilities; it is deliberately outside this course.

Further reading

• pandas: converting calendar dates
• pandas: CSV ingestion options