Python Pandas: force to save a df in a csv numerical values with 4 digits

Question

I have a dataframe which have float numbers and I would like to save it with the same number of digits. This is my dataframe df:

       1     0.917007  0.920562  0.922801  0.922745  0.917396  0.922445   
       2     0.914374  0.926690  0.914257  0.914607  0.914462  0.910307   
       3     0.919565  0.907405  0.916554  0.916463  0.919817  0.917184   
       4     0.912238  0.915469  0.900786  0.900752  0.912059  0.919894   
       5     0.906972  0.920995  0.918015  0.918200  0.908895  0.907092

And I am saving it doing:

from pandas import ExcelWriter
writer = ExcelWriter('PythonExport.xlsx')
table.to_excel(writer,'aName',float_format='%11.4f')
writer.save()

But when I open the file, I see this:

How can I do to force the number of digits saved for each cell to get this?:

Edit: same happening trying to save as a csv file with the following

table.to_csv('PythonExport.csv', sep=',',float_format='%11.4f')

Note: I'm using python3

This is an issue with how excel chooses to display floats. You need to add some workbook formatting. See here: https://stackoverflow.com/questions/44423036/pandas-to-excel-float-format — killian95, Nov 30 '18 at 19:44
I've just edited my question because it is happens not only using excel, it happens with csv files... — diens, Nov 30 '18 at 19:51

score 2 · Accepted Answer · answered Nov 30 '18 at 20:52

2

The following should work as described here

df.applymap('{:.4f}'.format).to_csv('PythonExport.csv')

To verify make sure you open the .csv in a text editor and not Excel.

answered Nov 30 '18 at 20:52

jeschwar

1,286
7
10

I'm using LibreOffice Calc to open it and the .csv file is not right showed in that program but yes in a text editor, as you said. But I've also tried doing `table.applymap('{:.4f}'.format).to_excel(w...` and it works even opening it with the LibreOffice Calc. Thanks a lot! – diens Nov 30 '18 at 21:04
1

No problem. Check that your columns are indeed floats by checking the output of `df.dtypes` before doing the float formatting. – jeschwar Nov 30 '18 at 21:21

score 1 · Answer 2 · answered Nov 30 '18 at 19:51

You need to create format and then add it to column wherever you need to add, this might help you.

df.to_excel(writer, sheet_name='Sheet1')
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
format1 = workbook.add_format({'num_format': '0.0000'})
worksheet.set_column('B:B', None, format1)
writer.save()

More info: http://xlsxwriter.readthedocs.io/example_pandas_column_formats.html

Python Pandas: force to save a df in a csv numerical values with 4 digits

2 Answers2