CODEWITHSUNDEEP
pythonpython-2.7csvpython-2.x
Iorek
Iorek

Reputation: 581

Adding another column to a CSV file w/ python

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

Upvotes: 1

Views: 210

Answers (2)

AChampion
AChampion

Reputation: 30288

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

Upvotes: 3

midori
midori

Reputation: 4837

You need 3 files at the same time, and append only needed column from second file

import csv

date_reader = csv.reader(open('Date.csv', 'rb'))
price_reader = csv.reader(open('Price.csv', 'rb'))
writer = csv.writer(open('NewData.csv', 'wb'))
for date_row in date_reader:
    price_row = price_reader.next()
    writer.writerow(date_row + [price_row[1]])

And the 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

Upvotes: 3

Related Questions