cs-130 — The Duplicate Rows

Problem

Two registration exports must describe the same class of students.

Export A (from the old system, ids prefixed S-):

S-0007, Mia
S-0042, Omar
S-0117, Petra
S-0203, Quan
S-0311, Rosa

Export B (from the new system, ids unprefixed, newest last):

0007, Mia,     t=1
0042, Omar,    t=2
0042, Omar,    t=5   (re-registration, different email)
0117, Petra K., t=3
0203, Quan,    t=4
0450, Tara,    t=6

Your deliverables:

1. A one-line normalisation rule that makes ids comparable across the two exports.
2. A dedup rule: which field is the key, and which row wins when a key appears more than once?
3. The final unique-student count, the id of every duplicate you removed, and every row you flag rather than merge (with the reason).
4. One ambiguity in this data that you would escalate to a human instead of deciding yourself.

If the problem is ambiguous anywhere, state the assumption you are making and carry on — a stated assumption is part of a good answer.

Examples

• Input: the two exports above
• Output: unique students: 6 — plus the duplicate id(s) removed, the flagged id(s) with reasons, and the escalated ambiguity

Solve for the worked instance first. Then ask what would change if one number, name, or condition were replaced by something stranger.

What to notice

• "Same student" is decided by the key, not by the name: Petra and "Petra K." share an id, and the id wins.
• A row can be a duplicate and still carry information (the newer email) — merging is not the same as ignoring.
• An id present in only one export is not an error: it is a record with a different story, and it needs a flag, not a delete.

Discussion starter

After the solution is revealed, the class spends five minutes on this prompt: "Next month a third export arrives with no ids at all — name and programme only. Which of your rules survives, and which must be redesigned?"

Constraints and assumptions

• Difficulty: Intermediate (rule design with edge cases and escalation).
• Topics: deduplication, matching, missing values.
• The timestamp t is monotone within export B; ties cannot happen there.
• Classroom-scale inputs unless the problem says otherwise.
• Time-box: about five minutes for a first attempt; partial answers with clear reasoning are worth more than silent struggling.
• Optional stretch (not required): describe an alternative approach and say when it beats the obvious one.

Hints and extension

• First nudge: decide the key before deciding the winner — not after.
• If stuck after three minutes, write the input/output line you expect and trace the smallest example by hand.
• After the reveal, attempt the extension below before the next lecture.
• Extension challenge: state, as a testable rule, when two rows should be considered "probably the same student" without a shared id — and name one real-world cost of getting that rule wrong.