Joining Tables with merge
merge lines up the rows of two tables by a key column they share, the job a spreadsheet's VLOOKUP does, with on= when the key has the same name in both, left_on and right_on when it doesn't, and suffixes to tell apart other columns the two tables share.
Apprentice · Python · Database · Updated
After this lesson you can
- Join two tables on a shared key column with merge and on=, and explain why the key must name exactly one row
- Join on keys with different names using left_on and right_on, and tell apart clashing columns with suffixes
Key takeaways
- fall.merge(students, on="student_id") adds each student's columns to their scores row, matching the rows by student_id.
- A key must name exactly one row in the lookup table. A student_id does; a first name, shared by ten students, doesn't.
- When both tables have a column with the same name that isn't the key, suffixes=("_fall", "_spring") says which table each came from.
- When the key has a different name in each table, left_on= and right_on= name it on each side, and both columns are kept.
The scores file has a student_id and four numbers. The principal wants names and homerooms beside the scores, and those live in a different file. Putting two tables side by side by a column they share is one of the jobs you'll do most with data: orders and customers, sales and products, scores and students.
The example is Oakridge Middle School's records: fall_scores.csv (one row per student, student_id and quiz_1 to quiz_4) and students.csv (student_id, first_name, grade, homeroom), loaded as fall and students.
A score with no name
import pandas as pd
students = pd.read_csv("students.csv")
fall = pd.read_csv("fall_scores.csv")
students.head(3)
student_id first_name grade homeroom
0 1001 Maya 8 8A
1 1002 Ben 7 7A
2 1003 Iris 8 8B
To put a name on each score by hand, you'd take a student_id from the scores, find the same number in the student list, copy the name across, and repeat 239 times. In a spreadsheet, that's what VLOOKUP or XLOOKUP does, one formula per row.
merge: matching rows by a key
A coat check works the same way: your ticket number matches you to your coat. The column both tables share, here student_id, is the key. merge takes each row of the first table, finds the row with the same key in the second, and puts the two side by side. on= names the key:
with_names = fall.merge(students, on="student_id")
print("rows before:", len(fall))
print("rows after:", len(with_names))
with_names.head(3)
rows before: 240
rows after: 240
student_id quiz_1 quiz_2 quiz_3 quiz_4 first_name grade homeroom
0 1001 74 59 84 72 Maya 8 8A
1 1002 85 74 76 79 Ben 7 7A
2 1003 78 74 77 77 Iris 8 8B
Every score row now carries its student's name, grade and homeroom, and the count is the same before and after: each score found exactly one student. Databases call this a join, and the next lesson, Inner, Left, Right and Outer Joins, covers rows that find no match.
A key names one row
Why student_id and not first_name? Count them:
print("different student_ids:", students["student_id"].nunique())
print("different first names:", students["first_name"].nunique())
different student_ids: 240
different first names: 50
240 students share 50 first names; ten of them are Maya. Join on first_name, and every Maya's scores would be matched to all ten Mayas. A key works only when each value names exactly one row of the lookup table, and an id made for that purpose does. In a database, a column like that is the table's primary key.
Clashing column names: suffixes
To put fall and spring side by side, merge the two score files on student_id. Both have quiz_1 to quiz_4, so pandas has to rename them:
spring = pd.read_csv("spring_scores.csv")
side = fall.merge(spring, on="student_id")
print(list(side.columns))
['student_id', 'quiz_1_x', 'quiz_2_x', 'quiz_3_x', 'quiz_4_x', 'quiz_1_y', 'quiz_2_y', 'quiz_3_y', 'quiz_4_y']
_x is the first table and _y the second, which nobody will remember next week. suffixes= gives them names that say what they are:
side = fall.merge(spring, on="student_id", suffixes=("_fall", "_spring"))
side[["student_id", "quiz_1_fall", "quiz_1_spring"]].head(3)
student_id quiz_1_fall quiz_1_spring
0 1001 74 71
1 1002 85 82
2 1003 78 91
Different names for the key: left_on and right_on
Files from two different systems often name the same key differently. Say the student list came from the office's system, which calls the column id (renamed here to show it). left_on= names the key in the first table, the left one, and right_on= in the second:
roster = students.rename(columns={"student_id": "id"})
joined = fall.merge(roster, left_on="student_id", right_on="id")
print(list(joined.columns))
print("rows:", len(joined))
['student_id', 'quiz_1', 'quiz_2', 'quiz_3', 'quiz_4', 'id', 'first_name', 'grade', 'homeroom']
rows: 240
Both key columns are kept, holding the same numbers. joined.drop(columns="id") removes the spare one.
Check yourself
2 questions to try on the lesson page.
So what
merge is the lookup you'd otherwise do by hand for every row: pick a key that names exactly one row, check the row count before and after, and the two tables become one.