[Python] styling dataframe with multi-index and export to excel

I have a dataframe like this:

df = pd.DataFrame({
'Counterparty': ['foo', 'fizz', 'fizz', 'fizz','fizz'],
'Commodity': ['bar', 'bar', 'bar', 'bar','bar'],
'DealType': ['Buy', 'Buy', 'Buy', 'Buy', 'Buy'],
'StartDate': ['07/01/2024', '09/01/2024', '10/01/2024', '11/01/2024', '12/01/2024'],
'FloatPrice': [18.73, 17.12, 17.76, 18.72, 19.47],
'MTMValue':[10, 10, 10, 10, 10]
})


df = df.set_index(['Counterparty', 'Commodity', 'DealType','StartDate']).sort_index()[['FloatPrice', 'MTMValue']]

print(df)


i am trying to apply styles to the dataframe before I export it to excel. This is what I have done so far:

def style_index(s):
return "background-color: lightblue; text-align: center; border: 1px solid black; vertical-align: middle;"

def style_header(s):
return "background-color: lightgrey; text-align: center;"

styled_df = df.style.set_properties(**{
'background-color': 'darkblue',
'color': 'white',
'text-align': 'center'}).map_index(style_index).map_index(style_header, axis="columns")


which yields this:



however I need the dataframe to look like this:

• the index column headers are not getting highlighted
• the items in the index are not aligned in the center

Here is how I export my dataframes, item is a dataframe:

with pd.ExcelWriter(path, engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
for item in list:
styled_df = item.style.set_properties(**{
'background-color': '#0F243E',
'color': 'white',
'text-align': 'center'}).map_index(style_index).map_index(style_header, axis="columns")

styled_df.to_excel(writer, sheet_name=name, startrow=rowPos, float_format = "%0.2f", index=bool)


Also, I feel like there is a more efficient way to accomplish what I am already doing so any suggestions are appreciated.

EDIT: managed to get the text vertically aligned in the center, but still no luck with highlighting the index column headers

Continue reading...