Loyihalar

24-bob 32 kod bloki 11801 so'z

Bu qo'llanmada shu paytgacha hamma narsa bittadan g'oya sifatida o'rgatildi: bu yerda bitta GROUP BY, u yerda bitta boxplot. Haqiqiy analitik ish hech qachon bunday ko'rinishda kelmaydi. U siz qaysi tool ishlatishingizni bilmaydigan va bilishni ham xohlamaydigan odamning savoli sifatida keladi, javob esa butun masofani bosib o'tishi kerak — database da yotgan row lardan boshlab, tozalash va qayta shakllantirish orqali, chart gacha va analitik bo'lmagan odam amal qila oladigan bitta jumlagacha.

Bu yakuniy bob to'rtta end-to-end loyihani o'z ichiga oladi. Ularning har biri qo'llanmada o'rgatilgan butun stack ni ishga soladi:

1 va 2 loyihalar 1-bobda qurilgan kichik o'quv database i school.db dan foydalanadi. Unda o'nta student va yigirmata enrolment bor — bu o'quvchi har bir oraliq natijani qo'lda tekshirib chiqishi uchun aynan mos o'lcham, qiziqarli chart uchun esa aynan noto'g'ri o'lcham. Shuning uchun 3 va 4 loyihalar numpy yordamida noldan kattaroq, realistik SQLite database quradi. Generator reproducible (seed qat'iy belgilangan), internetga ulanishni talab qilmaydi va taxminan bir soniyada ishlaydi.

24.1 Bu loyihalardan qanday foydalanish kerak

Bular tarqatib berib, keyin yig'ib olinadigan mashqlar emas. Ular sinf bilan birga bajarilishi, keyin esa o'quvchilar qo'liga topshirilishi uchun mo'ljallangan.

Tavsiya etilgan sur'at

Agar siz jonli ko'rsatib bersangiz va student lar birga yozib borsa, har bir loyiha taxminan uch soatlik bitta mashg'ulot bo'ladi. Ishlaydigan ritm:

• 0:00–0:15 — biznes savolini ovoz chiqarib o'qing. Hech qanday kod ko'rsatmasdan oldin sinfdan qaysi table lar va qaysi column lar kerak bo'lishini so'rang. Javoblarini doskaga yozing; keyin 1-qadamdagi query bilan solishtiring.
• 0:15–0:45 — 1-qadamni birga bajaring. Query ni ishga tushiring. Xom natija table ga qarang. So'rang: biz nechta row kutgan edik va nechtasini oldik? Shu bitta savol join xatolarining ko'pini ushlab qoladi.
• 0:45–1:15 — 2-qadam. Aynan shu joyda student lar eng zaif. Chart ga tegishdan oldin har safar, ovoz chiqarib, df.info() va df.isna().sum() ni chaqirishni talab qiling.
• 1:15–2:15 — 3-qadam. Chart larni birma-bir quring. Har biridan keyin, tushuntirishdan oldin, sinfdan nima ko'rayotganini so'rang. Ularning noto'g'ri o'qishlari — xonadagi eng foydali o'quv materiali.
• 2:15–2:45 — 4-qadam va gotcha lar. Bir student topilmani jargonsiz bitta jumlada aytib bersin.
• 2:45–3:00 — kengaytirish topshiriqlarini uyga vazifa qilib bering.

Nimani baholash kerak

Chart ning chiroyliligiga baho qo'yishdan tiyiling. Muhim ko'nikmalar, ahamiyat tartibida:

1. SQL berilgan savolga javob beryaptimi? O'xshash savolga emas. Aynan o'shanga.
2. Ular yetishmayotgan ma'lumotni payqadimi? Bu yerdagi har bir loyihada kamida bitta ataylab qo'yilgan NULL tuzog'i bor.
3. Aggregation to'g'ri darajada bajarilganmi? Fan-out join dan keyin aggregate qilish — eng ko'p uchraydigan professional xato, va u ovozsiz sodir bo'ladi.
4. Chart turi savolga mos keladimi? Distribution → boxplot yoki histogram. Kategoriyalarni taqqoslash → barplot. Vaqt bo'yicha o'zgarish → lineplot. Ikki tomonlama sanoq → heatmap.
5. Ular birorta function nomini aytmasdan topilmani ifodalay oladimi?

24.2 Umumiy setup

Har bir loyiha bir xil oltita qatordan boshlanadi. Ularni har bir notebook ning birinchi cell iga qo'ying.

# --- bu bobdagi har bir loyiha uchun standart import lar ---
import sqlite3
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

sns.set_theme(style="whitegrid", palette="deep")
pd.set_option("display.max_columns", 40)
pd.set_option("display.width", 140)

Connection va helper

Har bir query uchun connection ochib, uni yopishni unutish — erta tashlab yuborishga arziydigan odat. Bitta kichkina helper function takrorlanishni yo'q qiladi va bizga muammolarni keyinchalik tuzatish uchun yagona joy beradi.

DB_SCHOOL = "school_big.db"      # 1-bobda qurilgan
DB_SHOP   = "shop.db"        # quyida, 24.5 da generatsiya qilinadi

def q(sql, db=DB_SCHOOL, params=None):
    """Run a SELECT and return the result as a DataFrame."""
    with sqlite3.connect(db) as conn:          # avtomatik yopadi/commit qiladi
        return pd.read_sql_query(sql, conn, params=params)

# --- school_big.db ni bir marta quramiz: 1-bob schema si, real hajmda ------
# 1-bob dagi school.db da 20 ta enrolment bor: JOIN ni ko'z bilan o'qish uchun
# ideal, lekin pass rate hisoblash uchun juda kichik. Bu XUDDI SHU schema ni
# kattaroq hajmda quradi va quyidagi query lar tayanadigan barcha o'quv
# nuqtalarini saqlaydi: enrolment siz course, NULL city, NULL teacher_id, NULL grade.
from pathlib import Path

SEED_SCHOOL = 11
if not Path(DB_SCHOOL).exists():
    rng = np.random.default_rng(SEED_SCHOOL)
    con = sqlite3.connect(DB_SCHOOL)
    con.executescript("""
        CREATE TABLE students (
            student_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL,
            last_name TEXT NOT NULL, gender TEXT CHECK (gender IN ('M','F')),
            city TEXT, birth_date DATE, enrolled_date DATE NOT NULL);
        CREATE TABLE teachers (
            teacher_id INTEGER PRIMARY KEY, full_name TEXT NOT NULL,
            subject TEXT, hired_date DATE, salary DECIMAL(10,2));
        CREATE TABLE courses (
            course_id INTEGER PRIMARY KEY, title TEXT NOT NULL,
            category TEXT, credits INTEGER DEFAULT 3, price DECIMAL(10,2));
        CREATE TABLE enrollments (
            enrollment_id INTEGER PRIMARY KEY, student_id INTEGER NOT NULL,
            course_id INTEGER NOT NULL, teacher_id INTEGER,
            enrolled_at DATE NOT NULL, grade INTEGER,
            status TEXT DEFAULT 'active',
            FOREIGN KEY (student_id) REFERENCES students(student_id),
            FOREIGN KEY (course_id)  REFERENCES courses(course_id),
            FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id));
    """)

    teachers = [("Nodira Alimova","Data Science","2021-08-15",9500000),
                ("Rustam Halilov","Databases","2019-03-01",11000000),
                ("Kamola Ergasheva","Statistics","2023-01-10",8200000),
                ("Sardor Yoqubov","Programming","2024-09-01",7800000),
                ("Dilshod Umarov","Programming","2020-02-17",8900000),
                ("Feruza Saidova","Data Science","2022-06-05",9100000),
                ("Jahongir Aliev","Mathematics","2018-11-20",10200000),
                ("Zulfiya Karimova","Statistics","2023-09-12",7950000)]
    con.executemany("INSERT INTO teachers VALUES (?,?,?,?,?)",
                    [(i+1,)+t for i,t in enumerate(teachers)])

    # 12-course ataylab HECH QANDAY enrolment olmaydi - LEFT JOIN tuzog'i
    courses = [("Python Basics","Programming",4,1200000),
               ("SQL for Analytics","Data",3,1500000),
               ("Statistics 101","Math",4,1100000),
               ("Data Visualization","Data",3,1300000),
               ("Machine Learning","Data",5,2100000),
               ("Linear Algebra","Math",4,1000000),
               ("Web Scraping","Programming",2,900000),
               ("Time Series Analysis","Data",4,1750000),
               ("Probability Theory","Math",4,1150000),
               ("Deep Learning","Data",5,2400000),
               ("Git and Workflow","Programming",2,700000),
               ("Research Seminar","Math",1,0)]
    con.executemany("INSERT INTO courses VALUES (?,?,?,?,?)",
                    [(i+1,)+c for i,c in enumerate(courses)])

    fem = ["Aziza","Dilnoza","Malika","Sevara","Zarina","Nilufar","Gulnora",
           "Kamila","Shahzoda","Feruza","Nodira","Mohira"]
    mal = ["Bekzod","Jasur","Otabek","Timur","Ulugbek","Sanjar","Aziz",
           "Doniyor","Shohruh","Farrux","Islom","Bobur"]
    last = ["Karimova","Toshev","Yusupova","Rahimov","Sobirova","Nazarov",
            "Ismoilova","Abdullaev","Qodirova","Mirzaev","Ergashev","Saidova"]
    cities = ["Tashkent","Samarkand","Bukhara","Namangan","Andijan","Fergana","Nukus"]
    starts = ["2025-09-01","2025-09-15","2026-02-01","2026-02-10","2026-09-01"]

    rows = []
    for sid in range(1, 241):
        male = rng.random() < 0.5
        city = None if rng.random() < 0.07 else str(rng.choice(cities))  # ~7% NULL
        bd = f"{int(rng.integers(2002,2007))}-{int(rng.integers(1,13)):02d}-{int(rng.integers(1,29)):02d}"
        rows.append((sid, str(rng.choice(mal if male else fem)), str(rng.choice(last)),
                     "M" if male else "F", city, bd, str(rng.choice(starts))))
    con.executemany("INSERT INTO students VALUES (?,?,?,?,?,?,?)", rows)

    pop = np.array([1.6,1.5,1.2,1.1,1.3,0.7,0.8,0.9,0.7,1.0,1.1]); pop = pop/pop.sum()
    mean_grade = {1:78, 2:84, 3:71, 4:80, 5:74, 6:69, 7:82, 8:73, 9:70, 10:68, 11:88}

    enr, eid = [], 1
    for sid in range(1, 241):
        picks = rng.choice(np.arange(1,12), size=int(rng.integers(3,9)),
                           replace=False, p=pop)
        for cid in picks:
            cid = int(cid)
            tid = None if rng.random() < 0.05 else int(rng.integers(1,9))  # ~5% NULL
            r = rng.random()
            status = "completed" if r < 0.62 else ("active" if r < 0.88 else "dropped")
            if status == "completed":
                g = int(np.clip(rng.normal(mean_grade[cid], 11), 35, 100))
            elif status == "dropped" and rng.random() < 0.4:
                g = int(np.clip(rng.normal(mean_grade[cid]-18, 12), 20, 75))
            else:
                g = None                       # active = not graded yet
            enr.append((eid, sid, cid, tid, str(rng.choice(starts)), g, status))
            eid += 1
    con.executemany("INSERT INTO enrollments VALUES (?,?,?,?,?,?,?)", enr)
    con.commit(); con.close()
    print("school_big.db built")

SQLite versiyangizni tekshiring

2, 3 va 4 loyihalar window function lardan (RANK, LAG, NTILE) foydalanadi — SQLite ularni 3.25 versiyada oldi; 1-loyiha esa 3.30 da qo'shilgan aggregate FILTER clause ini ishlatadi. Qo'llab-quvvatlanadigan har bir Python relizi yangiroq versiya bilan keladi, lekin kurs boshida bir marta tekshiring — shunda eski mashinadagi student buni to'qqizinchi haftada emas, hozir bilib oladi.

print(sqlite3.sqlite_version) # 3.30.0 yoki undan yuqori bo'lishi shart

Offline dataset lar

Bu bobda hech narsa yuklab olinmaydi. 1 va 2 loyihalar school.db ni o'qiydi. 3 va 4 loyihalar shop.db ni 24.5-bo'limdagi generator bilan quradi, u esa faqat numpy ga bog'liq. Agar sinfxonangizda internet umuman bo'lmasa ham, bu bob boshidan oxirigacha ishlayveradi.

24.3 1-loyiha: Kurs revenue va enrolment tahlili

Biznes savoli

Nimani mashq qilasiz

• SQL: LEFT JOIN (nol enrolment li kurs saqlanib qolishi uchun), COUNT(expr) va COUNT(*) farqi, CASE WHEN bilan SUM, GROUP BY, ORDER BY, ROUND, hamda * 1.0 bilan float bo'lishni majburlash.
• pandas: astype, umumiy summadagi ulushni hisoblash, groupby().agg(), pivot_table.
• seaborn: hue= va palette= bilan barplot, countplot, annotation li heatmap.

1-qadam — ma'lumotni olish (SQL)

Har bir kurs uchun bitta row — unga kimdir yozilgan yoki yozilmaganidan qat'i nazar.

SQL_REVENUE = """ SELECT c.course_id, c.title, c.category, c.credits, c.price, COUNT(e.enrollment_id) AS n_enrolments, -- column ustidan COUNT NULL larni e'tiborsiz qoldiradi, shuning uchun -- 6-kurs uchun mos kelmagan LEFT JOIN row i to'g'ri holda 0 sanaladi. -- COUNT(*) bu yerda 1 qaytargan bo'lardi. Tuzoq aynan shu. COUNT(e.enrollment_id) * c.price AS gross_revenue, -- har bir enrolment kursning list price i bo'yicha hisoblanadi SUM(CASE WHEN e.status = 'dropped' THEN 0 ELSE c.price END) FILTER (WHERE e.enrollment_id IS NOT NULL) AS net_revenue, -- tashlab ketgan student larga pul qaytariladi, ya'ni ular 0 qo'shadi. -- FILTER 6-kurs uchun to'ldirilgan LEFT JOIN row ini chiqarib tashlaydi -- -- usiz e.status u yerda NULL bo'ladi, CASE ELSE ga o'tib ketadi -- va student i yo'q kurs to'liq narxni ishlab topgan bo'lib chiqadi. -- FILTER bilan SUM nol row ni aggregate qiladi va NULL qaytaradi, -- biz uni pandas da ataylab tuzatamiz. ROUND(c.price * 1.0 / c.credits, 2) AS price_per_credit -- * 1.0 haqiqiy bo'lishni majburlaydi; usiz SQLite butun sonli -- bo'lish qiladi va 1200/4 kabi natijalar kesib tashlanadi FROM courses AS c LEFT JOIN enrollments AS e ON e.course_id = c.course_id -- LEFT hamma 6 ta kursni saqlaydi. Oddiy JOIN ovozsiz 5 tasini qaytarardi. GROUP BY c.course_id, c.title, c.category, c.credits, c.price ORDER BY gross_revenue DESC; """

rev = q(SQL_REVENUE)          # yuqoridagi query ni SQL_REVENUE ga qo'ying
print(rev.shape)              # (12, 9) kutiladi — o'n ikki kurs, o'n bir emas
rev

Ikkinchi query kategoriya bo'yicha enrolment status ining ikki tomonlama taqsimotini beradi.

SQL_STATUS = """ SELECT c.category, e.status, COUNT(*) AS n FROM enrollments AS e JOIN courses AS c ON c.course_id = e.course_id -- bu yerda INNER join to'g'ri: biz enrolment larni sanayapmiz, -- enrolment i yo'q kurs esa status sanog'iga hech narsa qo'shmaydi GROUP BY c.category, e.status ORDER BY c.category, e.status; """

status = q(SQL_STATUS)
print(status["n"].sum())      # 1303 ga teng bo'lishi shart — umumiy enrolment soni

2-qadam — shakllantirish (pandas / numpy)

`rev.info() # doim. har safar.

nol mos row ustidan SUM NULL qaytaradi -> pandas da NaN.

6-kurs uchun halol qiymat 0, shuning uchun uni aniq to'ldiramiz.

rev["net_revenue"] = rev["net_revenue"].fillna(0).astype(float) rev["gross_revenue"] = rev["gross_revenue"].astype(float)

umumiy revenue dagi ulush, foizda

total = rev["gross_revenue"].sum() rev["revenue_share_pct"] = np.round(100 * rev["gross_revenue"] / total, 1)

har bir enrolment dan haqiqatan yig'ilgan o'rtacha revenue

rev["rev_per_enrolment"] = np.where( rev["n_enrolments"] > 0, rev["net_revenue"] / rev["n_enrolments"], np.nan, # 0 emas — bu aniqlanmagan, nol emas )

kategoriya darajasidagi yig'indi

by_cat = ( rev.groupby("category", as_index=False) .agg(courses=("course_id", "count"), enrolments=("n_enrolments", "sum"), gross=("gross_revenue", "sum"), net=("net_revenue", "sum")) .sort_values("gross", ascending=False) )

heatmap uchun keng table: row lar = category, column lar = status

status_wide = status.pivot_table(index="category", columns="status", values="n", aggfunc="sum", fill_value=0)`

3-qadam — o'rganish va vizualizatsiya (seaborn)

1-chart — kurslar bo'yicha revenue. Gorizontal barplot, saralangan, kategoriya bo'yicha ranglangan.

fig, ax = plt.subplots(figsize=(9, 5))
sns.barplot(
    data=rev.sort_values("gross_revenue", ascending=False),
    x="gross_revenue", y="title",
    hue="category", palette="deep", dodge=False,   # palette bilan hue talab qilinadi
    ax=ax,
)
ax.set_xlabel("Gross revenue")
ax.set_ylabel("")
ax.set_title("Revenue by course")
ax.legend(title="Category", loc="lower right")
plt.tight_layout()

Nimaga e'tibor berish kerak: bar larning uzunligi revenue, lekin eng qiziq narsa — eng pastdagi bar. Deep Learning uzunligi nolga teng bar bilan ko'rinishi shart. Agar u chart da umuman yo'q bo'lsa, student qayerdadir inner join ishlatgan va sotilmayotgan kursni rahbariyat uchun ko'rinmas qilib qo'ygan.

2-chart — kategoriya bo'yicha enrolment status i.

fig, ax = plt.subplots(figsize=(8, 4.5))
sns.heatmap(status_wide, annot=True, fmt="d", cmap="Blues",
            linewidths=.5, cbar_kws={"label": "enrolments"}, ax=ax)
ax.set_title("Enrolment status by category")
ax.set_xlabel("Status")
ax.set_ylabel("Category")
plt.tight_layout()

Nimaga e'tibor berish kerak: muvaffaqiyatsizliklar va tashlab ketishlar bir tekis tarqalganmi yoki bitta kategoriyada to'plangan. Yigirmata enrolment bilan cell lardagi sonlar kichik, shuning uchun ularni statistika sifatida emas, pattern sifatida o'qing.

Xuddi shu ma'lumot countplot ko'rinishida, bu ko'pincha yangi boshlovchi uchun o'qishga osonroq:

fig, ax = plt.subplots(figsize=(8, 4.5))
sns.countplot(
    data=q("""SELECT c.category, e.status
              FROM enrollments e JOIN courses c ON c.course_id = e.course_id"""),
    x="category", hue="status", palette="deep", ax=ax,
)
ax.set_title("Enrolment status by category")
plt.tight_layout()

3-chart — credit boshiga narx. Bizning narx siyosatimiz ichki jihatdan izchilmi?

fig, ax = plt.subplots(figsize=(8, 4.5))
sns.barplot(data=rev, x="price_per_credit", y="title",
            hue="category", palette="deep", dodge=False,
            errorbar=None, ax=ax)          # errorbar=None, ci=None emas (0.13 da olib tashlangan)
ax.set_xlabel("Price per credit")
ax.set_ylabel("")
ax.set_title("Is pricing consistent across the catalogue?")
plt.tight_layout()

Nimaga e'tibor berish kerak: notekis profil credit va price bir-biridan mustaqil belgilanganini bildiradi. Bu narx siyosatiga oid topilma, ma'lumot muammosi emas — va aynan stakeholder eshitishni xohlashini o'zi ham bilmagan turdagi narsa.

4-qadam — topilma

Kafedra mudiriga topshirilishi kerak bo'lgan shaklda yozilgan:

• Joriy katalogdagi yigirmata enrolment ning barchasi olti kursimizdan beshtasiga to'g'ri keladi. Deep Learning umuman enrolment jalb qilmagan, ya'ni u nol revenue keltiradi, lekin baribir katalogda joy egallaydi va, ehtimol, o'qituvchining tayyorgarlik vaqtini oladi.
• Gross revenue to'plangan holatda: revenue enrolment lar sonini price ga ko'paytirgani uchun, yuqori narx bilan o'rtachadan yuqori talabni birlashtirgan kurslar umumiy summada ustunlik qiladi. Aniq reytingni barplot dan o'qing va yuqoridagi ikki kursning birgalikdagi revenue_share_pct ini keltiring.
• Gross va net revenue faqat tashlab ketilgan enrolment lar hisobiga farq qiladi. Ikki column ni solishtiring: farq — bu tashlab ketishning bevosita moliyaviy narxi va kafedra unga qarab chora ko'ra oladigan raqam.
• Credit boshiga narx katalog bo'ylab bir xil emas, ya'ni "qimmat" kurslar har doim ham yetkazilgan o'quv birligi hisobida qimmat emas.

Bu loyihadagi gotcha lar

Kengaytirish topshiriqlari

1. Har bir kurs uchun completion_rate column ini qo'shing: o'sha kursning status = 'completed' bo'lgan enrolment lari ulushi. Nol enrolment li kurs nolga bo'lish error i emas, NULL berishiga e'tibor bering va uni qanday ko'rsatishni hal qiling.
2. Hozir revenue — list price ni enrolment lar soniga ko'paytirgan qiymat. Query ni shunday qayta yozingki, failed enrolment lar yarim narxda, dropped lar esa nolda hisoblansin — bitta CASE ifodasi bilan; keyin 1-chart ni qayta chizing va reyting qanday o'zgarganini tasvirlang.
3. Umumiy revenue, umumiy enrolment lar va credit boshiga o'rtacha narx bilan kategoriya darajasidagi summary table tuzing va uni kategoriyalari revenue bo'yicha tartiblangan barplot sifatida chizing. Qaysi kategoriya umumiy hisobda emas, taklif qilingan har bir kurs hisobiga eng yuqori revenue ga ega?
4. 19-enrolment ning teacher_id qiymati NULL. Har bir teacher bo'yicha revenue ni ko'rsatadigan va biriktirilmagan enrolment ni tashlab yubormasdan "Unassigned" deb belgilangan alohida row sifatida ko'rinadigan qiladigan query yozing. Sizga qaysi join turi kerak va COALESCE qayerga qo'yiladi?

24.4 2-loyiha: Student performance hisoboti

Biznes savoli

Nimani mashq qilasiz

• SQL: ko'p table li join lar, AVG va uning NULL bilan xatti-harakati, COUNT(grade) va COUNT(*) farqi, rate sifatida AVG(CASE WHEN ...), || bilan string birlashtirish, COALESCE va window function lar — 7-bobdagi RANK() OVER (PARTITION BY ... ORDER BY ...).
• pandas: pivot_table, isna() bilan audit, tartiblangan categorical lar, merge.
• seaborn: boxplot, kde bilan histplot, annotation li heatmap, errorbar= bilan barplot va boxplot ustiga qo'yilgan stripplot.

1-qadam — ma'lumotni olish (SQL)

Mavjud eng mayda grain dan boshlang: har bir enrolment uchun bitta row, ismlar biriktirilgan holda.

SQL_MARKS = """ SELECT e.enrollment_id, s.student_id, s.first_name || ' ' || s.last_name AS student, -- || bu SQLite ning birlashtirish operatori, + emas COALESCE(s.city, 'Unknown') AS city, -- 7-student (Sevara) ning city qiymati NULL; yo'qotish o'rniga belgilang c.course_id, c.title AS course, c.category, COALESCE(t.full_name, 'Unassigned') AS teacher, -- 19-enrolment ning teacher_id qiymati NULL e.status, e.grade -- 20 ta grade dan 6 tasi NULL: aktiv student lar va tashlab ketganlar FROM enrollments AS e JOIN students AS s ON s.student_id = e.student_id -- INNER xavfsiz: har bir enrolment da yaroqli student_id bor JOIN courses AS c ON c.course_id = e.course_id -- bu yerda ham INNER xavfsiz, lekin u 6-kursni yashiradi (enrolment yo'q) LEFT JOIN teachers AS t ON t.teacher_id = e.teacher_id -- LEFT SHART: bu yerda INNER join 19-enrolment ni o'chirib yuborardi ORDER BY c.course_id, e.grade DESC; """

marks = q(SQL_MARKS)
print(marks.shape)                 # (1303, 10) kutiladi — 1303 ta enrolment ham saqlanadi
print(marks["grade"].isna().sum()) # 423 ta NULL grade kutiladi

Endi kurs bo'yicha summary — u SQL da hisoblanadi, shunda sinf NULL lar aggregate larda qanday yo'l tutishini ko'radi:

SQL_COURSE_SUMMARY = """ SELECT c.course_id, c.title AS course, COUNT(*) AS n_enrolments, COUNT(e.grade) AS n_graded, -- COUNT(*) dan aynan NULL grade lar soniga farq qiladi ROUND(AVG(e.grade), 1) AS avg_grade, -- AVG NULL larni ovozsiz TASHLAB KETADI: u n_graded ga bo'ladi, n_enrolments ga emas ROUND(MIN(e.grade), 1) AS min_grade, ROUND(MAX(e.grade), 1) AS max_grade, ROUND(100.0 * AVG(CASE WHEN e.grade >= 60 THEN 1.0 ELSE 0.0 END), 1) AS pass_rate_pct -- 1/0 flag ning AVG qiymati — bu ulush. CASE NULL grade lar uchun -- NULL qaytaradi, agar biz ruxsat bersak — bu yerda NULL >= 60 NULL, -- shuning uchun CASE ELSE 0.0 ga o'tadi va baholanmagan row lar fail sanaladi. -- Buni xohlaganingizni ataylab hal qiling (gotcha larga qarang). FROM enrollments AS e JOIN courses AS c ON c.course_id = e.course_id GROUP BY c.course_id, c.title HAVING COUNT(e.grade) > 0 -- HAVING aggregation dan KEYIN filtrlaydi; WHERE buni qila olmasdi ORDER BY avg_grade DESC; """

Va window function li query: har bir kursdagi eng yuqori bahoga ega student.

WITH ranked AS (
    SELECT
        c.title                              AS course,
        s.first_name || ' ' || s.last_name    AS student,
        e.grade,
        RANK() OVER (
            PARTITION BY e.course_id          -- har bir kurs uchun ranking qaytadan boshlanadi
            ORDER BY e.grade DESC             -- eng yuqori grade 1-rank ni oladi
        ) AS rnk
    FROM enrollments AS e
    JOIN students AS s ON s.student_id = e.student_id
    JOIN courses  AS c ON c.course_id  = e.course_id
    WHERE e.grade IS NOT NULL
        -- ranking dan OLDIN filtrlang, aks holda NULL lar o'z rank ini oladi
)
SELECT course, student, grade, rnk
FROM ranked
WHERE rnk = 1                -- teng natijalar ikkalasi ham rank 1 ni saqlaydi, ya'ni kursda ikki g'olib bo'lishi mumkin
ORDER BY course;

… 24 qatordan birinchi 15 tasi

24 qator

2 ms

2-qadam — shakllantirish (pandas / numpy)

marks.info()
audit = marks.isna().sum()
print(audit[audit > 0]) # aynan qaysi column lar missing value tashiydi

# grade aynan NULL lar SABABLI float64 bo'lib keladi.
# int64 column NaN ni saqlay olmaydi. Uni float qoldiring yoki
# butun sonli semantika muhim bo'lsa, nullable integer dtype ishlating:
marks["grade"] = marks["grade"].astype("Float64")

# hosila column lar
marks["passed"] = marks["grade"] >= 60 # grade NA bo'lgan joyda pd.NA
marks["is_graded"] = marks["grade"].notna()

# harf bo'yicha band lar, chart lar to'g'ri saralanishi uchun TARTIBLANGAN categorical bilan
bands = ["F", "D", "C", "B", "A"]
marks["band"] = pd.cut(
 marks["grade"].astype(float),
 bins=[0, 60, 70, 80, 90, 100],
 labels=bands,
 right=False, # [0,60) bu F, 60 esa D
)
marks["band"] = marks["band"].cat.set_categories(bands, ordered=True)

# baholangan qism — har bir o'rtacha va har bir boxplot 

marks

 ni emas, shuni ishlatadi
graded = marks[marks["is_graded"]].copy()
print(len(graded)) # 20 - 6 = 14 ta baholangan enrolment

# heatmap uchun student x course grade matritsasi
grid = graded.pivot_table(index="student", columns="course",
 values="grade", aggfunc="mean")

# student bo'yicha summary
per_student = (
 graded.groupby("student", as_index=False)
 .agg(n_courses=("course_id", "nunique"),
 avg_grade=("grade", "mean"),
 n_passed=("passed", "sum"))
 .sort_values("avg_grade", ascending=False)
)

3-qadam — o'rganish va vizualizatsiya (seaborn)

1-chart — kurs bo'yicha grade distribution. Ustiga alohida nuqtalar chizilgan boxplot, chunki o'n to'rtta baholangan row bilan yolg'iz box chalg'ituvchi bo'ladi.

fig, ax = plt.subplots(figsize=(9, 5))
sns.boxplot(data=graded, x="course", y="grade",
            hue="course", palette="deep", legend=False,
            width=.55, showfliers=False, ax=ax)
sns.stripplot(data=graded, x="course", y="grade",
              color="black", size=6, jitter=.15, alpha=.8, ax=ax)
ax.axhline(60, ls="--", lw=1, color="grey")
ax.text(ax.get_xlim()[1], 60.5, " pass mark", va="bottom", ha="right",
        fontsize=9, color="grey")
ax.set_title("Grade distribution by course (graded enrolments only)")
ax.tick_params(axis="x", rotation=20)
plt.tight_layout()

Nimaga e'tibor berish kerak: qaysi box lar asosan punktir pass chizig'idan pastda joylashgan va har bir box aslida nechta qora nuqtadan qurilgan. Ikkita nuqtadan chizilgan "box" — bu bezak, distribution emas; buni ovoz chiqarib ayting.

2-chart — umumiy grade distribution.

fig, ax = plt.subplots(figsize=(8, 4.5))
sns.histplot(data=graded, x="grade", bins=np.arange(40, 105, 5),
             kde=True, color="#4C72B0", ax=ax)
ax.axvline(graded["grade"].astype(float).mean(), color="crimson", ls="--",
           label=f"mean = {graded['grade'].astype(float).mean():.1f}")
ax.legend()
ax.set_title("Distribution of all graded results")
plt.tight_layout()

Nimaga e'tibor berish kerak: shakl bitta cho'qqili mi yoki asosiy massadan ajralgan, pastda joylashgan klaster bormi? Bimodal grade distribution odatda bir xil baholashda o'tirgan ikki xil student populyatsiyasini bildiradi.

3-chart — student × course matritsasi.

fig, ax = plt.subplots(figsize=(9, 6))
sns.heatmap(grid.astype("float64"), annot=True, fmt=".0f", cmap="RdYlGn",
            vmin=40, vmax=100, linewidths=.6,
            cbar_kws={"label": "grade"}, ax=ax)
ax.set_title("Grade by student and course (blank = not taken / not yet graded)")
ax.set_xlabel("")
ax.set_ylabel("")
plt.tight_layout()

Nimaga e'tibor berish kerak: uni ikki marta o'qing — bir marta row lar bo'ylab (hamma joyda zaif student mi yoki bitta fanda zaif mi) va bir marta column lar bo'ylab (hamma qiynaladigan kurs). Bu ikki o'qish butunlay boshqacha aralashuvlarga olib keladi.

4-chart — kurs bo'yicha pass rate.

rates = q(SQL_COURSE_SUMMARY)          # 1-qadamdagi ikkinchi query

fig, ax = plt.subplots(figsize=(9, 4.5))
sns.barplot(data=rates.sort_values("pass_rate_pct", ascending=False),
            x="pass_rate_pct", y="course",
            hue="course", palette="deep", legend=False,
            errorbar=None, ax=ax)      # errorbar=None: bular allaqachon aggregate
for i, row in enumerate(rates.sort_values("pass_rate_pct", ascending=False)
                             .itertuples()):
    ax.text(row.pass_rate_pct + 1, i, f"n={row.n_graded}", va="center", fontsize=9)
ax.set_xlim(0, 110)
ax.set_xlabel("Pass rate (%)")
ax.set_ylabel("")
ax.set_title("Pass rate by course")
plt.tight_layout()

Nimaga e'tibor berish kerak: n= annotation i chart dagi eng muhim element. Bitta baholangan student dan chiqqan 100% pass rate va qirq student dan chiqqan 100% pass rate bir xil da'vo emas, bar chart esa maxrajni chop etmasangiz ularni bir xil ko'rsatadi.

4-qadam — topilma

• Yigirmata enrolment dan faqat 14 tasida grade bor. Qolgan oltitasi aktiv yoki tashlab ketgan student lar. Demak, bu hisobotdagi har bir o'rtacha enrolment larning 70% ini tasvirlaydi va hisobot buni aytishi shart.
• AVG NULL bo'lmagan grade lar soniga bo'lgani uchun, kurs bo'yicha o'rtachalar juda kichik maxrajlar ustida hisoblanadi. Har bir o'rtacha yonida n_graded ni ko'rsating; ikkita baho asosidagi kurs o'rtachasi — bu anekdot.
• RANK() query si har bir kursdagi eng yaxshi student ni nomlaydi. Ikki student eng yuqori grade ni bo'lishsa, ikkalasi ham rank 1 oladi va ikkalasi ham chiqadi — bu mukofot ro'yxati uchun to'g'ri xatti-harakat va bu yerda ROW_NUMBER o'rniga RANK ni afzal ko'rish sababi.
• Heatmap performance bilan bir qatorda qamrovni ham ko'rsatadi: hech bir student hamma kursni olmagan, ya'ni student larni kurslar bo'ylab taqqoslash teng shartlarda emas.

Bu loyihadagi gotcha lar

Kengaytirish topshiriqlari

1. Pass-rate query sini "faqat baholangan row lar" ta'rifi bilan qayta yozing, ikkala versiyani bitta DataFrame da yonma-yon qo'ying va ularni taqqoslaydigan guruhlangan barplot chizing. Ota-onalarga beriladigan hisobotga qaysi birini qo'yishingizni va nima uchun ekanini ikki jumlada tushuntiring.
2. Eng yaxshi student query sida RANK() o'rniga ROW_NUMBER() ishlating. Ikkalasi turli javob beradigan kursni toping va nima sodir bo'lganini tushuntiring.
3. Student o'rtachalari CTE sidan hisoblangan DENSE_RANK() OVER (ORDER BY avg_grade DESC) yordamida butun maktab bo'yicha student rank ini qo'shing. Agar mean o'rniga median bo'yicha rank qilsangiz, qaysi student lar o'rin almashadi?
4. Grade lar aktiv va tashlab ketilgan enrolment larda yetishmaydi. Yetishmayotgan grade lar sonini status va category bo'yicha taqqoslaydigan chart chizing va yetishmovchilik tasodifiy ko'rinadimi yoki tizimli ekanini asoslang.

24.5 3-loyiha: Sotuv time series va seasonality

school.db da yigirmata enrolment row i bor. Yigirmata row da seasonality ni ko'ra olmaysiz. Shuning uchun bu loyiha — va 4-loyiha — avval o'z database ini quradi.

shop.db ni qurish (bir marta ishga tushiring)

Quyidagi generator besh product kategoriyasi bo'ylab uch yillik kunlik sotuvni yaratadi. Har bir series — bu trend, yillik seasonal to'lqinga, haftalik effektga, dekabrdagi ko'tarilishga va multiplikativ shovqinga ko'paytirilgan. Tuzilma ataylab ichiga joylashtirilgani uchun, student lar o'z tahlili u yerda borligini bilgan narsani tiklay oldimi yo'qmi tekshira oladi — bu esa time-series tahlilini o'rganishning eng yaxshi usuli.

SEED = 7
rng = np.random.default_rng(SEED)      # zamonaviy numpy 2 generator API si

dates = pd.date_range("2022-01-01", "2024-12-31", freq="D")
n     = len(dates)                      # 1096 kun (2024 kabisa yili)
t     = np.arange(n)                    # 0,1,2,... — chiziqli trend index i
doy   = dates.dayofyear.to_numpy()
dow   = dates.dayofweek.to_numpy()      # Dushanba = 0 ... Yakshanba = 6
month = dates.month.to_numpy()

# bazaviy daraja, kunlik o'sish, yillik amplituda, dam olish kunlari ko'tarilishi
spec = {
    "Electronics": dict(base=1800, growth=1.10, yearly=0.30, weekend=0.15),
    "Grocery":     dict(base=2600, growth=0.35, yearly=0.08, weekend=0.30),
    "Clothing":    dict(base=1200, growth=0.60, yearly=0.45, weekend=0.25),
    "Home":        dict(base= 900, growth=0.25, yearly=0.20, weekend=0.10),
    "Toys":        dict(base= 600, growth=0.15, yearly=0.55, weekend=0.35),
}

frames = []
for name, p in spec.items():
    trend  = p["base"] + p["growth"] * t
    yearly = 1 + p["yearly"] * np.sin(2 * np.pi * (doy - 80) / 365.25)
        # cho'qqi taxminan 171-kun atrofida (iyun oxiri); -80 siljish cho'qqini ko'chiradi
    weekly = 1 + p["weekend"] * (dow >= 5)      # bool array -> 0/1 arifmetikasi
    xmas   = 1 + 0.45 * (month == 12)          # dekabr ko'tarilishi
    noise  = rng.normal(1.0, 0.08, n)          # multiplikativ, 8% sd

    revenue = np.maximum(trend * yearly * weekly * xmas * noise, 0).round(2)
    units   = rng.poisson(revenue / 40).astype(int)

    frames.append(pd.DataFrame({
        "sale_date": dates.strftime("%Y-%m-%d"),   # ISO matn: SQLite ning sana formati
        "category":  name,
        "units":     units,
        "revenue":   revenue,
    }))

sales = pd.concat(frames, ignore_index=True)
print(sales.shape)      # (5480, 4) = 1096 kun x 5 kategoriya

conn = sqlite3.connect(DB_SHOP)
sales.to_sql("sales", conn, if_exists="replace", index=False)
conn.execute("CREATE INDEX IF NOT EXISTS ix_sales_date ON sales(sale_date)")
conn.commit()
conn.close()

(5480, 4)

Biznes savoli

Nimani mashq qilasiz

• SQL: sana qismlari uchun strftime, hosila column bo'yicha GROUP BY, SUM, sanalar bo'yicha ORDER BY va oydan oyga o'zgarish uchun LAG window function i (7-bob).
• pandas: to_datetime, set_index, rolling, pct_change, pivot_table, PeriodIndex ustidan groupby.
• numpy: sinusoidal seasonal komponentlar, bool dan raqamga arifmetika, np.maximum.
• seaborn: hue bilan lineplot, rolling-mean overlay, month × year heatmap, diverging palette bilan o'zgarish barplot i.

1-qadam — ma'lumotni olish (SQL)

SQL_MONTHLY = """ SELECT strftime('%Y-%m', s.sale_date) AS ym, -- '2024-03'. Matn, lekin u xronologik saralanadi, ISO sanalar -- shuning uchun muhim. Yolg'iz '%Y' yilni, '%m' oyni beradi. s.category, SUM(s.revenue) AS revenue, SUM(s.units) AS units, COUNT(*) AS n_days -- sanity column i: to'liq oy uchun 28-31, ma'lumot yetishmasa kamroq FROM sales AS s WHERE s.sale_date >= '2022-01-01' AND s.sale_date <= '2024-12-31' -- string taqqoslash ishlaydi, chunki format ISO-8601 GROUP BY ym, s.category ORDER BY ym, s.category; """

m = q(SQL_MONTHLY, db=DB_SHOP)
print(m.shape)                    # (180, 5) = 36 oy x 5 kategoriya
print(m["n_days"].unique())       # faqat 28, 29, 30, 31 bo'lishi kerak

Oydan oyga o'zgarishni LAG bilan butunlay SQL da bajarish mumkin; buni ko'rsatishga arziydi, shunda student lar pandas ga qo'l cho'zishdan oldin SQL-native yo'lni ko'radi:

WITH monthly AS (
    SELECT strftime('%Y-%m', sale_date) AS ym,
           category,
           SUM(revenue)                 AS revenue
    FROM sales
    GROUP BY ym, category
)
SELECT
    ym,
    category,
    revenue,
    LAG(revenue) OVER (PARTITION BY category ORDER BY ym) AS prev_revenue,
        -- LAG har bir kategoriyaning o'z timeline i ichida bitta row ORQAGA qaraydi.
        -- Har bir kategoriyaning birinchi oyi NULL oladi — oldingisi yo'q.
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (PARTITION BY category ORDER BY ym))
              / LAG(revenue) OVER (PARTITION BY category ORDER BY ym), 1
    ) AS mom_pct
        -- haqiqiy bo'lishni majburlash uchun 100.0 (100 emas)
