I have few dataframes in an excel sheet with 2 rows of spaces between them and now I want to give title or description to those dfs. How is it possible?

Used this function for multiple 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)

CodePudding user response：

You can wrap your description in a Series and write it the same way as the dataframe:

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)