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

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

# Beginner Example 2: Basic overview of columns
print('Columns:', df.columns.tolist())
print('Sample values for Invoice, Quantity, and Price:')
print(df[['Invoice', 'Quantity', 'Price']].head())

# Beginner Example 3: Calculate total sales per transaction line
df['LineTotal'] = df['Quantity'] * df['Price']
print(df[['Invoice', 'StockCode', 'Quantity', 'Price', 'LineTotal']].head())

# Intermediate Example 1: Aggregate total sales revenue
total_sales = df['LineTotal'].sum()
print('Total sales revenue in dataset:', round(total_sales, 2))

# Intermediate Example 2: Sales per product
product_sales = df.groupby('Description')['LineTotal'].sum().sort_values(ascending=False)
print('Top 5 best-selling products by revenue:')
print(product_sales.head())

# Intermediate Example 3: Time-based sales analysis (monthly)
df['InvoiceMonth'] = df['InvoiceDate'].dt.to_period('M')
monthly_sales = df.groupby('InvoiceMonth')['LineTotal'].sum()
print('Sales by month:')
print(monthly_sales.head())

# Intermediate Example 4: Filter out cancelled transactions (Credit Notes start with 'C')
original_rows = df.shape[0]
df_clean = df[~df['Invoice'].astype(str).str.startswith('C')]
print('Removed', original_rows - df_clean.shape[0], 'cancelled transaction lines.')

# Intermediate Example 5: Re-calculate total sales after removing cancellations
final_sales = df_clean['LineTotal'].sum()
print('Total sales after removing cancellations:', round(final_sales, 2))

# Intermediate Example 6: Average order value (AOV)
aov = df_clean.groupby('Invoice')['LineTotal'].sum().mean()
print('Average Order Value (AOV):', round(aov, 2))

# Advanced Example 1: Identify high-value customers
customer_sales = df_clean.groupby('Customer ID')['LineTotal'].sum().sort_values(ascending=False)
print('Top 5 customers by total spend:')
print(customer_sales.head())

# Advanced Example 2: Sales per country
country_sales = df_clean.groupby('Country')['LineTotal'].sum().sort_values(ascending=False)
print('Top 5 countries by sales:')
print(country_sales.head())

# Advanced Example 3: Forecasting next month's sales (naive method)
last_month = df_clean['InvoiceDate'].dt.to_period('M').max()
prev_month = last_month - 1
current_month_sales = monthly_sales.get(str(prev_month), 0)
print(f'Forecast for {last_month}:', round(current_month_sales, 2))

# Advanced Example 4: Detecting seasonal patterns (yearly aggregation)
df_clean['Year'] = df_clean['InvoiceDate'].dt.year
yearly_sales = df_clean.groupby('Year')['LineTotal'].sum()
print('Yearly sales totals:')
print(yearly_sales)

# Error Handling 1: Find missing or null values in key columns
missing = df_clean[['Invoice', 'Quantity', 'Price', 'Customer ID']].isnull().sum()
print('Missing values in key columns:')
print(missing)

# Error Handling 2: Check for negative values in Quantity or Price
neg_qty = (df_clean['Quantity'] < 0).sum()
neg_price = (df_clean['Price'] < 0).sum()
print('Lines with negative quantity:', neg_qty)
print('Lines with negative price:', neg_price)

# Error Handling 3: Check grouping correctness by product and date
group_check = df_clean.groupby(['Description', 'InvoiceMonth'])['LineTotal'].sum().reset_index()
print(group_check.head())

# Best Practice Example: Segment top 10% of customers by spend
threshold = customer_sales.quantile(0.9)
top_customers = customer_sales[customer_sales >= threshold]
print('Number of top 10% customers:', len(top_customers))
print('Sample top customers:')
print(top_customers.head())

# Best Practice Example: Identify top product categories by sales value
category_sales = df_clean.groupby('StockCode')['LineTotal'].sum().sort_values(ascending=False)
print('Top product SKUs by sales:')
print(category_sales.head())

# Best Practice Example: Simple demand forecasting using recent trends
recent_months = monthly_sales.tail(3)
forecast_next = recent_months.mean()
print('Simple demand forecast for next month (last 3-month avg):', round(forecast_next, 2))

# Mini Case Study: End-to-end  Find top-selling product and country this quarter
this_quarter = df_clean[df_clean['InvoiceDate'].dt.to_period('Q') == df_clean['InvoiceDate'].dt.to_period('Q').max()]
top_product = this_quarter.groupby('Description')['LineTotal'].sum().sort_values(ascending=False).head(1)
top_country = this_quarter.groupby('Country')['LineTotal'].sum().sort_values(ascending=False).head(1)
print('Top-selling product this quarter:')
print(top_product)
print('Top country by sales this quarter:')
print(top_country)