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

Question

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?

Answer 1

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

Answer 2

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))