Python append column header & append column values from list to csv

Question

I am trying to append column header (hard-coded) and append column values from list to an existing csv. I am not getting the desired result.

Method 1 is appending results on an existing csv file. Method 2 clones a copy of existing csv into temp.csv. Both methods don't get me the desired output I am looking for. In Results 1, it just appends after the last row cell. In results 2, all list values append on each row. Expected results is what I am looking for.

I have included my code below. Appreciate any input or guidance.

Existing CSV Test.csv

Type,Id,TypeId,CalcValues
B,111K,111Kequity(long) 111K,116.211768
C,111N,B(long) 111N,0.106559957
B,111J,c(long) 111J,20.061634

Code - Method 1 & 2

final_results = ['0.1065599566767107', '0.0038113334533441123', '20.061623176440904']

# Method1
csvfile = "test.csv"
with open(csvfile, "a") as output:
    writer = csv.writer(output, lineterminator='\n')
    for val in final_results:
        writer.writerow([val])  

# Method2
with open("test.csv", 'rb') as input, open('temp.csv', 'wb') as output:
    reader = csv.reader(input, delimiter = ',')
    writer = csv.writer(output, delimiter = ',')

    all = []
    row = next(reader)
    row.insert(5, 'Results')
    all.append(row)

    for row in reader:
        for i in final_results:
            print type(i)
            row.insert(5, i)
        all.append(row)
    writer.writerows(all)

Results for Method 1

Type,Id,TypeId,CalcValues
B,111K,111Kequity(long) 111K,116.211768
C,111N,B(long) 111N,0.106559957
B,111J,c(long) 111J,20.0616340.1065599566767107
0.0038113334533441123
20.061623176440904

Results for Method 2

Type,Id,TypeId,CalcValues,Results
B,111K,111Kequity(long) 111K,116.211768,0.1065599566767107,20.061623176440904,0.0038113334533441123
C,111N,B(long) 111N,0.106559957,0.1065599566767107,20.061623176440904,0.0038113334533441123
B,111J,c(long) 111J,20.061634,0.1065599566767107,20.061623176440904,0.0038113334533441123

Expected Result

Type,Id,TypeId,CalcValues,ID
B,111K,111Kequity(long) 111K,116.211768,0.1065599566767107
C,111N,B(long) 111N,0.106559957,20.061623176440904
B,111J,c(long) 111J,20.061634,0.0038113334533441123

Forgot to mention as I am limited to using csv reader or built in python libraries. Thanks. — Mihir Patel
– Mihir Patel, Commented Sep 28, 2016 at 18:42
Good question, but the values seem wrong: final_results seems not in the same order as the expected input. And the Result column has turned to ID. Can you edit it? — Jean-François Fabre
– Jean-François Fabre ♦, Commented Sep 28, 2016 at 18:54

Jean-François Fabre · Accepted Answer · 2016-09-28 21:07:16Z

1

First method is bound to fail: you don't want to add new lines but new columns. So back to second method:

You insert the title OK, but then you're looping through the results on each row, whereas you need to iterate on them.

For this, i create an iterator from the final_results list (with __iter__()), then I call it.next and append to each row (no need to insert in the end, just append)

I removed the all big list, because 1) you can write one line at a time, saves memory, and 2) all is a predefined function. Avoid to use that as a variable.

final_results = ['0.1065599566767107', '0.0038113334533441123', '20.061623176440904']

# Method2
with open("test.csv", 'rb') as input, open('temp.csv', 'wb') as output:
    reader = csv.reader(input, delimiter = ',')
    writer = csv.writer(output, delimiter = ',')


    row = next(reader)  # read title line
    row.append("Results")
    writer.writerow(row)  # write enhanced title line

    it = final_results.__iter__()  # create an iterator on the result

    for row in reader:
        if row:  # avoid empty lines that usually lurk undetected at the end of the files
            try:
                row.append(next(it))  # add a result to current row
            except StopIteration:
                row.append("N/A")     # not enough results: pad with N/A
            writer.writerow(row)

result:

Type,Id,TypeId,CalcValues,Results
B,111K,111Kequity(long) 111K,116.211768,0.1065599566767107
C,111N,B(long) 111N,0.106559957,0.0038113334533441123
B,111J,c(long) 111J,20.061634,20.061623176440904

Note: had we included "Results" in the final_results variable, we wouldn't even have needed to process first line differently.

Note2: the values seem wrong: final_results seems not in the same order as the expected output. And the Result column has turned to ID, but that's easy to correct.

edited Sep 28, 2016 at 21:07

answered Sep 28, 2016 at 18:46

Jean-François Fabre♦

141k24 gold badges179 silver badges246 bronze badges

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

7 Comments

Mihir Patel Over a year ago

I get stopiteration error. How do I avoid that with your solution?

Jean-François Fabre Over a year ago

I have added a protection against last empty line in my code. Problem is: if your result list is smaller than the number of rows, you get StopIterationError (not enough values in the iterator). I've edited to protect against that too.

Mihir Patel Over a year ago

Still getting the same error on line row.append("Results")

Jean-François Fabre Over a year ago

you cannot have stopiteration exception on row.append("Results"). Post full stacktrace.

Jean-François Fabre Over a year ago

you're welcome. Whe you get StopIteration, this is because you reached the end of the "list". Print the values issued by next() and you'll see what is happening.

|

Haifeng Zhang · Accepted Answer · 2016-09-28 19:01:00Z

import csv

HEADER = "Type,Id,TypeId,CalcValues,ID"
final_results = ['0.1065599566767107', '20.061623176440904', '0.0038113334533441123']

with open("test.csv") as inputs, open("tmp.csv", "wb") as outputs:
    reader = csv.reader(inputs, delimiter=",")
    writer = csv.writer(outputs, delimiter=",")

    reader.next()  # ignore header line
    writer.writerow(HEADER.split(","))  

    for row in reader:
        writer.writerow(row + [final_results.pop(0)])

I store the header fields into HEADER and switch 2nd and 3rd elements of final_results, use pop(0) to remove and return the first element of final_results

output:

Type,Id,TypeId,CalcValues,ID
B,111K,111Kequity(long) 111K,116.211768,0.1065599566767107
C,111N,B(long) 111N,0.106559957,20.061623176440904
B,111J,c(long) 111J,20.061634,0.0038113334533441123

Collectives™ on Stack Overflow

Python append column header & append column values from list to csv

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related