from google.colab import drive
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
import seaborn as sns



# Environment Set up
drive.mount('/content/drive')
%cd /content/drive/MyDrive/dataset_final_project/


# ============================================================
# Function: Load CSV file with safety checks
# ============================================================

def load_csv(filepath, name="Dataset"):
    """Load a CSV file with error handling"""
    try:
        df = pd.read_csv(filepath)
        print(f"[INFO] {name} loaded successfully with {df.shape[0]} rows and {df.shape[1]} columns.")
        return df
    except FileNotFoundError:
        print(f"[ERROR] File not found: {filepath}")
        return pd.DataFrame()
    except Exception as e:
        print(f"[ERROR] Failed to load {name}: {e}")
        return pd.DataFrame()


# ============================================================
# Function: Clean Events dataset
# ============================================================

def clean_events(df_events, df_countries):
    """Clean events dataset: handle missing values and normalize text fields"""

    # Count missing values before cleaning
    missing_country = df_events["Country Code"].isna().sum()
    print(f"[INFO] Missing Country Code before cleaning: {missing_country} rows ({missing_country/len(df_events):.2%})")

    # Handle missing Country Code
    df_events["Country Code"] = df_events["Country Code"].fillna("UNK")
    if missing_country > 0:
        df_countries = pd.concat([
            df_countries,
            pd.DataFrame({
                "alpha-3": ["UNK"],
                "name": ["Unknown"],
                "region": ["Unknown"],
                "sub-region": ["Unknown"]
            })
        ], ignore_index=True)
        print(f"[INFO] Added 'UNK' as placeholder for {missing_country} missing rows.")

    # Drop rows with missing Units Sold
    missing_units = df_events["Units Sold"].isna().sum()
    df_events = df_events.dropna(subset=["Units Sold"])
    print(f"[INFO] Dropped {missing_units} rows with missing Units Sold.")

    # Normalize Sales Channel and Order Priority
    df_events.loc[:, "Sales Channel"] = df_events["Sales Channel"].str.strip().str.capitalize()
    df_events.loc[:, "Order Priority"] = df_events["Order Priority"].str.strip()


    print(f"[INFO] Dataset cleaned. Final shape: {df_events.shape}")
    return df_events, df_countries

# ============================================================
# Function: Merge & Transform Data
# ============================================================
def merge_and_transform(df_events, df_countries, df_products):
    """Merge 3 datasets and perform feature engineering for analysis."""

    # --- Merge 3 DataFrames into 1
    df_merged = (
        df_events
        .merge(df_countries, left_on="Country Code", right_on="alpha-3")
        .merge(df_products, left_on="Product ID", right_on="id")
    )
    print(f"[INFO] Shape after merging: {df_merged.shape}")

    # --- Rename columns for readability
    df_merged = df_merged.rename(columns={
        'item_type': 'Product Name',
        'name': 'Country Name',
        'region': 'Country Region',
        'sub-region': 'Country Sub-Region'
    })

    # --- Drop repetitive/unnecessary columns
    drop_cols = ['alpha-2', 'alpha-3', 'id']
    df_merged = df_merged.drop(columns=drop_cols, errors="ignore")
    print(f"[INFO] Dropped columns: {drop_cols}")

    # --- Update date columns to datetime
    df_merged["Order Date"] = pd.to_datetime(df_merged["Order Date"], errors="coerce")
    df_merged["Ship Date"] = pd.to_datetime(df_merged["Ship Date"], errors="coerce")

    # --- Add Year, Quarter, Fulfillment Days
    df_merged["Year"] = df_merged["Order Date"].dt.year
    df_merged["Quarter"] = df_merged["Order Date"].dt.to_period('Q')
    df_merged["Fulfillment Days"] = (
        df_merged["Ship Date"] - df_merged["Order Date"]
    ).dt.days

    # --- Calculate Profit and Revenue
    df_merged["Profit"] = df_merged["Unit Price"] - df_merged["Unit Cost"]
    df_merged["Profit Margin"] = (df_merged["Unit Price"] - df_merged["Unit Cost"]) / df_merged["Unit Price"] * 100
    df_merged["Total Profit"] = df_merged["Profit"] * df_merged["Units Sold"]
    df_merged["Total Revenue"] = df_merged["Unit Price"] * df_merged["Units Sold"]

    print("[INFO] Transformation complete. Final shape:", df_merged.shape)
    return df_merged


