Excel rounds the number when transferring from pandas

user_14596074 0
Viewed 43

Creating a dataframe of float. Here's what pandas outputs:

   print(hh.iloc[2][Number])
   >>1028112000000713.0

And that 's what excel outputs:

1028112000000710

Output code to excel:

    xl = pd.ExcelWriter('gg.xlsx', engine='xlsxwriter')
    df.to_excel(
            xl,
            sheet_name='ww',
            index=False)

I tried that:

    wb = xl.book
    ws = xl.sheets['ww']

    form = wb.add_format()
    form.set_num_format('################')
    ws.set_column('D:D', 18, form)

But it didn't work

Why does excel round numbers? How to fix it?

pandas excel python
1 Answers

I think Excel can't store more than 15 significant positions. See this link.

The number of digits of precision also limits the accuracy of the numbers. Excel store 15 significant digits of precision. For example, the number 1234567890123456 cannot be exactly represented if 15 digits of precision are used.

Your number has 16 positions.

This happens always in Excel. It is not an issue with Pandas or Python writing the value to Excel. You can check this as follows:

  1. Open Excel and write this number 1234567890123456789 in a cell.
  2. Press enter
  3. You will see it shows this 1234567890123450000
user_116629720
Related