sparc_spread sparc_spread - 7 months ago 90
Python Question

Writing pandas DataFrame to Excel with different formats for different columns

I am trying to write a pandas

to an
file where different numerical columns would have different formats. For example, some would show only two decimal places, some would show none, some would be formatted as percents with a "%" symbol, etc.

I noticed that
has a
parameter that allows one to do just that, mapping different formats to different columns. However, there is no similar parameter on the
method. The most we have is a
that is global to all numbers.

I have read many SO posts that are at least partly related to my question, for example:

Are there other more convenient Excel-related functions/properties in the pandas API that can help here, or something similar on
, or perhaps some way to specify output format metadata directly onto each column in the
that would then be interpreted downstream by different outputters?


You can do this with Pandas 0.16 and the XlsxWriter engine by accessing the underlying workbook and worksheet objects:

import pandas as pd

# Create a Pandas dataframe from some data.
df = pd.DataFrame(zip(
    [1010, 2020, 3030, 2020, 1515, 3030, 4545],
    [.1, .2, .33, .25, .5, .75, .45],
    [.1, .2, .33, .25, .5, .75, .45],

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  =
worksheet = writer.sheets['Sheet1']

# Add some cell formats.
format1 = workbook.add_format({'num_format': '#,##0.00'})
format2 = workbook.add_format({'num_format': '0%'})
format3 = workbook.add_format({'num_format': 'h:mm:ss AM/PM'})

# Set the column width and format.
worksheet.set_column('B:B', 18, format1)

# Set the format but not the column width.
worksheet.set_column('C:C', None, format2)

worksheet.set_column('D:D', 16, format3)

# Close the Pandas Excel writer and output the Excel file.


enter image description here

See also Working with Python Pandas and XlsxWriter.