Pandas Data Merge (merge / join)

Pandas provides powerful data merging capabilities that can join two or more DataFrames based on keys, just like SQL.mergeandjoinare the two most commonly used methods.

merge Basic Usage

pd.merge()The function is used to merge two DataFrames by columns, similar to the JOIN operation in SQL.

Simple Merge

Example

import pandas as pd

# Create two DataFrames
df1 = pd.DataFrame({
    "Student ID": ["S001", "S002", "S003"],
    "Name": ["Zhang San", "Li Si", "Wang Wu"]
})

df2 = pd.DataFrame({
    "Student ID": ["S001", "S002", "S003"],
    "Math": [85, 92, 78]
})

print("DataFrame 1：")
print(df1)
print()

print("DataFrame 2：")
print(df2)
print()

# Merge
result = pd.merge(df1, df2, on="Student ID")
print("Merge result:")
print(result)

Merging with Different Column Names

Example

import pandas as pd

df1 = pd.DataFrame({
    "Student ID": ["S001", "S002", "S003"],
    "Name": ["Zhang San", "Li Si", "Wang Wu"]
})

df2 = pd.DataFrame({
    "student_id": ["S001", "S002", "S003"],
    "Math": [85, 92, 78]
})

# Use left_on and right_on
result = pd.merge(df1, df2, left_on="Student ID", right_on="student_id")
print("Merge with different column names:")
print(result)
print()

# Drop redundant columns
result = result.drop("student_id", axis=1)
print("After dropping redundant columns:")
print(result)

Merge Types

inner / left / right / outer

Example

import pandas as pd

df1 = pd.DataFrame({
    "Student ID": ["S001", "S002", "S003", "S004"],
    "Name": ["Zhang San", "Li Si", "Wang Wu", "Zhao Liu"]
})

df2 = pd.DataFrame({
    "Student ID": ["S001", "S002", "S003", "S005"],
    "Math": [85, 92, 78, 88]
})

print("DataFrame 1:")
print(df1)
print("\nDataFrame 2:")
print(df2)
print()

# inner join (default): keep only what both have
print("inner join (intersection):")
print(pd.merge(df1, df2, on="Student ID", how="inner"))
print()

# left join: keep all rows from the left table
print("left join (keep left table):")
print(pd.merge(df1, df2, on="Student ID", how="left"))
print()

# right join: keep all rows from the right table
print("right join (keep right table):")
print(pd.merge(df1, df2, on="Student ID", how="right"))
print()

# outer join: keep all rows
print("outer join (union):")
print(pd.merge(df1, df2, on="Student ID", how="outer"))

It is important to understand the differences between the four JOIN types: inner keeps the intersection, left keeps all rows from the left table, right keeps all rows from the right table, and outer keeps all rows from both tables.

Multi-key Merge

Example

import pandas as pd

df1 = pd.DataFrame({
    "City": ["Beijing", "Beijing", "Shanghai", "Shanghai"],
    "Year": [2023, 2024, 2023, 2024],
    "Sales": [100, 120, 90, 110]
})

df2 = pd.DataFrame({
    "City": ["Beijing", "Beijing", "Shanghai", "Shanghai"],
    "Year": [2023, 2024, 2023, 2024],
    "Profit": [20, 25, 18, 22]
})

# Multi-key merge
result = pd.merge(df1, df2, on=["City", "Year"])
print("Multi-key merge:")
print(result)

join Method

DataFrame.join()It is another merging method that defaults to left join and merges by index.

Merge by Index

Example

import pandas as pd

# Create data, set the index
df1 = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu"],
    "Age": [25, 30, 28]
}, index=["S001", "S002", "S003"])

df2 = pd.DataFrame({
    "Math": [85, 92, 78],
    "English": [90, 88, 95]
}, index=["S001", "S002", "S003"])

print("DataFrame 1：")
print(df1)
print()

print("DataFrame 2：")
print(df2)
print()

# Use join to merge
result = df1.join(df2)
print("join merge:")
print(result)

Multi-table Merge

Example

import pandas as pd

df1 = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu"]
}, index=["S001", "S002", "S003"])

df2 = pd.DataFrame({
    "Math": [85, 92, 78]
}, index=["S001", "S002", "S004"])

df3 = pd.DataFrame({
    "English": [90, 88, 95]
}, index=["S001", "S003", "S004"])

# Chained join
result = df1.join(df2, how="left").join(df3, how="left")
print("Multi-table join (left join):")
print(result)
print()

# Use outer
result2 = df1.join(df2, how="outer").join(df3, how="outer")
print("Multi-table join (full outer join):")
print(result2)

Practice: Multi-table Association

Example

import pandas as pd

# Simulate business data
# 1. Employee table
employees = pd.DataFrame({
    "Employee ID": ["E001", "E002", "E003", "E004"],
    "Name": ["Zhang San", "Li Si", "Wang Wu", "Zhao Liu"],
    "Department ID": ["D001", "D001", "D002", "D003"]
})

# 2. Department table
departments = pd.DataFrame({
    "Department ID": ["D001", "D002", "D003"],
    "Department Name": ["Technology Department", "Sales Department", "Operations Department"]
})

# 3. Salary table
salaries = pd.DataFrame({
    "Employee ID": ["E001", "E002", "E003", "E004"],
    "Salary": [12000, 15000, 11000, 18000]
})

print(Step 1: Merge employees and departments)
result = pd.merge(employees, departments, on="Department ID")
print(result)
print()

print(Step 2: Then merge salaries)
result = pd.merge(result, salaries, on="Employee ID")
print(result)

merge vs join Selection

Comparison merge join
Merge basis Column Index
Default join inner left
Applicable scenarios Merge by business key Merge by primary key/index

In most cases, merge is more flexible, while join is more concise when merging by index. Choose the appropriate method based on the actual data structure.

Other Extensions