How do I write data to csv file in columns and rows from a list in python?

Question

I have a list of lists and I want to write them in a csv file with columns and rows. I have tried to use writerows but it isn't what I want. An example of my list is the following:

[[1, 2], [2, 3], [4, 5]]

With this:

example = csv.writer(open('test.csv', 'wb'), delimiter=' ')
example.writerows([[1, 2], [2, 3], [4, 5]])

I get 1 2 in a cell, 2 3 in a cell, etc. And not 1 in a cell and 2 in the next cell.

I need to write this example list to a file so when I open it with Excel every element is in its own cell.

My output should be like this:

1 2
2 3
4 5

Each element in a different cell.

"in a cell" doesn't have much meaning in csv world. In any case, your code *DOES* do what you say you want. How are you displaying the output file?? — John Machin, Sep 23 '11 at 13:03

score 16 · Accepted Answer · answered Sep 23 '11 at 13:30

16

The provided examples, using csv modules, are great! Besides, you can always simply write to a text file using formatted strings, like the following tentative example:

l = [[1, 2], [2, 3], [4, 5]]

out = open('out.csv', 'w')
for row in l:
    for column in row:
        out.write('%d;' % column)
    out.write('\n')
out.close()

I used ; as separator, because it works best with Excell (one of your requirements).

Hope it helps!

answered Sep 23 '11 at 13:30

heltonbiker

26,657
28
137
252

2

You have an extra ; at the end of each line. Writing a cell at a time is clunky. A ; works best with Excel **ONLY** in locales that use a comma as a decimal point. Why show the OP this DIY stuff when all he needs is a bit of tweak to his call to `csv.writer`?? – John Machin Sep 23 '11 at 13:53
1

@John Machin: some other reader might prefer this approach. I doubt the OP would go back do DIY if he is already using `csv`. But I think SO is a place where you can learn new ways of doing things, and choose among them. Also, with the string-formatting operation you could also use your preferred field separator, end of line, etc. – heltonbiker Sep 23 '11 at 14:16

John Machin · Answer 2 · 2011-09-23T13:32:45.267

>>> import csv
>>> with open('test.csv', 'wb') as f:
...     wtr = csv.writer(f, delimiter= ' ')
...     wtr.writerows( [[1, 2], [2, 3], [4, 5]])
...
>>> with open('test.csv', 'r') as f:
...     for line in f:
...         print line,
...
1 2 <<=== Exactly what you said that you wanted.
2 3
4 5
>>>

To get it so that it can be loaded sensibly by Excel, you need to use a comma (the csv default) as the delimiter, unless you are in a locale (e.g. Europe) where you need a semicolon.

score 3 · Answer 3 · answered Sep 23 '11 at 12:46

Well, if you are writing to a CSV file, then why do you use space as a delimiter? CSV files use commas or semicolons (in Excel) as cell delimiters, so if you use delimiter=' ', you are not really producing a CSV file. You should simply construct csv.writer with the default delimiter and dialect. If you want to read the CSV file later into Excel, you could specify the Excel dialect explicitly just to make your intention clear (although this dialect is the default anyway):

example = csv.writer(open("test.csv", "wb"), dialect="excel")

score 2 · Answer 4 · answered Nov 24 '14 at 15:29

Have a go with these code:

