how/script/analysis/analyze_model_comparison.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
分析模型对比结果并导出到Excel

同时分析两种实现：
1. cache/text_embedding - 向量模型实现（text2vec）
2. cache/semantic_similarity - LLM实现（GPT/Claude等）

生成Excel报告，对比不同实现的效果差异。
"""

import json
import sys
from pathlib import Path
from typing import Dict, List, Tuple
import pandas as pd
from datetime import datetime

# 添加项目根目录到路径
project_root = Path(__file__).parent.parent.parent
sys.path.insert(0, str(project_root))


def extract_results_from_cache(
    cache_dir: str,
    model_type: str
) -> Dict[Tuple[str, str], Dict]:
    """
    从缓存目录提取结果

    Args:
        cache_dir: 缓存目录路径
        model_type: 模型类型（"text_embedding" 或 "semantic_similarity"）

    Returns:
        结果字典，键为 (phrase_a, phrase_b) 元组，值为结果数据
    """
    cache_path = Path(cache_dir)

    if not cache_path.exists():
        print(f"缓存目录不存在: {cache_dir}")
        return {}

    results = {}
    cache_files = list(cache_path.glob("*.json"))

    print(f"扫描 {model_type} 缓存: {len(cache_files)} 个文件")

    for cache_file in cache_files:
        try:
            with open(cache_file, 'r', encoding='utf-8') as f:
                data = json.load(f)

            # 提取输入和输出
            input_data = data.get("input", {})
            output_data = data.get("output", {})

            phrase_a = input_data.get("phrase_a")
            phrase_b = input_data.get("phrase_b")

            if phrase_a and phrase_b:
                # 统一处理两种缓存格式
                if model_type == "text_embedding":
                    # text_embedding 的输出直接是 {"说明": "...", "相似度": 0.xx}
                    result = {
                        "相似度": output_data.get("相似度"),
                        "说明": output_data.get("说明", ""),
                        "模型": input_data.get("model_name", "unknown")
                    }
                elif model_type == "semantic_similarity":
                    # semantic_similarity 的输出在 output.parsed 中
                    parsed = output_data.get("parsed", output_data)
                    result = {
                        "相似度": parsed.get("相似度"),
                        "说明": parsed.get("说明", ""),
                        "模型": input_data.get("model_name", "LLM")
                    }
                else:
                    continue

                # 使用原始顺序的元组作为键（保持 phrase_a 和 phrase_b 的原始顺序）
                pair_key = (phrase_a, phrase_b)

                # 如果同一对短语有多个缓存（不同模型），保存为列表
                if pair_key not in results:
                    results[pair_key] = []

                results[pair_key].append({
                    "phrase_a": phrase_a,
                    "phrase_b": phrase_b,
                    "相似度": result["相似度"],
                    "说明": result["说明"],
                    "模型": result["模型"]
                })

        except (json.JSONDecodeError, IOError, KeyError) as e:
            print(f"  读取缓存文件失败: {cache_file.name} - {e}")
            continue

    return results


def merge_all_results(
    text_embedding_results: Dict[Tuple[str, str], List[Dict]],
    semantic_similarity_results: Dict[Tuple[str, str], List[Dict]]
) -> List[Dict]:
    """
    合并所有结果

    Args:
        text_embedding_results: 向量模型结果
        semantic_similarity_results: LLM模型结果

    Returns:
        合并后的结果列表
    """
    # 获取所有唯一的短语对
    all_pairs = set(text_embedding_results.keys()) | set(semantic_similarity_results.keys())

    merged = []

    for pair_key in all_pairs:
        phrase_a, phrase_b = pair_key

        row = {
            "短语A": phrase_a,
            "短语B": phrase_b,
        }

        # 添加向量模型结果
        if pair_key in text_embedding_results:
            for result in text_embedding_results[pair_key]:
                model_name = result["模型"].split('/')[-1]  # 提取模型简称
                row[f"向量_{model_name}_相似度"] = result["相似度"]
                row[f"向量_{model_name}_说明"] = result["说明"]

        # 添加LLM模型结果
        if pair_key in semantic_similarity_results:
            for result in semantic_similarity_results[pair_key]:
                model_name = result["模型"].split('/')[-1]  # 提取模型简称
                row[f"LLM_{model_name}_相似度"] = result["相似度"]
                row[f"LLM_{model_name}_说明"] = result["说明"]

        merged.append(row)

    return merged


def create_comparison_dataframe(merged_results: List[Dict]) -> pd.DataFrame:
    """
    创建模型对比数据框

    Args:
        merged_results: 合并后的结果列表

    Returns:
        包含所有模型对比的DataFrame
    """
    # 直接从合并结果创建DataFrame
    df = pd.DataFrame(merged_results)

    # 添加序号
    df.insert(0, "序号", range(1, len(df) + 1))

    # 只保留相似度列，移除说明列
    columns_to_keep = ["序号", "短语A", "短语B"]
    similarity_cols = [col for col in df.columns if col.endswith("_相似度")]
    columns_to_keep.extend(similarity_cols)

    # 过滤出要保留的列
    df = df[[col for col in columns_to_keep if col in df.columns]]

    # 计算相似度差异
    for idx, row in df.iterrows():
        similarities = []
        for col in similarity_cols:
            if col in df.columns:
                sim = row[col]
                if sim is not None and isinstance(sim, (int, float)):
                    similarities.append(sim)

        if len(similarities) > 1:
            df.at[idx, "相似度_差异"] = max(similarities) - min(similarities)
        elif len(similarities) == 1:
            df.at[idx, "相似度_差异"] = 0
        else:
            df.at[idx, "相似度_差异"] = None

    # 移动相似度差异列到最后
    if "相似度_差异" in df.columns:
        cols = [col for col in df.columns if col != "相似度_差异"]
        cols.append("相似度_差异")
        df = df[cols]

    return df


def export_to_excel(
    comparison_df: pd.DataFrame,
    output_file: str
) -> None:
    """
    导出到Excel文件

    Args:
        comparison_df: 对比数据框
        output_file: 输出Excel文件路径
    """
    output_path = Path(output_file)
    output_path.parent.mkdir(parents=True, exist_ok=True)

    # 创建Excel写入器
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        # 写入完整对比数据
        comparison_df.to_excel(writer, sheet_name='模型对比', index=False)

        # 格式化工作表
        workbook = writer.book
        ws = writer.sheets['模型对比']

        # 自动调整列宽
        for col in ws.columns:
            max_length = 0
            column = col[0].column_letter
            for cell in col:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = min(max_length + 2, 50)
            ws.column_dimensions[column].width = adjusted_width

    print(f"Excel报告已导出到: {output_file}")


def main():
    """主函数"""
    # 配置参数
    text_embedding_cache = "cache/text_embedding"
    semantic_similarity_cache = "cache/semantic_similarity"
    output_file = f"data/model_comparison_report_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"

    print("=" * 60)
    print("模型对比结果分析（向量模型 vs LLM模型）")
    print("=" * 60)

    # 步骤 1: 从缓存提取结果
    print("\n步骤 1: 从缓存提取结果...")
    print(f"  - 向量模型缓存: {text_embedding_cache}")
    text_embedding_results = extract_results_from_cache(text_embedding_cache, "text_embedding")
    print(f"    提取到 {len(text_embedding_results)} 个唯一短语对")

    print(f"  - LLM模型缓存: {semantic_similarity_cache}")
    semantic_similarity_results = extract_results_from_cache(semantic_similarity_cache, "semantic_similarity")
    print(f"    提取到 {len(semantic_similarity_results)} 个唯一短语对")

    if not text_embedding_results and not semantic_similarity_results:
        print("\n错误: 未找到任何缓存数据")
        print("请先运行以下脚本生成缓存:")
        print("  - match_inspiration_features.py (生成 text_embedding 缓存)")
        print("  - test_all_models.py (生成多模型缓存)")
        return

    # 步骤 2: 合并结果
    print("\n步骤 2: 合并所有模型结果...")
    merged_results = merge_all_results(text_embedding_results, semantic_similarity_results)
    print(f"合并后共 {len(merged_results)} 个测试用例")

    # 步骤 3: 创建对比数据框
    print("\n步骤 3: 创建对比数据框...")
    comparison_df = create_comparison_dataframe(merged_results)
    print(f"对比数据框创建完成，共 {len(comparison_df)} 行")

    # 显示列信息
    similarity_cols = [col for col in comparison_df.columns if col.endswith("_相似度")]
    print(f"包含 {len(similarity_cols)} 个模型的相似度数据:")
    for col in similarity_cols:
        print(f"  - {col}")

    # 步骤 4: 导出到Excel
    print("\n步骤 4: 导出到Excel...")
    export_to_excel(comparison_df, output_file)

    print("\n" + "=" * 60)
    print("分析完成!")
    print(f"报告文件: {output_file}")
    print("=" * 60)


if __name__ == "__main__":
    main()