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.

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

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!

techraf · Accepted Answer · 2016-03-30 01:01:47Z

1

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()

edited Mar 30, 2016 at 1:01

techraf

69.3k30 gold badges211 silver badges215 bronze badges

answered Mar 30, 2016 at 0:40

Tang Jian Ke

323 bronze badges

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

1 Comment

Kyle Over a year ago

Thank you! That's what I wanted. So I just need to strip the \n for every item.

Mark Tolonen · Accepted Answer · 2016-04-01 14:25:56Z

0

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:

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:

edited Apr 1, 2016 at 14:25

answered Mar 30, 2016 at 0:43

Mark Tolonen

181k26 gold badges183 silver badges279 bronze badges

4 Comments

Kyle Over a year ago

Hi I am using Python 2, but I get this instead: two rows. First row only has the first telephone number ie 222 second row has the other 3 numbers 333 444 555 but they are not in one cell and not I couldn't see the commas

Mark Tolonen Over a year ago

@Kyle, you copied the code exactly? With the exact input data shown?

Mark Tolonen Over a year ago

Look at the file in notepad not Excel. But I think I understand now that what you really want is "222,333,444,555" so it shows up in a single cell in Excel. Note the quotes.

Kyle Pennell Over a year ago

I tried this with 70k rows of ingredients list and for some reason, they come out different. It's odd. I find 61942 matches of the search "Aqua" in one and 55143 of "Aqua" in the test.csv file...

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related