Overwrite A Specific Column In A Csv File Using Python Csv Module

I am using Python csv module to read a csv file with every line being like: 2013-04-16 7:11:01,186744,3,2,2,1.89E-03 I then convert row[0] to unix time but then I want to replace

Solution 1:

First of all, there are better ways to convert a textual date-time format into a UNIX timestamp. Direct use of the time module simplifies your code to:

import time
importcalendartimestamp= calendar.gmtime(time.strptime(row[0], '%Y-%m-%d %H:%M:%S'))

but even the datetime object you created has .timetuple() and .utctimetuple() methods that would be miles more reliable at producing a time_struct tuple than parsing the string format of the datetime object back to a tuple of integers. You may as well do that directly on row[0] as the output of str(datetime.now())is the same format as what you started with.

Next, write out a new file and replace the old one with it once done:

import csv
import time
import calendar
import os

withopen('FakeAPData.csv', 'rb') as infile, open('FakeAPData.csv.new', 'wb') as outfile:
    writer = csv.writer(outfile)
    for row in csv.reader(infile):
        timestamp = calendar.gmtime(time.strptime(row[0], '%Y-%m-%d %H:%M:%S'))
        writer.writerow([timestamp] + row[1:])

os.rename('FakeAPData.csv.new', 'FakeAPData.csv')

Solution 2:

Each row is just a list. You can modify it in-place, or create a new list with the value you want substituted out:

row[0] = y # orrow= [y] +row[1:], or ...

If you want to write it back to a file, you need to use a csv.writer for that. For example:

os.rename('FakeAPData.csv', 'FakeAPData.csv.bak')

csv_in =open('FakeAPData.csv.bak', 'rb')
csv_out =open('FakeAPData.csv', 'wb')

writer = csv.writer(csv_out)

forrowin csv.reader(csv_in):
    date= datetime.datetime.strptime(row[0], '%Y-%m-%d %H:%M:%S')
    datet = unicode(datetime.datetime.strptime(row[0], '%Y-%m-%d %H:%M:%S'))
    datett = tuple(int(v) for v in re.findall("[0-9]+", datet))
    y = calendar.timegm(datett)
    row[0] = y
    writer.writerow(row)

---

Of course you'll also want to close your files, and clean up all the repeated and unused code. While we're at it, I'd factor out the date-transforming code into a function. And use functions that make it easy, instead of ones that make it difficult and fragile.

So:

def transform_date(date):
    return calendar.gmtime(datetime.strptime(date, '%Y-%m-%d %H:%M:%S').timetuple())

def transform_row(row):
    return [transform_date(row[0])] + row[1:]

name = 'FakeAPData.csv'
bakname = name + '.bak'
os.rename(name, bakname)
with open(bakname, 'rb') as in csv_in, open(name, 'wb') as csv_out:
    writer = csv.writer(csv_out)
    writer.writerows(transform_row(row) for row in csv.reader(csv_in))