Relational Data Combination Milestone 3: 03. Pandas ⭐ Foreign Key Lookups

Pandas — Merge & Join

Combine separate transactional and dimensional DataFrames like an expert data analyst. Master pd.merge(), understand the 4 relational join behaviors (INNER, LEFT, RIGHT, OUTER), connect tables with differing key column names, resolve column collisions with suffixes, and learn index-based join().

18 Structured Analytics Lessons

Real Interactive Python Engine

Verified against official pandas.pydata.org docs

Curriculum Overview

1. Why Merge Data? 2. Understanding the Key 3. Basic pd.merge() 4. 🔥 Live Basic Merge 5. The 4 Join Types 6. Inner Join 7. Left Join 8. Right Join 9. Outer Join 10. Join Comparison 11. 🔥 Live Join Types Lab 12. Different Key Names 13. Multiple Keys 14. Duplicate Column Suffixes 15. Merge vs. Join 16. Basic DataFrame.join() 17. Practical Analytics 18. 🔥 Final Live Challenge 19. Certification Quiz

Section 01

1. Why Do We Merge Data?

In production databases, information is deliberately separated into dedicated tables to eliminate duplication and preserve integrity:

CUSTOMERS Table

CustomerID | Name
101        | Amit
102        | Priya
103        | Rahul

ORDERS Table

CustomerID | Product | Amount
101        | Laptop  | 60000
102        | Phone   | 30000
103        | Tablet  | 20000

Customer names live in one table, and purchase orders live in another. To answer: "Which customer purchased the Laptop?", we must connect both tables via their common key: CustomerID.

Section 02

2. Understanding the Common Key

A common key is an identifier column present in both DataFrames that allows Pandas to determine which rows correspond to each other:

CustomerID: Links profile records to shopping transactions.
EmployeeID: Links employee profiles to department payroll data.
ProductID: Links warehouse inventory stock to sales registers.

Section 03

3. Basic Syntax: `pd.merge()`

Connect two tables by passing the left DataFrame, right DataFrame, and matching column name:

merged_df = pd.merge(customers, orders, on="CustomerID")

customers: Left DataFrame.
orders: Right DataFrame.
on="CustomerID": Common key column used to match rows.

Section 04 • Live Lab

4. 🔥 Live Basic Merge Lab

Merge the customers and orders DataFrames using CustomerID:

Python Code Editor: Basic Merge

Section 05 - 10

5-10. The 4 Fundamental Join Types

In real datasets, keys rarely match perfectly. Customers may have no orders, or orders might reference guest checkouts:

INNER JOIN

how="inner" (default)

Keeps only matching keys present in both tables. Any unmatched customer or order is dropped.

LEFT JOIN

how="left"

Keeps ALL rows from the left table. Unmatched right-side order values become NaN.

RIGHT JOIN

how="right"

Keeps ALL rows from the right table. Unmatched left-side customer names become NaN.

OUTER JOIN

how="outer"

Keeps ALL records from both tables. Missing values on either side are filled with NaN.

Section 11 • Live Lab

11. 🔥 Live Join Types Comparison Lab

Execute all four join types on Customers (101, 102, 103, 104) and Orders (102, 103, 105):

Python Code Editor: 4 Joins Comparison

Section 12 - 14

12-14. Disparate Key Names & Column Collisions

Real production databases rarely have identical column names:

1. Different Key Column Names

When the left table calls the key CustomerID and the right table calls it ClientID:

pd.merge(customers, orders, left_on="CustomerID", right_on="ClientID")

2. Resolving Overlapping Column Suffixes

If both tables share a non-key column like Date, customize the auto-generated suffixes:

pd.merge(customers, orders, on="CustomerID", suffixes=("_cust", "_order"))

Section 15 - 16

15-16. `merge()` vs. `DataFrame.join()`

When should you use pd.merge() versus df.join()?

pd.merge()

Database-style relational joining on any column or key. Defaults to how="inner". The workhorse tool for data analytics.

df.join()

Convenience method specifically for joining DataFrames on their row Index. Defaults to how="left".

Section 18 • Final Challenge

18. 🔥 Final Live Challenge: Customer & Order Intelligence

Perform relational analytics on customers in Mumbai, Delhi, and Pune:

Inner merge: Find customers with matching orders
Left merge: Complete customer profile view with orders (or NaNs)
Outer merge: Total system union showing unmatched customers and guest orders

Python Code Editor: Relational Challenge

Section 19 • Knowledge Check

19. Knowledge Assessment Quiz

Test your understanding of relational joins, key matching, and NaN behaviors:

TEST YOUR KNOWLEDGE