Writing list of tuples as a CSV file

Question

I got a problem with writing a csv file in way that i want.

Description:

I got 3 lists:

list1=[1,2,3]

list2=['dd1','dd2','dd3']

list3=['vi1','vi2','vi3']

I want to write my csv file like:

      Column 1| Column 2| Column 3
Row 1        1|      dd1|      vi1
Row 2        2|      dd2|      vi2
Row 3        3|      dd3|      vi3

So i use zip method like this:

csvd = zip(list1, list2, list3)

After zipping i have:

csvd = [(1, 'dd1', 'vi1'), (2, 'dd2', 'vi2'), (3, 'dd3', 'vi3')]

And next i use csv writer like this:

with open('path\\test.csv', 'w') as f:
     writer = csv.writer(f, delimiter='\t')
     writer.writerows(csvd)

This is my output: csv

First thing is that, there is every touple in first column (i want each element of touple to be in different column). Second problem is that i got an empty lines between nexts touples.

I tried to solve my problem with just writerow and for loop but this is only solution for my second problem, with this method i still got every touple in first column.

I think you might be reading your csv file from excel with the wrong delimiter. — Iron Fist, Apr 05 '16 at 09:10

score 5 · Accepted Answer · answered Apr 05 '16 at 09:06

Not sure about the blank rows but it could be because of the line terminator. Explicitly setting it to '\n' could help.

Regarding the column problem in Excel: Excel uses ';' as column separator by default. That can be changed in Excel, but you could also change the character to use for column separator.

This writer call will change the line terminator and the column separator:

writer = csv.writer(f, delimiter=';', lineterminator='\n')

See if that helps.

score 0 · Answer 2 · answered Apr 05 '16 at 08:46

0

Try this:

with open('path\\test.csv', 'wb') as f:

answered Apr 05 '16 at 08:46

bananafish

2,877
20
29

after changing 'w' into 'wb', empty lines are gone, but i still got all elements of touple in first column – K.Maj Apr 05 '16 at 08:56
@K.Maj Hmm, I can't reproduce that, my file is tab-delimited with this change. If you use `,` as the delimiter, do you get the same problem? It's not just an excel import issue? – bananafish Apr 05 '16 at 09:07
That 'writer = csv.writer(f, delimiter=';', lineterminator='\n')' solve my problem. – K.Maj Apr 05 '16 at 09:19
@K.Maj right, if you don't actually care about having tab as the separator, there is no reason to use it. I'd suggest comma myself, but ; is good too I suppose. I think the csv was fine, but the Excel import wasn't done right. – bananafish Apr 05 '16 at 09:30

Simon · Answer 3 · 2016-04-05T09:14:51.903

You just need to loop over your rows and columns when you're writing out to the CSV:

with open('test.csv', 'w') as f:
     writer = csv.writer(f, delimiter='\t')
     for row in csvd:
         for i, column in enumerate(row):
             f.write(str(column))

             if i != len(row)-1:
                 f.write(',')
         f.write('\n')

The above will give you a csv file with:

1,dd1,vi1
2,dd2,vi2
3,dd3,vi3

EDIT:

Can't believe I didn't notice this, but your whitespacing is because you've set the writer to tab delimited. Just change your original code to:

with open('test.csv', 'wb') as f:
     writer = csv.writer(f, delimiter=',')
     writer.writerows(csvd)

With your original code, the raw output file actually looks like this:

1   dd1 vi1

2   dd2 vi2

3   dd3 vi3

Which when opened in excel, will look the way you posted in your image

So by setting the delimiter to , the raw file will look like my original output above. And in excel it will look normal (rows and columns, no white spaces):

Does not seem to solve the formatting in Excel – dvaergiller Apr 05 '16 at 09:08 — dvaergiller, Apr 05 '16 at 09:08

Writing list of tuples as a CSV file

3 Answers3