Merging DataFrames in Pandas
Learn how to combine related datasets using common columns, multiple keys, and indexes with the Pandas merge() function.
Learning objectives: Understand merge keys, inner joins, left joins, right joins, outer joins, suffixes, indicators, duplicate keys, and merge validation.
1. What Is Merging in Pandas?
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 information in one table and examination scores in another. Both tables contain a StudentID column. Merging these tables lets us create one report containing student names, classes, and scores.
Merging matches related records using key values rather than simply stacking rows.
2. Import Pandas and Create DataFrames
First, import Pandas and create two sample DataFrames. The first contains student details, and the second contains exam scores.
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
StudentID 103 appears only in the students table. StudentID 105 appears only in the scores table. These unmatched records will help demonstrate the different join types.
3. Basic Merge Using a Common Column
When both DataFrames contain a key column with the same name, use the on parameter to specify that column.
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() performs an inner join. Only StudentID values found in both DataFrames appear in the result.
on="StudentID". This makes the intended matching rule clear and helps avoid accidental matches on other shared column names.
4. Inner Join
An inner join returns only records 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 no matching score exists. StudentID 105 is excluded because no matching student record exists.
When should you use an inner join?
Use it when you only need records with a match in both datasets, such as students who have both a student record and an examination result.
5. Left Join
A left join keeps every row from the left DataFrame and adds matching information from the right DataFrame. If a match is missing, 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
Every student remains in the result. Samiksha has a missing score because StudentID 103 is not present in the scores DataFrame.
The Score column may be represented as floating-point values because missing numeric values can require a dtype that supports them.
When should you use a left join?
Use it when the left DataFrame is your main dataset and every record in it must remain in the output, even when additional information is unavailable.
6. Right Join
A right join keeps every row from the right DataFrame and adds 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 keys found in either DataFrame. Matching records are combined, while unmatched records receive missing values for unavailable columns.
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 output contains every StudentID from both datasets, including 103 and 105, which do not have matches in both tables.
The join type controls which unmatched records are preserved.
8. Compare the Four Join Types
| Join type | Records retained | Typical use |
|---|---|---|
inner |
Only matching keys. | Keep records found in both datasets. |
left |
All left-side keys. | Preserve every student record. |
right |
All right-side keys. | Preserve every score record. |
outer |
Keys from both sides. | Audit all matched and unmatched records. |
Choose the join type based on the records you must retain. Do not assume that an inner join is always the best choice.
9. Merge When Column Names Are Different
Sometimes the matching columns have different names. Use left_on and right_on to identify the corresponding keys.
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
Both key columns appear in the result because they have different names.
Remove the redundant key column
result = result.drop(columns=["ID"]) print(result)
Expected output
StudentID Student Score 0 101 Samadhan 85 1 102 Pradnya 92
10. Merge Using Multiple Columns
You can use multiple columns as merge keys. This is useful when one column alone does not uniquely identify a record.
exam_scores = pd.DataFrame({
"Student": ["Samadhan", "Pradnya", "Samadhan"],
"Subject": ["Math", "Math", "Science"],
"Score": [85, 92, 89]
})
teachers = pd.DataFrame({
"Student": ["Samadhan", "Pradnya", "Samadhan"],
"Subject": ["Math", "Math", "Science"],
"Teacher": ["Mr. Patil", "Ms. Shah", "Mr. Rao"]
})
result = pd.merge(
exam_scores,
teachers,
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 be combined.
on=["Student", "Subject"].
11. Handle Duplicate Column Names with suffixes
If both DataFrames contain a non-key column with the same name, Pandas adds suffixes to distinguish the columns in the output.
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 resulting columns make it clear which Student value came from each DataFrame.
12. Find Unmatched Records with indicator
Use indicator=True to add a column named _merge. It identifies whether each key was found in both inputs or only one.
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 has three possible values:
both: the key exists in both DataFrames.left_only: the key exists only in the left DataFrame.right_only: the key exists only in the right DataFrame.
Show only unmatched records
unmatched = result[result["_merge"] != "both"] print(unmatched[["StudentID", "_merge"]])
Expected output
StudentID _merge 2 103 left_only 4 105 right_only
This technique is useful for data-quality checks and reconciliation reports.
13. Validate Merge Relationships
The validate parameter checks whether key values follow an expected relationship between the two DataFrames.
result = pd.merge(
students,
scores,
on="StudentID",
how="left",
validate="one_to_one"
)
print(result)
This validation succeeds because each StudentID appears at most once in each input DataFrame.
| Validation option | Meaning |
|---|---|
one_to_one |
Keys must be unique in both DataFrames. |
one_to_many |
Keys must be unique in the left DataFrame. |
many_to_one |
Keys must be unique in the right DataFrame. |
many_to_many |
Many-to-many matching is allowed without uniqueness checks. |
14. Understand Duplicate Keys and Row Multiplication
When a key appears more than once in both DataFrames, each matching left-side row can combine with every matching right-side row.
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
There are two matching order rows and two matching payment rows. The result therefore contains four combinations.
Check key uniqueness before merging
print(orders["StudentID"].is_unique) print(payments["StudentID"].is_unique)
Expected output
False False
If you expect exactly one result per key, investigate duplicate records and choose an appropriate validation rule.
15. Merge Using Indexes
When the matching information is stored in the indexes rather than regular columns, you can use left_index and right_index.
student_names = pd.DataFrame({
"Student": ["Samadhan", "Pradnya", "Samiksha"]
}, index=[101, 102, 103])
student_scores = pd.DataFrame({
"Score": [85, 92, 88]
}, index=[101, 102, 103])
result = pd.merge(
student_names,
student_scores,
left_index=True,
right_index=True
)
print(result)
Expected output
Student Score 101 Samadhan 85 102 Pradnya 92 103 Samiksha 88
The index values act as the matching keys. If you need to combine DataFrames using indexes, this approach can be useful.
16. Practical Mini Project: Create a Student Report
Combine student details, exam scores, and attendance into one report. A left join ensures every student remains in the final dataset.
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 report combines three related datasets. Samiksha remains in the report, even though her score is missing, because the joins preserve all students from the original student DataFrame.
17. Common Mistakes and Best Practices
Mistake 1: Choosing the wrong join type
An inner join removes unmatched records, while a left join preserves all records from the left DataFrame. Decide which records must remain before choosing how.
Mistake 2: Ignoring duplicate keys
Duplicate keys can multiply output rows. Check uniqueness when you expect one record per entity.
print(students["StudentID"].is_unique) print(scores["StudentID"].is_unique)
Mistake 3: Using the wrong key column
If the key names differ, specify left_on and right_on. Do not assume Pandas will automatically match columns with different names.
Mistake 4: Ignoring unmatched records
Use indicator=True when you need to audit records that appear on only one side of a merge.
Mistake 5: Incompatible key data types
Ensure that key columns use compatible data types. For example, integer identifiers and string identifiers may not match as intended.
print(students["StudentID"].dtype) print(scores["StudentID"].dtype)
Inspect the types first, then convert them only when appropriate for the actual data.
18. Practice Exercises
Exercise 1: Inner merge
Merge the students and scores DataFrames using StudentID. Keep only matching records.
Hint: Use how="inner".
Exercise 2: Left merge
Keep all students and add scores where available.
Hint: Use how="left".
Exercise 3: Identify unmatched records
Perform an outer merge and identify records found 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 examination scores and teacher assignments using both Student and Subject.
Hint: Use a list in the on parameter.
Exercise 6: Validate uniqueness
Create duplicate StudentID values in one DataFrame and attempt a merge using validate="one_to_one". Explain why the validation fails.
Exercise 7: Build a complete report
Combine student details, scores, and attendance. Preserve all students, including those with missing scores.
Hint: Use two left merges.
19. 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 records after a merge?
- What does
validate="one_to_one"check? - Why can duplicate keys increase the number of merged rows?
- How can you merge DataFrames using their indexes?
- What is the difference between merging and concatenation?
Summary
The Pandas pd.merge() function 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. Use left_on and right_on for differently named keys, suffixes for overlapping column names, indicator to identify unmatched records, and validate to check key relationships.
Key concepts: pd.merge(), on, how, left_on, right_on, suffixes, indicator, validate, and index-based merging.