CODEWITHSUNDEEP
pythonexcelpandas
Alexander Gven
Alexander Gven

Reputation: 11

Excel rounds the number when transferring from pandas

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?

Upvotes: 1

Views: 191

Answers (1)

Adrian Fischer
Adrian Fischer

Reputation: 387

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

Upvotes: 1

Related Questions