notebooks/IPLM_Model_Benchmark.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {"id": "header"},
      "source": [
        "# 🏆 IPLM Model Benchmark — Evaluasi & Perbandingan Model AI\n",
        "\n",
        "Notebook ini membandingkan kualitas **interpretasi & rekomendasi kebijakan perpustakaan (IPLM)** dari beberapa model AI:\n",
        "\n",
        "| Model | Provider | API |\n",
        "|---|---|---|\n",
        "| `gpt-4o-mini` | Sumopod (OpenAI-compatible) | OpenAI SDK |\n",
        "| `llama-3.3-70b-versatile` | Groq | Groq SDK |\n",
        "| `gemma2-9b-it` / `mixtral-8x7b-32768` | Groq | Groq SDK |\n",
        "| `claude-sonnet-4-20250514` | Anthropic | Anthropic SDK |\n",
        "\n",
        "## Metode Evaluasi\n",
        "1. **Skor Otomatis** — Rubrik kriteria terukur (panjang teks, kelengkapan komponen, kepatuhan format)\n",
        "2. **LLM-as-Judge** — Model AI menilai output model lain secara terstruktur\n",
        "3. **Perbandingan Side-by-Side** — Tampilan manual berdampingan per wilayah\n",
        "\n",
        "## Cara Pakai\n",
        "1. Jalankan **Cell 1** (install) → **Cell 2** (API keys) → **Cell 3** (upload data)\n",
        "2. Jalankan **Cell 4** (konfigurasi) → **Cell 5** (fungsi)\n",
        "3. Jalankan **Cell 6** untuk generate output semua model\n",
        "4. Jalankan **Cell 7** untuk evaluasi otomatis\n",
        "5. Jalankan **Cell 8** untuk LLM-as-Judge\n",
        "6. Jalankan **Cell 9** untuk dashboard side-by-side\n",
        "7. Jalankan **Cell 10** untuk export hasil benchmark ke Excel\n",
        "\n",
        "> ⚠️ **Jalankan cell per cell — JANGAN Run All**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {"id": "cell1"},
      "source": [
        "# ============================================================\n",
        "# CELL 1 — Install Library\n",
        "# Jalankan SEKALI per sesi\n",
        "# ============================================================\n",
        "import subprocess, sys\n",
        "\n",
        "print('📦 Menginstall library...')\n",
        "subprocess.run([\n",
        "    sys.executable, '-m', 'pip', 'install',\n",
        "    'openai', 'groq', 'anthropic',\n",
        "    'openpyxl', 'xlsxwriter', 'ipywidgets',\n",
        "    '-q'\n",
        "], check=True)\n",
        "\n",
        "import pandas as pd\n",
        "import time, json, re, io, textwrap\n",
        "import ipywidgets as widgets\n",
        "from IPython.display import display, clear_output, HTML\n",
        "import warnings\n",
        "warnings.filterwarnings('ignore')\n",
        "\n",
        "print('✅ Semua library berhasil dimuat')\n",
        "print('   → Lanjut ke Cell 2')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {"id": "cell2"},
      "source": [
        "# ============================================================\n",
        "# CELL 2 — Masukkan API Keys\n",
        "# Daftarkan masing-masing API key di sini\n",
        "# ============================================================\n",
        "from getpass import getpass\n",
        "\n",
        "print('🔑 Masukkan API Keys (tekan Enter jika tidak digunakan):')\n",
        "print()\n",
        "\n",
        "SUMOPOD_API_KEY   = getpass('Sumopod API Key  (gpt-4o-mini)       : ').strip()\n",
        "GROQ_API_KEY      = getpass('Groq API Key     (llama/gemma/mixtral): ').strip()\n",
        "ANTHROPIC_API_KEY = getpass('Anthropic API Key (claude)            : ').strip()\n",
        "\n",
        "# ── Inisialisasi client yang tersedia ─────────────────────\n",
        "clients = {}\n",
        "\n",
        "if SUMOPOD_API_KEY:\n",
        "    from openai import OpenAI\n",
        "    clients['sumopod'] = OpenAI(\n",
        "        api_key=SUMOPOD_API_KEY,\n",
        "        base_url='https://ai.sumopod.com/v1'\n",
        "    )\n",
        "    print('✅ Sumopod (GPT-4o-mini) siap')\n",
        "\n",
        "if GROQ_API_KEY:\n",
        "    from groq import Groq\n",
        "    clients['groq'] = Groq(api_key=GROQ_API_KEY)\n",
        "    print('✅ Groq (Llama / Gemma / Mixtral) siap')\n",
        "\n",
        "if ANTHROPIC_API_KEY:\n",
        "    import anthropic\n",
        "    clients['anthropic'] = anthropic.Anthropic(api_key=ANTHROPIC_API_KEY)\n",
        "    print('✅ Anthropic (Claude) siap')\n",
        "\n",
        "if not clients:\n",
        "    print('❌ Tidak ada API key yang dimasukkan. Minimal 1 API key diperlukan.')\n",
        "else:\n",
        "    print(f'\\n🔗 Total {len(clients)} provider terhubung')\n",
        "    print('   → Lanjut ke Cell 3')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {"id": "cell3"},
      "source": [
        "# ============================================================\n",
        "# CELL 3 — Upload File Excel Data IPLM\n",
        "# ============================================================\n",
        "from google.colab import files as colab_files\n",
        "\n",
        "print('📂 Pilih file Excel data IPLM Anda...')\n",
        "uploaded = colab_files.upload()\n",
        "FILENAME = list(uploaded.keys())[0]\n",
        "print(f'✅ File \"{FILENAME}\" berhasil diupload')\n",
        "print('   → Lanjut ke Cell 4')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {"id": "cell4"},
      "source": [
        "# ============================================================\n",
        "# CELL 4 — Konfigurasi Sheet, Kolom & Daftar Model\n",
        "# Sesuaikan dengan struktur file Excel Anda\n",
        "# ============================================================\n",
        "\n",
        "# ── Sheet & kolom ──────────────────────────────────────────\n",
        "SHEET_PROVINSI  = 'Provinsi'\n",
        "SHEET_KABKOTA   = 'KabKota'       # Ganti jika nama sheet berbeda\n",
        "\n",
        "COL_WILAYAH_P   = 'PROVINSI'\n",
        "COL_WILAYAH_K   = 'KAB_KOTA'\n",
        "COL_SDM         = 'VARIABEL SDM'\n",
        "COL_KOLEKSI     = 'VARIABEL KOLEKSI'\n",
        "COL_PELAYANAN   = 'VARIABEL PELAYANAN'\n",
        "COL_PENGELOLAAN = 'VARIABEL PENGELOLAAN'\n",
        "COL_KEPATUHAN   = 'DIMENSI KEPATUHAN'\n",
        "COL_KINERJA     = 'DIMENSI KINERJA'\n",
        "COL_IPLM        = 'IPLM'\n",
        "\n",
        "# ── Daftar model yang akan diuji ───────────────────────────\n",
        "# Format: (nama_tampilan, provider_key, model_string)\n",
        "# Hapus/komentari model yang tidak ingin diuji\n",
        "MODELS_TO_TEST = [\n",
        "    ('GPT-4o-mini',          'sumopod',   'gpt-4o-mini'),\n",
        "    ('Llama-3.3-70B',        'groq',      'llama-3.3-70b-versatile'),\n",
        "    ('Gemma2-9B',            'groq',      'gemma2-9b-it'),\n",
        "    ('Mixtral-8x7B',         'groq',      'mixtral-8x7b-32768'),\n",
        "    ('Claude-Sonnet',        'anthropic', 'claude-sonnet-4-20250514'),\n",
        "]\n",
        "\n",
        "# Saring hanya model yang providernya tersedia\n",
        "MODELS_TO_TEST = [\n",
        "    m for m in MODELS_TO_TEST if m[1] in clients\n",
        "]\n",
        "\n",
        "# ── Benchmark: jumlah wilayah sample ───────────────────────\n",
        "# Untuk benchmark, cukup uji N wilayah pertama agar hemat kuota\n",
        "N_SAMPLE_PROVINSI = 3   # Jumlah provinsi yang akan dibandingkan\n",
        "N_SAMPLE_KABKOTA  = 3   # Jumlah kab/kota yang akan dibandingkan\n",
        "\n",
        "# ── Retry & rate limit ─────────────────────────────────────\n",
        "MAX_RETRY  = 4\n",
        "JEDA_DETIK = 3\n",
        "\n",
        "print('✅ Konfigurasi selesai')\n",
        "print(f'   Model yang akan diuji ({len(MODELS_TO_TEST)}):')\n",
        "for nm, prov, mdl in MODELS_TO_TEST:\n",
        "    print(f'   • {nm:20s} [{prov}] → {mdl}')\n",
        "\n",
        "# Baca data\n",
        "xls = pd.ExcelFile(FILENAME)\n",
        "print(f'\\n   Sheet tersedia: {xls.sheet_names}')\n",
        "\n",
        "df_prov = pd.read_excel(FILENAME, sheet_name=SHEET_PROVINSI).dropna(subset=[COL_WILAYAH_P])\n",
        "try:\n",
        "    df_kk = pd.read_excel(FILENAME, sheet_name=SHEET_KABKOTA).dropna(subset=[COL_WILAYAH_K])\n",
        "    print(f'   Provinsi : {len(df_prov)} baris | Kab/Kota: {len(df_kk)} baris')\n",
        "except:\n",
        "    df_kk = None\n",
        "    print(f'   Provinsi : {len(df_prov)} baris | Sheet KabKota tidak ditemukan')\n",
        "\n",
        "print('   → Lanjut ke Cell 5')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {"id": "cell5"},
      "source": [
        "# ============================================================\n",
        "# CELL 5 — Fungsi Inti: Prompt, Generate, Parse\n",
        "# ============================================================\n",
        "\n",
        "JSON_KEYS = [\n",
        "    'interpretasi_koleksi', 'rekomendasi_koleksi',\n",
        "    'interpretasi_sdm',     'rekomendasi_sdm',\n",
        "    'interpretasi_pelayanan', 'rekomendasi_pelayanan',\n",
        "    'interpretasi_pengelolaan', 'rekomendasi_pengelolaan',\n",
        "    'interpretasi_kepatuhan', 'rekomendasi_kepatuhan',\n",
        "    'interpretasi_kinerja',  'rekomendasi_kinerja',\n",
        "    'interpretasi_iplm',     'rekomendasi_iplm',\n",
        "]\n",
        "\n",
        "def buat_prompt(nama, jenis_wilayah, sdm, koleksi, pelayanan,\n",
        "                pengelolaan, kepatuhan, kinerja, iplm):\n",
        "    return f\"\"\"Anda adalah analis kebijakan perpustakaan daerah Indonesia yang berpengalaman.\n",
        "Buatkan interpretasi dan rekomendasi kebijakan berdasarkan data indikator perpustakaan berikut.\n",
        "\n",
        "Data {jenis_wilayah}: {nama}\n",
        "- Variabel Koleksi    : {koleksi:.3f}  (jumlah judul & eksemplar koleksi tercetak/digital, laju penambahan koleksi, anggaran koleksi)\n",
        "- Variabel SDM        : {sdm:.3f}  (tenaga ilmu perpustakaan, tenaga non-ilmu, PKB, anggaran diklat)\n",
        "- Variabel Pelayanan  : {pelayanan:.3f}  (peserta budaya baca, pemustaka luring/daring, fasilitas TIK, koleksi termanfaat)\n",
        "- Variabel Pengelolaan: {pengelolaan:.3f}  (kegiatan budaya baca, kerjasama, variasi layanan, kebijakan, anggaran, perda)\n",
        "- Dimensi Kepatuhan   : {kepatuhan:.3f}  (rata-rata Koleksi & SDM, bobot 30% dalam IPLM)\n",
        "- Dimensi Kinerja     : {kinerja:.3f}  (rata-rata Pelayanan & Pengelolaan, bobot 70% dalam IPLM)\n",
        "- IPLM Keseluruhan    : {iplm:.2f}  (skala 0–100)\n",
        "\n",
        "Semua nilai variabel dan dimensi berskala 0–1. IPLM berskala 0–100.\n",
        "Nilai mendekati 1 (atau 100) = sangat baik. Nilai mendekati 0 = sangat lemah.\n",
        "\n",
        "Kembalikan HANYA JSON murni tanpa teks lain, tanpa markdown, tanpa tanda ```:\n",
        "{{\n",
        "  \"interpretasi_koleksi\": \"...\",\n",
        "  \"rekomendasi_koleksi\": \"...\",\n",
        "  \"interpretasi_sdm\": \"...\",\n",
        "  \"rekomendasi_sdm\": \"...\",\n",
        "  \"interpretasi_pelayanan\": \"...\",\n",
        "  \"rekomendasi_pelayanan\": \"...\",\n",
        "  \"interpretasi_pengelolaan\": \"...\",\n",
        "  \"rekomendasi_pengelolaan\": \"...\",\n",
        "  \"interpretasi_kepatuhan\": \"...\",\n",
        "  \"rekomendasi_kepatuhan\": \"...\",\n",
        "  \"interpretasi_kinerja\": \"...\",\n",
        "  \"rekomendasi_kinerja\": \"...\",\n",
        "  \"interpretasi_iplm\": \"...\",\n",
        "  \"rekomendasi_iplm\": \"...\"\n",
        "}}\n",
        "\n",
        "=== PANDUAN GAYA PENULISAN WAJIB ===\n",
        "\n",
        "INTERPRETASI (setiap variabel/dimensi, 4 kalimat):\n",
        "- Kalimat 1: Sebutkan nama variabel + nama wilayah ({nama}) + nilai angka secara eksplisit.\n",
        "- Kalimat 2: Jelaskan komponen/indikator spesifik pembentuk variabel tersebut.\n",
        "- Kalimat 3: Deskripsikan kondisi tanpa label kategori (JANGAN tulis rendah/sedang/tinggi).\n",
        "- Kalimat 4: Kalimat penutup maknawi — awali dengan 'mencerminkan...' atau 'menggambarkan...'\n",
        "\n",
        "REKOMENDASI (setiap variabel/dimensi, 1 paragraf mengalir):\n",
        "- Format: [Kalimat pembuka situasi]. Pertama, [rekomendasi]. Kedua, [rekomendasi]. Ketiga, [rekomendasi].\n",
        "- Tulis dalam SATU paragraf mengalir — BUKAN bullet point.\n",
        "\n",
        "LARANGAN: JANGAN gunakan bullet point, label rendah/sedang/tinggi, atau teks di luar JSON.\n",
        "\"\"\"\n",
        "\n",
        "\n",
        "def parse_json(text):\n",
        "    text = re.sub(r'```(?:json)?', '', text).replace('```', '').strip()\n",
        "    try:\n",
        "        return json.loads(text)\n",
        "    except Exception:\n",
        "        pass\n",
        "    start, end = text.find('{'), text.rfind('}')\n",
        "    if start != -1 and end != -1:\n",
        "        try:\n",
        "            return json.loads(text[start:end+1])\n",
        "        except Exception:\n",
        "            pass\n",
        "    return None\n",
        "\n",
        "\n",
        "def call_model(provider, model_str, prompt, system_msg):\n",
        "    \"\"\"Memanggil API sesuai provider, mengembalikan (teks_respons, waktu_detik)\"\"\"\n",
        "    t0 = time.time()\n",
        "\n",
        "    if provider == 'anthropic':\n",
        "        client = clients['anthropic']\n",
        "        resp = client.messages.create(\n",
        "            model=model_str,\n",
        "            max_tokens=2500,\n",
        "            system=system_msg,\n",
        "            messages=[{'role': 'user', 'content': prompt}]\n",
        "        )\n",
        "        raw = resp.content[0].text\n",
        "\n",
        "    elif provider == 'groq':\n",
        "        client = clients['groq']\n",
        "        resp = client.chat.completions.create(\n",
        "            model=model_str,\n",
        "            messages=[\n",
        "                {'role': 'system', 'content': system_msg},\n",
        "                {'role': 'user',   'content': prompt}\n",
        "            ],\n",
        "            max_tokens=2500,\n",
        "            temperature=0.7\n",
        "        )\n",
        "        raw = resp.choices[0].message.content\n",
        "\n",
        "    elif provider == 'sumopod':\n",
        "        client = clients['sumopod']\n",
        "        resp = client.chat.completions.create(\n",
        "            model=model_str,\n",
        "            messages=[\n",
        "                {'role': 'system', 'content': system_msg},\n",
        "                {'role': 'user',   'content': prompt}\n",
        "            ],\n",
        "            max_tokens=2500,\n",
        "            temperature=0.7\n",
        "        )\n",
        "        raw = resp.choices[0].message.content\n",
        "\n",
        "    else:\n",
        "        raise ValueError(f'Provider tidak dikenal: {provider}')\n",
        "\n",
        "    elapsed = round(time.time() - t0, 2)\n",
        "    return raw, elapsed\n",
        "\n",
        "\n",
        "def generate_all_models(nama, jenis_wilayah, sdm, koleksi, pelayanan,\n",
        "                        pengelolaan, kepatuhan, kinerja, iplm):\n",
        "    \"\"\"Generate output dari semua model untuk satu wilayah.\n",
        "    Mengembalikan dict: model_name -> {hasil_json, waktu, status, raw}\"\"\"\n",
        "\n",
        "    prompt = buat_prompt(nama, jenis_wilayah, sdm, koleksi, pelayanan,\n",
        "                         pengelolaan, kepatuhan, kinerja, iplm)\n",
        "    system_msg = ('Anda analis kebijakan perpustakaan Indonesia. '\n",
        "                  'Selalu balas hanya dengan JSON murni sesuai format yang diminta.')\n",
        "\n",
        "    semua_hasil = {}\n",
        "\n",
        "    for (nama_model, provider, model_str) in MODELS_TO_TEST:\n",
        "        print(f'     [{nama_model}] ... ', end='', flush=True)\n",
        "        sukses = False\n",
        "\n",
        "        for attempt in range(1, MAX_RETRY + 1):\n",
        "            try:\n",
        "                raw, elapsed = call_model(provider, model_str, prompt, system_msg)\n",
        "                hasil = parse_json(raw)\n",
        "\n",
        "                if hasil:\n",
        "                    semua_hasil[nama_model] = {\n",
        "                        'hasil': hasil,\n",
        "                        'waktu': elapsed,\n",
        "                        'status': 'OK',\n",
        "                        'raw': raw\n",
        "                    }\n",
        "                    print(f'✅ ({elapsed}s)')\n",
        "                    sukses = True\n",
        "                    break\n",
        "                else:\n",
        "                    print(f'⚠️ parse gagal ({attempt}/{MAX_RETRY}) ', end='')\n",
        "                    time.sleep(3)\n",
        "\n",
        "            except Exception as e:\n",
        "                err = str(e)\n",
        "                if '429' in err or 'rate' in err.lower():\n",
        "                    tunggu = 60\n",
        "                    print(f'⏳ rate limit, tunggu {tunggu}s... ', end='')\n",
        "                    time.sleep(tunggu)\n",
        "                else:\n",
        "                    print(f'❌ {err[:60]}... ', end='')\n",
        "                    time.sleep(5)\n",
        "\n",
        "        if not sukses:\n",
        "            semua_hasil[nama_model] = {\n",
        "                'hasil': {k: '[GAGAL]' for k in JSON_KEYS},\n",
        "                'waktu': None,\n",
        "                'status': 'GAGAL',\n",
        "                'raw': ''\n",
        "            }\n",
        "            print('❌ GAGAL')\n",
        "\n",
        "        time.sleep(JEDA_DETIK)\n",
        "\n",
        "    return semua_hasil\n",
        "\n",
        "\n",
        "print('✅ Semua fungsi siap')\n",
        "print('   → Lanjut ke Cell 6')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {"id": "cell6"},
      "source": [
        "# ============================================================\n",
        "# CELL 6 — Generate Output Semua Model (Sample)\n",
        "# Memproses N_SAMPLE wilayah dari masing-masing sheet\n",
        "# ============================================================\n",
        "\n",
        "benchmark_results = []   # list of dict, satu entri per (wilayah x model)\n",
        "raw_by_wilayah    = {}   # dict: nama_wilayah -> {model -> hasil}\n",
        "\n",
        "def proses_df(df, col_wilayah, jenis, n_sample):\n",
        "    sample = df.head(n_sample)\n",
        "    for i, row in sample.iterrows():\n",
        "        nama = str(row[col_wilayah]).strip()\n",
        "        print(f'\\n  🌍 [{jenis}] {nama}')\n",
        "\n",
        "        try:\n",
        "            sdm      = float(row[COL_SDM])\n",
        "            koleksi  = float(row[COL_KOLEKSI])\n",
        "            pelay    = float(row[COL_PELAYANAN])\n",
        "            peng     = float(row[COL_PENGELOLAAN])\n",
        "            kepa     = float(row[COL_KEPATUHAN])\n",
        "            kiner    = float(row[COL_KINERJA])\n",
        "            iplm_val = float(row[COL_IPLM])\n",
        "        except Exception as e:\n",
        "            print(f'     ⚠️ Skip — error baca kolom: {e}')\n",
        "            continue\n",
        "\n",
        "        semua = generate_all_models(\n",
        "            nama, jenis, sdm, koleksi, pelay, peng, kepa, kiner, iplm_val\n",
        "        )\n",
        "        raw_by_wilayah[nama] = {\n",
        "            'jenis': jenis,\n",
        "            'data': {'sdm': sdm, 'koleksi': koleksi, 'pelayanan': pelay,\n",
        "                     'pengelolaan': peng, 'kepatuhan': kepa,\n",
        "                     'kinerja': kiner, 'iplm': iplm_val},\n",
        "            'outputs': semua\n",
        "        }\n",
        "\n",
        "        # Flatten ke benchmark_results\n",
        "        for nm_model, info in semua.items():\n",
        "            rec = {\n",
        "                'Wilayah'     : nama,\n",
        "                'Jenis'       : jenis,\n",
        "                'Model'       : nm_model,\n",
        "                'Status'      : info['status'],\n",
        "                'Waktu (s)'   : info['waktu'],\n",
        "                'IPLM'        : iplm_val,\n",
        "            }\n",
        "            for k in JSON_KEYS:\n",
        "                rec[k] = info['hasil'].get(k, '')\n",
        "            benchmark_results.append(rec)\n",
        "\n",
        "\n",
        "print('=' * 55)\n",
        "print('  GENERATE OUTPUT — SEMUA MODEL')\n",
        "print(f'  Provinsi : {N_SAMPLE_PROVINSI} sampel')\n",
        "if df_kk is not None:\n",
        "    print(f'  Kab/Kota : {N_SAMPLE_KABKOTA} sampel')\n",
        "print(f'  Model    : {len(MODELS_TO_TEST)} model')\n",
        "print('=' * 55)\n",
        "\n",
        "proses_df(df_prov, COL_WILAYAH_P, 'Provinsi', N_SAMPLE_PROVINSI)\n",
        "\n",
        "if df_kk is not None:\n",
        "    proses_df(df_kk, COL_WILAYAH_K, 'Kabupaten/Kota', N_SAMPLE_KABKOTA)\n",
        "\n",
        "df_bench = pd.DataFrame(benchmark_results)\n",
        "print(f'\\n✅ Selesai! Total record: {len(df_bench)}')\n",
        "print(f'   Wilayah diproses : {df_bench[\"Wilayah\"].nunique()}')\n",
        "print(f'   Model diuji      : {df_bench[\"Model\"].nunique()}')\n",
        "print('   → Lanjut ke Cell 7 (Evaluasi Otomatis)')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {"id": "cell7"},
      "source": [
        "# ============================================================\n",
        "# CELL 7 — Evaluasi Otomatis (Rubrik Kriteria)\n",
        "# Menilai output berdasarkan kriteria terukur tanpa AI\n",
        "# ============================================================\n",
        "\n",
        "def hitung_skor_rubrik(hasil_dict, iplm_val):\n",
        "    \"\"\"\n",
        "    Menghitung skor rubrik 0-100 berdasarkan kriteria terukur.\n",
        "    Mengembalikan dict skor per dimensi + skor total.\n",
        "    \"\"\"\n",
        "    skor = {}\n",
        "\n",
        "    # ── 1. KELENGKAPAN (20 poin) ───────────────────────────\n",
        "    # Apakah semua 14 key JSON terisi dan tidak kosong/gagal?\n",
        "    terisi = sum(1 for k in JSON_KEYS\n",
        "                 if hasil_dict.get(k, '') not in ('', '[GAGAL]', None))\n",
        "    skor['kelengkapan'] = round((terisi / len(JSON_KEYS)) * 20, 2)\n",
        "\n",
        "    # ── 2. PANJANG TEKS INTERPRETASI (20 poin) ─────────────\n",
        "    # Target: 4 kalimat ≈ 80–200 kata per interpretasi\n",
        "    interp_keys = [k for k in JSON_KEYS if k.startswith('interpretasi_')]\n",
        "    panjang_scores = []\n",
        "    for k in interp_keys:\n",
        "        teks = hasil_dict.get(k, '')\n",
        "        if not teks or teks == '[GAGAL]':\n",
        "            panjang_scores.append(0)\n",
        "            continue\n",
        "        n_kata  = len(teks.split())\n",
        "        n_kali  = len([s for s in teks.split('.') if s.strip()])\n",
        "        # Ideal: 3-5 kalimat, 60-200 kata\n",
        "        skor_kata  = min(n_kata / 80, 1.0) if n_kata < 80 else max(1.0 - (n_kata - 200) / 200, 0.5)\n",
        "        skor_kali  = 1.0 if 3 <= n_kali <= 5 else max(0, 1 - abs(n_kali - 4) * 0.2)\n",
        "        panjang_scores.append((skor_kata + skor_kali) / 2)\n",
        "    skor['panjang_interpretasi'] = round(sum(panjang_scores) / len(panjang_scores) * 20, 2)\n",
        "\n",
        "    # ── 3. FORMAT REKOMENDASI (20 poin) ────────────────────\n",
        "    # Cek apakah mengikuti format \"Pertama/Kedua/Ketiga\"\n",
        "    rekomendasi_keys = [k for k in JSON_KEYS if k.startswith('rekomendasi_')]\n",
        "    format_scores = []\n",
        "    for k in rekomendasi_keys:\n",
        "        teks = hasil_dict.get(k, '').lower()\n",
        "        if not teks or teks == '[gagal]':\n",
        "            format_scores.append(0)\n",
        "            continue\n",
        "        ada_pertama = 'pertama' in teks\n",
        "        ada_kedua   = 'kedua'   in teks\n",
        "        ada_ketiga  = 'ketiga'  in teks\n",
        "        no_bullet   = '•' not in teks and '- ' not in teks[:30]\n",
        "        s = sum([ada_pertama, ada_kedua, ada_ketiga, no_bullet]) / 4\n",
        "        format_scores.append(s)\n",
        "    skor['format_rekomendasi'] = round(sum(format_scores) / len(format_scores) * 20, 2)\n",
        "\n",
        "    # ── 4. KEPATUHAN LARANGAN (20 poin) ───────────────────\n",
        "    # Apakah tidak menggunakan kata yang dilarang?\n",
        "    KATA_LARANGAN = ['rendah', 'sedang', 'tinggi', 'cukup baik', 'kurang baik',\n",
        "                     'sangat baik', 'sangat rendah', 'sangat tinggi']\n",
        "    semua_teks = ' '.join(hasil_dict.get(k, '') for k in JSON_KEYS).lower()\n",
        "    pelanggaran = sum(1 for w in KATA_LARANGAN if w in semua_teks)\n",
        "    skor['kepatuhan_larangan'] = round(max(0, 20 - pelanggaran * 3), 2)\n",
        "\n",
        "    # ── 5. RELEVANSI NILAI ANGKA (20 poin) ─────────────────\n",
        "    # Apakah teks menyebut nilai angka dari data?\n",
        "    dimensi_map = {\n",
        "        'koleksi': 'koleksi', 'sdm': 'sdm',\n",
        "        'pelayanan': 'pelayanan', 'pengelolaan': 'pengelolaan',\n",
        "        'kepatuhan': 'kepatuhan', 'kinerja': 'kinerja', 'iplm': 'iplm'\n",
        "    }\n",
        "    relevansi_scores = []\n",
        "    for k in interp_keys:\n",
        "        teks = hasil_dict.get(k, '')\n",
        "        if not teks or teks == '[GAGAL]':\n",
        "            relevansi_scores.append(0)\n",
        "            continue\n",
        "        # Cek ada angka desimal di teks (nilai variabel)\n",
        "        ada_angka = bool(re.search(r'\\d+[,.]\\d+', teks))\n",
        "        # Cek nama wilayah disebut\n",
        "        ada_nama  = True  # sulit diverifikasi tanpa nama, beri kredit penuh\n",
        "        relevansi_scores.append(1.0 if ada_angka else 0.5)\n",
        "    skor['relevansi_data'] = round(sum(relevansi_scores) / len(relevansi_scores) * 20, 2)\n",
        "\n",
        "    # ── Total ──────────────────────────────────────────────\n",
        "    skor['total'] = round(sum([\n",
        "        skor['kelengkapan'],\n",
        "        skor['panjang_interpretasi'],\n",
        "        skor['format_rekomendasi'],\n",
        "        skor['kepatuhan_larangan'],\n",
        "        skor['relevansi_data']\n",
        "    ]), 2)\n",
        "\n",
        "    return skor\n",
        "\n",
        "\n",
        "# ── Hitung skor untuk semua record ────────────────────────\n",
        "print('⚙️  Menghitung skor rubrik otomatis...')\n",
        "\n",
        "skor_cols = ['kelengkapan', 'panjang_interpretasi', 'format_rekomendasi',\n",
        "             'kepatuhan_larangan', 'relevansi_data', 'total']\n",
        "\n",
        "for rec in benchmark_results:\n",
        "    hasil_d = {k: rec.get(k, '') for k in JSON_KEYS}\n",
        "    skor    = hitung_skor_rubrik(hasil_d, rec.get('IPLM', 50))\n",
        "    for sc in skor_cols:\n",
        "        rec[f'Skor_{sc.title()}'] = skor[sc]\n",
        "\n",
        "df_bench = pd.DataFrame(benchmark_results)\n",
        "\n",
        "# ── Ringkasan per model ────────────────────────────────────\n",
        "print('\\n📊 RINGKASAN SKOR RUBRIK PER MODEL (rata-rata):')\n",
        "print('-' * 75)\n",
        "\n",
        "ringkasan = df_bench.groupby('Model')[\n",
        "    ['Skor_Kelengkapan', 'Skor_Panjang_Interpretasi', 'Skor_Format_Rekomendasi',\n",
        "     'Skor_Kepatuhan_Larangan', 'Skor_Relevansi_Data', 'Skor_Total',\n",
        "     'Waktu (s)']\n",
        "].mean().round(2)\n",
        "\n",
        "ringkasan = ringkasan.sort_values('Skor_Total', ascending=False)\n",
        "print(ringkasan.to_string())\n",
        "\n",
        "print('\\n🏅 Ranking Model (berdasarkan Skor Total):')\n",
        "for rank, (model, row) in enumerate(ringkasan.iterrows(), 1):\n",
        "    medal = ['🥇', '🥈', '🥉', '4️⃣', '5️⃣'][rank - 1] if rank <= 5 else f'{rank}.'\n",
        "    print(f'   {medal} {model:22s} → Total: {row[\"Skor_Total\"]:.1f}/100  | Waktu: {row[\"Waktu (s)\"]:.1f}s')\n",
        "\n",
        "print('\\n   → Lanjut ke Cell 8 (LLM-as-Judge)')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {"id": "cell8"},
      "source": [
        "# ============================================================\n",
        "# CELL 8 — LLM-as-Judge\n",
        "# Gunakan Claude atau Llama sebagai juri untuk menilai\n",
        "# output semua model secara komparatif\n",
        "# ============================================================\n",
        "\n",
        "# Pilih model juri (gunakan yang paling kuat yang tersedia)\n",
        "if 'anthropic' in clients:\n",
        "    JUDGE_PROVIDER = 'anthropic'\n",
        "    JUDGE_MODEL    = 'claude-sonnet-4-20250514'\n",
        "    JUDGE_NAME     = 'Claude Sonnet'\n",
        "elif 'groq' in clients:\n",
        "    JUDGE_PROVIDER = 'groq'\n",
        "    JUDGE_MODEL    = 'llama-3.3-70b-versatile'\n",
        "    JUDGE_NAME     = 'Llama 3.3 70B'\n",
        "else:\n",
        "    JUDGE_PROVIDER = 'sumopod'\n",
        "    JUDGE_MODEL    = 'gpt-4o-mini'\n",
        "    JUDGE_NAME     = 'GPT-4o-mini'\n",
        "\n",
        "print(f'⚖️  Model Juri: {JUDGE_NAME}\\n')\n",
        "\n",
        "\n",
        "def buat_prompt_judge(nama_wilayah, data_iplm, outputs_per_model):\n",
        "    \"\"\"Buat prompt evaluasi untuk LLM-as-Judge.\"\"\"\n",
        "    outputs_text = ''\n",
        "    for nm_model, info in outputs_per_model.items():\n",
        "        if info['status'] != 'OK':\n",
        "            continue\n",
        "        interp_iplm = info['hasil'].get('interpretasi_iplm', '-')\n",
        "        rekom_iplm  = info['hasil'].get('rekomendasi_iplm', '-')\n",
        "        outputs_text += f\"\"\"\n",
        "--- MODEL: {nm_model} ---\n",
        "Interpretasi IPLM: {interp_iplm}\n",
        "Rekomendasi IPLM: {rekom_iplm}\n",
        "\"\"\"\n",
        "\n",
        "    return f\"\"\"Anda adalah juri ahli evaluasi teks kebijakan perpustakaan Indonesia.\n",
        "Tugas Anda: menilai output dari beberapa model AI untuk wilayah yang sama.\n",
        "\n",
        "Data Wilayah: {nama_wilayah}\n",
        "IPLM: {data_iplm['iplm']:.2f} | SDM: {data_iplm['sdm']:.3f} | Koleksi: {data_iplm['koleksi']:.3f}\n",
        "Pelayanan: {data_iplm['pelayanan']:.3f} | Pengelolaan: {data_iplm['pengelolaan']:.3f}\n",
        "\n",
        "=== OUTPUT YANG DINILAI (hanya dimensi IPLM ditampilkan sebagai sampel) ===\n",
        "{outputs_text}\n",
        "\n",
        "Nilai setiap model pada 5 kriteria (skala 1-10):\n",
        "1. Akurasi Kontekstual - apakah interpretasi sesuai dengan nilai angka IPLM?\n",
        "2. Kedalaman Analisis - apakah analisis mendalam, tidak superfisial?\n",
        "3. Kualitas Rekomendasi - apakah rekomendasi konkret, spesifik, dan actionable?\n",
        "4. Gaya Bahasa - apakah bahasa profesional, mengalir, dan tidak kaku?\n",
        "5. Kepatuhan Format - apakah mengikuti format Pertama/Kedua/Ketiga tanpa bullet?\n",
        "\n",
        "Kembalikan HANYA JSON murni:\n",
        "{{\n",
        "  \"penilaian\": {{\n",
        "    \"[NAMA_MODEL_1]\": {{\n",
        "      \"akurasi_kontekstual\": 0,\n",
        "      \"kedalaman_analisis\": 0,\n",
        "      \"kualitas_rekomendasi\": 0,\n",
        "      \"gaya_bahasa\": 0,\n",
        "      \"kepatuhan_format\": 0,\n",
        "      \"total\": 0,\n",
        "      \"komentar\": \"...\"\n",
        "    }}\n",
        "  }},\n",
        "  \"model_terbaik\": \"[nama model terbaik]\",\n",
        "  \"alasan_terbaik\": \"...\",\n",
        "  \"model_terlemah\": \"[nama model terlemah]\",\n",
        "  \"alasan_terlemah\": \"...\"\n",
        "}}\n",
        "Ganti [NAMA_MODEL_1] dengan nama model yang sebenarnya untuk setiap model yang dinilai.\n",
        "\"\"\"\n",
        "\n",
        "\n",
        "judge_results = []   # list of dict hasil penilaian juri per wilayah\n",
        "\n",
        "print('=' * 55)\n",
        "print('  LLM-as-JUDGE — Penilaian Komparatif')\n",
        "print('=' * 55)\n",
        "\n",
        "for nama_wilayah, isi in raw_by_wilayah.items():\n",
        "    print(f'\\n  🌍 {nama_wilayah} ... ', end='', flush=True)\n",
        "\n",
        "    prompt_judge = buat_prompt_judge(nama_wilayah, isi['data'], isi['outputs'])\n",
        "    system_judge = 'Anda juri evaluasi netral. Balas hanya dengan JSON murni.'\n",
        "\n",
        "    for attempt in range(1, MAX_RETRY + 1):\n",
        "        try:\n",
        "            raw_judge, elapsed = call_model(JUDGE_PROVIDER, JUDGE_MODEL,\n",
        "                                            prompt_judge, system_judge)\n",
        "            hasil_judge = parse_json(raw_judge)\n",
        "            if hasil_judge and 'penilaian' in hasil_judge:\n",
        "                print(f'✅ ({elapsed}s)')\n",
        "                # Terbaik & terlemah\n",
        "                print(f'     🏅 Terbaik : {hasil_judge.get(\"model_terbaik\",\"?\")}')\n",
        "                print(f'     ⚠️  Terlemah: {hasil_judge.get(\"model_terlemah\",\"?\")}')\n",
        "\n",
        "                for nm_model, nilai in hasil_judge['penilaian'].items():\n",
        "                    judge_results.append({\n",
        "                        'Wilayah'             : nama_wilayah,\n",
        "                        'Model'               : nm_model,\n",
        "                        'Judge'               : JUDGE_NAME,\n",
        "                        'J_Akurasi'           : nilai.get('akurasi_kontekstual', 0),\n",
        "                        'J_Kedalaman'         : nilai.get('kedalaman_analisis', 0),\n",
        "                        'J_Rekomendasi'       : nilai.get('kualitas_rekomendasi', 0),\n",
        "                        'J_Gaya_Bahasa'       : nilai.get('gaya_bahasa', 0),\n",
        "                        'J_Kepatuhan_Format'  : nilai.get('kepatuhan_format', 0),\n",
        "                        'J_Total'             : nilai.get('total', 0),\n",
        "                        'J_Komentar'          : nilai.get('komentar', ''),\n",
        "                        'J_Model_Terbaik'     : hasil_judge.get('model_terbaik', ''),\n",
        "                        'J_Alasan_Terbaik'    : hasil_judge.get('alasan_terbaik', ''),\n",
        "                    })\n",
        "                break\n",
        "            else:\n",
        "                print(f'⚠️ parse gagal ({attempt}) ', end='')\n",
        "                time.sleep(5)\n",
        "\n",
        "        except Exception as e:\n",
        "            err = str(e)\n",
        "            if '429' in err or 'rate' in err.lower():\n",
        "                print(f'⏳ rate limit... ', end='')\n",
        "                time.sleep(60)\n",
        "            else:\n",
        "                print(f'❌ {err[:50]} ', end='')\n",
        "                time.sleep(5)\n",
        "\n",
        "    time.sleep(JEDA_DETIK)\n",
        "\n",
        "df_judge = pd.DataFrame(judge_results)\n",
        "\n",
        "if not df_judge.empty:\n",
        "    print('\\n\\n📊 RINGKASAN SKOR JURI PER MODEL (rata-rata):')\n",
        "    print('-' * 65)\n",
        "    ring_judge = df_judge.groupby('Model')[[\n",
        "        'J_Akurasi', 'J_Kedalaman', 'J_Rekomendasi',\n",
        "        'J_Gaya_Bahasa', 'J_Kepatuhan_Format', 'J_Total'\n",
        "    ]].mean().round(2).sort_values('J_Total', ascending=False)\n",
        "    print(ring_judge.to_string())\n",
        "\n",
        "    print('\\n🏅 Ranking Model (Skor Juri):')\n",
        "    for rank, (model, row) in enumerate(ring_judge.iterrows(), 1):\n",
        "        medal = ['🥇', '🥈', '🥉', '4️⃣', '5️⃣'][rank - 1] if rank <= 5 else f'{rank}.'\n",
        "        print(f'   {medal} {model:22s} → Skor Juri: {row[\"J_Total\"]:.1f}/50')\n",
        "\n",
        "print('\\n   → Lanjut ke Cell 9 (Side-by-Side)')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {"id": "cell9"},
      "source": [
        "# ============================================================\n",
        "# CELL 9 — Dashboard Side-by-Side (Perbandingan Manual)\n",
        "# Widget interaktif untuk membandingkan output antar model\n",
        "# ============================================================\n",
        "\n",
        "DIMENSI_OPTIONS = [\n",
        "    ('Interpretasi Koleksi',      'interpretasi_koleksi'),\n",
        "    ('Rekomendasi Koleksi',       'rekomendasi_koleksi'),\n",
        "    ('Interpretasi SDM',          'interpretasi_sdm'),\n",
        "    ('Rekomendasi SDM',           'rekomendasi_sdm'),\n",
        "    ('Interpretasi Pelayanan',    'interpretasi_pelayanan'),\n",
        "    ('Rekomendasi Pelayanan',     'rekomendasi_pelayanan'),\n",
        "    ('Interpretasi Pengelolaan',  'interpretasi_pengelolaan'),\n",
        "    ('Rekomendasi Pengelolaan',   'rekomendasi_pengelolaan'),\n",
        "    ('Interpretasi Kepatuhan',    'interpretasi_kepatuhan'),\n",
        "    ('Rekomendasi Kepatuhan',     'rekomendasi_kepatuhan'),\n",
        "    ('Interpretasi Kinerja',      'interpretasi_kinerja'),\n",
        "    ('Rekomendasi Kinerja',       'rekomendasi_kinerja'),\n",
        "    ('Interpretasi IPLM',         'interpretasi_iplm'),\n",
        "    ('Rekomendasi IPLM',          'rekomendasi_iplm'),\n",
        "]\n",
        "\n",
        "wilayah_list = list(raw_by_wilayah.keys())\n",
        "\n",
        "dd_wilayah = widgets.Dropdown(\n",
        "    options=wilayah_list,\n",
        "    description='Wilayah  :',\n",
        "    layout=widgets.Layout(width='420px'),\n",
        "    style={'description_width': '120px'}\n",
        ")\n",
        "dd_dimensi = widgets.Dropdown(\n",
        "    options=[d[0] for d in DIMENSI_OPTIONS],\n",
        "    description='Dimensi  :',\n",
        "    layout=widgets.Layout(width='420px'),\n",
        "    style={'description_width': '120px'}\n",
        ")\n",
        "out_compare = widgets.Output()\n",
        "\n",
        "MODEL_COLORS = [\n",
        "    '#E3F2FD', '#E8F5E9', '#FFF3E0', '#F3E5F5', '#FCE4EC'\n",
        "]\n",
        "\n",
        "def tampilkan_comparison(_):\n",
        "    with out_compare:\n",
        "        clear_output(wait=True)\n",
        "        nama     = dd_wilayah.value\n",
        "        dim_label= dd_dimensi.value\n",
        "        dim_key  = dict(DIMENSI_OPTIONS)[dim_label]\n",
        "\n",
        "        isi    = raw_by_wilayah.get(nama, {})\n",
        "        data   = isi.get('data', {})\n",
        "        outputs= isi.get('outputs', {})\n",
        "\n",
        "        # Header info wilayah\n",
        "        html  = f\"\"\"\n",
        "        <div style='background:#1F497D;color:white;padding:10px 14px;\n",
        "                    border-radius:6px;margin-bottom:10px;font-family:sans-serif'>\n",
        "          <b>🌍 {nama}</b> &nbsp;|&nbsp;\n",
        "          IPLM: <b>{data.get('iplm',0):.2f}</b> &nbsp;|&nbsp;\n",
        "          SDM: {data.get('sdm',0):.3f} &nbsp;|\n",
        "          Koleksi: {data.get('koleksi',0):.3f} &nbsp;|\n",
        "          Pelayanan: {data.get('pelayanan',0):.3f} &nbsp;|\n",
        "          Pengelolaan: {data.get('pengelolaan',0):.3f}\n",
        "        </div>\n",
        "        <div style='font-family:sans-serif;font-size:13px;margin-bottom:8px'>\n",
        "          <b>📋 Dimensi yang dibandingkan: {dim_label}</b>\n",
        "        </div>\n",
        "        <div style='display:flex;gap:8px;flex-wrap:wrap'>\n",
        "        \"\"\"\n",
        "\n",
        "        for idx, (nm_model, info) in enumerate(outputs.items()):\n",
        "            teks  = info['hasil'].get(dim_key, '[GAGAL]') if info['status'] == 'OK' else '[GAGAL]'\n",
        "            waktu = f\"{info['waktu']}s\" if info['waktu'] else 'N/A'\n",
        "            bg    = MODEL_COLORS[idx % len(MODEL_COLORS)]\n",
        "\n",
        "            # Hitung skor rubrik untuk model ini\n",
        "            skor_row = df_bench[\n",
        "                (df_bench['Wilayah'] == nama) & (df_bench['Model'] == nm_model)\n",
        "            ]\n",
        "            skor_total = skor_row['Skor_Total'].values[0] if not skor_row.empty else '-'\n",
        "\n",
        "            # Skor juri\n",
        "            judge_row = df_judge[\n",
        "                (df_judge['Wilayah'] == nama) & (df_judge['Model'] == nm_model)\n",
        "            ] if not df_judge.empty else pd.DataFrame()\n",
        "            skor_juri = judge_row['J_Total'].values[0] if not judge_row.empty else '-'\n",
        "\n",
        "            teks_wrapped = teks.replace('<', '&lt;').replace('>', '&gt;')\n",
        "\n",
        "            html += f\"\"\"\n",
        "            <div style='flex:1;min-width:240px;max-width:360px;\n",
        "                        background:{bg};border-radius:6px;\n",
        "                        padding:10px;font-family:sans-serif;font-size:12px'>\n",
        "              <div style='font-weight:bold;font-size:13px;margin-bottom:4px'>\n",
        "                🤖 {nm_model}\n",
        "              </div>\n",
        "              <div style='color:#555;margin-bottom:6px'>\n",
        "                ⏱ {waktu} &nbsp;|\n",
        "                📊 Rubrik: <b>{skor_total}/100</b> &nbsp;|\n",
        "                ⚖️ Juri: <b>{skor_juri}/50</b>\n",
        "              </div>\n",
        "              <div style='background:white;padding:8px;border-radius:4px;\n",
        "                          border:1px solid #ddd;line-height:1.5;color:#222'>\n",
        "                {teks_wrapped}\n",
        "              </div>\n",
        "            </div>\n",
        "            \"\"\"\n",
        "\n",
        "        html += '</div>'\n",
        "        display(HTML(html))\n",
        "\n",
        "\n",
        "btn_compare = widgets.Button(\n",
        "    description='▶ Tampilkan Perbandingan',\n",
        "    button_style='success',\n",
        "    layout=widgets.Layout(width='220px', height='36px')\n",
        ")\n",
        "btn_compare.on_click(tampilkan_comparison)\n",
        "\n",
        "display(widgets.VBox([\n",
        "    widgets.HTML(\"<h3 style='font-family:sans-serif'>🔍 Side-by-Side Model Comparison</h3>\"),\n",
        "    dd_wilayah,\n",
        "    dd_dimensi,\n",
        "    btn_compare,\n",
        "    out_compare\n",
        "]))\n",
        "\n",
        "# Tampilkan auto untuk wilayah & dimensi pertama\n",
        "tampilkan_comparison(None)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {"id": "cell10"},
      "source": [
        "# ============================================================\n",
        "# CELL 10 — Export Hasil Benchmark ke Excel\n",
        "# Menghasilkan 4 sheet: Ringkasan, Rubrik, Juri, Full Output\n",
        "# ============================================================\n",
        "from google.colab import files as colab_files\n",
        "\n",
        "OUTPUT_FILE = 'IPLM_Benchmark_Results.xlsx'\n",
        "\n",
        "with pd.ExcelWriter(OUTPUT_FILE, engine='xlsxwriter') as writer:\n",
        "    wb = writer.book\n",
        "\n",
        "    # Format\n",
        "    hdr_fmt  = wb.add_format({'bold': True, 'bg_color': '#1F497D',\n",
        "                               'font_color': 'white', 'text_wrap': True, 'valign': 'vcenter'})\n",
        "    wrap_fmt = wb.add_format({'text_wrap': True, 'valign': 'top', 'font_size': 10})\n",
        "    num_fmt  = wb.add_format({'num_format': '0.00', 'align': 'center'})\n",
        "    gold_fmt = wb.add_format({'bg_color': '#FFD700', 'bold': True, 'align': 'center'})\n",
        "    green_fmt= wb.add_format({'bg_color': '#C8E6C9', 'bold': True})\n",
        "\n",
        "    # ── Sheet 1: Ringkasan ─────────────────────────────────\n",
        "    # Gabung skor rubrik + skor juri\n",
        "    rubrik_avg = df_bench.groupby('Model')[[\n",
        "        'Skor_Kelengkapan', 'Skor_Panjang_Interpretasi',\n",
        "        'Skor_Format_Rekomendasi', 'Skor_Kepatuhan_Larangan',\n",
        "        'Skor_Relevansi_Data', 'Skor_Total', 'Waktu (s)'\n",
        "    ]].mean().round(2)\n",
        "\n",
        "    if not df_judge.empty:\n",
        "        judge_avg = df_judge.groupby('Model')[[\n",
        "            'J_Akurasi', 'J_Kedalaman', 'J_Rekomendasi',\n",
        "            'J_Gaya_Bahasa', 'J_Kepatuhan_Format', 'J_Total'\n",
        "        ]].mean().round(2)\n",
        "        df_summary = rubrik_avg.join(judge_avg, how='outer')\n",
        "    else:\n",
        "        df_summary = rubrik_avg.copy()\n",
        "\n",
        "    df_summary = df_summary.sort_values('Skor_Total', ascending=False)\n",
        "    df_summary.reset_index(inplace=True)\n",
        "    df_summary.insert(0, 'Ranking', range(1, len(df_summary) + 1))\n",
        "\n",
        "    df_summary.to_excel(writer, sheet_name='Ringkasan', index=False)\n",
        "    ws = writer.sheets['Ringkasan']\n",
        "    for col_num, val in enumerate(df_summary.columns):\n",
        "        ws.write(0, col_num, val, hdr_fmt)\n",
        "    ws.set_column('A:A', 8)\n",
        "    ws.set_column('B:B', 22)\n",
        "    ws.set_column('C:P', 14, num_fmt)\n",
        "    # Highlight baris terbaik\n",
        "    for col in range(len(df_summary.columns)):\n",
        "        ws.write(1, col, df_summary.iloc[0, col], green_fmt)\n",
        "\n",
        "    # ── Sheet 2: Skor Rubrik Detail ───────────────────────\n",
        "    cols_rubrik = ['Wilayah', 'Jenis', 'Model', 'IPLM', 'Waktu (s)',\n",
        "                   'Skor_Kelengkapan', 'Skor_Panjang_Interpretasi',\n",
        "                   'Skor_Format_Rekomendasi', 'Skor_Kepatuhan_Larangan',\n",
        "                   'Skor_Relevansi_Data', 'Skor_Total', 'Status']\n",
        "    df_bench[cols_rubrik].to_excel(writer, sheet_name='Skor_Rubrik', index=False)\n",
        "    ws2 = writer.sheets['Skor_Rubrik']\n",
        "    for col_num, val in enumerate(cols_rubrik):\n",
        "        ws2.write(0, col_num, val, hdr_fmt)\n",
        "    ws2.set_column('A:D', 18)\n",
        "    ws2.set_column('E:L', 14, num_fmt)\n",
        "\n",
        "    # ── Sheet 3: Skor Juri ────────────────────────────────\n",
        "    if not df_judge.empty:\n",
        "        df_judge.to_excel(writer, sheet_name='Skor_Juri', index=False)\n",
        "        ws3 = writer.sheets['Skor_Juri']\n",
        "        for col_num, val in enumerate(df_judge.columns):\n",
        "            ws3.write(0, col_num, val, hdr_fmt)\n",
        "        ws3.set_column('A:C', 22)\n",
        "        ws3.set_column('D:J', 14, num_fmt)\n",
        "        ws3.set_column('K:M', 55, wrap_fmt)\n",
        "        ws3.set_default_row(60)\n",
        "\n",
        "    # ── Sheet 4: Full Output ──────────────────────────────\n",
        "    output_cols = ['Wilayah', 'Jenis', 'Model', 'IPLM', 'Status'] + JSON_KEYS\n",
        "    df_bench[output_cols].to_excel(writer, sheet_name='Full_Output', index=False)\n",
        "    ws4 = writer.sheets['Full_Output']\n",
        "    for col_num, val in enumerate(output_cols):\n",
        "        ws4.write(0, col_num, val, hdr_fmt)\n",
        "    ws4.set_column('A:E', 18)\n",
        "    ws4.set_column('F:S', 65, wrap_fmt)\n",
        "    ws4.set_default_row(120)\n",
        "\n",
        "print(f'✅ File benchmark berhasil disimpan: {OUTPUT_FILE}')\n",
        "print(f'   Sheet: Ringkasan | Skor_Rubrik | Skor_Juri | Full_Output')\n",
        "print('\\n📥 Mengunduh file...')\n",
        "colab_files.download(OUTPUT_FILE)\n",
        "print('✅ Selesai! Cek folder unduhan Anda.')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {"id": "penutup"},
      "source": [
        "---\n",
        "\n",
        "## 📌 Panduan Membaca Hasil Benchmark\n",
        "\n",
        "### Sheet **Ringkasan**\n",
        "Peringkat akhir setiap model berdasarkan rata-rata dari semua wilayah sampel. Baris hijau = model terbaik.\n",
        "\n",
        "### Skor Rubrik (0–100)\n",
        "| Kriteria | Bobot | Keterangan |\n",
        "|---|---|---|\n",
        "| Kelengkapan | 20 | Semua 14 key JSON terisi |\n",
        "| Panjang Interpretasi | 20 | 3–5 kalimat, 60–200 kata |\n",
        "| Format Rekomendasi | 20 | Ada Pertama/Kedua/Ketiga, tanpa bullet |\n",
        "| Kepatuhan Larangan | 20 | Tidak memakai kata rendah/sedang/tinggi |\n",
        "| Relevansi Data | 20 | Menyebut nilai angka dari data |\n",
        "\n",
        "### Skor Juri (0–50)\n",
        "| Kriteria | Maks | Keterangan |\n",
        "|---|---|---|\n",
        "| Akurasi Kontekstual | 10 | Sesuai nilai IPLM |\n",
        "| Kedalaman Analisis | 10 | Tidak superfisial |\n",
        "| Kualitas Rekomendasi | 10 | Konkret & actionable |\n",
        "| Gaya Bahasa | 10 | Profesional & mengalir |\n",
        "| Kepatuhan Format | 10 | Ikuti panduan gaya |\n",
        "\n",
        "### Tips\n",
        "- Jika ingin uji lebih banyak wilayah, ubah `N_SAMPLE_PROVINSI` dan `N_SAMPLE_KABKOTA` di Cell 4\n",
        "- Jalankan ulang Cell 6–10 setelah mengubah konfigurasi\n",
        "- Model terbaik bisa berbeda untuk Provinsi vs Kab/Kota — perhatikan kolom `Jenis`"
      ]
    }
  ]
}