Appending a CSV file with a new column in python

Question

I am trying to create a clean csv file by merging some of variables together from an old file and appending them to a new csv file.

I have no problem running the data the first time. I get the output I want but whenever I try to append the data with a new variable (i.e. new column) it appends the variable to the bottom and the output is wonky.

I have basically been running the same code for each variable, except changing the groupvariables variable to my desired variables and then using the f2= open('outputfile.csv', "ab") <--- but with an ab for amend. Any help would be appreciated

groupvariables=['x','y']

f2  = open('outputfile.csv', "wb")
writer = csv.writer(f2, delimiter=",")
writer.writerow(("ID","Diagnosis"))

for line in csv_f:
    line = line.rstrip('\n')
    columns  = line.split(",")
    tempname = columns[0]
    tempindvar = columns[1:]

templist = []

for j in groupvariables:
    tempvar=tempindvar[headers.index(j)]
    if tempvar != ".":
        templist.append(tempvar)

newList = list(set(templist))

if len(newList) > 1:
    output = 'nomatch'
elif len(newList) == 0:
    output = "."
else:
    output = newList[0]

tempoutrow = (tempname,output)
writer.writerow(tempoutrow)

f2.close()

Not a real answer, but if you're looking to do anything significant with tabular data, including grouping and serializing to/from CSV consider looking into a library like Pandas — Iguananaut
– Iguananaut, Commented Dec 18, 2013 at 2:48
It's unclear from the code you've provide what you're trying to accomplish (because it doesn't match your description very well). Please provide a SSCCE with sample data. — martineau
– martineau, Commented Dec 18, 2013 at 3:08
Opening a file with mode='a' for append is for adding data starting at end of the file (new lines/rows). To add a column of data to a csv file will generally require appending something to every line of the original file and completely rewriting it. — martineau
– martineau, Commented Dec 18, 2013 at 3:13

Max Noel · Accepted Answer · 2013-12-18 03:07:39Z

2

CSV is a line-based file format, so the only way to add a column to an existing CSV file is to read it into memory and overwrite it entirely, adding the new column to each line.

If all you want to do is add lines, though, appending will work fine.

answered Dec 18, 2013 at 3:07

Max Noel

8,9301 gold badge29 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dstromberg Over a year ago

Well, reading all of it into memory is one way. Another is to write to a tempfile and then os.rename() it afterward.

deepelement Over a year ago

I have solved this problem many times with @dstromberg approach. Batch read each line in file A, transform in memory and append to File B. This keeps memory down to the # of lines in the Batch window.

Valdis Grinbergs · Accepted Answer · 2013-12-18 05:09:24Z

Here is something that might help. I assumed the first field on each row in each csv file is a primary key for the record and can be used to match rows between the two files. The code below reads the records in from one file, stored them in a dictionary, then reads in the records from another file, appended the values to the dictionary, and writes out a new file. You can adapt this example to better fit your actual problem.

import csv
# using python3

db = {}
reader = csv.reader(open('t1.csv', 'r'))
for row in reader:
    key, *values = row
    db[key] = ','.join(values)

reader = csv.reader(open('t2.csv', 'r'))
for row in reader:
    key, *values = row
    if key in db:
        db[key] = db[key] + ',' + ','.join(values)
    else:
        db[key] = ','.join(values)

writer = open('combo.csv', 'w')
for key in sorted(db.keys()):
    writer.write(key + ',' + db[key] + '\n')

Collectives™ on Stack Overflow

Appending a CSV file with a new column in python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related