Pandas how to keep the LAST trailing zeros when exporting DataFrame into CSV

Question

In this question, my goal is to preserve the last trailing zeros when exporting the DataFrame to CSV

My dataset looks like this:

EST_TIME    Open    High
2017-01-01  1.0482  1.1200    
2017-01-02  1.0483  1.1230
2017-01-03  1.0485  1.0521
2017-01-04  1.0480  1.6483
2017-01-05  1.0480  1.7401
...., ...., ....
2017-12-31  1.0486  1.8480

I import and create a DataFrame and save to CSV by doing this:

df_file = '2017.csv'
df.to_csv(df_file, index=False)
files.download(df_file)

When I view the CSV, I see this:

EST_TIME    Open    High
2017-01-01  1.0482  1.12   
2017-01-02  1.0483  1.123
2017-01-03  1.0485  1.0521
2017-01-04  1.048   1.6483
2017-01-05  1.048   1.7401
...., ...., ....
2017-12-31  1.0486  1.848

All the zeros at the end are gone. I want to preserve the trailing zeros when I save the CSV and I want it at 4 decimal place.

Could you please let me know how can I achieve this?

Vitek

I assume this is contingent on the behaviour of your CSV reader when reading floats, to keep trailing zeros you'll have to convert the column(s) to strings and slice it in the manner you want. – Handedness

Answer 1

You could call apply and use format as such before writing to csv:

df_file = '2017.csv'
df['Open'] = df['Open'].apply('{:0<6}'.format)
df['High'] = df['High'].apply('{:0<6}'.format)
df.to_csv(df_file, index=False)

To apply to all columns, you can use applymap:

df = df.applymap('{:0<6}'.format)

Hope this helps.

Hutchison

That helps. Can we do it automatically for all the columns if I have other columns besides Open and High? – Vitek

Answer 2

Try this: Float format both to display your data with 4 decimal places and to save it with 4 decimal.

when reading to pandas:

pd.options.display.float_format = '{:,.4f}'.format

when saving to CSV.

df.to_csv('your_file.csv', float_format='%.4f',index=False)

Cornwall