""" Step 19: 3-way 비교 평가 — bge-m3 vs +HyDE vs +Contextual+HyDE Contextual Retrieval(18번 스크립트) 적용 후 개선 효과 측정. 기존 Menusearch_100_answer.xlsx의 bge-m3 / +HyDE 결과를 baseline으로 읽고, 새로 재빌드된 인덱스로 +Contextual+HyDE 검색을 실행해 3-way 비교. 실행 순서: 1. python scripts/18_contextual_retrieval.py (인덱스 재빌드 포함) 2. python scripts/19_eval_triple.py 출력: data/generated/Menusearch_triple_YYYYMMDD.xlsx Excel 컬럼: A: No B: 쿼리 C: 정답 메뉴 D~H : [bge-m3] Top1~5 (baseline, 이전 결과) I : [bge-m3] 결과 J~N : [+HyDE] Top1~5 (baseline, 이전 결과) O : [+HyDE] 결과 P~T : [+Contextual+HyDE] Top1~5 (신규 검색) U : [+Contextual+HyDE] 결과 V : 변화 (+HyDE vs +Contextual+HyDE) """ import sys import re from datetime import datetime from pathlib import Path sys.path.insert(0, str(Path(__file__).parent.parent)) import pandas as pd from openpyxl import load_workbook from openpyxl.styles import PatternFill, Font, Alignment from core.search_engine import MenuSearchEngine # ── 경로 ────────────────────────────────────────────────────────────────────── BASE_DIR = Path(__file__).parent.parent BASELINE_PATH = BASE_DIR / "data" / "generated" / "Menusearch_100_answer.xlsx" TODAY = datetime.now().strftime("%Y%m%d") OUT_PATH = BASE_DIR / "data" / "generated" / f"Menusearch_triple_{TODAY}.xlsx" RANK_MAP = {"Top1 정답": 3, "Top3 정답": 2, "Top5 정답": 1, "오답": 0, "평가제외": -1} # ── 정답 비교 유틸 ───────────────────────────────────────────────────────────── def normalize_path(path: str) -> str: if not path: return "" path = re.sub(r'\s*>\s*', '>', path) path = re.sub(r'\(.*?\)', '', path) return path.strip().lower() def matches(pred: str, ans: str) -> bool: if not pred or not ans: return False if pred == ans: return True pred_end = pred.split(">")[-1].strip() ans_end = ans.split(">")[-1].strip() if pred_end and ans_end and pred_end == ans_end: pred_parts = pred.split(">") ans_parts = ans.split(">") overlap = sum(1 for p in pred_parts if p in ans_parts) return overlap >= min(2, len(ans_parts)) return False def evaluate(answer_cell, top_paths: list) -> tuple: if not answer_cell or pd.isna(answer_cell): return None, None, None ans_lower = str(answer_cell).lower() first_line = ans_lower.split("\n")[0] if ">" not in first_line and any( first_line.startswith(m) for m in ["hts", "계좌개설", "마이페이지"] ): return None, None, None valid_answers = [normalize_path(a) for a in str(answer_cell).split("\n") if a.strip()] norm_top = [normalize_path(p) for p in top_paths if p] def hit_at(k): return any(matches(p, a) for p in norm_top[:k] for a in valid_answers) return hit_at(1), hit_at(3), hit_at(5) def label_from_eval(a1, a3, a5) -> str: if a1 is None: return "평가제외" if a1: return "Top1 정답" if a3: return "Top3 정답" if a5: return "Top5 정답" return "오답" # ── 통계 ────────────────────────────────────────────────────────────────────── def calc_stats(labels: list[str]) -> tuple: a1_list = [lbl == "Top1 정답" for lbl in labels if lbl != "평가제외"] a3_list = [lbl in ("Top1 정답", "Top3 정답") for lbl in labels if lbl != "평가제외"] a5_list = [lbl in ("Top1 정답", "Top3 정답", "Top5 정답") for lbl in labels if lbl != "평가제외"] valid = sum(1 for lbl in labels if lbl != "평가제외") skip = len(labels) - valid n1 = sum(a1_list); n3 = sum(a3_list); n5 = sum(a5_list) r1 = n1 / valid if valid else 0 r3 = n3 / valid if valid else 0 r5 = n5 / valid if valid else 0 return valid, skip, n1, n3, n5, r1, r3, r5 # ── 신규 검색 ───────────────────────────────────────────────────────────────── def run_contextual_search(engine, queries: list[str], answers: list, use_hyde: bool) -> list[dict]: label = "+Contextual+HyDE" if use_hyde else "+Contextual" print(f"\n[검색] {label} 모드...") results = [] for i, (query, answer_raw) in enumerate(zip(queries, answers)): hits = engine.search(query, top_n=5, threshold=0.0, use_hyde=use_hyde) paths = [h.get("menu_path", "") for h in hits] while len(paths) < 5: paths.append("") a1, a3, a5 = evaluate(answer_raw, paths) lbl = label_from_eval(a1, a3, a5) status = "[O]" if a1 else ("[ ]" if a1 is None else "[X]") print(f" {status} [{i+1:3d}] {query[:28]:<28} → {paths[0][:40]}") results.append({"paths": paths, "label": lbl, "a1": a1, "a3": a3, "a5": a5}) return results # ── 메인 ───────────────────────────────────────────────────────────────────── def main(): print("[19_triple] 3-way 비교 평가 시작") # ── 1. baseline 로드 ────────────────────────────────────────────────────── if not BASELINE_PATH.exists(): print(f"[오류] 기준 파일 없음: {BASELINE_PATH}") print(" 먼저 python scripts/17_eval_comparison.py 를 실행하세요.") sys.exit(1) df_base = pd.read_excel(BASELINE_PATH, sheet_name="시트1") # 숫자 No 행만 필터 df_base = df_base[pd.to_numeric(df_base["No"], errors="coerce").notna()].reset_index(drop=True) print(f"[19_triple] baseline {len(df_base)}개 행 로드 (from {BASELINE_PATH.name})") # baseline 컬럼 자동 탐지 (17_eval 출력 컬럼명 기준) bge_top_cols = [c for c in df_base.columns if "[bge-m3] Top" in str(c)] bge_lbl_col = next((c for c in df_base.columns if "[bge-m3] 결과" in str(c)), None) hyde_top_cols = [c for c in df_base.columns if "[+HyDE] Top" in str(c)] hyde_lbl_col = next((c for c in df_base.columns if "[+HyDE] 결과" in str(c)), None) if not bge_top_cols or not hyde_top_cols: print("[오류] baseline Excel에서 [bge-m3] / [+HyDE] 컬럼을 찾을 수 없습니다.") print(" 17_eval_comparison.py 로 먼저 baseline을 생성해 주세요.") sys.exit(1) queries = df_base["쿼리"].astype(str).str.strip().tolist() answers = [row if pd.notna(row) else None for row in df_base["정답 메뉴"]] bge_labels = df_base[bge_lbl_col].astype(str).tolist() if bge_lbl_col else ["평가제외"] * len(df_base) hyde_labels = df_base[hyde_lbl_col].astype(str).tolist() if hyde_lbl_col else ["평가제외"] * len(df_base) # ── 2. 검색엔진 초기화 ──────────────────────────────────────────────────── print("[19_triple] 검색엔진 초기화 (Contextual Retrieval 인덱스)…") engine = MenuSearchEngine.get_instance() print("[19_triple] 준비 완료") # ── 3. 신규 검색 ────────────────────────────────────────────────────────── ctx_results = run_contextual_search(engine, queries, answers, use_hyde=False) ctxh_results = run_contextual_search(engine, queries, answers, use_hyde=True) ctx_labels = [r["label"] for r in ctx_results] ctxh_labels = [r["label"] for r in ctxh_results] # ── 4. 통계 ─────────────────────────────────────────────────────────────── b_valid, b_skip, b_n1, b_n3, b_n5, b_r1, b_r3, b_r5 = calc_stats(bge_labels) h_valid, h_skip, h_n1, h_n3, h_n5, h_r1, h_r3, h_r5 = calc_stats(hyde_labels) c_valid, c_skip, c_n1, c_n3, c_n5, c_r1, c_r3, c_r5 = calc_stats(ctx_labels) ch_valid, ch_skip, ch_n1, ch_n3, ch_n5, ch_r1, ch_r3, ch_r5 = calc_stats(ctxh_labels) print("\n" + "=" * 80) print("== 3-way 비교 결과 ==") print("=" * 80) print(f"{'':20s} {'bge-m3':>12s} {'+ HyDE':>14s} {'+ Ctx':>13s} {'+ Ctx+HyDE':>14s}") print(f" {'Acc@1':<18s} {b_n1}/{b_valid}={b_r1:.1%} {h_n1}/{h_valid}={h_r1:.1%} {c_n1}/{c_valid}={c_r1:.1%} {ch_n1}/{ch_valid}={ch_r1:.1%}") print(f" {'Acc@3':<18s} {b_n3}/{b_valid}={b_r3:.1%} {h_n3}/{h_valid}={h_r3:.1%} {c_n3}/{c_valid}={c_r3:.1%} {ch_n3}/{ch_valid}={ch_r3:.1%}") print(f" {'Acc@5':<18s} {b_n5}/{b_valid}={b_r5:.1%} {h_n5}/{h_valid}={h_r5:.1%} {c_n5}/{c_valid}={c_r5:.1%} {ch_n5}/{ch_valid}={ch_r5:.1%}") print("=" * 80) # ── 5. DataFrame 구성 ───────────────────────────────────────────────────── rows = [] for i, row in df_base.iterrows(): cr = ctx_results[i] chr = ctxh_results[i] hyde_rank = RANK_MAP.get(hyde_labels[i], -1) ctxh_rank = RANK_MAP.get(ctxh_labels[i], -1) if hyde_labels[i] == "평가제외": change = "제외" elif ctxh_rank > hyde_rank: change = "개선" elif ctxh_rank < hyde_rank: change = "하락" else: change = "동일" bge_tops = [row.get(c, "") for c in bge_top_cols[:5]] hyde_tops = [row.get(c, "") for c in hyde_top_cols[:5]] while len(bge_tops) < 5: bge_tops.append("") while len(hyde_tops) < 5: hyde_tops.append("") rows.append({ "No": row["No"], "쿼리": str(row["쿼리"]).strip(), "정답 메뉴": row["정답 메뉴"] if pd.notna(row.get("정답 메뉴")) else "", # baseline bge-m3 "bge Top1": bge_tops[0], "bge Top2": bge_tops[1], "bge Top3": bge_tops[2], "bge Top4": bge_tops[3], "bge Top5": bge_tops[4], "bge 결과": bge_labels[i], # baseline +HyDE "hyde Top1": hyde_tops[0], "hyde Top2": hyde_tops[1], "hyde Top3": hyde_tops[2], "hyde Top4": hyde_tops[3], "hyde Top5": hyde_tops[4], "hyde 결과": hyde_labels[i], # +Contextual+HyDE (신규) "ctxh Top1": chr["paths"][0], "ctxh Top2": chr["paths"][1], "ctxh Top3": chr["paths"][2], "ctxh Top4": chr["paths"][3], "ctxh Top5": chr["paths"][4], "ctxh 결과": ctxh_labels[i], # 변화 "변화": change, }) df_out = pd.DataFrame(rows) df_out.columns = [ "No", "쿼리", "정답 메뉴", "[bge-m3] Top1", "[bge-m3] Top2", "[bge-m3] Top3", "[bge-m3] Top4", "[bge-m3] Top5", "[bge-m3] 결과", "[+HyDE] Top1", "[+HyDE] Top2", "[+HyDE] Top3", "[+HyDE] Top4", "[+HyDE] Top5", "[+HyDE] 결과", "[+Ctx+HyDE] Top1", "[+Ctx+HyDE] Top2", "[+Ctx+HyDE] Top3", "[+Ctx+HyDE] Top4", "[+Ctx+HyDE] Top5", "[+Ctx+HyDE] 결과", "변화", ] df_out.to_excel(OUT_PATH, sheet_name="시트1", index=False) # ── 6. Excel 스타일링 ────────────────────────────────────────────────────── wb = load_workbook(OUT_PATH) ws = wb["시트1"] col_widths = { "A": 5, "B": 34, "C": 34, "D": 28, "E": 28, "F": 28, "G": 28, "H": 28, "I": 12, "J": 28, "K": 28, "L": 28, "M": 28, "N": 28, "O": 12, "P": 28, "Q": 28, "R": 28, "S": 28, "T": 28, "U": 12, "V": 9, } for col, w in col_widths.items(): ws.column_dimensions[col].width = w ws.row_dimensions[1].height = 28 HDR_BGE = "1F4E79" HDR_HYDE = "1A5276" HDR_CTX = "145A32" # 초록 계열 (Contextual) HDR_ETC = "2C3E50" FILL_MAP = { "Top1 정답": PatternFill("solid", start_color="C6EFCE"), "Top3 정답": PatternFill("solid", start_color="FFEB9C"), "Top5 정답": PatternFill("solid", start_color="FCE4D6"), "오답": PatternFill("solid", start_color="FFC7CE"), "평가제외": PatternFill("solid", start_color="F2F2F2"), } CHANGE_FILL = { "개선": PatternFill("solid", start_color="ABEBC6"), "하락": PatternFill("solid", start_color="F1948A"), "동일": PatternFill("solid", start_color="EBF5FB"), "제외": PatternFill("solid", start_color="F2F2F2"), } CHANGE_FC = {"개선": "1E8449", "하락": "922B21", "동일": "1A5276", "제외": "7F7F7F"} # 헤더 for cell in ws[1]: cl = cell.column_letter if cl in ("A", "B", "C", "V"): bg = HDR_ETC elif cl in ("D", "E", "F", "G", "H", "I"): bg = HDR_BGE elif cl in ("J", "K", "L", "M", "N", "O"): bg = HDR_HYDE else: bg = HDR_CTX cell.fill = PatternFill("solid", start_color=bg) cell.font = Font(bold=True, color="FFFFFF", name="맑은 고딕", size=9) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) # 데이터 행 for row in ws.iter_rows(min_row=2, max_row=ws.max_row): rc = {cell.column_letter: cell for cell in row} bge_lbl = rc.get("I").value if rc.get("I") else "" hyde_lbl = rc.get("O").value if rc.get("O") else "" ctxh_lbl = rc.get("U").value if rc.get("U") else "" chg_val = rc.get("V").value if rc.get("V") else "" for cell in row: cell.font = Font(name="맑은 고딕", size=8) cell.alignment = Alignment(wrap_text=True, vertical="center") for col_letter, lbl in [("I", bge_lbl), ("O", hyde_lbl), ("U", ctxh_lbl)]: if rc.get(col_letter) and lbl: rc[col_letter].fill = FILL_MAP.get(lbl, FILL_MAP["평가제외"]) rc[col_letter].font = Font(name="맑은 고딕", size=8, bold=True) rc[col_letter].alignment = Alignment(horizontal="center", vertical="center") if rc.get("V") and chg_val: rc["V"].fill = CHANGE_FILL.get(chg_val, CHANGE_FILL["동일"]) rc["V"].font = Font(name="맑은 고딕", size=8, bold=True, color=CHANGE_FC.get(chg_val, "000000")) rc["V"].alignment = Alignment(horizontal="center", vertical="center") if rc.get("A"): rc["A"].alignment = Alignment(horizontal="center", vertical="center") # 요약 행 ws.append([]) ws.append([ "", "【요약】", f"유효 {b_valid}개 / 제외 {b_skip}개", f"Acc@1: {b_r1:.1%}", f"Acc@3: {b_r3:.1%}", f"Acc@5: {b_r5:.1%}", "", "", "", f"Acc@1: {h_r1:.1%}", f"Acc@3: {h_r3:.1%}", f"Acc@5: {h_r5:.1%}", "", "", "", f"Acc@1: {ch_r1:.1%}", f"Acc@3: {ch_r3:.1%}", f"Acc@5: {ch_r5:.1%}", "", "", "", "", ]) sum_fill = PatternFill("solid", start_color="D9E1F2") for cell in ws[ws.max_row]: cell.fill = sum_fill cell.font = Font(bold=True, name="맑은 고딕", size=10) cell.alignment = Alignment(horizontal="center", vertical="center") # 개선 행 ws.append([ "", "【개선폭 vs bge-m3 baseline】", "", "+0.0%", "+0.0%", "+0.0%", "", "", "", f"+{h_r1 - b_r1:.1%}", f"+{h_r3 - b_r3:.1%}", f"+{h_r5 - b_r5:.1%}", "", "", "", f"+{ch_r1 - b_r1:.1%}", f"+{ch_r3 - b_r3:.1%}", f"+{ch_r5 - b_r5:.1%}", "", "", "", "", ]) delta_fill = PatternFill("solid", start_color="E2EFDA") for cell in ws[ws.max_row]: cell.fill = delta_fill cell.font = Font(bold=True, name="맑은 고딕", size=10, color="375623") cell.alignment = Alignment(horizontal="center", vertical="center") wb.save(OUT_PATH) print(f"\n[19_triple] 저장 완료: {OUT_PATH}") print(f" bge-m3 baseline: Acc@1={b_r1:.1%} Acc@3={b_r3:.1%} Acc@5={b_r5:.1%}") print(f" +HyDE baseline: Acc@1={h_r1:.1%} Acc@3={h_r3:.1%} Acc@5={h_r5:.1%}") print(f" +Contextual+HyDE: Acc@1={ch_r1:.1%} Acc@3={ch_r3:.1%} Acc@5={ch_r5:.1%}") print(f" 총 개선 (bge→Ctx+HyDE): Acc@1 +{ch_r1-b_r1:.1%} Acc@3 +{ch_r3-b_r3:.1%} Acc@5 +{ch_r5-b_r5:.1%}") if __name__ == "__main__": main()