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

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

# Simulate a retail product catalog
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_catalog = pd.DataFrame({'ProductID':product_ids,'Category':product_categories,'Price':product_prices})
print(df_catalog.shape)
print(df_catalog.head(3))

total_quantity = df_retail['Quantity'].sum()
print('Total items sold:', total_quantity)

df_retail['Revenue'] = df_retail['Quantity'] * df_retail['Price']
total_revenue = df_retail['Revenue'].sum()
print('Total revenue generated:', round(total_revenue, 2))

top_products = df_retail.groupby('Description')['Quantity'].sum().sort_values(ascending=False).head(5)
print('Top 5 selling products by quantity:')
print(top_products)

customer_purchase_counts = df_retail['Customer ID'].value_counts().head(5)
print('Top 5 customers by number of purchases:')
print(customer_purchase_counts)

# Simulate joining transactions and catalog by ProductID
np.random.seed(42)
product_map = dict(zip(df_catalog['ProductID'], df_catalog['Category']))
retail_sample = df_retail.copy()
retail_sample['ProductID'] = np.random.choice(df_catalog['ProductID'], len(retail_sample))
retail_sample['Category'] = retail_sample['ProductID'].map(product_map)
category_sales = retail_sample.groupby('Category')['Revenue'].sum().sort_values(ascending=False)
print('Revenue by product category:')
print(category_sales)

order_values = df_retail.groupby('Invoice')['Revenue'].sum()
aov = order_values.mean()
print('Average order value:', round(aov, 2))

df_retail['Month'] = df_retail['InvoiceDate'].dt.to_period('M')
monthly_revenue = df_retail.groupby('Month')['Revenue'].sum()
print('Monthly revenue:')
print(monthly_revenue.head())

customer_revenue = df_retail.groupby('Customer ID')['Revenue'].sum().sort_values(ascending=False)
top_customers = customer_revenue.head(5)
print('Top 5 customers by total revenue:')
print(top_customers)

# Simulate a market basket transactions dataset
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})
basket_counts = basket_df.groupby(['TransactionID','Product']).size().unstack(fill_value=0)
co_occurrence = (basket_counts.T @ basket_counts)
np.fill_diagonal(co_occurrence.values, 0)
print('Product co-occurrence matrix (first 5 products):')
print(co_occurrence.iloc[:5,:5])

missing_qty = df_retail['Quantity'].isnull().sum()
missing_cust = df_retail['Customer ID'].isnull().sum()
print('Missing quantities:', missing_qty)
print('Missing customer IDs:', missing_cust)
# Clean sample: remove missing customer ID rows
df_clean = df_retail.dropna(subset=['Customer ID'])
print('After cleaning, rows:', df_clean.shape[0])

# Incorrect aggregation: sum of prices only
incorrect_total = df_retail['Price'].sum()
# Correct aggregation: sum of revenues
correct_total = df_retail['Revenue'].sum()
print('Incorrect: sum of prices =', incorrect_total)
print('Correct: sum of revenue =', correct_total)

# Mistake: grouping by ProductID only (simulated) for category analysis
bad_category_group = retail_sample.groupby('ProductID')['Revenue'].sum()
# Better: group by Category
good_category_group = retail_sample.groupby('Category')['Revenue'].sum()
print('By ProductID only (sample):', bad_category_group.head())
print('By Category (top):', good_category_group.sort_values(ascending=False).head())

monthly_growth = monthly_revenue.pct_change().dropna()*100
print('Monthly revenue growth rate (%):')
print(monthly_growth.round(1).head())

# Calculate average revenue by month of year
df_retail['MonthNum'] = df_retail['InvoiceDate'].dt.month
avg_by_month = df_retail.groupby('MonthNum')['Revenue'].mean()
print('Average revenue per month of year:')
print(avg_by_month.round(2))

# Identify last quarter's timeframe
latest_date = df_retail['InvoiceDate'].max()
quarter = (df_retail['InvoiceDate'] > (latest_date - pd.Timedelta(days=90)))
df_q = df_retail[quarter]
# Top products by revenue
prod_q = df_q.groupby('Description')['Revenue'].sum().sort_values(ascending=False).head(5)
print('Top 5 products last quarter by revenue:')
print(prod_q)
# Top customers by revenue
cust_q = df_q.groupby('Customer ID')['Revenue'].sum().sort_values(ascending=False).head(5)
print('Top 5 customers last quarter by revenue:')
print(cust_q)