Merging DataFrames in Pandas
Learn how to combine related datasets using common columns or indexes with Pandas merge().
Learning objectives: Understand merge keys, inner joins, left joins, right joins, outer joins, multiple keys, suffixes, and merge validation.
1. What Is Merging?
Merging combines two DataFrames by matching related values in one or more columns. It is similar to joining tables in a relational database.
Real-world example
A school stores student details in one table and exam scores in another. Both tables contain a StudentID column. You can merge them to create one dataset containing student names and their scores.
Merging matches rows using related key values rather than simply stacking tables.
2. Create Sample DataFrames
We will create one DataFrame for student information and another for exam results.
import pandas as pd
students = pd.DataFrame({
"StudentID": [101, 102, 103, 104],
"Student": ["Samadhan", "Pradnya", "Samiksha", "Aarav"],
"Class": ["A", "A", "B", "B"]
})
scores = pd.DataFrame({
"StudentID": [101, 102, 104, 105],
"Score": [85, 92, 76, 88]
})
print("Students:")
print(students)
print("\nScores:")
print(scores)
Expected output
Students: StudentID Student Class 0 101 Samadhan A 1 102 Pradnya A 2 103 Samiksha B 3 104 Aarav B Scores: StudentID Score 0 101 85 1 102 92 2 104 76 3 105 88
Notice that student 103 has no score record, while student 105 has a score record but is not present in the students table. These differences will help demonstrate join types.
3. Basic Merge with a Common Column
Use pd.merge() to combine DataFrames. If both DataFrames have a column with the same name, specify it using on.
result = pd.merge(
students,
scores,
on="StudentID"
)
print(result)
Expected output
StudentID Student Class Score 0 101 Samadhan A 85 1 102 Pradnya A 92 2 104 Aarav B 76
By default, pd.merge() uses an inner join. Only StudentID values present in both DataFrames appear in the result.
on="StudentID" when both DataFrames contain the same key column name. Explicitly specifying the key makes your code easier to understand.
4. Inner Join
An inner join keeps only rows whose merge keys match in both DataFrames.
inner_result = pd.merge(
students,
scores,
on="StudentID",
how="inner"
)
print(inner_result)
Expected output
StudentID Student Class Score 0 101 Samadhan A 85 1 102 Pradnya A 92 2 104 Aarav B 76
StudentID 103 is excluded because there is no matching score. StudentID 105 is excluded because there is no matching student record.
5. Left Join
A left join keeps every row from the left DataFrame and adds matching information from the right DataFrame. When a match does not exist, the right-side columns contain missing values.
left_result = pd.merge(
students,
scores,
on="StudentID",
how="left"
)
print(left_result)
Expected output
StudentID Student Class Score 0 101 Samadhan A 85.0 1 102 Pradnya A 92.0 2 103 Samiksha B NaN 3 104 Aarav B 76.0
All four students remain in the result. Samiksha's score is NaN because StudentID 103 is absent from the scores DataFrame.
6. Right Join
A right join keeps every row from the right DataFrame and attaches matching information from the left DataFrame.
right_result = pd.merge(
students,
scores,
on="StudentID",
how="right"
)
print(right_result)
Expected output
StudentID Student Class Score 0 101 Samadhan A 85 1 102 Pradnya A 92 2 104 Aarav B 76 3 105 NaN NaN 88
Every score record remains in the result. StudentID 105 has no matching student details, so the Student and Class values are missing.
7. Outer Join
An outer join keeps all key values from both DataFrames. Where a key has no match on one side, the corresponding columns contain missing values.
outer_result = pd.merge(
students,
scores,
on="StudentID",
how="outer"
)
print(outer_result)
Expected output
StudentID Student Class Score 0 101 Samadhan A 85.0 1 102 Pradnya A 92.0 2 103 Samiksha B NaN 3 104 Aarav B 76.0 4 105 NaN NaN 88.0
The outer join includes every StudentID found in either table, including 103 and 105, which do not have matches on both sides.
The join type determines which unmatched keys are preserved.
8. Understand the how Parameter
| Join type | Keys retained | Use case |
|---|---|---|
inner |
Keys found in both DataFrames | Keep records that match in both tables. |
left |
All left-side keys | Keep every student and add available scores. |
right |
All right-side keys | Keep every score record and add available student details. |
outer |
Keys from both sides | Identify matched and unmatched records from both tables. |
9. Merge Using Different Column Names
Sometimes the key column has different names in the two DataFrames. Use left_on and right_on to specify the matching columns.
student_info = pd.DataFrame({
"StudentID": [101, 102, 103],
"Student": ["Samadhan", "Pradnya", "Samiksha"]
})
exam_results = pd.DataFrame({
"ID": [101, 102, 104],
"Score": [85, 92, 76]
})
result = pd.merge(
student_info,
exam_results,
left_on="StudentID",
right_on="ID",
how="inner"
)
print(result)
Expected output
StudentID Student ID Score 0 101 Samadhan 101 85 1 102 Pradnya 102 92
Because the key columns have different names, both columns appear in the result. You can drop the redundant key column if you do not need it.
result = result.drop(columns=["ID"])
10. Merge Using Multiple Columns
You can match records using more than one key column. This is useful when a single column is not sufficient to identify a record.
left_df = pd.DataFrame({
"Student": ["Samadhan", "Pradnya", "Samadhan"],
"Subject": ["Math", "Math", "Science"],
"Score": [85, 92, 89]
})
right_df = pd.DataFrame({
"Student": ["Samadhan", "Pradnya", "Samadhan"],
"Subject": ["Math", "Math", "Science"],
"Teacher": ["Mr. Patil", "Ms. Shah", "Mr. Rao"]
})
result = pd.merge(
left_df,
right_df,
on=["Student", "Subject"],
how="inner"
)
print(result)
Expected output
Student Subject Score Teacher 0 Samadhan Math 85 Mr. Patil 1 Pradnya Math 92 Ms. Shah 2 Samadhan Science 89 Mr. Rao
Both Student and Subject must match for a pair of rows to join.
11. Handle Overlapping Column Names with suffixes
When both DataFrames contain a non-key column with the same name, Pandas adds suffixes to distinguish the columns.
first = pd.DataFrame({
"StudentID": [101, 102],
"Student": ["Samadhan", "Pradnya"],
"Score": [85, 92]
})
second = pd.DataFrame({
"StudentID": [101, 102],
"Student": ["Samadhan P.", "Pradnya S."],
"Attendance": [95, 98]
})
result = pd.merge(
first,
second,
on="StudentID",
suffixes=("_left", "_right")
)
print(result)
Expected output
StudentID Student_left Score Student_right Attendance 0 101 Samadhan 85 Samadhan P. 95 1 102 Pradnya 92 Pradnya S. 98
The suffixes parameter helps you identify which DataFrame each overlapping column came from.
12. Use the indicator Parameter
The indicator=True option adds a column called _merge showing where each output row came from.
result = pd.merge(
students,
scores,
on="StudentID",
how="outer",
indicator=True
)
print(result[["StudentID", "Student", "Score", "_merge"]])
Expected output
StudentID Student Score _merge 0 101 Samadhan 85.0 both 1 102 Pradnya 92.0 both 2 103 Samiksha NaN left_only 3 104 Aarav 76.0 both 4 105 NaN 88.0 right_only
The indicator column has three possible values:
both: the key was found in both DataFrames.left_only: the key was found only in the left DataFrame.right_only: the key was found only in the right DataFrame.
This is especially useful for auditing data and finding unmatched records.
13. Validate Merge Relationships
The validate parameter lets you check the expected relationship between key values before accepting a merge result.
result = pd.merge(
students,
scores,
on="StudentID",
how="left",
validate="one_to_one"
)
print(result)
The validation succeeds here because each StudentID occurs at most once in each input DataFrame.
| Validation value | Expected relationship |
|---|---|
one_to_one or 1:1 |
Keys must be unique in both DataFrames. |
one_to_many or 1:m |
Keys must be unique in the left DataFrame. |
many_to_one or m:1 |
Keys must be unique in the right DataFrame. |
many_to_many or m:m |
Many-to-many relationships are allowed; uniqueness is not checked. |
14. Understand Duplicate Keys
If a key appears multiple times in both DataFrames, Pandas can produce multiple matching combinations. This is expected many-to-many merge behavior.
orders = pd.DataFrame({
"StudentID": [101, 101],
"Order": ["Book", "Pen"]
})
payments = pd.DataFrame({
"StudentID": [101, 101],
"Payment": [100, 50]
})
result = pd.merge(
orders,
payments,
on="StudentID"
)
print(result)
Expected output
StudentID Order Payment 0 101 Book 100 1 101 Book 50 2 101 Pen 100 3 101 Pen 50
Each of the two order rows matches each of the two payment rows, resulting in four rows. If you expected only two rows, inspect the key uniqueness and the business meaning of the relationship before merging.
15. Common Mistakes and Best Practices
Mistake 1: Using the wrong join type
An inner join removes unmatched keys. A left join retains all left-side records. Choose the join based on which records must remain in the result.
Mistake 2: Forgetting that keys can repeat
Repeated keys can increase the number of output rows. Check key uniqueness when you expect one row per entity.
print(students["StudentID"].is_unique) print(scores["StudentID"].is_unique)
Mistake 3: Mismatched key names
If the key columns have different names, specify left_on and right_on rather than assuming Pandas will match them automatically.
Mistake 4: Ignoring missing matches
Use indicator=True to identify unmatched records. Missing values after a left or outer join may be meaningful data-quality findings.
Mistake 5: Merging columns with incompatible types
Make sure matching keys use compatible types. For example, the integer 101 and the string "101" may not match as intended.
students["StudentID"] = students["StudentID"].astype("int64")
scores["StudentID"] = scores["StudentID"].astype("int64")
Only convert types when the conversion is appropriate for your actual data.
16. Practical Mini Project: Student Report
Combine student details, exam scores, and attendance into one report using StudentID.
import pandas as pd
students = pd.DataFrame({
"StudentID": [101, 102, 103, 104],
"Student": ["Samadhan", "Pradnya", "Samiksha", "Aarav"],
"Class": ["A", "A", "B", "B"]
})
scores = pd.DataFrame({
"StudentID": [101, 102, 104],
"Score": [85, 92, 76]
})
attendance = pd.DataFrame({
"StudentID": [101, 102, 103, 104],
"Attendance": [95, 98, 88, 90]
})
report = pd.merge(
students,
scores,
on="StudentID",
how="left",
validate="one_to_one"
)
report = pd.merge(
report,
attendance,
on="StudentID",
how="left",
validate="one_to_one"
)
print(report)
Expected output
StudentID Student Class Score Attendance 0 101 Samadhan A 85.0 95 1 102 Pradnya A 92.0 98 2 103 Samiksha B NaN 88 3 104 Aarav B 76.0 90
The left joins preserve every student while adding available scores and attendance information. Samiksha remains in the report even though her score record is missing.
17. Practice Exercises
Exercise 1: Inner merge
Merge the students and scores DataFrames using StudentID and keep only matching records.
Hint: Use how="inner".
Exercise 2: Left merge
Keep all students and add their scores where available.
Hint: Use how="left".
Exercise 3: Find unmatched keys
Perform an outer merge and identify records that exist in only one DataFrame.
Hint: Use indicator=True.
Exercise 4: Different key names
Merge two DataFrames where one uses StudentID and the other uses ID.
Hint: Use left_on and right_on.
Exercise 5: Multiple keys
Merge exam results and teacher assignments using both Student and Subject.
Hint: Use a list in on.
Exercise 6: Validate uniqueness
Try merging two DataFrames with duplicate StudentID values using validate="one_to_one". Observe the error and explain why it occurs.
18. Revision Questions
- What is merging in Pandas?
- What is the default join type for
pd.merge()? - How does an inner join differ from an outer join?
- When should you use
left_onandright_on? - What does the
suffixesparameter do? - How can you identify unmatched rows after a merge?
- What does
validate="one_to_one"check? - Why can duplicate keys increase the number of merged rows?
- How can you combine three related DataFrames into one report?
- What is the difference between merging and concatenation?
Summary
Pandas pd.merge() combines DataFrames by matching key columns or indexes. Inner joins retain matching keys, left joins preserve all left-side keys, right joins preserve all right-side keys, and outer joins preserve keys from both sides. The left_on, right_on, suffixes, indicator, and validate parameters provide control over matching, overlapping columns, unmatched records, and key relationships.
Key concepts: pd.merge(), on, how, left_on, right_on, suffixes, indicator, and validate.