Adding another column to a CSV file w/ python

Question

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

you have to read from both files, connect rows and write to new file. And at the end you can rename new file to Date.csv — furas
– furas, Commented Jan 26, 2016 at 2:15

AChampion · Accepted Answer · 2016-01-26 02:41:40Z

3

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

edited Jan 26, 2016 at 2:41

answered Jan 26, 2016 at 2:25

AChampion

30.4k4 gold badges63 silver badges79 bronze badges

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

2 Comments

Iorek Over a year ago

Thank you very much. I [partially] resolved this another way with import pandas as pd file1 = pd.read_csv("C:/Users/User/OneDrive/Documents/Date.csv") file2 = pd.read_csv("C:/Users/User/OneDrive/Documents/Price.csv") output = pd.concat([file1,file2], axis=1) output.to_csv("All.csv") and then delete the additional column.

AChampion Over a year ago

You can avoid deleting additional columns in pandas if you set the index_col - see update

midori · Accepted Answer · 2016-01-26 02:14:40Z

3

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

answered Jan 26, 2016 at 2:14

midori

4,8375 gold badges37 silver badges62 bronze badges

4 Comments

AChampion Over a year ago

You could avoid the .next() call with zip, e.g. for date_row, price_row in zip(date_reader, price_reader):. BTW .next() does not work in Py3, use next(price_reader)

midori Over a year ago

i just changed his code a little bit, so it does what it supposed to

Iorek Over a year ago

does using 'rb' and 'wb' as opposed to 'r' and 'w' make a difference?

midori Over a year ago

you can read here difference between using r or rb - docs.python.org/2/tutorial/…

Collectives™ on Stack Overflow

Adding another column to a CSV file w/ python

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related