FROM monthly
ORDER BY category, ym;

2-qadam — shakllantirish (pandas / numpy)

m["ym"] = pd.to_datetime(m["ym"] + "-01")     # matn -> haqiqiy timestamp lar
m["year"]  = m["ym"].dt.year
m["month"] = m["ym"].dt.month
m["month_name"] = m["ym"].dt.strftime("%b")
m["month_name"] = pd.Categorical(
    m["month_name"],
    categories=["Jan","Feb","Mar","Apr","May","Jun",
                "Jul","Aug","Sep","Oct","Nov","Dec"],
    ordered=True,                              # aks holda chart lar Apr, Aug, Dec... deb saralaydi
)

# oydan oyga o'zgarish, pandas usulida (LAG query si bilan solishtiring)
m = m.sort_values(["category", "ym"])
m["mom_pct"] = m.groupby("category")["revenue"].pct_change() * 100
    # pct_change dan oldin groupby — bu PARTITION BY ning pandas dagi ekvivalenti

# bosh series uchun barcha kategoriyalar bo'yicha umumiy summa
total = (m.groupby("ym", as_index=False)["revenue"].sum()
           .sort_values("ym"))
total["roll3"]  = total["revenue"].rolling(3,  min_periods=3).mean()
total["roll12"] = total["revenue"].rolling(12, min_periods=12).mean()
    # 12 oylik rolling mean seasonality ni butunlay olib tashlaydi
    # va trend ni qoldiradi. Chart ning butun ma'nosi shu.

