Merging DataFrames

🔗 Merging DataFrames in Pandas

The Story: Bringing Friends Together at a Party

Imagine you’re throwing a birthday party. You have two guest lists:

• List A: Names and phone numbers
• List B: Names and favorite cake flavors

To plan the perfect party, you need to combine these lists. That’s exactly what merging does in Pandas!

🎯 What is Merging?

Merging is like connecting puzzle pieces. You take two tables and join them based on something they have in common.

import pandas as pd

# List A: Guest contacts
contacts = pd.DataFrame({
    'name': ['Emma', 'Liam', 'Olivia'],
    'phone': ['111', '222', '333']
})

# List B: Cake preferences
cakes = pd.DataFrame({
    'name': ['Emma', 'Liam', 'Noah'],
    'cake': ['Chocolate', 'Vanilla', 'Strawberry']
})

contacts:

cakes:

1️⃣ Merge DataFrames Basics

The simplest merge finds matching values in both tables.

# Basic merge - finds common names
result = pd.merge(contacts, cakes)
print(result)

Result:

What happened?

• Emma and Liam are in both lists
• Olivia (only in contacts) is gone
• Noah (only in cakes) is gone

Think of it like: “Who’s on BOTH guest lists?”

2️⃣ Merge on Column

Sometimes your columns have different names but mean the same thing.

# Different column names
employees = pd.DataFrame({
    'emp_name': ['Sara', 'Mike'],
    'dept': ['Sales', 'Tech']
})

salaries = pd.DataFrame({
    'worker': ['Sara', 'Mike'],
    'pay': [50000, 60000]
})

# Tell Pandas which columns to match
result = pd.merge(
    employees,
    salaries,
    left_on='emp_name',  # From left table
    right_on='worker'    # From right table
)

Result:

Key Point: Use left_on and right_on when column names don’t match!

3️⃣ Merge on Index

What if the “key” is the row label (index) instead of a column?

# Tables with meaningful indexes
products = pd.DataFrame(
    {'price': [10, 20]},
    index=['apple', 'banana']
)

stock = pd.DataFrame(
    {'quantity': [100, 50]},
    index=['apple', 'banana']
)

# Merge using indexes
result = pd.merge(
    products,
    stock,
    left_index=True,   # Use left index
    right_index=True   # Use right index
)

Result:

Pro tip: You can mix! Use left_on='col' with right_index=True.

4️⃣ Merge Join Types

This is where merging gets powerful! There are 4 types:

graph TD
    A["Join Types"] --> B["inner"]
    A --> C["left"]
    A --> D["right"]
    A --> E["outer"]
    B --> B1["Only matches"]
    C --> C1["All from left"]
    D --> D1["All from right"]
    E --> E1["Everything"]

Let’s See Each Type:

left = pd.DataFrame({
    'key': ['A', 'B', 'C'],
    'value': [1, 2, 3]
})

right = pd.DataFrame({
    'key': ['B', 'C', 'D'],
    'score': [10, 20, 30]
})

INNER (Default) - “Only the Matches”

pd.merge(left, right, on='key', how='inner')

Only B and C are in both!

LEFT - “Keep Everyone from Left”

pd.merge(left, right, on='key', how='left')

A stays even with no match. Missing = NaN.

RIGHT - “Keep Everyone from Right”

pd.merge(left, right, on='key', how='right')

D stays even with no match.

OUTER - “Keep Absolutely Everyone”

pd.merge(left, right, on='key', how='outer')

Nobody left behind! Fill gaps with NaN.

5️⃣ Merge with Suffixes

Problem: What if both tables have the same column name?

jan = pd.DataFrame({
    'product': ['Book', 'Pen'],
    'sales': [100, 50]
})

feb = pd.DataFrame({
    'product': ['Book', 'Pen'],
    'sales': [120, 60]
})

# Without suffixes = confusion!
result = pd.merge(jan, feb, on='product')

Result (confusing):

Solution: Add meaningful suffixes!

result = pd.merge(
    jan,
    feb,
    on='product',
    suffixes=('_jan', '_feb')  # Custom labels!
)

Result (clear!):

Now you know exactly which sales came from which month!

🎉 Quick Summary

🚀 You Did It!

Merging is like being a matchmaker for data. You bring tables together based on what they have in common. Now you can:

• Combine any two DataFrames
• Handle different column names
• Use indexes as keys
• Choose exactly who stays (join types)
• Avoid confusion with suffixes

Go forth and merge with confidence! 🎊