>>> import pyexcel as pe
>>> sheet = pe.Sheet(data)
>>> data=[[1, 2], [2, 3], [4, 5]]
>>> sheet
Sheet Name: pyexcel
+---+---+
| 1 | 2 |
+---+---+
| 2 | 3 |
+---+---+
| 4 | 5 |
+---+---+
>>> sheet.save_as("one.csv")
>>> b = [[126, 125, 123, 122, 123, 125, 128, 127, 128, 129, 130, 130, 128, 126, 124, 126, 126, 128, 129, 130, 130, 130, 130, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 132, 134, 134, 134, 134, 134, 134, 134, 134, 133, 134, 135, 134, 133, 133, 134, 135, 136], [135, 135, 136, 137, 137, 136, 134, 135, 135, 135, 134, 134, 133, 133, 133, 134, 134, 134, 133, 133, 132, 132, 132, 135, 135, 133, 133, 133, 133, 135, 135, 131, 135, 136, 134, 133, 136, 137, 136, 133, 134, 135, 136, 136, 135, 134, 133, 133, 134, 135, 136, 136, 136, 135, 134, 135, 138, 138, 135, 135, 138, 138, 135, 139], [137, 135, 136, 138, 139, 137, 135, 142, 139, 137, 139, 138, 136, 137, 141, 138, 138, 139, 139, 139, 139, 138, 138, 138, 138, 137, 137, 137, 137, 138, 138, 136, 137, 137, 137, 137, 137, 137, 138, 148, 144, 140, 138, 137, 138, 138, 138, 137, 137, 137, 137, 137, 138, 139, 140, 141, 141, 141, 141, 141, 141, 141, 141, 141], [141, 141, 141, 141, 141, 141, 141, 139, 139, 139, 140, 140, 141, 141, 141, 140, 140, 140, 140, 140, 141, 142, 143, 138, 138, 138, 139, 139, 140, 140, 140, 141, 140, 139, 139, 141, 141, 140, 139, 145, 137, 137, 145, 145, 137, 137, 144, 141, 139, 146, 134, 145, 140, 149, 144, 145, 142, 140, 141, 144, 145, 142, 139, 140]]
>>> s2 = pe.Sheet(b)
>>> s2
Sheet Name: pyexcel
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| 126 | 125 | 123 | 122 | 123 | 125 | 128 | 127 | 128 | 129 | 130 | 130 | 128 | 126 | 124 | 126 | 126 | 128 | 129 | 130 | 130 | 130 | 130 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 132 | 134 | 134 | 134 | 134 | 134 | 134 | 134 | 134 | 133 | 134 | 135 | 134 | 133 | 133 | 134 | 135 | 136 |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| 135 | 135 | 136 | 137 | 137 | 136 | 134 | 135 | 135 | 135 | 134 | 134 | 133 | 133 | 133 | 134 | 134 | 134 | 133 | 133 | 132 | 132 | 132 | 135 | 135 | 133 | 133 | 133 | 133 | 135 | 135 | 131 | 135 | 136 | 134 | 133 | 136 | 137 | 136 | 133 | 134 | 135 | 136 | 136 | 135 | 134 | 133 | 133 | 134 | 135 | 136 | 136 | 136 | 135 | 134 | 135 | 138 | 138 | 135 | 135 | 138 | 138 | 135 | 139 |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| 137 | 135 | 136 | 138 | 139 | 137 | 135 | 142 | 139 | 137 | 139 | 138 | 136 | 137 | 141 | 138 | 138 | 139 | 139 | 139 | 139 | 138 | 138 | 138 | 138 | 137 | 137 | 137 | 137 | 138 | 138 | 136 | 137 | 137 | 137 | 137 | 137 | 137 | 138 | 148 | 144 | 140 | 138 | 137 | 138 | 138 | 138 | 137 | 137 | 137 | 137 | 137 | 138 | 139 | 140 | 141 | 141 | 141 | 141 | 141 | 141 | 141 | 141 | 141 |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| 141 | 141 | 141 | 141 | 141 | 141 | 141 | 139 | 139 | 139 | 140 | 140 | 141 | 141 | 141 | 140 | 140 | 140 | 140 | 140 | 141 | 142 | 143 | 138 | 138 | 138 | 139 | 139 | 140 | 140 | 140 | 141 | 140 | 139 | 139 | 141 | 141 | 140 | 139 | 145 | 137 | 137 | 145 | 145 | 137 | 137 | 144 | 141 | 139 | 146 | 134 | 145 | 140 | 149 | 144 | 145 | 142 | 140 | 141 | 144 | 145 | 142 | 139 | 140 |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
>>> s2[0,0]
126
>>> s2.save_as("two.csv")

score -1 · Answer 5 · answered Jul 04 '18 at 07:29

import pandas as pd
header=['a','b','v']
df=pd.DataFrame(columns=header)
for i in range(len(doc_list)):
  d_id=(test_data.filenames[i]).split('\\')
  doc_id.append(d_id[len(d_id)-1])
  df['a']=doc_id
print(df.head())
df[column_names_to_be_updated]=np.asanyarray(data)
print(df.head())
df.to_csv('output.csv')

Using pandas dataframe,we can write to csv. First create a dataframe as per the your needs for storing in csv. Then create csv of the dataframe using pd.DataFrame.to_csv() API.

How do I write data to csv file in columns and rows from a list in python?

5 Answers5

Linked

Related