Joining DataFrames in Pandas
Learn how to combine DataFrames using indexes, understand left, right, inner, and outer joins, and use join() to build meaningful reports.
Learning objectives: Understand DataFrame.join(), index alignment, join types, joining multiple DataFrames, suffixes, and common errors.
1. What Is Joining in Pandas?
Joining combines related DataFrames, usually by matching their index labels. It is especially useful when datasets already use a common identifier as their index.
Real-world example
A school stores student names in one DataFrame and examination scores in another. Both DataFrames use StudentID as their index. You can join them to create a report containing student names and scores.
DataFrame.join() is designed for combining columns using indexes or a specified key column.
2. Create Sample DataFrames
We will create student and score DataFrames using StudentID as the index.
import pandas as pd
students = pd.DataFrame({
"Student": ["Samadhan", "Pradnya", "Samiksha", "Aarav"],
"Class": ["A", "A", "B", "B"]
}, index=[101, 102, 103, 104])
scores = pd.DataFrame({
"Score": [85, 92, 76, 88]
}, index=[101, 102, 104, 105])
students.index.name = "StudentID"
scores.index.name = "StudentID"
print("Students:")
print(students)
print("\nScores:")
print(scores)
Expected output
Students:
Student Class
StudentID
101 Samadhan A
102 Pradnya A
103 Samiksha B
104 Aarav B
Scores:
Score
StudentID
101 85
102 92
104 76
105 88
StudentID 103 appears only in the students DataFrame, while StudentID 105 appears only in the scores DataFrame.
3. Basic Joining with join()
Use DataFrame.join() to combine columns from another DataFrame. By default, it performs a left join, retaining the index labels of the calling DataFrame.
result = students.join(scores) print(result)
Expected output
Student Class Score StudentID 101 Samadhan A 85.0 102 Pradnya A 92.0 103 Samiksha B NaN 104 Aarav B 76.0
All students are retained. StudentID 103 has a missing score because no matching index exists in the scores DataFrame.
students.join(scores) defaults to a left join. It keeps the index labels from students.
4. Left Join
A left join retains every index label from the left DataFrame and adds matching columns from the right DataFrame.
left_result = students.join(
scores,
how="left"
)
print(left_result)
Expected output
Student Class Score StudentID 101 Samadhan A 85.0 102 Pradnya A 92.0 103 Samiksha B NaN 104 Aarav B 76.0
The result keeps all four students, even though one student has no score.
5. Inner Join
An inner join keeps only index labels that appear in both DataFrames.
inner_result = students.join(
scores,
how="inner"
)
print(inner_result)
Expected output
Student Class Score StudentID 101 Samadhan A 85 102 Pradnya A 92 104 Aarav B 76
StudentID 103 and 105 are excluded because they are not shared by both indexes.
6. Right Join
A right join retains every index label from the right DataFrame and adds matching columns from the left DataFrame.
right_result = students.join(
scores,
how="right"
)
print(right_result)
Expected output
Student Class Score StudentID 101 Samadhan A 85 102 Pradnya A 92 104 Aarav B 76 105 NaN NaN 88
Every score record remains in the result. StudentID 105 has no matching student details.
7. Outer Join
An outer join retains every index label found in either DataFrame. Missing values appear wherever one DataFrame does not have a corresponding record.
outer_result = students.join(
scores,
how="outer"
)
print(outer_result)
Expected output
Student Class Score StudentID 101 Samadhan A 85.0 102 Pradnya A 92.0 103 Samiksha B NaN 104 Aarav B 76.0 105 NaN NaN 88.0
The output includes all five index labels, including 103 and 105, which are missing from one of the DataFrames.
Join types determine how unmatched index labels are handled.
8. Compare the Join Types
| Join type | Index labels retained | Typical use |
|---|---|---|
left |
All labels from the left DataFrame. | Keep every student. |
inner |
Only labels shared by both DataFrames. | Keep students with matching scores. |
right |
All labels from the right DataFrame. | Keep every score record. |
outer |
Labels from both DataFrames. | Audit all available records. |
9. Join Multiple DataFrames
You can join several DataFrames in one call by passing a list of DataFrames. This is useful when different datasets share the same index.
students = pd.DataFrame({
"Student": ["Samadhan", "Pradnya", "Samiksha"],
"Class": ["A", "A", "B"]
}, index=[101, 102, 103])
scores = pd.DataFrame({
"Score": [85, 92, 88]
}, index=[101, 102, 103])
attendance = pd.DataFrame({
"Attendance": [95, 98, 88]
}, index=[101, 102, 103])
report = students.join(
[scores, attendance]
)
print(report)
Expected output
Student Class Score Attendance 101 Samadhan A 85 95 102 Pradnya A 92 98 103 Samiksha B 88 88
All three DataFrames are combined into one report because they use matching index labels.
10. Handle Overlapping Column Names with suffixes
If both DataFrames contain a column with the same name, use the lsuffix and rsuffix parameters to distinguish them.
left_df = pd.DataFrame({
"Student": ["Samadhan", "Pradnya"],
"Score": [85, 92]
}, index=[101, 102])
right_df = pd.DataFrame({
"Student": ["Samadhan P.", "Pradnya S."],
"Attendance": [95, 98]
}, index=[101, 102])
result = left_df.join(
right_df,
lsuffix="_left",
rsuffix="_right"
)
print(result)
Expected output
Student_left Score Student_right Attendance 101 Samadhan 85 Samadhan P. 95 102 Pradnya 92 Pradnya S. 98
The suffixes identify which DataFrame each overlapping column came from.
11. Join Using a Key Column Instead of an Index
By default, join() uses the caller's index. You can also use a column from the left DataFrame to match the index of the right DataFrame with on.
students = pd.DataFrame({
"StudentID": [101, 102, 103],
"Student": ["Samadhan", "Pradnya", "Samiksha"]
})
scores = pd.DataFrame({
"Score": [85, 92, 88]
}, index=[101, 102, 103])
result = students.join(
scores,
on="StudentID"
)
print(result)
Expected output
StudentID Student Score 0 101 Samadhan 85 1 102 Pradnya 92 2 103 Samiksha 88
Here, the StudentID column in the left DataFrame is matched against the index of the right DataFrame.
12. Understand Index Alignment
When joining by index, Pandas matches index labels rather than simply pairing rows by their displayed positions.
names = pd.DataFrame({
"Student": ["Samadhan", "Pradnya"]
}, index=[101, 102])
scores = pd.DataFrame({
"Score": [92, 85]
}, index=[102, 101])
result = names.join(scores)
print(result)
Expected output
Student Score 101 Samadhan 85 102 Pradnya 92
Samadhan receives score 85 because the matching index is 101. Pradnya receives score 92 because the matching index is 102. Pandas does not match rows solely by their physical position.
13. Reset an Index Before Joining
If an identifier is stored in the index but you need it as a regular column, use reset_index().
students = pd.DataFrame({
"Student": ["Samadhan", "Pradnya"]
}, index=[101, 102])
students.index.name = "StudentID"
result = students.reset_index()
print(result)
Expected output
StudentID Student 0 101 Samadhan 1 102 Pradnya
Resetting the index is useful when you need to use the identifier as a regular column for filtering, exporting, or merging with another dataset.
14. Practical Mini Project: Student Performance Report
Build a complete report containing student names, classes, scores, and attendance. Use a left join so every student remains in the output.
import pandas as pd
students = pd.DataFrame({
"Student": ["Samadhan", "Pradnya", "Samiksha", "Aarav"],
"Class": ["A", "A", "B", "B"]
}, index=[101, 102, 103, 104])
scores = pd.DataFrame({
"Score": [85, 92, 76]
}, index=[101, 102, 104])
attendance = pd.DataFrame({
"Attendance": [95, 98, 88, 90]
}, index=[101, 102, 103, 104])
students.index.name = "StudentID"
scores.index.name = "StudentID"
attendance.index.name = "StudentID"
report = students.join(
[scores, attendance],
how="left"
)
print(report)
Expected output
Student Class Score Attendance StudentID 101 Samadhan A 85.0 95 102 Pradnya A 92.0 98 103 Samiksha B NaN 88 104 Aarav B 76.0 90
The final report contains every student. Samiksha's score is missing, but her class and attendance remain available.
15. Common Mistakes and Best Practices
Mistake 1: Assuming join() matches by any common column
By default, join() uses indexes. If you need to match columns by name, consider merge() or specify on when joining a left column to the right index.
Mistake 2: Using unrelated indexes
Make sure index labels identify the same entities in both DataFrames. Matching unrelated indexes can produce incorrect results.
Mistake 3: Forgetting overlapping columns
Use lsuffix and rsuffix when both DataFrames contain columns with the same name.
Mistake 4: Losing unmatched records
Use how="left" when every row from the calling DataFrame must remain in the result.
Mistake 5: Ignoring missing values
Inspect missing values after joining, especially when an outer or left join is used.
print(report.isna().sum())
16. Practice Exercises
Exercise 1: Basic join
Create two DataFrames with StudentID as the index and combine student names with scores.
Hint: Use students.join(scores).
Exercise 2: Inner join
Join student details and scores, keeping only index labels shared by both DataFrames.
Hint: Use how="inner".
Exercise 3: Outer join
Keep every index label from both datasets and identify the resulting missing values.
Hint: Use how="outer".
Exercise 4: Multiple DataFrames
Join student details, scores, and attendance in one operation.
Hint: Pass a list of DataFrames to join().
Exercise 5: Join with a column
Keep StudentID as a regular column in the left DataFrame and match it to the right DataFrame's index.
Hint: Use on="StudentID".
Exercise 6: Overlapping columns
Join two DataFrames that both contain a Student column and distinguish their values.
Hint: Use lsuffix and rsuffix.
17. Revision Questions
- What is joining in Pandas?
- What does
DataFrame.join()use by default to match rows? - What is the default join type for
join()? - How does a left join differ from an inner join?
- What does an outer join preserve?
- How can you join several DataFrames in one operation?
- What are
lsuffixandrsuffixused for? - How can a regular column be matched to another DataFrame's index?
- Why is index alignment important?
- What is the difference between
join(),merge(), andconcat()?
Summary
The Pandas DataFrame.join() method is commonly used to combine DataFrames by matching indexes. It supports left, inner, right, and outer joins, can combine multiple DataFrames, and provides suffixes for overlapping columns. You can also use a left-side key column to match the right DataFrame's index with the on parameter.
Key concepts: join(), index alignment, how, on, lsuffix, rsuffix, and reset_index().