# umumiy revenue ning year x month matritsasi
grid = m.pivot_table(index="month_name", columns="year",
                     values="revenue", aggfunc="sum", observed=True)

3-qadam — o'rganish va vizualizatsiya (seaborn)

1-chart — rolling mean bilan bosh series.

fig, ax = plt.subplots(figsize=(11, 5))
sns.lineplot(data=total, x="ym", y="revenue",
             color="#9aa5b1", lw=1.2, label="monthly revenue", ax=ax)
sns.lineplot(data=total, x="ym", y="roll12",
             color="#C44E52", lw=2.5, label="12-month rolling mean", ax=ax)
ax.set_xlabel("")
ax.set_ylabel("Revenue")
ax.set_title("Total revenue: raw monthly series and de-seasonalised trend")
plt.tight_layout()

Nimaga e'tibor berish kerak: kulrang chiziq tebranadi, qizil chiziq esa yo'q. Agar qizil chiziq barqaror ko'tarilsa, o'sish haqiqiy; agar u tekis bo'lsa-yu kulrang chiziqda katta cho'qqilar bo'lsa, o'sishga o'xshab ko'ringan narsa shunchaki takrorlanayotgan seasonality. Aynan shu bitta chart stakeholder ning savoliga javob beradi.

2-chart — kategoriya bo'yicha series lar.

