Ad

Adding Another Column To A CSV File W/ Python

- 1 answer

I am looking to add another column to a CSV file w/ python.

file1 is Date.csv has format

ID, Date
0,"Jan 22, 2016"
1,"Jan 21, 2016"
2,"Jan 20, 2016"
3,"Jan 19, 2016"

and

file2 is Price.csv

ID, Price
0,27.89
1,26.80
2,26.78
3,26.00

My desired output is (in Date.csv)

ID, Date
0,"Jan 22, 2016", 27.89
1, "Jan 21, 2016", 26.80
2, "Jan 20, 2016", 26.78
3, "Jan 19, 2016", 26.00

but what I'm returning is the price repeating

0,27.89,27.89
1,26.80,26.80
2,26.78,26.78
3,26.00,26.00

My program is as follows

import csv

with open('C:/Users/User/OneDrive/Documents/Price.csv','r') as csvinput:
    with open('C:/Users/User/OneDrive/Documents/Date.csv', 'w') as csvoutput:
        writer = csv.writer(csvoutput, lineterminator='\n')
        reader = csv.reader(csvinput)

        all = []
        row = next(reader)

        for row in reader:
            row.append(row[1])
            all.append(row)

        writer.writerows(all)

Appreciate some guidance, cheers

Ad

Answer

You can't just write a single column into an existing file. Best option is create a new file. If the data is in order for both files then you can simply zip them up and write the updated dictionary out:

with open('C:/Users/User/OneDrive/Documents/Date.csv') as file1, \
     open('C:/Users/User/OneDrive/Documents/Price.csv') as file2, \
     open('C:/Users/User/OneDrive/Documents/Output.csv', 'w') as output:
    reader1 = csv.DictReader(file1)
    reader2 = csv.DictReader(file2)
    writer = csv.DictWriter(output, ['ID', 'Date', 'Price'])
    writer.writeheader()  # Optional if you want the header

    for row1, row2 in zip(reader1, reader2):
        row1.update(row2)
        writer.writerow(row1)

Pandas is also a another option:

import pandas as pd
file1 = pd.read_csv('Data.csv', index_col='ID')
file2 = pd.read_csv('Price.csv', index_col='ID')
pd.concat([file1,file2], axis=1).to_csv('Output.csv')

Output:

ID,Date,Price
0,"Jan 22, 2016",27.89
1,"Jan 21, 2016",26.80
2,"Jan 20, 2016",26.78
3,"Jan 19, 2016",26.00
Ad
source: stackoverflow.com
Ad