5

I have multiple dataframes that look like this, the data is irrelevant.

enter image description here

I want it to look like this, i want to insert a title above the column headers.

enter image description here

I want to combine them into multiple tabs in an excel file.

Is it possible to add another row above the column headers and insert a Title into the first cell before saving the file to excel.

I am currently doing it like this.

with pd.ExcelWriter('merged_file.xlsx',engine='xlsxwriter') as writer:
    for filename in os.listdir(directory):
        if filename.endswith('xlsx'):
            print(filename)
            if 'brands' in filename:
                some function
            elif 'share' in filename:
                somefunction
            else:
                some function
            df.to_excel(writer,sheet_name=f'{filename[:-5]}',index=True,index_label=True)
writer.close()

But the sheet_name is too long, that's why I want to add the title above the column headers.

I tried this code,

columns = df.columns
columns = list(zip([f'{filename[:-5]}'] * len(df.columns), columns))             
columns = pd.MultiIndex.from_tuples(columns) 
df2 = pd.DataFrame(df,index=df.index,columns=columns) 
df2.to_excel(writer,sheet_name=f'{filename[0:3]}',index=True,index_label=True)

But it ends up looking like this with all the data gone,

enter image description here

It should look like this

enter image description here

Michael Delgado
  • 13,789
  • 3
  • 29
  • 54
anarchy
  • 3,709
  • 2
  • 16
  • 48
  • 1
    Please do not include picture of data. See [this post](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for details. – Quang Hoang Mar 03 '21 at 14:43
  • 1
    there is no need for the data in this question – anarchy Mar 03 '21 at 14:43
  • Fwiw, if the data is irrelevant, that’s an especially good reason to create a reproducible example (eg using `np.random` or with dummy data as in the accepted answer). The [post](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) Quang Hoang links to is very much worthy of a read. – Michael Delgado Dec 18 '22 at 19:40

2 Answers2

8

You can write data from sedond row first and then write to first cell your text:

df = pd.DataFrame({'col': list('abc'), 'col1': list('def')})
print (df)
  col col1
0   a    d
1   b    e
2   c    f

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', startrow = 1, index=False)
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

text = 'sometitle'
worksheet.write(0, 0, text)
writer.save()

Then for reading need:

title = pd.read_excel('test.xlsx', nrows=0).columns[0]
print (title)
sometitle

df = pd.read_excel('test.xlsx', skiprows=1)
print (df)
  col col1
0   a    d
1   b    e
2   c    f
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
4

You can use MultiIndex. There is an example:

import pandas as pd 
df = pd.read_excel('data.xls')
header = pd.MultiIndex.from_product([['Title'],
                                     list(df.columns)])
pd.DataFrame(df.to_numpy(), None , columns = header)

Also, I can share with you my solution with real data in Deepnote (my favorite tool). Feel free to duplicate and play with your own .xls: https://deepnote.com/publish/3cfd4171-58e8-48fd-af21-930347e8e713

Andrej Chudy
  • 859
  • 6
  • 5
  • 1
    hi, i used `header = pd.MultiIndex.from_product([['title'],df.columns])` `df = pd.DataFrame(df.to_numpy(), index=df.index , columns = header)` `df.to_excel(writer,sheet_name=f'{filename[0:3]}',index=True,index_label=True)`, but there is are spaces below the sheet name and the title doesnt start in the first cell – anarchy Mar 03 '21 at 15:57
  • also is there a way to unmerge and un center it? – anarchy Mar 03 '21 at 15:59