Data Cleaning — E-commerce Analytics¶

The Challenge¶

Unlike single-file projects, the work here is joining 8 tables into analysis-ready frames — and doing it without accidentally multiplying rows or losing data.

Step 1 — Load All Tables¶

import pandas as pd
import numpy as np

orders = pd.read_csv("olist_orders_dataset.csv", parse_dates=[
    "order_purchase_timestamp", "order_approved_at",
    "order_delivered_carrier_date", "order_delivered_customer_date",
    "order_estimated_delivery_date"])
items = pd.read_csv("olist_order_items_dataset.csv")
customers = pd.read_csv("olist_customers_dataset.csv")
payments = pd.read_csv("olist_order_payments_dataset.csv")
reviews = pd.read_csv("olist_order_reviews_dataset.csv")
products = pd.read_csv("olist_products_dataset.csv")
translation = pd.read_csv("product_category_name_translation.csv")

for name, df in [("orders", orders), ("items", items), ("customers", customers),
                 ("payments", payments), ("reviews", reviews), ("products", products)]:
    print(f"{name}: {df.shape}")

Step 2 — Translate Product Categories to English¶

products = products.merge(translation, on="product_category_name", how="left")
products["category"] = products["product_category_name_english"].fillna("unknown")
print(products["category"].value_counts().head(10))

Step 3 — Filter to Delivered Orders for Revenue Analysis¶

print(orders["order_status"].value_counts())

# For revenue/delivery analysis, focus on delivered orders
delivered = orders[orders["order_status"] == "delivered"].copy()
print(f"Delivered orders: {len(delivered)} of {len(orders)}")

Step 4 — Compute Delivery Metrics¶

# Actual delivery time (days from purchase to arrival)
delivered["delivery_days"] = (
    delivered["order_delivered_customer_date"] - delivered["order_purchase_timestamp"]
).dt.days

# Was it late? (delivered after the estimated date)
delivered["delivery_delay_days"] = (
    delivered["order_delivered_customer_date"] - delivered["order_estimated_delivery_date"]
).dt.days
delivered["is_late"] = (delivered["delivery_delay_days"] > 0).astype(int)

# Drop rows with missing delivery dates (data quality)
before = len(delivered)
delivered = delivered.dropna(subset=["order_delivered_customer_date"])
print(f"Dropped {before - len(delivered)} rows with no delivery date")
print(f"Avg delivery: {delivered['delivery_days'].mean():.1f} days")
print(f"Late rate: {delivered['is_late'].mean():.1%}")

Step 5 — Build Order-Level Revenue (Aggregate Items First)¶

Aggregate items BEFORE joining to orders

An order can have multiple items (multiple rows in order_items). If you join items directly to orders and then aggregate, you risk double-counting order-level fields. Aggregate items to one row per order first.

# One row per order with total value
order_value = (
    items.groupby("order_id")
    .agg(
        items_count=("order_item_id", "count"),
        total_price=("price", "sum"),
        total_freight=("freight_value", "sum"),
    )
    .reset_index()
)
order_value["order_total"] = order_value["total_price"] + order_value["total_freight"]

Step 6 — Build the Master Analysis Frame¶

# Start with delivered orders, add value, customer, and review
master = (
    delivered
    .merge(order_value, on="order_id", how="inner")
    .merge(customers[["customer_id", "customer_unique_id", "customer_state", "customer_city"]],
           on="customer_id", how="left")
    .merge(reviews[["order_id", "review_score"]].drop_duplicates("order_id"),
           on="order_id", how="left")
)

print(f"Master frame: {master.shape}")
print(f"Rows before/after should match delivered+value join (no duplication)")

Step 7 — Verify No Row Multiplication¶

# Sanity check: master should have one row per order
print(f"Unique orders in master: {master['order_id'].nunique()}")
print(f"Total rows in master: {len(master)}")
assert master["order_id"].nunique() == len(master), "Row multiplication detected!"

Always assert after a join

A failed assertion here would mean a join multiplied rows (e.g., reviews had duplicate order_ids). Catching it now prevents every downstream metric from being wrong.

Step 8 — Customer-Level Aggregation (Using customer_unique_id!)¶

customer_summary = (
    master.groupby("customer_unique_id")
    .agg(
        orders=("order_id", "nunique"),
        total_spend=("order_total", "sum"),
        avg_order=("order_total", "mean"),
        first_order=("order_purchase_timestamp", "min"),
        last_order=("order_purchase_timestamp", "max"),
        avg_review=("review_score", "mean"),
    )
    .reset_index()
)
customer_summary["is_repeat"] = (customer_summary["orders"] > 1).astype(int)
print(f"Repeat customer rate: {customer_summary['is_repeat'].mean():.1%}")

Step 9 — Export¶

master.to_csv("ecommerce_master.csv", index=False)
customer_summary.to_csv("ecommerce_customers.csv", index=False)
print("Exported analysis frames.")

← Dataset Guide · Next: SQL Analysis →