This page includes an interactive code editor. Try modifying and running the examples!

Pandas Data Merging and Joining

Data merging is a fundamental operation in data analysis that allows you to combine data from multiple sources based on common keys or indices. Pandas provides powerful methods for combining datasets in various ways.

1. Types of Joins in Pandas

Pandas supports all standard SQL join types:

Join Type	Description	Venn Diagram	Use Case
Inner Join	Returns only matching rows from both DataFrames	∩	Find common records
Left Join	Returns all rows from left DataFrame and matched rows from right	◐	Keep all primary records
Right Join	Returns all rows from right DataFrame and matched rows from left	◑	Keep all reference records
Outer Join	Returns all rows from both DataFrames	∪	Combine all possible records

2. Key Merging Functions

pd.merge() - Primary Function

Purpose: Database-style joins
Syntax: pd.merge(left, right, on='key', how='inner')
Best for: Combining DataFrames on columns
Flexibility: High - supports all join types

DataFrame.join() - Convenience Method

Purpose: Join on indexes
Syntax: left.join(right, on='key')
Best for: Index-based joins
Flexibility: Medium - simpler syntax

Basic Merge Operations

Basic Merge Examples

import pandas as pd

# Create sample DataFrames for merging
employees_data = {
    'EmployeeID': [1, 2, 3, 4, 5],
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
    'DepartmentID': [101, 102, 101, 103, 102],
    'Salary': [70000, 60000, 80000, 55000, 65000]
}

departments_data = {
    'DepartmentID': [101, 102, 103, 104],
    'DepartmentName': ['IT', 'HR', 'Finance', 'Marketing'],
    'Manager': ['John', 'Sarah', 'Mike', 'Lisa']
}

employees_df = pd.DataFrame(employees_data)
departments_df = pd.DataFrame(departments_data)

print("Employees DataFrame:")
print(employees_df)
print("\nDepartments DataFrame:")
print(departments_df)

# 1. Inner Join - Most common type
print("\n1. Inner Join (default):")
inner_merge = pd.merge(employees_df, departments_df, on='DepartmentID', how='inner')
print(inner_merge)

# 2. Left Join - Keep all employees
print("\n2. Left Join:")
left_merge = pd.merge(employees_df, departments_df, on='DepartmentID', how='left')
print(left_merge)

# 3. Right Join - Keep all departments
print("\n3. Right Join:")
right_merge = pd.merge(employees_df, departments_df, on='DepartmentID', how='right')
print(right_merge)

# 4. Outer Join - Keep all records
print("\n4. Outer Join (Full Outer Join):")
outer_merge = pd.merge(employees_df, departments_df, on='DepartmentID', how='outer')
print(outer_merge)

# 5. Merge with different column names
print("\n5. Merge with different column names:")
# Rename column for demonstration
departments_renamed = departments_df.rename(columns={'DepartmentID': 'DeptID'})
merge_different_cols = pd.merge(employees_df, departments_renamed, 
                               left_on='DepartmentID', right_on='DeptID')
print(merge_different_cols)

3. Advanced Merging Techniques

Technique	Description	Syntax Example
Multiple Keys	Merge on multiple columns	`on=['key1', 'key2']`
Different Column Names	Merge when key columns have different names	`left_on='col1', right_on='col2'`
Indicator Flag	Track source of each row	`indicator=True`
Suffixes	Handle overlapping column names	`suffixes=('_left', '_right')`
Validate	Check merge integrity	`validate='one_to_one'`

Advanced Merge Scenarios

Advanced Merge Techniques

import pandas as pd

# Advanced merging scenarios
customers_data = {
    'CustomerID': [1, 2, 3, 4, 5],
    'Name': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve'],
    'City': ['New York', 'London', 'Paris', 'Tokyo', 'Sydney']
}

orders_data = {
    'OrderID': [101, 102, 103, 104, 105, 106, 107],
    'CustomerID': [1, 2, 1, 3, 6, 2, 1],  # Note: CustomerID 6 doesn't exist in customers
    'Product': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Tablet', 'Phone', 'Headphones'],
    'Amount': [1200, 25, 75, 300, 500, 800, 150]
}

products_data = {
    'Product': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Phone', 'Headphones'],
    'Category': ['Electronics', 'Accessories', 'Accessories', 'Electronics', 'Electronics', 'Audio'],
    'Supplier': ['TechCorp', 'AccessoriesInc', 'AccessoriesInc', 'TechCorp', 'MobileCo', 'AudioTech']
}

customers_df = pd.DataFrame(customers_data)
orders_df = pd.DataFrame(orders_data)
products_df = pd.DataFrame(products_data)

print("Customers DataFrame:")
print(customers_df)
print("\nOrders DataFrame:")
print(orders_df)
print("\nProducts DataFrame:")
print(products_df)

