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

# Example: Loading an online retail transactions dataset
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))

print('Columns:', list(df_transactions.columns))

# Beginner Example 2: Counting Transactions per Country
trans_per_country = df_transactions['Country'].value_counts().head(5)
print(trans_per_country)

# Beginner Example 3: Simple Sales Revenue Calculation
df_transactions['Revenue'] = df_transactions['Quantity'] * df_transactions['Price']
total_revenue = df_transactions['Revenue'].sum()
print('Total Revenue:', round(total_revenue,2))

# Intermediate Example 1: Analyze Unique Customers
n_customers = df_transactions['Customer ID'].nunique()
print('Unique Customers:', n_customers)

# Intermediate Example 2: Top Selling Products by Quantity
top_products = df_transactions.groupby('Description')['Quantity'].sum().sort_values(ascending=False).head(5)
print(top_products)

# Intermediate Example 3: Average Order Value
avg_order_value = df_transactions.groupby('Invoice')['Revenue'].sum().mean()
print('Average Order Value:', round(avg_order_value,2))

# Intermediate Example 4: Sales Over Time - Monthly Trend
monthly_sales = df_transactions.resample('M', on='InvoiceDate')['Revenue'].sum()
print(monthly_sales.head())

# Intermediate Example 5: Customers with Most Transactions
top_customers = df_transactions['Customer ID'].value_counts().head(5)
print(top_customers)

# Advanced Example 1: Market Basket Data Setup
products = ['Bread','Milk','Butter','Eggs','Apples','Chicken','Rice','Cheese']
transaction_ids = np.repeat(np.arange(1,301),3)
np.random.seed(42)
product_choices = np.random.choice(products,len(transaction_ids))
df_basket = pd.DataFrame({'TransactionID':transaction_ids,'Product':product_choices})
print(df_basket.head(3))

# Advanced Example 2: Counting Basket Product Frequencies
basket_counts = df_basket['Product'].value_counts()
print(basket_counts)

# Advanced Example 3: Cross-Sell Product Pairs
cross_sell = df_basket.groupby('TransactionID')['Product'].apply(list)
from itertools import combinations
from collections import Counter
pair_counts = Counter()
for basket in cross_sell:
    for pair in combinations(sorted(basket), 2):
        pair_counts[pair] += 1
top_pairs = pair_counts.most_common(5)
print(top_pairs)

# Advanced Example 4: Setting Up a Product Catalog
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)
df_catalog = pd.DataFrame({'ProductID':product_ids,'Category':product_categories,'Price':product_prices})
print(df_catalog.head(3))

# Advanced Example 5: Category Revenue Calculation
merged = pd.merge(df_transactions, df_catalog, left_on='StockCode', right_on='ProductID', how='inner')
category_revenue = merged.groupby('Category')['Revenue'].sum().sort_values(ascending=False)
print(category_revenue)

# Advanced Example 6: Simulated 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 = 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,'Quantity':quantities,'OrderDate':order_dates})
print(df_orders.head(3))

# Error Handling Example 1: Checking for Missing Values
missing_counts = df_transactions.isnull().sum()
print(missing_counts[missing_counts > 0])

# Error Handling Example 2: Catching Incorrect Aggregations
incorrect_sum = df_transactions['Quantity'].sum()
print('Sum of Quantities:', incorrect_sum)
correct_sum = df_transactions[df_transactions['Quantity'] > 0]['Quantity'].sum()
print('Correct (Excluding Returns):', correct_sum)

# Error Handling Example 3: Mistaken Grouping by Product
wrong_group = df_transactions.groupby('StockCode')['Revenue'].sum().head(3)
correct_group = df_transactions.groupby('Description')['Revenue'].sum().head(3)
print('By StockCode:', wrong_group)
print('By Description:', correct_group)

# Error Handling Example 4: Misinterpreting Revenue Calculation
df_transactions['FakeRevenue'] = df_transactions['Price']
fake_sum = df_transactions['FakeRevenue'].sum()
true_sum = df_transactions['Revenue'].sum()
print('Fake Revenue:', fake_sum)
print('True Revenue:', true_sum)

# Pattern: High-Value Customer Segmentation
customer_rev = df_transactions.groupby('Customer ID')['Revenue'].sum().sort_values(ascending=False)
vip_customers = customer_rev[customer_rev > customer_rev.quantile(0.95)]
print(vip_customers)

# Pattern: Product Performance by Category
category_perf = merged.groupby('Category').agg({'Revenue': 'sum', 'Quantity':'sum'})
print(category_perf)

# Pattern: Demand Forecasting using Recent Sales
recent_sales = df_transactions.set_index('InvoiceDate').last('30D')['Revenue'].resample('D').sum()
print(recent_sales.head())

top2 = df_transactions.groupby('Description')['Revenue'].sum().nlargest(2)
print('Recommended for promotion:')
print(top2)