# ============================================================
# Data Visualization Functions
# ============================================================

def plot_profit_by_region(df):
    """Bar chart: Total Profit per Region."""
    plt.figure(figsize=(8, 6))
    ax = plt.gca()
    ax.yaxis.set_major_formatter(ticker.FuncFormatter(lambda x, _: f'{x/1e6:.1f}M'))

    (
        df.groupby("Country Region")["Total Profit"]
        .sum()
        .sort_values(ascending=False)
        .plot(kind="bar")
    )

    plt.title("Total Profit per Region")
    plt.xticks(rotation=45)
    plt.show()
    print("[INFO] Displayed bar chart for Total Profit by Region.")


def plot_fulfillment_vs_margin(df):
    """Scatter + regression: Fulfillment Days vs Profit Margin (Europe vs Asia)."""
    df_region = df[df["Country Region"].isin(["Europe", "Asia"])]

    plt.figure(figsize=(10, 6))
    sns.scatterplot(
        data=df_region,
        x="Fulfillment Days",
        y="Profit Margin",
        hue="Country Region",
        alpha=0.7
    )

    # Trendlines per region
    for region, color in [("Europe", "blue"), ("Asia", "orange")]:
        sns.regplot(
            data=df_region[df_region["Country Region"] == region],
            x="Fulfillment Days",
            y="Profit Margin",
            scatter=False,
            color=color,
            label=f"{region} Trend"
        )

    plt.title("Fulfillment Days vs Profit Margin (Europe vs Asia)")
    plt.xlabel("Fulfillment Days")
    plt.ylabel("Profit Margin (%)")
    plt.legend()
    plt.grid(True)
    plt.show()
    print("[INFO] Displayed scatter + regression for Fulfillment Days vs Profit Margin.")


def plot_profit_trend_by_priority(df):
    """Line chart: Profit trend per Order Priority across years."""
    order_priority = df["Order Priority"].unique()

    priority_trend = (
        df.groupby(["Order Priority", "Year"])["Total Profit"]
        .sum()
        .reset_index()
    )

    priority_mapping = {
        "M": "Medium",
        "C": "Critical",
        "L": "Low",
        "H": "High"
    }

    plt.figure(figsize=(12, 6))
    ax = plt.gca()
    ax.yaxis.set_major_formatter(ticker.FuncFormatter(lambda x, _: f'{x/1e6:.1f}M'))

    for priority in order_priority:
        priority_data = priority_trend[priority_trend["Order Priority"] == priority]
        plt.plot(
            priority_data["Year"],
            priority_data["Total Profit"],
            label=priority_mapping.get(priority, priority)
        )

    plt.xlabel("Year")
    plt.ylabel("Profit")
    plt.title("Profit Trend by Order Priority")
    plt.legend()
    plt.grid(True)
    plt.show()
    print("[INFO] Displayed line chart for Profit Trend by Order Priority.")


def plot_avg_profit_per_quarter(df):
    """Bar chart: Average Profit per Quarter (2010–2017)."""
    quarterly_profit = df.groupby('Quarter')['Total Profit'].sum().reset_index()
    quarterly_profit['Quarter'] = quarterly_profit['Quarter'].dt.to_timestamp()
    quarterly_profit['Quarter_Num'] = quarterly_profit['Quarter'].dt.quarter

    avg_quarter = quarterly_profit.groupby('Quarter_Num')['Total Profit'].mean().reset_index()

    plt.figure(figsize=(8, 5))
    plt.bar(
        avg_quarter['Quarter_Num'],
        avg_quarter['Total Profit'],
        color=['blue','green','orange','red']
    )
    plt.title("Average Profit per Quarter (2010–2017)")
    plt.xlabel("Quarter")
    plt.ylabel("Average Profit")
    plt.xticks([1,2,3,4], ["Q1","Q2","Q3","Q4"])
    plt.show()
    print("[INFO] Displayed bar chart for Average Profit per Quarter.")


