Combining Tables: Joins, Keys and the Danger of Fan-Out
Join a visits table to a customers table, see what an unmatched key and a repeated key do to your totals, and learn a safe routine.
In this lesson you will
- Explain keys and the main kinds of join
- Find rows that failed to match
- Spot a fan-out from a repeated key
- Follow a safe routine for every join
About 28 minutes
One table is rarely enough
Leila wants to know whether the workshop’s members behave differently from other customers. But the visits table does not say who is a member. That fact lives in a second file, customers.csv, that Omar keeps for the membership forms. To answer her question, the two tables must be combined. Combining tables is called a join or a merge, and it is one of the most useful and most dangerous steps in analysis, because a join can change your numbers without any error message.
customer,joined,area,member
Asha Rao,2025-11-08,Hill,yes
Ben Cole,2026-01-17,Dock,no
Chloe Park,2026-03-07,Hill,yes
Dina Shah,2026-02-14,Park,no
...
Keys
A join needs a key: a column that appears in both tables and identifies the same thing. Here the key is the customer name. (Real workshops would use a customer number, because names can be spelled in many ways, as you are about to see.) The table of visits has many rows for each customer, and the table of customers should have exactly one row for each. This is a many-to-one join, and it is the most common shape.
Four kinds of join
| Join | Keeps |
|---|---|
| Inner | Only rows with a match in both tables |
| Left | Every row of the left table, with gaps where nothing matched |
| Right | Every row of the right table |
| Outer | Every row from both tables |
For adding facts to a main table, the left join is almost always right: you keep every visit and attach what you can. The inner join silently throws away anything that does not match. Run the program below and compare the row counts.
import pandas as pd
visits = pd.read_csv("clean.csv")
people = pd.read_csv("customers.csv")
print("visits:", len(visits), "| customers:", len(people))
print("customer is unique in people:", people["customer"].is_unique)
inner = visits.merge(people, on="customer", how="inner")
left = visits.merge(people, on="customer", how="left", indicator=True)
print("inner join rows:", len(inner))
print("left join rows:", len(left))
nomatch = left[left["_merge"] == "left_only"]
print("visits with no match:", len(nomatch), list(nomatch["customer"].unique()))
twice = pd.concat([people, people[people["customer"] == "Dina Shah"]])
fan = visits.merge(twice, on="customer", how="left")
print("after a repeated key, rows:", len(fan))
print("total cost then:", round(fan["cost"].sum(), 2), "| true total:", round(visits["cost"].sum(), 2))
try:
visits.merge(twice, on="customer", validate="many_to_one")
except Exception as error:
print("validate refused:", type(error).__name__)
people["customer"] = people["customer"].replace({"Hanna Lee": "Hana Lee"})
good = visits.merge(people, on="customer", how="left", validate="many_to_one")
print("fixed rows:", len(good), "| unmatched:", int(good["member"].isna().sum()))
for who, part in good.groupby("member"):
print("member " + who + ": " + str(len(part)) + " visits, mean cost " + str(round(part["cost"].mean(), 2)))
never = people[~people["customer"].isin(visits["customer"])]
print("never visited:", list(never["customer"]))
visits: 38 | customers: 9
customer is unique in people: True
inner join rows: 35
left join rows: 38
visits with no match: 3 ['Hana Lee']
after a repeated key, rows: 44
total cost then: 591.0 | true total: 515.5
validate refused: MergeError
fixed rows: 38 | unmatched: 0
member no: 20 visits, mean cost 13.62
member yes: 18 visits, mean cost 13.5
never visited: ['Ivy Ross']
The table has 38 visits, and the inner join returns only 35 rows, while the left join returns all 38. The difference is 3 visits with no match, all for Hana Lee. A careless analyst using the inner join would lose those visits without a word and report a lower total. The cause is a spelling difference: the customers file says Hanna Lee. The marker column that pandas adds when you ask for an indicator showed exactly which rows failed to match.
The silent danger: repeated keys
The second half of the program repeats Dina Shah’s row in the customers table, as if she had been entered twice, for example after a change of address. Now every one of her visits matches two rows, and the joined table grows from 38 to 44 rows. The total cost jumps from the true 515.5 to 591.0. No error appeared. This is called a fan-out, and it is a common source of wrong totals in reports.
The defence is the validate option. Asking for a many-to-one join tells pandas to refuse if the right-hand table repeats a key, and the program shows it refusing with a merge error. Using it costs one word and can save a report.
A safe routine for every join
- Count the rows of both tables before the join, and check that the key is unique in the table that should have one row per key.
- Choose the join type on purpose, and ask for the indicator so you can see unmatched rows.
- Count the rows afterward. For a left join with a unique right-hand key, the count must equal the left table.
- Compare an important total before and after. If it moved, find out why.
- Fix the cause at the source, for example the spelling, and join again with the validation turned on.
After the spelling is fixed, the joined table has 38 rows and no unmatched visits. Members made 18 visits with a mean cost of 13.5, and other customers made 20 visits with a mean cost of 13.62. The two groups look alike, which is itself a finding, though with so few customers it is not strong evidence. The last line also shows a useful by-product: Ivy Ross is in the customers file but never visited, something a join can reveal and a single table cannot.
Trusting the row count to be right because the join did not fail. A join can run without errors and still duplicate or lose rows. Count before and after, every time.
The customers and their membership are made up. The misspelling was planted so that you can see a failed match. In real data the cause might be spacing, capital letters or a nickname.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What is a join key?
- A password for the file
- A column present in both tables that identifies the same thing
- The first row of a table
- A chart axis
The customer name linked the visits to the customers table.
Which join keeps every row of the main table, with gaps where nothing matched?
- An inner join
- A sort
- A left join
- A filter
The inner join dropped the visits that did not match.
Why did the total cost rise after a customer row was repeated?
- Each of her visits matched two rows, so the visits were counted twice
- The prices increased
- The file was reread
- Ratings were added to cost
This is called a fan-out.
What does asking for a many-to-one check do?
- It sorts the table
- It deletes duplicates silently
- It refuses the merge if the right-hand table repeats a key
- It rounds the cost
The program showed pandas refusing with a merge error.
What should you compare after every join?
- The row counts and an important total before and after
- The file size
- The colours of the chart
- The order of the columns
A join can change numbers without an error message.
Summary
- A join needs a key that identifies the same thing in both tables.
- A left join keeps every row of the main table.
- A repeated key multiplies rows and inflates totals.
- Count rows and compare totals before and after every join.
Questions
Which join should I use most of the time?
A left join, when you are adding facts to a main table. It keeps every row you already have. Use an inner join only when you really want to discard rows that do not match.
Why use a customer number instead of a name?
Names can be spelled in many ways and can be shared by two people. A number assigned by the system is unique and does not change when someone moves or marries.
Is the same idea used in SQL and spreadsheets?
Yes. SQL has the same join types, and a spreadsheet lookup behaves like a left join. The same dangers of unmatched and repeated keys apply everywhere.
