Joins: predict the result before running it
A join adds information from another table by matching keys. Extra or missing matches can change the number or meaning of the rows.
A join combines records using keys. A left join retains left-table rows and adds matches; missing right-side matches become missing values. An inner join retains matching keys only and can silently narrow the population. Cardinality describes the number of possible matches: one-to-one, one-to-many, many-to-one or many-to-many.
Joining enrolments on the left to a unique student table is many-to-one. If the right key is unique, the left join should retain 4,665 registration rows. Duplicated student keys can multiply rows. A left row matching two right rows appears twice; with repeated keys on both sides, matches can multiply within each key.
joined = enrolments.merge(
students[['student_id', 'programme', 'study_mode']],
on='student_id', how='left', validate='many_to_one',
indicator=True
)
assert len(joined) == len(enrolments)
print(joined['_merge'].value_counts())
validate='many_to_one' checks uniqueness on the right; it does not prove all IDs matched or that the source values are accurate. The merge indicator distinguishes unmatched records from a matched record whose programme itself is missing. For the provided Lab 1, a programme-not-missing assertion is also used as a practical check under its data assumptions.
Sources: W1 PDF pp. 3, 13-17, 22-30, 32-53, 57-60; L1 Parts 1-5.
The lecture distinguishes people from repeated observations. This table repeats student IDs because each row is a course registration.
many_to_one is the lecture's key rule in code. Unique student keys on the right prevent registrations from multiplying.
Practice problems
Supplementary study questions on the lecture concepts — not verbatim exam questions. Each answer counts toward spaced review.
1. Conceptual understanding
Definitions and distinctions.
What does validate='many_to_one' require in this merge?
A student has 4 registrations and their registry row is duplicated. How many extra left-join rows result?
2. Application
Apply the concept to a case or calculation.
A left row has no right-side match. What normally happens in a left join?
Two left rows and three right rows share one key. How many matched rows can that key produce?
3. Interpretation and limitations
Evaluate assumptions and justify conclusions.
Why inspect the merge indicator?
There are 100 left rows, all matching a unique right key. What is the expected left-join size?
Explain it in writing
Self-checked, not marked. Write a few sentences, then compare with the example answer. Your writing is saved as you type.
Show an example answer
It catches duplicate right-side keys that violate the expected relationship. It does not prove every left key matched or that the matched values are accurate.
Check: did you answer the question, use evidence or working, and avoid claiming more than the evidence supports?
Show an example answer
It removes left records that have no matching right key. If those missing matches are related to programme or outcome, the resulting table may describe a different group.
Check: did you answer the question, use evidence or working, and avoid claiming more than the evidence supports?