Module 17 · Core

Merge related tables

Connect tables by a key and audit which records matched.

Before you start: Use column selection and understand unique Student_ID values.

Python loads from this site on your first run.

Each run starts with fresh data. Download any exported files before another run. Your written answers and code drafts are saved on this device; export your work to keep a backup.

M17C01 · Explore · predict · practice

Attach the department

The Program column is a key shared by two tables. A many-to-one merge lets several students match one department row. The validation checks that the lookup table has only one row per program.

ReadWhat does on="Program" tell pandas?

PredictWill the result contain 2 rows or 12 rows?

ExplainWhy validate="many_to_one"?

Read the example

df = students()
result = df.merge(departments(), on="Program", how="left", validate="many_to_one")
print(result[["Student_ID", "Program", "Department"]])

Verified example output · pandas 2.2.3

   Student_ID Program           Department
0        S001      AI            Computing
1        S002      IT  Information Systems
2        S003      AI            Computing
3        S004      IT  Information Systems
4        S005      IT  Information Systems
5        S006      AI            Computing
6        S007      AI            Computing
7        S008      IT  Information Systems
8        S009      IT  Information Systems
9        S010      AI            Computing
10       S011      AI            Computing
11       S012      IT  Information Systems

Watch for this. Do not join tables by row position when they describe entities identified by keys.

Your turn

Use DataFrame.join to attach the same lookup fields. Set Program as the lookup index and keep the student index.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

df = students()
result = df.merge(departments(), on="Program", how="left", validate="many_to_one")
print(result[["Student_ID", "Program", "Department"]])

Show a hint

Use departments().set_index("Program"), then join(..., on="Program").

Show one worked solution

df = students()
lookup = departments().set_index("Program")
result = df.join(lookup, on="Program", how="left", validate="many_to_one")
print(result[["Student_ID", "Program", "Department"]])

join can use the right table index as the key. Both forms preserve all 12 students here.

Compare your explanations

Read. Match the two tables using Program values.
Predict. 12 rows, one for each student, because each Program matches exactly one lookup row.
Explain. Students may repeat a Program, but each Program must appear at most once in the lookup table.

One quick check

Which side must have unique Program values?

What you should be able to explain. Choose and validate the matching key.

Your own assessment, separate from code checks

M17C02 · Explore · predict · practice

Keep matches or keep every student

An inner merge keeps only keys found in both tables. A left merge keeps every key from the left table. In this complete assessment lookup, a missing Quiz comes from an unmatched student. With other data, a matched assessment could itself have a missing Quiz; use the merge indicator to test for unmatched records.

ReadWhich table supplies Quiz?

PredictWhich registered student disappears from the inner merge?

ExplainDoes an unmatched quiz mean the student earned zero?

Read the example

result = students().merge(assessment(), on="Student_ID", how="inner", validate="one_to_one")
print(result[["Student_ID", "Name", "Quiz"]])
print("Rows:", len(result))

Verified example output · pandas 2.2.3

   Student_ID    Name  Quiz
0        S001   Amira    17
1        S002   Bilal    14
2        S003   Chloe    19
3        S004    Dana    13
4        S005   Ethan    16
5        S006  Fatima    19
6        S007   Grace    17
7        S008  Hassan    11
8        S009     Ivy    15
9        S010   Jamal    18
10       S011  Khalid    14
Rows: 11

Watch for this. Filling an unmatched assessment with zero changes its meaning; agree on a policy first.

Your turn

Change the merge to left and store the full merged table in merged. Select only students whose Quiz is missing into result, keeping Student_ID, Name, and Quiz, then print result.

For the code check, keep these result variable names: merged, result. Different valid approaches are welcome.

Your Python code

result = students().merge(assessment(), on="Student_ID", how="inner", validate="one_to_one")
print(result[["Student_ID", "Name", "Quiz"]])
print("Rows:", len(result))

Show a hint

Use how="left" and then Quiz.isna().

Show one worked solution

merged = students().merge(assessment(), on="Student_ID", how="left", validate="one_to_one")
result = merged.loc[merged["Quiz"].isna(), ["Student_ID", "Name", "Quiz"]]
print(result)

The left merge retains 12 students; the missing-match report contains only S012.

Compare your explanations

Read. The assessment table.
Predict. S012, Lina, because the assessment table has no S012 row.
Explain. No. A missing match is unknown and needs investigation.

One quick check

Which merge preserves every registered student?

What you should be able to explain. Merge type determines which records remain.

Your own assessment, separate from code checks

