How to add data to the new column in existing CSV using Python script

Question

Team,

2 things i am trying to do as per below code

1) write data in column 2,3,4 and 8 of file1 to a new file.
2) data in 1st column(copied in new file)should be searched file2. if found,pick the data in 3rd column of same row of file 2 and write the same to the new column of new file.

point 1 is working fine..finding issue in getting output as per 2nd point

import csv         


f1 = csv.reader(open("C:/Users/file1.csv","rb"))  
f2 = csv.writer(open("C:/Users/newfile.csv","wb"))  
f3 = csv.reader(open("C:/Users/file2.csv","rb"))

for row in f1:

    if not row[0].startswith("-"):

        f2.writerow((row[1],row[2],row[3],row[7]))

        var1 = row[1]

        for row in f3:

                if var1 in row:

                    f2.append(row[2])

the value to be searched in f3 can be anywhere? Not in a particular column? — DocZerø
– DocZerø, Commented Aug 10, 2016 at 11:19
the value to be searched in f3 will be in 2nd column.and if it is found then data in 3rd column of same row should be picked and copied in next column in new file — Pkde
– Pkde, Commented Aug 10, 2016 at 11:25

DocZerø · Accepted Answer · 2016-08-10 11:34:50Z

There are several issues with your code:

you're opening all files in binary mode.
for each row in f1, you're (potentially) iterating over all rows in f3. This will decrease your performance.
Once you iterate through all rows in f3, you're at the end of the file and the next time the iteration will not return any rows.

Here's my suggestion (not tested):

# Create lookup from f3
lookup = {}

with open('C:/Users/file2.csv', newline='') as f3:
    csv_f3 = csv.reader(f3)
    for row in csv_f3:
        lookup[row[1]] = row[2]

# Process the rows in f1
with open('C:/Users/file1.csv', newline='') as f1:
    with open('C:/Users/newfile.csv', 'w', newline='') as f2:
        csv_f1 = csv.reader(f1)
        csv_f2 = csv.writer(f2)
        for row in csv_f1:
            if not row[0].startswith("-"):
                try:
                    csv_f2.writerow(row[1],row[2],row[3],row[7],lookup[row[1]])
                except KeyError:
                    csv_f2.writerow(row[1],row[2],row[3],row[7])

Andrew · Accepted Answer · 2016-08-11 09:31:37Z

0

I suspect that your re-use of the "row" variable name in the second for loop is clobbering the one held in "var1". I would always steer clear of that kind of recycling of variable names in nested loops.

for row_f1 in f1:

    if not row_f1[0].startswith("-"):

        f2.writerow((row_f1[1],row_f1[2],row_f1[3],row_f1[7]))

        var1 = row_f1[1]

        for row_f3 in f3:

            if var1 in row_f3:

                f2.append(row_f3[2])

However, I don't know that that append would do what you need, as f2 will have no append method, as far as I can see. From your use of .append, it looks like you wanted to put the elements from f1 into a list

for row_f1 in f1:

    if not row_f1[0].startswith("-"):

        temp_list = [row_f1[1],row_f1[2],row_f1[3],row_f1[7]]

        for row_f3 in f3:

            if temp_list[0] in row_f3:

                temp_list.append(row_f3[2])
        f2.writerow(temp_list]

Though your explanation of what you want to achieve isn't completely clear to me.

EDIT: I think Kristof's solution is far better, I was just trying to arrive at a solution that required minimal changes to your existing code. If you provided an example of what you expect your output to be with given inputs, that would definitely clarify things.

edited Aug 11, 2016 at 9:31

answered Aug 10, 2016 at 11:25

Andrew

1,0821 gold badge7 silver badges15 bronze badges

1 Comment

Pkde Over a year ago

@ Andrew ,@Andrew , as per your input i made changes again in my code... i tried to print the output before i put it in csv file...but it is not executing properly as it doesn't give any output. actually as i mentioned above..i am taking data in 2nd column of f1(file1) and will search each one in f3(file2) if it is found,then i have to take the data in 3rd column of file2 and put it in f2(newfile) in a new column in f2(newfile)

Pkde · Accepted Answer · 2016-08-11 06:47:02Z

0

@Andrew, i have made change in my code as per your input.But the value in 1st row for row_f1[1] is only going to the 2nd for loop.which means the value for row_f1[1] in other rows are not being considered for the 2nd for loop.

 for row_f1 in f1:                        

     if not row_f1[0].startswith("-"):

     temp_list = [row_f1[1],row_f1[2],row_f1[3],row_f1[7]]

         for row_f3 in f3:

             if temp_list[0] in row_f3:

                 temp_list.append(row_f3[2])

         f2.writerow(temp_list)

answered Aug 11, 2016 at 6:47

Pkde

351 gold badge1 silver badge3 bronze badges

Collectives™ on Stack Overflow

How to add data to the new column in existing CSV using Python script

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related