lesson
50 min

Joining Tables

Combine two public tables on a shared key, find the rows that did not match, and build a modeling table.

Objective

By the end of this lesson, you can join two tables on a key, check which rows failed to match, and explain why.

Keys

A key is a column that identifies each row, so two tables can be lined up. For U.S. counties, the standard key is the FIPS code: a five-digit number for every county (two digits for the state, three for the county).

Names are bad keys. "Washington County" exists in many states. Spelling and capital letters differ between sources. Codes are safer.

Joins

A join combines two tables wherever their keys match. The most common types:

JoinKeeps
InnerOnly rows found in both tables
LeftEvery row from the left table, with blanks where the right table had no match

A left join plus a check for blanks is the safest habit. It shows you what did not match instead of silently dropping it.

Run it. Two made-up tables share a FIPS key. One county has no weather match. Change the code so it also prints how many counties matched.
Python 3
Ctrl+Enter to run | Tab to indent | Python runs in your browser via Pyodide

Notice the FIPS codes are text, not numbers. As a number, 01001 would lose its leading zero and stop matching. Read key columns as text.

The same thing in pandas

import pandas as pd

# dtype=str keeps leading zeros in the key
income = pd.read_csv("income.csv", dtype={"fips": str})
weather = pd.read_csv("weather.csv", dtype={"fips": str})

merged = income.merge(weather, on="fips", how="left", indicator=True)
print(merged["_merge"].value_counts())   # both / left_only
print(merged[merged["_merge"] == "left_only"].head())

One row per prediction target

Before modeling, shape the table so one row is one thing you want to predict. If you predict something about counties, one row per county. If you predict daily power demand, one row per day. Mixing levels (some rows are counties, some are states) breaks models in quiet ways.

A national example

Judgment check

Joining data about the same people from different sources can reveal things no single source shows. That can help, and it can also expose people. Join place-level data freely. Be very careful joining person-level data, and never do it with data about minors without clear permission and a reason.

Reflection

Private. Saved only in this browser. No one else can see it.

Ask. Answer. Build together.