107 lines
3.7 KiB
Python
107 lines
3.7 KiB
Python
|
"""
|
|||
|
|
修复 AI 标签映射：ground-truth ep编号 ≠ Excel 播出期号
|
|||
|
|
用标题模糊匹配重新关联正确的 AI 标签
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
import json
|
|||
|
|
import sys
|
|||
|
|
import os
|
|||
|
|
from difflib import SequenceMatcher
|
|||
|
|
|
|||
|
|
sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..', 'backend'))
|
|||
|
|
|
|||
|
|
from app.db.session import get_session
|
|||
|
|
from app.models.episode import Episode
|
|||
|
|
from app.models.user import User
|
|||
|
|
from sqlmodel import select
|
|||
|
|
|
|||
|
|
project_root = os.path.join(os.path.dirname(__file__), '..')
|
|||
|
|
|
|||
|
|
# 读 ground-truth
|
|||
|
|
with open(os.path.join(project_root, 'ai-labeling', 'benchmark-set', 'ground-truth.json'), 'r', encoding='utf-8') as f:
|
|||
|
|
gt_data = json.load(f)
|
|||
|
|
|
|||
|
|
gt_episodes = gt_data['episodes']
|
|||
|
|
|
|||
|
|
# 读数据库
|
|||
|
|
session = next(get_session())
|
|||
|
|
db_episodes = session.exec(select(Episode).order_by(Episode.episode_number)).all()
|
|||
|
|
|
|||
|
|
print(f"DB episodes: {len(db_episodes)}")
|
|||
|
|
print(f"GT episodes: {len(gt_episodes)}")
|
|||
|
|
print()
|
|||
|
|
|
|||
|
|
# ── 标题相似度匹配 ──
|
|||
|
|
def title_similarity(a, b):
|
|||
|
|
"""计算两个标题的相似度"""
|
|||
|
|
# 清理标题
|
|||
|
|
clean_a = a.replace('"', '').replace('"', '').replace("'", '').replace('——', '').replace('：', '').replace(' ', '').replace('《', '').replace('》', '')
|
|||
|
|
clean_b = b.replace('"', '').replace('"', '').replace("'", '').replace('——', '').replace('：', '').replace(' ', '').replace('《', '').replace('》', '')
|
|||
|
|
return SequenceMatcher(None, clean_a, clean_b).ratio()
|
|||
|
|
|
|||
|
|
# 对每个 DB episode，找最佳匹配的 GT episode
|
|||
|
|
matched = []
|
|||
|
|
used_gt = set()
|
|||
|
|
|
|||
|
|
for db_ep in db_episodes:
|
|||
|
|
best_score = 0
|
|||
|
|
best_gt = None
|
|||
|
|
|
|||
|
|
for i, gt_ep in enumerate(gt_episodes):
|
|||
|
|
if i in used_gt:
|
|||
|
|
continue
|
|||
|
|
|
|||
|
|
gt_title = gt_ep.get('title', '')
|
|||
|
|
score = title_similarity(db_ep.program_name, gt_title)
|
|||
|
|
|
|||
|
|
# 也检查份额是否匹配（辅助判断）
|
|||
|
|
gt_share = gt_ep.get('share')
|
|||
|
|
share_match = (gt_share is not None and db_ep.audience_share is not None
|
|||
|
|
and abs(float(gt_share) - float(db_ep.audience_share)) < 0.01)
|
|||
|
|
|
|||
|
|
# 份额匹配加分
|
|||
|
|
if share_match:
|
|||
|
|
score += 0.3
|
|||
|
|
|
|||
|
|
if score > best_score:
|
|||
|
|
best_score = score
|
|||
|
|
best_gt = (i, gt_ep)
|
|||
|
|
|
|||
|
|
if best_gt and best_score > 0.3:
|
|||
|
|
used_gt.add(best_gt[0])
|
|||
|
|
matched.append((db_ep, best_gt[1], best_score))
|
|||
|
|
status = 'OK' if best_score > 0.5 else 'WEAK'
|
|||
|
|
print(f"[{status}] DB ep{db_ep.episode_number:02d} \"{db_ep.program_name[:15]}\" -> GT ep{best_gt[1]['ep']:02d} \"{best_gt[1]['title'][:15]}\" (score={best_score:.2f})")
|
|||
|
|
else:
|
|||
|
|
print(f"[MISS] DB ep{db_ep.episode_number:02d} \"{db_ep.program_name[:15]}\" -> NO MATCH")
|
|||
|
|
matched.append((db_ep, None, 0))
|
|||
|
|
|
|||
|
|
print(f"\nMatched: {sum(1 for _,gt,_ in matched if gt is not None)}/{len(db_episodes)}")
|
|||
|
|
|
|||
|
|
# ── 确认后更新 ──
|
|||
|
|
print("\n--- Updating AI labels ---")
|
|||
|
|
updated = 0
|
|||
|
|
for db_ep, gt_ep, score in matched:
|
|||
|
|
if gt_ep is None:
|
|||
|
|
continue
|
|||
|
|
|
|||
|
|
db_ep.program_format = gt_ep.get('program_format')
|
|||
|
|
db_ep.equipment_domain = gt_ep.get('equipment_domain')
|
|||
|
|
db_ep.scene_tags = gt_ep.get('scene_tags')
|
|||
|
|
db_ep.tech_tags = gt_ep.get('tech_tags')
|
|||
|
|
db_ep.narrative_structure = gt_ep.get('narrative_structure')
|
|||
|
|
db_ep.opening_hook = gt_ep.get('opening_hook')
|
|||
|
|
db_ep.ai_label_confidence = 'reviewed'
|
|||
|
|
|
|||
|
|
session.add(db_ep)
|
|||
|
|
updated += 1
|
|||
|
|
|
|||
|
|
session.commit()
|
|||
|
|
print(f"Updated {updated} episodes")
|
|||
|
|
|
|||
|
|
# ── 验证 ──
|
|||
|
|
print("\n--- Verification ---")
|
|||
|
|
db_episodes = session.exec(select(Episode).order_by(Episode.episode_number)).all()
|
|||
|
|
for ep in db_episodes:
|
|||
|
|
print(f"ep{ep.episode_number:02d} | {ep.program_name[:18]:18s} | {ep.program_format or '-':8s} | {ep.narrative_structure or '-':6s} | {ep.opening_hook or '-'}")
|