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

# Load the Online Retail Transactions dataset (UCI)
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[['Invoice', 'StockCode', 'Description', 'Quantity', 'InvoiceDate', 'Price', 'Customer ID', 'Country']].head(3))

# Preview unique countries to analyze regional breakdowns
countries = df_retail['Country'].unique()
print('Number of unique countries:', len(countries))
print('Sample countries:', countries[:7])

# Add a synthetic 'Channel' column to simulate channel data
np.random.seed(42)
possible_channels = ['Online', 'Offline']
df_retail['Channel'] = np.random.choice(possible_channels, len(df_retail))
print(df_retail[['Invoice', 'Country', 'Channel']].head(5))

# Calculate revenue per transaction row
df_retail['Revenue'] = df_retail['Quantity'] * df_retail['Price']
print(df_retail[['Quantity', 'Price', 'Revenue']].head(5))

# Beginner Example 1: Total sales revenue by channel
channel_sales = df_retail.groupby('Channel')['Revenue'].sum().sort_values(ascending=False)
print('Total revenue by sales channel:')
print(channel_sales)

# Beginner Example 2: Total sales revenue by region (country)
country_sales = df_retail.groupby('Country')['Revenue'].sum().sort_values(ascending=False)
print('Top 5 revenue-generating countries:')
print(country_sales.head(5))

# Beginner Example 3: Number of sales transactions per channel
transactions_per_channel = df_retail['Channel'].value_counts()
print('Number of sales transactions per channel:')
print(transactions_per_channel)

# Intermediate Example 1: Average revenue per transaction, by channel
avg_revenue_channel = df_retail.groupby('Channel')['Revenue'].mean()
print('Average revenue per transaction by channel:')
print(avg_revenue_channel)

# Intermediate Example 2: Channel and region: total revenue pivot table
channel_region_pivot = pd.pivot_table(df_retail, values='Revenue', index='Country', columns='Channel', aggfunc='sum', fill_value=0)
print('Revenue by country and channel:')
print(channel_region_pivot.head(8))

# Intermediate Example 3: Identify regions with over 10,000 total revenue in offline sales
offline_high = channel_region_pivot[channel_region_pivot['Offline'] > 10000]
print('Regions with over 10,000 in offline sales:')
print(offline_high.index.tolist())

# Intermediate Example 4: Monthly trend of online versus offline sales
df_retail['Month'] = df_retail['InvoiceDate'].dt.to_period('M')
trend = df_retail.groupby(['Month', 'Channel'])['Revenue'].sum().unstack()
print('Monthly revenue trend by channel:')
print(trend.tail(6))

# Advanced Example 1: Find the country-channel pairs with the highest and lowest average transaction value
mean_trans = df_retail.groupby(['Country','Channel'])['Revenue'].mean().reset_index()
max_pair = mean_trans.loc[mean_trans['Revenue'].idxmax()]
min_pair = mean_trans.loc[mean_trans['Revenue'].idxmin()]
print('Highest average transaction value:', max_pair.to_dict())
print('Lowest average transaction value:', min_pair.to_dict())

# Advanced Example 2: Proportion of online vs offline sales in the UK
uk = df_retail[df_retail['Country'] == 'United Kingdom']
uk_channel_counts = uk['Channel'].value_counts(normalize=True)
print('Share of online vs offline transactions in the UK:')
print(uk_channel_counts)

# Advanced Example 3: Calculate the coefficient of variation for revenue by channel and region
def coef_of_variation(x): return np.std(x) / np.mean(x) if np.mean(x) != 0 else np.nan
cv_table = df_retail.groupby(['Country','Channel'])['Revenue'].agg(coef_of_variation).unstack()
print('Coefficient of variation (CV) of revenue by region and channel:')
print(cv_table.head(8))

# Error Example 1: What happens if we group by the wrong field?
try:
    error_group = df_retail.groupby('Price')['Revenue'].sum()
    print(error_group.head())
except Exception as e:
    print('Error:', str(e))

# Error Example 2: Handling missing data in the 'Revenue' column
df_retail.loc[df_retail.sample(frac=0.01, random_state=42).index, 'Revenue'] = np.nan
missing_rev = df_retail['Revenue'].isna().sum()
print(f'Number of missing revenue values: {missing_rev}')
mean_rev = df_retail['Revenue'].mean(skipna=True)
print('Mean revenue after introducing missing values:', mean_rev)

# Error Example 3: Incorrect aggregationsumming Quantity and Price instead of Revenue
agg_wrong = df_retail.groupby('Country')[['Quantity', 'Price']].sum().head()
print('WRONG: Summing Quantity and Price directly (incorrect for revenue):')
print(agg_wrong)
agg_correct = df_retail.groupby('Country')['Revenue'].sum().head()
print('CORRECT: Summing Revenue for total sales:')
print(agg_correct)

# Best Practice 1: Use fillna() to handle missing revenue
df_retail['Revenue'] = df_retail['Revenue'].fillna(df_retail['Revenue'].median())
print('Number of missing values after filling:', df_retail['Revenue'].isna().sum())

# Best Practice 2: Segment your customers by purchase frequency
customer_counts = df_retail.groupby('Customer ID')['Invoice'].nunique().sort_values(ascending=False)
top_customers = customer_counts.head(5)
print('Top 5 customers by purchase frequency:')
print(top_customers)

# Best Practice 3: Product category performance by channel (requires more data)
df_product = pd.DataFrame({'ProductID': df_retail['StockCode'], 'Category': np.random.choice(['Electronics','Clothing','Home','Sports','Beauty'], len(df_retail)), 'Price': df_retail['Price']})
cat_perf = df_retail.copy()
cat_perf['Category'] = df_product['Category']
performance = cat_perf.groupby(['Category','Channel'])['Revenue'].sum().unstack()
print('Product category revenue by channel:')
print(performance)

# Analytical Pattern: Find periods of peak demand by country and channel
peak = df_retail.groupby(['Country','Channel'])['Revenue'].sum().reset_index()
peak_country = peak.loc[peak.groupby('Country')['Revenue'].idxmax()]
print('Channel with peak revenue for each country:')
print(peak_country[['Country','Channel','Revenue']].head(8))

# Analytical Pattern: Simple demand forecasting via recent monthly growth
trend_recent = trend.tail(3)
growth = (trend_recent.iloc[-1] - trend_recent.iloc[0]) / trend_recent.iloc[0]
print('Recent 3-month growth by channel:')
print(growth)

# End-to-End Problem: Which country and channel combination should we invest in next month?
recent_month = trend.index[-1]
recent_results = df_retail[df_retail['Month'] == recent_month]
pivot = pd.pivot_table(recent_results, values='Revenue', index='Country', columns='Channel', aggfunc='sum', fill_value=0)
pivot['Total'] = pivot.sum(axis=1)
top_region_channel = pivot[['Online','Offline']].stack().idxmax()
print('Recommend investing in:', top_region_channel)
print('Reason: This country-channel pair had the highest revenue last month.')