Excel loader: sarlavha, nomlar va "Jami" qatorlari

11-bob 8 kod bloki 1981 so'z

Excel jadval emas

4-bobda Excel faylni o'qish tezligini o'lchadik. Lekin u yerdagi fayl toza edi: birinchi qatorda sarlavha, qolganida ma'lumot. Haqiqiy bank fayli bunday emas. Unda odatda:

• tepada jadval nomi va o'lchov birligi ("mlrd so'm") yozilgan qatorlar;
• ikki qatorli sarlavha: "Kreditlar" ostida "shundan: jismoniy" va "yuridik";
• ma'lumot orasida "Jami", "Итого", oraliq jamilar ("Davlat banklari");
• pastda izoh: "Manba: ...";
• bo'sh ustun va bo'sh qatorlar.

Agar bu faylni to'g'ridan-to'g'ri bazaga yuklasak, "Jami" qatori ham oddiy qator bo'lib qoladi. Keyin "filiallar bo'yicha jami" so'ralganda, jami ikki marta hisoblanadi — va hech kim sezmaydi. Bu 1-bobdagi beshinchi talabga — "birorta ham yolg'on raqam o'tmasin" — to'g'ridan-to'g'ri zid.

Tezda bu ishni accuracy/load_excel.py qiladi. Fayl calamine bilan o'qiladi (4-bobdagi tanlov), keyin clean_sheet funksiyasi uni toza jadvalga aylantiradi.

Haqiqiy fayl

Ommaga ochiq e'lon qilingan bitta haqiqiy jadvaldan boshlaymiz: hududlar bo'yicha kredit va depozitlar. Bu — test to'plamlaridagi real_regions jadvalining asl fayli. Avval uni xom holda, qanday bo'lsa shunday o'qiymiz:

import json
from accuracy.load_excel import read_sheet, clean_sheet

qoidalar = json.load(open(TEZ / "accuracy/load.json", encoding="utf-8"))
sozlamalar = json.load(open(TEZ / "accuracy/settings.json", encoding="utf-8"))

xom = read_sheet(str(DATA / "regions_eng.xlsx"))
print(f"xom qatorlar: {len(xom)}")
for qator in xom[:6]:
    print([str(katak)[:28] for katak in qator])

xom qatorlar: 19
['Information on total loans a', '', '', '', '', '', '', '']
['', '', '', '', '', '', '', 'billion UZS']
['№', 'Regions', 'Total loan portfolio', 'of which:', '', 'Total deposits', 'of which:', '']
['', '', '', 'individuals', 'legal entities', '', 'individuals', 'legal entities']
['Total', '', '635978.4033489999', '232852.27900200002', '403126.1243470001', '460599.8919684046', '178481.074612382', '282118.81735602254']
['1.0', 'Republic of Karakalpakstan', '21456.552435999998', '11539.971472', '9916.580963999999', '8572.64147465092', '7283.4187656963095', '1289.2227089546104']

Birinchi qator — jadval nomi. Ikkinchisi — bo'sh. Uchinchi va to'rtinchi — ikki qatorli sarlavha: "of which:" ostida "individuals" va "legal entities". Beshinchi — "Total". Haqiqiy ma'lumot faqat oltinchi qatordan boshlanadi.

Endi loaderdan o'tkazamiz:

jadval = clean_sheet(xom, qoidalar, sozlamalar)
print("sarlavhalar (titles):", jadval["titles"])
print("ustunlar:")
for nom in jadval["columns"]:
    print("   ", nom)
print(f"ma'lumot qatorlari: {len(jadval['rows'])}")
print("chetga olingan jami qatorlari:", [qator[0] for qator in jadval["totals"]])
print("izohlar:", jadval["notes"])

sarlavhalar (titles): ['Information on total loans and total deposits of banking system by regions as of June 1, 2026', 'billion UZS']
ustunlar:
    №
    Regions
    Total loan portfolio
    Total loan portfolio of which: individuals
    Total loan portfolio of which: legal entities
    Total deposits
    Total deposits of which: individuals
    Total deposits of which: legal entities
ma'lumot qatorlari: 14
chetga olingan jami qatorlari: ['Total']
izohlar: []

Uchta ish qilindi:

1. Jadval nomi va birlik ("billion UZS") ma'lumotdan ajratildi va titles ga yozildi. Ular yo'qolmaydi: 3-bobda ko'rdik, prompt jadval nomi yonida ularni modelga ko'rsatadi.
2. Ikki qatorli sarlavha birlashtirildi. "individuals" o'zi yolg'iz turmaydi — u "of which:" ostida, u esa "Total loan portfolio" ostida. Natijada ustun nomi to'liq ma'noli bo'ldi: "Total loan portfolio of which: individuals". Depozitlar uchun ham xuddi shunday — va ikkala "individuals" ustuni endi farqli nomga ega.
3. "Total" qatori chetga olindi. U ma'lumotdan chiqarildi, lekin tashlanmadi — totals da saqlanadi.

Jami — tekshiruv vositasi

Chetga olingan "Total" qatori — shunchaki keraksiz qator emas. U tekshiruv: fayl muallifi yozgan jami. Agar loader hamma ma'lumot qatorini to'g'ri olgan bo'lsa, ularning yig'indisi shu jami bilan teng bo'lishi kerak:

import math

jami = jadval["totals"][0]
for i, nom in enumerate(jadval["columns"]):
    if isinstance(jami[i], float):
        yigindi = sum(qator[i] for qator in jadval["rows"])
        belgi = "teng" if math.isclose(yigindi, jami[i], rel_tol=1e-9) else "FARQ"
        print(f"{nom[:45]:45s} qatorlar: {yigindi:14,.1f}   fayldagi jami: {jami[i]:14,.1f}   {belgi}")

Total loan portfolio                          qatorlar:      635,978.4   fayldagi jami:      635,978.4   teng
Total loan portfolio of which: individuals    qatorlar:      232,852.3   fayldagi jami:      232,852.3   teng
Total loan portfolio of which: legal entities qatorlar:      403,126.1   fayldagi jami:      403,126.1   teng
Total deposits                                qatorlar:      460,599.9   fayldagi jami:      460,599.9   teng
Total deposits of which: individuals          qatorlar:      178,481.1   fayldagi jami:      178,481.1   teng
Total deposits of which: legal entities       qatorlar:      282,118.8   fayldagi jami:      282,118.8   teng

Har bir sonli ustunda o'n to'rtta hudud yig'indisi faylning o'z jamisi bilan teng. Demak bitta ham qator yo'qolmagan va bitta ham jami ikki marta kirmagan.

Bu tekshiruv kelajakdagi ilova uchun muhim g'oya: fayl ochilganda Tez "qatorlaringiz yig'indisi faylingizdagi jami bilan mos keladi" deb ko'rsatishi mumkin. Mos kelmasa — bu fayldagi xatoni yoki loader xatosini darhol ko'rsatadi. Bugun bu tekshiruv kodda avtomatik ishlamaydi; biz uni shu yerda qo'lda qildik.

Bank fayliga o'xshash sintetik fayl

Endi qiyinroq holat. O'ylab topilgan, lekin bank hisobotiga o'xshash fayl yasaymiz: o'zbekcha nom, birlik, ikki qatorli sarlavha, oraliq jamilar (so'z bilan emas, faqat raqam bilan belgilangan), kirill yozuvidagi "ЖАМИ", bo'sh ustun va pastda izoh:

import xlsxwriter

fayl = ISH / "banklar.xlsx"
kitob = xlsxwriter.Workbook(str(fayl))
varaq = kitob.add_worksheet()
qatorlar = [
    ["Banklar bo'yicha kredit va depozitlar", "", "", "", ""],
    ["", "", "", "", "mlrd so'm"],
    ["Bank", "", "Kreditlar", "shundan:", ""],
    ["", "", "", "jismoniy", "yuridik"],
    ["Davlat banklari", "", 70.0, 30.0, 40.0],
    ["Agrobank", "", 30.0, 10.0, 20.0],
    ["Asakabank", "", 40.0, 20.0, 20.0],
    ["Xususiy banklar", "", 25.0, 15.0, 10.0],
    ["Kapitalbank", "", 15.0, 9.0, 6.0],
    ["Hamkorbank", "", 10.0, 6.0, 4.0],
    ["ЖАМИ", "", 95.0, 45.0, 50.0],
    ["Manba: o'ylab topilgan ma'lumot", "", "", "", ""],
]
for i, qator in enumerate(qatorlar):
    varaq.write_row(i, 0, qator)
