循环中使用pd.ExcelWriter仅输出首个文件？请求排查与修正

现有文件目录 #

D:\Development\AllDataSet\Data\2020\ER       ['50K_PreProcessed.csv']
D:\Development\AllDataSet\Data\2020\PMV      ['50K_PreProcessed.csv']
D:\Development\AllDataSet\Data\2020\PNY      ['50K_PreProcessed.csv']
D:\Development\AllDataSet\Data\2021\ER       ['50K_PreProcessed.csv']
D:\Development\AllDataSet\Data\2021\PMV      ['50K_PreProcessed.csv']
D:\Development\AllDataSet\Data\2021\PNY      ['50K_PreProcessed.csv']
D:\Development\AllDataSet\Data\2022_2023\ER  ['50K_PreProcessed.csv']
D:\Development\AllDataSet\Data\2022_2023\PMV ['50K_PreProcessed.csv']
D:\Development\AllDataSet\Data\2022_2023\PNY ['50K_PreProcessed.csv']

代码实现 #

writer = pd.ExcelWriter('Allpros.xlsx', engine='xlsxwriter')
# num = 1

classifiers = [
    ['ExtraTreesClassifier :', ExtraTreeClassifier(min_samples_split=2, random_state = 2)],
    ['LGBMClassifier : ', LGBMClassifier(n_estimators = 400, max_depth=15,learning_rate=1)],
    ['XGB :', XGBClassifier(tree_method = "hist", random_state= 2, learning_rate= 1)],
]

for root,dirs,files in os.walk(filepath):
    for i in files:
        if i.endswith('PreProcessed.csv'):
            uio = f'{root}/{i}'
            year = root.rsplit('\\',2, )[1]
            strategy = root.rsplit('\\',2, )[2]
            print(root,files)
            df = pd.read_csv(uio,usecols = col, nrows = 500)

            for i in df.columns:
                df[i] = df[i].astype('float64')

            X = df.drop(['WinTrade'], axis=1)
            Y = df.WinTrade

            X_train, X_test, y_train, y_test = train_test_split(X, Y, test_size=0.3, random_state=32, stratify= Y)
            
            predictions_df = []

            for name,classifier in classifiers:
                classifier.fit(X_train, y_train)
                predictions = classifier.predict(X_test)

                kappa = round(cohen_kappa_score(y_test, predictions) * 100, 1)
                predictions_df.append([name,kappa])

            predictions_df = pd.DataFrame(predictions_df, columns=['Algo','Kappa',])
            display(predictions_df.sort_values('Kappa', ascending=False))
            
            num = 1

            for name ,classifier in classifiers:
                if name in predictions_df.sort_values('Kappa', ascending=False)['Algo'][:2].to_list():
                    print(name,year,strategy)
                    Model = classifier
                    Model.fit(X_train, y_train)

                    importances = Model.feature_importances_
                    weights = pd.Series(importances, index=X.columns.values).sort_values(ascending=False)
                    print(weights.index[:5].to_list())
                    weights.reset_index().rename(columns= {'index': str(strategy)+ '_'+ str(year)}).iloc[:3,:1].to_excel(writer,startcol = num, startrow = 1, index = False,)
                    num = num+2
            writer.save()
writer.close()

问题分析 #

代码逻辑是循环读取每个CSV文件，训练三个模型后筛选Kappa排名前2的模型，将其Top3特征写入同一Excel，但实际仅保留第一个文件的结果，核心问题有两个：

1. writer.save()位置错误：使用xlsxwriter引擎时，调用save()会直接关闭Excel文件的写入流，后续所有写入操作都会失效，只有第一次写入的内容被保存。
2. 列索引num初始化位置错误：num = 1放在每个CSV文件的处理逻辑内，导致每个文件的特征都从第1列开始写入，即使save()位置正确，后续内容也会覆盖之前的结果。

另外还有两处小问题：

• root.rsplit('\\',2, )中的多余逗号会触发语法警告，应改为root.rsplit('\\', 2)
• 重复调用predictions_df.sort_values()会降低代码效率，建议提前排序并存储为变量

修正后的代码 #

writer = pd.ExcelWriter('Allpros.xlsx', engine='xlsxwriter')
num = 1  # 移到外层，确保列索引持续累加

classifiers = [
    ['ExtraTreesClassifier :', ExtraTreeClassifier(min_samples_split=2, random_state = 2)],
    ['LGBMClassifier : ', LGBMClassifier(n_estimators = 400, max_depth=15,learning_rate=1)],
    ['XGB :', XGBClassifier(tree_method = "hist", random_state= 2, learning_rate= 1)],
]

for root,dirs,files in os.walk(filepath):
    for i in files:
        if i.endswith('PreProcessed.csv'):
            uio = f'{root}/{i}'
            # 修正rsplit的语法错误
            year = root.rsplit('\\', 2)[1]
            strategy = root.rsplit('\\', 2)[2]
            print(root,files)
            df = pd.read_csv(uio,usecols = col, nrows = 500)

            for col_name in df.columns:  # 变量名避免和usecols的col冲突
                df[col_name] = df[col_name].astype('float64')

            X = df.drop(['WinTrade'], axis=1)
            Y = df.WinTrade

            X_train, X_test, y_train, y_test = train_test_split(X, Y, test_size=0.3, random_state=32, stratify= Y)
            
            predictions_df = []

            for name,classifier in classifiers:
                classifier.fit(X_train, y_train)
                predictions = classifier.predict(X_test)

                kappa = round(cohen_kappa_score(y_test, predictions) * 100, 1)
                predictions_df.append([name,kappa])

            predictions_df = pd.DataFrame(predictions_df, columns=['Algo','Kappa',])
            # 提前排序并存储，避免重复计算
            sorted_predictions = predictions_df.sort_values('Kappa', ascending=False)
            display(sorted_predictions)
            
            top_algos = sorted_predictions['Algo'][:2].to_list()

            for name ,classifier in classifiers:
                if name in top_algos:
                    print(name,year,strategy)
                    Model = classifier
                    Model.fit(X_train, y_train)

                    importances = Model.feature_importances_
                    weights = pd.Series(importances, index=X.columns.values).sort_values(ascending=False)
                    print(weights.index[:5].to_list())
                    weights.reset_index().rename(columns= {'index': f"{strategy}_{year}"}).iloc[:3,:1].to_excel(writer, startcol=num, startrow=1, index=False)
                    num += 2  # 每次写入后列索引加2，避免重叠

# 所有文件处理完成后再保存并关闭
writer.save()
writer.close()

关键修正点 #

1. 将writer.save()移到所有循环结束后，确保所有写入操作完成后再保存文件
2. 将num = 1初始化移到最外层，保证每个文件的特征写入不同的列，不会覆盖之前的内容
3. 修正rsplit的语法错误，去掉多余的逗号
4. 提前排序predictions_df并存储为变量，减少重复计算
5. 变量名i在循环中重复使用（文件名循环和列名循环），改为col_name避免冲突

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