python - 使用python在excel表上为我的不同数据框赋予标题

Question

我在 excel 表中有几个数据框，它们之间有 2 行空格，现在我想为这些 dfs 提供标题或描述。这怎么可能？
将此功能用于多个 dfs

# funtion
def multiple_dfs(df_list, sheets, file_name, spaces):
    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')   
    row = 2
    for dataframe in df_list:
        dataframe.to_excel(writer,sheet_name=sheets,startrow=row , startcol=0)   
        row = row + len(dataframe.index) + spaces + 1
    writer.save()

# list of dataframes
dfs = [df,df1,df2]

# run function
multiple_dfs(dfs, 'Validation', 'test1.xlsx', 1)

Answer 1

您可以将描述包装在 Series 中，并以与数据框相同的方式编写它:

import pandas as pd
import numpy as np

def multiple_dfs(df_list, sheets, file_name, spaces, comments):
    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')   
    row = 2
    for dataframe, comment in zip(df_list, comments):
        pd.Series(comment).to_excel(writer,sheet_name=sheets, startrow=row,
                                    startcol=1, index=False, header=False)
        dataframe.to_excel(writer,sheet_name=sheets,startrow=row + 1, startcol=0)   
        row = row + len(dataframe.index) + spaces + 2
    writer.save()

# list of dataframes
df = pd.DataFrame(np.arange(9).reshape(3,3))
df1 = pd.DataFrame(np.arange(10, 19).reshape(3,3))
df2 = pd.DataFrame(np.arange(20, 29).reshape(3,3))
dfs = [df,df1,df2]
comments = ['first', 'second', 'third']

# run function
multiple_dfs(dfs, 'Validation', 'test1.xlsx', 1, comments)

根据评论更新:

def multiple_dfs(df_list, sheets, spaces, comments):   
    row = 2
    for dataframe, comment in zip(df_list, comments):
        pd.Series(comment).to_excel(writer,sheet_name=sheets, startrow=row,
                                    startcol=1, index=False, header=False)
        dataframe.to_excel(writer,sheet_name=sheets,startrow=row + 1, startcol=0)   
        row = row + len(dataframe.index) + spaces + 2

# list of dataframes and comments
# ...

# run function
writer = pd.ExcelWriter('test1.xlsx',engine='xlsxwriter')
multiple_dfs(dfs123, 'Validation1', 1, comments123)
multiple_dfs(dfs456, 'Validation2', 1, comments456)
writer.save()