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.

I think is a "bug". Opening an issue over at http://www.github.com/pydata/pandas... — Phillip Cloud, Aug 08 '13 at 00:07
Thanks for taking a look. It looks like I'll have to wait for a few versions. — rhaskett, Aug 13 '13 at 18:00
This is fairly old at this point. This link has more robust solutions https://xlsxwriter.readthedocs.io/working_with_pandas.html — rhaskett, May 17 '18 at 22:43

Mike Demenok · Answer 1 · 2016-08-18T04:44:30.207

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.

i added `if col[0].value == 'expected column name'` between two fors. — Lei Yang, May 12 '23 at 05:41

score 7 · Accepted Answer · answered Oct 16 '13 at 17:54

7

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()

answered Oct 16 '13 at 17:54

Saullo G. P. Castro

56,802
26
179
234

7

Does this solution yield strings in the Excel sheet's cells? This is rather a disadvantage... Isn't it? – Dror Dec 29 '14 at 09:29
1

@Dror good point... I tested and the resulting Excel sheet has numbers, not strings – Saullo G. P. Castro Dec 30 '14 at 00:32
6

I tried this and Excel flags the cells with "Number stored as text". – rleelr Nov 22 '18 at 16:11

Colin Middleton · Answer 3 · 2022-09-16T23:18:32.990

The XlsxWriter docs have a helpful example of how to achieve this: https://xlsxwriter.readthedocs.io/example_pandas_percentage.html

Here's the gist:

writer = pd.ExcelWriter('pandas_percent.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
percent_format = writer.book.add_format({'num_format': '0%'})
# Now apply the number format to the column with index 2.
writer.sheets['Sheet1'].set_column(2, 2, None, percent_format)
writer.save()

Note 1: The column you want to format as a percent must be a ratio float (i.e. do not multiply it by 100).
Note 2: The parameter in the set_column() call that is set to None is the column width. If you want to automatically fit the column width check out this post: https://stackoverflow.com/a/61617835/13261722.
Note 3: If you want more on the set_column() function you can check out the docs: https://xlsxwriter.readthedocs.io/worksheet.html?highlight=set_column#set_column

Writing Percentages in Excel Using Pandas

3 Answers3

Linked