Pandas DataFrame to Excel: Vertical Alignment of Index

Question

Given the following data frame: import pandas as pd

d=pd.DataFrame({'a':['a','a','b','b'],
               'b':['a','b','c','d'],
               'c':[1,2,3,4]})
d=d.groupby(['a','b']).sum()
d

I'd like to export this with the same alignment with respect to the index (see how the left-most column is centered vertically?). The rub is that when exporting this to Excel, the left column is aligned to the top of each cell:

writer = pd.ExcelWriter('pandas_out.xlsx', engine='xlsxwriter')
workbook  = writer.book
f=workbook.add_format({'align': 'vcenter'})
d.to_excel(writer, sheet_name='Sheet1')
writer.save()

...produces...

Is there any way to center column A vertically via XLSX Writer or another library?

Thanks in advance!

Raulrausch

Try the structure here. It looks similar to what you've done, but I don't know enough about the xlsxwriter library to know if they're identical. – Skyler

Answer 1

You are trying to change the formatting of the header so you should first reset the default header settings

from pandas.io.formats.excel import ExcelFormatter
ExcelFormatter.header_style = None

Then apply the formatting as required

format = workbook.add_format()
format.set_align('center')
format.set_align('vcenter')

worksheet.set_column('A:C',5, format)

here is complete working code

d=pd.DataFrame({'a':['a','a','b','b'],
               'b':['a','b','c','d'],
               'c':[1,2,3,4]})
d=d.groupby(['a','b']).sum()

pd.core.format.header_style = None

writer = pd.ExcelWriter('pandas_out.xlsx', engine='xlsxwriter')
workbook  = writer.book
d.to_excel(writer, sheet_name='Sheet1')

worksheet = writer.sheets['Sheet1']

format = workbook.add_format()
format.set_align('center')
format.set_align('vcenter')

worksheet.set_column('A:C',5, format)
writer.save()

Interconnect

Instead of using 'center' i am using 'left' and the text still gets aligned to the center – Nyala

@acuriousengineer Same here :( – Gorges

AttributeError: module 'pandas.core' has no attribute 'format' it seems that it no longer works... – Mcclelland

Fixed - edited as per https://mcmap.net/q/1167091/-pandas-raising-attributeerror-module-39-pandas-core-39-has-no-attribute-39-format-39 – Wanhsien