# 1. Multiple merges - Chain operations
print("\n1. Multiple Table Join:")
# First merge: customers with orders
customer_orders = pd.merge(customers_df, orders_df, on='CustomerID', how='left')
# Second merge: result with products
complete_data = pd.merge(customer_orders, products_df, on='Product', how='left')
print(complete_data)

# 2. Merge with indicator - Track source of rows
print("\n2. Merge with Indicator:")
merge_indicator = pd.merge(customers_df, orders_df, on='CustomerID', 
                          how='outer', indicator=True)
print(merge_indicator)

# 3. Merge on multiple columns
print("\n3. Merge on Multiple Columns:")
# Create sample data with multiple key columns
sales_q1 = {
    'Region': ['North', 'South', 'East', 'West'],
    'Product': ['A', 'B', 'A', 'C'],
    'Q1_Sales': [1000, 1500, 1200, 800]
}

sales_q2 = {
    'Region': ['North', 'South', 'East', 'West'],
    'Product': ['A', 'B', 'A', 'C'],
    'Q2_Sales': [1100, 1600, 1300, 900]
}

df_q1 = pd.DataFrame(sales_q1)
df_q2 = pd.DataFrame(sales_q2)

multi_key_merge = pd.merge(df_q1, df_q2, on=['Region', 'Product'], how='inner')
print(multi_key_merge)

# 4. Handling duplicate column names with suffixes
print("\n4. Merge with Suffixes:")
df1 = pd.DataFrame({'key': [1, 2, 3], 'value': [10, 20, 30]})
df2 = pd.DataFrame({'key': [1, 2, 3], 'value': [100, 200, 300]})

merge_suffixes = pd.merge(df1, df2, on='key', suffixes=('_left', '_right'))
print(merge_suffixes)

4. Concatenation vs Merging

pd.concat() - Stacking Data

Purpose: Stack DataFrames vertically or horizontally
Vertical: axis=0 (add rows)
Horizontal: axis=1 (add columns)
Best for: Same-structure data combination

When to Use Each

Use merge: Combining related data with keys
Use concat: Stacking similar datasets
Use join: Index-based combinations
Use append: Adding rows (deprecated)

Concatenation and Join Methods

Concatenation Examples

import pandas as pd
import numpy as np

# Concatenation and Joining examples
print("=== CONCATENATION EXAMPLES ===\n")

# 1. Vertical Concatenation (Stacking rows)
df1 = pd.DataFrame({
    'A': ['A0', 'A1', 'A2'],
    'B': ['B0', 'B1', 'B2'],
    'C': ['C0', 'C1', 'C2']
})

df2 = pd.DataFrame({
    'A': ['A3', 'A4', 'A5'],
    'B': ['B3', 'B4', 'B5'],
    'C': ['C3', 'C4', 'C5']
})

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

print("\n1. Vertical Concatenation:")
vertical_concat = pd.concat([df1, df2], ignore_index=True)
print(vertical_concat)

# 2. Horizontal Concatenation (Stacking columns)
df3 = pd.DataFrame({
    'D': ['D0', 'D1', 'D2'],
    'E': ['E0', 'E1', 'E2']
})

print("\n2. Horizontal Concatenation:")
horizontal_concat = pd.concat([df1, df3], axis=1)
print(horizontal_concat)

# 3. Concatenation with different indexes
print("\n3. Concatenation with Different Indexes:")
df4 = pd.DataFrame({
    'B': ['B3', 'B4', 'B5'],
    'C': ['C3', 'C4', 'C5'],
    'D': ['D3', 'D4', 'D5']
}, index=[3, 4, 5])

concat_different = pd.concat([df1, df4])
print(concat_different)

print("\n=== JOIN METHOD EXAMPLES ===\n")

# 4. DataFrame.join() method
left_df = pd.DataFrame({
    'Key': ['K0', 'K1', 'K2', 'K3'],
    'A': ['A0', 'A1', 'A2', 'A3'],
    'B': ['B0', 'B1', 'B2', 'B3']
})

right_df = pd.DataFrame({
    'C': ['C0', 'C1', 'C2'],
    'D': ['D0', 'D1', 'D2']
}, index=['K0', 'K1', 'K2'])

print("Left DataFrame:")
print(left_df)
print("\nRight DataFrame:")
print(right_df)

print("\n4. Join on Index:")
join_result = left_df.join(right_df, on='Key')
print(join_result)

# 5. Compare merge vs join
print("\n5. Merge vs Join Comparison:")
# Using merge
merge_result = pd.merge(left_df, right_df, left_on='Key', right_index=True)
print("Merge result:")
print(merge_result)

# Using join
join_result = left_df.set_index('Key').join(right_df)
print("\nJoin result:")
print(join_result)

Real-World E-commerce Data Integration

Real-World Multi-Table Join

import pandas as pd

# Real-world example: E-commerce data integration
print("=== REAL-WORLD E-COMMERCE DATA INTEGRATION ===\n")

