Master Pandas From Scratch

Learn Pandas fundamentals, Series, DataFrames, data selection, filtering, cleaning, sorting, grouping, merging, reshaping, string operations, time series, data analysis, performance optimization, visualization, and practical projects through structured lessons.

COURSE SYLLABUS
1. Pandas Introduction and Environment Setup
Introduction to Pandas Installation and Environment Setup
2. Pandas Core Data Structures
Data Structures Overview Pandas Series DataFrames Indexes, Rows and Columns Shape, Size and Dimensions
3. Pandas Data Input and Output
Data Input and Output Overview Reading CSV, Excel and JSON Files Writing and Exporting Files
4. Initial Data Inspection
Data Inspection Overview Head and Tail Data Info and Types Descriptive Statistics
5. Selection and Indexing
Selection and Indexing Overview Column Selection loc and iloc Index Management
6. Filtering and Conditional Selection
Filtering Overview Boolean Filtering Multiple Conditions isin, between and str.contains
7. Basic Data Cleaning
Data Cleaning Overview Missing Values Duplicate Data Rename and Replace Data Type Conversion
8. Vectorized Operations and Sorting
Operations and Sorting Overview Column Operations Sorting Values and Indexes Sum, Mean, Max and Min
9. GroupBy and Aggregation
GroupBy Overview Grouping Data Aggregation Functions Group Filtering Group Transform
10. Merging, Joining and Concatenating
Merge, Join and Concat Overview Concatenation Merging DataFrames Joining by Index
11. Reshaping and Pivoting
Reshaping Overview Pivot Tables Melting Data Stack and Unstack Cross Tabulation
12. String and Categorical Data
String and Categorical Overview String Operations Categorical Data One-Hot Encoding
13. Time Series Analysis
Time Series Overview DateTime Operations Time-Based Indexes Resampling Data Shift and Rolling Windows
14. Custom Functions and Mapping
Apply and Mapping Overview Apply and Lambda Functions Map Operations Element-Wise Operations
15. Performance and Large Datasets
Performance Overview Memory Optimization Processing Files in Chunks PyArrow, Dask and Polars
16. Pandas Data Visualization
Visualization Overview Basic Plotting Histograms, Lines, Scatter and Box Plots Matplotlib and Seaborn

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.

Joining by Index Student Data Index | Student 101 | Samadhan 102 | Pradnya Score Data Index | Score 101 | 85 102 | 92 join() Match indexes Joined Report Index | Student | Score 101 | Samadhan | 85 102 | Pradnya | 92 Columns aligned by index Join type determines which index labels are retained.

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.

Remember: 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 by Index Students index 101, 102, 103, 104 Scores index 101, 102, 104, 105 Inner: 101, 102, 104 Outer: 101, 102, 103, 104, 105 Left: keep student indexes Right: keep score indexes Choose how based on which records must remain.

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.

Important: If your indexes do not represent the same entities, joining can associate incorrect records. Verify that the index labels have the intended meaning before joining.

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

  1. What is joining in Pandas?
  2. What does DataFrame.join() use by default to match rows?
  3. What is the default join type for join()?
  4. How does a left join differ from an inner join?
  5. What does an outer join preserve?
  6. How can you join several DataFrames in one operation?
  7. What are lsuffix and rsuffix used for?
  8. How can a regular column be matched to another DataFrame's index?
  9. Why is index alignment important?
  10. What is the difference between join(), merge(), and concat()?

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().