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?

The python tutorial is a good start. Then take a look at the csv and datetime modules. docs.python.org/tutorial — monkut
– monkut, Commented Aug 1, 2012 at 1:05

Mike Pennington · Accepted Answer · 2012-08-01 01:28:54Z

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,

answered Aug 1, 2012 at 1:28

Mike Pennington

43.2k22 gold badges140 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Julian · Accepted Answer · 2012-07-31 22:53:24Z

1

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.

answered Jul 31, 2012 at 22:53

Julian

3,44918 silver badges29 bronze badges

Comments

Jason Strimpel · Accepted Answer · 2012-08-01 01:39:22Z

1

Check out the read_csv() method in pandas (http://pandas.pydata.org/pandas-docs/stable/io.html#csv-text-files).

It has a great date parsing utility that allows you to bring strings from multiple columns together.

answered Aug 1, 2012 at 1:39

Jason Strimpel

15.7k25 gold badges81 silver badges110 bronze badges

Collectives™ on Stack Overflow

Use Python to modify specific columns in csv

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related