import re, glob import numpy as np, pandas as pd import matplotlib.pyplot as plt, seaborn as sns pd.set_option("display.max_columns", 40); pd.set_option("display.width", 160) sns.set_theme(style="whitegrid", font_scale=0.9) DATA_DIR = "." # folder berisi ws*.csv, day*.csv, dst. TASKS = { # nama tugas -> (file reguler, file late atau None) "WS1": ("ws1", "ws1-late"), "WS2": ("ws2", "ws2-late"), "WS3": ("ws3", "ws3-late"), "WS4": ("ws4", "ws4-late"), "AKKH": ("apa-kabar-kampung-halaman", "apa-kabar-kampung-halaman-late"), "Kapsul": ("kapsul-waktu", "kapsul-waktu-late"), } IUP_LAST_N = 4 # 4 keluarga terakhir tiap bata = keluarga IUP KEL_PER_BATA = 22 EXPECT_OVERRIDE = {} # isi mis. {"WS1": .95} kalau mau patok manual; kosong = median keluarga per grup (lihat seksi 2) DAYS = ["day1", "day2", "day3", "day4"]

def load_task(name): reg = pd.read_csv(f"{DATA_DIR}/{name}.csv") return reg.assign(sub=reg["status_pengumpulan"].ne("Belum Mengumpulkan")) rows = [] for task, (reg_f, late_f) in TASKS.items(): r = load_task(reg_f)[["nim", "bata", "keluarga", "fakultas", "nama", "sub", "nilai", "is_flagged", "tipe"]] r = r.rename(columns={"sub": "sub_reg", "nilai": "nilai_reg"}) if late_f: l = load_task(late_f)[["nim", "sub", "nilai"]].rename(columns={"sub": "sub_late", "nilai": "nilai_late"}) r = r.merge(l, on="nim", how="left") else: r["sub_late"], r["nilai_late"] = False, 0 r["task"] = task rows.append(r) sub = pd.concat(rows, ignore_index=True) sub["submitted"] = sub["sub_reg"] | sub["sub_late"] sub["late_only"] = ~sub["sub_reg"] & sub["sub_late"] sub["duplikat"] = sub["sub_reg"] & sub["sub_late"] # late diabaikan, reguler yang berlaku sub["graded"] = (sub["nilai_reg"].fillna(0) + sub["nilai_late"].fillna(0)) > 0 sub["task"] = pd.Categorical(sub["task"], list(TASKS)) # kehadiran harian att = [] for d in DAYS: df = pd.read_csv(f"{DATA_DIR}/{d}.csv") pres = df.filter(regex="(?i)presensi|ruang") att.append(pd.DataFrame({"nim": df["NIM"], "day": d, "hadir": pres.eq("hadir").any(axis=1), "izin": pres.eq("izin").any(axis=1) & ~pres.eq("hadir").any(axis=1)})) att = pd.concat(att, ignore_index=True) master = sub[["nim", "bata", "keluarga", "fakultas", "nama"]].drop_duplicates("nim") master["grup"] = np.where((master["keluarga"] - 1) % KEL_PER_BATA >= KEL_PER_BATA - IUP_LAST_N, "IUP", "Reguler") sub = sub.merge(master[["nim", "grup"]], on="nim") N = len(master) print(f"{N} maba | {master.bata.nunique()} bata | {master.keluarga.nunique()} keluarga | {sub.task.nunique()} tugas | " f"IUP: {(master.grup=='IUP').sum()} maba")

