simonkoson
f37679530a
收视分析页面完整实现：指标卡（含四档动画）、走势折线图（dataZoom滑块+确认按钮）、 季度/编导/题材对比（双列布局）、双引擎象限图（题材热度×叙事结构散点）。 导入25期真实收视数据及AI标签，修复侧边栏fixed定位和滚轮冲突。 Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
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 '-'}")
|