CODEWITHSUNDEEP
pythonexcelpandas
rhaskett
rhaskett

Reputation: 1962

Writing Percentages in Excel Using Pandas

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.

Upvotes: 15

Views: 19770

Answers (3)

Colin Middleton
Colin Middleton

Reputation: 61

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

Upvotes: 6

Mike Demenok
Mike Demenok

Reputation: 810

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.

Upvotes: 9

Saullo G. P. Castro
Saullo G. P. Castro

Reputation: 58915

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

Upvotes: 8

Related Questions