kitob.close()

banklar = clean_sheet(read_sheet(str(fayl)), qoidalar, sozlamalar)
print("sarlavhalar:", banklar["titles"])
print("ustunlar:", banklar["columns"])
print("ma'lumot:", [qator[0] for qator in banklar["rows"]])
print("chetga olingan:", [qator[0] for qator in banklar["totals"]])
print("izohlar:", banklar["notes"])

sarlavhalar: ["Banklar bo'yicha kredit va depozitlar", "mlrd so'm"]
ustunlar: ['Bank', 'Kreditlar', 'shundan: jismoniy', 'shundan: yuridik']
ma'lumot: ['Agrobank', 'Asakabank', 'Kapitalbank', 'Hamkorbank']
chetga olingan: ['Davlat banklari', 'Xususiy banklar', 'ЖАМИ']
izohlar: ["Manba: o'ylab topilgan ma'lumot"]

Natijani bandma-band o'qing:

• Bo'sh ikkinchi ustun olib tashlandi.
• "shundan:" ostidagi "jismoniy" va "yuridik" — "shundan: jismoniy" va "shundan: yuridik" bo'ldi.
• "ЖАМИ" — so'z bo'yicha topildi. Jami so'zlari ro'yxati sozlamalarda turadi va har qanday yozuvda solishtiriladi (kirill harflar lotinga o'giriladi).
• "Davlat banklari" va "Xususiy banklar" — ularda "jami" so'zi yo'q. Ular yig'indi bo'yicha topildi: "Davlat banklari" qatoridagi har bir son o'zidan keyingi ikki qator yig'indisiga teng.
• Izoh pastda ajratildi.

Sozlamalar fayli — accuracy/load.json:

print(json.dumps(qoidalar, ensure_ascii=False, indent=1))

{
 "header_min_fill": 0.5,
 "total_words": [
  "jami",
  "itogo",
  "vsego",
  "total"
 ],
 "subtotal_min_rows": 2,
 "subtotal_min_columns": 2,
 "subtotal_max_rows": 1000,
 "subtotal_tolerance": 1e-06,
 "unnamed_column": "column {n}"
}

subtotal_min_columns — oraliq jami kamida ikkita sonli ustunda mos kelishi kerak. Bitta ustunda tasodifiy moslik juda oson: 5 + 7 = 12 har qanday ro'yxatda uchrashi mumkin.

Chegara: tasodifiy oraliq jami

Ikki ustun qoidasi tasodifni kamaytiradi, lekin yo'q qilmaydi. Buni sinab ko'ramiz: to'rtta oddiy filial, va ulardan birining raqamlari tasodifan boshqa ikkitasining yig'indisiga teng:

filiallar = [
    ["Filial", "Kreditlar", "Depozitlar"],
    ["Chilonzor", 5.0, 3.0],
    ["Yunusobod", 7.0, 2.0],
    ["Sergeli", 12.0, 5.0],
    ["Olmazor", 4.0, 1.0],
]
natija = clean_sheet(filiallar, qoidalar, sozlamalar)
print("ma'lumot:", [qator[0] for qator in natija["rows"]])
print("chetga olingan:", [qator[0] for qator in natija["totals"]])

ma'lumot: ['Chilonzor', 'Yunusobod', 'Olmazor']
chetga olingan: ['Sergeli']

Sergeli — oddiy filial. Lekin uning kreditlari (12) va depozitlari (5) Chilonzor va Yunusobod yig'indisiga teng, va loader uni oraliq jami deb chetga oldi. Agar shu holda "filiallar bo'yicha jami" so'ralsa, Sergeli javobga kirmaydi.

Bu — biz shu bobni yozayotganda topgan haqiqiy kamchilik, va uni yashirmaymiz. Ikkita yumshatuvchi holat bor: chetga olingan qatorlar totals da saqlanadi (yo'qolmaydi), va haqiqiy faylda butun sonlar bilan bunday tasodif kam uchraydi. Lekin bank uchun "kam" yetarli emas. Yechim planda yozildi: chetga olinadigan qatorlar yuklashdan oldin foydalanuvchiga ko'rsatiladi, va qoida faqat raqamga emas, belgiga ham (nom, joylashuv, blok chegarasi) tayanadi. Bu kod test natijalariga ta'sir qiladi, shuning uchun u faqat ikki benchmark orasida o'zgartiriladi.

Ma'nosiz sarlavha

Yana bir chegara — loader qila olmaydigan narsa. Valyuta kurslari jadvalining asl faylini o'qiymiz:

kurslar = clean_sheet(read_sheet(str(DATA / "0009_eng.xlsx")), qoidalar, sozlamalar)
print("ustunlar:", kurslar["columns"])
for qator in kurslar["rows"][:3]:
    print("  ", qator)

ustunlar: ['G1', 'G2', 'G3', 'G4', 'G5', 'G6', 'G7', 'G8', 'G9']
   ['USD', 840, 1, 12931.94, '12.03.2025', 'Доллар США', 'AQSH dollari', 'АҚШ доллари', 'US Dollar']
   ['EUR', 978, 1, 14107.45, '12.03.2025', 'Евро', 'EVRO', 'EВРО', 'Euro']
   ['RUB', 643, 1, 150.18, '12.03.2025', 'Российский рубль', 'Rossiya rubli', 'Россия рубли', 'Russian Ruble']

Bu faylning o'z sarlavhasi — G1, G2, ... G9. Loader hech narsa o'ylab topmadi: faylda shunday yozilgan. Odam birinchi qatorga qarab G1 — valyuta kodi, G4 — kurs, G6 — ruscha nomi ekanini tushunadi. Model esa faqat nom va bir nechta namunani ko'radi.

6-qismda modelning xatolarini birma-bir tahlil qilamiz, va bu jadvaldagi to'rtta xato aynan shu sabab bilan bog'liq chiqadi: "dollar kursi" so'ralganda model kod ustunini emas, nom ustunini qidirdi. Bu loader xatosi emas — bu ma'lumot yetishmasligi. Yechim ham loaderda emas, ilovada: sarlavha ma'nosiz bo'lsa, foydalanuvchidan asosiy ustunlarni nomlashni so'rash.

Testlar

Loaderning har bir qoidasi test bilan himoyalangan — shu bobda ko'rgan holatlarning hammasi: nom va birlik, ikki qatorli sarlavha, jami so'zlari har xil yozuvda, yig'indi bo'yicha oraliq jamilar, bitta sonli ustunda hech narsa chetga olinmasligi:

cd /mnt/c/Users/Admin/Desktop/Offline/spikes/spike2_3_model
PYTHONDONTWRITEBYTECODE=1 "$OLDPWD/.venv/bin/python" -m pytest accuracy/test_load_excel.py -q -p no:cacheprovider 2>&1 | tail -1

9 passed in 1.34s

Testlar o'tadi — lekin yuqoridagi Sergeli holati uchun test yo'q. Testlar faqat biz o'ylagan holatlarni tekshiradi. Shuning uchun kitobni yozish ham tekshiruvning bir turi bo'lib chiqdi: har bir qoidani o'quvchiga ko'rsatish uchun uni boshqa ma'lumotda sinab ko'rishga to'g'ri keladi.

Xulosa

• Haqiqiy Excel fayl — jadval emas: unda nom, birlik, ko'p qatorli sarlavha, jami va izohlar bor.
• clean_sheet nom va birlikni titles ga, jamilarni totals ga, izohlarni notes ga ajratadi va ikki qatorli sarlavhani to'liq nomga aylantiradi.
• Faylning o'z jamisi — tekshiruv: haqiqiy hududlar faylida qatorlar yig'indisi har bir ustunda jami bilan teng chiqdi.
• Oraliq jamilar so'z bo'yicha ham, yig'indi bo'yicha ham topiladi. Yig'indi qoidasi tasodifan oddiy qatorni ham chetga olishi mumkin — buni o'lchadik va planga yozdik.
• Ma'nosiz sarlavhani (G1...G9) loader tuzata olmaydi; bu ilovaning savoli.

Bu bilan uchinchi qism tugadi. To'rtinchi qismda training ma'lumotiga o'tamiz: biz Gemma ni o'qituvchi sifatida qanday ishlatdik — va nega u Tezning ichiga hech qachon kirmaydi.