def plot_europe_subregion_distribution(df):
    """Pie chart: Order distribution across Europe Sub-Regions."""
    df_europe = df[df["Country Region"] == "Europe"]

    europe_sub_region = (
        df_europe.groupby("Country Sub-Region")["Order ID"]
        .count()
        .sort_values(ascending=False)
    )

    plt.figure(figsize=(8, 6))
    europe_sub_region.plot(kind="pie", autopct="%1.1f%%", startangle=140)
    plt.title("Order Distribution of Europe Sub-Region")
    plt.ylabel("")
    plt.show()
    print("[INFO] Displayed pie chart for Order Distribution in Europe Sub-Region.")

def plot_sales_channel_revenue_trend(df):
  """Line chart: Revenue Trend by Sales Channel"""
  sales_channels = df["Sales Channel"].unique()

  sales_trend = (
      df.groupby(["Sales Channel", "Year"])["Total Revenue"].sum().reset_index()
  )

  plt.figure(figsize=(12, 6))
  ax = plt.gca()
  ax.yaxis.set_major_formatter(ticker.FuncFormatter(lambda x, _: f'{x/1e6:.1f}M'))

  for channel in sales_channels:
      channel_data = sales_trend[sales_trend["Sales Channel"] == channel]
      plt.plot(channel_data["Year"], channel_data["Total Revenue"], label=channel)

  plt.xlabel("Year")
  plt.ylabel("Revenue")
  plt.title("Revenue Trend by Sales Channel")
  plt.legend()
  plt.grid(True)
  plt.show()

# ============================================================
# Main Function
# ============================================================

df_countries = load_csv("countries.csv", "Countries")
df_events = load_csv("events.csv", "Events")
df_products = load_csv("products.csv", "Products")

df_events, df_countries = clean_events(df_events, df_countries)
df_merged = merge_and_transform(df_events, df_countries, df_products)

plot_profit_by_region(df_merged)
plot_fulfillment_vs_margin(df_merged)
plot_profit_trend_by_priority(df_merged)
plot_avg_profit_per_quarter(df_merged)
plot_europe_subregion_distribution(df_merged)
plot_sales_channel_revenue_trend(df_merged)

Mounted at /content/drive
/content/drive/MyDrive/dataset_final_project
[INFO] Countries loaded successfully with 249 rows and 5 columns.
[INFO] Events loaded successfully with 1330 rows and 10 columns.
[INFO] Products loaded successfully with 12 rows and 2 columns.
[INFO] Missing Country Code before cleaning: 82 rows (6.17%)
[INFO] Added 'UNK' as placeholder for 82 missing rows.
[INFO] Dropped 2 rows with missing Units Sold.
[INFO] Dataset cleaned. Final shape: (1328, 10)
[INFO] Shape after merging: (1328, 17)
[INFO] Dropped columns: ['alpha-2', 'alpha-3', 'id']
[INFO] Transformation complete. Final shape: (1328, 21)

[INFO] Displayed bar chart for Total Profit by Region.

[INFO] Displayed scatter + regression for Fulfillment Days vs Profit Margin.

[INFO] Displayed line chart for Profit Trend by Order Priority.

[INFO] Displayed bar chart for Average Profit per Quarter.

[INFO] Displayed pie chart for Order Distribution in Europe Sub-Region.

📊 Sales Data Analysis (2010–2017)¶

1. Executive Summary¶

2. Introduction¶

3. Business Questions¶

4. Analysis & Visualizations¶

Q1. How does sales performance differ between Europe and Asia?¶

Analysis: Total Profit per Region¶

Q2. What is the relationship between order fulfillment time and profit margin?¶

Analysis: Fulfillment Days vs Profit Margin¶

Q3. Which order priority contributes most to overall profitability?¶

Analysis: Profit Trend by Order Priority (2010–2017)¶

Q4. Are there any seasonal trends in sales or profit across quarters and years?¶

Analysis: Average Total Profit per Quarter (2010–2017)¶

Q5. How is the distribution of orders across sub-regions within Europe?¶

Analysis: Order Distribution of Europe Sub-Region¶

Q6. What is the relationship between Sales Channel and Total Revenue?¶

Revenue Trend by Sales Channel (2010–2017)¶

5. Key Insights & Recommendations¶

Key Insights¶

Recommendations¶

6. Portfolio Note¶