Have a csv file called 'data.csv' in the following format:
test1,test2,test3
1,2,3
4,5,6
7,8,9
Given a list in the format ['test4', 4, 7, 10], how can I create a new csv file 'adjusted.csv' with all the data from data.csv and the added column like so:
test1,test2,test3, test4
1,2,3,4
4,5,6,7
7,8,9,10
read lines in
with open('data.csv', 'r') as fi:
lines = [[i.strip() for i in line.strip().split(',')] \
for line in fi.readlines()]
col = ['test4', 4, 7, 10]
Concatenate each row with corresponding element of col. Using enumerate to help keep track of which list index to use.
new_lines = [line + [str(col[i])] for i, line in enumerate(lines)]
Output to file
with open('adjusted.csv', 'w') as fo:
for line in new_lines:
fo.write(','.join(line) + '\n')
I would just treat the csv like the raw text it is. Load in each line, strip off the line break, append the new entry, then put the line break back. This only works if the entries in test4 are guaranteed to be in the same order as the rows in data.csv.
If instead test4 needs to be added to rows based on meeting certain conditions, that would change things a lot. In that case you would probably want to turn both into Pandas dataframes, then perform a proper merge on the required conditions.
test4 = ['test4', 4, 7, 10]
with open(data.csv, 'r') as ifile
with open(adjusted.csv, 'w') as ofile:
for line, new in zip(ifile, test4):
new_line = line.rstrip('\n') + ',' + str(new) + '\n'
ofile.write(new_line)
You can also condense the first two lines into this:
with open(data.csv, 'r') as ifile, open(adjusted.csv, 'w') as ofile:
Do whichever reads more clearly.
Since you're working with csv files use the csv readers and writers to improve readability:
import csv
new_data = ['test4', 4, 7, 10]
with open(r'data.csv', 'r') as in_csv, open(r'adj_data.csv', 'w') as out_csv:
reader = csv.reader(in_csv)
writer = csv.writer(out_csv)
for row, new_col in zip(reader, new_data):
row.append(new_col)
writer.writerow(row)
