CODEWITHSUNDEEP
pythoncsv
tonemgub
tonemgub

Reputation: 35

How to concatenate two columns and update the csv in python?

In Excel, I would be able to use something like this:

=CONCATENATE(A1," ",B1)

(User flood fill's this down the spreadsheet, and then simply deletes A and B, lastly you move results to A)

End Result is expected to have A and B columns merged into one column (A) and separated by one space.

In Python here is what I have so far:

import csv
with open("test.csv","rb") as source:
    rdr= csv.reader( source )
    with open("result","wb") as result:
        wtr= csv.writer( result )
        for r in rdr:
            print("Adding merged COL1/COL2 into one column for every row...")
            wtr.writerow(r+[r[0] + ' ' + r[1]])
            print("Deleting COL1/COL2 columns from every row...")
            del r[0]
            del r[0]
            wtr.writerow( r )
    result.close();

Although, the above code does properly merge two columns and append a column to the end of the file, it does not properly delete the first two rows once it is finished, I believe this is because wtr.writerow has already been called. Also, I am unsure how to move the column to the left (back to A), since it always appends to the end.

Upvotes: 1

Views: 27641

Answers (2)

Moses Koledoye
Moses Koledoye

Reputation: 78554

You're calling writerow twice in that loop. I don't think you want that - writing the merged columns and then the others separately? No.

You can simply join the merged columns to the start of the rows, and slice off the old rows:

wtr.writerow([r[0] + ' ' + r[1]] + r[2:])
#            ^<- merged rows are added to the start
#                                   ^<- slice off old A1 and B1

You won't need the del statements any longer, and one call to writerow inserts your modified row:

import csv 

with open("test.csv","rb") as source, open("result","wb") as result:
    rdr = csv.reader(source)
    wtr = csv.writer(result)
    for r in rdr:
        wtr.writerow([r[0] + ' ' + r[1]] + r[2:])

Also, note that you can put multiple expressions after a with statement, like I did above.

Finally, you don't need to manually close the file - result.close(). The with context already manages that.

Upvotes: 3

Nick Marinakis
Nick Marinakis

Reputation: 1848

So every row you're reading from (and writing to) csv is a list, right? So take another step or two and create the list you want, then write it.

e.g.

import csv
with open('test.csv') as f:
    reader = csv.reader(f)
    with open('output.csv', 'w') as g:
        writer = csv.writer(g)
        for row in reader:
            new_row = [' '.join([row[0], row[1]])] + row[2:]
            writer.writerow(new_row)

Also, I doubt you need to read/write binary ('rb', 'wb') from a csv.

Upvotes: 3

Related Questions