Writing Percentages in Excel Using Pandas

Question

When writing to csv's before using Pandas, I would often use the following format for percentages:

'%0.2f%%' % (x * 100)

This would be processed by Excel correctly when loading the csv.

Now, I'm trying to use Pandas' to_excel function and using

(simulated * 100.).to_excel(writer, 'Simulated', float_format='%0.2f%%')

and getting a "ValueError: invalid literal for float(): 0.0126%". Without the '%%' it writes fine but is not formatted as percent.

Is there a way to write percentages in Pandas' to_excel?

This question is all pretty old at this point. For better solutions check out xlsxwriter working with pandas.

Answer 1

You can do the following workaround in order to accomplish this:

df *= 100
df = pandas.DataFrame(df, dtype=str)
df += '%'

ew = pandas.ExcelWriter('test.xlsx')
df.to_excel(ew)
ew.save()

Answer 2

This is the solution I arrived at using pandas with OpenPyXL v2.2, and ensuring cells contain numbers at the end, and not strings. Keep values as floats, apply format at the end cell by cell (warning: not efficient):

xlsx = pd.ExcelWriter(output_path)
df.to_excel(xlsx, "Sheet 1")
sheet = xlsx.book.worksheets[0]
for col in sheet.columns[1:sheet.max_column]:
    for cell in col[1:sheet.max_row]:
        cell.number_format = '0.00%'
        cell.value /= 100 #if your data is already in percentages, has to be fractions
xlsx.save()

See OpenPyXL documentation for more number formats.

Interestingly enough, the docos suggest that OpenPyXL is smart enough to guess percentages from string formatted as "1.23%", but this doesn't happen for me. I found code in Pandas' _Openpyxl1Writer that uses "set_value_explicit" on strings, but nothing of the like for other versions. Worth further investigation if somebody wants to get to the bottom of this.