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:
| Join | Keeps |
|---|---|
| Inner | Only rows found in both tables |
| Left | Every 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.
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
Discussion
Ask. Answer. Build together.
Loading…