Summary  
This chapter covers how to use merge() to join two tabular datasets with inner, outer, left, and right merge types, controlling which rows are included based on key matches.  

General domain of usage  
Data analysis

When you combine two DataFrames in pandas, you often need to decide how to handle rows that do not have matching keys in both DataFrames. The `merge()` function provides several options, known as merge types: **inner**, **outer**, **left**, and **right**. Each merge type determines which rows appear in the result, depending on whether their keys are present in one or both DataFrames.

- **Inner merge**: includes only the rows with keys present in both DataFrames; use this when you want only matched records;
- **Outer merge**: includes all rows from both DataFrames, filling in missing values with `NaN` where there is no match; use this when you want a complete set of keys;
- **Left merge**: includes all rows from the left DataFrame and matched rows from the right; unmatched rows from the right are filled with `NaN`;
- **Right merge**: includes all rows from the right DataFrame and matched rows from the left; unmatched rows from the left are filled with `NaN`.

Choosing the right merge type depends on your analysis needs: whether you want only overlapping data, all data, or to prioritize one DataFrame over the other.

import pandas as pd

# Create two example DataFrames with overlapping and non-overlapping keys
df1 = pd.DataFrame({
    "ID": [1, 2, 3, 4],
    "Name": ["Alice", "Bob", "Charlie", "David"]
})

df2 = pd.DataFrame({
    "ID": [3, 4, 5, 6],
    "Score": [85, 92, 78, 88]
})

# Inner merge: only rows with IDs present in both DataFrames
inner_merged = pd.merge(df1, df2, on="ID", how="inner")
print("Inner merge result:")
print(inner_merged)

# Left merge: all rows from df1, matched rows from df2
left_merged = pd.merge(df1, df2, on="ID", how="left")
print("\nLeft merge result:")
print(left_merged)

# Outer merge: all rows from both DataFrames, missing values filled with NaN
outer_merged = pd.merge(df1, df2, on="ID", how="outer")
print("Outer merge result:")
print(outer_merged)

The following table summarizes the key differences between the main merge types and their effect on the resulting DataFrame:

Use this table to quickly determine which merge type best fits your data joining requirements.

Which merge type includes all rows from both DataFrames and fills missing values with NaN where there is no match?

Master advanced data manipulation techniques in pandas, focusing on combining and reshaping DataFrames for powerful data analysis workflows.

Explore pandas techniques for combining multiple DataFrames, including merging, joining, and concatenation, to create unified datasets for analysis.

Master reshaping techniques in pandas to transform DataFrames for deeper analysis and visualization.

merge() types