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

# Beginner Example 1: Load Online Retail Transaction Data
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))

# Beginner Example 2: Check for Exact Duplicate Rows
n_rows_before = df.shape[0]
n_duplicates = df.duplicated().sum()
print(f'Total records: {n_rows_before}')
print(f'Number of exact duplicate rows: {n_duplicates}')

# Beginner Example 3: Remove Exact Duplicate Rows
df_dedup = df.drop_duplicates()
print(f'After removing duplicates: {df_dedup.shape[0]} rows remain.')

# Beginner Example 4: Find Duplicates Based on Invoice and StockCode Only
dup_invoice_stock = df.duplicated(subset=['Invoice', 'StockCode'])
print('Number of duplicate Invoice+StockCode pairs:', dup_invoice_stock.sum())

# Beginner Example 5: Examine Duplicate Invoice-Product Rows
duplicates_df = df[df.duplicated(subset=['Invoice', 'StockCode'], keep=False)].sort_values(['Invoice','StockCode'])
print(duplicates_df[['Invoice', 'StockCode', 'Quantity', 'Price']].head(6))

# Intermediate Example 1: Simulate Duplicates in Customer Orders (Synthetic Data)
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')
orders_df = pd.DataFrame({
    'OrderID': order_ids,
    'CustomerID': customer_ids,
    'ProductID': product_ids,
    'Quantity': quantities,
    'OrderDate': order_dates,
})
# Artificially insert duplicates
orders_df = pd.concat([orders_df, orders_df.sample(50, random_state=42)], ignore_index=True)
print('Orders with synthetic duplicates:', orders_df.shape)

# Intermediate Example 2: Identify Duplicate Orders by OrderID
dupes_by_orderid = orders_df.duplicated(subset=['OrderID'])
print('Number of duplicated OrderIDs:', dupes_by_orderid.sum())

# Intermediate Example 3: Remove Duplicate OrderIDs, Keeping First Occurrence
orders_nodup = orders_df.drop_duplicates(subset=['OrderID'], keep='first')
print(f'Records after dropping duplicate OrderIDs: {orders_nodup.shape[0]}' )

# Intermediate Example 4: Aggregating Orders Without Removing Duplicates
order_sales = orders_df.groupby('OrderID').agg({'Quantity':'sum'}).reset_index()
print(f'Orders aggregated (with duplicates): {order_sales.shape[0]}')
print(order_sales.head(3))

# Intermediate Example 5: Aggregating Orders After Removing Duplicates
order_sales_nodup = orders_nodup.groupby('OrderID').agg({'Quantity':'sum'}).reset_index()
print(f'Orders aggregated (after deduplication): {order_sales_nodup.shape[0]}')
print(order_sales_nodup.head(3))

# Intermediate Example 6: Calculate Unique Customer Count (With and Without Duplicates)
n_customers_with_dupes = orders_df['CustomerID'].nunique()
n_customers_no_dupes = orders_nodup['CustomerID'].nunique()
print(f'Unique customers (with duplicates): {n_customers_with_dupes}')
print(f'Unique customers (no duplicate orders): {n_customers_no_dupes}')

# Advanced Example 1: Find Near-Duplicate Orders by Multiple Columns
dupe_cols = ['CustomerID', 'ProductID', 'OrderDate']
near_dupes = orders_df.duplicated(subset=dupe_cols, keep=False)
print('Possible near-duplicate orders:', near_dupes.sum())
print(orders_df[near_dupes].head(4))

# Advanced Example 2: Detect Duplicate Transactions Within Minutes (Potential Replay Issue)
orders_df['OrderTime'] = pd.to_datetime(orders_df['OrderDate'])
orders_df['OrderMinute'] = orders_df['OrderTime'].dt.floor('min')
dupe_within_min = orders_df.duplicated(subset=['CustomerID', 'ProductID', 'OrderMinute'], keep=False)
print('Orders with same customer, product, minute:', dupe_within_min.sum())

# Advanced Example 3: Remove Duplicates and Recompute Key Sales KPIs
# Assume each row represents a sale of price $20 (for demonstration)
orders_df['UnitPrice'] = 20
orders_clean = orders_df.drop_duplicates(subset=['OrderID'])
total_sales = (orders_df['Quantity'] * orders_df['UnitPrice']).sum()
total_sales_clean = (orders_clean['Quantity'] * orders_clean['UnitPrice']).sum()
print(f'Total sales WITH duplicates: ${total_sales}')
print(f'Total sales AFTER removing duplicates: ${total_sales_clean}')

# Error Handling Example 1: Try to Remove Duplicates When None Exist
empty_df = orders_df.drop_duplicates().copy()
empty_df = empty_df[~empty_df.duplicated(subset=['OrderID'])]
print('Rows after aggressive deduplication:', empty_df.shape[0])

# Error Handling Example 2: Detect If Missing Values Cause Apparent Duplicates
orders_with_nan = orders_df.copy()
orders_with_nan.loc[orders_with_nan.sample(20, random_state=42).index, 'ProductID'] = np.nan
nan_dupes = orders_with_nan.duplicated(subset=['OrderID', 'ProductID'])
print('Possible duplicates where ProductID is missing:', nan_dupes.sum())

# Error Handling Example 3: Avoid Incorrect Grouping by Using All Unique Identifiers
df_grouped_wrong = orders_df.groupby('ProductID').agg({'Quantity':'sum'}).reset_index()
df_grouped_right = orders_df.groupby(['OrderID','ProductID']).agg({'Quantity':'sum'}).reset_index()
print('Total product quantity (wrong grouping):', df_grouped_wrong['Quantity'].sum())
print('Total product quantity (correct grouping):', df_grouped_right['Quantity'].sum())

# Pattern: Customer Segmentation After Deduplication
seg_orders = orders_nodup.copy()
customer_segments = seg_orders.groupby('CustomerID').agg({'OrderID': 'count'})
customer_segments['Segment'] = pd.qcut(customer_segments['OrderID'], q=3, labels=['Low','Medium','High'])
print(customer_segments['Segment'].value_counts())

# Pattern: Market Basket Analysis with Cleaned Transaction Data
basket_data = orders_nodup.groupby(['OrderID','ProductID']).size().unstack(fill_value=0)
print('Cleaned basket matrix shape:', basket_data.shape)
print(basket_data.head(2))

# End-to-End Problem: Identify Top-Selling Products After Removing Duplicates
orders_cleaned = orders_df.drop_duplicates(subset=['OrderID'])
product_counts = orders_cleaned['ProductID'].value_counts().head(5)
print('Top 5 products by unduplicated order count:')
print(product_counts)