fig, ax = plt.subplots(figsize=(11, 5))
sns.lineplot(data=m, x="ym", y="revenue", hue="category",
             palette="deep", lw=1.6, ax=ax)
ax.set_title("Monthly revenue by category")
ax.set_xlabel("")
ax.legend(title="Category", ncol=5, loc="upper left", fontsize=9)
plt.tight_layout()

Nimaga e'tibor berish kerak: har bir chiziqning o'z darajasiga nisbatan amplitudasi. Toys Grocery ga qaraganda ancha kuchli tebranishi kerak — bu generator dagi yearly parametri bo'lib, ma'lumotdan tiklangan.

3-chart — month × year heatmap.

fig, ax = plt.subplots(figsize=(7, 6))
sns.heatmap(grid.astype("float64"), annot=True, fmt=".0f", cmap="YlOrRd",
            linewidths=.5, cbar_kws={"label": "revenue"}, ax=ax)
ax.set_title("Revenue by month and year")
ax.set_xlabel("Year")
ax.set_ylabel("")
plt.tight_layout()

Nimaga e'tibor berish kerak: o'sha oyning yildan yilga o'sishini ko'rish uchun row bo'ylab o'qing; yil ichidagi seasonal shaklni ko'rish uchun column bo'ylab pastga o'qing. Doimiy to'q rangdagi row — ishonchli tarzda kuchli oy, bu esa zaxirani rejalashtiruvchiga aynan kerak bo'lgan narsa.

