add a new column to an existing csv file

Question

I have a csv file with 5 columns and I want to add data in a 6th column. The data I have is in an array.

Right now, the code that I have will insert the data I would want in the 6th column only AFTER all the data that already exists in the csv file.

For instance I have:

wind, site, date, time, value
10, 01, 01-01-2013, 00:00, 5.1
89.6    ---> this is the value I want to add in a 6th column but it puts it after all the  data from the csv file

Here is the code I am using:

csvfile = 'filename'
with open(csvfile, 'a') as output:
    writer = csv.writer(output, lineterminator='\n')
    for val in data:
        writer.writerow([val])

I thought using 'a' would append the data in a new column, but instead it just puts it after ('under') all the other data... I don't know what to do!

Answer 1

Appending writes data to the end of a file, not to the end of each row.

Instead, create a new file and append the new value to each row.

csvfile = 'filename'
with open(csvfile, 'r') as fin, open('new_'+csvfile, 'w') as fout:
    reader = csv.reader(fin, newline='', lineterminator='\n')
    writer = csv.writer(fout, newline='', lineterminator='\n')
    if you_have_headers:
        writer.writerow(next(reader) + [new_heading])
    for row, val in zip(reader, data)
        writer.writerow(row + [data])

On Python 2.x, remove the newline='' arguments and change the filemodes from 'r' and 'w' to 'rb' and 'wb', respectively.

Once you are sure this is working correctly, you can replace the original file with the new one:

import os
os.remove(csvfile) # not needed on unix
os.rename('new_'+csvfile, csvfile)

Answer 2

csv module does not support writing or appending column. So the only thing you can do is: read from one file, append 6th column data, and write to another file. This shows as below:

with open('in.txt') as fin, open('out.txt', 'w') as fout:
        index = 0
        for line in fin:
            fout.write(line.replace('\n', ', ' + str(data[index]) + '\n'))
            index += 1

data is a int list.

I test these codes in python, it runs fine.