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

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))

num_unique_products = retail_df['StockCode'].nunique()
print('Unique products sold:', num_unique_products)

num_transactions = retail_df['Invoice'].nunique()
print('Total unique transactions (invoices):', num_transactions)

retail_df['LineRevenue'] = retail_df['Quantity'] * retail_df['Price']
total_revenue = retail_df['LineRevenue'].sum()
print('Total revenue (GBP): {:.2f}'.format(total_revenue))

invoice_revenue = retail_df.groupby('Invoice')['LineRevenue'].sum()
avg_order_value = invoice_revenue.mean()
print('Average order value (GBP): {:.2f}'.format(avg_order_value))

product_sales = retail_df.groupby('Description')['Quantity'].sum().sort_values(ascending=False)
print('Top 5 Products by Units Sold:')
print(product_sales.head(5))

product_revenue = retail_df.groupby('Description')['LineRevenue'].sum().sort_values(ascending=False)
print('Top 5 Products by Revenue:')
print(product_revenue.head(5))

country_revenue = retail_df.groupby('Country')['LineRevenue'].sum().sort_values(ascending=False)
print('Top 5 Countries by Revenue:')
print(country_revenue.head(5))

retail_df['Month'] = retail_df['InvoiceDate'].dt.to_period('M')
monthly_revenue = retail_df.groupby('Month')['LineRevenue'].sum()
print(monthly_revenue.head())

items_per_invoice = retail_df.groupby('Invoice')['Quantity'].sum()
avg_items_transaction = items_per_invoice.mean()
print('Average items per transaction: {:.2f}'.format(avg_items_transaction))

missing_lines = retail_df[retail_df['Quantity'].isna() | retail_df['Price'].isna()]
print('Rows with missing Quantity or Price:', missing_lines.shape[0])

# Incorrect: Double counting by not grouping properly
incorrect_total = (retail_df.groupby('Invoice')['LineRevenue'].mean().sum())
print('Incorrect total if not grouping right: {:.2f}'.format(incorrect_total))
# Correct way for comparison
print('Correct total revenue (should match earlier): {:.2f}'.format(total_revenue))

customer_spend = retail_df.groupby('Customer ID')['LineRevenue'].sum().dropna()
high_value_customers = customer_spend[customer_spend > customer_spend.quantile(0.95)]
print('Top 5% of customers by spend:', high_value_customers.shape[0])

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})
print(catalog_df.head(3))

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})
print(basket_df.head(3))

result = retail_df.groupby('Description')['LineRevenue'].sum().sort_values(ascending=False).head(3)
print('Top 3 revenue-generating products:')
for i, (desc, rev) in enumerate(result.items(), 1):
    print(f'{i}. {desc}: GBP {rev:.2f}')