Need to generate csv output in python as below

Question

import csv
d={'a':[1,3,4],'b':[2,3,4],'c':[1]}
with open("result.csv","w") as csv_file:
   csv_writer = csv.writer(csv_file,dialect='excel')

   csv_writer.writerows(d.items())

need to get the output as below:

That isn't really a .csv file. You are trying to achieve what in a spreadsheet would be called *merged cells*. A .csv is a flat file with the same number of columns in every row, and no cell suppressed just because it matches the one above it. — BoarGules, May 14 '18 at 14:39

score 1 · Accepted Answer · answered May 14 '18 at 14:39

You need to iterate over your input dictionary; you don't have input rows at the ready here.

For each key-value pair in your input dictionary, you have multiple rows. The first row consists of the key, and the first value, and then you have more rows for the remaining values with the first column empty.

You need to write those rows explicitly:

csv_writer = csv.writer(csv_file, dialect='excel')
for key, values in d.items():
    csv_writer.writerow((key, values[0]))
    csv_writer.writerows([(None, value) for value in values[1:]])

I used None for the empty column value, that results in an empty column in the output:

>>> import csv
>>> from io import StringIO
>>> output = StringIO()
>>> d = {'a': [1, 3, 4], 'b': [2, 3, 4], 'c': [1]}
>>> csv_writer = csv.writer(output, dialect='excel')
>>> for key, values in d.items():
...     csv_writer.writerow((key, values[0]))
...     csv_writer.writerows([(None, value) for value in values[1:]])
...
5
5
5
>>> print(output.getvalue())
a,1
,3
,4
b,2
,3
,4
c,1

You probably want to add a sorted() call to the d.items() loop; otherwise your output will be in dictionary order, which is arbitrary based on insertion and deletion history, at least before Python 3.6.

Jean-François Fabre · Answer 2 · 2018-05-14T14:46:40.713

First you need to sort your keys to get a,b,c ... (dicts aren't sorted until python 3.6)

Then you have to issue tuples, not the raw dicts. I suggest using itertools.zip_longest with an empty fill value to interleave the key & the values together:

import csv,itertools
d={'a':[1,3,4],'b':[2,3,4],'c':[1]}
with open("result.csv","w",newline="") as csv_file:
    csv_writer = csv.writer(csv_file,dialect='excel',delimiter=",") # or ";"
    for k,v in sorted(d.items()):
        csv_writer.writerows(itertools.zip_longest([k],v,fillvalue=""))

creates the following raw format:

a,1
,3
,4
b,2
,3
,4
c,1

if you need merged cells instead, just join the integer values with newline:

csv_writer.writerows([k,"\n".join(map(str,v))] for k,v in sorted(d.items()))

outputs:

a,"1
3
4"
b,"2
3
4"
c,1

in excel (you may want to change the delimiter to ";" depending on the versions)

Need to generate csv output in python as below

2 Answers2