qc = sub.groupby("task", observed=True).agg( submit=("submitted", "sum"), reg_only=("sub_reg", lambda s: (s & ~sub.loc[s.index, "sub_late"]).sum()), late_only=("late_only", "sum"), duplikat=("duplikat", "sum"), flagged=("is_flagged", "sum"), graded=("graded", "sum")) qc["rate"] = (qc["submit"] / N).round(3) qc["late_share"] = (qc["late_only"] / qc["submit"]).round(3) # porsi submisi yang masuk lewat slot late display(qc) # duplikat: submit reguler DAN late -> entri late-nya dihapus/diabaikan dup = sub[sub["duplikat"]][["task", "nim", "nama", "bata", "keluarga", "nilai_reg", "nilai_late"]].sort_values(["task", "bata", "keluarga"]) dup.to_csv("nim_duplikat_late.csv", index=False) print(f"{len(dup)} entri late duplikat disimpan ke nim_duplikat_late.csv (hapus dari slot late). " f"{int((dup.nilai_late>0).sum())} di antaranya keburu dinilai mentor di slot late — nilai itu harus dipindah/diabaikan.") # rate dengan denominator 'maba aktif' (hadir >= 1 hari) aktif = att.groupby("nim")["hadir"].any() sub["aktif"] = sub["nim"].map(aktif) den = pd.DataFrame({ "rate_semua": sub.groupby("task", observed=True)["submitted"].mean(), "rate_aktif": sub[sub.aktif].groupby("task", observed=True)["submitted"].mean(), "rate_hadir4": sub[sub.nim.isin(att.groupby("nim")["hadir"].sum().pipe(lambda s: s[s == 4]).index)].groupby("task", observed=True)["submitted"].mean(), }).round(3) print(f"Maba aktif (hadir >=1 hari): {int(aktif.sum())} dari {N} ({aktif.mean():.1%})") display(den.style.format("{:.1%}").set_caption("Tingkat submisi menurut denominator")) # ekspektasi: P75 tingkat submisi keluarga per grup, kecuali di-override rate_kel_g = sub.groupby(["grup", "keluarga", "task"], observed=True)["submitted"].mean().unstack("task") EXPECT = (rate_kel_g.groupby(level="grup").median() * 20).round() / 20 # median keluarga per grup, dibulatkan ke 5% for t, v in EXPECT_OVERRIDE.items(): EXPECT[t] = v display(EXPECT.rename(columns=str).style.format("{:.0%}").set_caption("EXPECT = median keluarga per grup (dibulatkan 5%)")) print(sub.groupby(["grup", "task"], observed=True)["submitted"].mean().unstack().round(3).rename(columns=str).to_string())

rate_bt = sub.groupby(["grup", "bata", "task"], observed=True)["submitted"].mean().unstack("task") * 100 fig, axes = plt.subplots(2, 1, figsize=(15, 9), sharex=True) for ax, grp in zip(axes, ["Reguler", "IUP"]): r = rate_bt.loc[grp] r.plot(kind="bar", ax=ax, width=0.85, edgecolor="none", legend=(grp == "Reguler")) ax.axhline(100, color="black", lw=1, label="Kapasitas (100%)") ax.axhline(r.stack().mean(), color="tab:red", lw=1.2, ls="--", label=f"Rerata ({r.stack().mean():.1f}%)") ax.axhline(r.stack().median(), color="tab:orange", lw=1.2, ls=":", label=f"Median ({r.stack().median():.1f}%)") ax.set(ylabel="% maba submit", ylim=(0, 105), title=f"{grp} — submisi per bata per tugas") ax.legend(ncol=3, loc="lower left", fontsize=8) axes[1].set_xlabel("Bata"); plt.xticks(rotation=0); plt.tight_layout() gap = pd.concat({g: (rate_bt.loc[g] - EXPECT.loc[g] * 100) for g in ["Reguler", "IUP"]}, names=["grup"]).round(1) display(gap.style.background_gradient(cmap="RdYlGn", vmin=-25, vmax=10).set_caption("Selisih (poin %) terhadap EXPECT grupnya — merah = di bawah ekspektasi"))

ws = rate_bt.loc["Reguler", ["WS1", "WS2", "WS3", "WS4"]] fig, ax = plt.subplots(figsize=(8, 4.5)) for b, r in ws.iterrows(): ax.plot(r.index, r.values, marker="o", lw=1, alpha=.55, label=f"Bata {b}") ax.plot(ws.columns, ws.mean(), color="black", lw=2.5, marker="s", label="Rerata Reguler") ax.plot(ws.columns, rate_bt.loc["IUP", ws.columns].mean(), color="gray", lw=2.5, marker="s", ls="--", label="Rerata IUP") ax.set(ylabel="% submit", title="Penurunan submisi worksheet per bata"); ax.legend(ncol=4, fontsize=7); plt.tight_layout() drop = (ws["WS1"] - ws["WS4"]).sort_values(ascending=False).round(1) print("Penurunan WS1→WS4 (poin %) per bata (Reguler):\n", drop.to_string())

