Pandas.to_excel rounding my decimal values up to 2 decimal places, but gives accurate results when converted to_csv

user_11316823 0
Viewed 3327

I have a dataframe with large set of records.

When this dataframe is converted to CSV it give accurate results with all digits after decimals. However the same dataframe when converted to excel (xlsx) then the Decimal Values get rounded off in the Excel sheet (most of them up to two decimal places).

For example:

CSV output: 3363.6499999999999

Excel output: 3363.65

I am writing the output in output=BytesIO() to save the Excel in S3 using "put(output.getvalue())"

output= BytesIO()
writer=pd.ExcelWriter(output)
df.to_excel(writer, index=  False)
writer.save()

I do not want to round. I would like my actual value without any changes. How do I convert to_excel without rounding?

pandas excel python
3 Answers

Try this example:

df = pd.DataFrame({
        'date':['1/15/2016','2/1/2016','2/15/2016','3/15/2016'],
        'numA':[1000,2000,3000,4000.3],
        'numB':[10000,20000.2,30000,40000]
    })

writer = pd.ExcelWriter('c:/.../pandas_excel_test.xlsx', engine = 'xlsxwriter')
df.to_excel(writer, index=False, sheet_name='Sheet1')
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
format1 = workbook.add_format({'num_format': '0.00'})
worksheet.set_column('C:C', None, format1)  # Adds formatting to column C
writer.save()

You can change the format 0.00 in the .add_format({'num_format': '0.00'}) on your desired format, for example, 0.0000 for four decimal places. Note this only applies to column C.

If you want to change the formatting of all columns, modify the worksheet.set_column('C:C', None, format1), for example

worksheet.set_column(0, 3, None, format1)

where 0 the first column and 3 the final column.

Check more formatting here: https://xlsxwriter.readthedocs.io/worksheet.html

user_157840490

Another example:

import xlsxwriter
writer = pd.ExcelWriter('c:/.../pandas_excel_test.xlsx', engine = 'xlsxwriter')
df.to_excel(writer, index=False, sheet_name='Sheet1')

workbook  = writer.book
cell_format1 = workbook.add_format()
cell_format1.set_num_format('0.000')
worksheet.write(1, 0, 3.1415926, cell_format1)

Result

3.142
user_157840490

I am new here, would you be able to round to 2 decimal places using this?

df['col_name'].apply(lambda x: round(x, 2))
Admin1
Related