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

# Loading Retail Transactions: Online Retail Dataset (UCI)
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))

# Create a product catalog from the transaction data
products = df[['StockCode','Description','Price']].drop_duplicates()
products = products.rename(columns={'StockCode':'ProductID'})
print(products.shape)
print(products.head(3))

# Add a Revenue column to the transactions data
df['Revenue'] = df['Quantity'] * df['Price']
print(df[['StockCode','Quantity','Price','Revenue']].head(3))

# Beginner: Total revenue for the entire store
total_revenue = df['Revenue'].sum()
print('Total revenue:', total_revenue)

# Beginner: Find and display total product sales volume
product_sales = df.groupby('StockCode')['Quantity'].sum().reset_index()
print(product_sales.head(3))

# Beginner: Top 5 revenue-generating products
top_products = df.groupby(['StockCode', 'Description'])['Revenue'].sum().sort_values(ascending=False).head(5)
print(top_products)

# Beginner: Find number of unique products sold
num_products_sold = df['StockCode'].nunique()
print('Unique products sold:', num_products_sold)

# Intermediate: Top 5 products by average price (of products sold)
product_avg_prices = df.groupby(['StockCode','Description'])['Price'].mean().sort_values(ascending=False).head(5)
print(product_avg_prices)

# Intermediate: Monthly product sales trend for a top seller
top_stock = top_products.index[0][0]
df['Month'] = df['InvoiceDate'].dt.to_period('M')
monthly_trend = df[df['StockCode'] == top_stock].groupby('Month')['Revenue'].sum()
print(monthly_trend)

# Intermediate: Aggregate revenue by country (market segmentation)
country_revenue = df.groupby('Country')['Revenue'].sum().sort_values(ascending=False)
print(country_revenue.head(5))

# Intermediate: Average order value (AOV) calculation
invoice_totals = df.groupby('Invoice')['Revenue'].sum()
aov = invoice_totals.mean()
print('Average Order Value (AOV):', round(aov,2))

# Intermediate: Top 5 products by number of orders
orders_per_product = df.groupby('StockCode')['Invoice'].nunique().sort_values(ascending=False).head(5)
print(orders_per_product)

# Advanced: Product revenue share across portfolio
total_revenue = df['Revenue'].sum()
product_revenue_share = df.groupby(['StockCode','Description'])['Revenue'].sum() / total_revenue
product_revenue_share = product_revenue_share.sort_values(ascending=False).head(5)
print(product_revenue_share)

# Advanced: Top 3 high revenue products by country
country_top3 = (df.groupby(['Country','StockCode','Description'])['Revenue'].sum()
                  .reset_index()
                  .sort_values(['Country','Revenue'], ascending=[True,False]))
country_top3 = country_top3.groupby('Country').head(3)
print(country_top3.head(9))

# Advanced: Cumulative revenue percent (Pareto/80-20 analysis)
product_rev = df.groupby('StockCode')['Revenue'].sum().sort_values(ascending=False)
cum_rev_pct = product_rev.cumsum() / product_rev.sum()
top20_pct = (cum_rev_pct <= 0.8).sum() / len(product_rev)
print('Share of products responsible for 80% of revenue:', round(top20_pct*100,2), '%')

# Advanced: Detect products with no sales (inactive inventory)
all_products = products['ProductID'].unique()
sold_products = df['StockCode'].unique()
unsold = set(all_products) - set(sold_products)
print('Number of products never sold:', len(unsold))

# Error handling: Check for missing values in key columns
missing = df[['StockCode','Quantity','Price','Revenue']].isnull().sum()
print('Missing values per column:')
print(missing)

# Error correction: Remove transactions with negative quantities (usually returns or data errors)
df_clean = df[df['Quantity'] > 0]
print('Original rows:', len(df), ' | Cleaned rows:', len(df_clean))

# Error example: Incorrect aggregation (summing prices instead of revenue)
wrong_total = df['Price'].sum()
real_total = df['Revenue'].sum()
print('Incorrect total (price sum):', wrong_total)
print('Correct total (revenue sum):', real_total)

# Pattern: Product performance ranking for executive reports
product_ranking = df.groupby(['StockCode','Description'])['Revenue'].sum().sort_values(ascending=False).reset_index()
product_ranking['Rank'] = range(1, len(product_ranking)+1)
print(product_ranking[['StockCode','Description','Revenue','Rank']].head(10))

# Pattern: Compare top versus bottom performing products
top = product_ranking.head(3)
bottom = product_ranking.tail(3)
print('Top performers:')
print(top[['StockCode','Description','Revenue']])
print(' ')
print('Low performers:')
print(bottom[['StockCode','Description','Revenue']])

# End-to-end: Identify top 2 bestsellers and recommend next steps
best_two = product_ranking.head(2)
product_names = ', '.join(best_two['Description'].tolist())
print('Top 2 Bestselling Products: ', product_names)
print('\nRecommendation: Focus marketing, stock, and promotions on these items to maximize revenue impact.')