rate_kel = sub.groupby(["grup", "bata", "keluarga", "task"], observed=True)["submitted"].mean().unstack("task") * 100 rate_kel["rerata"] = rate_kel.mean(axis=1) rk = rate_kel.reset_index() fig, ax = plt.subplots(figsize=(13, 4.5)) sns.boxplot(data=rk, x="bata", y="rerata", hue="grup", hue_order=["Reguler", "IUP"], fliersize=0, ax=ax) sns.stripplot(data=rk, x="bata", y="rerata", hue="grup", hue_order=["Reguler", "IUP"], dodge=True, size=3, alpha=.6, palette="dark", legend=False, ax=ax) ax.set(ylabel="Rerata % submit (6 tugas)", title="Sebaran tingkat submisi keluarga di dalam tiap bata (Reguler vs IUP)"); plt.tight_layout() print("Keluarga terendah di grupnya (selisih terhadap median grup, poin %) — kandidat follow-up mentor:") rk["vs_median_grup"] = rk["rerata"] - rk.groupby("grup")["rerata"].transform("median") display(rk.sort_values("vs_median_grup").head(15).round(1).set_index(["grup", "bata", "keluarga"]))

late = sub.groupby(["bata", "task"], observed=True).apply( lambda g: g["late_only"].sum() / max(g["submitted"].sum(), 1) * 100, include_groups=False).unstack() ax = late.plot(kind="bar", figsize=(12, 4), width=.8, title="% submisi yang masuk lewat slot late (dari total yang submit)") ax.set(ylabel="%", xlabel="Bata"); plt.xticks(rotation=0); plt.tight_layout()

fig, axes = plt.subplots(1, 2, figsize=(13, 4.8)) corr = rate_kel[list(TASKS)].corr(method="spearman") # semua keluarga; grup IUP memperkuat korelasi ini sns.heatmap(corr, annot=True, fmt=".2f", cmap="Blues", vmin=0, vmax=1, ax=axes[0]) axes[0].set_title("Spearman antar tugas (level keluarga)") days_att = att.groupby("nim")["hadir"].sum().rename("hari_hadir") n_sub = sub.groupby("nim")["submitted"].sum().rename("tugas_submit") stu = master.set_index("nim").join([days_att, n_sub]) ct = pd.crosstab(stu["hari_hadir"], stu["tugas_submit"]) sns.heatmap(ct, annot=True, fmt="d", cmap="Greens", ax=axes[1], cbar=False) axes[1].set(title="Maba: hari hadir (0–4) vs jumlah tugas submit (0–6)", xlabel="tugas submit", ylabel="hari hadir") plt.tight_layout() print(f"Spearman hari_hadir vs tugas_submit: {stu[['hari_hadir','tugas_submit']].corr('spearman').iloc[0,1]:.2f}") print("Rerata tugas submit menurut hari hadir:\n", stu.groupby("hari_hadir")["tugas_submit"].agg(["mean", "count"]).round(2).to_string())

rate_fak = sub.groupby(["grup", "fakultas", "task"], observed=True)["submitted"].mean().unstack("task") * 100 rate_fak["rerata"] = rate_fak.mean(axis=1) display(rate_fak.sort_values(["grup", "rerata"], ascending=[False, False]).round(1) .style.background_gradient(cmap="RdYlGn", axis=None).set_caption("Per fakultas, dipisah grup (SBM & STEI dominan di IUP)")) tipe = (sub[sub.submitted].assign(tipe=sub["tipe"].fillna("(late-only)")) .groupby(["task", "tipe"], observed=True).size().unstack(fill_value=0)) display(tipe)

