Exercise 2: dirty sales data

import pandas as pd

sv = pd.read_csv(DATA / "iflos_savdo.csv")
print("shape:", sv.shape)
print()
print(sv.dtypes.to_string())
print()
show(sv.head(8))

print("1) Date formats:")
dotted = sv.order_date.astype(str).str.match(r"\d{2}\.\d{2}\.\d{4}").sum()
iso = sv.order_date.astype(str).str.match(r"\d{4}-\d{2}-\d{2}").sum()
print(f"   dotted (26.02.2025): {dotted}")
print(f"   ISO    (2025-05-26): {iso}")
print(f"   other/empty        : {len(sv) - dotted - iso}")
print()
print("2) unit_price dtype:", sv.unit_price.dtype)
print("   written with spaces:", sv.unit_price.astype(str).str.contains(" ").sum())
print("   samples:", sv.unit_price.astype(str).head(4).tolist())
print()
print("3) region: number of distinct values:", sv.region.nunique())
print("   but the real number of provinces is much smaller. Here's why:")
for v in sorted(sv.region.dropna().unique())[:9]:
    print("     ", repr(v))
print("     ... in total", sv.region.nunique(), "different spellings")
print()
print("4) Duplicate rows:", int(sv.duplicated().sum()))
print()
print("5) Missing values:")
missing = sv.isna().sum()
print(missing[missing > 0].to_string())
print()
print("6) status (the future target):")
print(sv.status.value_counts(dropna=False).to_string())

import pandas as pd
show(pd.DataFrame([
    {"#": 1, "Answer to write down": "How many rows, how many columns"},
    {"#": 2, "Answer to write down": "\"I predict ___ from ___\": one sentence"},
    {"#": 3, "Answer to write down": "Task type: regression / binary / multiclass"},
    {"#": 4, "Answer to write down": "The target ratio and the BASELINE number"},
    {"#": 5, "Answer to write down": "Did you split? Do you need stratify?"},
    {"#": 6, "Answer to write down": "The dummy result"},
    {"#": 7, "Answer to write down": "Which columns you dropped, and WHY"},
    {"#": 8, "Answer to write down": "What you did about missing values and types"},
    {"#": 9, "Answer to write down": "Your first honest result"},
    {"#": 10, "Answer to write down": "Which metric you chose, and WHY"},
    {"#": 11, "Answer to write down": "Which group the mistakes cluster in"},
    {"#": 12, "Answer to write down": "Which ONE change you made, and the result"},
]))

show(pd.DataFrame([
    {"T": 1, "Cleaning task": "Bring order_date to one format (there are two)"},
    {"T": 2, "Cleaning task": "Turn unit_price into a number (remove the spaces)"},
    {"T": 3, "Cleaning task": "Normalise region: case, spaces, the CYRILLIC alphabet"},
    {"T": 4, "Cleaning task": "Remove the duplicate rows"},
    {"T": 5, "Cleaning task": "Decide what to do with an empty status (it's your target)"},
    {"T": 6, "Cleaning task": "Create the target: what will you predict?"},
]))

import numpy as np

CYRILLIC = {"андижон": "andijon", "бухоро": "buxoro", "самарқанд": "samarqand",
            "тошкент шаҳри": "toshkent shahri", "фарғона": "fargona"}

def normalise(x):
    if pd.isna(x):
        return np.nan
    s = str(x).strip().lower()
    s = CYRILLIC.get(s, s)
    return s.replace("'", "").replace("`", "").replace("ʻ", "")

clean = sv.drop_duplicates().copy()
clean["region_clean"] = clean.region.map(normalise)
clean["price"] = pd.to_numeric(
    clean.unit_price.astype(str).str.replace(r"[^\d.]", "", regex=True), errors="coerce")
clean["date"] = pd.to_datetime(clean.order_date, format="mixed", dayfirst=True, errors="coerce")

print(f"rows           : {len(sv)} -> {len(clean)}  (duplicates removed)")
print(f"region variety : {sv.region.nunique()} -> {clean.region_clean.nunique()}")
print(f"price dtype    : {sv.unit_price.dtype} -> {clean.price.dtype}")
print(f"price NaN      : {int(clean.price.isna().sum())}")
print(f"date NaN       : {int(clean.date.isna().sum())}")
print()
print("Provinces after normalising:")
print(clean.region_clean.value_counts().to_string())

from sklearn.model_selection import train_test_split, cross_val_score
from sklearn.pipeline import Pipeline
from sklearn.compose import ColumnTransformer
from sklearn.preprocessing import StandardScaler, OneHotEncoder
from sklearn.impute import SimpleImputer
from sklearn.linear_model import LogisticRegression
from sklearn.dummy import DummyClassifier
from sklearn.metrics import accuracy_score, f1_score, average_precision_score

m = clean.dropna(subset=["status", "price", "date"]).copy()
m["cancelled"] = (m.status == "cancelled").astype(int)
m["month"] = m.date.dt.month
m["weekday"] = m.date.dt.dayofweek
m["quantity"] = m.quantity.fillna(m.quantity.median())
m["revenue"] = m.quantity * m.price

num = ["price", "quantity", "revenue", "month", "weekday"]
cat = ["region_clean", "category"]
X, y = m[num + cat], m["cancelled"]
X_tr, X_te, y_tr, y_te = train_test_split(X, y, test_size=0.3, random_state=42, stratify=y)

print(f"Rows for the model: {len(m)}")
print(f"Share of target 'cancelled': {y.mean():.2%}   <-- severely imbalanced")
print()
baseline = accuracy_score(y_te, DummyClassifier(strategy="most_frequent").fit(X_tr[num], y_tr).predict(X_te[num]))
print(f"BASELINE accuracy: {baseline:.4f}   <-- accuracy is USELESS here")

pipe = Pipeline([
    ("prep", ColumnTransformer([
        ("num", Pipeline([("impute", SimpleImputer(strategy="median")),
                          ("scale", StandardScaler())]), num),
        ("cat", Pipeline([("impute", SimpleImputer(strategy="most_frequent")),
                          ("encode", OneHotEncoder(handle_unknown="ignore"))]), cat),
    ])),
    ("model", LogisticRegression(max_iter=3000, class_weight="balanced")),
]).fit(X_tr, y_tr)

b = pipe.predict(X_te)
e = pipe.predict_proba(X_te)[:, 1]
print(f"Accuracy               : {accuracy_score(y_te, b):.4f}  (baseline {baseline:.4f})")
print(f"F1 (the cancelled class): {f1_score(y_te, b, zero_division=0):.4f}")
print(f"Average Precision      : {average_precision_score(y_te, e):.4f}")
print(f"AP baseline            : {y_te.mean():.4f}  (= the share of positives)")