Setting default number format when writing to Excel from Pandas

Question

I'm looking to set the default number format when writing to Excel from a Pandas dataframe. Is this possible?

I can set the default date/datetime_format with the following, but couldn't find a way to set the default number format.

writer = pd.ExcelWriter(f'{file_variable}.xlsx', engine='xlsxwriter',datetime_format='MM/DD/YYYY')

Otherwise, I assume I'm going to have to assign worksheets to variables and loop through the rows for the specified columns to set the number format.

Journalese

Answer 1

I got this format the floats to 1 decimal place.

data = {'A Prime': {0: 3.26,  1: 3.24,  2: 3.22,  3: 3.2,  4: 3.18,  5: 3.16,
  6: 3.14,  7: 1.52,  8: 1.5,  9: 1.48,  10: 1.46,  11: 1.44,  12: 1.42},
 'B': {0: 0.16608,  1: 0.16575,  2: 0.1654,  3: 0.16505999999999998,  4: 0.1647,  5: 0.16434,  6: 0.16398,  7: 0.10759,  8: 0.10687,  9: 0.10614000000000001,
  10: 0.10540999999999999,  11: 0.10469,  12: 0.10396}, 'Proto Name': {0: 'Alpha',
  1: 'Alpha',  2: 'Alpha', 3: 'Alpha',  4: 'Alpha',  5: 'Alpha',  6: 'Alpha',  7: 'Bravo',  8: 'Bravo',  9: 'Bravo',  10: 'Bravo',  11: 'Bravo',  12: 'Bravo'}}

import pandas as pd
df = pd.DataFrame(data)


    A Prime B       Proto Name
0   3.26    0.16608 Alpha
1   3.24    0.16575 Alpha
2   3.22    0.16540 Alpha
3   3.20    0.16506 Alpha
4   3.18    0.16470 Alpha
5   3.16    0.16434 Alpha
6   3.14    0.16398 Alpha
7   1.52    0.10759 Bravo
8   1.50    0.10687 Bravo
9   1.48    0.10614 Bravo
10  1.46    0.10541 Bravo
11  1.44    0.10469 Bravo
12  1.42    0.10396 Bravo

writer = pd.ExcelWriter(r'c:\temp\output.xlsx')
# This method will truncate the data past the first decimal point
df.to_excel(writer,'Sheet1',float_format = "%0.1f")
writer.save()

but that alas is not perhaps all cases - no joy with say larger numbers and thousands separator

df.to_excel(writer,'Sheet1',float_format = ":,")

However the following seems to work

data = {'A Prime': {0: 326000,  1: 3240000}}
df = pd.DataFrame(data)

    A Prime
0   326000
1   3240000

writer = pd.ExcelWriter(r'c:\temp\output.xlsx')
df.to_excel(writer,'Sheet1')
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
format1 = workbook.add_format({'num_format': '#,##0.00'})
worksheet.set_column('B:B', 18, format1)
#Alternatively, you could specify a range of columns with 'B:D' and 18 sets the column width
writer.close()

All taken from here: http://xlsxwriter.readthedocs.io/working_with_pandas.html

Cargian

Thank ChrisG for the updates to the post - those make it clearer. – Cargian

For future readers, I preferred the second approach to the first approach presented. Thanks @Dickster, somehow I missed that section on the readthedocs page. Much appreciated. – Journalese

Answer 2

For what it's worth and because the question was also tagged for openpyxl, you can also also edit the default style of a whole workbook in openpyxl. This could make sense for the number format but can have unexpected consquences if things like the font size is changed, because other GUI elements are affected. The following should work, if used with caution.

wb._named_styles['Normal'].number_format = '#,##0.00'

Beauvoir

Should this code be executed after using df.to_excel()? If yes, could you please give a complete code example with using df.to_excel(), changing number_format and then saving the workbook? – Ashlynashman