如何在for循环中将多个Pandas DataFrame保存到单个Excel工作表

我后续实验需要增加待测试试剂种类，已经用for循环从Excel读取浓度和体积数据，生成了以浓度为索引/表头、移液体积为内容的DataFrame，但在把多个DataFrame保存到单个Excel工作表时出错，报错：TypeError: unhashable type: 'DataFrame'

初始数据 #

实现代码 #

import numpy as np
import pandas as pd
from itertools import product


df = pd.read_excel("BlueTest.xlsx")


with pd.ExcelWriter('test3.xlsx', engine='xlsxwriter') as writer:
    workbook = writer.book
    worksheet = workbook.add_worksheet('Result')
    writer.sheets['Result'] = worksheet

COLUMN = 0
row = 0


for x in ["6-BAP", "TDZ"]:
    for y in ["Picloram","2,4D","Dicamba"]:
        df1 = df.loc[:,df.columns.str.startswith(x)]
        df2 = df.loc[:,df.columns.str.startswith(y)]
        my_product = list(product(df1[f"{x}_Vol"], df2[f"{y}_Vol"]))
        my_product_str = [str(a) + "uL" for a in my_product]
        my_product_str_np = np.array(my_product_str)
        my_product_str_np = my_product_str_np.reshape(len(df1), len(df2))
        dfn = pd.DataFrame(my_product_str_np, index=df1[f"{x}_Conc.1"], columns=df2[f"{y}_Conc"])
        worksheet.write_string(row,COLUMN,dfn)
        row += 1
        dfn.to_excel(writer, sheet_name="Results", startrow= row, startcol=COLUMN)
        row += dfn.shape[0]+2

问题分析与修复 #

1. 报错根源：worksheet.write_string(row,COLUMN,dfn)这行代码错误地将整个DataFrame对象传给了write_string方法——该方法仅接受字符串参数，而DataFrame是不可哈希的复杂对象，因此抛出TypeError。

2. 核心修复点：

   • 删除worksheet.write_string(row,COLUMN,dfn)这行错误代码，dfn.to_excel()已经可以完成DataFrame的写入操作。
   • 统一工作表名称：代码中创建的工作表是'Result'，但dfn.to_excel()里写的是sheet_name="Results"（多了一个s），会导致创建新工作表，需统一为'Result'。
   • 修正索引列名：原数据中浓度列是6-BAP_Conc而非6-BAP_Conc.1，需修改索引读取逻辑避免KeyError。

3. 修正后的代码：

import numpy as np
import pandas as pd
from itertools import product


df = pd.read_excel("BlueTest.xlsx")

with pd.ExcelWriter('test3.xlsx', engine='xlsxwriter') as writer:
    worksheet = writer.book.add_worksheet('Result')
    writer.sheets['Result'] = worksheet

    COLUMN = 0
    row = 0

    for x in ["6-BAP", "TDZ"]:
        for y in ["Picloram","2,4D","Dicamba"]:
            df1 = df.loc[:, df.columns.str.startswith(x)]
            df2 = df.loc[:, df.columns.str.startswith(y)]
            
            # 生成体积组合并格式化
            my_product = list(product(df1[f"{x}_Vol"], df2[f"{y}_Vol"]))
            my_product_str = [f"{val}uL" for val in my_product]
            my_product_arr = np.array(my_product_str).reshape(len(df1), len(df2))
            
            # 创建结果DataFrame，修正索引列名
            dfn = pd.DataFrame(my_product_arr, 
                              index=df1[f"{x}_Conc"], 
                              columns=df2[f"{y}_Conc"])
            
            # 可选：添加试剂组合标题
            worksheet.write_string(row, COLUMN, f"{x} × {y}")
            row += 1
            
            # 将DataFrame写入指定位置
            dfn.to_excel(writer, sheet_name='Result', startrow=row, startcol=COLUMN)
            
            # 更新行号，留出空行分隔不同表格
            row += dfn.shape[0] + 2

内容的提问来源于stack exchange，提问作者Teigsta