How to merge multiple rows into single cell in csv separated by comma using Python

Question

here is my problem:

in my csv file, I only have one column and multiple rows containing telephone numbers.

    a 
1  222
2  333
3  444
4  555

what I want is merge them into one string and separate by comma, eg:

   a
1  222,333,444,555

The code I am using right now is:

import csv

b = open('test.csv', 'wb')
a = csv.writer(b)

s = ''
with open ("book2.csv", "rb") as annotate:
    for col in annotate:

        ann = col.lower().split(",")
        s += ann[0] + ','
s = s[:-1] # Remove last comma

a.writerow([s])
b.close()

what I get from this is

   a
1   222,
    333,
    444,
    555

All the numbers are in one cell now (good) but they are not on one line (there is /r/n after each telephone number so I think that's why they are not on one line). Thank you in advance!

Answer 1

You're using the csv module, but ignoring csv.reader. It handles all the parsing for you:

#!python2
import csv
with open('book2.csv','rb') as inf, open('test.csv','wb') as outf:
    r = csv.reader(inf)
    w = csv.writer(outf)
    L = [row for row in r] # Read all lines as list of lists.
    L = zip(*L)            # transpose all the lines.
    w.writerows(L)         # Write them all back out.

Input:

222
333
444
555

Output in the .csv file:

222,333,444,555

EDIT: I see now that you want the data in Excel to be in a single cell. The above will put it in a row of four cells:

The following will write a single cell:

#!python2
import csv
with open('book2.csv') as inf, open('test.csv','wb') as outf:
    w = csv.writer(outf)
    data = inf.read().splitlines()
    w.writerow([','.join(data)])

Output in the .csv file:

"222,333,444,555"

Output in Excel:

Answer 2

import csv

b = open('test.csv', 'wb')
a = csv.writer(b)

s = ''
with open ("book2.csv", "rb") as annotate:
    for col in annotate:

        ann = col.lower().strip('\n').split(",")
        s += ann[0] + ','
s = s[:-1] # Remove last comma

a.writerow([s])
b.close()