Start: load data (one-line)

# If you use the Google Sheet: (first sheet / default gid)
sheet_url = "https://docs.google.com/spreadsheets/d/1UgLeYK_U-DMKbJt66aWX_Tu7E9zmDHOMGFP9C8GSnbA/export?format=csv"
df = pd.read_csv(sheet_url)

# Or load your local orders_raw.csv
# df = pd.read_csv("orders_raw.csv")
    

What this does: loads your master table into `df` — the single table that contains orders, customer and product info.

Step 1 — Make customers, products & orders tables

# customers: unique customers
customers = df[['customer_id','customer_name','city','gender','signup_date','loyalty_member']].drop_duplicates()

# products: unique products
products  = df[['product_id','product_name','category','unit_price']].drop_duplicates()

# orders: trimmed orders table
orders    = df[['order_id','customer_id','product_id','order_date','quantity','discount','total_amount','payment_method']].copy()
    

Why: Smaller tables make joins simpler and mirror how real databases are organized.

Step 2 — Easy joins (what they do)

• Left join: keep every row from left table, add matching info from right. (Good for: keep all orders, add customer info.)
• Inner join: keep only rows that appear in both tables. (Good for: analysis where both sides must exist.)
• Right join: keep every row from right table, add matching left info (opposite of left).
• Outer join: keep all rows from both tables, fill missing values with NaN. (Good for a full view.)

Step 3 — Code examples (one-liners)

# left join: keep all orders, bring customer info
orders_with_customers = orders.merge(customers, on='customer_id', how='left')
orders_with_customers.head()

# left join orders with products
orders_full = orders.merge(products, on='product_id', how='left')
orders_full.head()

# inner join (only orders that have matching product and customer)
orders_inner = orders.merge(customers, on='customer_id', how='inner').merge(products, on='product_id', how='inner')
orders_inner.head()

# outer join example: full outer between products and orders (shows unmatched products or orders)
products_orders_outer = products.merge(orders, on='product_id', how='outer', indicator=True)
products_orders_outer.head()
    

Tip: use indicator=True to see where rows came from (`left_only`, `right_only`, `both`).

Step 4 — Simple business questions (use joins)

Q: Who are the top 5 customers by spend (name + city)?

cust_revenue = orders.groupby('customer_id')['total_amount'].sum().reset_index()
cust_top = cust_revenue.merge(customers, on='customer_id', how='left').sort_values('total_amount', ascending=False).head(5)
cust_top[['customer_name','city','total_amount']]
    

Q: Show orders with product name and customer city:

orders_full = orders.merge(customers[['customer_id','city','customer_name']], on='customer_id', how='left') \
                    .merge(products[['product_id','product_name','category']], on='product_id', how='left')
orders_full[['order_id','customer_name','city','product_name','category','total_amount']].head(8)
    

Mini exercises (5–15 min)

1. Create customers, products, and orders tables from your loaded df.
2. Do a left join of orders with customers and show the first 5 rows.
3. Find top 3 products by revenue (join orders→products to show product names).
4. Use indicator=True with an outer join between products and orders and count how many rows are left_only, right_only, both.
5. Explain in one line when you’d use an inner vs left join (class discussion).