如何在Python循环中将多模型评估结果写入Excel单工作表

以下是修改后的完整代码，实现将每个模型的评估指标、分类报告、Top5特征写入同一工作表，按列排列且模型间保留3个空白列：

import pandas as pd
from sklearn.metrics import accuracy_score, precision_score, recall_score, cohen_kappa_score, roc_auc_score, classification_report
from sklearn.ensemble import ExtraTreesClassifier, RandomForestClassifier
import lightgbm as lgb
import xgboost as xgb
from catboost import CatBoostClassifier

classifiers = [
    ['ExtraTreesClassifier :', ExtraTreesClassifier(min_samples_split=2, random_state=2)],
    ['LGBMClassifier : ', lgb.LGBMClassifier(n_estimators=400, max_depth=15, learning_rate=1)],
    ['XGB :', xgb.XGBClassifier(tree_method="hist", random_state=2, learning_rate=1)],
    ['Random Forest : ', RandomForestClassifier(n_estimators=400, min_samples_split=2, random_state=2)],
    ['CatBoost :', CatBoostClassifier(random_seed=22, learning_rate=1, verbose=0)],
]

# 初始化Excel写入器和工作表
writer = pd.ExcelWriter('top pros.xlsx', engine='xlsxwriter')
workbook = writer.book
worksheet = workbook.add_worksheet('模型评估汇总')

start_col = 0  # 跟踪当前写入的起始列位置

for name, classifier in classifiers:
    print(f"正在处理模型: {name}")
    Model = classifier
    Model.fit(X_train, y_train)
    
    y_predrf = Model.predict(X_test)
    
    # 计算核心评估指标
    acc = accuracy_score(y_test, y_predrf)
    prec = precision_score(y_test, y_predrf)
    rec = recall_score(y_test, y_predrf)
    kappa = cohen_kappa_score(y_test, y_predrf)
    auc = roc_auc_score(y_test, y_predrf)
    
    # 整理指标为键值对
    metrics = {
        "Accuracy Test": f"{acc:.4f}",
        "Precision Test": f"{prec:.4f}",
        "Recall Test": f"{rec:.4f}",
        "Cohen Kappa": f"{kappa:.4f}",
        "AUC": f"{auc:.4f}"
    }
    
    # 生成分类报告DataFrame
    class_report_df = pd.DataFrame(classification_report(y_test, y_predrf, output_dict=True)).transpose()
    
    # 获取Top5重要特征
    feature_importances = Model.feature_importances_
    sorted_features = pd.Series(feature_importances, index=X.columns).sort_values(ascending=False)
    top5_features = sorted_features.index[:5].tolist()
    
    # -------------------------- 写入Excel内容 --------------------------
    # 1. 写入模型名称
    worksheet.write(0, start_col, name)
    
    # 2. 写入评估指标（左列是指标名，右列是数值）
    current_row = 2
    for metric_name, metric_val in metrics.items():
        worksheet.write(current_row, start_col, metric_name)
        worksheet.write(current_row, start_col + 1, metric_val)
        current_row += 1
    
    # 3. 写入分类报告
    worksheet.write(current_row, start_col, "分类报告")
    current_row += 1
    # 写入报告表头
    for col_idx, col_name in enumerate(class_report_df.columns):
        worksheet.write(current_row, start_col + col_idx, col_name)
    current_row += 1
    # 写入报告内容（包含行索引）
    for row_idx, (index_val, row_data) in enumerate(class_report_df.iterrows()):
        worksheet.write(current_row + row_idx, start_col, index_val)
        for col_idx, val in enumerate(row_data):
            # 数值型数据保留4位小数，非数值直接写入
            cell_val = f"{val:.4f}" if isinstance(val, float) else val
            worksheet.write(current_row + row_idx, start_col + col_idx + 1, cell_val)
    current_row += len(class_report_df) + 1  # 空一行分隔
    
    # 4. 写入Top5特征
    worksheet.write(current_row, start_col, "Top5特征")
    current_row += 1
    for idx, feat in enumerate(top5_features):
        worksheet.write(current_row + idx, start_col, feat)
    
    # 更新起始列：当前模型占用的最大列数 + 3个空白列
    max_cols = max(2, len(class_report_df.columns) + 1)  # 指标用2列，分类报告用列数+1（索引列）
    start_col += max_cols + 3

# 保存并关闭Excel写入器
writer.close()
print("所有模型评估结果已成功写入Excel！")

关键修改说明 #

1. 精确控制写入位置：改用xlsxwriter的workbook和worksheet对象直接写入，替代原代码中to_excel的工作表分散写入方式，实现同一工作表的列排列。
2. 列偏移管理：用start_col变量跟踪每个模型的起始写入列，每个模型完成后偏移max_cols +3，确保模型间保留3个空白列。
3. 格式统一：所有数值型评估结果统一保留4位小数，分类报告完整保留原结构，Top5特征清晰列出。
4. bug修复：修正原代码中ExtraTreeClassifier的拼写错误（正确应为ExtraTreesClassifier），并给CatBoost添加verbose=0参数避免训练日志冗余输出。

内容的提问来源于stack exchange，提问作者Divyansh Kumar Singh