Run pandas Online

Merge pandas DataFrames

Join two tables on a shared column, just like a SQL join, and choose which rows to keep.

  • Intermediate
  • 2 min read
  • 1 runnable example
What you'll learn
  • merge(on="id")
  • how="inner", "left", "right" and "outer"
  • Handling columns with the same name
merge(on="id")
students
  • 1 Ada
  • 2 Lin
scores
  • 1 → 95
  • 2 → 88
result
  • 1 Ada 95
  • 2 Lin 88

Match rows by a key

The problem

Related data is often stored in separate tables: student names in one, scores in another, joined by a student ID. To analyze them together you need to match rows by that shared key, like a database join.

Think of it like this

Merging tables is like matching name tags with test papers by student number. You lay the two piles side by side and staple together the tag and the paper that have the same number.

From matching tags and papers to code
students🏷️ Name tags with an id
scores📝 Test papers with an id
merge(scores, on="id")📎 Staple together the ones with the same id
id, name, score📋 One row per matched student
Rows are matched by a shared key column.
In one sentence

merge combines two DataFrames by matching values in one or more key columns; by default it keeps only keys found in both tables (an inner join).

Key points

  1. students.merge(scores, on="id") matches rows whose id is the same.
  2. Each result row has the columns of both tables: id, name and score.
  3. By default only ids present in both tables are kept.
  4. The id column appears once, because it is the key.

When should you use it?

  • Combining tables from a database or several files.
  • Adding information such as names or prices to rows that only hold an ID.
  • Comparing two versions of a dataset.

When not to use it

  • Stacking tables with the same columns on top of each other: use pd.concat.
  • Keys that are not unique on either side: each match multiplies rows, so check duplicates first.

Example code

Run code →
main.py
import pandas as pd

students = pd.DataFrame({"id": [1, 2], "name": ["Ada", "Lin"]})
scores = pd.DataFrame({"id": [1, 2], "score": [95, 88]})

print(students.merge(scores, on="id"))
Get a Run button for this example

Result of running the code

id name  score
0   1  Ada     95
1   2  Lin     88

Code explanation

students holds the names and scores holds the results, and both tables share an id column. merge(scores, on="id") pairs each student row with the score row that has the same id. Because the default is an inner join, only ids found in both tables appear, and the result has the columns of both tables.

Change the values and run the program in the CodeUtility online Python compiler without installing Python locally.

Practice exercises

Modify the runnable example with the exercises below to build understanding beyond copying the result.

  1. Add rows, columns, and missing values.
  2. Combine filtering, sorting, and groupby into a small report.
  3. Load a real file, inspect dtypes, and handle invalid data.
Run in the Python compiler →

Common mistakes

Losing rows without noticing

The default inner join drops students who have no score.

Problem
students.merge(scores, on="id")
Fix
students.merge(scores, on="id", how="left")

Finished this lesson?

Mark it as learned to track your progress.