Example: Pandas Excel output with percentage formatting

To create a percentage in Excel the data must be a number, must be divided by 100 and must have a percentage number format applied.

Here is a simple example of converting some string percentage data in a Pandas dataframe to percentage numbers in an xlsx file using XlsxWriter as the Pandas excel engine:

_images/pandas_percentage.png
##############################################################################
#
# An example of converting some string percentage data in a Pandas dataframe
# to percentage numbers in an xlsx file with using XlsxWriter.
#
# SPDX-License-Identifier: BSD-2-Clause
# Copyright 2013-2022, John McNamara, jmcnamara@cpan.org
#

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Names': ['Anna', 'Arek', 'Arun'],
                   'Grade': ['100%', '70%',  '85%']})

# Convert the percentage strings to percentage numbers.
df['Grade'] = df['Grade'].str.replace('%', '')
df['Grade'] = df['Grade'].astype(float)
df['Grade'] = df['Grade'].div(100)

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_percent.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a percent number format.
percent_format = workbook.add_format({'num_format': '0%'})

# Apply the number format to Grade column.
worksheet.set_column(2, 2, None, percent_format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()