4

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?

Camper731
  • 83
  • 6
  • Why don't you just select "percent" as number format in Excel? – Bertil Johannes Ipsen May 27 '20 at 13:24
  • 1
    Doesn't that defeat the purpose of writing a script? This task is done repeatedly.. – Camper731 May 27 '20 at 13:54
  • Well, if you want to do further calculations with it, it doesn't matter that it's not in the format of a percentage. If pandas saved your data with the percent sign, it would be in the form of a string - right? In which case, you can't process it further. If you want it to look like a percentage, you could also of course multiply everything by 100, but then of course the same thing would apply - you won't be able to further calculations, because you've distorted the data. – Bertil Johannes Ipsen May 27 '20 at 13:59
  • 1
    I do not intend to do further calcuations. I am writing it to disk so that it can be reviewed by others. It is done weekly, and I don't want to bother with formatting it by hand. I'm sorry my question offends you for whatever reason. The Styler class has been created to format dataframes, and this is not the expected behavior. I don't think any further explanation is needed. – Camper731 May 28 '20 at 07:44
  • Does this answer your question? [How to save pandas dataframe with float format changed to percentage with 2 decimal places](https://stackoverflow.com/questions/54182424/how-to-save-pandas-dataframe-with-float-format-changed-to-percentage-with-2-deci) – Bertil Johannes Ipsen May 28 '20 at 07:49
  • It's a semi-suitable work-around, but does not answer the question. See my answer. – Camper731 May 28 '20 at 08:42

2 Answers2

2

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

Camper731
  • 83
  • 6
0

You might want to have a look at openpyxl, eg through this thread Writing Percentages in Excel Using Pandas

Bertil Johannes Ipsen
  • 1,656
  • 1
  • 14
  • 27