Pandas

Pandas Joins

01 · What is a Join? File · Joins · 1 of 8

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.

02 · Sample Tables File · Joins · 2 of 8

Sample Tables

Python
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
Output – customers
customer_id name city
0 101 ravi Hyderabad
1 102 priya Bangalore
2 103 arun chennai
3 104 sneha pune
Output – loans
loan_id customer_id loan_amount
0 L001 101 500000
1 L002 102 300000
We have two tables: customers and loans. They are related through the common column customer_id.
03 · Inner Join File · Joins · 3 of 8

Inner Join

Returns only the rows that have matching values in both tables.

Python
pd.merge(customers, loans, on="customer_id", how="inner")
Output
customer_id name city loan_id loan_amount
0 101 ravi Hyderabad L001 500000
1 102 priya Bangalore L002 300000
Only customers who have a loan will appear in the result (customer 101 and 102).
04 · Left Join File · Joins · 4 of 8

Left Join

Returns all rows from the left table and matching rows from the right table. If there is no match, NaN is filled.

Python
pd.merge(customers, loans, on="customer_id", how="left")
Output
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
All customers will appear. Customers without a loan will have NaN in loan columns.
05 · Right Join File · Joins · 5 of 8

Right Join

Returns all rows from the right table and matching rows from the left table.

Python
pd.merge(customers, loans, on="customer_id", how="right")
Output
customer_id name city loan_id loan_amount
0 101 ravi Hyderabad L001 500000
1 102 priya Bangalore L002 300000
All loans will appear. If a loan has no matching customer, customer columns will be NaN.
06 · Outer Join File · Joins · 6 of 8

Outer Join (Full Outer)

Returns all rows from both tables. Missing matches are filled with NaN.

Python
pd.merge(customers, loans, on="customer_id", how="outer")
Output
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
All customers and all loans will appear. Non-matching rows will have NaN values.
07 · Multiple Joins File · Joins · 7 of 8

Joining More Than Two Tables

We can chain multiple merges together.

Python
(customers
    .merge(loans, on="customer_id", how="left")
    .merge(transaction_summary, on="customer_id", how="left")
)
Output
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
First we join customers with loans, then we join the result with transaction_summary.
08 · Summary File · Joins · 8 of 8

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() or df.merge()
  • We can chain multiple joins
Home Pandas 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 →