pythonpandasxlsxwriter

If you specify a background color in pandas in python, number formatting is disabled

After sorting the data in pandas, I am saving it in Excel. At this time, we are trying to improve readability by changing the format. I specified the number format and background color at the same time, but there is an error where only one of the two is specified. I'm pretty sure this isn't an error, it's just 'I'm not understanding very well'. But I don't know where I made a mistake. I would like to receive your help.

Below is the example code I wrote and the results of running it. The first code is when I formatted the number. It works normally.

df = pd.DataFrame({
    'Name': ['John', 'Isac'],
    'Value': [100000, 300000000]
})

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='test', index=False)
    
    workbook = writer.book
    worksheet = writer.sheets['test']
    
    format_with_commas = workbook.add_format({'num_format':'#,##0'})
    worksheet.set_column('B:B', 15, format_with_commas)

enter image description here

However, the problem is that if you write code to specify the background color here, the number format will not work. I have included the code and execution results as images.

df = pd.DataFrame({
    'Name': ['John', 'Isac'],
    'Value': [100000, 300000000]
})

styled_df = df.style.set_properties(**{'background-color':'yellow', 'color':'black'})

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer:
    styled_df.to_excel(writer, sheet_name='test', index=False)
    
    workbook = writer.book
    worksheet = writer.sheets['test']
    
    format_with_commas = workbook.add_format({'num_format':'#,##0'})
    worksheet.set_column('B:B', 15, format_with_commas)

enter image description here

If my method is not correct, please let me know where I am wrong. Alternatively, if this code is outdated, please point that out as well.

Solution

  • I think you can use only pandas styles solution, but if check Styler.format:

    Styler.format is ignored when using the output format Styler.to_excel, since Excel and Python have inherrently different formatting structures. However, it is possible to use the number-format pseudo CSS attribute to force Excel permissible formatting. See examples.

    So try number-format pseudo CSS attribute, see last paragraph:

    styled_df = df.style.set_properties(**{'background-color':'yellow', 'color':'black'})

pseudo_css = 'number-format: #,##0'

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer:
    (styled_df.map(lambda  v: pseudo_css, subset=['Value'])
              .to_excel(writer, sheet_name='test', index=False))

    For oldier pandas versions:

    styled_df = df.style.set_properties(**{'background-color':'yellow', 'color':'black'})

pseudo_css = 'number-format: #,##0'

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer:
    (styled_df.applymap(lambda  v: pseudo_css, subset=['Value'])
              .to_excel(writer, sheet_name='test', index=False))