Loading content...
Loading content...
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().
In production databases, information is deliberately separated into dedicated tables to eliminate duplication and preserve integrity:
CustomerID | Name 101 | Amit 102 | Priya 103 | Rahul
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.
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.pd.merge()Connect two tables by passing the left DataFrame, right DataFrame, and matching column name:
Merge the customers and orders DataFrames using CustomerID:
In real datasets, keys rarely match perfectly. Customers may have no orders, or orders might reference guest checkouts:
Keeps only matching keys present in both tables. Any unmatched customer or order is dropped.
Keeps ALL rows from the left table. Unmatched right-side order values become NaN.
Keeps ALL rows from the right table. Unmatched left-side customer names become NaN.
Keeps ALL records from both tables. Missing values on either side are filled with NaN.
Execute all four join types on Customers (101, 102, 103, 104) and Orders (102, 103, 105):
Real production databases rarely have identical column names:
When the left table calls the key CustomerID and the right table calls it ClientID:
If both tables share a non-key column like Date, customize the auto-generated suffixes:
merge() vs. DataFrame.join()When should you use pd.merge() versus df.join()?
Database-style relational joining on any column or key. Defaults to how="inner". The workhorse tool for data analytics.
Convenience method specifically for joining DataFrames on their row Index. Defaults to how="left".
Perform relational analytics on customers in Mumbai, Delhi, and Pune:
Test your understanding of relational joins, key matching, and NaN behaviors:
Test your understanding with real-world query prediction and syntax questions.
What is the default join behavior in pd.merge() when the how parameter is omitted?