Writing multiple dictionaries to csv file?

Question

Thanks to this other thread, I've successfully written my dictionary to a csv as a beginner using Python: Writing a dictionary to a csv file with one line for every 'key: value'

dict1 = {0 : 24.7548, 1: 34.2422, 2: 19.3290}

csv looks like this:

0  24.7548
1  34.2422
2  19.3290

Now, i'm wondering what would be the best approach to organize several dictionaries with the same keys. I'm looking to have the keys as a first column, then the dict values in columns after that, all with a first row to distinguish the columns by dictionary names.

Sure, there are a lot of threads trying to do similar things, such as: Trouble writing a dictionary to csv with keys as headers and values as columns, but don't have my data structured in the same way (yet…). Maybe the dictionaries must be merged first.

dict2 = {0 : 13.422, 1 : 9.2308, 2 : 20.132}
dict3 = {0 : 32.2422, 1 : 23.342, 2 : 32.424}

My ideal output:

ID  dict1    dict2    dict3
0   24.7548  13.422   32.2422
1   34.2422  9.2308   23.342
2   19.3290  20.132   32.424

I'm not sure, yet, how the column name ID for key names will work its way in there.

Steinar Lima · Accepted Answer · 2014-03-08T20:09:38.200

4

Use the csv module and list comprehension:

import csv

dict1 = {0: 33.422, 1: 39.2308, 2: 30.132}
dict2 = {0: 42.2422, 1: 43.342, 2: 42.424}
dict3 = {0: 13.422, 1: 9.2308, 2: 20.132}
dict4 = {0: 32.2422, 1: 23.342, 2: 32.424}

dicts = dict1, dict2, dict3, dict4

with open('my_data.csv', 'wb') as ofile:
    writer = csv.writer(ofile, delimiter='\t')
    writer.writerow(['ID', 'dict1', 'dict2', 'dict3', 'dict4'])
    for key in dict1.iterkeys():
        writer.writerow([key] + [d[key] for d in dicts])

Note that dictionaries is unordered by default, so if you want the keys in ascending order, you have to sort the keys:

for key in sorted(dict1.iterkeys(), key=lambda x: int(x)):
    writer.writerow([key] + [d[key] for d in dicts])

If you need to handle situations where you can't be sure that all dicts have the same keys, you'll need to change some small stuff:

with open('my_data.csv', 'wb') as ofile:
    writer = csv.writer(ofile, delimiter='\t')
    writer.writerow(['ID', 'dict1', 'dict2', 'dict3', 'dict4'])
    keys = set(d.keys() for d in dicts)
    for key in keys:
        writer.writerow([key] + [d.get(key, None) for d in dicts])

edited Mar 08 '14 at 20:09

answered Mar 08 '14 at 19:54

Steinar Lima

7,644
2
39
40

The comprehension will fail if `key` doesn't exist for every `dict`. This may not be a problem for the poster, but it's important to note. You could use a `defaultdict` or a custom class that overrides `__missing__` instead of the `dict`s, but just trying to access `d[key]` for each `d` in `dicts` could raise a `KeyError`. – ely Mar 08 '14 at 19:57
That depends. If it is expected that all dicts contains all keys, a defaultdict is **not** right. – Steinar Lima Mar 08 '14 at 20:00
@EMS If you need to handle possible missing entries, use d.`get(key, None)` – Steinar Lima Mar 08 '14 at 20:07
That's one way to handle them if you merely want to swap in a sentinel value. `__missing__` lets you do much more. – ely Mar 08 '14 at 20:24

score 1 · Answer 2 · answered Mar 08 '14 at 19:45

Use defaultdict(list)

from collections import defaultdict
merged_dict = defaultdict(list)
dict_list = [dict1, dict2, dict3]

for dict in dict_list:
    for k, v in dict.items():
        merged_dict[k].append(v)

This is what you get:

{0: [24.7548, 13.422, 32.2422], 1: [34.2422, 9.2308, 23.342], 2: [19.329, 20.132, 32.424]})

Then write the merged_dict to csv file as you had previously done for a single dict. This time writerow method of csv module will be helpful.

ely · Answer 3 · 2014-03-08T20:24:57.950

Here is one way to do it.

my_dicts = [dict1, dict2, dict3]
dict_names = range(1, len(my_dicts)+1)
header = "ID," + ",".join(map(lambda x: "dict"+str(x)), dict_names) + "\n"
all_possible_keys = set(reduce(lambda x,y: x + y.keys(), my_dicts, []))

with open("file_to_write.csv", "w") as output_file:
    output_file.write(header)
    for k in all_possible_keys:
        print_str = "{},".format(k)
        for d in my_dicts:
            print_str += "{},".format(d.get(k, None))
        print_str += "\n"
        output_file.write(print_str)

score -2 · Answer 4 · answered Mar 08 '14 at 19:40

It has been some time since I used Python, but here's my suggestion. In Python, dictionary values can be of any type (as far as I remember, don't flame me if I'm wrong). At least it should be possible to map your keys to lists.

So you can loop over your dictionaries and maybe create a new dictionary 'd', and for each key, if the value is already in 'd', push the value to the value of 'd' (since the value of the associated key is a list).

Then you can write out the new dictionary as: (pseudocode) for each key,value in dictionary write key write TAB for each v in value write v + TAB write new line end for

This doesn't include the 'header names' though, but I'm sure that's quite easy to add.

Writing multiple dictionaries to csv file?

4 Answers4

Linked