4-chart — oydan oyga o'zgarish.

recent = m[(m["category"] == "Toys") & (m["year"] == 2024)].copy()

fig, ax = plt.subplots(figsize=(10, 4.5))
sns.barplot(data=recent, x="month_name", y="mom_pct",
            hue="mom_pct", palette="vlag", legend=False,
            errorbar=None, ax=ax)
ax.axhline(0, color="black", lw=1)
ax.set_xlabel("")
ax.set_ylabel("Change vs previous month (%)")
ax.set_title("Toys: month-over-month revenue change, 2024")
plt.tight_layout()

Nimaga e'tibor berish kerak: navbatma-navbat musbat va manfiy bar lar — bu beqarorlik emas, oddiy seasonality. Dekabr bar i va yanvar bar i kuzatish kerak bo'lgan juftlik — katta dekabr ko'tarilishidan keyingi katta yanvar tushishi ikkita emas, bitta hodisa.

4-qadam — topilma

• O'sish haqiqiy va u seasonality dan alohida. 12 oylik rolling mean butun oyna bo'ylab ko'tariladi, buni takrorlanuvchi seasonal pattern hosil qila olmaydi. Series boshi va oxiridagi rolling-mean qiymatini keltiring.
• Seasonality kategoriyalar bo'yicha keskin farq qiladi. Grocery yil davomida deyarli tekis; Toys va Clothing eng kuchli tebranadi. Demak, zaxirani rejalashtirish do'kon bo'yicha emas, kategoriya bo'yicha bo'lishi kerak.
• Dekabr — hodisa, trend emas. Har bir kategoriya dekabrdagi ko'tarilishni, keyin esa yanvardagi tushishni ko'rsatadi. Yanvardagi oydan oyga foizlar dahshatli ko'rinadi va hech narsani anglatmaydi; yanvarni oldingi yil yanvari bilan solishtiring.
• Bularning barchasi ostida haftalik pattern bor va oylik aggregation uni butunlay yashiradi — dam olish kunlari ish kunlaridan yuqori bo'ladi. Oylarga aggregate qilish — ma'lumotni yo'q qiladigan qaror va u ongli qaror bo'lishi kerak.

