Merge & Inner Join
Python · Combining DataFrames (SQL Joins & Unions)
Theory
pd.merge(left, right, on='key') - or left.merge(right, on='key') - combines two DataFrames side by side by matching key values, exactly like a SQL JOIN. The default how='inner' keeps only rows whose key exists in both tables. If customer 1 has two orders, the customer row is repeated once per order; customers with no orders and orders with unknown customers are dropped.
When the key has different names, use left_on='cust_id', right_on='customer_id' (both columns are kept, so drop one afterwards). on=['cust_id', 'order_date'] joins on several columns. Columns that exist in both tables but are not keys get suffixes, _x and _y by default; suffixes=('_cust', '_ord') makes them readable. Key columns should have the same dtype on both sides - an int key will not match a text key like '1'.
Why we use it
Real data is spread across tables: customers in one, orders in another, products in a third. merge brings related facts together so you can answer questions such as 'revenue by customer city'.
When to use it
Use an inner merge when you only want rows that match on both sides, for example orders with a known customer. If you must keep unmatched rows from one side, use a left, right or outer join instead; to stack tables vertically, use concat.
How it works
- customers.merge(orders, on='cust_id')
- Take each key value in the left table
- Key found in the right table? Yes: One row per matching right row. No: Drop the row (inner).
- Add suffixes to overlapping non-key columns
- Joined DataFrame with columns from both tables
Syntax
pd.merge(left, right, on='key', how='inner')
left.merge(right, left_on='a', right_on='b', suffixes=('_l', '_r'))
left.merge(right, on=['k1', 'k2'])Example
result = orders.merge(customers, on='cust_id', how='inner')
print(result)Key points
- how='inner' is the default: unmatched rows on either side are dropped
- A key that repeats on one side repeats the rows from the other side
- left_on / right_on join columns with different names
- suffixes=('_l', '_r') names overlapping non-key columns clearly
- SQL equivalent: SELECT * FROM customers c JOIN orders o ON c.cust_id = o.cust_id