-1

when I export numbers from Python to Excel they get rounded incorrectly in Excel. For example, in Python the number is 5.49 (decimal.Decimal()) but when I export it to Excel, it gets rounded to 5.5. What should I put in my code to prevent this from happening? I want it to display 5.49 just like it is in Python.

martineau
  • 119,623
  • 25
  • 170
  • 301
user29617
  • 49
  • 5
  • Please share your code here in a code block. – CypherX Jul 08 '21 at 16:49
  • What code you using to "export" the values? Please add it to your question. – martineau Jul 08 '21 at 16:50
  • Is it actually rounded in Excel, or is the number format only set to one decimal? – BigBen Jul 08 '21 at 16:52
  • Please supply the expected [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) (MRE). We should be able to copy and paste a contiguous block of your code, execute that file, and reproduce your problem along with tracing output for the problem points. This lets us test our suggestions against your test data and desired output. Show where the intermediate results differ from what you expected. – Prune Jul 08 '21 at 16:54

2 Answers2

1

"Exporting numbers from python to excel": Although this may sound specific enough, it is NOT. Does it answer any of the following?

  • Which python library are you using to export the data from python to excel?
  • How do you evaluate that excel is recording 5.49 as 5.5?
    • Did you save your excel data as a .csv file to check the ground reality?
    • Did you check the number of decimal places that excel is supposed round off to the number after? (⚠️ Most likely this is what is happening in your case)

Check the output of the following

OPEN both files (.csv and .xlsx) using a text editor and NOT MS Excel to check the ground reality).

import pandas as pd

df = pd.DataFrame({'x': [10.0, 5.49, 0.904], 'expected': ['10.0', '5.49', '0.904']})
df.to_excel('output.xlsx')
df.to_csv('output.csv')
CypherX
  • 7,019
  • 3
  • 25
  • 37
1

If we assume you have a data frame in pandas and you would export it to Excel without rounding. You could format your workbook from python as follows:

writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
format1 = workbook.add_format({'column_name': '#.##'})
# Set the format of your column but not the column width.
worksheet.set_column('A:A', None, format1)
writer.save()

You can read more at this link here . Credits for here