M17C03 · Explore · predict · practice

Audit both unmatched sides

An outer merge keeps keys from either table. indicator=True adds a _merge column showing left_only, right_only, or both. This is a simple way to find records that need review.

ReadWhat does right_only mean here?

PredictWhich IDs will the audit show?

ExplainWhy inspect both unmatched sides?

Read the example

audit = students().merge(assessment(), on="Student_ID", how="outer", indicator=True, validate="one_to_one")
result = audit.loc[audit["_merge"].ne("both"), ["Student_ID", "_merge"]]
print(result)
print("All merged rows:", len(audit))

Verified example output · pandas 2.2.3

   Student_ID      _merge
11       S012   left_only
12       S013  right_only
All merged rows: 13

Watch for this. Pandas may match missing keys to other missing keys; audit or reject null keys before important merges.

Your turn

Use a right merge instead. Keep indicator=True and report the row that has no registered student name.

For the code check, keep these result variable names: audit, result. Different valid approaches are welcome.

Your Python code

audit = students().merge(assessment(), on="Student_ID", how="outer", indicator=True, validate="one_to_one")
result = audit.loc[audit["_merge"].ne("both"), ["Student_ID", "_merge"]]
print(result)
print("All merged rows:", len(audit))

Show a hint

A right merge preserves the assessment keys; inspect Name.isna().

Show one worked solution

audit = students().merge(assessment(), on="Student_ID", how="right", indicator=True, validate="one_to_one")
result = audit.loc[audit["Name"].isna(), ["Student_ID", "_merge"]]
print(result)
print("All merged rows:", len(audit))

The right merge keeps 12 assessment records. S013 remains even though no student profile matches.

Compare your explanations

Read. The key exists in assessments but not in the student table.
Predict. S012 is left_only; S013 is right_only.
Explain. One side can reveal missing assessments; the other can reveal assessments for an unregistered or mistyped ID.

One quick check

How many distinct keys does the outer merge contain?

What you should be able to explain. Use indicator to make unmatched records visible.

Your own assessment, separate from code checks

M17C04 · Explore · predict · practice

Stop duplicate-key expansion

If a lookup repeats a key, one input row can produce several output rows. The code deliberately adds a duplicate AI lookup. Catching MergeError lets us demonstrate the problem without stopping the lesson.

ReadWhy is try/except used?

PredictHow many rows appear without validation?

ExplainShould every duplicate key be dropped automatically?

Read the example

bad_lookup = pd.concat([departments(), departments().iloc[[0]]], ignore_index=True)
blocked = False
try:
    students().merge(bad_lookup, on="Program", validate="many_to_one")
except pd.errors.MergeError:
    blocked = True
    print("Blocked: Program is not unique in the lookup.")
result = students().merge(bad_lookup, on="Program")
print("Rows without validation:", len(result))

Verified example output · pandas 2.2.3

Blocked: Program is not unique in the lookup.
Rows without validation: 18

Watch for this. validate checks the relationship; it does not decide how to repair conflicting records.

Your turn

Remove only exact duplicate lookup rows, confirm Program is unique, and perform the validated merge.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

bad_lookup = pd.concat([departments(), departments().iloc[[0]]], ignore_index=True)
blocked = False
try:
    students().merge(bad_lookup, on="Program", validate="many_to_one")
except pd.errors.MergeError:
    blocked = True
    print("Blocked: Program is not unique in the lookup.")
result = students().merge(bad_lookup, on="Program")
print("Rows without validation:", len(result))

Show a hint

drop_duplicates() removes exact repeats; is_unique checks the key.

Show one worked solution

bad_lookup = pd.concat([departments(), departments().iloc[[0]]], ignore_index=True)
lookup = bad_lookup.drop_duplicates()
assert lookup["Program"].is_unique
result = students().merge(lookup, on="Program", validate="many_to_one")
print("Validated rows:", len(result))

The duplicated lookup was identical, so removing the exact repeat restores 12 rows.

Compare your explanations

Read. To catch the expected validation error and continue the lesson.
Predict. 18: each of the 6 AI students matches twice, and the 6 IT students match once.
Explain. No. Repeated keys may describe different facts. This example repeats an identical lookup row, so removing that exact duplicate is justified.

One quick check

What can repeated keys on both sides cause?

What you should be able to explain. Check key uniqueness before trusting merged row counts.

Your own assessment, separate from code checks

Previous: Group, summarize, and compare Next: Concatenate and align

Further detail: official documentation for this topic.