how do you force write pandas styles to excel file in Pandas

Question

I have a data frame like this:

df

Server   CPU_Usage Memory Usage
Prod1    80        30
Prod2    70        10
Prod3    20        12

I need to apply Pandas Style to show % sign next to the numbers like below:

Server   CPU_Usage Memory_Usage
Prod1    80%        30%
Prod2    70%        10%
Prod3    20%        12%

I tried this:

df_style=df.style.format({'CPU_Usage':'{:}%', {'Memory_Usage':'{:}%')

When do df_style, it shows up on Jupyter notebook. But when I write this to excel as below:

df_style.to_excel(r'report.xlsx', engine='openpyxl')

Percent sign (%) disappear from the excel file. Any ideas to force writing % next to the CPU_Usage and Memory_Usage in excel file?

Answer 1

Please try:

import pandas as pd
data = {'Server': ['Prod1', 'Prod2', 'Prod3'],
        'CPU_Usage': [80, 70, 20],
        'Memory_Usage': [30, 10, 12]}
df = pd.DataFrame(data)
# Format columns as percentages with a percent sign
df[['CPU_Usage', 'Memory_Usage']] = df[['CPU_Usage', 'Memory_Usage']].applymap(lambda x: f'{x}%')
excelFile = r"d:/temp/report.xlsx"
df.to_excel(excelFile, engine='openpyxl', index=False)

Answer 2

Here is adapting on taller_ExcelHome's answer in conjunction with OP's use of df_style=df.style.format() to incorporate using the methods df.style methods to apply functions along with the .to_excel() method, and combining that with using Excel terminology for number-format that is able to be exported to Excel, as listed in the documentation under 'Export to Excel':

import pandas as pd
data = {'Server': ['Prod1', 'Prod2', 'Prod3'],
        'CPU_Usage': [80, 70, 20],
        'Memory_Usage': [30, 10, 12]}
df = pd.DataFrame(data)
def format2percent(val):
    return 'number-format: #\%' # add percent symbol in Excel terminology based https://stackoverflow.com/a/16807093/8508004 and https://stackoverflow.com/a/56779532/8508004

df_style=df.style.format({'CPU_Usage':'{:}%', 'Memory_Usage':'{:}%'})

df_style.\
    map(format2percent).\
    to_excel('styled.xlsx', engine='openpyxl', index=False)
df_style

And how 'styled.xlsx' looks:

(If you are using a version of Pandas before 2.10, such as 1.5.1, use applymap(format2percent).\ instead of map(format2percent).\. See here about how "since version 2.1.0: Styler.applymap has been deprecated. Use Styler.map instead.")

Related to this question there is:

• 'panda styling : export number-format to excel' did something similar with a different number formatting.
• 'Setting default number format when writing to Excel from Pandas', might also guide you.

The Excel terminology for the formatting came from this StackOverflow answer to 'Number format in excel: Showing % value without multiplying with 100'.

Note, you can test the 'number-format' syntax codes in the home ribbon in Excel if you add some numbers and then go to format number. Ultimately select 'custom codes' in there. It took some stumbling around in Microsoft's Help to even find how to activate it. This guide seem outdated but will give you an idea of what you are looking for in the Excel interface on the Home ribbon.