# 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 November 19, 2026

By Gregor Dzierzon

## 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

```python
import pandas as pd

students = pd.read_csv("students.csv")
fall = pd.read_csv("fall_scores.csv")
students.head(3)
```

```text
   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:

```python
with_names = fall.merge(students, on="student_id")
print("rows before:", len(fall))
print("rows after:", len(with_names))
with_names.head(3)
```

```text
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:

```python
print("different student_ids:", students["student_id"].nunique())
print("different first names:", students["first_name"].nunique())
```

```text
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:

```python
spring = pd.read_csv("spring_scores.csv")
side = fall.merge(spring, on="student_id")
print(list(side.columns))
```

```text
['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:

```python
side = fall.merge(spring, on="student_id", suffixes=("_fall", "_spring"))
side[["student_id", "quiz_1_fall", "quiz_1_spring"]].head(3)
```

```text
   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:

```python
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))
```

```text
['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

- [merge()](https://www.ask-codey.com/glossary/merge-python)
- [Key](https://www.ask-codey.com/glossary/key)
- [Primary Key](https://www.ask-codey.com/glossary/primary-key)

## Related

- [Inner, Left, Right and Outer Joins](https://www.ask-codey.com/lessons/pandas-join-types)
- [Stacking Tables with concat](https://www.ask-codey.com/lessons/pandas-concat)

Source: https://www.ask-codey.com/lessons/pandas-merge