zero = stu[stu["tugas_submit"] == 0].copy() zero["profil"] = pd.cut(zero["hari_hadir"], [-1, 0, 2, 4], labels=["hadir 0 hari", "hadir 1–2 hari", "hadir 3–4 hari"]) print(f"{len(zero)} maba 0 submisi.\n", pd.crosstab(zero["profil"], zero["grup"], margins=True).to_string()) print("\nPorsi IUP di 0-submisi: {:.0%} vs porsi IUP populasi: {:.0%}".format((zero.grup=="IUP").mean(), (master.grup=="IUP").mean())) print("\n0-submisi per bata & profil:\n", pd.crosstab(zero["bata"], zero["profil"]).to_string()) risk = stu[(stu["tugas_submit"] == 0) | (stu["hari_hadir"] <= 1)].sort_values(["tugas_submit", "hari_hadir"]) print(f"\n{len(risk)} maba: 0 tugas ATAU hadir ≤1 hari.") print("Distribusi jumlah tugas submit semua maba:\n", stu["tugas_submit"].value_counts().sort_index().to_string()) display(risk.head(20)) risk.to_csv("maba_perlu_perhatian.csv")

prog = sub[sub.submitted].groupby(["bata", "task"], observed=True)["graded"].mean().unstack("task") * 100 display(prog.round(1).style.background_gradient(cmap="Purples", vmin=0, vmax=100).set_caption("% submisi yang sudah punya nilai > 0"))

nws = sub[sub.task.isin(["WS1", "WS2", "WS3", "WS4"])].groupby("nim")["submitted"].sum().rename("n_ws") ext = sub.merge(nws, on="nim") non_reg = ext[~ext["sub_reg"]] print("Kontribusi slot late (poin % dari seluruh maba) dan uptake di antara yang belum submit reguler:") tbl = ext.groupby("task", observed=True).agg(reg=("sub_reg", "mean"), late=("late_only", "mean"), total=("submitted", "mean")) tbl["uptake_nonreg"] = non_reg.groupby("task", observed=True)["late_only"].mean() display((tbl * 100).round(1)) print("\nUptake late menurut jumlah WS yang sudah diselesaikan maba (0–4):") display((non_reg.groupby(["task", "n_ws"], observed=True)["late_only"].mean().unstack() * 100).round(0)) fig, axes = plt.subplots(1, 2, figsize=(14, 4.5)) up_b = non_reg.groupby(["task", "grup", "bata"], observed=True)["late_only"].mean().unstack("bata") * 100 up_b.loc[["AKKH", "Kapsul"]].T.plot(kind="bar", ax=axes[0], width=.8) axes[0].set(title="Uptake late per bata (AKKH & Kapsul, dipisah grup)", ylabel="% dari yang belum submit reguler", xlabel="Bata") axes[0].legend(fontsize=7, ncol=2); axes[0].tick_params(axis="x", rotation=0) up_k = non_reg.groupby(["task", "keluarga"], observed=True)["late_only"].agg(["mean", "size"]) sns.histplot(data=(up_k["mean"] * 100).reset_index(), x="mean", hue="task", bins=20, element="step", ax=axes[1]) axes[1].set(title="Sebaran uptake late per keluarga", xlabel="% uptake late di keluarga") plt.tight_layout() for t in ["AKKH", "Kapsul"]: k = up_k.loc[t] print(f"\n{t}: {int((k['mean']==0).sum())} keluarga uptake 0% (tidak ada satu pun yang masuk lewat late), " f"{int((k['mean']>=.5).sum())} keluarga uptake >=50%, dari {len(k)} keluarga.") zero_push = up_k[(up_k["mean"] == 0) & (up_k["size"] >= 5)].reset_index().merge(master[["keluarga", "bata", "grup"]].drop_duplicates(), on="keluarga") zero_push.to_csv("keluarga_uptake_late_nol.csv", index=False) print(f"{len(zero_push)} baris keluarga (>=5 maba belum submit, 0 yang masuk lewat late) disimpan ke keluarga_uptake_late_nol.csv — ini daftar untuk korbata tagih mentor.")

