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

By

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.

Key terms

Related