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

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

# Beginner Example 2: Calculate Total Revenue for All Transactions
df['Revenue'] = df['Quantity'] * df['Price']
total_revenue = df['Revenue'].sum()
print(f'Total Revenue: {total_revenue:,.2f}')

# Beginner Example 3: Average Product Price Calculation
avg_price = df['Price'].mean()
print(f'Average Product Price: {avg_price:.2f}')

# Beginner Example 4: Find Total Quantity Sold
total_qty = df['Quantity'].sum()
print('Total Quantity Sold:', int(total_qty))

# Beginner Example 5: Basic Numpy Operations - Distribution of Item Quantities
import matplotlib.pyplot as plt
item_counts = np.array(df['Quantity'])
plt.hist(item_counts, bins=20, edgecolor='gray')
plt.title('Distribution of Item Quantities Purchased')
plt.xlabel('Quantity')
plt.ylabel('Frequency')
plt.show()

# Intermediate Example 1: Product-Level Revenue Aggregation
prod_revenue = df.groupby('StockCode')['Revenue'].sum().sort_values(ascending=False)
print(prod_revenue.head(5))

# Intermediate Example 2: Identify Top-Selling Countries
country_revenue = df.groupby('Country')['Revenue'].sum().sort_values(ascending=False)
print(country_revenue.head(5))

# Intermediate Example 3: Time-Based Revenue Analysis (Monthly)
df['YearMonth'] = df['InvoiceDate'].dt.to_period('M').astype(str)
monthly_revenue = df.groupby('YearMonth')['Revenue'].sum()
print(monthly_revenue)

# Intermediate Example 4: Using NumPy for Fast Filtering - High Value Orders
high_value = df['Revenue'].values > 1000
high_value_orders = df[high_value]
print(high_value_orders[['Invoice', 'Revenue']].head())

# Intermediate Example 5: Calculate Average Order Value (AOV)
order_revenue = df.groupby('Invoice')['Revenue'].sum()
avg_order_value = order_revenue.mean()
print(f'Average Order Value (AOV): {avg_order_value:.2f}')

# Intermediate Example 6: Retail Product Catalog Setup for Cross-Analysis
categories = ['Electronics', 'Clothing', 'Home', 'Sports', 'Beauty']
product_ids = list(range(1001,1101))
np.random.seed(42)
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})
print(catalog_df.head(3))

# Advanced Example 1: Merge Transactions with the Product Catalog
df['ProductID'] = pd.to_numeric(df['StockCode'], errors='coerce')
merged_df = pd.merge(df, catalog_df, on='ProductID', how='inner')
print(merged_df.head(3))

# Advanced Example 2: Calculate Revenue by Product Category
category_revenue = merged_df.groupby('Category')['Revenue'].sum().sort_values(ascending=False)
print(category_revenue)

# Advanced Example 3: Calculate Customer Lifetime Value (CLV) Using NumPy
customer_revenue = df.groupby('Customer ID')['Revenue'].sum().fillna(0).values
mean_clv = np.mean(customer_revenue)
print(f'Average Customer Lifetime Value: {mean_clv:.2f}')

# Advanced Example 4: Detect Monthly Revenue Growth Rate (NumPy pct_change)
monthly_vals = monthly_revenue.values
growth_rates = np.diff(monthly_vals) / monthly_vals[:-1]
print('Monthly Growth Rates:', np.round(growth_rates*100,2))

# Advanced Example 5: Outlier Detection in Revenue
revenue_arr = df['Revenue'].values
z_scores = (revenue_arr - np.mean(revenue_arr)) / np.std(revenue_arr)
outliers = np.where(np.abs(z_scores) > 3)[0]
print(f'Number of Revenue Outliers: {len(outliers)}')

# Error Handling Example 1: Handling Missing Values in Key Columns
missing_qty = df['Quantity'].isna().sum()
missing_price = df['Price'].isna().sum()
print(f'Missing Quantity: {missing_qty}, Missing Price: {missing_price}')

# Error Handling Example 2: Removing Negative or Zero Quantities
df_clean = df[df['Quantity'] > 0].copy()
print('Transactions after cleaning:', df_clean.shape[0])

# Error Handling Example 3: Prevent Incorrect Aggregation by Product
invalid_grouping = df.groupby('Invoice')['Price'].sum().head()
print('Sum of Price by Invoice (incorrect):')
print(invalid_grouping)

# Error Handling Example 4: Prevent Double Counting in Category Grouping
dup_count = merged_df.duplicated(subset=['Invoice', 'ProductID']).sum()
print('Potential Double-Counted Product/Invoice pairs:', dup_count)

# Best Practice Example 1: Customer Segmentation by Total Spend
customer_spend = df.groupby('Customer ID')['Revenue'].sum()
spend_segments = pd.qcut(customer_spend, q=4, labels=['Low', 'Medium', 'High', 'Top'])
segmented = pd.DataFrame({'CustomerID': customer_spend.index, 'Segment': spend_segments})
print(segmented.value_counts('Segment'))

# Best Practice Example 2: Analyze Top Performing Products
top5_products = prod_revenue.head(5)
print('Top 5 Products by Revenue:')
print(top5_products)

# Best Practice Example 3: Simple Market Basket Analysis - Unique Products per Order
unique_products_per_order = df.groupby('Invoice')['StockCode'].nunique()
print('Average unique products per order:', unique_products_per_order.mean())

# Best Practice Example 4: Detecting Monthly or Quarterly Revenue Trends
quarter_revenue = df.groupby(df['InvoiceDate'].dt.to_period('Q'))['Revenue'].sum()
print(quarter_revenue)

# End-to-End Retail Analytics: Identify Top-Selling Product and Recommend Action
top_stockcode = prod_revenue.idxmax()
top_product_desc = df[df['StockCode'] == top_stockcode]['Description'].mode()[0]
print(f'Top-selling product is {top_product_desc} (code: {top_stockcode}).')
print('Recommendation: Increase stock or highlight this product in store promotions.')