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

# Load online retail transactions dataset from UCI repository
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
df_transactions = pd.read_excel(url, sheet_name='Year 2010-2011')
df_transactions['InvoiceDate'] = pd.to_datetime(df_transactions['InvoiceDate'])
print(df_transactions.shape)
print(df_transactions.head(3))

# Example 1: Compute basic statistics for transaction quantities
print('Mean Quantity Sold:', df_transactions['Quantity'].mean())
print('Min Quantity Sold:', df_transactions['Quantity'].min())
print('Max Quantity Sold:', df_transactions['Quantity'].max())

# Example 2: Total revenue by summing Quantity * Price
df_transactions['Revenue'] = df_transactions['Quantity'] * df_transactions['Price']
total_revenue = df_transactions['Revenue'].sum()
print('Total Revenue in dataset:', round(total_revenue,2))

# Example 3: Count number of unique customers
n_customers = df_transactions['Customer ID'].nunique()
print('Number of unique customers:', n_customers)

# Load synthetic product catalog data
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)
df_products = pd.DataFrame({'ProductID':product_ids,'Category':product_categories,'Price':product_prices})
print(df_products.shape)
print(df_products.head(3))

# Example 4: Compute average price by product category
mean_price_by_cat = df_products.groupby('Category')['Price'].mean()
print('Average price per category:')
print(mean_price_by_cat)

# Load synthetic customer orders dataset
np.random.seed(42)
n_orders = 1000
order_ids = list(range(1, n_orders+1))
customer_ids = np.random.randint(1000,1500,n_orders)
product_ids_orders = np.random.randint(1001,1100,n_orders)
quantities = np.random.randint(1,5,n_orders)
order_dates = pd.date_range('2023-01-01', periods=n_orders, freq='h')
df_orders = pd.DataFrame({'OrderID':order_ids,'CustomerID':customer_ids,'ProductID':product_ids_orders,'Quantity':quantities,'OrderDate':order_dates})
print(df_orders.shape)
print(df_orders.head(3))

# Example 5: Find the most frequently ordered product
top_product = df_orders['ProductID'].mode()[0]
print('Most ordered product ID:', top_product)

# Example 6: Orders per customer - finding loyal customers
order_counts = df_orders.groupby('CustomerID')['OrderID'].count()
top_customers = order_counts.sort_values(ascending=False).head(5)
print('Top 5 most loyal customers (by number of orders):')
print(top_customers)

# Intermediate Example 1: Weekly order trends
df_orders['Week'] = df_orders['OrderDate'].dt.isocalendar().week
weekly_orders = df_orders.groupby('Week')['OrderID'].count()
print('Orders per week:')
print(weekly_orders.head())

# Intermediate Example 2: Merge orders with product catalog for category analysis
df_merged = pd.merge(df_orders, df_products, how='left', left_on='ProductID', right_on='ProductID')
cat_sales = df_merged.groupby('Category')['Quantity'].sum().sort_values(ascending=False)
print('Total quantity sold by category:')
print(cat_sales)

# Intermediate Example 3: Average order value (AOV) calculation
df_merged['OrderValue'] = df_merged['Quantity'] * df_merged['Price']
aov = df_merged.groupby('OrderID')['OrderValue'].sum().mean()
print('Average Order Value (AOV):', round(aov, 2))

# Advanced Example 1: Identify returning vs. one-time customers
customer_orders_count = df_orders.groupby('CustomerID')['OrderID'].count()
returning = (customer_orders_count > 1).sum()
one_time = (customer_orders_count == 1).sum()
print('Returning customers:', returning)
print('One-time customers:', one_time)

# Advanced Example 2: Quick cohort analysis by customer first purchase week
df_orders['FirstOrderWeek'] = df_orders.groupby('CustomerID')['OrderDate'].transform('min').dt.isocalendar().week
cohort_sizes = df_orders.groupby('FirstOrderWeek')['CustomerID'].nunique()
print('Cohort sizes by first purchase week:')
print(cohort_sizes.head())

# Advanced Example 3: Find peak sales hour
df_orders['Hour'] = df_orders['OrderDate'].dt.hour
hourly_sales = df_orders.groupby('Hour')['Quantity'].sum()
peak_hour = hourly_sales.idxmax()
print('Peak hour for sales:', peak_hour)

# Error Example 1: Handling missing or null values in customer ID
missing_customers = df_transactions['Customer ID'].isnull().sum()
print('Number of transactions missing customer ID:', missing_customers)

# Error Example 2: Incorrect revenue by grouping only on product
incorrect_revenue = df_merged.groupby('ProductID')['OrderValue'].sum()
print('Total Revenue by Product:')
print(incorrect_revenue.head(3))

# Error Example 3: Common bug - using sum instead of mean for order value
wrong_aov = df_merged['OrderValue'].sum()
print('Wrong AOV (sum, not mean):', wrong_aov)

# Market basket pattern: Find most common product pairs in baskets
market_products = ['Bread','Milk','Butter','Eggs','Apples','Chicken','Rice','Cheese']
transaction_ids = np.repeat(np.arange(1,301),3)
product_choices = np.random.choice(market_products, len(transaction_ids))
df_basket = pd.DataFrame({'TransactionID':transaction_ids,'Product':product_choices})
basket_pairs = (df_basket.groupby('TransactionID')['Product'].apply(lambda x: tuple(sorted(x)))
                .value_counts().head(5))
print('Most common product combinations (top 5):')
print(basket_pairs)

# Product performance pattern: Top 3 categories by total revenue
df_merged['Revenue'] = df_merged['Quantity'] * df_merged['Price']
category_revenue = df_merged.groupby('Category')['Revenue'].sum().sort_values(ascending=False)
print('Top 3 categories by revenue:')
print(category_revenue.head(3))

# Simple demand forecasting: Monthly sales trend for top category
top_cat = category_revenue.idxmax()
cat_df = df_merged[df_merged['Category'] == top_cat]
cat_df['Month'] = cat_df['OrderDate'].dt.to_period('M')
monthly_sales = cat_df.groupby('Month')['Revenue'].sum()
print(f'Monthly revenue trend for top category ({top_cat}):')
print(monthly_sales)

# END-TO-END PROBLEM: Identify top 5 high value customers
df_merged['CustomerRevenue'] = df_merged.groupby('CustomerID')['OrderValue'].transform('sum')
top5_customers = df_merged[['CustomerID','CustomerRevenue']].drop_duplicates().sort_values('CustomerRevenue',ascending=False).head(5)
print('Top 5 high value customers:')
print(top5_customers)