Spaces:
Sleeping
Sleeping
File size: 4,888 Bytes
cf796c5 37c8346 cf796c5 37c8346 cf796c5 37c8346 cf796c5 37c8346 d84e2c7 37c8346 d84e2c7 37c8346 cf796c5 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 | import sqlite3
import pandas as pd
import os
from pathlib import Path
RAW = Path("data/raw")
DB = Path("data/olist.db")
TABLE_MAP = {
"olist_customers_dataset.csv": "customers",
"olist_orders_dataset.csv": "orders",
"olist_order_items_dataset.csv": "order_items",
"olist_order_payments_dataset.csv": "order_payments",
"olist_order_reviews_dataset.csv": "order_reviews",
"olist_products_dataset.csv": "products",
"olist_sellers_dataset.csv": "sellers",
"olist_geolocation_dataset.csv": "geolocation",
"product_category_name_translation.csv": "category_translation",
}
def load_tables(conn):
"""Load CSV files into SQLite tables"""
for filename, table in TABLE_MAP.items():
filepath = RAW / filename
if not filepath.exists():
print(f" ⚠ Skipping {table} - file not found: {filepath}")
continue
df = pd.read_csv(filepath, low_memory=False)
df.to_sql(table, conn, if_exists="replace", index=False)
print(f" ✓ Loaded {table} ({len(df)} rows)")
def create_indexes(conn):
"""Create indexes for faster queries"""
indexes = [
"CREATE INDEX IF NOT EXISTS idx_orders_id ON orders(order_id)",
"CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id)",
"CREATE INDEX IF NOT EXISTS idx_items_order ON order_items(order_id)",
"CREATE INDEX IF NOT EXISTS idx_items_product ON order_items(product_id)",
"CREATE INDEX IF NOT EXISTS idx_items_seller ON order_items(seller_id)",
"CREATE INDEX IF NOT EXISTS idx_reviews_order ON order_reviews(order_id)",
"CREATE INDEX IF NOT EXISTS idx_payments_order ON order_payments(order_id)",
]
for idx in indexes:
conn.execute(idx)
print(" ✓ Indexes created")
def create_views(conn):
"""Create composite views for common queries"""
# Use dataset reference date (2018-09-03 - last order date in dataset)
DEMO_DATE = '2018-09-03'
# Main composite view — single query for full order info
conn.execute(f"""
CREATE VIEW IF NOT EXISTS v_order_full AS
SELECT
o.order_id,
o.customer_id,
o.order_status,
o.order_purchase_timestamp,
o.order_approved_at,
o.order_delivered_carrier_date,
o.order_delivered_customer_date,
o.order_estimated_delivery_date,
ROUND(SUM(oi.price + oi.freight_value), 2) AS total_amount,
COUNT(oi.order_item_id) AS item_count,
GROUP_CONCAT(DISTINCT oi.seller_id) AS seller_ids,
GROUP_CONCAT(DISTINCT p.payment_type) AS payment_methods,
CASE
WHEN o.order_delivered_customer_date IS NOT NULL
AND o.order_delivered_customer_date > o.order_estimated_delivery_date
THEN 1
WHEN o.order_delivered_customer_date IS NULL
AND o.order_status NOT IN ('delivered', 'canceled')
AND DATE('{DEMO_DATE}') > DATE(o.order_estimated_delivery_date)
THEN 1
ELSE 0
END AS is_late,
CASE
WHEN o.order_delivered_customer_date IS NOT NULL AND o.order_delivered_customer_date > o.order_estimated_delivery_date
THEN ROUND(JULIANDAY(o.order_delivered_customer_date) - JULIANDAY(o.order_estimated_delivery_date), 1)
WHEN o.order_delivered_customer_date IS NULL AND o.order_status NOT IN ('delivered', 'canceled')
THEN ROUND(JULIANDAY('{DEMO_DATE}') - JULIANDAY(o.order_estimated_delivery_date), 1)
ELSE 0
END AS days_overdue
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN order_payments p ON o.order_id = p.order_id
GROUP BY o.order_id
""")
# Seller performance view
conn.execute("""
CREATE VIEW IF NOT EXISTS v_seller_summary AS
SELECT
oi.seller_id,
s.seller_city,
s.seller_state,
COUNT(DISTINCT oi.order_id) AS total_orders,
ROUND(AVG(r.review_score), 2) AS avg_rating,
SUM(CASE WHEN r.review_score <= 2 THEN 1 ELSE 0 END) AS negative_reviews,
SUM(CASE WHEN r.review_score >= 4 THEN 1 ELSE 0 END) AS positive_reviews
FROM order_items oi
JOIN sellers s ON oi.seller_id = s.seller_id
LEFT JOIN order_reviews r ON oi.order_id = r.order_id
GROUP BY oi.seller_id
""")
print(" ✓ Views created")
if __name__ == "__main__":
print("Setting up Olist database...")
# Create data directory if it doesn't exist
DB.parent.mkdir(parents=True, exist_ok=True)
conn = sqlite3.connect(DB)
try:
load_tables(conn)
create_indexes(conn)
create_views(conn)
conn.commit()
print(f"\n✅ Database ready at {DB}")
except Exception as e:
print(f"\n❌ Error: {e}")
conn.rollback()
finally:
conn.close()
|