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

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

# Beginner Example 2: Check for missing values
missing_counts = df_retail.isnull().sum()
print('Missing values by column:')
print(missing_counts)

# Beginner Example 3: Count duplicated invoices and lines
dup_invoices = df_retail['Invoice'].duplicated().sum()
dup_fullrows = df_retail.duplicated().sum()
print('Duplicate invoices:', dup_invoices)
print('Fully duplicated rows:', dup_fullrows)

# Intermediate Example 1: Summarize null Customer IDs by Country
cust_missing_by_country = df_retail[df_retail['Customer ID'].isnull()]['Country'].value_counts()
print('Missing Customer IDs, by Country:')
print(cust_missing_by_country.head())

# Intermediate Example 2: Find transactions with negative or zero Quantity
invalid_qty = df_retail[df_retail['Quantity'] <= 0]
print('Transactions with invalid (negative or zero) quantity:')
print(invalid_qty[['Invoice', 'Description', 'Quantity']].head())

# Intermediate Example 3: Look for price outliers
Q1 = df_retail['Price'].quantile(0.25)
Q3 = df_retail['Price'].quantile(0.75)
IQR = Q3 - Q1
outlier_rows = df_retail[(df_retail['Price'] < (Q1 - 1.5 * IQR)) | (df_retail['Price'] > (Q3 + 1.5 * IQR))]
print('Prices below Q1-1.5*IQR or above Q3+1.5*IQR:')
print(outlier_rows[['StockCode', 'Description', 'Price']].head())

# Intermediate Example 4: Check unique StockCodes per product description
desc_code_counts = df_retail.groupby('Description')['StockCode'].nunique().sort_values(ascending=False)
print('Descriptions associated with multiple StockCodes:')
print(desc_code_counts.head())

# Intermediate Example 5: Detect invoices with missing descriptive information
missing_desc = df_retail[df_retail['Description'].isnull()]
print('Invoices with missing product descriptions:')
print(missing_desc[['Invoice', 'StockCode', 'Description']].head())

# Intermediate Example 6: Summarize unique countries in data
unique_countries = df_retail['Country'].nunique()
print('Number of unique countries in this sales data:', unique_countries)

# Advanced Example 1: Combine with a product catalog and spot mismatches
np.random.seed(42)
categories = ['Electronics','Clothing','Home','Sports','Beauty']
product_ids = list(range(1001,1101))
product_categories = np.random.choice(categories,100)
product_prices = np.round(np.random.uniform(5,500,100),2)
catalog = pd.DataFrame({'StockCode':product_ids,'Category':product_categories,'Cat_Price':product_prices})
joined = df_retail.merge(catalog, how='left', left_on='StockCode', right_on='StockCode')
missing_catalogs = joined[joined['Category'].isnull()]
print('Retail transactions missing a product catalog entry:')
print(missing_catalogs[['Invoice', 'StockCode', 'Description']].head())

# Advanced Example 2: Create a data quality summary table
quality_summary = pd.DataFrame({
    'missing_customer_id': [df_retail['Customer ID'].isnull().mean()],
    'missing_description': [df_retail['Description'].isnull().mean()],
    'duplicate_invoices': [df_retail['Invoice'].duplicated().mean()],
    'negative_quantity': [(df_retail['Quantity'] <= 0).mean()]
})
print('Fraction of problem rows by issue type:')
print((quality_summary * 100).round(2), '%')

# Advanced Example 3: Visual inspection for missing values
import matplotlib.pyplot as plt
null_counts = df_retail.isnull().mean().sort_values(ascending=False)
plt.figure(figsize=(10,4))
null_counts.plot(kind='bar', color='cornflowerblue')
plt.title('Fraction of Missing Values per Column')
plt.xlabel('Column')
plt.ylabel('Fraction Missing')
plt.show()

# Error Example 1: Use info() to debug missing data
df_retail.info()

# Error Example 2: Use describe() to surface unusual values
desc_stats = df_retail.describe(include='all')
print(desc_stats.T[['count', 'unique', 'top', 'freq']] if 'unique' in desc_stats.T.columns else desc_stats)

# Error Example 3: Check if grouping drops transactions
sales_by_invoice = df_retail.groupby('Invoice').agg({'Quantity': 'sum', 'Price': 'mean'})
missing_invoices = set(df_retail['Invoice']) - set(sales_by_invoice.index)
print('Invoices missing after groupby:', len(missing_invoices))

# Error Example 4: Detect wrong unitsidentify outliers in Quantity
q_low = df_retail['Quantity'].quantile(0.01)
q_high = df_retail['Quantity'].quantile(0.99)
suspect_units = df_retail[(df_retail['Quantity'] < q_low) | (df_retail['Quantity'] > q_high)]
print('Possible unit errors in Quantity:')
print(suspect_units[['Invoice', 'Description', 'Quantity']].head())

# Pattern Example 1: Segmenting customers by data quality
seg_q = (df_retail.assign(is_missing_customer=df_retail['Customer ID'].isnull())
    .groupby('Country')['is_missing_customer'].mean().sort_values(ascending=False))
print('Fraction of transactions with no customer per country:')
print((seg_q*100).round(2))

# Pattern Example 2: Validate that product codes match catalog
valid_refs = df_retail['StockCode'].isin(catalog['StockCode'])
valid_pct = valid_refs.mean()
print('Percentage of retail rows with a matching product catalog code:', round(valid_pct*100,2),'%')

# Pattern Example 3: Market basket analysis - check data shape and missingness
np.random.seed(42)
products = ['Bread','Milk','Butter','Eggs','Apples','Chicken','Rice','Cheese']
transaction_ids = np.repeat(np.arange(1,301),3)
product_choices = np.random.choice(products,len(transaction_ids))
df_basket = pd.DataFrame({'TransactionID':transaction_ids,'Product':product_choices})
print('Market basket dataset shape:', df_basket.shape)
print('Missing values in basket data:', df_basket.isnull().sum().sum())

# Pattern Example 4: Do a seasonal trend check on sales dates
df_retail['Month'] = df_retail['InvoiceDate'].dt.month
monthly_count = df_retail.groupby('Month').size()
print('Transactions per month:')
print(monthly_count)

# Workflow Step 1: Filter out incomplete or duplicate transactions
clean_sales = (df_retail
    .dropna(subset=['Description','Customer ID'])
    .drop_duplicates()
    .loc[df_retail['Quantity']>0]
)
print('Cleaned transactions shape:', clean_sales.shape)

# Workflow Step 2: Aggregate total sales by product
clean_sales['Revenue'] = clean_sales['Quantity'] * clean_sales['Price']
product_sales = (clean_sales.groupby('Description')['Revenue'].sum()
    .sort_values(ascending=False))
print('Top 5 selling products by revenue:')
print(product_sales.head(5))