Spaces:
Runtime error
Runtime error
| # -*- coding: utf-8 -*- | |
| """ | |
| TKM Dashboard — MSI pooled + Exclusion (STRICT) + Penyesuaian Sampel ala IPLM | |
| + Normalisasi MIN–MAX GLOBAL di LEVEL RESPONDEN sebelum agregasi wilayah | |
| + Export Excel + Export Word (.docx) dengan TABEL Interpretasi & Rekomendasi (Pra/Saat/Pasca/Indeks TKM) | |
| UPDATE UTAMA (sesuai instruksi terakhir Anda): | |
| ✅ Interpretasi & rekomendasi sekarang SELALU menyesuaikan: | |
| - NILAI (angka) dan KATEGORI (sangat rendah / rendah / sedang / tinggi / sangat tinggi) | |
| ✅ Isi antar kategori dibuat “beda signifikan”: | |
| - sangat rendah: pemulihan dasar (foundation recovery) | |
| - rendah : penguatan terstruktur (SOP + kapasitas) | |
| - sedang : stabilisasi mutu (QA, konsistensi, pemerataan) | |
| - tinggi : pemantapan + replikasi selektif | |
| - sangat tinggi: praktik unggul + skalabilitas & keberlanjutan | |
| ✅ Catatan n<400 (disesuaikan) hanya muncul bila memang baris berada pada kelompok disesuaikan. | |
| ✅ Tabel Word menampilkan per baris: | |
| Pra / Saat / Pasca / Indeks → interpretasi & rekomendasi yang relevan dengan kategori baris itu. | |
| Catatan: | |
| - FULL CODE (no ringkas). | |
| """ | |
| from pathlib import Path | |
| from typing import Dict, List, Tuple, Optional | |
| from datetime import datetime | |
| import re | |
| import numpy as np | |
| import pandas as pd | |
| from scipy import stats | |
| import matplotlib.pyplot as plt | |
| import gradio as gr | |
| # docx | |
| try: | |
| from docx import Document | |
| from docx.shared import Pt, Inches | |
| from docx.enum.text import WD_ALIGN_PARAGRAPH | |
| from docx.enum.table import WD_TABLE_ALIGNMENT, WD_ALIGN_VERTICAL | |
| DOCX_AVAILABLE = True | |
| except Exception: | |
| DOCX_AVAILABLE = False | |
| import openpyxl # noqa: F401 | |
| np.random.seed(42) | |
| # ========================= | |
| # KONFIGURASI | |
| # ========================= | |
| DATA_PATH = "DATA_TKM_28_JANUARI_2026.xlsx" | |
| WEIGHTS = {"pra": 0.15, "saat": 0.50, "pasca": 0.35} | |
| LIKERT_MIN, LIKERT_MAX = 1, 4 | |
| MIN_FRAC_AVAILABLE_PER_SUBINDEX = 0.50 | |
| MIN_RESPONDEN_SLOVIN = 400 | |
| # ========================= | |
| # EXCLUSION LIST (STRICT) | |
| # ========================= | |
| EXCLUDE_PROVINSI_DROP = [] | |
| EXCLUDE_PROVINSI_NO_AGG = [ | |
| "Bali", | |
| "Papua Barat Daya", | |
| "Papua Pegunungan", | |
| "Papua Selatan", | |
| "Papua Tengah", | |
| ] | |
| EXCLUDE_KABKOTA_BY_PROV_TYPE: List[Tuple[str, str, str]] = [ | |
| ("Bali", "kab", "Bangli"), | |
| ("Bali", "kab", "Jembrana"), | |
| ("Sumatera Utara", "kab", "Humbang Hasundutan"), | |
| ("Lampung", "kab", "Pesisir Barat"), | |
| ("Papua", "kab", "Biak Numfor"), | |
| ("Papua Tengah", "kab", "Nabire"), | |
| ("Jawa Barat", "kab", "Tasikmalaya"), | |
| ("Kalimantan Timur", "kab", "Mahakam Ulu"), | |
| ("Papua Barat", "kab", "Sorong Selatan"), | |
| ("Papua Barat Daya", "kab", "Tambrauw"), | |
| ("Maluku Utara", "kab", "Halmahera Timur"), | |
| ("Papua", "kab", "Mamberamo Raya"), | |
| ("Papua Tengah", "kab", "Puncak"), | |
| ("Maluku", "kab", "Seram Bagian Barat"), | |
| ] | |
| # ========================= | |
| # AUTO-DETECT KOLOM WILAYAH | |
| # ========================= | |
| def _norm(s: str) -> str: | |
| return ( | |
| str(s).strip().lower() | |
| .replace(" ", "") | |
| .replace("-", "") | |
| .replace("/", "") | |
| .replace("\\", "") | |
| .replace(".", "") | |
| .replace(",", "") | |
| ) | |
| def detect_region_cols(df: pd.DataFrame) -> Tuple[str, str]: | |
| cols = list(df.columns) | |
| norm_map = {_norm(c): c for c in cols} | |
| prov_candidates = ["provinsiasal", "provinsi asal", "provinsi", "province", "namaprovinsi", "prov"] | |
| kab_candidates = [ | |
| "kabkota", "kab_kota", "kabkotaasal", "kabupatenkota", "kabupatenkotaasal", | |
| "kab/kota", "kabupaten/kota", "kabupaten", "kota", "kab" | |
| ] | |
| prov_col = None | |
| for cand in prov_candidates: | |
| if _norm(cand) in norm_map: | |
| prov_col = norm_map[_norm(cand)] | |
| break | |
| kab_col = None | |
| for cand in kab_candidates: | |
| if _norm(cand) in norm_map: | |
| kab_col = norm_map[_norm(cand)] | |
| break | |
| if prov_col is None or kab_col is None: | |
| raise ValueError( | |
| "Kolom wilayah tidak terdeteksi.\n" | |
| f"Kolom tersedia (contoh 40): {list(df.columns)[:40]}\n" | |
| ) | |
| return prov_col, kab_col | |
| # ========================= | |
| # NORMALISASI NAMA WILAYAH | |
| # ========================= | |
| def norm_region_name(x: str) -> str: | |
| if pd.isna(x): | |
| return "" | |
| s = str(x).strip().lower() | |
| s = re.sub(r"[^\w\s]", " ", s) | |
| s = re.sub(r"\s+", " ", s).strip() | |
| for pfx in ["provinsi ", "propinsi "]: | |
| if s.startswith(pfx): | |
| s = s[len(pfx):].strip() | |
| return s | |
| def split_kabkota_type_name(x: str) -> Tuple[str, str]: | |
| if pd.isna(x): | |
| return ("", "") | |
| raw = str(x).strip().lower() | |
| s = re.sub(r"[^\w\s]", " ", raw) | |
| s = re.sub(r"\s+", " ", s).strip() | |
| if s.startswith("kabupaten "): | |
| return ("kab", s[len("kabupaten "):].strip()) | |
| if s.startswith("kab. "): | |
| return ("kab", s[len("kab. "):].strip()) | |
| if s.startswith("kab "): | |
| return ("kab", s[len("kab "):].strip()) | |
| if s.startswith("kota. "): | |
| return ("kota", s[len("kota. "):].strip()) | |
| if s.startswith("kota "): | |
| return ("kota", s[len("kota "):].strip()) | |
| return ("", s) | |
| def apply_exclusions_strict(df: pd.DataFrame, prov_col: str, kab_col: str): | |
| d = df.copy() | |
| d["_prov_norm"] = d[prov_col].apply(norm_region_name) | |
| tmp = d[kab_col].apply(split_kabkota_type_name) | |
| d["_kab_type"] = tmp.apply(lambda t: t[0]) | |
| d["_kab_name"] = tmp.apply(lambda t: t[1]) | |
| d["_kab_name_norm"] = d["_kab_name"].apply(norm_region_name) | |
| ex_prov_drop = set(norm_region_name(p) for p in EXCLUDE_PROVINSI_DROP) | |
| mask_prov_drop = d["_prov_norm"].isin(ex_prov_drop) | |
| ex_triples = set( | |
| (norm_region_name(p), str(t).strip().lower(), norm_region_name(n)) | |
| for p, t, n in EXCLUDE_KABKOTA_BY_PROV_TYPE | |
| ) | |
| mask_triple = pd.Series(False, index=d.index) | |
| m_has_type = d["_kab_type"].isin(["kab", "kota"]) | |
| mask_triple.loc[m_has_type] = [ | |
| (pv, kt, kn) in ex_triples | |
| for pv, kt, kn in zip( | |
| d.loc[m_has_type, "_prov_norm"], | |
| d.loc[m_has_type, "_kab_type"].str.lower(), | |
| d.loc[m_has_type, "_kab_name_norm"], | |
| ) | |
| ] | |
| # pengecualian Tasikmalaya: jangan hapus "Kota Tasikmalaya" bila yang di-exclude kab | |
| raw_kab = d[kab_col].astype(str).str.lower() | |
| is_tasik = (d["_prov_norm"] == norm_region_name("Jawa Barat")) & (d["_kab_name_norm"] == norm_region_name("Tasikmalaya")) | |
| tasik_is_kota = raw_kab.str.contains(r"^\s*kota\b", regex=True) | |
| mask_triple = mask_triple & ~(is_tasik & tasik_is_kota) | |
| mask_exclude = mask_prov_drop | mask_triple | |
| stats_info = { | |
| "baris_awal": int(len(d)), | |
| "terhapus_total": int(mask_exclude.sum()), | |
| "terhapus_prov_drop": int(mask_prov_drop.sum()), | |
| "terhapus_kabkota_drop": int((~mask_prov_drop & mask_triple).sum()), | |
| "diolah": int((~mask_exclude).sum()), | |
| } | |
| audit_hapus = d.loc[mask_exclude, [prov_col, kab_col]].copy() | |
| out = d.loc[~mask_exclude].copy().reset_index(drop=True) | |
| out.drop(columns=["_prov_norm", "_kab_type", "_kab_name", "_kab_name_norm"], inplace=True, errors="ignore") | |
| return out, stats_info, audit_hapus | |
| # ========================= | |
| # UTIL | |
| # ========================= | |
| def mean_if_enough(row: pd.Series, min_frac: float) -> float: | |
| non_na = row.dropna() | |
| if len(row) == 0: | |
| return np.nan | |
| return float(non_na.mean()) if (len(non_na) / len(row) >= min_frac) else np.nan | |
| def detect_item_groups(columns: List[str]) -> Dict[str, List[str]]: | |
| cols = [str(c).strip() for c in columns] | |
| return { | |
| "pra": [c for c in cols if str(c).strip().upper().startswith("B")], | |
| "saat": [c for c in cols if str(c).strip().upper().startswith("C")], | |
| "pasca": [c for c in cols if str(c).strip().upper().startswith("D")], | |
| } | |
| def faktor_penyesuaian(n: int, target: int = MIN_RESPONDEN_SLOVIN) -> float: | |
| try: | |
| n = int(n) | |
| except Exception: | |
| return np.nan | |
| if target <= 0: | |
| return np.nan | |
| return float(min(max(n, 0) / target, 1.0)) | |
| def status_penyesuaian(n: int) -> str: | |
| f = faktor_penyesuaian(n) | |
| if not np.isfinite(f): | |
| return "" | |
| if f >= 1.0: | |
| return f"✅ n≥{MIN_RESPONDEN_SLOVIN} (tanpa penyesuaian)" | |
| return f"⚠️ n<{MIN_RESPONDEN_SLOVIN} (disesuaikan ×{f:.2f})" | |
| def kategori_indeks_final(score_0_100: float) -> str: | |
| x = pd.to_numeric(score_0_100, errors="coerce") | |
| if not np.isfinite(x): | |
| return "" | |
| if x < 50: | |
| return "sangat rendah" | |
| elif x < 65: | |
| return "rendah" | |
| elif x < 80: | |
| return "sedang" | |
| elif x < 90: | |
| return "tinggi" | |
| else: | |
| return "sangat tinggi" | |
| def _fmt2(x: float) -> str: | |
| v = pd.to_numeric(x, errors="coerce") | |
| return f"{float(v):.2f}" if np.isfinite(v) else "—" | |
| def _is_adjusted(status_sampel: str) -> bool: | |
| s = (status_sampel or "").strip() | |
| return ("⚠️" in s) or ("n<" in s.lower()) | |
| def _gap_profile(pra: float, saat: float, pasca: float) -> str: | |
| arr = np.array([pd.to_numeric(pra, errors="coerce"), | |
| pd.to_numeric(saat, errors="coerce"), | |
| pd.to_numeric(pasca, errors="coerce")], dtype=float) | |
| arr = arr[np.isfinite(arr)] | |
| if len(arr) < 2: | |
| return "tidak diketahui" | |
| gap = float(arr.max() - arr.min()) | |
| if gap >= 20: | |
| return "kesenjangan lebar" | |
| if gap >= 10: | |
| return "kesenjangan sedang" | |
| return "relatif seimbang" | |
| def _weakest_phase(pra: float, saat: float, pasca: float) -> str: | |
| vals = { | |
| "Pra": pd.to_numeric(pra, errors="coerce"), | |
| "Saat": pd.to_numeric(saat, errors="coerce"), | |
| "Pasca": pd.to_numeric(pasca, errors="coerce"), | |
| } | |
| clean = {k: float(v) for k, v in vals.items() if np.isfinite(v)} | |
| if not clean: | |
| return "lintas fase" | |
| return min(clean.items(), key=lambda kv: kv[1])[0] | |
| # ========================= | |
| # MSI pooled per fase | |
| # ========================= | |
| def compute_msi_mapping_from_values(values: pd.Series, | |
| min_cat: int = LIKERT_MIN, | |
| max_cat: int = LIKERT_MAX) -> Dict[int, float]: | |
| s = pd.to_numeric(values, errors="coerce").dropna().astype(int) | |
| if s.empty: | |
| return {cat: np.nan for cat in range(min_cat, max_cat + 1)} | |
| cats = list(range(min_cat, max_cat + 1)) | |
| N = len(s) | |
| counts = np.array([(s == cat).sum() for cat in cats], dtype=float) | |
| p = counts / N | |
| cum_p = np.cumsum(p) | |
| boundaries = np.concatenate([[0.0], cum_p]) | |
| eps = 0.5 / N | |
| boundaries[0] = max(boundaries[0], eps) | |
| boundaries[-1] = min(boundaries[-1], 1 - eps) | |
| z = stats.norm.ppf(boundaries) | |
| msi_vals = [(z[i] + z[i + 1]) / 2.0 for i in range(len(cats))] | |
| return {cat: float(val) for cat, val in zip(cats, msi_vals)} | |
| def apply_msi_pooled_phase(df_phase: pd.DataFrame, cols: List[str]) -> Tuple[pd.DataFrame, Dict[int, float]]: | |
| tmp = df_phase.copy() | |
| pooled = pd.Series(tmp[cols].values.ravel()) | |
| mapping = compute_msi_mapping_from_values(pooled) | |
| for c in cols: | |
| tmp[c] = pd.to_numeric(tmp[c], errors="coerce").map(mapping) | |
| return tmp, mapping | |
| # ========================= | |
| # MIN–MAX GLOBAL | |
| # ========================= | |
| def minmax_0_100_global(x: pd.Series) -> Tuple[pd.Series, float, float]: | |
| s = pd.to_numeric(x, errors="coerce") | |
| minv = float(s.min(skipna=True)) | |
| maxv = float(s.max(skipna=True)) | |
| if not np.isfinite(minv) or not np.isfinite(maxv) or maxv <= minv: | |
| y = pd.Series(np.nan, index=s.index) | |
| return y, minv, maxv | |
| z = (s - minv) / (maxv - minv) | |
| z = z.clip(0, 1) | |
| y = (z * 100).round(2) | |
| return y, minv, maxv | |
| # ========================= | |
| # AGREGASI | |
| # ========================= | |
| def weighted_mean(values: pd.Series, weights: pd.Series) -> float: | |
| v = pd.to_numeric(values, errors="coerce") | |
| w = pd.to_numeric(weights, errors="coerce") | |
| m = v.notna() & w.notna() & (w > 0) | |
| if not m.any(): | |
| return np.nan | |
| return float(np.average(v[m], weights=w[m])) | |
| def aggregate_prov_from_kab(kab_df: pd.DataFrame, prov_col: str) -> pd.DataFrame: | |
| rows = [] | |
| for prov, g in kab_df.groupby(prov_col, dropna=False): | |
| n_series = pd.to_numeric(g["n_responden"], errors="coerce").fillna(0) | |
| any_adjusted = (n_series < MIN_RESPONDEN_SLOVIN).any() | |
| rows.append({ | |
| prov_col: prov, | |
| "tkm_final_mean": weighted_mean(g["Indeks_TKM_0_100_final"], g["n_responden"]), | |
| "n_responden": int(n_series.sum()), | |
| "prov_status": ( | |
| f"⚠️ Ada Kab/Kota n<{MIN_RESPONDEN_SLOVIN} (nilai Kab/Kota disesuaikan)" | |
| if any_adjusted else | |
| f"✅ Semua Kab/Kota n≥{MIN_RESPONDEN_SLOVIN} (tanpa penyesuaian)" | |
| ) | |
| }) | |
| return pd.DataFrame(rows) | |
| # ========================= | |
| # TEMPLATE KEBIJAKAN (ADAPTIF PER KATEGORI) | |
| # ========================= | |
| def _frame_kategori(kat: str) -> Dict[str, str]: | |
| if kat == "sangat rendah": | |
| return { | |
| "label": "pemulihan dasar", | |
| "tekanan": "membangun fondasi program minimum yang terukur", | |
| "resiko": "risiko kegagalan program tinggi bila fondasi tidak dibenahi", | |
| "arah": "quick wins + paket minimum layanan", | |
| } | |
| if kat == "rendah": | |
| return { | |
| "label": "penguatan terstruktur", | |
| "tekanan": "standardisasi pelaksanaan dan penguatan kapasitas pelaksana", | |
| "resiko": "risiko inkonsistensi tinggi bila SOP dan pembinaan lemah", | |
| "arah": "SOP + pelatihan + pembinaan periodik", | |
| } | |
| if kat == "sedang": | |
| return { | |
| "label": "stabilisasi mutu", | |
| "tekanan": "kontrol mutu, konsistensi, dan pemerataan antar lokasi/pelaksana", | |
| "resiko": "risiko stagnasi bila mutu tidak distabilkan", | |
| "arah": "QA + monitoring rutin + perbaikan berbasis data", | |
| } | |
| if kat == "tinggi": | |
| return { | |
| "label": "pemantapan & replikasi selektif", | |
| "tekanan": "mempertahankan mutu dan memperluas praktik baik secara terkurasi", | |
| "resiko": "risiko penurunan bila kontrol mutu melemah", | |
| "arah": "pemeliharaan mutu + inovasi terarah", | |
| } | |
| return { | |
| "label": "praktik unggul & skalabilitas", | |
| "tekanan": "menjaga keberlanjutan dan memperluas dampak dengan kelembagaan", | |
| "resiko": "risiko utama pada keberlanjutan dan ketergantungan aktor", | |
| "arah": "institusionalisasi + kemitraan + replikasi luas", | |
| } | |
| def interpretasi_dimensi_adaptif(dimensi: str, | |
| nilai: float, | |
| kategori: str, | |
| status_sampel: str) -> str: | |
| """ | |
| Interpretasi netral-deskriptif, tetapi isi dan arah kalimat menyesuaikan kategori baris. | |
| """ | |
| f = _frame_kategori(kategori) | |
| ncat = f"“{kategori}”" | |
| vv = _fmt2(nilai) | |
| note = "" | |
| if _is_adjusted(status_sampel): | |
| note = " Catatan: baris berada pada kelompok n<400 (disesuaikan), sehingga nilai dipengaruhi faktor penyesuaian sampel." | |
| if dimensi == "Pra Membaca": | |
| return ( | |
| f"Subindeks Pra ({vv}) pada kategori {ncat} menggambarkan kondisi kesiapan sebelum membaca. " | |
| f"Pada tahap {f['label']}, fokus kebijakan diarahkan pada {f['tekanan']} terkait perencanaan sesi, kurasi bahan bacaan, " | |
| f"serta kesiapan fasilitator/materi agar pelaksanaan tahap inti dapat berjalan konsisten.{note}" | |
| ) | |
| if dimensi == "Saat Membaca": | |
| return ( | |
| f"Subindeks Saat ({vv}) pada kategori {ncat} menggambarkan mutu pelaksanaan inti saat sesi membaca berlangsung. " | |
| f"Pada tahap {f['label']}, kebutuhan utama berada pada {f['tekanan']} terkait strategi keterlibatan pembaca, fasilitasi, " | |
| f"dan penguatan pemahaman selama proses agar sesi efektif dan berulang.{note}" | |
| ) | |
| if dimensi == "Pasca Membaca": | |
| return ( | |
| f"Subindeks Pasca ({vv}) pada kategori {ncat} menggambarkan tindak lanjut setelah membaca. " | |
| f"Pada tahap {f['label']}, arah kebijakan menekankan {f['tekanan']} agar tindak lanjut tidak hanya terjadi, " | |
| f"tetapi menghasilkan keluaran yang memperkuat kebiasaan membaca berkelanjutan.{note}" | |
| ) | |
| # Indeks TKM | |
| return ( | |
| f"Indeks TKM ({vv}) pada kategori {ncat} merefleksikan capaian komposit lintas fase (pra–saat–pasca). " | |
| f"Pada tahap {f['label']}, kebijakan perlu difokuskan pada {f['tekanan']} agar capaian lintas fase terkonsolidasi " | |
| f"menjadi perilaku membaca yang lebih stabil pada skala populasi.{note}" | |
| ) | |
| def rekomendasi_dimensi_adaptif(dimensi: str, | |
| nilai: float, | |
| kategori: str, | |
| pra: float, | |
| saat: float, | |
| pasca: float) -> str: | |
| """ | |
| Rekomendasi spesifik per dimensi dan berbeda signifikan antar kategori. | |
| """ | |
| vv = _fmt2(nilai) | |
| f = _frame_kategori(kategori) | |
| # ===== PRA ===== | |
| if dimensi == "Pra Membaca": | |
| if kategori == "sangat rendah": | |
| return ( | |
| f"Paket {f['label']} Pra Membaca ({vv}):\n" | |
| "1) Susun kurasi minimum “starter pack” bacaan per titik layanan (berbasis sasaran).\n" | |
| "2) Terapkan rencana sesi 1 halaman + checklist kesiapan (buku, alat bantu, pertanyaan pemantik, penataan ruang).\n" | |
| "3) Pelatihan singkat fasilitator fokus persiapan (2–3 jam) agar standar minimum tercapai.\n" | |
| "Indikator: % sesi punya rencana sesi & judul tercatat; jumlah titik punya starter pack." | |
| ) | |
| if kategori == "rendah": | |
| return ( | |
| f"Paket {f['label']} Pra Membaca ({vv}):\n" | |
| "1) Standardisasi SOP perencanaan sesi + format rencana sesi seragam.\n" | |
| "2) Pelatihan fasilitator + coaching awal untuk memastikan penerapan SOP.\n" | |
| "3) Tetapkan penanggung jawab kualitas di tiap titik layanan.\n" | |
| "Indikator: kepatuhan SOP; audit rencana sesi berkala; ketersediaan paket bacaan." | |
| ) | |
| if kategori == "sedang": | |
| return ( | |
| f"Paket {f['label']} Pra Membaca ({vv}):\n" | |
| "1) Quality assurance perencanaan: review berkala rencana sesi & kurasi bacaan.\n" | |
| "2) Perkuat konsistensi lintas lokasi (materi standar + pembinaan berbasis umpan balik).\n" | |
| "3) Pemerataan kesiapan: pastikan titik layanan dengan nilai lebih rendah mendapat pendampingan.\n" | |
| "Indikator: variasi antar titik menurun; kepatuhan rencana sesi meningkat; distribusi paket bacaan merata." | |
| ) | |
| if kategori == "tinggi": | |
| return ( | |
| f"Paket {f['label']} Pra Membaca ({vv}):\n" | |
| "1) Pertahankan standar melalui QA rutin dan dokumentasi praktik baik.\n" | |
| "2) Optimalkan kurasi berbasis data peminjaman/akses dan profil sasaran.\n" | |
| "Indikator: mutu rencana sesi stabil; kepuasan fasilitator/peserta meningkat." | |
| ) | |
| return ( | |
| f"Paket {f['label']} Pra Membaca ({vv}):\n" | |
| "1) Institusionalisasikan standar kesiapan ke seluruh jejaring layanan.\n" | |
| "2) Replikasi model pelatihan fasilitator + modul siap pakai lintas wilayah.\n" | |
| "Indikator: replikasi bertambah; standar tetap terjaga; keberlanjutan pendanaan/SDM." | |
| ) | |
| # ===== SAAT ===== | |
| if dimensi == "Saat Membaca": | |
| if kategori == "sangat rendah": | |
| return ( | |
| f"Paket {f['label']} Saat Membaca ({vv}):\n" | |
| "1) Terapkan modul fasilitasi inti (membaca nyaring + tanya-jawab terstruktur + aktivitas 5 menit).\n" | |
| "2) Panduan langkah-demi-langkah untuk sesi agar pelaksana tidak bergantung improvisasi.\n" | |
| "3) Jadwal rutin minimal (mis. 2 sesi/minggu per titik) agar terbentuk kebiasaan.\n" | |
| "Indikator: frekuensi sesi; % sesi menggunakan pertanyaan pemantik; repeat attendance." | |
| ) | |
| if kategori == "rendah": | |
| return ( | |
| f"Paket {f['label']} Saat Membaca ({vv}):\n" | |
| "1) Standarkan teknik fasilitasi dan alat bantu (lembar aktivitas, daftar kosakata kunci).\n" | |
| "2) Coaching berbasis observasi ringan (5 aspek) + umpan balik periodik.\n" | |
| "3) Penguatan tata laksana ruang baca (zona nyaman, aturan sederhana, jadwal tetap).\n" | |
| "Indikator: kepatuhan standar; kualitas interaksi meningkat; partisipasi ulang naik." | |
| ) | |
| if kategori == "sedang": | |
| return ( | |
| f"Paket {f['label']} Saat Membaca ({vv}):\n" | |
| "1) QA pelaksanaan: peer review antar fasilitator + supervisi berkala.\n" | |
| "2) Perbaikan berbasis data: gunakan log sesi (judul, metode, peserta, umpan balik) untuk koreksi rutin.\n" | |
| "3) Pemerataan mutu: fokus pendampingan pada titik dengan variasi kualitas tinggi.\n" | |
| "Indikator: variasi mutu turun; kepuasan peserta meningkat; konsistensi metode terjaga." | |
| ) | |
| if kategori == "tinggi": | |
| return ( | |
| f"Paket {f['label']} Saat Membaca ({vv}):\n" | |
| "1) Pertahankan mutu melalui kontrol rutin dan inovasi aktivitas terarah.\n" | |
| "2) Replikasi selektif praktik baik ke titik yang tertinggal.\n" | |
| "Indikator: mutu stabil; inovasi tidak menurunkan konsistensi." | |
| ) | |
| return ( | |
| f"Paket {f['label']} Saat Membaca ({vv}):\n" | |
| "1) Skalakan model fasilitasi sebagai rujukan (pelatihan-of-trainers).\n" | |
| "2) Bangun standar kompetensi fasilitator + sertifikasi internal sederhana.\n" | |
| "Indikator: kapasitas meluas; kualitas tetap terjaga lintas wilayah." | |
| ) | |
| # ===== PASCA ===== | |
| if dimensi == "Pasca Membaca": | |
| if kategori == "sangat rendah": | |
| return ( | |
| f"Paket {f['label']} Pasca Membaca ({vv}):\n" | |
| "1) Aktifkan tindak lanjut minimum: refleksi singkat + tugas ringan (jurnal/kartu refleksi).\n" | |
| "2) Jadwalkan tindak lanjut terstruktur agar tidak sporadis.\n" | |
| "3) Hubungkan tindak lanjut dengan akses bacaan (pinjam/pojok baca/e-book legal).\n" | |
| "Indikator: % peserta melakukan tindak lanjut; retensi; peminjaman/akses meningkat." | |
| ) | |
| if kategori == "rendah": | |
| return ( | |
| f"Paket {f['label']} Pasca Membaca ({vv}):\n" | |
| "1) Standarkan format tindak lanjut (resume singkat, kartu refleksi, tantangan baca).\n" | |
| "2) Libatkan keluarga/sekolah/komunitas sebagai dukungan kebiasaan.\n" | |
| "3) Pelacakan sederhana (log tindak lanjut) untuk memastikan konsistensi.\n" | |
| "Indikator: kepatuhan tindak lanjut; retensi; akses bacaan pasca sesi." | |
| ) | |
| if kategori == "sedang": | |
| return ( | |
| f"Paket {f['label']} Pasca Membaca ({vv}):\n" | |
| "1) QA tindak lanjut: evaluasi keluaran (resume/refleksi) dan keterkaitan dengan akses bacaan.\n" | |
| "2) Perbaikan berbasis umpan balik peserta untuk menjaga dampak.\n" | |
| "3) Pemerataan kualitas tindak lanjut lintas titik layanan.\n" | |
| "Indikator: kualitas keluaran meningkat; kebiasaan membaca lebih berulang; variasi antar titik menurun." | |
| ) | |
| if kategori == "tinggi": | |
| return ( | |
| f"Paket {f['label']} Pasca Membaca ({vv}):\n" | |
| "1) Perkaya modul tindak lanjut (diskusi tematik, klub baca) sambil menjaga kontrol mutu.\n" | |
| "2) Replikasi selektif paket tindak lanjut ke titik yang tertinggal.\n" | |
| "Indikator: keberlanjutan terjaga; kualitas stabil." | |
| ) | |
| return ( | |
| f"Paket {f['label']} Pasca Membaca ({vv}):\n" | |
| "1) Institusionalisasikan tindak lanjut sebagai kultur (komunitas/klub baca berjejaring).\n" | |
| "2) Skalakan jejaring dukungan lintas OPD/komunitas untuk keberlanjutan.\n" | |
| "Indikator: dampak meluas; retensi tinggi; sistem dukungan kuat." | |
| ) | |
| # ===== INDEKS ===== | |
| gap = _gap_profile(pra, saat, pasca) | |
| weakest = _weakest_phase(pra, saat, pasca) | |
| if kategori == "sangat rendah": | |
| return ( | |
| f"Rencana {f['label']} untuk Indeks TKM ({vv}):\n" | |
| "A. Quick wins 90 hari (terukur)\n" | |
| "1) Standarisasi “Siklus Membaca 3 Fase” di semua titik layanan (Pra→Saat→Pasca).\n" | |
| "2) Re-fokus pada fase terlemah (utama: Pra & Saat) melalui pelatihan fasilitator + paket bacaan + panduan fasilitasi.\n" | |
| "3) Target output jelas (mis. minimal 2 sesi/minggu per titik + minimal 1 tindak lanjut per sesi).\n" | |
| f"Catatan teknis: profil antarfase {gap}; fase terlemah saat ini: {weakest}." | |
| ) | |
| if kategori == "rendah": | |
| return ( | |
| f"Rencana {f['label']} untuk Indeks TKM ({vv}):\n" | |
| "1) SOP pelaksanaan lintas fase + pembinaan periodik untuk memastikan standar dijalankan.\n" | |
| "2) Penguatan kapasitas pelaksana (TOT fasilitator, jadwal rutin, log pelaksanaan).\n" | |
| "3) Monitoring rutin berbasis indikator minimum (jumlah sesi, peserta, judul bacaan, tindak lanjut).\n" | |
| f"Catatan teknis: profil antarfase {gap}; fase terlemah saat ini: {weakest}." | |
| ) | |
| if kategori == "sedang": | |
| return ( | |
| f"Rencana {f['label']} untuk Indeks TKM ({vv}):\n" | |
| "1) Quality assurance lintas fase (review rencana sesi, observasi fasilitasi, evaluasi tindak lanjut).\n" | |
| "2) Monitoring rutin + perbaikan berbasis data untuk menurunkan variasi antar titik layanan.\n" | |
| "3) Pemerataan: pendampingan ditargetkan pada titik dengan nilai fase terlemah.\n" | |
| f"Catatan teknis: profil antarfase {gap}; fase terlemah saat ini: {weakest}." | |
| ) | |
| if kategori == "tinggi": | |
| return ( | |
| f"Rencana {f['label']} untuk Indeks TKM ({vv}):\n" | |
| "1) Pertahankan kontrol mutu dan konsistensi layanan.\n" | |
| "2) Replikasi selektif praktik baik ke titik yang tertinggal.\n" | |
| "3) Inovasi terarah tanpa mengurangi standar minimum.\n" | |
| f"Catatan teknis: profil antarfase {gap}; fase terlemah saat ini: {weakest}." | |
| ) | |
| return ( | |
| f"Rencana {f['label']} untuk Indeks TKM ({vv}):\n" | |
| "1) Institusionalisasikan program (kebijakan daerah, penganggaran berkelanjutan, standar SDM fasilitator).\n" | |
| "2) Skalakan jejaring titik layanan (perpusda+TBM+sekolah+kelurahan+ruang publik).\n" | |
| "3) Sistem monitoring sederhana yang stabil sebagai mekanisme akuntabilitas.\n" | |
| f"Catatan teknis: profil antarfase {gap}; fase terlemah saat ini: {weakest}." | |
| ) | |
| # ========================= | |
| # TABEL INTERPRETASI & REKOMENDASI (ADAPTIF) | |
| # ========================= | |
| def build_interpretasi_rekom_table_adaptif(sub_pra: float, | |
| sub_saat: float, | |
| sub_pasca: float, | |
| indeks_final: float, | |
| status_sampel: str) -> pd.DataFrame: | |
| k_pra = kategori_indeks_final(sub_pra) | |
| k_saat = kategori_indeks_final(sub_saat) | |
| k_pasca = kategori_indeks_final(sub_pasca) | |
| k_indeks = kategori_indeks_final(indeks_final) | |
| rows = [ | |
| {"No": 1, "Dimensi": "Pra Membaca", "Nilai": sub_pra, "Kategori": k_pra}, | |
| {"No": 2, "Dimensi": "Saat Membaca", "Nilai": sub_saat, "Kategori": k_saat}, | |
| {"No": 3, "Dimensi": "Pasca Membaca","Nilai": sub_pasca, "Kategori": k_pasca}, | |
| {"No": 4, "Dimensi": "Indeks TKM", "Nilai": indeks_final, "Kategori": k_indeks}, | |
| ] | |
| df = pd.DataFrame(rows) | |
| interps, reks = [], [] | |
| for _, r in df.iterrows(): | |
| dim = str(r["Dimensi"]) | |
| val = float(pd.to_numeric(r["Nilai"], errors="coerce")) if pd.notna(r["Nilai"]) else np.nan | |
| kat = str(r["Kategori"]) | |
| interps.append(interpretasi_dimensi_adaptif(dim, val, kat, status_sampel)) | |
| reks.append(rekomendasi_dimensi_adaptif(dim, val, kat, sub_pra, sub_saat, sub_pasca)) | |
| df["Interpretasi"] = interps | |
| df["Rekomendasi"] = reks | |
| return df | |
| # ========================= | |
| # DOCX TABLE HELPERS | |
| # ========================= | |
| def _docx_set_cell(cell, text: str, bold: bool = False, align_center: bool = False): | |
| cell.text = "" if text is None else str(text) | |
| for p in cell.paragraphs: | |
| for run in p.runs: | |
| run.bold = bold | |
| if align_center: | |
| p.alignment = WD_ALIGN_PARAGRAPH.CENTER | |
| cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER | |
| def add_interpretasi_rekom_table_docx(doc: "Document", df_table: pd.DataFrame): | |
| doc.add_heading("Tabel Interpretasi dan Rekomendasi Kebijakan (Pra/Saat/Pasca/Indeks TKM)", level=2) | |
| if df_table is None or df_table.empty: | |
| doc.add_paragraph("Tidak ada data untuk tabel interpretasi & rekomendasi.") | |
| doc.add_paragraph("") | |
| return | |
| cols = ["No", "Dimensi", "Nilai", "Kategori", "Interpretasi", "Rekomendasi"] | |
| df2 = df_table[cols].copy() | |
| table = doc.add_table(rows=1, cols=len(cols)) | |
| table.alignment = WD_TABLE_ALIGNMENT.CENTER | |
| widths = [Inches(0.5), Inches(1.3), Inches(0.85), Inches(1.0), Inches(3.0), Inches(3.0)] | |
| try: | |
| for i, w in enumerate(widths): | |
| table.columns[i].width = w | |
| except Exception: | |
| pass | |
| hdr = table.rows[0].cells | |
| for j, c in enumerate(cols): | |
| _docx_set_cell(hdr[j], c, bold=True, align_center=True) | |
| for _, r in df2.iterrows(): | |
| cells = table.add_row().cells | |
| _docx_set_cell(cells[0], str(int(r["No"])) if pd.notna(r["No"]) else "", align_center=True) | |
| _docx_set_cell(cells[1], str(r["Dimensi"]) if pd.notna(r["Dimensi"]) else "", align_center=False) | |
| v = pd.to_numeric(r["Nilai"], errors="coerce") | |
| _docx_set_cell(cells[2], f"{float(v):.2f}" if np.isfinite(v) else "", align_center=True) | |
| _docx_set_cell(cells[3], str(r["Kategori"]) if pd.notna(r["Kategori"]) else "", align_center=True) | |
| _docx_set_cell(cells[4], str(r["Interpretasi"]) if pd.notna(r["Interpretasi"]) else "", align_center=False) | |
| _docx_set_cell(cells[5], str(r["Rekomendasi"]) if pd.notna(r["Rekomendasi"]) else "", align_center=False) | |
| doc.add_paragraph("") | |
| # ========================= | |
| # PLOT HELPERS | |
| # ========================= | |
| def empty_figure(msg: str): | |
| fig, ax = plt.subplots(figsize=(8, 3)) | |
| ax.text(0.5, 0.5, msg, ha="center", va="center") | |
| ax.axis("off") | |
| fig.tight_layout() | |
| return fig | |
| def plot_dimensi_bar_0_100(pra_0_100: float, saat_0_100: float, pasca_0_100: float, title: str): | |
| fig, ax = plt.subplots(figsize=(6, 4)) | |
| labels = ["Pra (0–100)", "Saat (0–100)", "Pasca (0–100)"] | |
| vals = [pra_0_100, saat_0_100, pasca_0_100] | |
| ax.bar(labels, vals) | |
| ax.set_ylabel("Skor (0–100)") | |
| ax.set_title(title) | |
| ax.set_ylim(0, 100) | |
| ax.grid(True, linestyle="--", alpha=0.25) | |
| for i, v in enumerate(vals): | |
| if pd.isna(v) or not np.isfinite(v): | |
| continue | |
| ax.text(i, v + 1.2, f"{v:.2f}", ha="center", va="bottom") | |
| fig.tight_layout() | |
| return fig | |
| # ========================= | |
| # LOAD DATA + HITUNG SEKALI | |
| # ========================= | |
| DATA_FILE = Path(DATA_PATH) | |
| if not DATA_FILE.exists(): | |
| raise FileNotFoundError(f"File data tidak ditemukan: {DATA_PATH}") | |
| df_raw = pd.read_excel(DATA_FILE) | |
| PROV_COL, KABKOT_COL = detect_region_cols(df_raw) | |
| df_raw[PROV_COL] = df_raw[PROV_COL].astype(str).fillna("").replace({"nan": ""}).str.strip() | |
| df_raw[KABKOT_COL] = df_raw[KABKOT_COL].astype(str).fillna("").replace({"nan": ""}).str.strip() | |
| df_clean, excl_stats, audit_hapus = apply_exclusions_strict(df_raw, PROV_COL, KABKOT_COL) | |
| GROUP_COLS = detect_item_groups(df_clean.columns.tolist()) | |
| df_idx = df_clean.copy() | |
| phase_maps: Dict[str, Dict[int, float]] = {} | |
| for phase in ["pra", "saat", "pasca"]: | |
| cols = GROUP_COLS.get(phase, []) | |
| if not cols: | |
| df_idx[f"subidx_{phase}_msi"] = np.nan | |
| phase_maps[phase] = {1: np.nan, 4: np.nan} | |
| continue | |
| tmp = df_idx[cols].copy() | |
| tmp_msi, mapping = apply_msi_pooled_phase(tmp, cols) | |
| phase_maps[phase] = mapping | |
| df_idx[f"subidx_{phase}_msi"] = tmp_msi.apply( | |
| lambda r: mean_if_enough(r, MIN_FRAC_AVAILABLE_PER_SUBINDEX), | |
| axis=1 | |
| ) | |
| df_idx["subidx_pra_0_100"], MIN_PRA, MAX_PRA = minmax_0_100_global(df_idx["subidx_pra_msi"]) | |
| df_idx["subidx_saat_0_100"], MIN_SAAT, MAX_SAAT = minmax_0_100_global(df_idx["subidx_saat_msi"]) | |
| df_idx["subidx_pasca_0_100"],MIN_PAS, MAX_PAS = minmax_0_100_global(df_idx["subidx_pasca_msi"]) | |
| w_sum = float(sum(WEIGHTS.values())) | |
| df_idx["index_msi_raw"] = ( | |
| WEIGHTS["pra"] * df_idx["subidx_pra_msi"] + | |
| WEIGHTS["saat"] * df_idx["subidx_saat_msi"] + | |
| WEIGHTS["pasca"]* df_idx["subidx_pasca_msi"] | |
| ) / w_sum | |
| df_idx["index_0_100"], MIN_DATA, MAX_DATA = minmax_0_100_global(df_idx["index_msi_raw"]) | |
| # ========================= | |
| # CORE PIPELINE PER FILTER | |
| # ========================= | |
| def compute_outputs(provinsi: str, kabkota: str): | |
| sub = df_idx.copy() | |
| if provinsi != "(Semua)": | |
| sub = sub[sub[PROV_COL] == provinsi] | |
| if kabkota != "(Semua)": | |
| sub = sub[sub[KABKOT_COL] == kabkota] | |
| if sub.empty: | |
| hero = "## Ringkasan Eksekutif\n⚠️ Tidak ada data pada filter ini." | |
| info = "⚠️ Tidak ada data." | |
| empty = pd.DataFrame() | |
| return ( | |
| hero, info, | |
| empty, empty, empty, | |
| empty, pd.DataFrame(columns=["Komponen", "Nilai"]), | |
| empty_figure("Pilih Kab/Kota."), | |
| ) | |
| kab = ( | |
| sub.groupby([PROV_COL, KABKOT_COL], dropna=False) | |
| .agg( | |
| indeks_mean=("index_0_100", "mean"), | |
| n_responden=(KABKOT_COL, "size"), | |
| subidx_pra_0_100_mean=("subidx_pra_0_100", "mean"), | |
| subidx_saat_0_100_mean=("subidx_saat_0_100", "mean"), | |
| subidx_pasca_0_100_mean=("subidx_pasca_0_100", "mean"), | |
| ) | |
| .reset_index() | |
| ) | |
| kab["_faktor_internal"] = kab["n_responden"].apply(faktor_penyesuaian) | |
| kab["status_sampel"] = kab["n_responden"].apply(status_penyesuaian) | |
| kab["_raw_internal"] = pd.to_numeric(kab["indeks_mean"], errors="coerce") | |
| kab["Indeks_TKM_0_100_final"] = (kab["_raw_internal"] * kab["_faktor_internal"]).round(2) | |
| kab["Kategori_Indeks_TKM_FINAL"] = kab["Indeks_TKM_0_100_final"].apply(kategori_indeks_final) | |
| out_kab_all = kab.rename(columns={PROV_COL: "Provinsi", KABKOT_COL: "Wilayah"})[ | |
| [ | |
| "Provinsi", | |
| "Wilayah", | |
| "subidx_pra_0_100_mean", | |
| "subidx_saat_0_100_mean", | |
| "subidx_pasca_0_100_mean", | |
| "Indeks_TKM_0_100_final", | |
| "Kategori_Indeks_TKM_FINAL", | |
| "n_responden", | |
| "status_sampel", | |
| ] | |
| ].rename( | |
| columns={ | |
| "subidx_pra_0_100_mean": "SubIndeks_Pra_0_100", | |
| "subidx_saat_0_100_mean": "SubIndeks_Saat_0_100", | |
| "subidx_pasca_0_100_mean": "SubIndeks_Pasca_0_100", | |
| "Indeks_TKM_0_100_final": "Indeks_TKM_0_100", | |
| } | |
| ).sort_values(["Provinsi", "Wilayah"]) | |
| out_kab_valid = out_kab_all[out_kab_all["n_responden"] >= MIN_RESPONDEN_SLOVIN] | |
| out_kab_adjusted = out_kab_all[out_kab_all["n_responden"] < MIN_RESPONDEN_SLOVIN] | |
| prov_from_kab = aggregate_prov_from_kab(kab, PROV_COL).rename(columns={PROV_COL: "Provinsi"}) | |
| prov_from_kab["Indeks_TKM_0_100"] = pd.to_numeric(prov_from_kab["tkm_final_mean"], errors="coerce").round(2) | |
| prov_from_kab["Kategori_Indeks_TKM_FINAL"] = prov_from_kab["Indeks_TKM_0_100"].apply(kategori_indeks_final) | |
| prov_from_kab["Status"] = prov_from_kab["prov_status"] | |
| no_agg = set(norm_region_name(p) for p in EXCLUDE_PROVINSI_NO_AGG) | |
| mask_no_agg = prov_from_kab["Provinsi"].map(norm_region_name).isin(no_agg) | |
| prov_from_kab.loc[mask_no_agg, "Indeks_TKM_0_100"] = np.nan | |
| prov_from_kab.loc[mask_no_agg, "Kategori_Indeks_TKM_FINAL"] = "" | |
| prov_from_kab.loc[mask_no_agg, "Status"] = "🚫 EXCLUDED (kelembagaan): provinsi ditampilkan tetapi agregat tidak dihitung" | |
| out_prov = prov_from_kab[["Provinsi", "Indeks_TKM_0_100", "Kategori_Indeks_TKM_FINAL", "n_responden", "Status"]] | |
| hero = ( | |
| "## Ringkasan Eksekutif\n" | |
| f"- Exclusion (STRICT) sebelum MSI: terhapus **{excl_stats['terhapus_total']}** dari **{excl_stats['baris_awal']}**, diolah **{excl_stats['diolah']}**.\n" | |
| f"- Penyesuaian sampel Kab/Kota: target **{MIN_RESPONDEN_SLOVIN}**; n<{MIN_RESPONDEN_SLOVIN} → indeks dikali faktor min(n/{MIN_RESPONDEN_SLOVIN},1).\n" | |
| ) | |
| info = f"Responden pada filter: **{len(sub)}**" | |
| # detail plot untuk kab/kota terpilih | |
| detail_plot = empty_figure("Pilih Kab/Kota untuk melihat SubIndeks.") | |
| detail_df = pd.DataFrame(columns=["Komponen", "Nilai"]) | |
| if kabkota != "(Semua)": | |
| row = out_kab_all[out_kab_all["Wilayah"] == kabkota].head(1) | |
| if not row.empty: | |
| r0 = row.iloc[0] | |
| detail_df = pd.DataFrame([ | |
| {"Komponen": "SubIndeks_Pra_0_100", "Nilai": r0["SubIndeks_Pra_0_100"]}, | |
| {"Komponen": "SubIndeks_Saat_0_100", "Nilai": r0["SubIndeks_Saat_0_100"]}, | |
| {"Komponen": "SubIndeks_Pasca_0_100", "Nilai": r0["SubIndeks_Pasca_0_100"]}, | |
| {"Komponen": "Indeks_TKM_0_100", "Nilai": r0["Indeks_TKM_0_100"]}, | |
| {"Komponen": "Kategori_Indeks_TKM_FINAL", "Nilai": r0["Kategori_Indeks_TKM_FINAL"]}, | |
| {"Komponen": "n_responden", "Nilai": r0["n_responden"]}, | |
| {"Komponen": "status_sampel", "Nilai": r0["status_sampel"]}, | |
| ]) | |
| pra_v = pd.to_numeric(r0["SubIndeks_Pra_0_100"], errors="coerce") | |
| saat_v = pd.to_numeric(r0["SubIndeks_Saat_0_100"], errors="coerce") | |
| pas_v = pd.to_numeric(r0["SubIndeks_Pasca_0_100"], errors="coerce") | |
| detail_plot = plot_dimensi_bar_0_100( | |
| float(pra_v) if np.isfinite(pra_v) else np.nan, | |
| float(saat_v) if np.isfinite(saat_v) else np.nan, | |
| float(pas_v) if np.isfinite(pas_v) else np.nan, | |
| f"SubIndeks 0–100 — {kabkota}" | |
| ) | |
| return ( | |
| hero, info, | |
| out_kab_valid, out_prov, out_kab_adjusted, | |
| out_kab_all, | |
| detail_df, detail_plot | |
| ) | |
| # ========================= | |
| # EXPORT WORD | |
| # ========================= | |
| def _now_tag() -> str: | |
| return datetime.now().strftime("%Y%m%d_%H%M%S") | |
| def export_word_report(provinsi: str, kabkota: str): | |
| if not DOCX_AVAILABLE: | |
| out = str(Path.cwd() / f"LAPORAN_TKM_{_now_tag()}.txt") | |
| with open(out, "w", encoding="utf-8") as f: | |
| f.write("python-docx tidak tersedia. Install: pip install python-docx\n") | |
| return out | |
| hero, info, out_kab_valid, out_prov, out_kab_adjusted, out_kab_all, detail_df, _ = compute_outputs(provinsi, kabkota) | |
| chosen = None | |
| if kabkota != "(Semua)": | |
| pick = out_kab_all[out_kab_all["Wilayah"] == kabkota].head(1) | |
| if not pick.empty: | |
| chosen = pick.iloc[0] | |
| else: | |
| if len(out_kab_all) == 1: | |
| chosen = out_kab_all.iloc[0] | |
| doc = Document() | |
| style = doc.styles["Normal"] | |
| style.font.name = "Calibri" | |
| style.font.size = Pt(11) | |
| title = doc.add_paragraph("TABEL INTERPRETASI DAN REKOMENDASI") | |
| title.runs[0].bold = True | |
| title.alignment = WD_ALIGN_PARAGRAPH.CENTER | |
| subp = doc.add_paragraph(f"Tanggal: {datetime.now().strftime('%d %B %Y')} | Filter: Provinsi={provinsi}, Kab/Kota={kabkota}") | |
| subp.alignment = WD_ALIGN_PARAGRAPH.CENTER | |
| doc.add_paragraph("") | |
| doc.add_heading("Ringkasan Dashboard", level=1) | |
| doc.add_paragraph(hero.replace("## ", "")) | |
| doc.add_paragraph(info.replace("**", "")) | |
| doc.add_paragraph("") | |
| doc.add_heading("Tabel Interpretasi dan Rekomendasi Kebijakan (Pra/Saat/Pasca/Indeks TKM)", level=1) | |
| if chosen is None: | |
| doc.add_paragraph( | |
| "Tabel interpretasi & rekomendasi memerlukan pemilihan Kab/Kota tertentu, " | |
| "atau kondisi filter menghasilkan tepat satu Kab/Kota." | |
| ) | |
| else: | |
| sub_pra = float(pd.to_numeric(chosen.get("SubIndeks_Pra_0_100", np.nan), errors="coerce")) | |
| sub_saat = float(pd.to_numeric(chosen.get("SubIndeks_Saat_0_100", np.nan), errors="coerce")) | |
| sub_pasca = float(pd.to_numeric(chosen.get("SubIndeks_Pasca_0_100", np.nan), errors="coerce")) | |
| idx_final = float(pd.to_numeric(chosen.get("Indeks_TKM_0_100", np.nan), errors="coerce")) | |
| status_sampel = str(chosen.get("status_sampel", "")).strip() | |
| df_tbl = build_interpretasi_rekom_table_adaptif(sub_pra, sub_saat, sub_pasca, idx_final, status_sampel) | |
| add_interpretasi_rekom_table_docx(doc, df_tbl) | |
| fn = f"LAPORAN_TKM_TABEL_{_now_tag()}_{provinsi.replace(' ', '_')}_{kabkota.replace(' ', '_')}.docx" | |
| fn = fn.replace("/", "_").replace("\\", "_") | |
| out_path = str(Path.cwd() / fn) | |
| doc.save(out_path) | |
| return out_path | |
| # ========================= | |
| # EXPORT EXCEL (opsional) | |
| # ========================= | |
| def _safe_sheet_name(name: str) -> str: | |
| bad = ['\\', '/', '*', '[', ']', ':', '?'] | |
| out = name | |
| for b in bad: | |
| out = out.replace(b, " ") | |
| out = " ".join(out.split()).strip() | |
| return out[:31] if out else "Sheet1" | |
| def export_excel(provinsi: str, kabkota: str): | |
| hero, info, out_kab_valid, out_prov, out_kab_adjusted, out_kab_all, detail_df, _ = compute_outputs(provinsi, kabkota) | |
| chosen = None | |
| if kabkota != "(Semua)": | |
| pick = out_kab_all[out_kab_all["Wilayah"] == kabkota].head(1) | |
| if not pick.empty: | |
| chosen = pick.iloc[0] | |
| else: | |
| if len(out_kab_all) == 1: | |
| chosen = out_kab_all.iloc[0] | |
| tbl = pd.DataFrame() | |
| if chosen is not None: | |
| sub_pra = float(pd.to_numeric(chosen.get("SubIndeks_Pra_0_100", np.nan), errors="coerce")) | |
| sub_saat = float(pd.to_numeric(chosen.get("SubIndeks_Saat_0_100", np.nan), errors="coerce")) | |
| sub_pasca = float(pd.to_numeric(chosen.get("SubIndeks_Pasca_0_100", np.nan), errors="coerce")) | |
| idx_final = float(pd.to_numeric(chosen.get("Indeks_TKM_0_100", np.nan), errors="coerce")) | |
| status_sampel = str(chosen.get("status_sampel", "")).strip() | |
| tbl = build_interpretasi_rekom_table_adaptif(sub_pra, sub_saat, sub_pasca, idx_final, status_sampel) | |
| fn = f"OUTPUT_TKM_{_now_tag()}_{provinsi.replace(' ', '_')}_{kabkota.replace(' ', '_')}.xlsx" | |
| fn = fn.replace("/", "_").replace("\\", "_") | |
| out_path = str(Path.cwd() / fn) | |
| with pd.ExcelWriter(out_path, engine="openpyxl") as writer: | |
| pd.DataFrame({"Hero_MD": [hero], "Info_MD": [info]}).to_excel(writer, sheet_name=_safe_sheet_name("RINGKASAN"), index=False) | |
| (out_kab_valid if out_kab_valid is not None else pd.DataFrame()).to_excel(writer, sheet_name=_safe_sheet_name("KabKota_Valid"), index=False) | |
| (out_kab_adjusted if out_kab_adjusted is not None else pd.DataFrame()).to_excel(writer, sheet_name=_safe_sheet_name("KabKota_Adjusted"), index=False) | |
| (out_prov if out_prov is not None else pd.DataFrame()).to_excel(writer, sheet_name=_safe_sheet_name("Provinsi"), index=False) | |
| (out_kab_all if out_kab_all is not None else pd.DataFrame()).to_excel(writer, sheet_name=_safe_sheet_name("KabKota_All"), index=False) | |
| (tbl if tbl is not None else pd.DataFrame()).to_excel(writer, sheet_name=_safe_sheet_name("Tabel_Interpretasi"), index=False) | |
| return out_path | |
| # ========================= | |
| # GRADIO UI | |
| # ========================= | |
| def get_prov_choices(): | |
| provs = sorted([p for p in df_idx[PROV_COL].dropna().unique().tolist() if str(p).strip() != ""]) | |
| return ["(Semua)"] + provs | |
| def update_kab_dropdown(provinsi: str): | |
| if provinsi == "(Semua)": | |
| kabs = sorted([k for k in df_idx[KABKOT_COL].dropna().unique().tolist() if str(k).strip() != ""]) | |
| else: | |
| sub = df_idx[df_idx[PROV_COL] == provinsi] | |
| kabs = sorted([k for k in sub[KABKOT_COL].dropna().unique().tolist() if str(k).strip() != ""]) | |
| return gr.update(choices=["(Semua)"] + kabs, value="(Semua)") | |
| with gr.Blocks(title="Dashboard TKM (MSI) — Interpretasi & Rekomendasi Adaptif") as demo: | |
| gr.Markdown( | |
| f""" | |
| # Dashboard Indeks TKM (MSI) | |
| Kategori Final (0–100): | |
| - < 50 : sangat rendah | |
| - 50 – < 65 : rendah | |
| - 65 – < 80 : sedang | |
| - 80 – < 90 : tinggi | |
| - ≥ 90 : sangat tinggi | |
| **Interpretasi & rekomendasi selalu menyesuaikan NILAI dan KATEGORI tiap baris (Pra/Saat/Pasca/Indeks).** | |
| """ | |
| ) | |
| with gr.Row(): | |
| dd_prov = gr.Dropdown(label="Provinsi", choices=get_prov_choices(), value="(Semua)") | |
| dd_kab = gr.Dropdown(label="Kab/Kota", choices=["(Semua)"], value="(Semua)") | |
| run_btn = gr.Button("Jalankan", variant="primary") | |
| hero_md = gr.Markdown() | |
| info_md = gr.Markdown() | |
| with gr.Row(): | |
| btn_xlsx = gr.Button("⬇️ Download Excel", variant="secondary") | |
| file_xlsx = gr.File(label="File Excel", interactive=False) | |
| btn_docx = gr.Button("⬇️ Download Laporan Word (Tabel Interpretasi & Rekomendasi)", variant="secondary") | |
| file_docx = gr.File(label="File Word", interactive=False) | |
| with gr.Accordion(f"🟢 Kab/Kota n≥{MIN_RESPONDEN_SLOVIN}", open=True): | |
| out_kab_valid_ui = gr.DataFrame(interactive=False) | |
| with gr.Accordion("🟠 Kab/Kota n<400 (disesuaikan)", open=False): | |
| out_kab_adjusted_ui = gr.DataFrame(interactive=False) | |
| with gr.Accordion("🔵 Provinsi (dari Kab/Kota final)", open=False): | |
| out_prov_ui = gr.DataFrame(interactive=False) | |
| with gr.Accordion("🟢 Detail SubIndeks (Kab/Kota terpilih)", open=False): | |
| detail_df_ui = gr.DataFrame(interactive=False) | |
| detail_plot_ui = gr.Plot() | |
| dd_prov.change(fn=update_kab_dropdown, inputs=dd_prov, outputs=dd_kab) | |
| def _run(prov, kab): | |
| hero, info, out_kab_valid, out_prov, out_kab_adjusted, out_kab_all, detail_df, detail_plot = compute_outputs(prov, kab) | |
| return hero, info, out_kab_valid, out_kab_adjusted, out_prov, detail_df, detail_plot | |
| run_btn.click( | |
| fn=_run, | |
| inputs=[dd_prov, dd_kab], | |
| outputs=[hero_md, info_md, out_kab_valid_ui, out_kab_adjusted_ui, out_prov_ui, detail_df_ui, detail_plot_ui], | |
| ) | |
| dd_kab.change( | |
| fn=_run, | |
| inputs=[dd_prov, dd_kab], | |
| outputs=[hero_md, info_md, out_kab_valid_ui, out_kab_adjusted_ui, out_prov_ui, detail_df_ui, detail_plot_ui], | |
| ) | |
| btn_docx.click(fn=export_word_report, inputs=[dd_prov, dd_kab], outputs=[file_docx]) | |
| btn_xlsx.click(fn=export_excel, inputs=[dd_prov, dd_kab], outputs=[file_xlsx]) | |
| if __name__ == "__main__": | |
| demo.launch() |