Merge pandas DataFrames
Join two tables on a shared column, just like a SQL join, and choose which rows to keep.
- merge(on="id")
- how="inner", "left", "right" and "outer"
- Handling columns with the same name
- 1 Ada
- 2 Lin
- 1 → 95
- 2 → 88
- 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.
students🏷️ Name tags with an idscores📝 Test papers with an idmerge(scores, on="id")📎 Staple together the ones with the same idid, name, score📋 One row per matched studentmerge 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
students.merge(scores, on="id")matches rows whoseidis the same.- Each result row has the columns of both tables: id, name and score.
- By default only ids present in both tables are kept.
- The
idcolumn 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
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"))
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.
- Add rows, columns, and missing values.
- Combine filtering, sorting, and groupby into a small report.
- Load a real file, inspect dtypes, and handle invalid data.
Common mistakes
Losing rows without noticing
The default inner join drops students who have no score.
students.merge(scores, on="id")
students.merge(scores, on="id", how="left")
Finished this lesson?
Mark it as learned to track your progress.