CODEWITHSUNDEEP
pythonpython-3.xcsvdictionary
YungBoy
YungBoy

Reputation: 235

Writing a list of dictionaries, with multiple values per key as new rows

To preface, there are going to be a lot of duplicate looking questions, but none of the solutions for those have really helped me. I have the usual code I use to write a list of dictionaries, using the csv package:

    import csv
    to_csv = list_dic
    keys = to_csv[0].keys()
    with open('output.csv', 'w') as output_file:
        dict_writer = csv.DictWriter(output_file, keys)
        dict_writer.writeheader()
        dict_writer.writerows(to_csv)

This code works when used on a list of dictionaries with single key values just fine (well, other than the fact that the output csv has a blank row every other row), but now I have a list of dictionaries with multiple values per key, much like the example below:

    list_dic = [{'a':[x,y],'b':[1,2],'c':[1,2]},
                {'a':[x,y],'b':[1,2],'c':[1,2]},
                {'a':[x,y],'b':[1,2],'c':[1,2]},
                {'a':[x,y],'b':[1,2],'c':[1,2]}]

Basically my csv output currently looks like this:

     'a'     'b'     'c'
    [x,y]   [1,2]   [1,2]
    [x,y]   [1,2]   [1,2]
    [x,y]   [1,2]   [1,2]
    [x,y]   [1,2]   [1,2]

And I'm trying to get here:

      'a'    'b'    'c'
       x      1      1
       y      2      2
       x      1      1
       y      2      2
       x      1      1
       y      2      2
       x      1      1
       y      2      2

I've poured through very similar questions, but can't seem to apply any of the solutions to my particular situation. I've tried using some sort of for statement to write multiple rows, but am failing.

Thanks for the help and if you know of any questions that are duplicate to this situation please point me in the right direction.

EDIT: Example has 2 values per key, but the number of values is unknown in practice, just >1

Upvotes: 1

Views: 4667

Answers (4)

AChampion
AChampion

Reputation: 30268

You can turn the current list of dictionaries into a list of exploded dictionaries and then write them to csv:

>>> import itertools as it
>>> [dict(n) for d in list_dic for n in zip(*(zip(it.repeat(k), v) for k, v in d.items()))]
[{'a': 'x', 'b': 1, 'c': 1},
 {'a': 'y', 'b': 2, 'c': 2},
 {'a': 'x', 'b': 1, 'c': 1},
 {'a': 'y', 'b': 2, 'c': 2},
 {'a': 'x', 'b': 1, 'c': 1},
 {'a': 'y', 'b': 2, 'c': 2},
 {'a': 'x', 'b': 1, 'c': 1},
 {'a': 'y', 'b': 2, 'c': 2}]

This works with arbitrary number of values:

>>> list_dic = [{'a':['x','y','z'],'b':[1,2,3],'c':[1,2,3]},
...             {'a':['x','y','a'],'b':[1,3,2],'c':[1,2,1]},
...             {'a':['x','y','b'],'b':[1,4,3],'c':[1,2,4]},
...             {'a':['x','y','a'],'b':[1,5,2],'c':[1,2,9]}]
>>> [dict(n) for d in list_dic for n in zip(*(zip(it.repeat(k), v) for k, v in d.items()))]
[{'a': 'x', 'b': 1, 'c': 1},
 {'a': 'y', 'b': 2, 'c': 2},
 {'a': 'z', 'b': 3, 'c': 3},
 {'a': 'x', 'b': 1, 'c': 1},
 {'a': 'y', 'b': 3, 'c': 2},
 {'a': 'a', 'b': 2, 'c': 1},
 {'a': 'x', 'b': 1, 'c': 1},
 {'a': 'y', 'b': 4, 'c': 2},
 {'a': 'b', 'b': 3, 'c': 4},
 {'a': 'x', 'b': 1, 'c': 1},
 {'a': 'y', 'b': 5, 'c': 2},
 {'a': 'a', 'b': 2, 'c': 9}]

Upvotes: 0

Eric Duminil
Eric Duminil

Reputation: 54263

Here's an alternative, for which only one zip is needed to transpose columns into rows :

x = 'x'
y = 'y'

list_dic = [{'a': [x, y], 'b':[1, 2], 'c':[1, 2]},
            {'a': [x, y], 'b':[3, 4], 'c':[1, 2]},
            {'a': [x, y], 'b':[1, 2], 'c':[1, 2]},
            {'a': [x, y], 'b':[3, 4], 'c':[1, 2]}]

keys = ['a', 'b', 'c']
sep = "\t"

print(sep.join(keys))
for dic in list_dic:
    columns = [dic[key] for key in keys]
    for row in zip(*columns):
        print(sep.join(str(cell) for cell in row))

It outputs :

a   b   c
x   1   1
y   2   2
x   3   1
y   4   2
x   1   1
y   2   2
x   3   1
y   4   2

Upvotes: 1

Robᵩ
Robᵩ

Reputation: 168716

If you zip and unzip the data enough, you'll get the format you want:

list_dic = [{'a':['x','y'],'b':[1,2],'c':[1,2]},
            {'a':['x','y'],'b':[1,2],'c':[1,2]},
            {'a':['x','y'],'b':[1,2],'c':[1,2]},
            {'a':['x','y'],'b':[1,2],'c':[1,2]}]
import csv
to_csv = list_dic
keys = to_csv[0].keys()
with open('output.csv', 'w') as output_file:
    dict_writer = csv.DictWriter(output_file, keys)
    dict_writer.writeheader()
    for dic in list_dic:
        keys, values = zip(*dic.items())
        for values in zip(*values):
            dict_writer.writerow(dict(zip(keys, values)))

Upvotes: 3

TemporalWolf
TemporalWolf

Reputation: 7952

Split the dictionaries, then run them through your csv:

def split_dict(dct):
    result = []
    result.append({k: v[0] for k, v in dct.items()})
    result.append({k: v[1] for k, v in dct.items()})
    return result

def list_dict_split(lst):
    result = []
    for dct in lst:
        result.extend(split_dict(dct))
    return result

now just drop list_dict_split(list_dic) wherever you had list_dic:

Intermediate list is:

[{'a': 'x', 'c': 1, 'b': 1}, 
 {'a': 'y', 'c': 2, 'b': 2}, 
 {'a': 'x', 'c': 1, 'b': 1}, 
 {'a': 'y', 'c': 2, 'b': 2}, 
 {'a': 'x', 'c': 1, 'b': 1}, 
 {'a': 'y', 'c': 2, 'b': 2}, 
 {'a': 'x', 'c': 1, 'b': 1}, 
 {'a': 'y', 'c': 2, 'b': 2}]

Upvotes: 1

Related Questions