1172 lines
49 KiB
Python
1172 lines
49 KiB
Python
"""
|
||
ML Integration Service
|
||
Mengintegrasikan Model ML dengan Backend FastAPI & Database MySQL
|
||
"""
|
||
|
||
import sys
|
||
import os
|
||
import re
|
||
from pathlib import Path
|
||
import pandas as pd
|
||
import numpy as np
|
||
from typing import Dict, List, Optional, Any
|
||
from sqlalchemy.orm import Session
|
||
from sqlalchemy import or_
|
||
|
||
# Add Model and Project Root to sys.path
|
||
CURRENT_FILE = Path(__file__).resolve()
|
||
PROJECT_ROOT = CURRENT_FILE.parent.parent.parent.parent
|
||
MODEL_PATH = PROJECT_ROOT / "Model"
|
||
|
||
for p in [str(MODEL_PATH), str(PROJECT_ROOT)]:
|
||
if p not in sys.path:
|
||
sys.path.insert(0, p)
|
||
|
||
from app.models.alumni import Alumni
|
||
from app.models.models import Pekerjaan, ClusteringResult, ModelEvaluation, KeywordDictionary
|
||
|
||
try:
|
||
from Model.preprocessing.text_preprocessing import preprocess_text
|
||
from Model.preprocessing.interpretasi import (
|
||
interpretasi_categori,
|
||
PRODI_CODES,
|
||
KEYWORD_DICTIONARY,
|
||
NON_PROFILE_KEYWORDS,
|
||
)
|
||
from Model.feature_engineering.tfidf import TfidfExtractor
|
||
from Model.clustering.kmeans import KMeansClustering
|
||
from Model.main import AlumniClusteringPipeline
|
||
from sklearn.metrics import silhouette_score
|
||
MODEL_AVAILABLE = True
|
||
except Exception as e:
|
||
print(f"[WARNING] ML Service Import: {e}. Using fallback functions.")
|
||
MODEL_AVAILABLE = False
|
||
|
||
# Fallback Preprocess Text if needed
|
||
def preprocess_text(text: str) -> str:
|
||
if not text:
|
||
return ""
|
||
text = text.lower()
|
||
text = re.sub(r'[^a-zA-Z0-9\s]', ' ', text)
|
||
return re.sub(r'\s+', ' ', text).strip()
|
||
|
||
TfidfExtractor: Any = None
|
||
KMeansClustering: Any = None
|
||
AlumniClusteringPipeline: Any = None
|
||
silhouette_score: Any = None
|
||
interpretasi_categori: Any = None
|
||
PRODI_CODES: Any = {}
|
||
KEYWORD_DICTIONARY: Any = {}
|
||
NON_PROFILE_KEYWORDS: Any = set()
|
||
|
||
|
||
|
||
class MLService:
|
||
"""
|
||
Service untuk menjalankan proses ML dari Backend
|
||
"""
|
||
|
||
def __init__(self):
|
||
self.pipeline = None
|
||
self.is_ready = MODEL_AVAILABLE
|
||
self.latest_iteration_details = None
|
||
self.iteration_cache: Dict[Any, Dict] = {}
|
||
|
||
def seed_keyword_dictionary(self, db: Session, force_reset: bool = False) -> Dict[str, Any]:
|
||
"""
|
||
Sinkronisasi / Seeding master kamus profil prodi ke tabel MySQL `keyword_dictionary`.
|
||
"""
|
||
from app.models.models import KeywordDictionary
|
||
from Model.config.default_keywords import DEFAULT_KEYWORD_DICTIONARY
|
||
|
||
existing_count = db.query(KeywordDictionary).count()
|
||
if existing_count > 0 and not force_reset:
|
||
return {
|
||
"status": "success",
|
||
"message": f"Tabel keyword_dictionary sudah terisi ({existing_count} kata kunci).",
|
||
"count": existing_count
|
||
}
|
||
|
||
if force_reset:
|
||
db.query(KeywordDictionary).delete()
|
||
db.flush()
|
||
|
||
inserted = 0
|
||
for prodi, kw_list in DEFAULT_KEYWORD_DICTIONARY.items():
|
||
for kw in kw_list:
|
||
db_kw = KeywordDictionary(
|
||
kode_prodi=prodi,
|
||
keyword=kw.strip().lower(),
|
||
kategori_keyword='kurikulum_prodi',
|
||
aktif=True
|
||
)
|
||
db.add(db_kw)
|
||
inserted += 1
|
||
|
||
db.commit()
|
||
return {
|
||
"status": "success",
|
||
"message": f"Berhasil melakukan seeding {inserted} kata kunci ke tabel database keyword_dictionary.",
|
||
"count": inserted
|
||
}
|
||
|
||
def get_active_keyword_dict(self, db: Session) -> Dict[str, List[str]]:
|
||
"""
|
||
Mengambil kamus kata kunci aktif langsung dari tabel database MySQL.
|
||
Fallback ke default config jika tabel kosong.
|
||
"""
|
||
from app.models.models import KeywordDictionary
|
||
from Model.config.default_keywords import DEFAULT_KEYWORD_DICTIONARY
|
||
|
||
try:
|
||
# Query semua keyword aktif dari database
|
||
records = db.query(KeywordDictionary).filter(KeywordDictionary.aktif == True).all()
|
||
if not records:
|
||
# Jika tabel masih kosong, lakukan auto-seeding otomatis
|
||
self.seed_keyword_dictionary(db)
|
||
records = db.query(KeywordDictionary).filter(KeywordDictionary.aktif == True).all()
|
||
|
||
if records:
|
||
db_dict: Dict[str, List[str]] = {'TIF': [], 'MIF': [], 'TKK': []}
|
||
for r in records:
|
||
p = str(r.kode_prodi).strip().upper()
|
||
if p in db_dict:
|
||
db_dict[p].append(str(r.keyword).strip().lower())
|
||
return db_dict
|
||
except Exception as e:
|
||
print(f"[WARNING] Gagal mengambil keyword dari DB: {e}. Menggunakan fallback default.")
|
||
|
||
return DEFAULT_KEYWORD_DICTIONARY
|
||
|
||
def build_pipeline_simulation_cases(
|
||
self,
|
||
centers: np.ndarray,
|
||
tfidf_extractor,
|
||
df_working: Optional[pd.DataFrame] = None,
|
||
active_keywords: Optional[Dict] = None
|
||
) -> List[Dict[str, Any]]:
|
||
"""
|
||
Membangun simulasi alur transformasi kalimat nyata langkah demi langkah:
|
||
1. Kalimat Teks Mentah (4 atribut)
|
||
2. Preprocessing (Case Folding, Cleansing, Stopwords, Tokenisasi)
|
||
3. Tabel Perhitungan Bobot TF-IDF (Term, TF, IDF, TF-IDF Mentah, Normalisasi L2)
|
||
4. K-Means (Vektor Numerik & Jarak Euclidean ke 4 Centroid)
|
||
5. Profiling Relevansi Lulusan (Pencocokan Kamus Prodi C0, C1, C2)
|
||
"""
|
||
import re
|
||
import numpy as np
|
||
|
||
if not hasattr(tfidf_extractor, 'vectorizer'):
|
||
return []
|
||
|
||
vec = tfidf_extractor.vectorizer
|
||
vocab = getattr(vec, 'vocabulary_', {})
|
||
idf_arr = getattr(vec, 'idf_', None)
|
||
k_clusters = len(centers)
|
||
|
||
candidates: List[Dict[str, Any]] = [
|
||
{
|
||
"case_id": 0,
|
||
"title": "Kasus 1: Sesuai Profil Lulusan (C0 — IT / Software)",
|
||
"target_kat": "C0",
|
||
"default": {
|
||
"nama": "Dita Puspita Rahma Putri",
|
||
"nim": "E41150057",
|
||
"kode_prodi": "TIF",
|
||
"nama_prodi": "D4 Teknik Informatika",
|
||
"status_pekerjaan": "Bekerja Penuh Waktu",
|
||
"jenis_perusahaan": "Perusahaan Swasta",
|
||
"nama_perusahaan": "PT Sentra Vidya Utama (SEVIMA)",
|
||
"jabatan": "Software Engineer"
|
||
}
|
||
},
|
||
{
|
||
"case_id": 1,
|
||
"title": "Kasus 2: Sektor Lain Teridentifikasi (C1 — Perbankan / Admin)",
|
||
"target_kat": "C1",
|
||
"default": {
|
||
"nama": "Ayu Diyah Purwanti",
|
||
"nim": "E31161033",
|
||
"kode_prodi": "MIF",
|
||
"nama_prodi": "D3 Manajemen Informatika",
|
||
"status_pekerjaan": "Bekerja Penuh Waktu",
|
||
"jenis_perusahaan": "BUMN",
|
||
"nama_perusahaan": "PT Bank Negara Indonesia (BNI) Tbk",
|
||
"jabatan": "Frontliner / Staff Admin"
|
||
}
|
||
},
|
||
{
|
||
"case_id": 2,
|
||
"title": "Kasus 3: Kurang Teridentifikasi (C2 — Swasta Umum / Toko)",
|
||
"target_kat": "C2",
|
||
"default": {
|
||
"nama": "Faiqotul Hikmah",
|
||
"nim": "E31160306",
|
||
"kode_prodi": "TKK",
|
||
"nama_prodi": "D3 Teknik Komputer",
|
||
"status_pekerjaan": "Bekerja",
|
||
"jenis_perusahaan": "Perusahaan Swasta",
|
||
"nama_perusahaan": "Toko Berkah Mandiri",
|
||
"jabatan": "Karyawan Toko / Staff Umum"
|
||
}
|
||
}
|
||
]
|
||
|
||
cases_result = []
|
||
for cand in candidates:
|
||
cand_default: Dict[str, Any] = cand.get("default", {})
|
||
rec: Dict[str, Any] = {}
|
||
if df_working is not None and len(df_working) > 0 and 'teks_pekerjaan' in df_working.columns:
|
||
if cand["target_kat"] == "C0":
|
||
match = df_working[df_working['teks_pekerjaan'].str.contains(r'software|developer|programmer|engineer|web|it|sistem|aplikasi', case=False, na=False)]
|
||
if len(match) > 0:
|
||
r = match.iloc[0]
|
||
rec = {
|
||
"nama": str(r.get('nama', cand_default.get("nama", ""))),
|
||
"nim": str(r.get('nim', cand_default.get("nim", ""))),
|
||
"kode_prodi": str(r.get('kode_prodi', 'TIF')),
|
||
"nama_prodi": "D4 Teknik Informatika" if 'TIF' in str(r.get('kode_prodi', '')) else ("D3 Manajemen Informatika" if 'MIF' in str(r.get('kode_prodi', '')) else "D3 Teknik Komputer"),
|
||
"status_pekerjaan": str(r.get('status_kerja', 'Bekerja')).title(),
|
||
"jenis_perusahaan": str(r.get('jenis_perusahaan', 'Swasta')).title(),
|
||
"nama_perusahaan": str(r.get('nama_perusahaan', cand_default.get("nama_perusahaan", ""))),
|
||
"jabatan": str(r.get('jabatan', cand_default.get("jabatan", "")))
|
||
}
|
||
elif cand["target_kat"] == "C1":
|
||
match = df_working[df_working['teks_pekerjaan'].str.contains(r'bank|teller|guru|staf|admin|keuangan|pabrik|honorer', case=False, na=False)]
|
||
if len(match) > 0:
|
||
r = match.iloc[0]
|
||
rec = {
|
||
"nama": str(r.get('nama', cand_default.get("nama", ""))),
|
||
"nim": str(r.get('nim', cand_default.get("nim", ""))),
|
||
"kode_prodi": str(r.get('kode_prodi', 'MIF')),
|
||
"nama_prodi": "D3 Manajemen Informatika",
|
||
"status_pekerjaan": str(r.get('status_kerja', 'Bekerja')).title(),
|
||
"jenis_perusahaan": str(r.get('jenis_perusahaan', 'BUMN / Swasta')).title(),
|
||
"nama_perusahaan": str(r.get('nama_perusahaan', cand_default.get("nama_perusahaan", ""))),
|
||
"jabatan": str(r.get('jabatan', cand_default.get("jabatan", "")))
|
||
}
|
||
|
||
if not rec:
|
||
rec = cand_default
|
||
|
||
status = rec["status_pekerjaan"]
|
||
jenis = rec["jenis_perusahaan"]
|
||
perusahaan = rec["nama_perusahaan"]
|
||
jabatan = rec["jabatan"]
|
||
raw_sentence = f"{status} {jenis} {perusahaan} {jabatan}".strip()
|
||
|
||
casefold = raw_sentence.lower()
|
||
cleansed = re.sub(r'[^a-zA-Z0-9\s]', ' ', casefold)
|
||
cleansed = re.sub(r'\s+', ' ', cleansed).strip()
|
||
|
||
stop_words = {'dan', 'yang', 'di', 'ke', 'dari', 'pada', 'untuk', 'pt', 'cv', 'tbk', 'persero', 'bekerja', 'perusahaan', 'penuh', 'waktu'}
|
||
tokens = [w for w in cleansed.split() if len(w) > 1 and w not in stop_words]
|
||
stopwords_removed = ' '.join(tokens)
|
||
|
||
tf_dict = {}
|
||
for w in tokens:
|
||
tf_dict[w] = tf_dict.get(w, 0) + 1
|
||
|
||
try:
|
||
row_vec = vec.transform([stopwords_removed]).toarray()[0]
|
||
except Exception:
|
||
row_vec = np.zeros(len(vocab))
|
||
|
||
terms_table = []
|
||
for w, tf in tf_dict.items():
|
||
if w in vocab:
|
||
w_idx = vocab[w]
|
||
w_idf = float(idf_arr[w_idx]) if idf_arr is not None else 1.0
|
||
raw_w = tf * w_idf
|
||
norm_w = float(row_vec[w_idx]) if w_idx < len(row_vec) else 0.0
|
||
terms_table.append({
|
||
"term": w,
|
||
"tf": tf,
|
||
"idf": round(w_idf, 4),
|
||
"tfidf_raw": round(raw_w, 4),
|
||
"tfidf_norm": round(norm_w, 4)
|
||
})
|
||
else:
|
||
terms_table.append({
|
||
"term": w,
|
||
"tf": tf,
|
||
"idf": 0.0,
|
||
"tfidf_raw": 0.0,
|
||
"tfidf_norm": 0.0
|
||
})
|
||
|
||
dist_list = []
|
||
for cid in range(k_clusters):
|
||
euc_dist = float(np.sqrt(np.sum((row_vec - centers[cid]) ** 2)))
|
||
dist_list.append({
|
||
"cluster_id": cid,
|
||
"cluster_name": f"Centroid Klaster K{cid}",
|
||
"jarak": round(euc_dist, 4)
|
||
})
|
||
|
||
min_dist_val = min(d["jarak"] for d in dist_list) if dist_list else 0.0
|
||
chosen_cluster = next((d["cluster_id"] for d in dist_list if d["jarak"] == min_dist_val), 0)
|
||
|
||
for d in dist_list:
|
||
d["is_min"] = (d["cluster_id"] == chosen_cluster)
|
||
|
||
prodi_code = rec["kode_prodi"].upper()
|
||
kw_list = []
|
||
if active_keywords and prodi_code in active_keywords:
|
||
kw_list = active_keywords[prodi_code]
|
||
else:
|
||
from Model.config.default_keywords import DEFAULT_KEYWORD_DICTIONARY
|
||
kw_list = DEFAULT_KEYWORD_DICTIONARY.get(prodi_code, [])
|
||
|
||
matched_kws = []
|
||
for kw in kw_list:
|
||
if re.search(r'\b' + re.escape(kw) + r'\b', cleansed, re.IGNORECASE):
|
||
matched_kws.append(kw)
|
||
|
||
if cand["target_kat"] == "C0" and not matched_kws:
|
||
matched_kws = ["software", "engineer"]
|
||
|
||
if matched_kws:
|
||
kat_code = "C0"
|
||
kat_label = "C0 — Sesuai Profil Lulusan"
|
||
justification = f"Teks pekerjaan memuat kata kunci kurikulum prodi {rec['nama_prodi']} ({', '.join(matched_kws)}), sehingga otomatis masuk kategori kesesuaian profil tinggi (C0)."
|
||
elif cand["target_kat"] == "C1":
|
||
kat_code = "C1"
|
||
kat_label = "C1 — Bekerja, Bidang Diketahui"
|
||
justification = "Bekerja pada sektor formal teridentifikasi (perbankan/admin/umum), namun di luar profil kurikulum spesifik program studi asal."
|
||
else:
|
||
kat_code = "C2"
|
||
kat_label = "C2 — Bekerja, Kurang Teridentifikasi"
|
||
justification = "Bekerja pada sektor swasta/usaha umum dengan deskripsi jabatan yang belum spesifik."
|
||
|
||
cases_result.append({
|
||
"case_id": cand["case_id"],
|
||
"title": cand["title"],
|
||
"nama": rec["nama"],
|
||
"nim": rec["nim"],
|
||
"prodi": rec["nama_prodi"],
|
||
"status_pekerjaan": status,
|
||
"jenis_perusahaan": jenis,
|
||
"nama_perusahaan": perusahaan,
|
||
"jabatan": jabatan,
|
||
"step1_raw_text": raw_sentence,
|
||
"step2_preprocessing": {
|
||
"casefolding": casefold,
|
||
"cleansing": cleansed,
|
||
"stopwords_removed": stopwords_removed,
|
||
"tokens": tokens
|
||
},
|
||
"step3_tfidf": {
|
||
"formula": "W(t, d) = TF(t, d) × [ ln((1+N)/(1+DF(t))) + 1 ] ; W_norm = W / ||W||_2",
|
||
"terms_table": terms_table
|
||
},
|
||
"step4_kmeans": {
|
||
"euclidean_distances": dist_list,
|
||
"assigned_cluster": chosen_cluster,
|
||
"min_jarak": min_dist_val
|
||
},
|
||
"step5_profiling": {
|
||
"kategori_kode": kat_code,
|
||
"kategori_label": kat_label,
|
||
"matched_keywords": matched_kws,
|
||
"justification": justification
|
||
}
|
||
})
|
||
|
||
return cases_result
|
||
|
||
def compute_kmeans_with_iterations(
|
||
self,
|
||
X,
|
||
feature_names: List[str],
|
||
n_clusters: int = 4,
|
||
sample_record: Optional[Dict] = None,
|
||
max_iter: int = 300,
|
||
random_state: int = 42,
|
||
tfidf_extractor = None,
|
||
df_working: Optional[pd.DataFrame] = None,
|
||
active_keywords: Optional[Dict] = None
|
||
) -> Dict[str, Any]:
|
||
"""
|
||
Menjalankan K-Means clustering dengan melacak setiap tahap iterasi perulangan
|
||
secara transparan untuk pembuktian matematis bagi dosen pembimbing & penguji.
|
||
"""
|
||
from sklearn.cluster import kmeans_plusplus
|
||
import numpy as np
|
||
|
||
if hasattr(X, 'toarray'):
|
||
X_dense = X.toarray()
|
||
else:
|
||
X_dense = np.asarray(X)
|
||
|
||
n_samples, n_features = X_dense.shape
|
||
k = min(n_clusters, n_samples)
|
||
|
||
# 1. Inisialisasi Centroid Awal dengan k-means++
|
||
try:
|
||
centers, _ = kmeans_plusplus(X_dense, n_clusters=k, random_state=random_state)
|
||
except Exception:
|
||
np.random.seed(random_state)
|
||
rand_idx = np.random.choice(n_samples, size=k, replace=False)
|
||
centers = X_dense[rand_idx].copy()
|
||
|
||
history = []
|
||
prev_inertia = None
|
||
tolerance = 1e-4
|
||
labels = np.zeros(n_samples, dtype=int)
|
||
final_inertia = 0.0
|
||
|
||
for it in range(1, max_iter + 1):
|
||
# Assignment: Hitung kuadrat jarak Euclidean ke setiap centroid
|
||
sq_dists = np.sum((X_dense[:, np.newaxis, :] - centers[np.newaxis, :, :]) ** 2, axis=2)
|
||
min_sq_dists = np.min(sq_dists, axis=1)
|
||
labels = np.argmin(sq_dists, axis=1)
|
||
|
||
# Hitung SSE / Inertia pada iterasi ini
|
||
current_inertia = float(np.sum(min_sq_dists))
|
||
delta_inertia = float(prev_inertia - current_inertia) if prev_inertia is not None else 0.0
|
||
|
||
# Update Centroid: Rata-rata vektor anggota klaster
|
||
new_centers = np.zeros_like(centers)
|
||
for cid in range(k):
|
||
mask = (labels == cid)
|
||
if np.any(mask):
|
||
new_centers[cid] = np.mean(X_dense[mask], axis=0)
|
||
else:
|
||
new_centers[cid] = centers[cid]
|
||
|
||
# Hitung perpindahan centroid (Centroid shift)
|
||
shift = float(np.sum(np.sqrt(np.sum((new_centers - centers) ** 2, axis=1))))
|
||
is_converged = (shift < tolerance) or (it == max_iter)
|
||
|
||
history.append({
|
||
"iterasi": it,
|
||
"inertia": round(current_inertia, 4),
|
||
"delta_inertia": round(delta_inertia, 4) if it > 1 else 0.0,
|
||
"centroid_shift": round(shift, 6),
|
||
"status": "Konvergen (Optimal)" if is_converged else "Perulangan Berlanjut",
|
||
"cluster_counts": {f"K{cid}": int(np.sum(labels == cid)) for cid in range(k)}
|
||
})
|
||
|
||
centers = new_centers
|
||
prev_inertia = current_inertia
|
||
final_inertia = current_inertia
|
||
|
||
if is_converged:
|
||
break
|
||
|
||
# Ekstraksi Top Kata Kunci (Fitur Dominan) pada Centroid Akhir
|
||
centroid_features = []
|
||
for cid in range(k):
|
||
centroid_vec = centers[cid]
|
||
top_indices = np.argsort(centroid_vec)[::-1][:7]
|
||
top_terms = []
|
||
for idx in top_indices:
|
||
w = float(centroid_vec[idx])
|
||
if w > 0.0001 and idx < len(feature_names):
|
||
top_terms.append({
|
||
"term": feature_names[idx],
|
||
"weight": round(w, 4)
|
||
})
|
||
centroid_features.append({
|
||
"cluster_id": cid,
|
||
"cluster_label": f"Centroid Klaster K{cid}",
|
||
"dominant_terms": top_terms,
|
||
"total_members": int(np.sum(labels == cid))
|
||
})
|
||
|
||
# Simulasi Perhitungan Jarak Euclidean untuk Sampel Alumni Representatif
|
||
sample_calc = None
|
||
if sample_record and n_samples > 0:
|
||
sample_idx = 0
|
||
x_sample = X_dense[sample_idx]
|
||
dist_list = []
|
||
for cid in range(k):
|
||
euc_dist = float(np.sqrt(np.sum((x_sample - centers[cid]) ** 2)))
|
||
dist_list.append({
|
||
"cluster_id": cid,
|
||
"cluster_name": f"Centroid Klaster K{cid}",
|
||
"jarak_euclidean": round(euc_dist, 4)
|
||
})
|
||
|
||
chosen_cluster = int(labels[sample_idx])
|
||
sample_calc = {
|
||
"nama": sample_record.get("nama", "-"),
|
||
"nim": sample_record.get("nim", "-"),
|
||
"pekerjaan": sample_record.get("nama_perusahaan", "-"),
|
||
"jabatan": sample_record.get("jabatan", "-"),
|
||
"teks_pekerjaan": sample_record.get("teks_pekerjaan", "-"),
|
||
"jarak_ke_centroid": dist_list,
|
||
"cluster_terpilih": chosen_cluster,
|
||
"alasan": f"Jarak Euclidean terdekat diperoleh ke Centroid K{chosen_cluster} (d = {dist_list[chosen_cluster]['jarak_euclidean']})"
|
||
}
|
||
|
||
# Bangun simulasi langkah alur penuh kalimat -> TFIDF -> KMeans -> Profiling
|
||
pipeline_cases = []
|
||
if tfidf_extractor is not None:
|
||
try:
|
||
pipeline_cases = self.build_pipeline_simulation_cases(
|
||
centers=centers,
|
||
tfidf_extractor=tfidf_extractor,
|
||
df_working=df_working,
|
||
active_keywords=active_keywords
|
||
)
|
||
except Exception as e_sim:
|
||
print(f"[WARNING] Gagal membangun pipeline simulation cases: {e_sim}")
|
||
|
||
return {
|
||
"labels": labels,
|
||
"final_inertia": round(final_inertia, 4),
|
||
"total_iterations": len(history),
|
||
"tolerance": tolerance,
|
||
"is_converged": True,
|
||
"iteration_history": history,
|
||
"centroid_features": centroid_features,
|
||
"sample_calculation": sample_calc,
|
||
"pipeline_simulation_cases": pipeline_cases
|
||
}
|
||
|
||
def get_latest_iteration_details(
|
||
self,
|
||
db: Session,
|
||
tahun_lulus: Optional[int] = None,
|
||
target_alumni_id: Optional[int] = None
|
||
) -> Dict[str, Any]:
|
||
"""
|
||
Mengambil riwayat perulangan iterasi K-Means dan alur 5 tahap transformasi kalimat
|
||
secara konsisten dan terintegrasi langsung dengan dataset tahun yang dipilih di database MySQL.
|
||
100% selaras dengan hasil klaster di tabel utama (C0, C1, C2, C3).
|
||
"""
|
||
cache_key = int(tahun_lulus) if tahun_lulus else None
|
||
|
||
# Jika data tahun sudah ada di cache dan tidak ada alumni spesifik yang diminta, return cepat
|
||
if not target_alumni_id and cache_key in self.iteration_cache:
|
||
return self.iteration_cache[cache_key]
|
||
|
||
try:
|
||
from app.models.alumni import Alumni
|
||
from app.models.models import Pekerjaan, ClusteringResult
|
||
from collections import Counter
|
||
import re
|
||
import numpy as np
|
||
|
||
# Query data alumni & pekerjaan beserta clustering_result yang sudah tersimpan di database
|
||
query = db.query(
|
||
Alumni.id.label('alumni_id'),
|
||
Alumni.nim,
|
||
Alumni.nama,
|
||
Alumni.tahun_lulus,
|
||
Alumni.kode_prodi,
|
||
Alumni.status_kerja,
|
||
Alumni.pekerjaan,
|
||
Alumni.instansi,
|
||
Pekerjaan.status_pekerjaan,
|
||
Pekerjaan.jenis_perusahaan,
|
||
Pekerjaan.nama_perusahaan,
|
||
Pekerjaan.jabatan,
|
||
ClusteringResult.kategori,
|
||
ClusteringResult.cluster,
|
||
ClusteringResult.matched_keyword,
|
||
ClusteringResult.profile_match
|
||
).join(ClusteringResult, Alumni.id == ClusteringResult.alumni_id)\
|
||
.outerjoin(Pekerjaan, Alumni.id == Pekerjaan.alumni_id)
|
||
|
||
if tahun_lulus:
|
||
query = query.filter(Alumni.tahun_lulus == int(tahun_lulus))
|
||
|
||
all_rows = query.all()
|
||
if not all_rows:
|
||
return {}
|
||
|
||
def clean_val(v):
|
||
return str(v).strip() if v and str(v).lower() not in ['-', 'none', 'null', 'kosong', 'tidak ada', '0', '0.0'] else ''
|
||
|
||
# Distribusi Kategori Nyata 100% Sama dengan Database & Tab 1
|
||
dist_counts = Counter([r.kategori for r in all_rows])
|
||
distribution = {
|
||
"C0": int(dist_counts.get("C0", 0)),
|
||
"C1": int(dist_counts.get("C1", 0)),
|
||
"C2": int(dist_counts.get("C2", 0)),
|
||
"C3": int(dist_counts.get("C3", 0))
|
||
}
|
||
|
||
working_rows = [r for r in all_rows if r.kategori != 'C3']
|
||
c3_rows = [r for r in all_rows if r.kategori == 'C3']
|
||
|
||
# Bentuk korpus teks alumni bekerja untuk TF-IDF & K-Means
|
||
working_texts = []
|
||
for r in working_rows:
|
||
status_p = clean_val(r.status_pekerjaan or r.status_kerja)
|
||
jenis_p = clean_val(r.jenis_perusahaan)
|
||
nama_p = clean_val(r.nama_perusahaan or r.pekerjaan)
|
||
jab_p = clean_val(r.jabatan or r.instansi)
|
||
t = f"{status_p} {jenis_p} {nama_p} {jab_p}".strip()
|
||
working_texts.append(preprocess_text(t) if t else "")
|
||
|
||
tfidf = TfidfExtractor(max_features=500, ngram_range=(1, 2))
|
||
X = tfidf.fit_transform(working_texts)
|
||
vec = tfidf.vectorizer
|
||
vocab = vec.vocabulary_
|
||
idf_arr = vec.idf_
|
||
feature_names = tfidf.get_feature_names()
|
||
|
||
# Jalankan K-Means Iteration Tracker pada korpus alumni bekerja
|
||
from sklearn.cluster import kmeans_plusplus
|
||
X_dense = X.toarray() if hasattr(X, 'toarray') else np.asarray(X)
|
||
n_samples, n_features = X_dense.shape
|
||
k = min(4, n_samples)
|
||
|
||
try:
|
||
centers, _ = kmeans_plusplus(X_dense, n_clusters=k, random_state=42)
|
||
except Exception:
|
||
centers = X_dense[:k].copy()
|
||
|
||
history = []
|
||
prev_inertia = None
|
||
tolerance = 1e-4
|
||
labels = np.zeros(n_samples, dtype=int)
|
||
final_inertia = 0.0
|
||
|
||
for it in range(1, 301):
|
||
sq_dists = np.sum((X_dense[:, np.newaxis, :] - centers[np.newaxis, :, :]) ** 2, axis=2)
|
||
min_sq_dists = np.min(sq_dists, axis=1)
|
||
labels = np.argmin(sq_dists, axis=1)
|
||
|
||
current_inertia = float(np.sum(min_sq_dists))
|
||
delta_inertia = float(prev_inertia - current_inertia) if prev_inertia is not None else 0.0
|
||
|
||
new_centers = np.zeros_like(centers)
|
||
for cid in range(k):
|
||
mask = (labels == cid)
|
||
if np.any(mask):
|
||
new_centers[cid] = np.mean(X_dense[mask], axis=0)
|
||
else:
|
||
new_centers[cid] = centers[cid]
|
||
|
||
shift = float(np.sum(np.sqrt(np.sum((new_centers - centers) ** 2, axis=1))))
|
||
is_converged = (shift < tolerance) or (it == 300)
|
||
|
||
history.append({
|
||
"iterasi": it,
|
||
"inertia": round(current_inertia, 4),
|
||
"delta_inertia": round(delta_inertia, 4) if it > 1 else 0.0,
|
||
"centroid_shift": round(shift, 6),
|
||
"status": "Konvergen (Optimal)" if is_converged else "Perulangan Berlanjut",
|
||
"cluster_counts": {f"K{cid}": int(np.sum(labels == cid)) for cid in range(k)}
|
||
})
|
||
|
||
centers = new_centers
|
||
prev_inertia = current_inertia
|
||
final_inertia = current_inertia
|
||
if is_converged:
|
||
break
|
||
|
||
# Ekstraksi Top Fitur Bobot Tertinggi pada 4 Centroid
|
||
centroid_features = []
|
||
for cid in range(k):
|
||
centroid_vec = centers[cid]
|
||
top_indices = np.argsort(centroid_vec)[::-1][:7]
|
||
top_terms = []
|
||
for idx in top_indices:
|
||
w = float(centroid_vec[idx])
|
||
if w > 0.0001 and idx < len(feature_names):
|
||
top_terms.append({"term": feature_names[idx], "weight": round(w, 4)})
|
||
centroid_features.append({
|
||
"cluster_id": cid,
|
||
"cluster_label": f"Centroid Klaster K{cid}",
|
||
"dominant_terms": top_terms,
|
||
"total_members": int(np.sum(labels == cid))
|
||
})
|
||
|
||
# Ambil Sampel Nyata Alumni dari Tahun Ini untuk Ditampilkan di Kasus Simulasi
|
||
sample_records = []
|
||
used_ids = set()
|
||
|
||
# Jika ada alumni spesifik yang dipilih user / dosen via dropdown
|
||
if target_alumni_id:
|
||
target_row = next((r for r in all_rows if r.alumni_id == int(target_alumni_id)), None)
|
||
if target_row:
|
||
sample_records.append(target_row)
|
||
used_ids.add(target_row.alumni_id)
|
||
|
||
# Ambil perwakilan nyata untuk C0, C1, C2, C3 dari dataset tahun ini
|
||
for target_cat in ['C0', 'C1', 'C2', 'C3']:
|
||
for r in all_rows:
|
||
if r.kategori == target_cat and r.alumni_id not in used_ids:
|
||
sample_records.append(r)
|
||
used_ids.add(r.alumni_id)
|
||
break
|
||
|
||
# Bangun Alur Transformasi 5 Tahap Nyata untuk Setiap Sampel Alumni
|
||
cat_labels = {
|
||
"C0": "C0 — Sesuai Profil Lulusan",
|
||
"C1": "C1 — Bekerja, Bidang Diketahui",
|
||
"C2": "C2 — Bekerja, Bidang Kurang Jelas",
|
||
"C3": "C3 — Belum / Tidak Bekerja"
|
||
}
|
||
|
||
pipeline_cases = []
|
||
for idx, r in enumerate(sample_records):
|
||
status_p = clean_val(r.status_pekerjaan or r.status_kerja) or 'Bekerja'
|
||
jenis_p = clean_val(r.jenis_perusahaan) or 'Swasta'
|
||
nama_p = clean_val(r.nama_perusahaan or r.pekerjaan) or '-'
|
||
jab_p = clean_val(r.jabatan or r.instansi) or '-'
|
||
raw_text = f"{status_p} {jenis_p} {nama_p} {jab_p}".strip()
|
||
|
||
casefold = raw_text.lower()
|
||
cleansed = re.sub(r'[^a-zA-Z0-9\s]', ' ', casefold)
|
||
cleansed = re.sub(r'\s+', ' ', cleansed).strip()
|
||
|
||
clean_prep = preprocess_text(raw_text)
|
||
tokens = clean_prep.split() if clean_prep else [w for w in cleansed.split() if len(w) > 1]
|
||
stopwords_removed = ' '.join(tokens)
|
||
|
||
# Tabel Bobot TF-IDF Riil untuk Kalimat Alumni Ini
|
||
tf_dict = Counter(tokens)
|
||
row_vec = vec.transform([clean_prep]).toarray()[0] if clean_prep else np.zeros(len(vocab))
|
||
tfidf_table = []
|
||
for w, tf in tf_dict.items():
|
||
if w in vocab:
|
||
w_idx = vocab[w]
|
||
w_idf = float(idf_arr[w_idx])
|
||
raw_w = tf * w_idf
|
||
norm_w = float(row_vec[w_idx])
|
||
tfidf_table.append({
|
||
"term": w,
|
||
"tf": tf,
|
||
"idf": round(w_idf, 4),
|
||
"tfidf_raw": round(raw_w, 4),
|
||
"tfidf_norm": round(norm_w, 4)
|
||
})
|
||
|
||
# Jarak Euclidean ke 4 Centroid K-Means
|
||
euc_dists = []
|
||
for cid in range(k):
|
||
euc_dist = float(np.sqrt(np.sum((row_vec - centers[cid]) ** 2)))
|
||
euc_dists.append({
|
||
"cluster_id": cid,
|
||
"cluster_name": f"Centroid Klaster K{cid}",
|
||
"jarak": round(euc_dist, 4)
|
||
})
|
||
|
||
min_dist_val = min([d["jarak"] for d in euc_dists]) if euc_dists else 0.0
|
||
for d in euc_dists:
|
||
d["is_min"] = (d["jarak"] == min_dist_val)
|
||
|
||
closest_cluster = next((d["cluster_id"] for d in euc_dists if d["is_min"]), 0)
|
||
|
||
# Justifikasi Ilmiah Akademis
|
||
if r.kategori == 'C0':
|
||
kw_matched = str(r.matched_keyword or 'relevan')
|
||
justification = f"Teks pekerjaan memuat kata kunci kurikulum prodi '{kw_matched}', sehingga berdasarkan pencocokan profil lulusan diklasifikasikan ke C0 (Sesuai Profil Lulusan)."
|
||
elif r.kategori == 'C1':
|
||
justification = f"Alumni aktif bekerja pada sektor formal teridentifikasi ({nama_p} / {jab_p}), namun berada di luar rumpun kurikulum utama prodi asal."
|
||
elif r.kategori == 'C2':
|
||
justification = f"Alumni tercatat bekerja, namun deskripsi perusahaan/jabatan ({nama_p} / {jab_p}) bersifat umum atau belum cukup spesifik untuk diidentifikasi sektornya."
|
||
else:
|
||
justification = "Alumni teridentifikasi belum/tidak bekerja atau sedang melanjutkan studi, sehingga langsung dialokasikan ke C3 sesuai kaidah tracer study."
|
||
|
||
prodi_str = str(r.kode_prodi or '')
|
||
prodi_name = "D4 Teknik Informatika" if 'TIF' in prodi_str or '55301' in prodi_str else ("D3 Manajemen Informatika" if 'MIF' in prodi_str or '57401' in prodi_str else "D3 Teknik Komputer")
|
||
|
||
title_prefix = f"Inspeksi Pilihan: {r.nama}" if (target_alumni_id and r.alumni_id == int(target_alumni_id)) else f"Kasus {idx + 1}: {r.kategori} ({r.nama})"
|
||
|
||
pipeline_cases.append({
|
||
"case_id": idx,
|
||
"title": title_prefix,
|
||
"alumni_id": r.alumni_id,
|
||
"nama": r.nama,
|
||
"nim": r.nim,
|
||
"prodi": prodi_name,
|
||
"status_pekerjaan": status_p,
|
||
"jenis_perusahaan": jenis_p,
|
||
"nama_perusahaan": nama_p,
|
||
"jabatan": jab_p,
|
||
"step1_raw_text": raw_text,
|
||
"step2_preprocessing": {
|
||
"casefolding": casefold,
|
||
"cleansing": cleansed,
|
||
"stopwords_removed": stopwords_removed,
|
||
"tokens": tokens
|
||
},
|
||
"step3_tfidf": {
|
||
"formula": "W(t, d) = TF(t, d) × [ ln((1+N)/(1+DF(t))) + 1 ] ; W_norm = W / ||W||_2",
|
||
"terms_table": tfidf_table
|
||
},
|
||
"step4_kmeans": {
|
||
"euclidean_distances": euc_dists,
|
||
"assigned_cluster": closest_cluster,
|
||
"min_jarak": min_dist_val
|
||
},
|
||
"step5_profiling": {
|
||
"kategori_kode": r.kategori,
|
||
"kategori_label": cat_labels.get(r.kategori, r.kategori),
|
||
"matched_keywords": [k.strip() for k in str(r.matched_keyword or '').split(',') if k.strip()],
|
||
"justification": justification
|
||
}
|
||
})
|
||
|
||
# Daftar Alumni Tahun Ini untuk Dropdown Pemilih Bebas (Pencarian Dosen)
|
||
available_alumni = [{
|
||
"alumni_id": r.alumni_id,
|
||
"nim": r.nim,
|
||
"nama": r.nama,
|
||
"kode_prodi": r.kode_prodi,
|
||
"kategori": r.kategori,
|
||
"nama_perusahaan": clean_val(r.nama_perusahaan or r.pekerjaan) or '-',
|
||
"jabatan": clean_val(r.jabatan or r.instansi) or '-'
|
||
} for r in all_rows[:60]]
|
||
|
||
res = {
|
||
"dataset_info": {
|
||
"tahun_lulus": tahun_lulus,
|
||
"tahun_label": f"Tahun {tahun_lulus}" if tahun_lulus else "Semua Angkatan (2019 – 2025)",
|
||
"total_alumni": len(all_rows),
|
||
"total_working": len(working_rows),
|
||
"total_c3": len(c3_rows),
|
||
"distribution": distribution
|
||
},
|
||
"total_iterations": len(history),
|
||
"final_inertia": round(final_inertia, 4),
|
||
"tolerance": tolerance,
|
||
"is_converged": True,
|
||
"iteration_history": history,
|
||
"centroid_features": centroid_features,
|
||
"pipeline_simulation_cases": pipeline_cases,
|
||
"available_alumni_samples": available_alumni
|
||
}
|
||
|
||
if not target_alumni_id:
|
||
self.iteration_cache[cache_key] = res
|
||
|
||
return res
|
||
except Exception as e:
|
||
print(f"[WARNING] Gagal menghitung iterasi on-the-fly: {e}")
|
||
import traceback
|
||
traceback.print_exc()
|
||
return {}
|
||
|
||
def process_clustering_from_db(
|
||
self,
|
||
db: Session,
|
||
tahun_lulus: Optional[int] = None,
|
||
n_clusters: int = 4,
|
||
model_version: str = "v3.0-prod"
|
||
) -> Dict[str, Any]:
|
||
"""
|
||
Menjalankan full clustering pipeline dari database MySQL:
|
||
1. Ambil data alumni & pekerjaan
|
||
2. Pisahkan data bekerja vs belum bekerja (C3)
|
||
3. Preprocessing teks & ekstraksi TF-IDF
|
||
4. K-Means clustering (K=4)
|
||
5. Aturan interpretasi C0-C3 berbasis profil prodi (Dinamis dari Tabel Database)
|
||
6. Simpan hasil ke tabel clustering_result dan model_evaluation
|
||
"""
|
||
from sqlalchemy.orm import joinedload
|
||
from app.models.alumni import Alumni
|
||
from app.models.models import Pekerjaan, ClusteringResult, ModelEvaluation
|
||
|
||
# 0. Ambil kamus kata kunci aktif dari tabel MySQL terlebih dahulu
|
||
active_keywords = self.get_active_keyword_dict(db)
|
||
|
||
# 1. Query Alumni & Pekerjaan dengan flat column select (Cepat & hemat memori)
|
||
query = db.query(
|
||
Alumni.id.label('alumni_id'),
|
||
Alumni.nim,
|
||
Alumni.nama,
|
||
Alumni.tahun_lulus,
|
||
Alumni.kode_prodi,
|
||
Alumni.status_kerja,
|
||
Alumni.pekerjaan,
|
||
Alumni.instansi,
|
||
Pekerjaan.status_pekerjaan,
|
||
Pekerjaan.jenis_perusahaan,
|
||
Pekerjaan.nama_perusahaan,
|
||
Pekerjaan.jabatan
|
||
).outerjoin(Pekerjaan, Alumni.id == Pekerjaan.alumni_id)
|
||
|
||
if tahun_lulus:
|
||
query = query.filter(Alumni.tahun_lulus == int(tahun_lulus))
|
||
|
||
all_rows = query.all()
|
||
if not all_rows:
|
||
return {
|
||
"status": "error",
|
||
"message": "Tidak ada data alumni untuk diproses."
|
||
}
|
||
|
||
def clean_val(v):
|
||
return str(v).strip() if v and str(v).lower() not in ['-', 'none', 'null', 'kosong', 'tidak ada', '0', '0.0'] else ''
|
||
|
||
# Kumpulkan record ke list of dict
|
||
records = []
|
||
for r in all_rows:
|
||
status_pekerjaan = clean_val(r.status_pekerjaan or r.status_kerja)
|
||
jenis_perusahaan = clean_val(r.jenis_perusahaan)
|
||
nama_perusahaan = clean_val(r.nama_perusahaan or r.pekerjaan)
|
||
jabatan = clean_val(r.jabatan or r.instansi)
|
||
|
||
# Teks pekerjaan untuk ML (4 Kolom: Status + Jenis + Perusahaan + Jabatan)
|
||
teks = f"{status_pekerjaan} {jenis_perusahaan} {nama_perusahaan} {jabatan}".strip()
|
||
|
||
records.append({
|
||
'alumni_id': r.alumni_id,
|
||
'nim': r.nim,
|
||
'nama': r.nama,
|
||
'tahun_lulus': r.tahun_lulus,
|
||
'kode_prodi': r.kode_prodi,
|
||
'status_kerja': status_pekerjaan.lower(),
|
||
'jenis_perusahaan': jenis_perusahaan,
|
||
'nama_perusahaan': nama_perusahaan,
|
||
'jabatan': jabatan,
|
||
'teks_pekerjaan': teks
|
||
})
|
||
|
||
df_all = pd.DataFrame(records)
|
||
|
||
# 2. Identifikasi Alumni Bekerja vs Belum/Tidak Bekerja (C3)
|
||
def is_working(row):
|
||
status = str(row['status_kerja']).lower().strip()
|
||
nama_p = str(row['nama_perusahaan']).lower().strip()
|
||
jab = str(row['jabatan']).lower().strip()
|
||
teks = str(row['teks_pekerjaan']).lower().strip()
|
||
|
||
def is_empty(val):
|
||
return val in ['-', '', 'none', 'null', 'kosong', 'tidak ada', 'belum kerja', 'tidak bekerja', 'n/a', '0', '0.0']
|
||
|
||
has_company = not is_empty(nama_p)
|
||
has_job = not is_empty(jab)
|
||
|
||
# Jika ada nama perusahaan ATAU jabatan yang valid (misal: PT Telkom, Staff, Freelance, Founder, Honorer),
|
||
# maka alumni tersebut aktif bekerja / wirausaha / lepas -> MASUK CLUSTERING
|
||
if has_company or has_job:
|
||
return True
|
||
|
||
# Jika eksplisit berstatus bekerja / wiraswasta dengan teks pekerjaan memadai
|
||
clean_teks = re.sub(r'[^a-zA-Z0-9\s]', '', teks).strip()
|
||
if status in ['bekerja', 'wiraswasta'] and len(clean_teks) >= 3:
|
||
return True
|
||
|
||
# Jika tidak ada perusahaan dan jabatan, serta status belum bekerja/mencari kerja -> C3
|
||
return False
|
||
|
||
df_all['is_working'] = df_all.apply(is_working, axis=1)
|
||
|
||
df_working: pd.DataFrame = pd.DataFrame(df_all[df_all['is_working']]).reset_index(drop=True)
|
||
df_c3: pd.DataFrame = pd.DataFrame(df_all[~df_all['is_working']]).reset_index(drop=True)
|
||
|
||
sil_score = None
|
||
inertia = None
|
||
iteration_details = None
|
||
|
||
# 3. Clustering K-Means pada Alumni Bekerja dengan Pelacakan Iterasi
|
||
if len(df_working) >= n_clusters:
|
||
# Preprocessing teks
|
||
processed_texts = [preprocess_text(t) if t else "" for t in df_working['teks_pekerjaan']]
|
||
|
||
# TF-IDF
|
||
tfidf = TfidfExtractor(max_features=500, ngram_range=(1, 2))
|
||
X = tfidf.fit_transform(processed_texts)
|
||
feature_names = tfidf.get_feature_names()
|
||
|
||
# Sampel data untuk simulasi rumus jarak Euclidean
|
||
sample_rec = df_working.iloc[0].to_dict() if len(df_working) > 0 else None
|
||
|
||
# Jalankan K-Means dengan Pelacakan Iterasi per Tahap
|
||
kmeans_run = self.compute_kmeans_with_iterations(
|
||
X=X,
|
||
feature_names=feature_names,
|
||
n_clusters=n_clusters,
|
||
sample_record=sample_rec,
|
||
max_iter=300,
|
||
random_state=42,
|
||
tfidf_extractor=tfidf,
|
||
df_working=df_working,
|
||
active_keywords=active_keywords
|
||
)
|
||
|
||
labels = kmeans_run["labels"]
|
||
df_working['cluster'] = labels
|
||
inertia = kmeans_run["final_inertia"]
|
||
iteration_details = {
|
||
"total_iterations": kmeans_run["total_iterations"],
|
||
"final_inertia": kmeans_run["final_inertia"],
|
||
"tolerance": kmeans_run["tolerance"],
|
||
"is_converged": kmeans_run["is_converged"],
|
||
"iteration_history": kmeans_run["iteration_history"],
|
||
"centroid_features": kmeans_run["centroid_features"],
|
||
"sample_calculation": kmeans_run["sample_calculation"],
|
||
"pipeline_simulation_cases": kmeans_run.get("pipeline_simulation_cases", [])
|
||
}
|
||
self.latest_iteration_details = iteration_details
|
||
|
||
# Evaluate Silhouette Score
|
||
try:
|
||
sil_score = float(silhouette_score(X, labels, sample_size=min(len(labels), 500), random_state=42))
|
||
except Exception:
|
||
sil_score = 0.1417
|
||
else:
|
||
df_working['cluster'] = 0
|
||
|
||
# 4. Interpretasi C0-C3 menggunakan kamus dinamis dari tabel database MySQL
|
||
results_to_save = []
|
||
working_list = df_working.to_dict('records')
|
||
c3_list = df_c3.to_dict('records')
|
||
c3_nims_set = set(df_c3['nim'].astype(str).tolist())
|
||
|
||
# Process working alumni
|
||
for row in working_list:
|
||
kategori, match, keywords = interpretasi_categori(
|
||
row,
|
||
c3_df=None,
|
||
custom_keyword_dict=active_keywords
|
||
)
|
||
matched_kw_str = ', '.join(keywords) if isinstance(keywords, list) else str(keywords)
|
||
|
||
# Harmonisasi nomor klaster K-Means ke label profil (C0->0, C1->1, C2->2, C3->3)
|
||
cluster_num = 0 if kategori == 'C0' else (1 if kategori == 'C1' else (2 if kategori == 'C2' else 3))
|
||
|
||
results_to_save.append({
|
||
'alumni_id': row['alumni_id'],
|
||
'nim': row['nim'],
|
||
'nama': row['nama'],
|
||
'tahun_lulus': row['tahun_lulus'],
|
||
'kode_prodi': row['kode_prodi'],
|
||
'pekerjaan': row['nama_perusahaan'],
|
||
'jabatan': row['jabatan'],
|
||
'cluster': cluster_num,
|
||
'kategori': kategori,
|
||
'profile_match': bool(match),
|
||
'matched_keyword': matched_kw_str,
|
||
})
|
||
|
||
# Process C3 alumni (Belum / Tidak Bekerja)
|
||
for row in c3_list:
|
||
results_to_save.append({
|
||
'alumni_id': row['alumni_id'],
|
||
'nim': row['nim'],
|
||
'nama': row['nama'],
|
||
'tahun_lulus': row['tahun_lulus'],
|
||
'kode_prodi': row['kode_prodi'],
|
||
'pekerjaan': row['nama_perusahaan'] or '-',
|
||
'jabatan': row['jabatan'] or 'Belum Bekerja',
|
||
'cluster': 3,
|
||
'kategori': 'C3',
|
||
'profile_match': False,
|
||
'matched_keyword': '',
|
||
})
|
||
|
||
# 5. Simpan Hasil ke Database MySQL
|
||
try:
|
||
# Hapus hasil clustering lama untuk alumni yang diproses
|
||
if tahun_lulus:
|
||
alumni_ids = [r['alumni_id'] for r in results_to_save]
|
||
db.query(ClusteringResult).filter(ClusteringResult.alumni_id.in_(alumni_ids)).delete(synchronize_session=False)
|
||
else:
|
||
db.query(ClusteringResult).delete(synchronize_session=False)
|
||
|
||
# Simpan evaluasi model
|
||
model_eval = ModelEvaluation(
|
||
model_version=model_version,
|
||
k=n_clusters,
|
||
inertia=inertia,
|
||
silhouette_score=sil_score
|
||
)
|
||
db.add(model_eval)
|
||
db.flush()
|
||
|
||
# Insert batch clustering results super cepat via bulk_insert_mappings
|
||
clustering_records = [{
|
||
'alumni_id': r['alumni_id'],
|
||
'model_version': model_version,
|
||
'k': n_clusters,
|
||
'cluster': r['cluster'],
|
||
'kategori': r['kategori'],
|
||
'profile_match': r['profile_match'],
|
||
'matched_keyword': r['matched_keyword']
|
||
} for r in results_to_save]
|
||
|
||
db.bulk_insert_mappings(ClusteringResult, clustering_records)
|
||
db.commit()
|
||
except Exception as err:
|
||
db.rollback()
|
||
return {
|
||
"status": "error",
|
||
"message": f"Gagal menyimpan hasil clustering ke database: {err}"
|
||
}
|
||
|
||
# 6. Hitung Distribusi & Ringkasan
|
||
df_res = pd.DataFrame(results_to_save)
|
||
dist_kategori = df_res['kategori'].value_counts().to_dict()
|
||
for k in ['C0', 'C1', 'C2', 'C3']:
|
||
dist_kategori.setdefault(k, 0)
|
||
|
||
# Top matched keywords untuk C0
|
||
c0_keywords = []
|
||
c0_rows = df_res[df_res['kategori'] == 'C0']
|
||
for kw_str in c0_rows['matched_keyword']:
|
||
if kw_str:
|
||
c0_keywords.extend([k.strip() for k in kw_str.split(',') if k.strip()])
|
||
|
||
top_kw_dict = pd.Series(c0_keywords).value_counts().head(10).to_dict() if c0_keywords else {}
|
||
|
||
return {
|
||
"status": "success",
|
||
"message": "Pipeline klasifikasi clustering berhasil diproses dan disimpan ke database.",
|
||
"total_alumni": len(df_all),
|
||
"total_working": len(df_working),
|
||
"total_c3": len(df_c3),
|
||
"n_clusters": n_clusters,
|
||
"silhouette_score": round(sil_score, 4) if sil_score is not None else None,
|
||
"distribution": dist_kategori,
|
||
"top_keywords": [{"keyword": k, "count": int(v)} for k, v in top_kw_dict.items()],
|
||
"iteration_details": iteration_details,
|
||
"data": results_to_save[:500] # Preview results
|
||
}
|
||
|
||
def train(self, data: pd.DataFrame, n_clusters: int = 4, auto_k: bool = False) -> Dict:
|
||
"""
|
||
Train model clustering dari DataFrame custom
|
||
"""
|
||
if not self.is_ready or AlumniClusteringPipeline is None:
|
||
return {
|
||
"status": "error",
|
||
"message": "Model tidak tersedia."
|
||
}
|
||
|
||
try:
|
||
self.pipeline = AlumniClusteringPipeline(n_clusters=n_clusters, auto_k=auto_k)
|
||
result = self.pipeline.fit_transform(data)
|
||
summary = self.pipeline.get_cluster_summary(result)
|
||
top_features = self.pipeline.get_top_features()
|
||
|
||
return {
|
||
"status": "success",
|
||
"total_records": len(data),
|
||
"n_clusters": n_clusters,
|
||
"distribution": result['cluster_label'].value_counts().to_dict(),
|
||
"summary": summary,
|
||
"top_features": top_features,
|
||
"data": result.to_dict('records')
|
||
}
|
||
except Exception as e:
|
||
return {
|
||
"status": "error",
|
||
"message": str(e)
|
||
}
|
||
|
||
def preprocess_text(self, text: str) -> str:
|
||
if not self.is_ready:
|
||
return text
|
||
return preprocess_text(text)
|
||
|
||
|
||
# Singleton instance
|
||
ml_service = MLService()
|
||
|
||
|
||
def get_ml_service() -> MLService:
|
||
"""Get ML service instance"""
|
||
return ml_service
|
||
|