CODEWITHSUNDEEP
python
user3641397
user3641397

Reputation: 91

add a new column to an existing csv file

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!

Upvotes: 9

Views: 27175

Answers (4)

fred.yu
fred.yu

Reputation: 865

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.

Upvotes: 2

Fefar Ravi
Fefar Ravi

Reputation: 959

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

Upvotes: 2

Steven Rumbalski
Steven Rumbalski

Reputation: 45562

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)

Upvotes: 9

Jonathan Eskritt
Jonathan Eskritt

Reputation: 84

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.

Upvotes: -1

Related Questions