Bu loyihadagi gotcha lar

Kengaytirish topshiriqlari

1. LAG(revenue, 12) OVER (PARTITION BY category ORDER BY ym) yordamida yildan yilga column ini qo'shing va har bir kategoriya uchun YoY o'sishning lineplot ini chizing. Qaysi kategoriya foiz jihatidan eng tez o'syapti va bu absolyut ko'rsatkich bo'yicha eng tez o'sayotgani bilan bir xilmi?
2. Kunlik ma'lumotga qayting va har bir kategoriya uchun hafta kuni bo'yicha revenue boxplot ini chizing. Generator dagi weekend parametrini tiklang: qaysi kategoriyada dam olish kunlari effekti eng kuchli?
3. Bitta kategoriyani qo'lda trend, seasonal va residual komponentlarga ajrating: trend = 12 oylik markazlashtirilgan rolling mean; seasonal = har bir kalendar oyi uchun haqiqiy qiymatning trend ga o'rtacha nisbati; residual = qolgan hamma narsa. Uchalasini ham chizing.
4. Generator ni boshqa SEED bilan qayta ishga tushiring va oydan oyga tebranishlar o'zgargani holda trend va seasonality saqlanib qolishini tasdiqlang. Topilmalaringizning qaysi qismlari signal, qaysilari shovqin bo'lganini ikki jumlada tushuntiring.

24.6 4-loyiha: RFM bilan mijozlarni segmentation qilish

Biznes savoli

RFM — bu savolga berilgan eng qadimiy va eng mustahkam javob. Har bir mijozni uchta o'q bo'yicha baholang:

shop.db ni kengaytirish (bir marta, 24.5 dan keyin ishga tushiring)