# Customer master data
customers = pd.DataFrame({
    'customer_id': [1001, 1002, 1003, 1004, 1005],
    'customer_name': ['John Doe', 'Jane Smith', 'Bob Johnson', 'Alice Brown', 'Charlie Wilson'],
    'join_date': ['2023-01-15', '2023-02-20', '2023-01-10', '2023-03-05', '2023-02-28'],
    'customer_tier': ['Gold', 'Silver', 'Bronze', 'Gold', 'Silver']
})

# Orders data
orders = pd.DataFrame({
    'order_id': [5001, 5002, 5003, 5004, 5005, 5006, 5007],
    'customer_id': [1001, 1002, 1001, 1003, 1004, 1002, 1001],
    'order_date': ['2024-01-15', '2024-01-20', '2024-02-05', '2024-02-10', '2024-02-15', '2024-03-01', '2024-03-10'],
    'order_amount': [150.00, 75.50, 200.00, 50.25, 300.75, 125.00, 80.50]
})

# Product catalog
products = pd.DataFrame({
    'product_id': [201, 202, 203, 204, 205],
    'product_name': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Headphones'],
    'category': ['Electronics', 'Accessories', 'Accessories', 'Electronics', 'Audio'],
    'price': [1000.00, 25.50, 75.00, 300.00, 150.00]
})

# Order items (bridge table)
order_items = pd.DataFrame({
    'order_id': [5001, 5001, 5002, 5003, 5004, 5005, 5006, 5007],
    'product_id': [201, 202, 202, 203, 204, 201, 205, 202],
    'quantity': [1, 2, 1, 1, 1, 1, 1, 3]
})

print("1. Customer Master Data:")
print(customers)
print("\n2. Orders Data:")
print(orders)
print("\n3. Product Catalog:")
print(products)
print("\n4. Order Items:")
print(order_items)

# Complex multi-table join
print("\n=== COMPLETE DATA INTEGRATION ===\n")

# Step 1: Join orders with customers
orders_with_customers = pd.merge(orders, customers, on='customer_id', how='left')

# Step 2: Join with order items
orders_with_items = pd.merge(orders_with_customers, order_items, on='order_id', how='left')

# Step 3: Join with products
complete_sales_data = pd.merge(orders_with_items, products, on='product_id', how='left')

# Calculate extended price
complete_sales_data['extended_price'] = complete_sales_data['quantity'] * complete_sales_data['price']

print("Complete Sales Data:")
print(complete_sales_data)

# Customer spending analysis
print("\n=== CUSTOMER SPENDING ANALYSIS ===\n")
customer_spending = complete_sales_data.groupby(['customer_id', 'customer_name', 'customer_tier']).agg({
    'order_id': 'nunique',
    'extended_price': 'sum'
}).rename(columns={'order_id': 'total_orders', 'extended_price': 'total_spent'}).round(2)

print(customer_spending)

# Product performance analysis
print("\n=== PRODUCT PERFORMANCE ANALYSIS ===\n")
product_performance = complete_sales_data.groupby(['product_id', 'product_name', 'category']).agg({
    'quantity': 'sum',
    'extended_price': 'sum'
}).rename(columns={'quantity': 'total_units_sold', 'extended_price': 'total_revenue'}).round(2)

print(product_performance)

Merge Best Practices

Always specify the how parameter explicitly
Use on parameter for clarity
Handle duplicate column names with suffixes
Validate merge assumptions
Check for missing keys after merge

Performance Tips

Use indexes for faster merges
Filter data before merging when possible
Consider data types for merge keys
Use validate to catch errors early
Monitor memory usage with large merges

Common Pitfalls:

Unintended many-to-many relationships
Missing key values causing data loss
Column name conflicts without suffixes
Incorrect join type selection

Pro Tip: Use merge(indicator=True) to understand where each row came from, especially with outer joins. This helps debug complex merge operations.

← GroupBy Operations Sorting Data →

Frontend

JavaScript & Frameworks

Backend

Artificial Intelligence

Database

CSS Frameworks

Data Analytics

Digital Marketing

Frontend

Backend

Artificial Intelligence

DevOps

Database

Cyber Security

Pandas Tutorial

Pandas Data Merging and Joining

1. Types of Joins in Pandas

2. Key Merging Functions

pd.merge() - Primary Function

DataFrame.join() - Convenience Method

Basic Merge Operations

Basic Merge Examples

3. Advanced Merging Techniques

Advanced Merge Scenarios

Advanced Merge Techniques

4. Concatenation vs Merging

pd.concat() - Stacking Data

When to Use Each

Concatenation and Join Methods

Concatenation Examples

Real-World E-commerce Data Integration

Real-World Multi-Table Join

Merge Best Practices

Performance Tips

Follow Us

Our Tools

Our Company

Special Tools