Mihir Patel Mihir Patel - 2 months ago 8
Python Question

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

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

Answer

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.

Comments