Pandas dataframe with dynamic title-CodePudding

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.

CodePudding user response：

You can use xlwings, it is one of the best libraries to get your data into excel using python

import pandas as pd
import xlwings as xw

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')
})


title = 'The sales'

wb = xw.Book("sales.xlsx")

sheet = wb.sheets['Sheet1']

sheet.range('A1').value = title

sheet.range('A2').options(pd.DataFrame, index=False).value = df

wb.save('sales.xlsx')
wb.close()

#If you dont need Index keep above #If you need Index put index=True

CodePudding user response：

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: