Zepto_cleaned.py

import pandas as pd
import os
from sqlalchemy import create_engine

# ============================================================
# Zepto Retail Analytics — Data Cleaning & PostgreSQL Loading
# Dataset: Kaggle Zepto E-Commerce Inventory (zepto_v2.csv)
# ============================================================

# 1. Load dataset
df = pd.read_csv("zepto_v2.csv")

print("Raw dataset shape:", df.shape)
print("\nColumns:", df.columns.tolist())
print("\nFirst 5 rows:\n", df.head())
print("\nMissing values:\n", df.isnull().sum())
print("\nDuplicate rows:", df.duplicated().sum())

# 2. Drop nulls
df_clean = df.dropna().copy()
print(f"\nRows after dropping nulls: {len(df_clean)}")

# 3. Standardise column names to snake_case
df_clean.columns = (
    df_clean.columns
    .str.strip()
    .str.lower()
    .str.replace(" ", "_")
)
print("\nStandardised columns:", df_clean.columns.tolist())

# 4. Remove zero MRP products
df_clean = df_clean[df_clean["mrp"] > 0].copy()
print(f"Rows after removing zero MRP: {len(df_clean)}")

# 5. Convert Paise to Rupees if needed
if df_clean["mrp"].mean() > 10000:
    df_clean["mrp"] = df_clean["mrp"] / 100
    df_clean["discountedsellingprice"] = df_clean["discountedsellingprice"] / 100
    print("Converted prices from Paise to Rupees")

# 6. Add weight segmentation
df_clean["weight_segment"] = pd.cut(
    df_clean["weightingms"],
    bins=[0, 100, 500, 1000, 5000, float("inf")],
    labels=["Mini", "Small", "Medium", "Large", "Bulk"]
)

# 7. Add price segment
df_clean["price_segment"] = pd.cut(
    df_clean["discountedsellingprice"],
    bins=[0, 50, 200, 500, 1000, float("inf")],
    labels=["Budget", "Economy", "Mid-Range", "Premium", "Luxury"]
)

# 8. Key statistics
print("\nKey Statistics")
print("="*40)
print(f"Total SKUs: {len(df_clean)}")
print(f"Total categories: {df_clean['category'].nunique()}")
print(f"Avg MRP: Rs.{df_clean['mrp'].mean():,.2f}")
print(f"Avg discounted price: Rs.{df_clean['discountedsellingprice'].mean():,.2f}")
print(f"Avg discount: {df_clean['discountpercent'].mean():,.2f}%")
print(f"In stock: {(~df_clean['outofstock']).sum()}")
print(f"Out of stock: {df_clean['outofstock'].sum()}")

# 9. Export CSV
os.makedirs("cleaned_data", exist_ok=True)
df_clean.to_csv("cleaned_data/zepto_cleaned.csv", index=False)
print("\nExported: cleaned_data/zepto_cleaned.csv")

# ============================================================
# 10. Connect to PostgreSQL and load cleaned data
# ============================================================
print("\nConnecting to PostgreSQL...")

# Configure your PostgreSQL connection details below
DB_USER = "postgres"
DB_PASSWORD = "your_password_here"  # Replace with your password
DB_HOST = "localhost"
DB_PORT = "4321"  # Replace with your port
DB_NAME = "Zepto_db"

engine = create_engine(f"postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}")

df_clean.to_sql(
    "zepto",
    engine,
    if_exists="replace",
    index=False
)

print("Data loaded into PostgreSQL successfully!")
print(f"Total rows loaded: {len(df_clean)}")