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'
df = pd.read_excel(url, sheet_name='Year 2010-2011')
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
print(df.shape)
print(df.head(3))

df['Revenue'] = df['Quantity'] * df['Price']
print(df[['Invoice', 'StockCode', 'Quantity', 'Price', 'Revenue']].head(3))

num_invoices = df['Invoice'].nunique()
total_revenue = df['Revenue'].sum()
print(f'Number of unique orders (invoices): {num_invoices}')
print(f'Total revenue: {total_revenue:.2f}')

invoice_totals = df.groupby('Invoice')['Revenue'].sum()
aov = invoice_totals.mean()
print(f'Average Order Value (AOV): {aov:.2f}')

country_invoice_totals = df.groupby(['Country', 'Invoice'])['Revenue'].sum().reset_index()
country_aov = country_invoice_totals.groupby('Country')['Revenue'].mean().sort_values(ascending=False)
print(country_aov.head(5))

high_value_orders = invoice_totals[invoice_totals > aov * 2]
print(f'Number of orders with high value: {high_value_orders.count()}')

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')
df_orders = pd.DataFrame({'OrderID':order_ids,'CustomerID':customer_ids,'ProductID':product_ids,'Quantity':quantities,'OrderDate':order_dates})
product_prices = np.round(np.random.uniform(5,500,100),2)
product_catalog = pd.DataFrame({'ProductID':range(1001,1101),'Price':product_prices})
df_orders = df_orders.merge(product_catalog, on='ProductID', how='left')
df_orders['Revenue'] = df_orders['Quantity'] * df_orders['Price']
print(df_orders.head(3))

synthetic_invoice_vals = df_orders.groupby('OrderID')['Revenue'].sum()
aov_syn = synthetic_invoice_vals.mean()
print(f'Synthetic dataset AOV: {aov_syn:.2f}')

orders_per_customer = df_orders.groupby('CustomerID')['OrderID'].nunique()
print(orders_per_customer.describe())

np.random.seed(42)
website_visits = 5000
orders = len(df_orders['OrderID'].unique())
conversion_rate = orders / website_visits
print(f'Estimated conversion rate: {conversion_rate:.2%}')

df_orders['Hour'] = df_orders['OrderDate'].dt.hour
hourly_aov = df_orders.groupby('Hour').apply(lambda x: x.groupby('OrderID')['Revenue'].sum().mean())
print(hourly_aov.head())

product_catalog['Category'] = np.random.choice(['Electronics','Clothing','Home','Sports','Beauty'], size=len(product_catalog), replace=True)
df_orders = df_orders.merge(product_catalog[['ProductID','Category']], on='ProductID', how='left')
cat_aov = df_orders.groupby('Category').apply(lambda x: x.groupby('OrderID')['Revenue'].sum().mean())
print(cat_aov.sort_values(ascending=False))

hours = df_orders['Hour'].unique()
np.random.seed(42)
visits_per_hour = dict(zip(hours, np.random.randint(150, 350, len(hours))))
orders_per_hour = df_orders.groupby('Hour')['OrderID'].nunique().to_dict()
hourly_conversion = {h: orders_per_hour.get(h,0)/visits_per_hour[h] for h in hours}
sorted_hourly = dict(sorted(hourly_conversion.items(), key=lambda x:x[1], reverse=True))
print('Hourly conversion rates:')
for h, rate in list(sorted_hourly.items())[:5]: print(f'Hour {h}: {rate:.2%}')

customer_totals = df_orders.groupby('CustomerID').agg({'OrderID':'nunique', 'Revenue':'sum'})
customer_totals['AOV'] = customer_totals['Revenue'] / customer_totals['OrderID']
segments = pd.qcut(customer_totals['AOV'], 4, labels=['Low','Medium','High','Very High'])
customer_totals['AOV_Segment'] = segments
print(customer_totals['AOV_Segment'].value_counts())

outliers = customer_totals[customer_totals['AOV'] > customer_totals['AOV'].mean() + 2 * customer_totals['AOV'].std()]
print(f'Number of AOV outlier customers: {outliers.shape[0]}')
print(outliers[['AOV']].head())

neg_orders = df[df['Revenue'] < 0]
print(f'Orders with negative revenue: {len(neg_orders)}')
df_clean = df[df['Revenue'] >= 0]

# Incorrect: Calculating mean revenue at product-row level instead of invoice
mean_row_val = df['Revenue'].mean()
print(f'Incorrect mean at product-row level: {mean_row_val:.2f}')
# Correct: Use invoice-level sum
correct_aov = df.groupby('Invoice')['Revenue'].sum().mean()
print(f'Correct AOV at order level: {correct_aov:.2f}')

df['Category'] = df['Description'].str.extract('(\w+)', expand=False).fillna('Unknown')
category_invoice_totals = df.groupby(['Category', 'Invoice'])['Revenue'].sum().reset_index()
category_aov = category_invoice_totals.groupby('Category')['Revenue'].mean().sort_values(ascending=False)
print(category_aov.head(5))

df['Day'] = df['InvoiceDate'].dt.date
daily_orders = df.groupby('Day')['Invoice'].nunique()
daily_revenue = df.groupby('Day')['Revenue'].sum()
daily_aov = daily_revenue / daily_orders
print(daily_aov.head())

customer_invoice_totals = df.groupby(['Customer ID', 'Invoice'])['Revenue'].sum().reset_index()
aov_per_customer = customer_invoice_totals.groupby('Customer ID')['Revenue'].mean().sort_values(ascending=False)
print('Top 5 customers by AOV:')
print(aov_per_customer.head(5))

top_orders = invoice_totals.sort_values(ascending=False).head(20).index
df_top_orders = df[df['Invoice'].isin(top_orders)]
product_counts = df_top_orders['Description'].value_counts().head(5)
print('Most common products in top 20 high-value orders:')
print(product_counts)