2 · Clean and join with an agent

Clean two messy tables with an agent, join them on a shared key, then produce a verification note documenting every decision. This is Stage 2 of your capstone workflow.

At a glance

Time	~65 minutes, in class
Work with	A partner (or your capstone team)
You need	An agent (Claude or Manus) + a spreadsheet
You hand in	A cleaned, joined CSV + a one-page verification note
Graded on	Specificity, logic, verification — not speed

The scenario

Kopi Kembali runs a loyalty programme. Marketing wants to know how much loyalty members spend — which means joining two exports:

• kk-customers.csv — the loyalty member list (~300 members, 303 rows)
• kk-orders.csv — 1,204 order rows, January to August 2026

One thing worth knowing before you start

The loyalty system was migrated on 15 March 2026. Files that cross a system migration are rarely as consistent as they look. The two files share customer_id — that is your join key, and it deserves your suspicion.

Data dictionaries

kk-customers.csv	kk-orders.csv
customer_id	order_id
name	customer_id
join_date	order_date
tier (Bronze, Silver, Gold)	outlet
home_outlet	amount_sgd
marketing_opt_in

Get your data

• Download kk-customers.csv
• Download kk-orders.csv

Part 1 — Profile and plan · 15 min

Do not use the agent yet. Open both files in a spreadsheet and answer for yourself:

• Customers: How many rows? Any missing values, and in which columns? What date format? Any inconsistent category spellings in tier? Any duplicate customer_id?
• Orders: How many rows? Any missing values or duplicate order_id? Date format? Do all customer_id values appear in the customers table?

Then decide your join and write it down before you run anything:

• Which column will you join on, and why?
• Inner join (keep only matched rows) or left join (keep all customers)? Why?
• Roughly how many rows do you expect in the result? Which rows do you expect to be dropped?

Part 2 — Brief the agent · 25 min

🔗 On your agent platform

Open Claude or Manus and upload both CSV files. Run the two prompts below in order — profile first, then clean. Pause after each and read the response.

Prompt 1 — profile only (do not clean yet):

I have two CSV files: kk-customers.csv and kk-orders.csv.

Before I ask you to clean them, profile them. For each table tell me:
1. The number of rows
2. Every column name and its data type
3. The number of missing values in each column (counts, not percentages)
4. Any obvious quality issues: inconsistent formatting, outliers, duplicates
   (for customers, count duplicate customer_id; for orders, count duplicate
   order_id), or pre-calculated columns

Do not clean anything yet. Just profile and report.

Note

Sanity check. Does the row count the agent reports match what you saw in your spreadsheet? If not, stop and find out why before going further.

Prompt 2 — execute your plan. Replace [YOUR PLAN] with the plan you wrote in Part 1:

Based on your profile, here is exactly what I want you to do:

[YOUR PLAN]

As you work:
1. Show me the row count before and after each step.
2. For missing values: tell me which column, how many, and exactly what you did.
3. For duplicates: show which rows were duplicates and why you removed them.
4. For any standardised format (dates, categories): show the mapping you used.
5. For the join: tell me which column(s) you joined on, how many rows from each
   table matched, and how many rows were dropped and why.

Then return the cleaned and joined dataset as CSV.

Watch it work and note: Did it ask clarifying questions? Did it show row counts for each step? Did anything surprise you or differ from your plan?

Save the output as cleaned_joined.csv and screenshot the transcript.

Interrogate the join

A join always produces a number. Your job is to find out what that number is standing on. How many orders survived? How many did not — and what do the lost rows have in common (the migration date, perhaps)? If your first join keeps far fewer orders than you expected, that is not a failure of the lab. It is the lab.

Part 3 — Verify and document · 25 min

This note is your portfolio deliverable. One sentence per decision. Specific, not vague. Fill in the template:

VERIFICATION NOTE
Team: ______   Date: ______
Dataset: Customers and Orders (joined on customer_id)

ROW COUNTS
  Customers, starting: ___      Orders, starting: ___
  Customers, cleaned:  ___      Orders, cleaned:  ___
  Final joined result: ___

CLEANING DECISIONS (customers, then orders)
  Missing values — which column, how many, how handled, why defensible
  Categories     — which column, what was inconsistent, mapping, how verified
  Duplicates     — how many, true dupes or legit records, what you did
  Dates          — standardised to YYYY-MM-DD? how many had issues

THE JOIN
  Join key + why · join type (inner/left) · matched rows ·
  rows dropped from each table · one-sentence reason the drop is acceptable

DISTRIBUTION CHECK (for any numeric column the agent modified)
  before vs after: mean, median, range — substantially different? why acceptable?

SUMMARY (one sentence): what this dataset now contains and what was cleaned.

The distribution check catches a classic mistake

If the agent “cleans” by silently removing outliers, it is reshaping the true distribution. Run .describe() (or a histogram) on the numeric column before and after. If the mean moved, you need to know why — and whether the removal was justified.

Hand-check before you submit

1. Open cleaned_joined.csv and pick one row.
2. Find its customer_id in the original customers table — does the name match your joined row?
3. Find its order_id in the original orders table — does the amount match?
4. In a categorical column, eyeball a few values — are they plausible, or did the agent invent categories to fill blanks?

If either trace does not match, you may have a join problem — flag it.

Verify & submit

• cleaned_joined.csv — one observation per row, one variable per column, one value per cell
• Column names consistent (lower_case_with_underscores); dates all YYYY-MM-DD; numbers stored as numbers
• Verification note names each cleaning action with a defensible reason
• The join section states matched rows, dropped rows, and why the drop is acceptable
• At least one row traced by hand, end to end
• Distribution check done for any numeric column the agent modified

Save both artefacts in the team Drive workspace and link them from your individual portfolio. Each member also submits the Stage 2 xSiTe checkpoint record, linking the exact Sheet tab and Project Hub row and stating their personal contribution.

Tip

The note is graded on specificity, logic, and verification — did you actually check, or just accept the agent? Speed is not graded. Traceability is.

Capstone connection

Your team does this same work on your chosen scenario dataset in Stage 2 (Wrangle). Fill in the Stage 2 row of your orchestration log with: what you asked the agent, what it produced, what you accepted/changed/rejected and why, and how you verified it. Do it during your team meeting, not from memory at the end — the log is where supervision lives. See the Orchestration Log Template (Assessment Pack folder in Google Drive) for the template and a worked example.