How to convert a list of variable length lists into a CSV file?

Question

I am attacking a problem at work that requires me to convert a list of columns into a unique CSV format. I say unique because I have not seen an explanation on SO for my particular issue. My research steered me toward a couple of similar questions, such as the ones by Sameer Patel and user1403483, which I tried, but the output was incorrect; let me explain.

Here is an example of my data:

animals = [['dog',
            ['a','b','c','d'],
            ['aa','aa'],
            ['bb','bb','bb','bb','bb']],
           ['cat',
            ['a','b','c'],
            [''],
            ['bb','bb']],
           ['bear',
            ['a'],
            ['aa','aa','aa'],
            ['bb']]]

The problem requires that the format of the CSV (when opened in Excel) appear as follows:

+------+------+------+------+
| dog  | a    | aa   | bb   |
+------+------+------+------+
|      | b    | aa   | bb   |
+------+------+------+------+
|      | c    |      | bb   |
+------+------+------+------+
|      | d    |      | bb   |
+------+------+------+------+
|      |      |      | bb   |
+------+------+------+------+
| cat  | a    |      | bb   |
+------+------+------+------+
|      | b    |      | bb   |
+------+------+------+------+
|      | c    |      |      |
+------+------+------+------+
| bear | a    | aa   | bb   |
+------+------+------+------+
|      |      | aa   |      |
+------+------+------+------+
|      |      | aa   |      |
+------+------+------+------+

Is there a Python function in pprint, csv, or pandas to do this, or do I need to create one, to produce the CSV output to create the table?

I wouldn't describe this as "a list of variable length lists". I would say "a list of columns", because that's the essential difficulty here: you want to rotate your data 90°. — larsks, Jan 04 '20 at 15:37

score 1 · Answer 1 · answered Jan 04 '20 at 15:50

I've made some assumptions about the format of your data, since you have failed to include valid Python in your question.

You can use itertools.zip_longest to rotate your columns into rows:

import csv
from itertools import zip_longest

animals = [
    [
        "dog",
        ["a", "b", "c", "d"],
        ["aa", "aa"],
        ["bb", "bb", "bb", "bb", "bb"],
    ],
    [
        "cat",
        ["a", "b", "c"], [""], ["bb", "bb"],
    ],
    [
        "bear", ["a"], ["aa", "aa", "aa"], ["bb"]
    ],
]

allrows = []
for data in animals:
    name = data.pop(0)
    rows = list(zip_longest(*data, fillvalue=''))
    for i in range(len(rows)):
        if i:
            rows[i] = ('',) + rows[i]
        else:
            rows[i] = (name,) + rows[i]

    allrows.extend(rows)

with open('output.csv', 'w') as fd:
    writer = csv.writer(fd)
    for row in allrows:
        writer.writerow(row)

The above will output:

dog,a,aa,bb
,b,aa,bb
,c,,bb
,d,,bb
,,,bb
cat,a,,bb
,b,,bb
,c,,
bear,a,aa,bb
,,aa,
,,aa,

Which if we format nicely looks like:

----  -  --  --
dog   a  aa  bb
      b  aa  bb
      c      bb
      d      bb
             bb
cat   a      bb
      b      bb
      c
bear  a  aa  bb
         aa
         aa
----  -  --  --

...which is I think what you are looking for.

Mark Tolonen · Accepted Answer · 2020-01-04T16:17:09.690

Given your data, The following will extract the animal name and transpose the column data into the rows you are looking for.

import csv
import itertools

animals = [['dog',
            ['a','b','c','d'],
            ['aa','aa'],
            ['bb','bb','bb','bb','bb']],
           ['cat',
            ['a','b','c'],
            [''],
            ['bb','bb']],
           ['bear',
            ['a'],
            ['aa','aa','aa'],
            ['bb']]]

# open() recommended for Excel to write any kind of Unicode string data
with open('data.csv','w',newline='',encoding='utf-8-sig') as f:
    w = csv.writer(f)
    for name,*data in animals:
        data.insert(0,[name]) # adjust animal name string to 1-element list.
        for row in itertools.zip_longest(*data):
            w.writerow(row)

data.csv:

dog,a,aa,bb
,b,aa,bb
,c,,bb
,d,,bb
,,,bb
cat,a,,bb
,b,,bb
,c,,
bear,a,aa,bb
,,aa,
,,aa,

Excel:

data.csv opened in Excel

How to convert a list of variable length lists into a CSV file?

2 Answers2