"""Export all experiment results to Excel.""" import os, json, numpy as np from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter PROJECT_DIR='/home/user/goat' os.chdir(PROJECT_DIR) wb = Workbook() header_font = Font(bold=True, color='FFFFFF', size=11) header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid') green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid') yellow_fill = PatternFill(start_color='FFEB9C', end_color='FFEB9C', fill_type='solid') red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid') # ── Sheet 1: Training Validation ── ws1 = wb.active ws1.title = "Training Validation" headers = [ ('#', '序号', 5), ('Experiment', '实验名称', 35), ('Best Ep', '最佳Epoch', 8), ('Total Ep', '总Epoch', 8), ('mAP50 ↑', '越高越好', 10), ('mAP50-95 ↑', '越高越好', 12), ('Precision ↑', '越高越好', 10), ('Recall ↑', '越高越好', 10), ('Box Loss ↓', '越低越好', 10), ('Cls Loss ↓', '越低越好', 10), ('DFL Loss ↓', '越低越好', 10), ('Val BoxL ↓', '越低越好', 12), ('Val ClsL ↓', '越低越好', 12), ('Val DflL ↓', '越低越好', 12), ('Category', '类别', 14), ('Note', '备注', 25), ] for col, (key, desc, w) in enumerate(headers, 1): cell = ws1.cell(row=1, column=col, value=desc) cell.font = header_font cell.fill = header_fill cell.alignment = Alignment(horizontal='center', wrap_text=True) ws1.column_dimensions[get_column_letter(col)].width = w exp = 'runs/detect/Detection_experiments' results = [] for ename in sorted(os.listdir(exp)): csv_path = f'{exp}/{ename}/results.csv' if not os.path.exists(csv_path): continue with open(csv_path) as f: lines = f.readlines() if len(lines) < 2: continue h = [s.strip() for s in lines[0].split(',')] try: idxs = {col: h.index(col) for col in h} except: continue i5095 = h.index('metrics/mAP50-95(B)') best_idx = 0; best_v = 0 for i, line in enumerate(lines[1:]): try: v = float(line.strip().split(',')[i5095]) if v > best_v: best_v = v; best_idx = i + 1 except: pass cols = lines[best_idx].strip().split(',') def g(col): try: if col in idxs: return float(cols[idxs[col]]) return '-' except: return '-' name = ename n = name.lower() if 'seed' in n: cat = 'seed' elif 'distilled' in n: cat = 'distilled' elif 'whatif' in n: cat = 'whatif' elif 'eastleft' in n: cat = 'eastleft' elif 'westright' in n: cat = 'westright' elif 'yolo11m' in n: cat = 'yolo11m' elif 'yolo11n' in n: cat = 'yolo11n' elif 'gmm' in n: cat = 'GMM' elif 'selfchallenge' in n: cat = 'self-challenge' elif 'mask_refined' in n: cat = 'mask' elif 'expanded' in n: cat = 'expanded' elif 'final_v3' in n: cat = 'label_v3' elif 'sc3' in n: cat = 'SC3' elif 'p2_s' in n: cat = 'P2_head' elif 'baseline' in n: cat = 'baseline' else: cat = 'other' mAP_val = g('metrics/mAP50-95(B)') note = '' if isinstance(mAP_val, float): if mAP_val >= 0.513: note = 'BEST' elif mAP_val >= 0.511: note = 'GOOD' elif mAP_val < 0.50: note = 'FAIL' results.append(( ename, best_idx, len(lines)-1, g('metrics/mAP50(B)'), mAP_val, g('metrics/precision(B)'), g('metrics/recall(B)'), g('train/box_loss'), g('train/cls_loss'), g('train/dfl_loss'), g('val/box_loss'), g('val/cls_loss'), g('val/dfl_loss'), cat, note, )) results.sort(key=lambda x: x[4] if isinstance(x[4], (int, float)) else 0, reverse=True) for i, r in enumerate(results): row = i + 2 ws1.cell(row=row, column=1, value=i+1) for j, val in enumerate(r): cell = ws1.cell(row=row, column=j+2, value=val if val != '-' else '-') if j in [3, 4, 5, 6] and isinstance(val, float): cell.number_format = '0.0000' if val >= 0.96: cell.fill = green_fill elif val >= 0.51 and j == 4: cell.fill = yellow_fill elif j in [7, 8, 9, 10, 11, 12] and isinstance(val, float): cell.number_format = '0.0000' # ── Sheet 2: WBF Ensemble ── ws2 = wb.create_sheet("WBF Ensemble") wbf_headers = [ ('Method', '方法', 35), ('mAP50-95 ↑', '越高越好', 14), ('IoU@75 ↑', '越高越好', 12), ('Delta', '相对基线提升', 16), ('N Models', '模型数', 10), ('N Sources', '预测源数', 10), ('Note', '备注', 30), ] for col, (key, desc, w) in enumerate(wbf_headers, 1): cell = ws2.cell(row=1, column=col, value=desc) cell.font = header_font; cell.fill = header_fill cell.alignment = Alignment(horizontal='center', wrap_text=True) ws2.column_dimensions[get_column_letter(col)].width = w wbf_data = [ ('v6_1 Single (训练验证)', 0.5125, 0.4900, '-', 1, 1, '训练验证标准基准'), ('v6_1 Single (eval框架)', 0.5521, 0.5789, '—', 1, 1, 'Eval框架基准'), ('1 Model x 3 Scales WBF', 0.5626, 0.6003, '+0.010', 1, 3, '单模型多尺度'), ('5 Checkpoint Ensemble', 0.5660, None, '+0.014', 1, 5, '单模型5cp WBF'), ('1m Kitchen Sink (12x)', 0.5691, 0.6055, '+0.017', 1, 12, '单模型×12变体'), ('5 Models x 3 Scales WBF', 0.5776, 0.6169, '+0.025', 5, 15, ''), ('Kitchen Sink 60x (5m)', 0.5816, 0.6203, '+0.030', 5, 60, '5模型×12变体'), ('KS 7 Models', 0.5872, 0.6215, '+0.035', 7, 84, '含yolo11n'), ('KS 10 Models', 0.5880, 0.6266, '+0.036', 10, 120, '10模型×12变体'), ('KS 13 Models BEST', 0.5888, 0.6266, '+0.037', 13, 156, '当前最佳WBF'), ('Mask Weighted WBF', 0.5690, None, '+0.017', 1, 12, '掩码加权→微弱'), ('Prior WBF', 0.5689, None, '-0.0002', 1, 12, '位置先验→无效'), ] for i, row_data in enumerate(wbf_data): for j, val in enumerate(row_data): cell = ws2.cell(row=i+2, column=j+1, value=val if val is not None else '-') if j == 1 and isinstance(val, float): cell.number_format = '0.0000' if val >= 0.58: cell.fill = green_fill elif j == 2 and isinstance(val, float): cell.number_format = '0.0000' # ── Sheet 3: Per-Camera ── ws3 = wb.create_sheet("Per-Camera") cam_headers = [ ('Method', '方法', 30), ('EastLeft ↑', '东左(越高越好)', 14), ('EastRight ↑', '东右(越高越好)', 14), ('WestLeft ↑', '西左(越高越好)', 14), ('WestRight ↑', '西右(越高越好)', 14), ('Overall ↑', '总体(越高越好)', 12), ('Note', '备注', 25), ] for col, (key, desc, w) in enumerate(cam_headers, 1): cell = ws3.cell(row=1, column=col, value=desc) cell.font = header_font; cell.fill = header_fill cell.alignment = Alignment(horizontal='center', wrap_text=True) ws3.column_dimensions[get_column_letter(col)].width = w cam_data = [ ('v6_1 Single', 0.5240, 0.6082, 0.5465, 0.5399, 0.5521, '基准'), ('v19_eastleft (特化)', 0.5283, 0.6049, 0.5469, 0.5396, 0.5526, 'EastLeft +0.0043'), ('KS 7 Models', 0.5602, 0.6299, 0.5772, 0.5690, 0.5820, 'WBF全面提升'), ('KS 10 Models', None, None, None, None, 0.5880, ''), ('KS 13 Models BEST', None, None, None, None, 0.5888, '当前最佳'), ] for i, row_data in enumerate(cam_data): for j, val in enumerate(row_data): cell = ws3.cell(row=i+2, column=j+1, value=val if val is not None else '-') if isinstance(val, float): cell.number_format = '0.0000' # ── Sheet 4: Idea Progress ── ws4 = wb.create_sheet("Idea Progress") idea_headers = [('Idea', '思路', 35), ('Status', '状态', 12), ('Best Result', '最佳结果', 18), ('Note', '备注', 40)] for col, (key, desc, w) in enumerate(idea_headers, 1): cell = ws4.cell(row=1, column=col, value=desc) cell.font = header_font; cell.fill = header_fill ws4.column_dimensions[get_column_letter(col)].width = w ideas = [ ('WBF Multi-Model Ensemble', 'WORKS', '+0.037 (13m)', '最有效路径'), ('Multi-Scale + Aug WBF', 'WORKS', '+0.010', '免费推理增强'), ('Per-Camera Adapt WBF', 'WORKS', 'EastLeft +0.036', '逐机位阈值+尺度'), ('Per-Camera Specialized', 'WORKS', 'Best single 0.5134', 'EastLeft oversampling'), ('Cross-Validation', 'WORKS', '0.552 +/-0.011', '确认真实mAP'), ('Checkpoint Ensemble', 'WORKS', '+0.014', '单模型5cp免费'), ('Knowledge Distillation', 'DEAD', '0.5018', 'TTA自蒸馏退化'), ('What-If Augmentation', 'DEAD', '0.5067', '亚像素增强'), ('BRN Boundary Refinement', 'DEAD', '退化', '合成噪声不匹配'), ('Model Soup', 'DEAD', '崩溃', 'BN不兼容'), ('Snake/Edge Refinement', 'DEAD', '退化', '山羊毛边界模糊'), ('GWD/WIoU/InnerIoU Loss', 'DEAD', '持平/退化', '改不动天花板'), ('Label Refine v2/v3', 'DEAD', '退化', '引入新偏差'), ('Auto-Labeling v13', 'DEAD', '0.5094', '伪标签上限=老师'), ('Mask-based Label Refine', 'DEAD', '0.4927', '掩码质量不够'), ('Position-Size Prior', 'DEAD', '-0.0002', '模型已校准'), ('Self-Challenge Training', 'RUNNING', '0.5084', '聚焦边界案例'), ('GMM Wasserstein Loss', 'RUNNING', '0.4925', '多高斯建模山羊'), ('WestRight Specialized', 'RUNNING', '0.4435', '刚启动epoch5'), ('Background Diff Segment', 'READY', '掩码已生成', '待训分割头'), ('Attention Probe', 'TODO', '-', '内部特征定位'), ('Contrastive Cross-Camera', 'TODO', '-', '跨机位不变特征'), ('Iterative Denoising Detect', 'TODO', '-', '多轮渐进精修'), ('Orthogonal Error Experts', 'TODO', '-', '刻意制造互补'), ('Stereo Geometry', 'TODO', '-', '双机位立体约束'), ('Iterative Consensus v4', 'TODO', '-', '打破标注天花板'), ] for i, (idea, status, result, note) in enumerate(ideas): for j, val in enumerate([idea, status, result, note]): cell = ws4.cell(row=i+2, column=j+1, value=val) if status == 'WORKS': cell.fill = green_fill elif status == 'DEAD': cell.fill = red_fill elif status == 'RUNNING': cell.fill = yellow_fill # ── Sheet 5: Legend ── ws5 = wb.create_sheet("Legend") ws5.column_dimensions['A'].width = 20 ws5.column_dimensions['B'].width = 50 ws5.cell(row=1, column=1, value='指标').font = Font(bold=True) ws5.cell(row=1, column=2, value='说明').font = Font(bold=True) legend = [ ('mAP50', 'IoU=0.5时的平均精度,衡量检测能力. 越高越好. 当前最佳: 0.9616'), ('mAP50-95', 'IoU=0.5到0.95的平均精度,衡量定位精度. 越高越好. 当前单模型最佳: 0.5134, WBF最佳: 0.5888'), ('Precision', '预测框中真正是羊的比例. 越高越好.'), ('Recall', '真羊中被检出的比例. 越高越好.'), ('Box Loss', '训练时边界框回归损失. 越低越好. 反映模型画框的准确度.'), ('Cls Loss', '训练时分类损失. 越低越好. 反映模型分辨羊/背景的能力.'), ('DFL Loss', 'Distribution Focal Loss. 越低越好. 反映框边缘分布的精确度.'), ('Val Box/Cls/DFL Loss', '验证集上的对应损失. 越低越好. 训练损失高+验证损失高=欠拟合, 训练低+验证高=过拟合.'), ('WBF', 'Weighted Box Fusion: 多模型多尺度预测的加权融合.'), ('IoU@75', 'IoU阈值0.75时的召回率. 越高越好. 反映高精度定位能力.'), ('★ 两个mAP口径', '训练验证mAP(0.5125)≠ Eval框架mAP(0.5521). 前者是ultralytics批处理评估,后者是单图推理评估.'), ] for i, (k, v) in enumerate(legend): ws5.cell(row=i+3, column=1, value=k) ws5.cell(row=i+3, column=2, value=v) path = 'logs/DairyGoat_All_Results.xlsx' wb.save(path) print(f'Saved: {path}') print(f'Sheets: {wb.sheetnames}')