Home Ask Login Register

Developers Planet

Your answer is one click away!

NewtoPython February 2016

format to 3 decimal places all decimal number in csv file python

index,Adj Close,Close,High,Low,Open,SMA_100,SMA_20,SMA_5,SMA_50,Volume,code,date_of_trade
0,2.8999999999999999,2.8999999999999999,2.9700000000000002,2.8300000000000001,2.9300000000000002,3.8003000000000009,3.2214999999999998,3.1319999999999992,3.0767999999999969,631100,1PG,2016-02-05 00:00:00

The above is my file. What I want to do is limit all decimal number to 3 decimal places( like 2.333) and write to another file . I am using python to read file. Is there anyway of doing it at file level rather at each column level? If it need to be done at column level what is the best way of doing it?


alpha1554 February 2016

If you want correct rounding, you will have to read each column and convert it to an integer. If you keep it as a string, best you can do is truncating the number to 3 digits after the decimal point.

In order to round, you should use the round primitive (see here).

If truncating is enough, you will still have to read the file line by line and write the output to a new one, but you could use a regex. In essence, what you'll want to do is :

import re

for line in file:
    newline = re.sub(r"(\d\.\d{3})(\d*)", r"\1", line)
    print(newline, file=newfile)

The first solution will give this :

index,Adj Close,Close,High,Low,Open,SMA_100,SMA_20,SMA_5,SMA_50,Volume,code,date_of_trade
 0,2.9,2.9,2.97,2.83,2.93,3.8,3.221,3.132,3.0768,631100,1PG,2016-02-05 00:00:00

And the second one this :

index,Adj, Close,Close,High,Low,Open,SMA_100,SMA_20,SMA_5,SMA_50,Volume,code,date_of_trade
0,2.899,2.899,2.970,2.830,2.930,3.800,3.221,3.131,3.076,631100,1PG,2016-02-05 00:00:00

Martin Evans February 2016

You could use Python's csv library as follows:

import csv

with open('input.csv', 'rb') as f_input, open('output.csv', 'wb') as f_output:
    csv_input = csv.reader(f_input)
    csv_output = csv.writer(f_output)
    csv_output.writerow(next(csv_input))    # write header

    for cols in csv_input:
        for i in xrange(1, 10):
            cols[i] = '{:.3f}'.format(float(cols[i]))

This would give you an output file as follows:

index,Adj Close,Close,High,Low,Open,SMA_100,SMA_20,SMA_5,SMA_50,Volume,code,date_of_trade
0,2.900,2.900,2.970,2.830,2.930,3.800,3.221,3.132,3.077,631100,1PG,2016-02-05 00:00:00

Post Status

Asked in February 2016
Viewed 2,448 times
Voted 7
Answered 2 times


Leave an answer

Quote of the day: live life