Tozalash: iflos ma'lumotdan ishonchli jadvalgacha

iflos = pd.read_csv(DATA / "iflos_savdo.csv")
print(iflos.shape)
show(iflos.sample(6, random_state=3))

print(iflos.dtypes.to_string(), "\n")
print("bo'sh qiymatlar:\n" + iflos.isna().sum()[iflos.isna().sum() > 0].to_string(), "\n")
print("to'liq dublikat qatorlar:", iflos.duplicated().sum())
print("item_id takrorlangan:", iflos.item_id.duplicated().sum())

print("region variantlari:", iflos.region.nunique(), "ta:")
print(iflos.region.value_counts().head(12).to_string())

print("sana formatlari (namuna):", iflos.order_date.sample(6, random_state=1).tolist())
print("quantity taqsimoti:", iflos.quantity.describe()[["min", "50%", "max"]].to_dict())
print("matnli narxlar:", iflos.unit_price.astype(str).str.contains(" ").sum(), "ta")

n0 = len(iflos)
toza = iflos.drop_duplicates().copy()
print(f"dublikat: {n0 - len(toza)} qator olib tashlandi, qoldi {len(toza)}")
print("item_id hali takrorlanadimi:", toza.item_id.duplicated().sum())

def sanaga(s):
    """'2025-03-15', '15.03.2025', '15/03/25' -> datetime. Uch formatni ketma-ket sinaydi."""
    for fmt in ("%Y-%m-%d", "%d.%m.%Y", "%d/%m/%y"):
        d = pd.to_datetime(s, format=fmt, errors="coerce")
        if d.notna().all():
            return d
    # aralash bo'lsa: har formatni alohida, keyin birlashtirish
    out = pd.Series(pd.NaT, index=s.index)
    for fmt in ("%Y-%m-%d", "%d.%m.%Y", "%d/%m/%y"):
        out = out.fillna(pd.to_datetime(s, format=fmt, errors="coerce"))
    return out

toza["order_date"] = sanaga(toza.order_date)
print(toza.order_date.dtype, "| bo'sh:", toza.order_date.isna().sum(), "| oraliq:", toza.order_date.min().date(), "-", toza.order_date.max().date())

KIRILL = {"тошкент шаҳри": "Toshkent shahri", "самарқанд": "Samarqand", "бухоро": "Buxoro", "фарғона": "Farg'ona", "андижон": "Andijon"}
regions = pd.read_csv(CSV / "regions.csv")
lotin = {r.lower(): r for r in regions.region}                   # "toshkent shahri" -> "Toshkent shahri"

norm = toza.region.astype(str).str.strip().str.lower()
toza["region"] = norm.map(lotin).fillna(norm.map(KIRILL))
print("variantlar:", iflos.region.nunique(), "->", toza.region.nunique(), "| tanilmagan:", toza.region.isna().sum())

toza["unit_price"] = toza.unit_price.astype(str).str.replace(" ", "", regex=False).str.replace(",", "").astype(int)
print(toza.unit_price.dtype, "| min", toza.unit_price.min(), "| max", f"{toza.unit_price.max():,}")

print(toza.isna().sum()[toza.isna().sum() > 0].to_string())

products = pd.read_csv(CSV / "products.csv")
# category: mahsulot nomidan tiklash mumkin (products da bor) -> to'ldiramiz
kat = toza.product_name.map(products.set_index("product_name").category)
toza["category"] = toza.category.fillna(kat)
# status: noma'lum holatni taxmin qilmaymiz -> alohida belgilaymiz
toza["status"] = toza.status.fillna("noma'lum")
# quantity: miqdor noma'lum bo'lsa summa hisoblanmaydi -> tashlaymiz (nechta ekanini yozib)
n_q = toza.quantity.isna().sum()
toza = toza[toza.quantity.notna()].copy()
print(f"category: manbadan tiklandi | status: 'noma\\'lum' belgisi | quantity: {n_q} qator tashlandi, qoldi {len(toza)}")

xato = toza[(toza.quantity <= 0) | (toza.quantity > 100)]
print(len(xato), "qator: miqdor <= 0 yoki > 100")
print(xato.quantity.value_counts().to_dict())
toza = toza[(toza.quantity > 0) & (toza.quantity <= 100)].copy()
toza["quantity"] = toza.quantity.astype(int)
print("qoldi:", len(toza))

haqiqat = pd.read_csv(DATA / "answers" / "iflos_savdo_toza.csv")
def summa(df): return (df.quantity * df.unit_price)[df.status == "delivered"].sum()
print(f"qatorlar: tozalangan {len(toza)}, haqiqat {len(haqiqat)}")
print(f"yetkazilgan summa: tozalangan {summa(toza)/1e9:.3f} mlrd, haqiqat {summa(haqiqat)/1e9:.3f} mlrd")
print(f"hudud bo'yicha buyurtma soni mos:", toza.region.value_counts().sort_index().equals(haqiqat[haqiqat.item_id.isin(toza.item_id)].region.value_counts().sort_index()))

def tozala(df, regions, products):
    """iflos_savdo formatidagi faylni tozalaydi. Qaytaradi: toza df va qadamlar hisoboti."""
    log = {"kirdi": len(df)}
    df = df.drop_duplicates().copy();                         log["dublikat"] = log["kirdi"] - len(df)
    df["order_date"] = sanaga(df.order_date)
    lotin = {r.lower(): r for r in regions.region}
    n = df.region.astype(str).str.strip().str.lower()
    df["region"] = n.map(lotin).fillna(n.map(KIRILL));        log["tanilmagan_hudud"] = int(df.region.isna().sum())
    df["unit_price"] = df.unit_price.astype(str).str.replace(" ", "", regex=False).astype(int)
    df["category"] = df.category.fillna(df.product_name.map(products.set_index("product_name").category))
    df["status"] = df.status.fillna("noma'lum")
    ok = df.quantity.notna() & (df.quantity > 0) & (df.quantity <= 100)
    log["miqdor_tashlandi"] = int((~ok).sum())
    df = df[ok].copy(); df["quantity"] = df.quantity.astype(int)
    log["chiqdi"] = len(df)
    return df, log

toza2, log = tozala(pd.read_csv(DATA / "iflos_savdo.csv"), regions, products)
print(log)
print("funksiya va qo'lda tozalash bir xil:", len(toza2) == len(toza) and summa(toza2) == summa(toza))