21 Joining tables

22 Joining tables

You will spend more of your career joining tables than running fancy regressions. AI will help with both — but the join is where it quietly lies to you. This chapter is short, dense, and worth reading twice.

22.1 What you will get from this chapter

The four join types in plain words, with a one-line decision rule.
The most common AI failure on joins, and a check that catches it.
A pattern that works for joins by (key, time) and other compound keys.

22.2 The four joins, in one paragraph

Two tables, A and B, share a key column. An inner join keeps only rows where the key appears in both. A left join keeps every row of A, fills nulls when B is missing. A right join is the same with A and B swapped (rarely needed; reverse the inputs and use left). A full (or outer) join keeps every row of both, fills nulls on whichever side is missing.

The decision rule:

I want to attach extra info from B to my main table A, and I want to keep all of A’s rows even when B is missing: left join. This is what you usually want.
I want only rows where I have complete info from both: inner join.
I want to find the overlap and the gaps explicitly: full join.

That is most of the substance. The pitfalls live in the rest of the chapter.

22.3 A worked example: footballers and salaries

You have two tables. players has 500 rows — name, club, position. salaries has 480 rows — name, weekly wage in pounds. You want a merged table with player and wage.

A naive prompt to your chat AI gets you something like:

merged = players.merge(salaries, on="name")

This is an inner join. It silently drops the 20 players with no salary record. If those 20 are random, fine. If those 20 are the youngest academy players whose contracts are not on the public site, you have just biased your dataset. AI has no way to know which it is.

The right move:

merged = players.merge(salaries, on="name", how="left", indicator=True)
print(merged["_merge"].value_counts())
# left_only      20
# both          480
# right_only     0

Now you can see the missing 20 and decide. Drop them deliberately if appropriate. Investigate them otherwise.

indicator=True is the cheapest insurance pandas has. Use it on every non-trivial join.

22.4 The check: row counts before and after

Always print these. Two lines of code, three seconds, no judgement required.

print(f"Left:   {len(players)} rows")
print(f"Right:  {len(salaries)} rows")
merged = players.merge(salaries, on="name", how="left")
print(f"Merged: {len(merged)} rows")

For a left join, len(merged) should be greater than or equal to len(players). If it is greater — by a lot — you have a one-to-many situation you may not have noticed.

22.5 One-to-many: the silent duplicator

Two tables. You join them on a key. The merge has more rows than you started with. This means the key is not unique on the right side: one row of A matched several rows of B.

This is sometimes what you want. Often it is not. Three habits prevent the surprise.

1. Check uniqueness before you join.

assert salaries["name"].is_unique, "Salaries has duplicate names — investigate."

2. Use validate= in pandas to declare your expectation.

merged = players.merge(salaries, on="name", how="left", validate="one_to_one")

If the assumption is wrong, pandas raises an error instead of silently producing a longer table. validate= accepts "one_to_one", "one_to_many", "many_to_one", "many_to_many".

3. If you do mean one-to-many, name it explicitly. Future-you will thank you.

22.6 Compound keys: time, place, and the gotcha

Many joins are not on a single column. Player wages by season. Air-quality readings by city and date. Hotel prices by city, month, year. The right join key is all of them together.

prices.merge(events, on=["city", "month", "year"], how="left", validate="m:1")

Two failure modes here.

Type mismatch. city is "Vienna" in one table and "VIENNA" in the other. They will not match. df["city"] = df["city"].str.lower() on both before joining.
Time-unit mismatch. prices is daily; events is weekly. Decide which time unit you want and aggregate the other one to it before joining. Do not just join on the date column and hope.

22.7 Where AI helps · Where AI bluffs

Helps. Writing the join syntax in three lines. Spotting a likely type mismatch when you describe the data. Suggesting indicator=True and validate= if you ask it to make the join “safer.”

Bluffs. Assuming your join is one-to-one when the data is one-to-many. Defaulting to inner joins without flagging the silent drop. Inventing column names. Not noticing case-sensitivity in the key.

22.8 Keep this with you, not the AI

The unit of observation after the join. Did it change? AI does not know whether that change is correct.
The decision about what to do with non-matching rows (drop, keep with NA, flag, investigate).
Any judgement about whether the missing rows are random or systematic. This is not a question the data alone can answer.

22.9 Try this

Take any two CSVs of your own with a shared key. Run the row-count check. Use merge(..., how="left", indicator=True, validate=...). Print _merge.value_counts(). Confirm the counts are what you expected.

If they are, you have done the join carefully, in three minutes. If they are not, you have caught a bug, in three minutes. Either outcome is a win.

22.10 AI and me

How did AI support me here?
How did AI fail me?
How did AI extend me?

22.11 Where to go next

Writing reports with AI — turning a clean joined table into something a reader can act on.

