Giving title to my different dataframes on excel sheet using python

Question

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)

Answer 1

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)

---

Update as per comment:

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()