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

# Load retail demand data from UCI repository
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))

# Look for missing values in key columns
print(df[['InvoiceDate','Quantity', 'StockCode']].isnull().sum())

# Filter the data to one country (United Kingdom) for consistency
country_df = df[df['Country'] == 'United Kingdom'].copy()
print(country_df.shape)

# Extract date only for daily aggregation
country_df['InvoiceDay'] = country_df['InvoiceDate'].dt.date
print(country_df[['InvoiceDate','InvoiceDay']].head(3))

# Beginner example 1: Aggregate total demand per day across all products
daily_demand = country_df.groupby('InvoiceDay')['Quantity'].sum().reset_index()
print(daily_demand.head())

# Beginner example 2: Plot daily demand over time
plt.figure(figsize=(12,4))
plt.plot(daily_demand['InvoiceDay'], daily_demand['Quantity'])
plt.title('Total Daily Demand - UK Retailer')
plt.xlabel('Date')
plt.ylabel('Quantity Sold')
plt.tight_layout()
plt.show()

# Beginner example 3: What is the average demand per day?
avg_demand = daily_demand['Quantity'].mean()
print(f'Average daily demand: {avg_demand:.2f} units')

# Intermediate example 1: Aggregate demand by both date and product
daily_product_demand = country_df.groupby(['InvoiceDay','StockCode'])['Quantity'].sum().reset_index()
print(daily_product_demand.head())

# Intermediate example 2: Find top 5 selling products overall
top_products = country_df.groupby('StockCode')['Quantity'].sum().sort_values(ascending=False).head(5)
print(top_products)

# Intermediate example 3: Aggregate and plot monthly demand
country_df['InvoiceMonth'] = country_df['InvoiceDate'].dt.to_period('M').dt.to_timestamp()
monthly_demand = country_df.groupby('InvoiceMonth')['Quantity'].sum().reset_index()
plt.figure(figsize=(10,5))
plt.bar(monthly_demand['InvoiceMonth'].dt.strftime('%Y-%m'), monthly_demand['Quantity'])
plt.title('Total Monthly Demand - UK Retailer')
plt.xlabel('Month')
plt.ylabel('Quantity Sold')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

# Intermediate example 4: Find days with missing demand (zero-sales days)
all_days = pd.date_range(country_df['InvoiceDay'].min(), country_df['InvoiceDay'].max(), freq='D')
existing_days = set(daily_demand['InvoiceDay'])
missing_days = [day for day in all_days.date if day not in existing_days]
print(f'Missing demand days: {len(missing_days)}')
print(missing_days[:5])

# Intermediate example 5: Aggregate demand by customer type (wholesale vs retail)
if 'InvoiceNo' in country_df and country_df['InvoiceNo'].dtype == object:
    country_df['InvoiceType'] = country_df['InvoiceNo'].str.startswith('C').map(lambda x: 'Credit' if x else 'Normal')
    invtype_summary = country_df.groupby('InvoiceType')['Quantity'].sum()
    print(invtype_summary)

# Advanced example 1: Create a pivot table of daily demand by top 3 products
top3_codes = top_products.index[:3]
pivot_df = daily_product_demand[daily_product_demand['StockCode'].isin(top3_codes)]
pivot = pivot_df.pivot(index='InvoiceDay', columns='StockCode', values='Quantity').fillna(0)
print(pivot.head())

# Advanced example 2: Compute rolling average demand per product (7-day window)
pivot_rolling = pivot.rolling(7, min_periods=1).mean()
print(pivot_rolling.head(10))

# Advanced example 3: Aggregate demand by weekday to spot operational peaks
country_df['Weekday'] = country_df['InvoiceDate'].dt.day_name()
weekday_demand = country_df.groupby('Weekday')['Quantity'].sum().reindex([
    'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'
])
print(weekday_demand)

# Error handling 1: What if there are missing dates in your aggregation?
try:
    # Check for missing days and fill
    full_daily = daily_demand.set_index('InvoiceDay').reindex(all_days.date).fillna(0)
    print(full_daily.head())
except Exception as e:
    print(f'Error encountered: {e}')

# Error handling 2: What if quantities are negative (returns)?
neg_qty = country_df[country_df['Quantity'] < 0]
print(f'Number of negative demand rows: {neg_qty.shape[0]}')
print(neg_qty[['InvoiceDate','StockCode','Quantity']].head())

# Error handling 3: What if two aggregations produce mismatched results?
daily_sum = country_df.groupby('InvoiceDay')['Quantity'].sum()
daily_mean = country_df.groupby('InvoiceDay')['Quantity'].mean()
if (daily_mean.index != daily_sum.index).any():
    print('Date mismatch between aggregations!')
else:
    print('Aggregation dates match.')

# Pattern: Get monthly, weekly, and daily demand in a single dataframe
kpi_df = country_df[['InvoiceDate','Quantity']].copy()
kpi_df['Day'] = kpi_df['InvoiceDate'].dt.date
kpi_df['Week'] = kpi_df['InvoiceDate'].dt.to_period('W').dt.start_time
kpi_df['Month'] = kpi_df['InvoiceDate'].dt.to_period('M').dt.to_timestamp()
agg_day = kpi_df.groupby('Day')['Quantity'].sum().rename('day_qty')
agg_week = kpi_df.groupby('Week')['Quantity'].sum().rename('week_qty')
agg_month = kpi_df.groupby('Month')['Quantity'].sum().rename('month_qty')
print(agg_month.head())

# Reload and clean data for a repeatable process
df = pd.read_excel(url, sheet_name='Year 2010-2011')
df = df[df['Country'] == 'United Kingdom']
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
df = df[df['Quantity'] > 0]
df['InvoiceDay'] = df['InvoiceDate'].dt.date
print(f'Post-cleaning rows: {df.shape[0]}')

# Aggregate and plot monthly demand after cleaning
monthly_demand = df.groupby(df['InvoiceDate'].dt.to_period('M').dt.to_timestamp())['Quantity'].sum()
monthly_demand.plot(kind='bar', figsize=(10,5))
plt.title('Monthly Demand After Cleaning')
plt.ylabel('Units Sold')
plt.xlabel('Month')
plt.tight_layout()
plt.show()