Spaces:
Sleeping
Sleeping
| # -*- coding: utf-8 -*- | |
| """ | |
| IPLM 2025 — Final (Target Sampel 33.88% per Jenis) — TANPA Kinerja Relatif / Percentile | |
| UPDATE (sesuai instruksi terbaru Anda) — TANPA mengubah pipeline lain: | |
| FOKUS PEMBENAHAN (LLM + WORD): | |
| 1) Nilai Kepatuhan, Koleksi, Tenaga, Kinerja, Pelayanan, Pengelolaan: | |
| - TIDAK dikalikan 100. | |
| - Ditulis APA ADANYA dari kolom agregat aplikasi: | |
| Rata2_dim_kepatuhan, Rata2_sub_koleksi, Rata2_sub_sdm, Rata2_dim_kinerja, | |
| Rata2_sub_pelayanan, Rata2_sub_pengelolaan. | |
| 2) Nilai IPLM ditulis apa adanya: Indeks_Final_Wilayah_0_100. | |
| 3) LLM mengisi Interpretasi & Rekomendasi: | |
| - Interpretasi: deskriptif, kondisi riil berbasis relasi angka (lebih besar/kecil, gap, dominan, konsistensi), | |
| plus pemaknaan substantif dimensi (koleksi/sdm/pelayanan/pengelolaan) TANPA label normatif. | |
| - Rekomendasi: operasional, 2–3 butir ringkas, menaut ke pola angka (gap/ketimpangan/kontribusi). | |
| 4) Di bawah tabel Word: tambah deskripsi jumlah perpustakaan sumber data (dari tabel agregat wilayah × jenis / “gambar 2”): | |
| sekolah=..., umum=..., khusus=..., total=... | |
| Catatan penting: | |
| - Semua perhitungan dan dashboard tetap. | |
| - Yang diubah hanya: (a) cara mengambil nilai untuk tabel Word (tanpa *100), | |
| (b) prompt LLM untuk isi interpretasi/rekomendasi agar nyambung dengan angka, | |
| (c) tambahan paragraf jumlah perpustakaan di bawah tabel Word. | |
| """ | |
| import os | |
| import re | |
| import time | |
| import json | |
| import math | |
| import tempfile | |
| from pathlib import Path | |
| import gradio as gr | |
| import numpy as np | |
| import pandas as pd | |
| import plotly.graph_objects as go | |
| from sklearn.preprocessing import PowerTransformer | |
| # python-docx (wajib kalau mau Word) | |
| DOCX_AVAILABLE = True | |
| try: | |
| from docx import Document | |
| from docx.shared import Pt | |
| from docx.oxml import OxmlElement | |
| from docx.oxml.ns import qn | |
| except Exception: | |
| DOCX_AVAILABLE = False | |
| Document = None | |
| # huggingface client (opsional) | |
| HF_AVAILABLE = True | |
| try: | |
| from huggingface_hub import InferenceClient | |
| except Exception: | |
| HF_AVAILABLE = False | |
| InferenceClient = None | |
| # ============================================================ | |
| # 1) KONFIGURASI | |
| # ============================================================ | |
| DATA_FILE = os.getenv("DATA_FILE", "DATA CLEAN GABUNGAN SANGGAH-TIDAK SANGGAH-ALL200226.xlsx") | |
| POP_KAB = os.getenv("POP_KAB", "Data_populasi_Kab_kota_fixed.xlsx") | |
| POP_PROV = os.getenv("POP_PROV", "Data_populasi_propinsi.xlsx") | |
| POP_KHUSUS = os.getenv("POP_KHUSUS", "Data_populasi_perp_khusus.xlsx") | |
| W_KEPATUHAN = float(os.getenv("W_KEPATUHAN", "0.30")) | |
| W_KINERJA = float(os.getenv("W_KINERJA", "0.70")) | |
| TARGET_RATIO = float(os.getenv("TARGET_RATIO", "0.3388")) | |
| USE_LLM = True | |
| LLM_MODEL_NAME = os.getenv("LLM_MODEL_NAME", "meta-llama/Meta-Llama-3-8B-Instruct") | |
| HF_TOKEN = ( | |
| os.getenv("HF_SECRET") | |
| or os.getenv("HF_TOKEN") | |
| or os.getenv("HUGGINGFACEHUB_API_TOKEN") | |
| or os.getenv("HF_API_TOKEN") | |
| ) | |
| # ============================================================ | |
| # 2) UTIL | |
| # ============================================================ | |
| def _mtime(path_str: str): | |
| p = Path(path_str) | |
| return p.stat().st_mtime if p.exists() else None | |
| def _canon(s: str) -> str: | |
| return re.sub(r"[^a-z0-9]+", "", str(s).lower()) | |
| def _disp_text(x): | |
| if pd.isna(x): | |
| return None | |
| t = str(x).strip().upper() | |
| return " ".join(t.split()) | |
| def pick_col(df, candidates): | |
| if df is None or df.empty: | |
| return None | |
| for c in candidates: | |
| if c in df.columns: | |
| return c | |
| can_map = {_canon(c): c for c in df.columns} | |
| for c in candidates: | |
| k = _canon(c) | |
| if k in can_map: | |
| return can_map[k] | |
| return None | |
| def coerce_num(val): | |
| if pd.isna(val): | |
| return np.nan | |
| t = str(val).strip() | |
| if t == "" or t in {"-", "–", "—", "NA", "N/A", "null", "NULL"}: | |
| return np.nan | |
| t = t.replace("\u00a0", " ").replace("Rp", "").replace("%", "") | |
| t = re.sub(r"[^0-9,.\-]", "", t) | |
| if t.count(".") > 1 and t.count(",") == 1: | |
| t = t.replace(".", "").replace(",", ".") | |
| elif t.count(",") > 1 and t.count(".") == 1: | |
| t = t.replace(",", "") | |
| elif t.count(",") == 1 and t.count(".") == 0: | |
| t = t.replace(",", ".") | |
| else: | |
| t = t.replace(",", "") | |
| try: | |
| return float(t) | |
| except Exception: | |
| return np.nan | |
| def minmax_norm(s: pd.Series) -> pd.Series: | |
| x = pd.to_numeric(s, errors="coerce").astype(float) | |
| mn, mx = x.min(skipna=True), x.max(skipna=True) | |
| if pd.isna(mn) or pd.isna(mx) or mx == mn: | |
| return pd.Series(0.0, index=s.index) | |
| return (x - mn) / (mx - mn) | |
| def norm_kew(v): | |
| if pd.isna(v): | |
| return None | |
| t = str(v).strip().upper() | |
| if "KAB" in t or "KOTA" in t: | |
| return "KAB/KOTA" | |
| if "PROV" in t: | |
| return "PROVINSI" | |
| if "PUSAT" in t or "NASIONAL" in t: | |
| return "PUSAT" | |
| return t | |
| def norm_prov_disp(s): | |
| if pd.isna(s): | |
| return None | |
| t = str(s).strip().upper() | |
| t = t.replace("\u00a0", " ") | |
| t = " ".join(t.split()) | |
| t = t.replace("PROPINSI", "PROVINSI") | |
| while t.startswith("PROVINSI PROVINSI "): | |
| t = t.replace("PROVINSI PROVINSI ", "PROVINSI ", 1) | |
| if t.startswith("PROVINSI "): | |
| name = t[len("PROVINSI "):].strip() | |
| else: | |
| name = t | |
| name = " ".join(name.split()) | |
| if not name: | |
| return None | |
| return f"PROVINSI {name}" | |
| def norm_prov_label(s): | |
| if pd.isna(s): | |
| return None | |
| t = str(s).strip().upper().replace("\u00a0", " ") | |
| t = " ".join(t.split()) | |
| t = t.replace("PROPINSI", "PROVINSI") | |
| t = t.replace("PROVINSI", "").strip() | |
| return re.sub(r"[^A-Z0-9]+", "", t) | |
| def norm_kab_label(s): | |
| if pd.isna(s): | |
| return None | |
| t = str(s).upper() | |
| t = t.replace("KABUPATEN", "KAB") | |
| t = t.replace("KAB.", "KAB") | |
| t = t.replace("KOTA ADMINISTRASI", "KOTA") | |
| t = t.replace("KOTA ADM.", "KOTA") | |
| t = t.replace("KOTA.", "KOTA") | |
| t = " ".join(t.split()) | |
| return re.sub(r"[^A-Z0-9]+", "", t) | |
| def safe_div(num, den): | |
| if den is None or pd.isna(den) or float(den) <= 0: | |
| return np.nan | |
| return float(num) / float(den) | |
| def faktor_penyesuaian_total(n_total: float, target_total: float) -> float: | |
| if target_total is None or pd.isna(target_total) or float(target_total) <= 0: | |
| return 1.0 | |
| if n_total is None or pd.isna(n_total) or float(n_total) < 0: | |
| n_total = 0.0 | |
| return float(min(float(n_total) / float(target_total), 1.0)) | |
| # ============================================================ | |
| # 3) INDIKATOR IPLM | |
| # ============================================================ | |
| koleksi_cols = [ | |
| "JudulTercetak","EksemplarTercetak","JudulElektronik","EksemplarElektronik", | |
| "TambahJudulTercetak","TambahEksemplarTercetak", | |
| "TambahJudulElektronik","TambahEksemplarElektronik", | |
| "KomitmenAnggaranKoleksi" | |
| ] | |
| sdm_cols = [ | |
| "TenagaKualifikasiIlmuPerpustakaan", | |
| "TenagaFungsionalProfesional", | |
| "TenagaPKB", | |
| "AnggaranTenaga" | |
| ] | |
| pelayanan_cols = [ | |
| "PesertaBudayaBaca","PemustakaLuringDaring","PemustakaFasilitasTIK", | |
| "PemanfaatanJudulTercetak","PemanfaatanEksemplarTercetak", | |
| "PemanfaatanJudulElektronik","PemanfaatanEksemplarElektronik" | |
| ] | |
| pengelolaan_cols = [ | |
| "KegiatanBudayaBaca","KegiatanKerjasama","VariasiLayanan","Kebijakan","AnggaranLayanan" | |
| ] | |
| all_indicators = koleksi_cols + sdm_cols + pelayanan_cols + pengelolaan_cols | |
| alias_map_raw = { | |
| "j_judul_koleksi_tercetak": "JudulTercetak", | |
| "j_eksemplar_koleksi_tercetak": "EksemplarTercetak", | |
| "j_judul_koleksi_digital": "JudulElektronik", | |
| "j_eksemplar_koleksi_digital": "EksemplarElektronik", | |
| "tambah_judul_koleksi_tercetak": "TambahJudulTercetak", | |
| "tambah_eksemplar_koleksi_tercetak": "TambahEksemplarTercetak", | |
| "tambah_judul_koleksi_digital": "TambahJudulElektronik", | |
| "tambah_eksemplar_koleksi_digital": "TambahEksemplarElektronik", | |
| "j_anggaran_koleksi": "KomitmenAnggaranKoleksi", | |
| "j_tenaga_ilmu_perpus": "TenagaKualifikasiIlmuPerpustakaan", | |
| "j_tenaga_nonilmu_perpus": "TenagaFungsionalProfesional", | |
| "j_tenaga_pkb": "TenagaPKB", | |
| "j_anggaran_diklat_perpus": "AnggaranTenaga", | |
| "j_peserta_budaya_baca": "PesertaBudayaBaca", | |
| "j_pemustaka_luring_daring": "PemustakaLuringDaring", | |
| "j_pemustaka_fasilitas_tik": "PemustakaFasilitasTIK", | |
| "j_judul_koleksi_tercetak_termanfaat": "PemanfaatanJudulTercetak", | |
| "j_eksemplar_koleksi_tercetak_termanfaat": "PemanfaatanEksemplarTercetak", | |
| "j_judul_koleksi_digital_termanfaat": "PemanfaatanJudulElektronik", | |
| "j_eksemplar_koleksi_digital_termanfaat": "PemanfaatanEksemplarElektronik", | |
| "j_kegiatan_budaya_baca_peningkatan_literasi": "KegiatanBudayaBaca", | |
| "j_kerjasama_pengembangan_perpus": "KegiatanKerjasama", | |
| "j_variasi_layanan": "VariasiLayanan", | |
| "j_kebijakan_prosedur_pelayanan": "Kebijakan", | |
| "j_anggaran_peningkatan_pelayanan": "AnggaranLayanan", | |
| } | |
| alias_map = {_canon(k): v for k, v in alias_map_raw.items()} | |
| # ============================================================ | |
| # 4) PIPELINE NASIONAL (LEVEL ENTITAS) | |
| # ============================================================ | |
| def _mean_norm_cols(row, cols): | |
| vals = [] | |
| for c in cols: | |
| k = f"norm_{c}" | |
| if k in row.index: | |
| v = row[k] | |
| if pd.isna(v): | |
| v = 0.0 | |
| vals.append(float(v)) | |
| return float(np.mean(vals)) if vals else 0.0 | |
| def prepare_global(df_src: pd.DataFrame) -> pd.DataFrame: | |
| if df_src is None or df_src.empty: | |
| return df_src | |
| df = df_src.copy() | |
| rename_map = {} | |
| for col in df.columns: | |
| c = _canon(col) | |
| if c in alias_map: | |
| rename_map[col] = alias_map[c] | |
| else: | |
| for tgt in all_indicators: | |
| if c == _canon(tgt): | |
| rename_map[col] = tgt | |
| break | |
| if rename_map: | |
| df = df.rename(columns=rename_map) | |
| available = [c for c in all_indicators if c in df.columns] | |
| for c in available: | |
| df[c] = df[c].apply(coerce_num) | |
| for c in available: | |
| x = pd.to_numeric(df[c], errors="coerce").astype(float).values | |
| mask = ~np.isnan(x) | |
| transformed = np.full_like(x, np.nan, dtype=float) | |
| if mask.sum() > 1: | |
| pt = PowerTransformer(method="yeo-johnson", standardize=False) | |
| transformed[mask] = pt.fit_transform(x[mask].reshape(-1, 1)).ravel() | |
| else: | |
| transformed[mask] = x[mask] | |
| df[f"norm_{c}"] = minmax_norm(pd.Series(transformed, index=df.index)) | |
| df["sub_koleksi"] = df.apply(lambda r: _mean_norm_cols(r, [c for c in koleksi_cols if c in available]), axis=1) | |
| df["sub_sdm"] = df.apply(lambda r: _mean_norm_cols(r, [c for c in sdm_cols if c in available]), axis=1) | |
| df["sub_pelayanan"] = df.apply(lambda r: _mean_norm_cols(r, [c for c in pelayanan_cols if c in available]), axis=1) | |
| df["sub_pengelolaan"] = df.apply(lambda r: _mean_norm_cols(r, [c for c in pengelolaan_cols if c in available]), axis=1) | |
| df["dim_kepatuhan"] = df[["sub_koleksi","sub_sdm"]].mean(axis=1) | |
| df["dim_kinerja"] = df[["sub_pelayanan","sub_pengelolaan"]].mean(axis=1) | |
| df["Indeks_Dasar_0_100"] = 100 * (W_KEPATUHAN * df["dim_kepatuhan"] + W_KINERJA * df["dim_kinerja"]) | |
| for c in ["sub_koleksi","sub_sdm","sub_pelayanan","sub_pengelolaan","dim_kepatuhan","dim_kinerja","Indeks_Dasar_0_100"]: | |
| df[c] = pd.to_numeric(df[c], errors="coerce").fillna(0.0) | |
| return df | |
| # ============================================================ | |
| # 5) CACHE LOADER (NO UPLOAD) | |
| # ============================================================ | |
| _CACHE = {"key": None, "df_all": None, "df_raw": None, "pop_kab": None, "pop_prov": None, "pop_khusus": None, "meta": None, "info": None} | |
| def _parse_pop_khusus(path_xlsx: str) -> pd.DataFrame: | |
| df = pd.read_excel(path_xlsx) | |
| if df is None or df.empty: | |
| return pd.DataFrame() | |
| c_mix = pick_col(df, [ | |
| "Propinsi/Kab/kota", "Propinsi/Kab/Kota", "Provinsi/Kab/Kota", | |
| "Provinsi/Kab/kota", "Provinsi/Kabupaten/Kota", | |
| "Wilayah", "Nama Wilayah" | |
| ]) | |
| if c_mix is None: | |
| raise ValueError("POP_KHUSUS: kolom gabungan Provinsi/Kab/Kota tidak ditemukan.") | |
| c_pop = pick_col(df, ["POP_KHUSUS", "Pop_Khusus", "pop_khusus"]) | |
| if c_pop is None: | |
| raise ValueError("POP_KHUSUS: kolom 'POP_KHUSUS' tidak ditemukan.") | |
| mix = df[c_mix].astype(str).fillna("").str.strip() | |
| pop_series = df[c_pop].apply(coerce_num) | |
| rows = [] | |
| current_prov = None | |
| for m, pval in zip(mix.tolist(), pop_series.tolist()): | |
| mm = _disp_text(m) or "" | |
| if mm == "": | |
| continue | |
| if mm.startswith("PROVINSI "): | |
| prov_name = mm.replace("PROVINSI", "").strip() | |
| current_prov = prov_name | |
| rows.append({ | |
| "LEVEL": "PROV", | |
| "Provinsi_Label": f"PROVINSI {prov_name}", | |
| "Kab_Kota_Label": None, | |
| "Pop_Total_Jenis": pval, | |
| }) | |
| continue | |
| rows.append({ | |
| "LEVEL": "KAB", | |
| "Provinsi_Label": f"PROVINSI {current_prov}" if current_prov else None, | |
| "Kab_Kota_Label": mm, | |
| "Pop_Total_Jenis": pval, | |
| }) | |
| pop = pd.DataFrame(rows) | |
| if pop.empty: | |
| return pop | |
| pop["Pop_Total_Jenis"] = pd.to_numeric(pop["Pop_Total_Jenis"], errors="coerce").fillna(0.0) | |
| pop["prov_key"] = pop["Provinsi_Label"].apply(norm_prov_label) | |
| pop["kab_key"] = pop["Kab_Kota_Label"].apply(norm_kab_label) if "Kab_Kota_Label" in pop.columns else None | |
| return pop | |
| def load_default_files(force=False): | |
| key = ( | |
| DATA_FILE, POP_KAB, POP_PROV, POP_KHUSUS, | |
| _mtime(DATA_FILE), _mtime(POP_KAB), _mtime(POP_PROV), _mtime(POP_KHUSUS) | |
| ) | |
| if (not force) and _CACHE["key"] == key and _CACHE["df_all"] is not None: | |
| return _CACHE["df_all"], _CACHE["df_raw"], _CACHE["pop_kab"], _CACHE["pop_prov"], _CACHE["pop_khusus"], _CACHE["meta"], _CACHE["info"] | |
| for p, label in [(DATA_FILE, "DM"), (POP_KAB, "POP_KAB"), (POP_PROV, "POP_PROV"), (POP_KHUSUS, "POP_KHUSUS")]: | |
| if not Path(p).exists(): | |
| info = f"File tidak ditemukan ({label}): {p}" | |
| _CACHE.update({"key": key, "df_all": None, "df_raw": None, "pop_kab": None, "pop_prov": None, "pop_khusus": None, "meta": {}, "info": info}) | |
| return None, None, None, None, None, {}, info | |
| fp = Path(DATA_FILE) | |
| xls = pd.ExcelFile(fp) | |
| frames = [pd.read_excel(fp, sheet_name=s) for s in xls.sheet_names] | |
| df_raw = pd.concat(frames, ignore_index=True, sort=False) | |
| prov_col = pick_col(df_raw, ["provinsi", "Provinsi", "PROVINSI"]) | |
| kab_col = pick_col(df_raw, ["kab/kota", "Kab/Kota", "Kab_Kota", "KAB/KOTA", "kabupaten_kota", "Kabupaten/Kota", "kabupaten kota", "kota", "kab_kota"]) | |
| kew_col = pick_col(df_raw, ["kewenangan", "jenis_kewenangan", "Kewenangan", "KEWENANGAN"]) | |
| jenis_col = pick_col(df_raw, ["jenis_perpustakaan", "Jenis Perpustakaan", "JENIS_PERPUSTAKAAN"]) | |
| nama_col = pick_col(df_raw, ["nm_perpustakaan","nama_perpustakaan","Nama Perpustakaan","nm_instansi_lembaga","nm_perpus"]) | |
| missing = [] | |
| if prov_col is None: missing.append("Provinsi") | |
| if kab_col is None: missing.append("Kab/Kota") | |
| if kew_col is None: missing.append("Kewenangan") | |
| if jenis_col is None: missing.append("Jenis Perpustakaan") | |
| if missing: | |
| info = f"Kolom wajib tidak ditemukan di DM: {', '.join(missing)}" | |
| _CACHE.update({"key": key, "df_all": None, "df_raw": None, "pop_kab": None, "pop_prov": None, "pop_khusus": None, "meta": {}, "info": info}) | |
| return None, None, None, None, None, {}, info | |
| val_map_jenis = { | |
| "PERPUSTAKAAN SEKOLAH": "sekolah", "SEKOLAH": "sekolah", | |
| "PERPUSTAKAAN UMUM": "umum", "UMUM": "umum", "PERPUSTAKAAN DAERAH": "umum", | |
| "PERPUSTAKAAN KHUSUS": "khusus", "KHUSUS": "khusus", | |
| } | |
| df_raw["KEW_NORM"] = df_raw[kew_col].apply(norm_kew) | |
| df_raw["_dataset"] = df_raw[jenis_col].astype(str).str.strip().str.upper().map(val_map_jenis) | |
| df_raw["PROV_DISP"] = df_raw[prov_col].apply(norm_prov_disp) | |
| df_raw["KAB_DISP"] = df_raw[kab_col].apply(_disp_text) | |
| df_raw["prov_key"] = df_raw["PROV_DISP"].apply(norm_prov_label) | |
| df_raw["kab_key"] = df_raw["KAB_DISP"].apply(norm_kab_label) | |
| if nama_col and nama_col in df_raw.columns: | |
| kcols = [prov_col, kab_col, kew_col, jenis_col, nama_col] | |
| else: | |
| kcols = [prov_col, kab_col, kew_col, jenis_col] | |
| tmp = df_raw[kcols].astype(str).fillna("").apply(lambda s: s.str.strip(), axis=0) | |
| df_raw["_row_key"] = tmp.apply(lambda r: "||".join(r.values.tolist()), axis=1).apply(_canon) | |
| before = len(df_raw) | |
| df_raw = df_raw.drop_duplicates(subset=["_row_key"], keep="first").copy() | |
| after = len(df_raw) | |
| # POP KAB | |
| pk = pd.read_excel(POP_KAB) | |
| c_kab = pick_col(pk, ["KABUPATEN_KOTA","Kab/Kota","Kabupaten/Kota","KAB/KOTA","Kabupaten_Kota","kab_kota","kabupaten_kota"]) | |
| c_prov = pick_col(pk, ["PROVINSI","Provinsi","provinsi"]) | |
| if c_kab is None: | |
| info = "POP_KAB: wajib ada kolom Kab/Kota." | |
| _CACHE.update({"key": key, "df_all": None, "df_raw": None, "pop_kab": None, "pop_prov": None, "pop_khusus": None, "meta": {}, "info": info}) | |
| return None, None, None, None, None, {}, info | |
| pop_kab = pk.copy() | |
| pop_kab["Kab_Kota_Label"] = pk[c_kab].astype(str).str.strip() | |
| pop_kab["Provinsi_Label"] = pk[c_prov].astype(str).str.strip() if c_prov else "" | |
| pop_kab["kab_key"] = pop_kab["Kab_Kota_Label"].apply(norm_kab_label) | |
| pop_kab = pop_kab.groupby("kab_key", as_index=False).first() | |
| # POP PROV | |
| pp = pd.read_excel(POP_PROV) | |
| c_pr = pick_col(pp, ["Provinsi","PROVINSI","provinsi","Propinsi","PROPINSI","propinsi"]) | |
| if c_pr is None: | |
| info = "POP_PROV: wajib ada kolom Provinsi." | |
| _CACHE.update({"key": key, "df_all": None, "df_raw": None, "pop_kab": None, "pop_prov": None, "pop_khusus": None, "meta": {}, "info": info}) | |
| return None, None, None, None, None, {}, info | |
| pop_prov = pp.copy() | |
| pop_prov["Provinsi_Label"] = pp[c_pr].astype(str).str.strip() | |
| pop_prov["prov_key"] = pop_prov["Provinsi_Label"].apply(norm_prov_label) | |
| pop_prov = pop_prov.groupby("prov_key", as_index=False).first() | |
| # POP KHUSUS | |
| try: | |
| pop_khusus = _parse_pop_khusus(POP_KHUSUS) | |
| except Exception as e: | |
| info = f"POP_KHUSUS gagal dibaca: {repr(e)}" | |
| _CACHE.update({"key": key, "df_all": None, "df_raw": None, "pop_kab": None, "pop_prov": None, "pop_khusus": None, "meta": {}, "info": info}) | |
| return None, None, None, None, None, {}, info | |
| df_all = prepare_global(df_raw) | |
| meta = dict(prov_col=prov_col, kab_col=kab_col, kew_col=kew_col, jenis_col=jenis_col, nama_col=nama_col) | |
| info = ( | |
| f"Mode NO UPLOAD (cache aktif)\n" | |
| f"DM: {fp.name} | Baris: {before} -> dedup: {after}\n" | |
| f"POP_KAB: {Path(POP_KAB).name} (n={len(pop_kab)})\n" | |
| f"POP_PROV: {Path(POP_PROV).name} (n={len(pop_prov)})\n" | |
| f"POP_KHUSUS: {Path(POP_KHUSUS).name} (n={len(pop_khusus)})\n" | |
| f"TARGET sampel per jenis: {TARGET_RATIO*100:.2f}%\n" | |
| f"mtime: DM={time.ctime(_mtime(DATA_FILE))} | Kab={time.ctime(_mtime(POP_KAB))} | Prov={time.ctime(_mtime(POP_PROV))} | Khusus={time.ctime(_mtime(POP_KHUSUS))}" | |
| ) | |
| _CACHE.update({ | |
| "key": key, | |
| "df_all": df_all, | |
| "df_raw": df_raw, | |
| "pop_kab": pop_kab, | |
| "pop_prov": pop_prov, | |
| "pop_khusus": pop_khusus, | |
| "meta": meta, | |
| "info": info | |
| }) | |
| return df_all, df_raw, pop_kab, pop_prov, pop_khusus, meta, info | |
| # ============================================================ | |
| # 6) FAKTOR WILAYAH — PER JENIS (TARGET 33.88%) | |
| # ============================================================ | |
| def build_faktor_wilayah_jenis(df_filtered, pop_kab, pop_prov, pop_khusus, kew_value): | |
| if df_filtered is None or df_filtered.empty: | |
| return pd.DataFrame() | |
| kew_norm = str(kew_value or "").upper() | |
| df = df_filtered.copy() | |
| df = df[df["_dataset"].isin(["sekolah", "umum", "khusus"])].copy() | |
| if df.empty: | |
| return pd.DataFrame() | |
| jenis_list = ["sekolah", "umum", "khusus"] | |
| if "PROV" in kew_norm: | |
| key_col, label_col, label_name, mode = "prov_key", "PROV_DISP", "Provinsi", "PROV" | |
| base_pop = pop_prov.copy() if (pop_prov is not None and not pop_prov.empty) else pd.DataFrame() | |
| if not base_pop.empty and "prov_key" not in base_pop.columns: | |
| base_pop["prov_key"] = base_pop["Provinsi_Label"].apply(norm_prov_label) | |
| base_pop = base_pop.set_index("prov_key") if (not base_pop.empty and "prov_key" in base_pop.columns) else pd.DataFrame().set_index(pd.Index([])) | |
| else: | |
| key_col, label_col, label_name, mode = "kab_key", "KAB_DISP", "Kab/Kota", "KAB" | |
| base_pop = pop_kab.copy() if (pop_kab is not None and not pop_kab.empty) else pd.DataFrame() | |
| if not base_pop.empty and "kab_key" not in base_pop.columns: | |
| base_pop["kab_key"] = base_pop["Kab_Kota_Label"].apply(norm_kab_label) | |
| base_pop = base_pop.set_index("kab_key") if (not base_pop.empty and "kab_key" in base_pop.columns) else pd.DataFrame().set_index(pd.Index([])) | |
| base_keys = df[[key_col, label_col]].drop_duplicates().rename(columns={key_col: "group_key", label_col: label_name}) | |
| full = base_keys.assign(_tmp=1).merge(pd.DataFrame({"Jenis": jenis_list, "_tmp": 1}), on="_tmp").drop(columns="_tmp") | |
| cnt = ( | |
| df.groupby([key_col, label_col, "_dataset"], dropna=False) | |
| .size() | |
| .reset_index(name="n_jenis") | |
| .rename(columns={key_col: "group_key", label_col: label_name, "_dataset": "Jenis"}) | |
| ) | |
| cnt["Jenis"] = cnt["Jenis"].astype(str).str.lower().str.strip() | |
| base_n = full.merge(cnt, on=["group_key", label_name, "Jenis"], how="left") | |
| base_n["n_jenis"] = pd.to_numeric(base_n["n_jenis"], errors="coerce").fillna(0).astype(int) | |
| base_n["target_total_33_88_jenis"] = 0.0 | |
| base_n["pop_total_jenis"] = 0.0 | |
| if not base_pop.empty: | |
| if mode == "KAB": | |
| pop_sekolah = pd.to_numeric(base_pop.get("jumlah_populasi_sekolah_base", base_pop.get("jumlah_populasi_sekolah", 0)), errors="coerce").fillna(0.0) | |
| pop_umum = pd.to_numeric(base_pop.get("jumlah_populasi_umum_base", base_pop.get("jumlah_populasi_umum", 0)), errors="coerce").fillna(0.0) | |
| tgt_sekolah = pop_sekolah * float(TARGET_RATIO) | |
| tgt_umum = pop_umum * float(TARGET_RATIO) | |
| else: | |
| sma = pd.to_numeric(base_pop.get("sma ", base_pop.get("sma", 0)), errors="coerce").fillna(0.0) | |
| smk = pd.to_numeric(base_pop.get("smk", 0), errors="coerce").fillna(0.0) | |
| slb = pd.to_numeric(base_pop.get("slb", 0), errors="coerce").fillna(0.0) | |
| pop_sekolah = sma + smk + slb | |
| tgt_sekolah = pop_sekolah * float(TARGET_RATIO) | |
| pop_umum = pd.to_numeric(base_pop.get("perpus_umum_prop", 0), errors="coerce").fillna(0.0) | |
| tgt_umum = pop_umum * float(TARGET_RATIO) | |
| m = base_n["Jenis"].eq("sekolah") | |
| base_n.loc[m, "pop_total_jenis"] = base_n.loc[m, "group_key"].map(pop_sekolah).fillna(0.0).values | |
| base_n.loc[m, "target_total_33_88_jenis"] = base_n.loc[m, "group_key"].map(tgt_sekolah).fillna(0.0).values | |
| m = base_n["Jenis"].eq("umum") | |
| base_n.loc[m, "pop_total_jenis"] = base_n.loc[m, "group_key"].map(pop_umum).fillna(0.0).values | |
| base_n.loc[m, "target_total_33_88_jenis"] = base_n.loc[m, "group_key"].map(tgt_umum).fillna(0.0).values | |
| if pop_khusus is not None and not pop_khusus.empty: | |
| pk = pop_khusus.copy() | |
| pk["Pop_Total_Jenis"] = pd.to_numeric(pk.get("Pop_Total_Jenis", 0), errors="coerce").fillna(0.0) | |
| if mode == "PROV": | |
| pk_prov = pk[pk["LEVEL"].astype(str).str.upper() == "PROV"].copy() | |
| pk_map = pk_prov.groupby("prov_key", as_index=True).agg(pop=("Pop_Total_Jenis", "sum")) | |
| pop_series = pk_map["pop"] | |
| else: | |
| pk_kab = pk[pk["LEVEL"].astype(str).str.upper() == "KAB"].copy() | |
| pk_map = pk_kab.groupby("kab_key", as_index=True).agg(pop=("Pop_Total_Jenis", "sum")) | |
| pop_series = pk_map["pop"] | |
| tgt_series = pop_series * float(TARGET_RATIO) | |
| m = base_n["Jenis"].eq("khusus") | |
| base_n.loc[m, "pop_total_jenis"] = base_n.loc[m, "group_key"].map(pop_series).fillna(0.0).values | |
| base_n.loc[m, "target_total_33_88_jenis"] = base_n.loc[m, "group_key"].map(tgt_series).fillna(0.0).values | |
| base_n["target_total_33_88_jenis"] = pd.to_numeric(base_n["target_total_33_88_jenis"], errors="coerce").fillna(0.0) | |
| base_n["pop_total_jenis"] = pd.to_numeric(base_n["pop_total_jenis"], errors="coerce").fillna(0.0) | |
| m_need_pop = (base_n["pop_total_jenis"] <= 0) & (base_n["target_total_33_88_jenis"] > 0) | |
| base_n.loc[m_need_pop, "pop_total_jenis"] = base_n.loc[m_need_pop, "target_total_33_88_jenis"] / float(TARGET_RATIO) | |
| base_n["faktor_penyesuaian_jenis"] = [ | |
| faktor_penyesuaian_total(n, t) | |
| for n, t in zip( | |
| pd.to_numeric(base_n["n_jenis"], errors="coerce").fillna(0).astype(float), | |
| pd.to_numeric(base_n["target_total_33_88_jenis"], errors="coerce").fillna(0).astype(float), | |
| ) | |
| ] | |
| base_n["coverage_jenis_%"] = [ | |
| (safe_div(n, p) * 100.0) if (p is not None and not pd.isna(p) and float(p) > 0) else 0.0 | |
| for n, p in zip( | |
| pd.to_numeric(base_n["n_jenis"], errors="coerce").fillna(0).astype(float), | |
| pd.to_numeric(base_n["pop_total_jenis"], errors="coerce").fillna(0).astype(float), | |
| ) | |
| ] | |
| base_n["gap_target33_88_jenis"] = [ | |
| max(float(t) - float(n), 0.0) | |
| for n, t in zip( | |
| pd.to_numeric(base_n["n_jenis"], errors="coerce").fillna(0).astype(float), | |
| pd.to_numeric(base_n["target_total_33_88_jenis"], errors="coerce").fillna(0).astype(float), | |
| ) | |
| ] | |
| base_n["target_total_33_88_jenis"] = pd.to_numeric(base_n["target_total_33_88_jenis"], errors="coerce").fillna(0).round(0).astype(int) | |
| base_n["pop_total_jenis"] = pd.to_numeric(base_n["pop_total_jenis"], errors="coerce").fillna(0).round(0).astype(int) | |
| base_n["coverage_jenis_%"] = pd.to_numeric(base_n["coverage_jenis_%"], errors="coerce").fillna(0.0).round(2) | |
| base_n["faktor_penyesuaian_jenis"] = pd.to_numeric(base_n["faktor_penyesuaian_jenis"], errors="coerce").fillna(1.0).round(3) | |
| base_n["gap_target33_88_jenis"] = pd.to_numeric(base_n["gap_target33_88_jenis"], errors="coerce").fillna(0).round(0).astype(int) | |
| return base_n | |
| # ============================================================ | |
| # 7) AGREGAT WILAYAH × JENIS | |
| # ============================================================ | |
| def build_agg_wilayah_jenis(df_filtered, faktor_wilayah_jenis, kew_value): | |
| if df_filtered is None or df_filtered.empty: | |
| return pd.DataFrame() | |
| kew_norm = str(kew_value or "").upper() | |
| df = df_filtered.copy() | |
| if "PROV" in kew_norm: | |
| key_col, label_col, label_name = "prov_key", "PROV_DISP", "Provinsi" | |
| else: | |
| key_col, label_col, label_name = "kab_key", "KAB_DISP", "Kab/Kota" | |
| df = df[df["_dataset"].isin(["sekolah", "umum", "khusus"])].copy() | |
| if df.empty: | |
| return pd.DataFrame() | |
| jenis_list = ["sekolah", "umum", "khusus"] | |
| base_keys = df[[key_col, label_col]].drop_duplicates().rename(columns={key_col: "group_key", label_col: label_name}) | |
| full = base_keys.assign(_tmp=1).merge(pd.DataFrame({"Jenis": jenis_list, "_tmp": 1}), on="_tmp").drop(columns="_tmp") | |
| agg_real = df.groupby([key_col, label_col, "_dataset"], dropna=False).agg( | |
| Jumlah=("Indeks_Dasar_0_100", "size"), | |
| Rata2_sub_koleksi=("sub_koleksi", "mean"), | |
| Rata2_sub_sdm=("sub_sdm", "mean"), | |
| Rata2_sub_pelayanan=("sub_pelayanan", "mean"), | |
| Rata2_sub_pengelolaan=("sub_pengelolaan", "mean"), | |
| Rata2_dim_kepatuhan=("dim_kepatuhan", "mean"), | |
| Rata2_dim_kinerja=("dim_kinerja", "mean"), | |
| Indeks_Dasar_Agregat_0_100=("Indeks_Dasar_0_100", "mean"), | |
| ).reset_index().rename(columns={key_col: "group_key", label_col: label_name, "_dataset": "Jenis"}) | |
| agg_real["Jenis"] = agg_real["Jenis"].astype(str).str.lower().str.strip() | |
| agg = full.merge(agg_real, on=["group_key", label_name, "Jenis"], how="left") | |
| for c in ["Jumlah","Rata2_sub_koleksi","Rata2_sub_sdm","Rata2_sub_pelayanan","Rata2_sub_pengelolaan", | |
| "Rata2_dim_kepatuhan","Rata2_dim_kinerja","Indeks_Dasar_Agregat_0_100"]: | |
| if c in agg.columns: | |
| agg[c] = pd.to_numeric(agg[c], errors="coerce").fillna(0.0) | |
| agg["Jumlah"] = agg["Jumlah"].round(0).astype(int) | |
| if faktor_wilayah_jenis is None or faktor_wilayah_jenis.empty: | |
| agg["faktor_penyesuaian_jenis"] = 1.0 | |
| else: | |
| fw = faktor_wilayah_jenis.copy() | |
| fw["Jenis"] = fw["Jenis"].astype(str).str.lower().str.strip() | |
| keep = ["group_key", label_name, "Jenis", | |
| "faktor_penyesuaian_jenis", "target_total_33_88_jenis", "pop_total_jenis", | |
| "coverage_jenis_%", "gap_target33_88_jenis", "n_jenis"] | |
| fw = fw[[c for c in keep if c in fw.columns]].copy() | |
| agg = agg.merge(fw, on=["group_key", label_name, "Jenis"], how="left") | |
| agg["faktor_penyesuaian_jenis"] = pd.to_numeric(agg["faktor_penyesuaian_jenis"], errors="coerce").fillna(1.0) | |
| agg["Indeks_Final_Agregat_0_100"] = ( | |
| pd.to_numeric(agg["Indeks_Dasar_Agregat_0_100"], errors="coerce").fillna(0.0) | |
| * pd.to_numeric(agg["faktor_penyesuaian_jenis"], errors="coerce").fillna(1.0) | |
| ) | |
| for c in [ | |
| "Rata2_sub_koleksi","Rata2_sub_sdm","Rata2_sub_pelayanan","Rata2_sub_pengelolaan", | |
| "Rata2_dim_kepatuhan","Rata2_dim_kinerja" | |
| ]: | |
| if c in agg.columns: | |
| agg[c] = pd.to_numeric(agg[c], errors="coerce").fillna(0.0).round(3) | |
| for c in ["Indeks_Dasar_Agregat_0_100","Indeks_Final_Agregat_0_100"]: | |
| if c in agg.columns: | |
| agg[c] = pd.to_numeric(agg[c], errors="coerce").fillna(0.0).round(2) | |
| agg["faktor_penyesuaian_jenis"] = pd.to_numeric(agg["faktor_penyesuaian_jenis"], errors="coerce").fillna(1.0).round(3) | |
| return agg | |
| # ============================================================ | |
| # 8) AGREGAT WILAYAH (KESELURUHAN) — avg3 dari 3 jenis | |
| # ============================================================ | |
| def build_agg_wilayah_total_from_jenis(agg_jenis, faktor_wilayah_jenis, kew_value): | |
| if agg_jenis is None or agg_jenis.empty: | |
| return pd.DataFrame() | |
| kew_norm = str(kew_value or "").upper() | |
| label_name = "Provinsi" if "PROV" in kew_norm else "Kab/Kota" | |
| jenis_list = ["sekolah", "umum", "khusus"] | |
| a = agg_jenis.copy() | |
| a["Jenis"] = a["Jenis"].astype(str).str.lower().str.strip() | |
| base_keys = a[["group_key", label_name]].drop_duplicates() | |
| full = base_keys.assign(_tmp=1).merge(pd.DataFrame({"Jenis": jenis_list, "_tmp": 1}), on="_tmp").drop(columns="_tmp") | |
| cols_present = [c for c in [ | |
| "Jumlah", | |
| "Rata2_sub_koleksi","Rata2_sub_sdm","Rata2_sub_pelayanan","Rata2_sub_pengelolaan", | |
| "Rata2_dim_kepatuhan","Rata2_dim_kinerja", | |
| "Indeks_Dasar_Agregat_0_100", | |
| "Indeks_Final_Agregat_0_100", | |
| ] if c in a.columns] | |
| full = full.merge(a[["group_key", label_name, "Jenis"] + cols_present], | |
| on=["group_key", label_name, "Jenis"], how="left") | |
| for c in cols_present: | |
| full[c] = pd.to_numeric(full[c], errors="coerce").fillna(0.0) | |
| out = full.groupby(["group_key", label_name], as_index=False).agg( | |
| n_total=("Jumlah", "sum"), | |
| Rata2_sub_koleksi=("Rata2_sub_koleksi", "mean"), | |
| Rata2_sub_sdm=("Rata2_sub_sdm", "mean"), | |
| Rata2_sub_pelayanan=("Rata2_sub_pelayanan", "mean"), | |
| Rata2_sub_pengelolaan=("Rata2_sub_pengelolaan", "mean"), | |
| Rata2_dim_kepatuhan=("Rata2_dim_kepatuhan", "mean"), | |
| Rata2_dim_kinerja=("Rata2_dim_kinerja", "mean"), | |
| Indeks_Dasar_Agregat_0_100=("Indeks_Dasar_Agregat_0_100", "mean"), | |
| Indeks_Final_Wilayah_0_100=("Indeks_Final_Agregat_0_100", "mean"), | |
| ) | |
| for c in ["Rata2_sub_koleksi","Rata2_sub_sdm","Rata2_sub_pelayanan","Rata2_sub_pengelolaan","Rata2_dim_kepatuhan","Rata2_dim_kinerja"]: | |
| out[c] = pd.to_numeric(out[c], errors="coerce").fillna(0.0).round(3) | |
| for c in ["Indeks_Dasar_Agregat_0_100","Indeks_Final_Wilayah_0_100"]: | |
| out[c] = pd.to_numeric(out[c], errors="coerce").fillna(0.0).round(2) | |
| out["n_total"] = pd.to_numeric(out["n_total"], errors="coerce").fillna(0).round(0).astype(int) | |
| return out | |
| # ============================================================ | |
| # 9) SUMMARY (PER JENIS) + KESELURUHAN | |
| # ============================================================ | |
| def build_summary_per_jenis(agg_jenis, agg_total): | |
| jenis_list = ["sekolah", "umum", "khusus"] | |
| def _row_default(jenis): | |
| return { | |
| "Jenis": jenis, | |
| "Jumlah_Wilayah": 0, | |
| "Total_Perpus": 0, | |
| "Pop_Total_Jenis": 0, | |
| "Target33_88_Total_Jenis": 0, | |
| "Terkumpul_Jenis": 0, | |
| "Coverage_Target33_88_Jenis_%": 0.0, | |
| "Indeks_Dasar_0_100": 0.0, | |
| "Indeks_Final_Disesuaikan_0_100": 0.0, | |
| "Penyesuaian_Poin": 0.0, | |
| } | |
| rows_by_jenis = {j: _row_default(j) for j in jenis_list} | |
| if agg_jenis is not None and not agg_jenis.empty: | |
| a = agg_jenis.copy() | |
| a["Jenis"] = a["Jenis"].astype(str).str.lower().str.strip() | |
| for c in ["Jumlah","Indeks_Dasar_Agregat_0_100","Indeks_Final_Agregat_0_100","pop_total_jenis","target_total_33_88_jenis"]: | |
| if c in a.columns: | |
| a[c] = pd.to_numeric(a[c], errors="coerce").fillna(0) | |
| for jenis in jenis_list: | |
| sub = a[a["Jenis"] == jenis].copy() | |
| if sub.empty: | |
| continue | |
| jumlah_wilayah = int(sub.shape[0]) | |
| terkumpul = int(pd.to_numeric(sub.get("Jumlah", 0), errors="coerce").fillna(0).sum()) | |
| pop_total = int(pd.to_numeric(sub.get("pop_total_jenis", 0), errors="coerce").fillna(0).sum()) | |
| target3388 = int(pd.to_numeric(sub.get("target_total_33_88_jenis", 0), errors="coerce").fillna(0).sum()) | |
| coverage = (terkumpul / target3388 * 100.0) if target3388 > 0 else 0.0 | |
| dasar = float(pd.to_numeric(sub.get("Indeks_Dasar_Agregat_0_100", 0), errors="coerce").fillna(0).mean()) | |
| final = float(pd.to_numeric(sub.get("Indeks_Final_Agregat_0_100", 0), errors="coerce").fillna(0).mean()) | |
| rows_by_jenis[jenis] = { | |
| "Jenis": jenis, | |
| "Jumlah_Wilayah": jumlah_wilayah, | |
| "Total_Perpus": terkumpul, | |
| "Pop_Total_Jenis": pop_total, | |
| "Target33_88_Total_Jenis": target3388, | |
| "Terkumpul_Jenis": terkumpul, | |
| "Coverage_Target33_88_Jenis_%": float(coverage), | |
| "Indeks_Dasar_0_100": float(dasar), | |
| "Indeks_Final_Disesuaikan_0_100": float(final), | |
| "Penyesuaian_Poin": float(final - dasar), | |
| } | |
| rows = [rows_by_jenis[j] for j in jenis_list] | |
| dasar_all = (rows_by_jenis["sekolah"]["Indeks_Dasar_0_100"] | |
| + rows_by_jenis["umum"]["Indeks_Dasar_0_100"] | |
| + rows_by_jenis["khusus"]["Indeks_Dasar_0_100"]) / 3.0 | |
| final_all = (rows_by_jenis["sekolah"]["Indeks_Final_Disesuaikan_0_100"] | |
| + rows_by_jenis["umum"]["Indeks_Final_Disesuaikan_0_100"] | |
| + rows_by_jenis["khusus"]["Indeks_Final_Disesuaikan_0_100"]) / 3.0 | |
| pop_all = int(rows_by_jenis["sekolah"]["Pop_Total_Jenis"] | |
| + rows_by_jenis["umum"]["Pop_Total_Jenis"] | |
| + rows_by_jenis["khusus"]["Pop_Total_Jenis"]) | |
| target_all = int(rows_by_jenis["sekolah"]["Target33_88_Total_Jenis"] | |
| + rows_by_jenis["umum"]["Target33_88_Total_Jenis"] | |
| + rows_by_jenis["khusus"]["Target33_88_Total_Jenis"]) | |
| terkumpul_all = int(rows_by_jenis["sekolah"]["Terkumpul_Jenis"] | |
| + rows_by_jenis["umum"]["Terkumpul_Jenis"] | |
| + rows_by_jenis["khusus"]["Terkumpul_Jenis"]) | |
| coverage_all = (terkumpul_all / target_all * 100.0) if target_all > 0 else 0.0 | |
| jumlah_wilayah_all = int(agg_total.shape[0]) if (agg_total is not None and not agg_total.empty) else int( | |
| max(rows_by_jenis["sekolah"]["Jumlah_Wilayah"], | |
| rows_by_jenis["umum"]["Jumlah_Wilayah"], | |
| rows_by_jenis["khusus"]["Jumlah_Wilayah"]) | |
| ) | |
| rows.append({ | |
| "Jenis": "keseluruhan", | |
| "Jumlah_Wilayah": jumlah_wilayah_all, | |
| "Total_Perpus": terkumpul_all, | |
| "Pop_Total_Jenis": pop_all, | |
| "Target33_88_Total_Jenis": target_all, | |
| "Terkumpul_Jenis": terkumpul_all, | |
| "Coverage_Target33_88_Jenis_%": float(coverage_all), | |
| "Indeks_Dasar_0_100": float(dasar_all), | |
| "Indeks_Final_Disesuaikan_0_100": float(final_all), | |
| "Penyesuaian_Poin": float(final_all - dasar_all), | |
| }) | |
| out = pd.DataFrame(rows) | |
| for c in ["Jumlah_Wilayah","Total_Perpus","Pop_Total_Jenis","Target33_88_Total_Jenis","Terkumpul_Jenis"]: | |
| out[c] = pd.to_numeric(out[c], errors="coerce").fillna(0).round(0).astype(int) | |
| for c in ["Coverage_Target33_88_Jenis_%","Indeks_Dasar_0_100","Indeks_Final_Disesuaikan_0_100","Penyesuaian_Poin"]: | |
| out[c] = pd.to_numeric(out[c], errors="coerce").fillna(0.0).round(2) | |
| return out | |
| # ============================================================ | |
| # 10) DETAIL ENTITAS (Final menempel dari wilayah) | |
| # ============================================================ | |
| def attach_final_to_detail(df_filtered, agg_total, meta, kew_value): | |
| if df_filtered is None or df_filtered.empty: | |
| return pd.DataFrame() | |
| kew_norm = str(kew_value or "").upper() | |
| df = df_filtered.copy() | |
| if "PROV" in kew_norm: | |
| key_col = "prov_key" | |
| label_cols = ("PROV_DISP", "KAB_DISP") | |
| else: | |
| key_col = "kab_key" | |
| label_cols = ("PROV_DISP", "KAB_DISP") | |
| if agg_total is None or agg_total.empty: | |
| df["Indeks_Final_0_100"] = df["Indeks_Dasar_0_100"] | |
| else: | |
| m = agg_total[["group_key", "Indeks_Final_Wilayah_0_100"]].copy() | |
| df = df.merge(m, left_on=key_col, right_on="group_key", how="left") | |
| df["Indeks_Final_0_100"] = df["Indeks_Final_Wilayah_0_100"].fillna(df["Indeks_Dasar_0_100"]) | |
| df = df.drop(columns=[c for c in ["group_key","Indeks_Final_Wilayah_0_100"] if c in df.columns]) | |
| base_cols = [label_cols[0], label_cols[1], "KEW_NORM", "_dataset"] | |
| if meta.get("nama_col") and meta["nama_col"] in df.columns: | |
| df["nm_perpustakaan"] = df[meta["nama_col"]].astype(str) | |
| base_cols.insert(2, "nm_perpustakaan") | |
| keep = base_cols + [ | |
| "sub_koleksi","sub_sdm","sub_pelayanan","sub_pengelolaan", | |
| "dim_kepatuhan","dim_kinerja", | |
| "Indeks_Dasar_0_100", | |
| "Indeks_Final_0_100", | |
| ] | |
| keep = [c for c in keep if c in df.columns] | |
| out = df[keep].copy() | |
| out = out.rename(columns={label_cols[0]:"Provinsi", label_cols[1]:"Kab/Kota", "_dataset":"Jenis"}) | |
| for c in ["sub_koleksi","sub_sdm","sub_pelayanan","sub_pengelolaan","dim_kepatuhan","dim_kinerja"]: | |
| if c in out.columns: | |
| out[c] = pd.to_numeric(out[c], errors="coerce").fillna(0.0).round(3) | |
| for c in ["Indeks_Dasar_0_100","Indeks_Final_0_100"]: | |
| if c in out.columns: | |
| out[c] = pd.to_numeric(out[c], errors="coerce").fillna(0.0).round(2) | |
| return out | |
| # ============================================================ | |
| # 11) VERIF (kecukupan sampel) | |
| # ============================================================ | |
| def build_verif_jenis(faktor_wilayah_jenis, kew_value): | |
| if faktor_wilayah_jenis is None or faktor_wilayah_jenis.empty: | |
| return pd.DataFrame() | |
| kew_norm = str(kew_value or "").upper() | |
| label_col = "Provinsi" if "PROV" in kew_norm else "Kab/Kota" | |
| out = faktor_wilayah_jenis.copy() | |
| keep = [c for c in [ | |
| label_col, "Jenis", | |
| "pop_total_jenis", "target_total_33_88_jenis", "n_jenis", | |
| "coverage_jenis_%", "faktor_penyesuaian_jenis", "gap_target33_88_jenis" | |
| ] if c in out.columns] | |
| out = out[keep].copy() | |
| for c in ["pop_total_jenis", "target_total_33_88_jenis", "n_jenis", "gap_target33_88_jenis"]: | |
| if c in out.columns: | |
| out[c] = pd.to_numeric(out[c], errors="coerce").fillna(0).round(0).astype(int) | |
| if "coverage_jenis_%" in out.columns: | |
| out["coverage_jenis_%"] = pd.to_numeric(out["coverage_jenis_%"], errors="coerce").fillna(0.0).round(2) | |
| if "faktor_penyesuaian_jenis" in out.columns: | |
| out["faktor_penyesuaian_jenis"] = pd.to_numeric(out["faktor_penyesuaian_jenis"], errors="coerce").fillna(1.0).round(3) | |
| return out | |
| # ============================================================ | |
| # 12) BELL CURVE — Indeks Dasar per Entitas (per Jenis) + Hover | |
| # ============================================================ | |
| def _make_bell_curve_entitas(dfp, title, xcol="Indeks_Dasar_0_100", label_col="nm_perpustakaan", hover_cols=None, min_points=2): | |
| fig = go.Figure() | |
| fig.update_layout( | |
| title=title, | |
| xaxis_title="Skor (0–100)", | |
| yaxis_title="Kepadatan", | |
| hovermode="closest", | |
| margin=dict(l=40, r=20, t=60, b=40), | |
| legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="left", x=0), | |
| ) | |
| if dfp is None or dfp.empty or xcol not in dfp.columns: | |
| fig.add_annotation(text="Tidak ada data untuk ditampilkan.", x=0.5, y=0.5, xref="paper", yref="paper", showarrow=False) | |
| fig.update_xaxes(range=[0, 100]) | |
| fig.update_yaxes(rangemode="tozero") | |
| return fig | |
| d = dfp.dropna(subset=[xcol]).copy() | |
| if d.empty: | |
| fig.add_annotation(text="Tidak ada data untuk ditampilkan.", x=0.5, y=0.5, xref="paper", yref="paper", showarrow=False) | |
| fig.update_xaxes(range=[0, 100]) | |
| fig.update_yaxes(rangemode="tozero") | |
| return fig | |
| x = pd.to_numeric(d[xcol], errors="coerce").astype(float) | |
| d = d.loc[x.notna()].copy() | |
| x = x.loc[x.notna()].values | |
| if len(x) < 1: | |
| fig.add_annotation(text="Tidak ada data untuk ditampilkan.", x=0.5, y=0.5, xref="paper", yref="paper", showarrow=False) | |
| fig.update_xaxes(range=[0, 100]) | |
| fig.update_yaxes(rangemode="tozero") | |
| return fig | |
| hover_cols = hover_cols or [] | |
| def _val(row, col): | |
| if col not in row.index: | |
| return "" | |
| v = row[col] | |
| return "" if pd.isna(v) else str(v) | |
| hover_text = [] | |
| for _, row in d.iterrows(): | |
| lines = [] | |
| nm = _val(row, label_col) if (label_col and label_col in d.columns) else "" | |
| if nm: | |
| lines.append(f"<b>{nm}</b>") | |
| lines.append(f"{xcol}: {float(pd.to_numeric(row[xcol], errors='coerce')):.2f}") | |
| for hc in hover_cols: | |
| vv = _val(row, hc) | |
| if vv: | |
| lines.append(f"{hc}: {vv}") | |
| hover_text.append("<br>".join(lines)) | |
| if len(x) < min_points: | |
| x_single = float(x[0]) | |
| fig.add_trace(go.Scatter( | |
| x=[x_single], y=[0], | |
| mode="markers", showlegend=False, | |
| hovertext=[hover_text[0]] if hover_text else None, | |
| hoverinfo="text" | |
| )) | |
| fig.add_vline(x=x_single, line_width=1, line_dash="dash", annotation_text=f"Nilai: {x_single:.1f}", annotation_position="top") | |
| fig.update_xaxes(range=[0, 100]) | |
| fig.update_yaxes(rangemode="tozero") | |
| return fig | |
| mu = float(np.mean(x)) | |
| sigma = float(np.std(x, ddof=1)) if len(x) > 1 else 1.0 | |
| sigma = max(sigma, 1e-3) | |
| xmin = max(0.0, float(np.min(x)) - 5.0) | |
| xmax = min(100.0, float(np.max(x)) + 5.0) | |
| xs = np.linspace(xmin, xmax, 250) | |
| pdf = (1.0 / (sigma * np.sqrt(2 * np.pi))) * np.exp(-0.5 * ((xs - mu) / sigma) ** 2) | |
| fig.add_trace(go.Scatter(x=xs, y=pdf, mode="lines", name="Kurva Normal (fit)")) | |
| fig.add_trace(go.Scatter( | |
| x=x, y=np.zeros_like(x), | |
| mode="markers", showlegend=False, | |
| hovertext=hover_text if hover_text else None, | |
| hoverinfo="text" | |
| )) | |
| q1, q2, q3 = np.percentile(x, [25, 50, 75]) | |
| for xv, lab in [(q1, "Q1"), (q2, "Q2 (Median)"), (q3, "Q3"), (mu, "Mean")]: | |
| fig.add_vline(x=float(xv), line_width=1, line_dash="dash", annotation_text=f"{lab}: {xv:.1f}", annotation_position="top") | |
| fig.update_xaxes(range=[0, 100]) | |
| fig.update_yaxes(rangemode="tozero") | |
| return fig | |
| # ============================================================ | |
| # 13) KPI DASHBOARD (2 kartu: final + dasar) | |
| # ============================================================ | |
| def _safe_first(df, col, default=0.0, where=None): | |
| if df is None or df.empty or col not in df.columns: | |
| return default | |
| sub = df | |
| if where is not None: | |
| sub = df.loc[where] | |
| if sub is None or sub.empty: | |
| return default | |
| return float(pd.to_numeric(sub[col], errors="coerce").fillna(default).iloc[0]) | |
| def build_kpi_markdown(summary_jenis): | |
| if summary_jenis is None or summary_jenis.empty: | |
| return "" | |
| final_all = _safe_first(summary_jenis, "Indeks_Final_Disesuaikan_0_100", 0.0, where=summary_jenis["Jenis"].astype(str).str.lower().eq("keseluruhan")) | |
| dasar_all = _safe_first(summary_jenis, "Indeks_Dasar_0_100", 0.0, where=summary_jenis["Jenis"].astype(str).str.lower().eq("keseluruhan")) | |
| def fmt(x, nd=2): | |
| return "NA" if pd.isna(x) else f"{x:.{nd}f}" | |
| return f""" | |
| <div style="display:flex; gap:12px; flex-wrap:wrap;"> | |
| <div style="border:1px solid #333; border-radius:10px; padding:10px 12px; min-width:260px;"> | |
| <div style="opacity:0.8;">Indeks IPLM FINAL (Disesuaikan 33.88%)</div> | |
| <div style="font-size:26px; font-weight:700;">{fmt(final_all,2)}</div> | |
| <div style="opacity:0.7;">Skor absolut (untuk akuntabilitas)</div> | |
| </div> | |
| <div style="border:1px solid #333; border-radius:10px; padding:10px 12px; min-width:260px;"> | |
| <div style="opacity:0.8;">Indeks Dasar (Tanpa Penyesuaian)</div> | |
| <div style="font-size:26px; font-weight:700;">{fmt(dasar_all,2)}</div> | |
| <div style="opacity:0.7;">Sebelum faktor kecukupan sampel</div> | |
| </div> | |
| </div> | |
| """.strip() | |
| # ============================================================ | |
| # 14) LLM: Isi Interpretasi & Rekomendasi (TABEL) + WORD | |
| # ============================================================ | |
| _HF_CLIENT = None | |
| def get_llm_client(): | |
| global _HF_CLIENT | |
| if _HF_CLIENT is not None: | |
| return _HF_CLIENT | |
| if (not HF_AVAILABLE) or (InferenceClient is None): | |
| _HF_CLIENT = None | |
| return None | |
| try: | |
| _HF_CLIENT = InferenceClient(model=LLM_MODEL_NAME, token=HF_TOKEN) if HF_TOKEN else InferenceClient(model=LLM_MODEL_NAME) | |
| return _HF_CLIENT | |
| except Exception: | |
| _HF_CLIENT = None | |
| return None | |
| def _to_float(x, default=0.0): | |
| try: | |
| if x is None: | |
| return float(default) | |
| if isinstance(x, float) and math.isnan(x): | |
| return float(default) | |
| return float(x) | |
| except Exception: | |
| return float(default) | |
| def summarize_jumlah_perpus_dari_agg_jenis(agg_jenis_full, wilayah_label, kew_value): | |
| """ | |
| Ambil jumlah perpustakaan sumber data dari tabel agregat wilayah × jenis (gambar 2). | |
| Untuk filter 1 wilayah (kab/prov), agg_jenis_full biasanya 3 baris (sekolah/umum/khusus). | |
| Untuk nasional/semua wilayah, ini akan menjumlahkan seluruh wilayah per jenis. | |
| """ | |
| if agg_jenis_full is None or agg_jenis_full.empty: | |
| return {"sekolah": 0, "umum": 0, "khusus": 0, "total": 0} | |
| a = agg_jenis_full.copy() | |
| if "Jenis" not in a.columns: | |
| return {"sekolah": 0, "umum": 0, "khusus": 0, "total": 0} | |
| a["Jenis"] = a["Jenis"].astype(str).str.lower().str.strip() | |
| if "Jumlah" in a.columns: | |
| a["Jumlah"] = pd.to_numeric(a["Jumlah"], errors="coerce").fillna(0).astype(int) | |
| else: | |
| a["Jumlah"] = 0 | |
| out = {} | |
| for j in ["sekolah", "umum", "khusus"]: | |
| out[j] = int(a.loc[a["Jenis"].eq(j), "Jumlah"].sum()) | |
| out["total"] = int(out["sekolah"] + out["umum"] + out["khusus"]) | |
| return out | |
| def build_interpretasi_table_values(agg_total, wilayah_label, target_ratio): | |
| """ | |
| MENGAMBIL NILAI APA ADANYA (tanpa *100) dari hasil aplikasi (agg_total): | |
| - Kepatuhan = Rata2_dim_kepatuhan | |
| - Koleksi = Rata2_sub_koleksi | |
| - Tenaga = Rata2_sub_sdm | |
| - Kinerja = Rata2_dim_kinerja | |
| - Pelayanan = Rata2_sub_pelayanan | |
| - Pengelolaan = Rata2_sub_pengelolaan | |
| - Nilai IPLM = Indeks_Final_Wilayah_0_100 | |
| Jika agg_total > 1 baris (mis. nasional), diambil mean kolom-kolom tersebut. | |
| """ | |
| if agg_total is None or agg_total.empty: | |
| base = { | |
| "kepatuhan": 0.0, "koleksi": 0.0, "tenaga": 0.0, | |
| "kinerja": 0.0, "pelayanan": 0.0, "pengelolaan": 0.0, | |
| "iplm": 0.0 | |
| } | |
| else: | |
| a = agg_total.copy() | |
| cols_needed = [ | |
| "Rata2_dim_kepatuhan", | |
| "Rata2_sub_koleksi", | |
| "Rata2_sub_sdm", | |
| "Rata2_dim_kinerja", | |
| "Rata2_sub_pelayanan", | |
| "Rata2_sub_pengelolaan", | |
| "Indeks_Final_Wilayah_0_100", | |
| ] | |
| for c in cols_needed: | |
| if c in a.columns: | |
| a[c] = pd.to_numeric(a[c], errors="coerce").fillna(0.0) | |
| else: | |
| a[c] = 0.0 | |
| base = { | |
| "kepatuhan": float(a["Rata2_dim_kepatuhan"].mean()), | |
| "koleksi": float(a["Rata2_sub_koleksi"].mean()), | |
| "tenaga": float(a["Rata2_sub_sdm"].mean()), | |
| "kinerja": float(a["Rata2_dim_kinerja"].mean()), | |
| "pelayanan": float(a["Rata2_sub_pelayanan"].mean()), | |
| "pengelolaan": float(a["Rata2_sub_pengelolaan"].mean()), | |
| "iplm": float(a["Indeks_Final_Wilayah_0_100"].mean()), | |
| } | |
| # pembulatan display (nilai tetap "apa adanya", hanya format) | |
| # untuk sub/dim (0–1) biasanya 3 desimal; untuk IPLM (0–100) 2 desimal. | |
| base_disp = { | |
| "kepatuhan": round(_to_float(base["kepatuhan"]), 3), | |
| "koleksi": round(_to_float(base["koleksi"]), 3), | |
| "tenaga": round(_to_float(base["tenaga"]), 3), | |
| "kinerja": round(_to_float(base["kinerja"]), 3), | |
| "pelayanan": round(_to_float(base["pelayanan"]), 3), | |
| "pengelolaan": round(_to_float(base["pengelolaan"]), 3), | |
| "iplm": round(_to_float(base["iplm"]), 2), | |
| } | |
| rows = [ | |
| {"No":"1", "Dimensi":"Kepatuhan", "Nilai":base_disp["kepatuhan"], "SumberKolom":"Rata2_dim_kepatuhan"}, | |
| {"No":"1.1", "Dimensi":"Variabel Koleksi", "Nilai":base_disp["koleksi"], "SumberKolom":"Rata2_sub_koleksi"}, | |
| {"No":"1.2", "Dimensi":"Variabel Tenaga Perpustakaan", "Nilai":base_disp["tenaga"], "SumberKolom":"Rata2_sub_sdm"}, | |
| {"No":"2", "Dimensi":"Kinerja", "Nilai":base_disp["kinerja"], "SumberKolom":"Rata2_dim_kinerja"}, | |
| {"No":"2.1", "Dimensi":"Variabel Pelayanan", "Nilai":base_disp["pelayanan"], "SumberKolom":"Rata2_sub_pelayanan"}, | |
| {"No":"2.2", "Dimensi":"Variabel Penyelenggaraan/Pengelolaan", "Nilai":base_disp["pengelolaan"], "SumberKolom":"Rata2_sub_pengelolaan"}, | |
| {"No":"4", "Dimensi":"Nilai IPLM", "Nilai":base_disp["iplm"], "SumberKolom":"Indeks_Final_Wilayah_0_100"}, | |
| ] | |
| header = { | |
| "judul": f"Interpretasi dan Rekomendasi IPLM — {wilayah_label}", | |
| "target_sampel": f"{target_ratio*100:.2f}%" | |
| } | |
| return header, rows | |
| def llm_fill_interpretasi_rekomendasi(header, rows, wilayah_label, kew_label, jumlah_perpus_by_jenis): | |
| """ | |
| LLM diminta mengisi kolom Interpretasi dan Rekomendasi dengan narasi yang NYAMBUNG ke angka: | |
| - Interpretasi: jelaskan apa arti angka untuk kondisi operasional perpustakaan (koleksi/sdm/pelayanan/pengelolaan), | |
| memakai relasi angka antardimensi (lebih besar/kecil, selisih, dominan, gap, konsistensi) TANPA label normatif. | |
| - Rekomendasi: 2–3 aksi teknis per baris yang langsung meng-address pola angka (misal dimensi lebih kecil → prioritas aktivitas), | |
| serta mengaitkan dengan volume data (jumlah perpustakaan per jenis) bila relevan. | |
| Output wajib JSON. | |
| """ | |
| client = get_llm_client() | |
| if client is None or (not USE_LLM): | |
| out = [] | |
| for r in rows: | |
| out.append({k: r.get(k) for k in ["No","Dimensi","Nilai"]} | {"Interpretasi":"", "Rekomendasi":""}) | |
| return out, "LLM tidak digunakan / tidak tersedia." | |
| payload = { | |
| "wilayah": wilayah_label, | |
| "kewenangan": kew_label, | |
| "target_sampel_per_jenis": header["target_sampel"], | |
| "jumlah_perpustakaan_sumber_data": jumlah_perpus_by_jenis, | |
| "catatan_skala": ( | |
| "Baris Kepatuhan/Koleksi/Tenaga/Kinerja/Pelayanan/Pengelolaan memakai nilai agregat 'apa adanya' " | |
| "(umumnya rentang 0–1 karena berasal dari sub/dim hasil normalisasi). " | |
| "Baris 'Nilai IPLM' memakai Indeks_Final_Wilayah_0_100 (rentang 0–100)." | |
| ), | |
| "baris": rows | |
| } | |
| system = ( | |
| "Anda adalah analis kebijakan perpustakaan di Indonesia.\n" | |
| "Tugas: isi kolom Interpretasi dan Rekomendasi untuk setiap baris tabel.\n" | |
| "ATURAN WAJIB:\n" | |
| "1) Jangan mengubah nilai angka. Jangan menghitung ulang skor.\n" | |
| "2) Netral-deskriptif: dilarang memakai label normatif seperti baik/buruk, tinggi/sedang/rendah, memuaskan/kurang, optimal/tidak optimal.\n" | |
| "3) Interpretasi harus nyambung langsung ke angka dan relasinya antardimensi: gunakan istilah lebih besar/kecil, selisih, gap, dominan, konsisten/tidak konsisten, kontribusi, proporsi.\n" | |
| "4) Interpretasi juga harus menjelaskan kondisi riil berbasis dimensi:\n" | |
| " - Koleksi: pengembangan, ketersediaan, pemanfaatan koleksi (sebagai fungsi layanan),\n" | |
| " - Tenaga: kecukupan/kapasitas SDM dan pengembangan kompetensi,\n" | |
| " - Pelayanan: aktivitas layanan dan pemanfaatan layanan,\n" | |
| " - Pengelolaan: tata kelola, kebijakan, kolaborasi, dukungan anggaran layanan,\n" | |
| " - Kepatuhan = gabungan koleksi+tenaga; Kinerja = gabungan pelayanan+pengelolaan.\n" | |
| " Jelaskan tanpa menghakimi; fokus pada apa yang angka itu representasikan.\n" | |
| "5) Rekomendasi harus operasional dan spesifik (2–3 butir singkat) untuk tiap baris. Gunakan pola angka untuk menurunkan aksi.\n" | |
| "6) Output HARUS JSON valid saja (tanpa teks tambahan), dengan struktur persis.\n" | |
| ) | |
| user = ( | |
| "Kembalikan JSON:\n" | |
| "{\n" | |
| ' "rows": [\n' | |
| ' {"No":"...","Dimensi":"...","Nilai":..., "Interpretasi":"...","Rekomendasi":"..."}\n' | |
| " ]\n" | |
| "}\n" | |
| "- Urutan dan jumlah baris harus sama.\n" | |
| "- 'Rekomendasi' boleh berupa bullet dengan tanda '-' dalam satu string.\n\n" | |
| f"INPUT:\n{json.dumps(payload, ensure_ascii=False)}" | |
| ) | |
| try: | |
| resp = client.chat_completion( | |
| model=LLM_MODEL_NAME, | |
| messages=[ | |
| {"role": "system", "content": system}, | |
| {"role": "user", "content": user}, | |
| ], | |
| max_tokens=1100, | |
| temperature=0.2, | |
| top_p=0.9, | |
| ) | |
| text = resp.choices[0].message.content.strip() | |
| data = json.loads(text) | |
| rows_out = data.get("rows", []) | |
| if not isinstance(rows_out, list) or len(rows_out) != len(rows): | |
| raise ValueError("Format JSON rows tidak sesuai.") | |
| cleaned = [] | |
| for i, r in enumerate(rows_out): | |
| cleaned.append({ | |
| "No": str(r.get("No", rows[i]["No"])), | |
| "Dimensi": str(r.get("Dimensi", rows[i]["Dimensi"])), | |
| "Nilai": rows[i]["Nilai"], # paksa nilai dari aplikasi | |
| "Interpretasi": str(r.get("Interpretasi","") or ""), | |
| "Rekomendasi": str(r.get("Rekomendasi","") or ""), | |
| }) | |
| return cleaned, "LLM mengisi Interpretasi & Rekomendasi." | |
| except Exception as e: | |
| out = [] | |
| for r in rows: | |
| out.append({k: r.get(k) for k in ["No","Dimensi","Nilai"]} | {"Interpretasi":"", "Rekomendasi":""}) | |
| return out, f"LLM error: {repr(e)}" | |
| def _set_cell_shading(cell, fill_hex="1F1F1F"): | |
| tcPr = cell._tc.get_or_add_tcPr() | |
| shd = OxmlElement("w:shd") | |
| shd.set(qn("w:val"), "clear") | |
| shd.set(qn("w:color"), "auto") | |
| shd.set(qn("w:fill"), fill_hex) | |
| tcPr.append(shd) | |
| def _set_cell_text_color(cell, rgb_hex="FFFFFF"): | |
| for p in cell.paragraphs: | |
| for run in p.runs: | |
| rPr = run._r.get_or_add_rPr() | |
| color = OxmlElement("w:color") | |
| color.set(qn("w:val"), rgb_hex) | |
| rPr.append(color) | |
| def _set_table_borders(table): | |
| tbl = table._tbl | |
| tblPr = tbl.tblPr | |
| if tblPr is None: | |
| tblPr = OxmlElement("w:tblPr") | |
| tbl.append(tblPr) | |
| tblBorders = OxmlElement("w:tblBorders") | |
| for edge in ("top", "left", "bottom", "right", "insideH", "insideV"): | |
| elem = OxmlElement(f"w:{edge}") | |
| elem.set(qn("w:val"), "single") | |
| elem.set(qn("w:sz"), "8") | |
| elem.set(qn("w:space"), "0") | |
| elem.set(qn("w:color"), "FFFFFF") | |
| tblBorders.append(elem) | |
| tblPr.append(tblBorders) | |
| def generate_word_table_interpretasi(header, rows_filled, wilayah_label, jumlah_perpus_by_jenis): | |
| if (not DOCX_AVAILABLE) or (Document is None): | |
| return None | |
| doc = Document() | |
| # Title | |
| title = doc.add_paragraph() | |
| run = title.add_run(header["judul"]) | |
| run.bold = True | |
| run.font.size = Pt(18) | |
| doc.add_paragraph(f"Target sampel per jenis: {header['target_sampel']}") | |
| # Table | |
| cols = ["No", "Dimensi", "Nilai", "Interpretasi", "Rekomendasi"] | |
| table = doc.add_table(rows=1, cols=len(cols)) | |
| table.autofit = True | |
| _set_table_borders(table) | |
| hdr_cells = table.rows[0].cells | |
| for i, c in enumerate(cols): | |
| hdr_cells[i].text = c | |
| _set_cell_shading(hdr_cells[i], "1A1A1A") | |
| _set_cell_text_color(hdr_cells[i], "FFFFFF") | |
| for p in hdr_cells[i].paragraphs: | |
| for rr in p.runs: | |
| rr.bold = True | |
| for r in rows_filled: | |
| row_cells = table.add_row().cells | |
| row_cells[0].text = str(r.get("No","")) | |
| row_cells[1].text = str(r.get("Dimensi","")) | |
| # format nilai: | |
| # - sub/dim biasanya 0–1 → tampilkan 3 desimal | |
| # - IPLM 0–100 → tampilkan 2 desimal | |
| try: | |
| dim = str(r.get("Dimensi","")).strip().lower() | |
| val = _to_float(r.get("Nilai", 0.0), 0.0) | |
| if dim == "nilai iplm": | |
| row_cells[2].text = f"{val:.2f}" | |
| else: | |
| row_cells[2].text = f"{val:.3f}" | |
| except Exception: | |
| row_cells[2].text = str(r.get("Nilai","")) | |
| row_cells[3].text = str(r.get("Interpretasi","") or "") | |
| row_cells[4].text = str(r.get("Rekomendasi","") or "") | |
| for c in row_cells: | |
| _set_cell_shading(c, "262626") | |
| _set_cell_text_color(c, "FFFFFF") | |
| # ===== tambahan: deskripsi jumlah perpustakaan sumber data (gambar 2) ===== | |
| doc.add_paragraph("") # spacer | |
| j = jumlah_perpus_by_jenis or {"sekolah":0,"umum":0,"khusus":0,"total":0} | |
| p = doc.add_paragraph() | |
| p.add_run("Sumber data (jumlah perpustakaan pada tabel agregat wilayah × jenis): ").bold = True | |
| doc.add_paragraph( | |
| f"Perpustakaan sekolah = {int(j.get('sekolah',0))}, " | |
| f"perpustakaan umum = {int(j.get('umum',0))}, " | |
| f"perpustakaan khusus = {int(j.get('khusus',0))}, " | |
| f"total = {int(j.get('total',0))}." | |
| ) | |
| outpath = tempfile.mktemp(suffix=".docx") | |
| doc.save(outpath) | |
| return outpath | |
| # ============================================================ | |
| # 15) CORE RUN | |
| # ============================================================ | |
| def _empty_outputs(msg="Data belum siap."): | |
| empty = pd.DataFrame() | |
| empty_fig = go.Figure() | |
| return ( | |
| "", # kpi_md | |
| empty, empty, empty, empty, empty, | |
| None, None, None, None, None, | |
| empty_fig, empty_fig, empty_fig, | |
| msg, # msg | |
| "LLM belum tersedia.", # status llm | |
| None # word path | |
| ) | |
| def run_calc(prov_value, kab_value, kew_value, df_all, df_raw, pop_kab, pop_prov, pop_khusus, meta): | |
| try: | |
| if df_all is None or df_all.empty or df_raw is None or df_raw.empty: | |
| return _empty_outputs("Data belum ter-load. Pastikan file tersedia.") | |
| # Filter | |
| df = df_all.copy() | |
| if prov_value and prov_value != "(Semua)": | |
| df = df[df["PROV_DISP"] == prov_value] | |
| if kab_value and kab_value != "(Semua)": | |
| df = df[df["KAB_DISP"] == kab_value] | |
| if kew_value and kew_value != "(Semua)": | |
| df = df[df["KEW_NORM"] == kew_value] | |
| if df.empty: | |
| return _empty_outputs("Tidak ada data untuk filter ini.") | |
| kew_norm = kew_value if (kew_value and kew_value != "(Semua)") else "(Semua)" | |
| faktor_wilayah_jenis = build_faktor_wilayah_jenis(df, pop_kab, pop_prov, pop_khusus, kew_norm) | |
| agg_jenis_full = build_agg_wilayah_jenis(df, faktor_wilayah_jenis, kew_norm) | |
| agg_total = build_agg_wilayah_total_from_jenis(agg_jenis_full, faktor_wilayah_jenis, kew_norm) | |
| summary_jenis = build_summary_per_jenis(agg_jenis_full, agg_total) | |
| verif_total = build_verif_jenis(faktor_wilayah_jenis, kew_norm) | |
| detail_view = attach_final_to_detail(df, agg_total, meta, kew_norm) | |
| # agg_jenis view (UI hanya sampai indeks dasar) | |
| if agg_jenis_full is None or agg_jenis_full.empty: | |
| agg_jenis_view = agg_jenis_full | |
| else: | |
| kew_norm2 = str(kew_norm).upper() | |
| label_name = "Kab/Kota" if ("KAB" in kew_norm2 or "KOTA" in kew_norm2) else ("Provinsi" if "PROV" in kew_norm2 else "Kab/Kota") | |
| cols_upto = [ | |
| "group_key", | |
| label_name, | |
| "Jenis", | |
| "Jumlah", | |
| "Rata2_sub_koleksi","Rata2_sub_sdm","Rata2_sub_pelayanan","Rata2_sub_pengelolaan", | |
| "Rata2_dim_kepatuhan","Rata2_dim_kinerja", | |
| "Indeks_Dasar_Agregat_0_100", | |
| ] | |
| cols_upto = [c for c in cols_upto if c in agg_jenis_full.columns] | |
| agg_jenis_view = agg_jenis_full[cols_upto].copy() | |
| # RAW download (hasil filter) | |
| raw = df_raw.copy() | |
| if prov_value and prov_value != "(Semua)": | |
| raw = raw[raw["PROV_DISP"] == prov_value] | |
| if kab_value and kab_value != "(Semua)": | |
| raw = raw[raw["KAB_DISP"] == kab_value] | |
| if kew_value and kew_value != "(Semua)": | |
| raw = raw[raw["KEW_NORM"] == kew_value] | |
| # Bell curve per jenis | |
| if detail_view is None or detail_view.empty: | |
| fig_umum = _make_bell_curve_entitas(pd.DataFrame(), "Bell Curve — Jenis: Umum") | |
| fig_sekolah = _make_bell_curve_entitas(pd.DataFrame(), "Bell Curve — Jenis: Sekolah") | |
| fig_khusus = _make_bell_curve_entitas(pd.DataFrame(), "Bell Curve — Jenis: Khusus") | |
| else: | |
| hover_cols = [hc for hc in ["Provinsi", "Kab/Kota", "Jenis"] if hc in detail_view.columns] | |
| def _fig(j): | |
| d = detail_view[detail_view["Jenis"].astype(str).str.lower() == j].copy() | |
| return _make_bell_curve_entitas( | |
| d, | |
| title=f"Bell Curve — Jenis: {j.title()} (Skor: Indeks_Dasar_0_100)", | |
| xcol="Indeks_Dasar_0_100", | |
| label_col=("nm_perpustakaan" if "nm_perpustakaan" in d.columns else "nm_perpustakaan"), | |
| hover_cols=hover_cols, | |
| min_points=2 | |
| ) | |
| fig_sekolah = _fig("sekolah") | |
| fig_umum = _fig("umum") | |
| fig_khusus = _fig("khusus") | |
| kpi_md = build_kpi_markdown(summary_jenis) | |
| # Export xlsx | |
| tmpdir = tempfile.mkdtemp() | |
| prov_slug = (_canon(prov_value or "SEMUA").upper() or "SEMUA") | |
| kab_slug = (_canon(kab_value or "SEMUA").upper() or "SEMUA") | |
| kew_slug = (_canon(kew_value or "SEMUA").upper() or "SEMUA") | |
| p_summary = str(Path(tmpdir) / f"IPLM_RingkasanJenisKeseluruhan_33_88_{prov_slug}_{kab_slug}_{kew_slug}.xlsx") | |
| p_total = str(Path(tmpdir) / f"IPLM_AgregatWilayah_Keseluruhan_33_88_{prov_slug}_{kab_slug}_{kew_slug}.xlsx") | |
| p_raw = str(Path(tmpdir) / f"IPLM_RAW_DATA_{prov_slug}_{kab_slug}_{kew_slug}.xlsx") | |
| p_detail = str(Path(tmpdir) / f"IPLM_DetailEntitas_FinalMenempelWilayah_{prov_slug}_{kab_slug}_{kew_slug}.xlsx") | |
| p_verif = str(Path(tmpdir) / f"IPLM_KecukupanSampel_33_88_{prov_slug}_{kab_slug}_{kew_slug}.xlsx") | |
| summary_jenis.to_excel(p_summary, index=False) | |
| agg_total.to_excel(p_total, index=False) | |
| raw.to_excel(p_raw, index=False) | |
| detail_view.to_excel(p_detail, index=False) | |
| verif_total.to_excel(p_verif, index=False) | |
| # ====== Word tabel interpretasi & rekomendasi ====== | |
| wilayah_txt = kab_value if (kab_value and kab_value != "(Semua)") else (prov_value if (prov_value and prov_value != "(Semua)") else "Nasional/All") | |
| header, rows = build_interpretasi_table_values(agg_total, wilayah_txt, TARGET_RATIO) | |
| # jumlah perpustakaan sumber data (gambar 2) | |
| jumlah_perpus = summarize_jumlah_perpus_dari_agg_jenis(agg_jenis_full, wilayah_txt, kew_norm) | |
| rows_filled, llm_status = llm_fill_interpretasi_rekomendasi( | |
| header=header, | |
| rows=rows, | |
| wilayah_label=wilayah_txt, | |
| kew_label=(kew_value or "(Semua)"), | |
| jumlah_perpus_by_jenis=jumlah_perpus | |
| ) | |
| word_path = generate_word_table_interpretasi(header, rows_filled, wilayah_txt, jumlah_perpus) | |
| msg = ( | |
| f"Selesai (TARGET {TARGET_RATIO*100:.2f}%): raw={len(raw)} | entitas={len(detail_view)} | " | |
| f"wilayah(keseluruhan)={len(agg_total)} | jenis(agregat)={len(agg_jenis_full)}" | |
| + ("" if DOCX_AVAILABLE else " | python-docx tidak tersedia (Word OFF)") | |
| ) | |
| return ( | |
| kpi_md, | |
| summary_jenis, agg_total, agg_jenis_view, detail_view, verif_total, | |
| p_summary, p_total, p_raw, p_detail, p_verif, | |
| fig_umum, fig_sekolah, fig_khusus, | |
| msg, | |
| llm_status, | |
| (word_path if word_path else None) | |
| ) | |
| except Exception as e: | |
| return _empty_outputs(f"Runtime error: {repr(e)}") | |
| # ============================================================ | |
| # 16) UI (NO UPLOAD) | |
| # ============================================================ | |
| def ui_load(force=False): | |
| df_all, df_raw, pop_kab, pop_prov, pop_khusus, meta, info = load_default_files(force=force) | |
| if df_all is None or (isinstance(df_all, pd.DataFrame) and df_all.empty): | |
| return ( | |
| None, None, None, None, None, {}, info, | |
| gr.update(choices=["(Semua)"], value="(Semua)"), | |
| gr.update(choices=["(Semua)"], value="(Semua)"), | |
| gr.update(choices=["(Semua)"], value="(Semua)"), | |
| ) | |
| prov_vals = df_all["PROV_DISP"].dropna().astype(str).tolist() | |
| prov_vals = [v for v in prov_vals if v and v.strip()] | |
| prov_choices = ["(Semua)"] + sorted(set(prov_vals)) | |
| kab_choices = ["(Semua)"] + sorted([x for x in df_all["KAB_DISP"].dropna().unique().tolist() if x]) | |
| kew_choices = ["(Semua)"] + sorted([x for x in df_all["KEW_NORM"].dropna().unique().tolist() if x]) | |
| default_kew = "KAB/KOTA" if "KAB/KOTA" in kew_choices else ("PROVINSI" if "PROVINSI" in kew_choices else "(Semua)") | |
| return ( | |
| df_all, df_raw, pop_kab, pop_prov, pop_khusus, meta, info, | |
| gr.update(choices=prov_choices, value="(Semua)"), | |
| gr.update(choices=kab_choices, value="(Semua)"), | |
| gr.update(choices=kew_choices, value=default_kew), | |
| ) | |
| def on_prov_change(prov_value): | |
| df_all, _, _, _, _, _, _ = load_default_files(force=False) | |
| if df_all is None or df_all.empty: | |
| return gr.update(choices=["(Semua)"], value="(Semua)") | |
| if prov_value is None or prov_value == "(Semua)": | |
| vals = df_all["KAB_DISP"].dropna().unique().tolist() | |
| else: | |
| vals = df_all.loc[df_all["PROV_DISP"] == prov_value, "KAB_DISP"].dropna().unique().tolist() | |
| vals = sorted([v for v in vals if v]) | |
| return gr.update(choices=["(Semua)"] + vals, value="(Semua)") | |
| with gr.Blocks() as demo: | |
| gr.Markdown(f""" | |
| # IPLM 2025 — Final (Target Sampel 33.88% per Jenis) — TANPA Kinerja Relatif / Percentile | |
| Mode NO UPLOAD (cache aktif). File dibaca dari repo/server: | |
| - DATA_FILE = {DATA_FILE} | |
| - POP_KAB = {POP_KAB} | |
| - POP_PROV = {POP_PROV} | |
| - POP_KHUSUS = {POP_KHUSUS} | |
| TARGET RATIO (per jenis): {TARGET_RATIO*100:.2f}% | |
| Dashboard KPI: | |
| - Indeks IPLM FINAL (disesuaikan 33.88%) | |
| - Indeks Dasar (tanpa penyesuaian) | |
| UPDATE LLM + WORD: | |
| - Tabel Word "Interpretasi & Rekomendasi" memakai NILAI APA ADANYA (tanpa dikali 100) untuk sub/dim. | |
| - Baris "Nilai IPLM" memakai Indeks_Final_Wilayah_0_100 apa adanya. | |
| - Di bawah tabel Word ditambahkan ringkasan jumlah perpustakaan sumber data (sekolah/umum/khusus/total) dari tabel agregat wilayah × jenis. | |
| """) | |
| state_df = gr.State(None) | |
| state_raw = gr.State(None) | |
| state_pop_kab = gr.State(None) | |
| state_pop_prov = gr.State(None) | |
| state_pop_khusus = gr.State(None) | |
| state_meta = gr.State({}) | |
| info_box = gr.Markdown() | |
| with gr.Row(): | |
| dd_prov = gr.Dropdown(label="Provinsi", choices=["(Semua)"], value="(Semua)") | |
| dd_kab = gr.Dropdown(label="Kab/Kota", choices=["(Semua)"], value="(Semua)") | |
| dd_kew = gr.Dropdown(label="Kewenangan", choices=["(Semua)"], value="(Semua)") | |
| dd_prov.change(fn=on_prov_change, inputs=[dd_prov], outputs=dd_kab) | |
| run_btn = gr.Button("Jalankan Perhitungan") | |
| msg_out = gr.Markdown() | |
| kpi_out = gr.Markdown() | |
| gr.Markdown("## Ringkasan (Jenis + Keseluruhan)") | |
| out_summary = gr.DataFrame(interactive=False) | |
| gr.Markdown("## Agregat Wilayah (Keseluruhan) — FIX avg3") | |
| out_agg_total = gr.DataFrame(interactive=False) | |
| gr.Markdown("## Agregat Wilayah x Jenis — (ditampilkan sampai Indeks Dasar)") | |
| out_agg_jenis = gr.DataFrame(interactive=False) | |
| gr.Markdown("## Detail Entitas (Final menempel dari wilayah)") | |
| out_detail = gr.DataFrame(interactive=False) | |
| gr.Markdown("## Kecukupan Sampel 33.88%") | |
| out_verif = gr.DataFrame(interactive=False) | |
| gr.Markdown("## Bell Curve — Indeks Dasar per Entitas (per Jenis) + Nama Perpustakaan") | |
| gr.Markdown("### Perpustakaan Umum") | |
| bell_umum = gr.Plot(scale=1) | |
| gr.Markdown("### Perpustakaan Sekolah") | |
| bell_sekolah = gr.Plot(scale=1) | |
| gr.Markdown("### Perpustakaan Khusus") | |
| bell_khusus = gr.Plot(scale=1) | |
| gr.Markdown("## Status LLM (Isi Interpretasi & Rekomendasi)") | |
| llm_status_out = gr.Markdown() | |
| with gr.Row(): | |
| dl_summary = gr.DownloadButton(label="Download Ringkasan (.xlsx)") | |
| dl_total = gr.DownloadButton(label="Download Agregat Wilayah (.xlsx)") | |
| dl_raw = gr.DownloadButton(label="Download Data Mentah (.xlsx)") | |
| dl_detail = gr.DownloadButton(label="Download Detail Entitas (.xlsx)") | |
| dl_verif = gr.DownloadButton(label="Download Kecukupan Sampel (.xlsx)") | |
| dl_word = gr.DownloadButton(label="Download Word: Interpretasi & Rekomendasi (.docx)" if DOCX_AVAILABLE else "Download Word (OFF)") | |
| run_btn.click( | |
| fn=run_calc, | |
| inputs=[dd_prov, dd_kab, dd_kew, state_df, state_raw, state_pop_kab, state_pop_prov, state_pop_khusus, state_meta], | |
| outputs=[ | |
| kpi_out, | |
| out_summary, out_agg_total, out_agg_jenis, out_detail, out_verif, | |
| dl_summary, dl_total, dl_raw, dl_detail, dl_verif, | |
| bell_umum, bell_sekolah, bell_khusus, | |
| msg_out, | |
| llm_status_out, | |
| dl_word | |
| ] | |
| ) | |
| demo.load( | |
| fn=lambda: ui_load(force=False), | |
| inputs=[], | |
| outputs=[state_df, state_raw, state_pop_kab, state_pop_prov, state_pop_khusus, state_meta, info_box, dd_prov, dd_kab, dd_kew] | |
| ) | |
| demo.launch() |