python - Pandas dataframe with dynamic title

Question

Suppose I have the following dataframe:

df = pd.DataFrame({
        'A':list('abcdef'),
         'B':[4,5,4,5,5,4],
         'C':[7,8,9,4,2,3],
         'D':[1,3,5,7,1,0],
         'E':[5,3,6,9,2,4],
         'F':list('aaabbb')
})

I want to give it a title provided by user e.g.: title = 'The sales'

How can I add the title to the df such that when send to Excel sheet or pdf it appears as: `

{title} in this case *The sales*
 A  B  C  D  E  F
0  a  4  7  1  5  a
1  b  5  8  3  3  a
2  c  4  9  5  6  a
3  d  5  4  7  9  b
4  e  5  2  1  2  b
5  f  4  3  0  4  b

Searching for possible solutions leads to adding column title, while what I want is adding dataframe title.

Answer 1

Here is one way to do it using xlsxwriter as the Pandas Excel engine. Something similar would also be possible using openpyxl:

import pandas as pd

df = pd.DataFrame({'A': list('abcdef'),
                   'B': [4, 5, 4, 5, 5, 4],
                   'C': [7, 8, 9, 4, 2, 3],
                   'D': [1, 3, 5, 7, 1, 0],
                   'E': [5, 3, 6, 9, 2, 4],
                   'F': list('aaabbb')})

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

# Shift the dataframe down one row in the Excel file.
df.to_excel(writer, sheet_name='Sheet1', startrow=1)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Write the title to the worksheet.
title = 'The sales'
worksheet.write(0, 0, title)

writer.save()

Output: