9

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!

darthbith
  • 18,484
  • 9
  • 60
  • 76
user3641397
  • 91
  • 1
  • 1
  • 3

4 Answers4

9

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)
Martin Tournoij
  • 26,737
  • 24
  • 105
  • 146
Steven Rumbalski
  • 44,786
  • 9
  • 89
  • 119
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.

fred.yu
  • 865
  • 7
  • 10
1

We have a CSV file i.e. data.csv and its contents are:

#data.csv
1,Joi,Python
2,Mark,Laravel
3,Elon,Wordpress
4,Emily,PHP
5,Sam,HTML

Now we want to add a column in this csv file and all the entries in this column should contain the same value i.e. Something text.

Example

from csv import writer
from csv import reader
new_column_text = 'Something text'

with open('data.csv', 'r') as read_object, \
    open('data_output.csv', 'w', newline='') as write_object:
    csv_reader = reader(read_object)
    csv_writer = writer(write_object)
    for row in csv_reader:
        row.append(new_column_text)
        csv_writer.writerow(row)

Output

#data_output.csv
1,Joi,Python,Something text
2,Mark,Laravel,Something text
3,Elon,Wordpress,Something text
4,Emily,PHP,Something text
5,Sam,HTML,Something text
Fefar Ravi
  • 794
  • 7
  • 18
-1

The append mode of opening files is meant to add data to the end of a file. what you need to do is provide random access to your file writing. you need to use the seek() method

you can see and example here: http://www.tutorialspoint.com/python/file_seek.htm

or read the python docs on it here: https://docs.python.org/2.4/lib/bltin-file-objects.html which isn't terribly useful

if you want to add to the end of a column you may want to open the file read a line to figure out it's length then seek to the end.

Jonathan Eskritt
  • 84
  • 4
  • -1. Low level file seeking is the wrong tool for this job. It is problematic because each write would clobber existing data unless it is first shifted to accommodate the new value. Doing this correctly would be fiddly and is likely to be inefficient. – Steven Rumbalski May 15 '14 at 15:43