import pandas as pd
df = pd.read_csv('online_retail.csv', parse_dates=['InvoiceDate'])
df.shape

df.columns.tolist()
df.dtypes

df.head(10)

df.isna().sum()
df.isna().mean().round(3) * 100

missing_cust = df[df['CustomerID'].isna()]
missing_cust.shape[0]
missing_cust['Country'].value_counts().head()

is_cancelled = df['InvoiceNo'].astype(str).str.startswith('C')
is_cancelled.sum()
df[is_cancelled].head()

(df['Quantity'] < 0).sum()
(df.loc[df['Quantity'] < 0, 'InvoiceNo'].astype(str).str.startswith('C')).mean().round(3) * 100

(df['UnitPrice'] <= 0).sum()
df.loc[df['UnitPrice'] <= 0, 'Description'].value_counts().head(10)

df.duplicated().sum()
df[df.duplicated(keep=False)].sort_values('InvoiceNo').head(6)

non_numeric = df[~df['StockCode'].astype(str).str[0].str.isdigit()]
non_numeric['StockCode'].value_counts().head(10)
non_numeric.shape[0]

df['Country'].nunique()
df['Country'].value_counts().head(10)
(df['Country'] == 'United Kingdom').mean().round(3) * 100

df['InvoiceDate'].min(), df['InvoiceDate'].max()
df.set_index('InvoiceDate').resample('ME').size()

naive_revenue = (df['Quantity'] * df['UnitPrice']).sum()
round(naive_revenue, 2)
cancelled_revenue = (df.loc[is_cancelled, 'Quantity'] * df.loc[is_cancelled, 'UnitPrice']).sum()
round(cancelled_revenue, 2)

def clean_retail(raw):
    out = raw.copy()
    out = out[~out['InvoiceNo'].astype(str).str.startswith('C')]
    out = out[out['Quantity'] > 0]
    out = out[out['UnitPrice'] > 0]
    out = out[out['StockCode'].astype(str).str[0].str.isdigit()]
    out = out.dropna(subset=['CustomerID'])
    out = out.drop_duplicates()
    out['CustomerID'] = out['CustomerID'].astype(int)
    out['Revenue'] = out['Quantity'] * out['UnitPrice']
    return out.reset_index(drop=True)

clean = clean_retail(df)
clean.shape
round(100 * (1 - clean.shape[0] / df.shape[0]), 2)

clean_revenue = clean['Revenue'].sum()
round(clean_revenue, 2)
round(naive_revenue - clean_revenue, 2)

clean.groupby('Description')['Revenue'].sum().sort_values(ascending=False).head(10)

clean.groupby('CustomerID')['Revenue'].sum().sort_values(ascending=False).head(10)

clean['CustomerID'].nunique()
clean['InvoiceNo'].nunique()
clean['InvoiceDate'].min(), clean['InvoiceDate'].max()

clean.to_csv('online_retail_clean.csv', index=False)
reloaded = pd.read_csv('online_retail_clean.csv', parse_dates=['InvoiceDate'])
reloaded.shape == clean.shape

import matplotlib.pyplot as plt
top_countries = clean['Country'].value_counts().head(10)
plt.figure(figsize=(9, 5))
plt.bar(top_countries.index, top_countries.values, color='steelblue')
plt.xticks(rotation=45, ha='right')
plt.title('Real Cleaned Transaction Rows by Country')
plt.tight_layout()
plt.savefig('retail_top_countries.png', dpi=120)
plt.close()

basket_size = clean.groupby('InvoiceNo').size()
basket_size.describe()
basket_size.sort_values(ascending=False).head(5)

orders_per_customer = clean.groupby('CustomerID')['InvoiceNo'].nunique()
(orders_per_customer == 1).sum()
(orders_per_customer > 1).sum()
round(100 * (orders_per_customer > 1).mean(), 2)

desc_per_code = clean.groupby('StockCode')['Description'].nunique()
(desc_per_code > 1).sum()
desc_per_code.sort_values(ascending=False).head(5)
clean.loc[clean['StockCode'] == desc_per_code.idxmax(), 'Description'].unique()

order_value = clean.groupby('InvoiceNo')['Revenue'].sum()
order_value.mean().round(2)
order_value.median().round(2)

clean['DayOfWeek'] = clean['InvoiceDate'].dt.day_name()
clean.groupby('DayOfWeek')['Revenue'].sum().sort_values(ascending=False)

clean[['Quantity', 'UnitPrice']].describe()
clean.sort_values('Quantity', ascending=False)[['Description', 'Quantity', 'CustomerID']].head(5)

summary_lines = [
    f'Raw rows: {df.shape[0]}',
    f'Cleaned rows: {clean.shape[0]}',
    f'Rows removed by the audit: {df.shape[0] - clean.shape[0]}',
    f'Naive revenue: {round(naive_revenue, 2)}',
    f'Trustworthy cleaned revenue: {round(clean_revenue, 2)}',
    f'Unique real customers: {clean["CustomerID"].nunique()}',
]
with open('online_retail_audit_summary.txt', 'w') as f:
    for line in summary_lines:
        f.write(line + chr(10))
for line in summary_lines:
    print(line)

import os
os.path.exists('online_retail_audit_summary.txt')
os.path.getsize('online_retail_audit_summary.txt') > 0

clean.isna().sum().sum()
(clean['Quantity'] > 0).all() and (clean['UnitPrice'] > 0).all()