CODEWITHSUNDEEP
pythonexcelpandas
Naveen Kumar
Naveen Kumar

Reputation: 41

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

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?

Upvotes: 4

Views: 11318

Answers (3)

Eddy Piedad
Eddy Piedad

Reputation: 366

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

Upvotes: 1

Eddy Piedad
Eddy Piedad

Reputation: 366

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

Upvotes: 5

user15753297
user15753297

Reputation:

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

Upvotes: 0

Related Questions