import os HEATMAP_DIR = "heatmap"; os.makedirs(HEATMAP_DIR, exist_ok=True) TASK_LABEL = {"WS1": "Worksheet 1", "WS2": "Worksheet 2", "WS3": "Worksheet 3", "WS4": "Worksheet 4", "AKKH": "Apa Kabar Kampung Halaman", "Kapsul": "Kapsul Waktu"} VERSI = {"gabungan": ("submitted", "Gabungan (reguler + late, unik)"), "reguler": ("sub_reg", "Reguler saja (tepat waktu)"), "late": ("late_only", "Late saja (tanpa duplikat)")} sub["ke_n"] = (sub["keluarga"] - 1) % KEL_PER_BATA + 1 kel_size = master.groupby("keluarga").size() def heatmap_keluarga(task, versi, save=True): col, vlabel = VERSI[versi] d = sub[sub.task == task] cnt = d.groupby(["bata", "ke_n"])[col].sum().unstack("ke_n") tot = d.groupby(["bata", "ke_n"])["nim"].count().unstack("ke_n") pct = cnt / tot * 100 kelid = d.groupby(["bata", "ke_n"])["keluarga"].first().unstack("ke_n") fig, ax = plt.subplots(figsize=(16, 5.2)) if versi == "late": cmap, vmin, vmax, center, cbar_label = "Blues", 0, max(30, np.nanmax(pct.values)), None, "% anggota keluarga yang masuk lewat late" else: cmap, vmin, vmax, center, cbar_label = "RdYlGn", 40, 100, pct.stack().mean(), f"% mengumpulkan (putih = rata-rata {pct.stack().mean():.1f}%)" sns.heatmap(pct, annot=cnt.astype(int), fmt="d", cmap=cmap, vmin=vmin, vmax=vmax, center=center, linewidths=.8, linecolor="white", annot_kws={"size": 7}, cbar_kws={"label": cbar_label}, ax=ax) ax.axvline(KEL_PER_BATA - IUP_LAST_N, color="black", lw=1.5) # batas IUP ax.text(KEL_PER_BATA - IUP_LAST_N + 0.1, -0.3, "IUP →", fontsize=8, va="bottom") flat = pct.stack(); flat.index = [int(kelid.loc[b, k]) for b, k in flat.index] worst = " | terendah: " + ", ".join(f"kel {k} ({v:.0f}%)" for k, v in flat.nsmallest(3).items()) if versi != "late" else "" ax.set(title=f"{TASK_LABEL[task]} — {vlabel}{worst}", xlabel="keluarga ke-n dalam bata", ylabel="bata") ax.tick_params(axis="y", rotation=0); plt.tight_layout() if save: fig.savefig(f"{HEATMAP_DIR}/{task}_{versi}.png", dpi=130); plt.close(fig) return pct for t in TASKS: for v in VERSI: heatmap_keluarga(t, v) print(f"{len(TASKS)*len(VERSI)} gambar disimpan ke {HEATMAP_DIR}/ (pola nama: <tugas>_<versi>.png)") # rekap 6 tugas dalam satu peta: rata-rata % submit gabungan avg = sub.groupby(["bata", "ke_n"])["submitted"].mean().unstack("ke_n") * 100 fig, ax = plt.subplots(figsize=(16, 5.2)) sns.heatmap(avg, annot=True, fmt=".0f", cmap="RdYlGn", vmin=40, vmax=100, center=avg.stack().mean(), linewidths=.8, linecolor="white", annot_kws={"size": 7}, cbar_kws={"label": "rata-rata % submit 6 tugas"}, ax=ax) ax.axvline(KEL_PER_BATA - IUP_LAST_N, color="black", lw=1.5) ax.set(title="Rekap — rata-rata % submisi 6 tugas per keluarga (gabungan)", xlabel="keluarga ke-n dalam bata", ylabel="bata") ax.tick_params(axis="y", rotation=0); plt.tight_layout(); fig.savefig(f"{HEATMAP_DIR}/rekap_6tugas_gabungan.png", dpi=130)