Use Python to modify specific columns in csv

Question

I have a bunch of data in a .csv like this:

-959.378170,-0.000026,-94.960000,1508.000000,9.000000,
-958.978170,-0.000026,-94.920000,1508.000000,9.000000,
-958.578170,-0.000026,-94.880000,1508.000000,10.000000,
-958.178170,-0.000026,-94.840000,1508.000000,10.000000,
-957.778170,-0.000026,-94.800000,1508.000000,10.000000,

The last two columns are supposed to be time. 15 is the hour, 08 is the minute, 6 is the second. The end goal is to join them so that I get something like:

-958.978170,-0.000026,-94.920000,15:08:09,                
-958.578170,-0.000026,-94.880000,15:08:10,

How can I do that?

Answer 1

I would use a regex and fileinput

import fileinput
import re

# Assume the input file is foo.csv
for line in fileinput.FileInput('foo.csv', inplace=1):
    mm = re.search(r'^(.+?,.+?,.+?,)(\d{1,2})(\d{2})\.0+,(\d{1,2})\.0+',
        line)
    g1, g2, g3, g4 = mm.group(1), int(mm.group(2)), int(mm.group(3)), int(mm.group(4))
    print "%s%02i:%02i:%02i," % (g1, g2, g3, g4)

Running this on the example results in...

-959.378170,-0.000026,-94.960000,15:08:09,
-958.978170,-0.000026,-94.920000,15:08:09,
-958.578170,-0.000026,-94.880000,15:08:10,
-958.178170,-0.000026,-94.840000,15:08:10,
-957.778170,-0.000026,-94.800000,15:08:10,

Answer 2

Use the csv module to read the .csv file (see here for examples), and the datetime.strptime method to parse the two columns into datetime objects, which you can then write out to whatever format you'd like (using datetime.strftime).

See this section of the datetime docs for more details.