Search code examples
pythoncsvdatetimewriter

Python- Can't overwrite first column of .csv file with new time stamp

I have a .csv file (see image): enter image description here

In the image there is a time column with datetime strings, I have a program that takes this column and only reads the times H:M:S. Yet, not only in my program I am attempting to take the column to read only the time stamp H:M:S , but I am also attempting to overwrite the time column of the first file and replace it with only the H:M:S time stamp onto a the new .csv with the following code.

CODE:

import csv
import datetime as dt
import os

File = 'C:/Users/Alan Cedeno/Desktop/Test_Folder/HiSAM1_data_160215_164858.csv'
root, ext = os.path.splitext(File)
output = root + '-new.csv'
with open(File,'r') as csvinput,open(output, 'w') as csvoutput:

    writer = csv.writer(csvoutput, lineterminator='\n')
    reader = csv.reader(csvinput)

    all = []
    row = next(reader)
    for line in reader:
        row.append(dt.datetime.strptime(line[0],'%m/%d/%Y %H:%M:%S').time())
        all.append(row)

    for row in reader:
        row.append(row[0])
        all.append(row)

    writer.writerows(all)

The program works, and takes the datetime strings and overwrites the string with the time stamp H:M:S in a new .csv file. However, here is the problem, the output file instead of replacing the time column it replaced every column obtaining an output file that looks like this. See 2nd image:

enter image description here

At this point I don' t really know how to make the new output file to look like the file of the first image, with the format H:M:S in the first column ONLY, not all scrambled like in the second image. Any suggestions?

SCREENSHOTenter image description here FOR BAH:

See the K column, it should be column A of the first image, and columns B,C,D,E,F,G,I,and J should stay the same like in image 1.

Download LInk of .csv file: http://www.speedyshare.com/z2jwq/HiSAM1-data-160215-164858.csv

Solution

  • The main problem with your code seems that you're keeping appending to the first row the time of each of the line in the csv, which results in the second image posted in the question.

    The idea is to keep track of the different lines and modify just the first element of each line. Also, if you want, you should keep the first line, which indicates the labels of the column. For solving the issue, the code would look like:

    import csv
import datetime as dt
import os

File = 'C:/Users/Alan Cedeno/Desktop/Test_Folder/HiSAM1_data_160215_164858.csv'
root, ext = os.path.splitext(File)
output = root + '-new.csv'
with open(File,'r') as csvinput,open(output, 'w') as csvoutput:

    writer = csv.writer(csvoutput, lineterminator='\n')
    reader = csv.reader(csvinput)

    rows = [next(reader)]
    for line in reader:
        line[0] = str(dt.datetime.strptime(line[0],'%m/%d/%Y %H:%M:%S').time())
        rows.append(line)

    writer.writerows(rows)

    Note the list rows has the modified lines from the csvinput.

    The resulting output csv file (tested with the first line in the question duplicated) would be enter image description here