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

# Beginner Example 1: Load retail transactions 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(df.shape)
print(df.head(3))

# Beginner Example 2: Preview unique products sold
unique_products = df['Description'].nunique()
print('Unique products sold:', unique_products)

# Beginner Example 3: Calculate basic revenue column
df['Revenue'] = df['Quantity'] * df['Price']
print(df[['Quantity', 'Price', 'Revenue']].head(5))

# Intermediate Example 1: Total revenue per country
revenue_by_country = df.groupby('Country')['Revenue'].sum().sort_values(ascending=False)
print(revenue_by_country.head(5))

# Intermediate Example 2: Average order size
avg_order_size = df.groupby('Invoice')['Quantity'].sum().mean()
print('Average number of items per order:', round(avg_order_size, 2))

# Intermediate Example 3: Find top customers by spending
top_customers = df.groupby('Customer ID')['Revenue'].sum().sort_values(ascending=False).head(5)
print(top_customers)

# Intermediate Example 4: Sales trend over time (monthly)
df['InvoiceMonth'] = df['InvoiceDate'].dt.to_period('M')
monthly_revenue = df.groupby('InvoiceMonth')['Revenue'].sum()
print(monthly_revenue.head(12))

# Intermediate Example 5: Most popular products (by volume)
product_sales = df.groupby('Description')['Quantity'].sum().sort_values(ascending=False).head(5)
print(product_sales)

# Advanced Example 1: Combine transactions with customer order 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')
orders = pd.DataFrame({'OrderID':order_ids,'CustomerID':customer_ids,'ProductID':product_ids,'Quantity':quantities,'OrderDate':order_dates})
print('Orders loaded:', orders.shape)

# Advanced Example 2: Create a product catalog and join with orders
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 = pd.DataFrame({'ProductID':product_ids,'Category':product_categories,'Price':product_prices})
orders_catalog = pd.merge(orders, catalog, on='ProductID', how='left')
print(orders_catalog.head(3))

# Advanced Example 3: Calculate average order value per customer
orders_catalog['OrderValue'] = orders_catalog['Quantity'] * orders_catalog['Price']
avg_value_per_customer = orders_catalog.groupby('CustomerID')['OrderValue'].mean().sort_values(ascending=False)
print(avg_value_per_customer.head(5))

# Advanced Example 4: Category revenue contribution
category_revenue = orders_catalog.groupby('Category')['OrderValue'].sum().sort_values(ascending=False)
print(category_revenue)

# Advanced Example 5: Market Basket - most common co-occurring products
products = ['Bread','Milk','Butter','Eggs','Apples','Chicken','Rice','Cheese']
transaction_ids = np.repeat(np.arange(1,301),3)
product_choices = np.random.choice(products,len(transaction_ids))
basket_df = pd.DataFrame({'TransactionID':transaction_ids,'Product':product_choices})
freq = basket_df.groupby(['TransactionID', 'Product']).size().reset_index(name='Count')
cooccurrence = freq.pivot_table(index='TransactionID', columns='Product', values='Count', fill_value=0)
from itertools import combinations
from collections import Counter
pairs = []
for row in cooccurrence.iterrows():
    items = row[1][row[1] > 0].index.tolist()
    pairs.extend(combinations(sorted(items), 2))
pair_counts = Counter(pairs)
print(pair_counts.most_common(5))

# Error Handling Example 1: Check for missing prices
missing_price = df['Price'].isnull().sum()
print('Missing price entries:', missing_price)

# Error Handling Example 2: Remove transactions with missing Customer ID
original_rows = df.shape[0]
df_clean = df.dropna(subset=['Customer ID'])
print('Removed', original_rows - df_clean.shape[0], 'transactions with missing Customer ID')

# Error Handling Example 3: Catching grouping errors
try:
    wrong_agg = df.groupby('Description')['Revenue'].mean()[0]
except Exception as e:
    print('Example grouping error:', str(e))

# End-to-End Example: Identify top-selling products and provide a recommendation
top_products = df.groupby('Description')['Quantity'].sum().sort_values(ascending=False).head(3)
print('Top selling products by volume:')
print(top_products)
recommendation = f"Consider increasing inventory and marketing for '{top_products.index[0]}' to capture more sales, since it leads in units sold."
print('Business recommendation:', recommendation)