Python Coding Classes for Kids East Coast Singapore

1. Introduction: Combining Multiple DataFrames

Real datasets are often split across tables (e.g. customers and orders). Pandas can combine them with:

• Merge / join — match on a shared key (like SQL joins)
• Concatenation — stack tables when no key match is needed

• Inner — only matching keys
• Left — all left rows, fill from right
• Right — all right rows, fill from left
• Outer — all rows from both sides

2. The Four Join Types

1. Inner join — keep only rows whose key exists in both frames
2. Left join — keep every left row; match right where possible
3. Right join — keep every right row; match left where possible
4. Outer join — keep every row from both sides

3. Sample Tables: customers and orders

import pandas as pd

customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Alice", "Bob", "Charlie"]
})

orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104],
    "customer_id": [1, 2, 2, 3],
    "amount": [250, 150, 300, 450]
})

Customers has three people; orders has four rows (Bob ordered twice).

4. Inner Join

merged_inner = pd.merge(customers, orders, on="customer_id", how="inner")
print(merged_inner)

IDs 1, 2, and 3 match both tables. Bob (id 2) appears twice because he has two orders. Result: four rows with names and amounts.

5. Left Join

merged_left = pd.merge(customers, orders, on="customer_id", how="left")
print(merged_left)

All customers are kept; order columns are filled where a match exists. Customers with no orders would still appear with null order fields.

6. Concatenation (pd.concat)

When you only need to stack rows and columns match, use concat:

concatenated_df = pd.concat([customers, customers], ignore_index=True)
print(concatenated_df)

This appends the second frame under the first (here, the same three customers twice). Column names and counts must match for a clean stack.