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

How DataFrame Merging Works Student Data StudentID | Name 101 | Samadhan 102 | Pradnya Score Data StudentID | Score 101 | 85 pd.merge() Match StudentID Merged Data StudentID | Name | Score 101 | Samadhan | 85 102 | Pradnya | NaN Matches depend on join type

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.

Remember: Use 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.

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: keep all left keys Right: keep all right keys

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.
Important: A many-to-many merge can multiply rows when keys repeat on both sides. Use validation when the intended relationship is known, especially in production data pipelines.

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

  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 rows 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 combine three related DataFrames into one report?
  10. 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.