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))

print(df.columns.tolist())

missing_summary = df.isnull().sum()
print(missing_summary)

num_missing = df.isnull().sum().sum()
print(f'Total missing values in dataset: {num_missing}')

missing_rows = df[df.isnull().any(axis=1)]
print(f'Rows with missing data: {missing_rows.shape[0]}')
print(missing_rows.head(2))

df['IsIncomplete'] = df.isnull().any(axis=1)
print(df[['Invoice', 'StockCode', 'IsIncomplete']].head(5))

percent_incomplete = df['IsIncomplete'].mean() * 100
print(f'Percentage of incomplete records: {percent_incomplete:.2f}%')

invalid_quant = df[df['Quantity'] <= 0]
print(f'Negative or zero quantity transactions: {invalid_quant.shape[0]}')
print(invalid_quant[['Invoice', 'StockCode', 'Quantity']].head(2))

invalid_price = df[df['Price'] <= 0]
print(f'Negative or zero price transactions: {invalid_price.shape[0]}')
print(invalid_price[['Invoice', 'StockCode', 'Price']].head(2))

empty_desc = df[df['Description'].isnull() | (df['Description'].str.strip() == '')]
print(f'Records missing a product description: {empty_desc.shape[0]}')
print(empty_desc[['Invoice', 'StockCode', 'Description']].head(2))

df_nodup = df.drop_duplicates()
print(f'Records after removing duplicates: {df_nodup.shape[0]}')

invalid_customer = df[df['Customer ID'].isnull() | (df['Customer ID'] == 0)]
print(f'Rows missing or invalid Customer ID: {invalid_customer.shape[0]}')
print(invalid_customer[['Invoice', 'StockCode', 'Customer ID']].head(2))

future_dates = df[df['InvoiceDate'] > pd.Timestamp('2011-12-31')]
print(f'Transactions with future invoice dates: {future_dates.shape[0]}')
print(future_dates[['Invoice', 'InvoiceDate']].head(1))

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(-2, 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})
df_orders.loc[::50, 'Quantity'] = np.nan  # insert some nulls
print(df_orders.head(5))

issues = df_orders[(df_orders['Quantity'].isnull()) | (df_orders['Quantity'] <= 0)]
print(f'Total problematic order records: {issues.shape[0]}')
print(issues.head(3))

df_clean = df_orders.dropna(subset=['Quantity'])
df_clean = df_clean[df_clean['Quantity'] > 0]
print(f'Remaining clean orders: {df_clean.shape[0]}')

top_product = df_clean.groupby('ProductID')['Quantity'].sum().idxmax()
top_sales = df_clean.groupby('ProductID')['Quantity'].sum().max()
print(f'Top-selling product is {top_product} with total quantity sold: {top_sales}')