PROJECT 003 · PYTHON SOURCE

Product credit exposure

Study-specific code. Shared modules, dependency versions, and reproduction instructions are included in all project files.

from pathlib import Path
import sys
ROOT = Path(__file__).resolve().parents[2]
sys.path.insert(0, str(ROOT))
import numpy as np
import pandas as pd
from scipy.stats import spearmanr
from sklearn.decomposition import PCA
from sklearn.ensemble import IsolationForest
from sklearn.preprocessing import StandardScaler
from sklearn.metrics import confusion_matrix, precision_score, recall_score
from portfolio.data import load
from portfolio.constants import *
from portfolio.methods import result, sql, rates, associations, distribution, regression, classification, cluster
from portfolio.engine import execute

META = {'id': 3, 'dataset': 'retail', 'title': 'Product credit exposure', 'question': 'Which product codes have the largest credited value relative to invoiced sales?', 'method': 'SQL conditional aggregation with a minimum sales exposure.', 'action': 'Investigate the product and invoice context before attributing credits to quality problems.', 'limitations': 'Historical invoice lines; credits are not reliably matched to original sales. Gross purchases are not profit. Unidentified customers cannot support customer-level conclusions. Exact repeated lines remain unless the study explicitly compares removal. '}

def analyze():
    df = load('retail').copy()
    query = """SELECT stockcode, SUM(CASE WHEN is_sale THEN value ELSE 0 END) gross_gbp,
     SUM(CASE WHEN quantity<0 AND unitprice>0 THEN -value ELSE 0 END) credit_gbp,
     COUNT(*) lines FROM observations GROUP BY stockcode HAVING gross_gbp>=10000"""
    t = sql(df,query)
    t['credit_to_gross'] = t.credit_gbp/t.gross_gbp
    t=t.sort_values('credit_to_gross',ascending=False)
    out=result(t,'stockcode','credit_to_gross','Ratio','Ratios compare period credits to period gross value; credits may relate to earlier sales.')
    return out

if __name__ == "__main__":
    execute(Path(__file__).parent, META, analyze)