import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')

# Beginner Example 1: Load the Online Retail Transactions Dataset
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
retail_df = pd.read_excel(url, sheet_name='Year 2010-2011')
retail_df['InvoiceDate'] = pd.to_datetime(retail_df['InvoiceDate'])
print(retail_df.shape)
print(retail_df.head(3))

# Beginner Example 2: Find total revenue in the dataset
retail_df['Revenue'] = retail_df['Quantity'] * retail_df['Price']
total_revenue = retail_df['Revenue'].sum()
print(f"Total revenue in this period: GBP {total_revenue:,.2f}")

# Beginner Example 3: Revenue by Product Description
revenue_by_product = retail_df.groupby('Description')['Revenue'].sum().sort_values(ascending=False)
print(revenue_by_product.head(5))

# Beginner Example 4: Revenue by Country
revenue_by_country = retail_df.groupby('Country')['Revenue'].sum().sort_values(ascending=False)
print(revenue_by_country.head(5))

# Beginner Example 5: Find number of unique customers
unique_customers = retail_df['Customer ID'].nunique()
print(f"Number of unique customers: {unique_customers}")

# Intermediate Example 1: Monthly Revenue Trend
monthly_revenue = retail_df.set_index('InvoiceDate').resample('M')['Revenue'].sum()
print(monthly_revenue)

# Intermediate Example 2: Average Order Value (AOV)
retail_df['OrderValue'] = retail_df['Revenue']
aov = retail_df.groupby('Invoice')['OrderValue'].sum().mean()
print(f"Average order value (AOV): GBP {aov:.2f}")

# Intermediate Example 3: High-Value Customer Identification
customer_revenue = retail_df.groupby('Customer ID')['Revenue'].sum()
top_customers = customer_revenue.sort_values(ascending=False).head(5)
print(top_customers)

# Intermediate Example 4: Product Category Revenue using Retail Product Catalog
np.random.seed(42)
categories = ['Electronics','Clothing','Home','Sports','Beauty']
product_ids = list(range(1001,1101))
product_categories = np.random.choice(categories,100)
product_prices = np.round(np.random.uniform(5,500,100),2)
catalog_df = pd.DataFrame({'ProductID':product_ids,'Category':product_categories,'Price':product_prices})
catalog_df['ProductID'] = catalog_df['ProductID'].astype(str)

# Merge with retail_df using StockCode as ProductID (string-match needed)
retail_df['ProductID'] = retail_df['StockCode'].astype(str)
merged_df = pd.merge(retail_df, catalog_df, left_on='ProductID', right_on='ProductID', how='left')

category_revenue = merged_df.groupby('Category')['Revenue'].sum().sort_values(ascending=False)
print(category_revenue)

# Intermediate Example 5: Time Series of a Product's Revenue
target_product = revenue_by_product.index[0]  # Use top seller
product_time_series = retail_df[retail_df['Description'] == target_product].set_index('InvoiceDate').resample('M')['Revenue'].sum()
print(f"Monthly revenue for {target_product}:")
print(product_time_series)

# Advanced Example 1: Calculate Revenue Growth Rate Month-over-Month
monthly_growth = monthly_revenue.pct_change().dropna() * 100
print("Monthly revenue growth rate (%) per month:")
print(monthly_growth.round(2))

# Advanced Example 2: Customer Segmentation by Revenue Quantiles
quantiles = customer_revenue.quantile([0.25, 0.5, 0.75]).to_dict()

def customer_segment(rev):
    if rev <= quantiles[0.25]:
        return 'Low Value'
    elif rev <= quantiles[0.5]:
        return 'Mid-Low Value'
    elif rev <= quantiles[0.75]:
        return 'Mid-High Value'
    else:
        return 'High Value'

customer_segments = customer_revenue.apply(customer_segment).value_counts()
print(customer_segments)

# Advanced Example 3: Detect Revenue Loss from Missing Values
missing_revenue = retail_df[retail_df['Revenue'].isnull()]
num_missing = missing_revenue.shape[0]
print(f"Transactions missing revenue: {num_missing}")

# Error Handling Example 1: Dropping Incomplete Records
clean_df = retail_df.dropna(subset=['Quantity','Price','Customer ID'])
print(f"Removed {len(retail_df) - len(clean_df)} incomplete records.")
print(f"Clean data now has {clean_df.shape[0]} rows.")

# Error Handling Example 2: Detecting Negative Sales
neg_qty = retail_df[retail_df['Quantity'] < 0]
print(f"Number of negative quantity transactions: {neg_qty.shape[0]}")

# Error Handling Example 3: Incorrect Aggregation Trap
incorrect_total = retail_df['Price'].sum()
correct_total = retail_df['Revenue'].sum()
print(f"Incorrect total (should NOT sum price alone): GBP {incorrect_total:,.2f}")
print(f"Correct total revenue: GBP {correct_total:,.2f}")

# Advanced Example 4: Simple Demand Forecasting (Moving Average)
monthly_revenue_ma = monthly_revenue.rolling(window=3).mean()
print("3-month moving average of revenue:")
print(monthly_revenue_ma)

# Advanced Example 5: Market Basket Analysis (Simplified)
from itertools import combinations
retail_baskets = retail_df.groupby('Invoice')['Description'].unique().tolist()
basket_pairs = []
for basket in retail_baskets:
    if len(basket) > 1:
        basket_pairs.extend(combinations(sorted(basket), 2))
from collections import Counter
pair_counts = Counter(basket_pairs)
top_pairs = pair_counts.most_common(5)
print("Top 5 purchased product pairs:")
print(top_pairs)

# Step 1: Clean negative and missing transactions
e2e_df = merged_df[(merged_df['Quantity'] > 0) & (~merged_df['Revenue'].isnull()) & (~merged_df['Category'].isnull())]
print(f"Rows after cleaning: {len(e2e_df)}")

# Step 2: Find fastest-growing categories (last 6 vs. prior 6 months)
e2e_df = e2e_df.set_index('InvoiceDate')
last_12m = e2e_df.sort_index().last('12M')
six_months_ago = last_12m.index.max() - pd.DateOffset(months=6)
cat_group = last_12m.groupby([pd.Grouper(freq='M'), 'Category'])['Revenue'].sum().reset_index()
growth_summary = cat_group.pivot(index='Category', columns='InvoiceDate', values='Revenue').fillna(0)
growth_summary['First6'] = growth_summary.iloc[:, :6].sum(axis=1)
growth_summary['Last6'] = growth_summary.iloc[:, -6:].sum(axis=1)
growth_summary['GrowthRate'] = ((growth_summary['Last6'] - growth_summary['First6']) / growth_summary['First6']) * 100
fastest = growth_summary['GrowthRate'].sort_values(ascending=False).head(3)
print('Fastest-growing categories (last 6 vs. first 6 months):')
print(fastest)