rng = np.random.default_rng(SEED + 1)      # boshqa oqim, o'sha reproducibility

N_CUST   = 1200
END_DATE = np.datetime64("2024-12-31")

cust_id = np.arange(1, N_CUST + 1)
cities  = rng.choice(["Tashkent", "Samarkand", "Bukhara", "Namangan", "Andijan"],
                     size=N_CUST, p=[.40, .20, .15, .15, .10])

# ro'yxatdan o'tish oynaning dastlabki ~2.5 yiliga taqsimlangan
signup = np.datetime64("2022-01-01") + rng.integers(0, 900, N_CUST).astype("timedelta64[D]")

# yashirin sodiqlik -> tranzaksiyalar soni. Gamma realistik
# o'ngga qiya shakl beradi: ko'p yengil xaridor, bir nechta juda og'ir xaridor.
loyalty = rng.gamma(shape=2.0, scale=1.6, size=N_CUST)
n_txn   = rng.poisson(loyalty * 2.0) + 1        # hamma kamida bir marta xarid qiladi

# har bir mijoz ro'yxatdan o'tgach qancha muddat aktiv qolishi
active_days = 60 + rng.integers(0, 900, N_CUST)

# har bir tranzaksiya uchun bitta row ga yoyish (vektorlashtirilgan — Python loop yo'q)
cust_rep   = np.repeat(cust_id, n_txn)
signup_rep = np.repeat(signup, n_txn)
span_rep   = np.repeat(active_days, n_txn)

# **1.3 offset larni aktiv oynaning BOSHIGA qiyalatadi, shunda mijozlarning
# realistik ulushi hammasi joriy bo'lib qolish o'rniga uzilib qoladi
offset   = (rng.random(cust_rep.size) ** 1.3 * span_rep).astype(int)
txn_date = signup_rep + offset.astype("timedelta64[D]")

# savat qiymati: lognormal — sarf uchun standart shakl
amount = np.round(rng.lognormal(mean=3.4, sigma=0.6, size=cust_rep.size), 2)

keep = txn_date <= END_DATE                     # oynadan keyingi hamma narsani tashlang

txns = pd.DataFrame({
    "customer_id": cust_rep[keep],
    "txn_date":    pd.to_datetime(txn_date[keep]).strftime("%Y-%m-%d"),
    "amount":      amount[keep],
})
custs = pd.DataFrame({
    "customer_id": cust_id,
    "city":        cities,
    "signup_date": pd.to_datetime(signup).strftime("%Y-%m-%d"),
})

print(len(custs), len(txns))                    # ~1200 mijoz, ~8-9k tranzaksiya

conn = sqlite3.connect(DB_SHOP)
custs.to_sql("customers",    conn, if_exists="replace", index=False)
txns .to_sql("transactions", conn, if_exists="replace", index=False)
conn.execute("CREATE INDEX IF NOT EXISTS ix_txn_cust ON transactions(customer_id)")
conn.commit()
conn.close()

1200 8059

Nimani mashq qilasiz

• SQL: CTE lar (WITH), julianday bilan sana arifmetikasi, guruh bo'yicha MAX/COUNT/SUM, NTILE(5) window scoring (7-bob), belgilash uchun ko'p tarmoqli CASE va tranzaksiyasi yo'q mijozlar ovozsiz o'chirilmasligi uchun LEFT JOIN.
• pandas: tartiblangan categorical lar, crosstab, merge, quantile tekshiruvi.
• seaborn: hue va log scale lar bilan scatterplot, crosstab ning heatmap i, segment bo'yicha revenue barplot i.

1-qadam — ma'lumotni olish (SQL)

SQL_RFM = """ WITH base AS ( SELECT c.customer_id, c.city, MAX(t.txn_date) AS last_txn, COUNT(t.txn_date) AS frequency, -- column ustidan COUNT, COUNT(*) emas: tranzaksiyasi yo'q mijoz -- 1 emas, 0 ball olishi kerak (1-loyihadagi o'sha tuzoq) COALESCE(SUM(t.amount), 0) AS monetary, CAST(julianday('2024-12-31') - julianday(MAX(t.txn_date)) AS INTEGER) AS recency_days -- julianday() ISO sanani kun raqamiga aylantiradi, shuning uchun -- farq oddiy kunlar soni bo'ladi. Ma'lumotning oxirgi kuniga -- bog'lang, HECH QACHON bugunga emas: 'now' natijalarni har kuni o'zgartiradi. FROM customers AS c LEFT JOIN transactions AS t ON t.customer_id = c.customer_id -- LEFT hech qachon xarid qilmagan mijozlarni saqlaydi; ularning frequency si 0 -- va recency si NULL, buni keyinroq hal qilishimiz kerak GROUP BY c.customer_id, c.city ), active AS ( SELECT * FROM base WHERE frequency > 0 -- NULL recency ustidagi NTILE xarid qilmaganlarni tasodifiy joylashtirardi, -- shuning uchun faqat haqiqatan tranzaksiya qilgan mijozlarni baholang ), scored AS ( SELECT a.*, NTILE(5) OVER (ORDER BY a.recency_days DESC) AS r_score, -- DESC: eng KATTA recency (eng yomon mijoz) 1-tile ga tushadi, -- ya'ni tile 5 = eng yaqinda ko'ringan = eng yaxshi. Buni teskari qiling -- va loyihadagi har bir xulosa teskarisiga aylanadi. NTILE(5) OVER (ORDER BY a.frequency ASC) AS f_score, NTILE(5) OVER (ORDER BY a.monetary ASC) AS m_score -- ASC: eng yuqori frequency / sarf 5-tile ga tushadi = eng yaxshi FROM active AS a ) SELECT s.*, s.r_score + s.f_score + s.m_score AS rfm_total, CASE WHEN s.r_score >= 4 AND s.f_score >= 4 AND s.m_score >= 4 THEN 'Champions' WHEN s.r_score >= 4 AND s.f_score >= 3 THEN 'Loyal' WHEN s.r_score >= 4 AND s.f_score <= 2 THEN 'New / Promising' WHEN s.r_score = 3 THEN 'Needs attention' WHEN s.r_score <= 2 AND s.m_score >= 4 THEN 'At risk (high value)' ELSE 'Lapsed' END AS segment -- CASE BIRINCHI mos keladigan tarmoqda to'xtaydi, shuning uchun tartib muhim: -- eng aniq qoida birinchi kelishi kerak FROM scored AS s ORDER BY rfm_total DESC, s.monetary DESC; -- ORDER BY chiqish alias iga murojaat qilishi mumkin, lekin HECH QACHON qualified iga emas: --s.rfm_total"no such column" bilan xato beradi, chunki alias -- natijaga tegishli, s table iga emas """

rfm = q(SQL_RFM, db=DB_SHOP)
print(rfm.shape)
print(rfm["segment"].value_counts())
print(rfm[["recency_days", "frequency", "monetary"]].describe())

2-qadam — shakllantirish (pandas / numpy)

rfm.info()

seg_order = ["Champions", "Loyal", "New / Promising", "Needs attention",
 "At risk (high value)", "Lapsed"]
rfm["segment"] = pd.Categorical(rfm["segment"], categories=seg_order, ordered=True)
 # har bir chart va har bir value_counts dagi tartibni butunlay qat'iylashtiradi

rfm["last_txn"] = pd.to_datetime(rfm["last_txn"])
rfm["avg_order_value"] = rfm["monetary"] / rfm["frequency"]
 # xavfsiz: 

active

 CTE si frequency >= 1 ni kafolatlagan

# segment darajasidagi summary — stakeholder aslida o'qiydigan table
seg = (
 rfm.groupby("segment", as_index=False, observed=True)
 .agg(customers=("customer_id", "count"),
 revenue=("monetary", "sum"),
 avg_freq=("frequency", "mean"),
 avg_recency=("recency_days", "mean"),
 avg_order=("avg_order_value", "mean"))
)
seg["revenue_share_pct"] = np.round(100 * seg["revenue"] / seg["revenue"].sum(), 1)
seg["cust_share_pct"] = np.round(100 * seg["customers"] / seg["customers"].sum(), 1)

# R x F sanoq matritsasi
rf = pd.crosstab(rfm["r_score"], rfm["f_score"])

# uchta o'q haqiqatan turli narsalarni o'lchayaptimi?
print(rfm[["recency_days", "frequency", "monetary",
 "avg_order_value"]].corr(numeric_only=True).round(2))