--- title: "Joining tables" --- # Joining tables You will spend more of your career joining tables than running fancy regressions. AI will help with both — but the join is where it quietly lies to you. This chapter is short, dense, and worth reading twice. ## What you will get from this chapter - The four join types in plain words, with a one-line decision rule. - The most common AI failure on joins, and a check that catches it. - A pattern that works for joins by `(key, time)` and other compound keys. ## The four joins, in one paragraph Two tables, A and B, share a key column. An **inner join** keeps only rows where the key appears in both. A **left join** keeps every row of A, fills nulls when B is missing. A **right join** is the same with A and B swapped (rarely needed; reverse the inputs and use left). A **full** (or outer) join keeps every row of both, fills nulls on whichever side is missing. The decision rule: - *I want to attach extra info from B to my main table A, and I want to keep all of A's rows even when B is missing*: **left join.** This is what you usually want. - *I want only rows where I have complete info from both*: **inner join.** - *I want to find the overlap and the gaps explicitly*: **full join.** That is most of the substance. The pitfalls live in the rest of the chapter. ## A worked example: footballers and salaries You have two tables. `players` has 500 rows — name, club, position. `salaries` has 480 rows — name, weekly wage in pounds. You want a merged table with player and wage. A naive prompt to your chat AI gets you something like: ```python merged = players.merge(salaries, on="name") ``` This is an inner join. It silently drops the 20 players with no salary record. If those 20 are *random*, fine. If those 20 are *the youngest academy players whose contracts are not on the public site*, you have just biased your dataset. AI has no way to know which it is. The right move: ```python merged = players.merge(salaries, on="name", how="left", indicator=True) print(merged["_merge"].value_counts()) # left_only 20 # both 480 # right_only 0 ``` Now you can *see* the missing 20 and decide. Drop them deliberately if appropriate. Investigate them otherwise. `indicator=True` is the cheapest insurance pandas has. Use it on every non-trivial join. ## The check: row counts before and after Always print these. Two lines of code, three seconds, no judgement required. ```python print(f"Left: {len(players)} rows") print(f"Right: {len(salaries)} rows") merged = players.merge(salaries, on="name", how="left") print(f"Merged: {len(merged)} rows") ``` For a left join, `len(merged)` should be greater than or equal to `len(players)`. If it is greater — by a lot — you have a one-to-many situation you may not have noticed. ## One-to-many: the silent duplicator Two tables. You join them on a key. The merge has *more rows than you started with*. This means the key is not unique on the right side: one row of A matched several rows of B. This is sometimes what you want. Often it is not. Three habits prevent the surprise. **1. Check uniqueness before you join.** ```python assert salaries["name"].is_unique, "Salaries has duplicate names — investigate." ``` **2. Use `validate=`** in pandas to declare your expectation. ```python merged = players.merge(salaries, on="name", how="left", validate="one_to_one") ``` If the assumption is wrong, pandas raises an error instead of silently producing a longer table. `validate=` accepts `"one_to_one"`, `"one_to_many"`, `"many_to_one"`, `"many_to_many"`. **3. If you do mean one-to-many, name it explicitly.** Future-you will thank you. ## Compound keys: time, place, and the gotcha Many joins are not on a single column. Player wages by season. Air-quality readings by city and date. Hotel prices by city, month, year. The right join key is *all of them together*. ```python prices.merge(events, on=["city", "month", "year"], how="left", validate="m:1") ``` Two failure modes here. - *Type mismatch.* `city` is `"Vienna"` in one table and `"VIENNA"` in the other. They will not match. `df["city"] = df["city"].str.lower()` on both before joining. - *Time-unit mismatch.* `prices` is daily; `events` is weekly. Decide which time unit you want and aggregate the other one to it before joining. Do not just join on the date column and hope. ## Where AI helps · Where AI bluffs **Helps.** Writing the join syntax in three lines. Spotting a likely type mismatch when you describe the data. Suggesting `indicator=True` and `validate=` if you ask it to make the join "safer." **Bluffs.** Assuming your join is one-to-one when the data is one-to-many. Defaulting to inner joins without flagging the silent drop. Inventing column names. Not noticing case-sensitivity in the key. ## Keep this with you, not the AI - The unit of observation *after* the join. Did it change? AI does not know whether that change is correct. - The decision about what to do with non-matching rows (drop, keep with NA, flag, investigate). - Any judgement about whether the missing rows are random or systematic. This is not a question the data alone can answer. ## Try this Take any two CSVs of your own with a shared key. Run the row-count check. Use `merge(..., how="left", indicator=True, validate=...)`. Print `_merge.value_counts()`. Confirm the counts are what you expected. If they are, you have done the join *carefully*, in three minutes. If they are not, you have caught a bug, in three minutes. Either outcome is a win. ## AI and me 1. How did AI **support** me here? 2. How did AI **fail** me? 3. How did AI **extend** me? ## Where to go next [Writing reports with AI](08-reports.qmd) — turning a clean joined table into something a reader can act on.