branch_performance.py

"""
Branch Performance Analysis with Python: Multi-City Sales Comparison

Compares retail branch performance across the 5 largest provinces on Java
Island using pivot tables, tracking order count, customer count, product
count, brand count, and GMV on a monthly basis.

Workflow:
1. Load and combine quarterly data
2. Data inspection
3. Data cleaning / transformation
4. Filter to target provinces
5. Group and aggregate
6. Unstack for comparison
7. Slice by measurement
8. Resample monthly
9. Visualize all measurements
"""

import pandas as pd
import matplotlib.pyplot as plt

DATA_URLS = [
    "https://storage.googleapis.com/dqlab-dataset/10%25_original_randomstate%3D42/retail_data_from_1_until_3_reduce.csv",
    "https://storage.googleapis.com/dqlab-dataset/10%25_original_randomstate%3D42/retail_data_from_4_until_6_reduce.csv",
    "https://storage.googleapis.com/dqlab-dataset/10%25_original_randomstate%3D42/retail_data_from_7_until_9_reduce.csv",
    "https://storage.googleapis.com/dqlab-dataset/10%25_original_randomstate%3D42/retail_data_from_10_until_12_reduce.csv",
]

JAVA_PROVINCES = ["DKI Jakarta", "Jawa Barat", "Jawa Tengah", "Jawa Timur", "Yogyakarta"]


# ---------------------------------------------------------------------------
# 1-2. LOAD, COMBINE & INSPECT DATA
# ---------------------------------------------------------------------------

def load_and_combine_data(urls: list[str] = DATA_URLS) -> pd.DataFrame:
    """Load each quarterly CSV and concatenate into a single dataframe."""
    quarterly_frames = [pd.read_csv(url) for url in urls]

    print("DATA CHECK\n")
    print(quarterly_frames[0].head())
    for i, df in enumerate(quarterly_frames, start=1):
        print(f"Columns in retail_data{i}: {df.columns.tolist()}")

    retail_table = pd.concat(quarterly_frames)
    print("\nRow count:", retail_table.shape[0])
    print("\nInfo:")
    print(retail_table.info())
    print("\nDescriptive statistics:\n", retail_table.describe())

    return retail_table


# ---------------------------------------------------------------------------
# 3. DATA CLEANING / TRANSFORMATION
# ---------------------------------------------------------------------------

def clean_data(retail_table: pd.DataFrame) -> pd.DataFrame:
    """Remove invalid rows and fix column dtypes."""
    print("DATA TRANSFORMATION\n")

    # Drop rows with negative item_price or total_price
    invalid_prices = retail_table.loc[
        (retail_table["item_price"] < 0) | (retail_table["total_price"] < 0)
    ]
    print("\nitem_price < 0 or total_price < 0:\n", invalid_prices)
    if invalid_prices.shape[0] != 0:
        retail_table = retail_table.loc[
            (retail_table["item_price"] > 0) & (retail_table["total_price"] > 0)
        ]

    # Drop rows with undefined order_id
    undefined_orders = retail_table.loc[retail_table["order_id"] == "undefined"]
    print("\norder_id == 'undefined':\n", undefined_orders)
    if undefined_orders.shape[0] != 0:
        retail_table = retail_table.loc[retail_table["order_id"] != "undefined"]

    # Fix dtypes
    retail_table["order_id"] = retail_table["order_id"].astype("int64")
    retail_table["order_date"] = pd.to_datetime(retail_table["order_date"])

    print("\nInfo:")
    print(retail_table.info())
    print("\nDescriptive statistics:\n", retail_table.describe())

    return retail_table


# ---------------------------------------------------------------------------
# 4-6. FILTER, GROUP, AND UNSTACK
# ---------------------------------------------------------------------------

def filter_to_java_provinces(retail_table: pd.DataFrame) -> pd.DataFrame:
    """Keep only the 5 largest provinces on Java Island."""
    print("\nFILTER TO JAVA'S 5 LARGEST PROVINCES\n")
    retail_table = retail_table.loc[retail_table["province"].isin(JAVA_PROVINCES)]
    print(retail_table["province"].unique())
    return retail_table


def group_and_aggregate(retail_table: pd.DataFrame) -> pd.DataFrame:
    """Group by order_date and province, aggregating each metric."""
    grouped = retail_table.groupby(["order_date", "province"]).agg({
        "order_id": "nunique",
        "customer_id": "nunique",
        "product_id": "nunique",
        "brand": "nunique",
        "total_price": "sum",
    })
    grouped.columns = ["order", "customer", "product", "brand", "GMV"]
    print("\ngroupby_city_province (top 10 rows):\n", grouped.head(10))
    return grouped


def unstack_by_province(grouped: pd.DataFrame) -> pd.DataFrame:
    """Reshape so order_date is the row index and province becomes columns."""
    unstacked = grouped.unstack("province").fillna(0)
    print("\nunstack_city_province (top 5 rows):\n", unstacked.head())
    return unstacked


# ---------------------------------------------------------------------------
# 7-8. SLICE BY MEASUREMENT & RESAMPLE MONTHLY
# ---------------------------------------------------------------------------

def slice_and_resample_monthly(unstacked: pd.DataFrame, measurement: str) -> pd.DataFrame:
    """Slice out one measurement across all provinces and resample to
    monthly averages."""
    idx = pd.IndexSlice
    by_measurement = unstacked.loc[:, idx[measurement]]
    monthly_mean = by_measurement.resample("M").mean()
    return monthly_mean


# ---------------------------------------------------------------------------
# 9. VISUALIZE ALL MEASUREMENTS
# ---------------------------------------------------------------------------

def plot_single_measurement(unstacked: pd.DataFrame, measurement: str = "order") -> None:
    """Plot one measurement's monthly average across all provinces."""
    monthly_mean = slice_and_resample_monthly(unstacked, measurement)
    monthly_mean.plot(
        figsize=(8, 5),
        title=f"Average Daily {measurement.title()} Size in Month View for all Province"
    )
    plt.ylabel(f"avg {measurement} size")
    plt.xlabel("month")
    plt.show()


def plot_all_measurements(grouped: pd.DataFrame, unstacked: pd.DataFrame) -> None:
    """Loop through every measurement and plot each as a panel in one figure."""
    fig, axes = plt.subplots(len(grouped.columns), 1, figsize=(8, 25))

    for i, measurement in enumerate(grouped.columns):
        monthly_mean = slice_and_resample_monthly(unstacked, measurement)
        monthly_mean.plot(
            title=f"Average Daily {measurement} Size in Month View for all Province",
            ax=axes[i]
        )
        axes[i].set_ylabel(f"avg {measurement} size")
        axes[i].set_xlabel("month")

    plt.tight_layout()
    plt.show()


# ---------------------------------------------------------------------------
# MAIN
# ---------------------------------------------------------------------------

def main():
    retail_table = load_and_combine_data()
    retail_table = clean_data(retail_table)
    retail_table = filter_to_java_provinces(retail_table)

    grouped = group_and_aggregate(retail_table)
    unstacked = unstack_by_province(grouped)

    plot_single_measurement(unstacked, measurement="order")
    plot_all_measurements(grouped, unstacked)


if __name__ == "__main__":
    main()