Pandas Joins
What is a Pandas Join?
A join combines information from two DataFrames based on a common relationship (key).
Two DataFrames → Common key → Matching Rule → Join Type → Combined DataFrame
Example: A bank stores customer information in one table and loan information in another table. We join them using customer_id.
Sample Tables
import pandas as pd
customers = pd.DataFrame({
"customer_id": [101, 102, 103, 104],
"name": ["ravi", "priya", "arun", "sneha"],
"city": ["Hyderabad", "Bangalore", "chennai", "pune"]
})
loans = pd.DataFrame({
"loan_id": ["L001", "L002"],
"customer_id": [101, 102],
"loan_amount": [500000, 300000]
})
customers
loans
| customer_id | name | city | |
|---|---|---|---|
| 0 | 101 | ravi | Hyderabad |
| 1 | 102 | priya | Bangalore |
| 2 | 103 | arun | chennai |
| 3 | 104 | sneha | pune |
| loan_id | customer_id | loan_amount | |
|---|---|---|---|
| 0 | L001 | 101 | 500000 |
| 1 | L002 | 102 | 300000 |
customers and loans. They are related through the common column customer_id.
Inner Join
Returns only the rows that have matching values in both tables.
pd.merge(customers, loans, on="customer_id", how="inner")
| customer_id | name | city | loan_id | loan_amount | |
|---|---|---|---|---|---|
| 0 | 101 | ravi | Hyderabad | L001 | 500000 |
| 1 | 102 | priya | Bangalore | L002 | 300000 |
Left Join
Returns all rows from the left table and matching rows from the right table. If there is no match, NaN is filled.
pd.merge(customers, loans, on="customer_id", how="left")
| customer_id | name | city | loan_id | loan_amount | |
|---|---|---|---|---|---|
| 0 | 101 | ravi | Hyderabad | L001 | 500000.0 |
| 1 | 102 | priya | Bangalore | L002 | 300000.0 |
| 2 | 103 | arun | chennai | NaN | NaN |
| 3 | 104 | sneha | pune | NaN | NaN |
Right Join
Returns all rows from the right table and matching rows from the left table.
pd.merge(customers, loans, on="customer_id", how="right")
| customer_id | name | city | loan_id | loan_amount | |
|---|---|---|---|---|---|
| 0 | 101 | ravi | Hyderabad | L001 | 500000 |
| 1 | 102 | priya | Bangalore | L002 | 300000 |
Outer Join (Full Outer)
Returns all rows from both tables. Missing matches are filled with NaN.
pd.merge(customers, loans, on="customer_id", how="outer")
| customer_id | name | city | loan_id | loan_amount | |
|---|---|---|---|---|---|
| 0 | 101 | ravi | Hyderabad | L001 | 500000.0 |
| 1 | 102 | priya | Bangalore | L002 | 300000.0 |
| 2 | 103 | arun | chennai | NaN | NaN |
| 3 | 104 | sneha | pune | NaN | NaN |
Joining More Than Two Tables
We can chain multiple merges together.
(customers
.merge(loans, on="customer_id", how="left")
.merge(transaction_summary, on="customer_id", how="left")
)
| customer_id | name | city | loan_id | loan_amount | number_transactions | total_transactions | |
|---|---|---|---|---|---|---|---|
| 0 | 101 | ravi | Hyderabad | L001 | 500000.0 | 3.0 | 8000.0 |
| 1 | 102 | priya | Bangalore | L002 | 300000.0 | 1.0 | 3000.0 |
| 2 | 103 | arun | chennai | NaN | NaN | NaN | NaN |
| 3 | 104 | sneha | pune | NaN | NaN | NaN | NaN |
Summary
- Inner Join → only matching rows from both tables
- Left Join → all rows from left table + matching from right
- Right Join → all rows from right table + matching from left
- Outer Join → all rows from both tables
- Use
pd.merge()ordf.merge() - We can chain multiple joins
Pandas Joins
Combine information from two or more DataFrames using a common key.
Select any topic from the left sidebar to begin.
Start Learning →
