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

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.

How Merging Works Students StudentID | Student 101 | Samadhan 102 | Pradnya Scores StudentID | Score 101 | 85 102 | 92 pd.merge() Match StudentID Merged Report StudentID | Student | Score 101 | Samadhan | 85 102 | Pradnya | 92 Rows match by key values The join type determines which unmatched records remain.

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.

Best practice: Specify the merge key explicitly with 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.

Join Type Comparison Left DataFrame 101, 102, 103, 104 Right DataFrame 101, 102, 104, 105 Inner: 101, 102, 104 Outer: 101, 102, 103, 104, 105 Left: retain all left-side keys Right: retain all right-side keys

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.

Tip: Pass multiple key names as a list: 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.
Important: If a key repeats in both DataFrames, the merge may create multiple combinations. Validation can help catch unexpected relationships before they affect reports or calculations.

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

  1. What is merging in Pandas?
  2. What is the default join type for pd.merge()?
  3. How does an inner join differ from an outer join?
  4. When should you use left_on and right_on?
  5. What does the suffixes parameter do?
  6. How can you identify unmatched records after a merge?
  7. What does validate="one_to_one" check?
  8. Why can duplicate keys increase the number of merged rows?
  9. How can you merge DataFrames using their indexes?
  10. 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.