Why does pandas Styler 'to_excel' method not save percent formatting?

user_5998829 0
Viewed 1852

I am using the pandas Styler class to format some columns as a percent. When I write the output to excel, the columns are still showing up as floats. Why am I able to format and save colors properly, but not percents?

import pandas as pd
import numpy as np

def color_negative_red(val):
    color = 'red' if val < 0 else 'black'
    return 'color: %s' % color

np.random.seed(24)
df = pd.DataFrame({'A': np.linspace(1, 10, 10)})
df = pd.concat([df, pd.DataFrame(np.random.randn(10, 4), columns=list('BCDE'))],axis=1)

This produces this:

df.style.format('{:.2%}').applymap(color_negative_red)

formatted dataframe

But saving to excel reverts the percents back to floats:

df.style.format('{:.2%}').applymap(color_negative_red).to_excel('format_test.xlsx')

Excel Output

What to do?

pandas-styles pandas dataframe python
2 Answers
user_78913260

Proposed workaround for percent formatting:

with pd.ExcelWriter('test_format.xlsx') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    percent_format = writer.book.add_format({'num_format': '0.00%'})
    worksheet = writer.book.worksheets_objs[0]
    for col in ['A','C','E']:
        worksheet.set_column(f'{col}:{col}', None, percent_format)

does indeed add percents to columns A, C, and E.

worksheet with percent formatting

However, it works only for the DataFrame object and cannot be used with the Styler, meaning, all other formatting will need to be done with the writer object:

with pd.ExcelWriter('test_format.xlsx') as writer:
    df.style.applymap(color_negative_red).to_excel(writer, sheet_name='Sheet1', index=False)
    percent_format = writer.book.add_format({'num_format': '0.00%'})
    worksheet = writer.book.worksheets_objs[0]
    for col in ['A','C','E']:
        worksheet.set_column(f'{col}:{col}', None, percent_format)

enter image description here

user_59988290
Related