3-qadam — o'rganish va vizualizatsiya (seaborn)

1-chart — frequency ga qarshi monetary, segment bo'yicha ranglangan.

fig, ax = plt.subplots(figsize=(9, 6))
sns.scatterplot(data=rfm, x="frequency", y="monetary",
                hue="segment", palette="deep",
                size="r_score", sizes=(20, 120),
                alpha=.75, edgecolor="none", ax=ax)
ax.set_yscale("log")            # sarf lognormal — chiziqli o'q plot ning 90% ini behuda sarflaydi
ax.set_xlabel("Number of purchases")
ax.set_ylabel("Total spend (log scale)")
ax.set_title("Customer base: frequency vs value")
ax.legend(bbox_to_anchor=(1.02, 1), loc="upper left", fontsize=8)
plt.tight_layout()

Nimaga e'tibor berish kerak: diagonal polosa — bu mexanik bog'liqlik (ko'proq xarid qilish ko'proq turadi). Qiziqarli mijozlar diagonaldan tashqarida: kam xarid, lekin juda katta sarf, yoki juda ko'p xarid, lekin arzimas qiymatda. Nuqta o'lchami recency ni kodlaydi, shuning uchun yuqorida va o'ngda joylashgan katta nuqtalar — champion lar.

2-chart — R × F grid i.

fig, ax = plt.subplots(figsize=(7, 5.5))
sns.heatmap(rf, annot=True, fmt="d", cmap="Purples",
            linewidths=.5, cbar_kws={"label": "customers"}, ax=ax)
ax.set_xlabel("Frequency score (5 = most frequent)")
ax.set_ylabel("Recency score (5 = most recent)")
ax.set_title("How many customers sit in each R x F cell?")
ax.invert_yaxis()               # eng yaxshi recency ni o'quvchilar kutgan joyga, yuqoriga qo'ying
plt.tight_layout()

Nimaga e'tibor berish kerak: ikkita burchak. Yuqori o'ng (yuqori R, yuqori F) — sodiq yadro; pastki o'ng (past R, yuqori F) — signal qo'ng'irog'i: ilgari tez-tez xarid qilib, endi to'xtagan mijozlar. Retention byudjeti aynan o'sha cell ga tegishli.

3-chart — segment bo'yicha revenue.

fig, ax = plt.subplots(figsize=(9, 5))
sns.barplot(data=seg, x="revenue", y="segment",
            hue="segment", palette="deep", legend=False,
            errorbar=None, ax=ax)
for i, row in enumerate(seg.itertuples()):
    ax.text(row.revenue, i,
            f"  {row.revenue_share_pct}% of revenue / {row.cust_share_pct}% of customers",
            va="center", fontsize=9)
ax.set_xlim(0, seg["revenue"].max() * 1.55)
ax.set_xlabel("Total revenue")
ax.set_ylabel("")
ax.set_title("Revenue and customer share by segment")
plt.tight_layout()

Nimaga e'tibor berish kerak: har bir bar dagi ikki foiz orasidagi farq. Mijozlarning 12% ini va revenue ning 35% ini ushlab turgan segment — himoya qilinishi kerak bo'lgani; teskari pattern esa tinch qo'yilishi kerak bo'lgani.

4-qadam — topilma

• Revenue to'plangan. Segmentlar bo'ylab revenue_share_pct ni cust_share_pct bilan solishtiring: yuqori segmentlar revenue dagi ulushi mijozlar ulushidan ancha yuqori bo'lgan qismni ushlab turadi. Ikkala raqamni birga keltiring — biri ikkinchisisiz topilma emas.
• "At risk (high value)" segmenti — kampaniyaning nishoni. Bu mijozlar yuqori darajalarda sarflagan, lekin yaqinda ko'rinmagan. Ularda to'lashga tayyorlik isbotlangan va qaytish uchun ma'lum sabab bor; yangi mijoz kampaniyasida esa ikkalasi ham yo'q.
• Frequency va monetary kuchli korrelyatsiyada — 2-qadamda chop etilgan korrelyatsiya matritsasiga qarang — ya'ni ular bir-birini takrorlaydigan ma'lumot tashiydi. Haqiqatan yangi narsa qo'shadigan o'q — recency, shuning uchun u segment qoidalarida birinchi turadi.
• Segment o'lchamlari qoidalarning natijasi, kashfiyot emas. NTILE har bir o'qda teng beshdan birlarni majburlagani uchun, CASE dagi bitta >= 4 ni >= 3 ga o'zgartirish yuzlab mijozni ko'chiradi. Natija bilan birga qoidani ham har doim keltiring.

Bu loyihadagi gotcha lar

Kengaytirish topshiriqlari

1. Hech qachon xarid qilmagan mijozlarni aniq ettinchi segment sifatida qaytarib qo'shing. Ular nechta va ularning mavjudligi siz keltirgan biror foizni o'zgartiradimi?
2. NTILE(5) ni qat'iy biznes chegaralari bilan almashtiring (masalan: recency 90 kundan kam, to'rtta yoki undan ko'p xarid, mediandan yuqori sarf). Ikki segmentation ni crosstab bilan solishtiring va qaysi mijozlar guruhini o'zgartirganini hamda nima uchun ekanini tasvirlang.
3. customers table idan city ni olib keling va shahar bo'yicha segment tarkibining stacked yoki guruhlangan barplot ini chizing. Champion ulushi shaharlar bo'ylab bir xilmi, yoki bitta shahar biznesni ko'tarib turibdimi?
4. 3-loyihadagi sales table ini RFM natijasiga oy bo'yicha join qiling va At-risk segmentining pasayishi biror aniq davrga to'g'ri kelishini tekshiring. Nima uchun bu korrelyatsiya bo'lib, sabab emasligini aniq ayting.

24.7 Baholash rubric i

Har bir loyiha uchun yigirma besh ball. Vazn taqsimoti ataylab taqdimotdan ko'ra to'g'rilik va interpretatsiyaga ustunlik beradi — noto'g'ri raqamning chiroyli chart i to'g'ri raqamning oddiy chart idan kamroq turadi.

Baho chegaralari

• 22–25 — professional. Yengil tahrir bilan stakeholder ga yuborsa bo'ladi.
• 18–21 — mustahkam. Tahlil to'g'ri, kommunikatsiya ustida ishlash kerak.
• 13–17 — mexanika ishlaydi, lekin kamida bitta tuzoqqa sezilmasdan tushib qolingan.
• 13 dan past — pipeline ishlaydi, lekin raqamlar qo'yilgan savolni tasvirlamaydi.

24.8 Xulosa

To'rtta loyiha, bitta pipeline, avtomatik bo'lib qolgunicha takrorlangan.

• 1-loyiha — revenue. Dars — LEFT JOIN va COUNT(column). Hech kim sotib olmagan kurs — table dagi eng muhim row, inner join esa uni o'chirib yuboradi.
• 2-loyiha — performance. Dars — NULL semantikasi. AVG yetishmayotgan grade larni o'tkazib yuboradi, NULL >= 60 na true, na false, rate esa faqat o'z maxraji yonida turganda talqin qilinadi.
• 3-loyiha — time series. Dars — trend ni season dan ajratish. Rolling yillik o'rtacha "biz o'syapmizmi?" degan savolga javob beradi; month × year heatmap "qachon zaxira qilishimiz kerak?" degan savolga javob beradi. Oydan oyga o'zgarish esa ikkalasiga ham javob bermaydi.
• 4-loyiha — segmentation. Dars shuki, segment — siz yozgan qoida, siz kashf qilgan fakt emas. NTILE qoidani nisbiy qiladi, sanani qat'iylashtirish uni reproducible qiladi, revenue ulushini mijoz ulushiga qarab keltirish esa uni foydali qiladi.

To'rttasi bo'ylab bir xil uchta odat qayta-qayta uchraydi va aynan shular bu qo'llanmadan olib chiqishga arziydigan odatlar:

I qism va II qismdagi texnikalar lug'at edi. Bu to'rtta loyiha esa o'sha lug'atda yozilgan birinchi paragraflar. Keyingilari sizning o'z student laringizning savollaridan chiqishi kerak — maktabning o'z dars jadvali ma'lumoti, mahalliy do'konning kassa cheklari, sport ligasining natijalar table i. Pipeline o'zgarmaydi; faqat savol o'zgaradi.