Python: writing a nested dictionary to CSV

Question

I am trying to write a nested dictionary in a CSV file. Here is a simple example:

import csv import itertools fields = [ 'org', '2015', '2014', '2013' ] dw = { 'orgname1': { '2015' : 2, '2014' : 1, '2013' : 1 }, 'orgname2': { '2015' : 1, '2014' : 2, '2013' : 3 }, 'orgname3': { '2015' : 1, '2014' : 3, '2013' : 1 } } with open("test_output.csv", "wb") as f: w = csv.writer( f ) years = dw.values()[0].keys() for key in dw.keys(): w.writerow([key, [dw[key][year] for year in years]]) 

This gives me a table with two columns: the first contains orgname ; the second contains [2, 1, 1] (or the corresponding values ​​from the sub-dictionary). I need a table with four columns: one for orgname , and then three for the corresponding list items.

Answer 1

Edit:

 w.writerow([key, [dw[key][year] for year in years]]) 

To:

 w.writerow([key] + [dw[key][year] for year in years]) 

Otherwise, you are trying to write something like [orgname1, [2, 1, 1]] in csv, while you mean [orgname1, 2, 1, 1] .

As mentioned in Padraic, you can change years = dw.values()[0].keys() to years = sorted(dw.values()[0].keys()) or years = fields[1:] to Avoid random behavior.

Answer 2

This looks like a job for DictWriter :

 import csv import itertools import sys fields = [ 'org', '2015', '2014', '2013' ] dw = { 'orgname1': { '2015' : 2, '2014' : 1, '2013' : 1 }, 'orgname2': { '2015' : 1, '2014' : 2, '2013' : 3 }, 'orgname3': { '2015' : 1, '2014' : 3, '2013' : 1 } } w = csv.DictWriter( sys.stdout, fields ) for key,val in sorted(dw.items()): row = {'org': key} row.update(val) w.writerow(row) 

Answer 3

Alternative implementation using DictWriter and with headers

 import csv import itertools fields = [ 'org', '2015', '2014', '2013' ] dw = { 'orgname1': { '2015' : 2, '2014' : 1, '2013' : 1 }, 'orgname2': { '2015' : 1, '2014' : 2, '2013' : 3 }, 'orgname3': { '2015' : 1, '2014' : 3, '2013' : 1 } } with open("test_output.csv", "wb") as f: w = csv.DictWriter(f, fields) w.writeheader() for k in dw: w.writerow({field: dw[k].get(field) or k for field in fields}) 

Output:

 org,2015,2014,2013 orgname1,2,1,1 orgname3,1,3,1 orgname2,1,2,3 

Answer 4

Using DictWriter, there is no need to sort the fields in advance, since w.writerow() will provide the correct order. But it makes sense to sort the elements yourself.

Thus, having collected all the above suggestions and choosing the best of them, I came up with the following code:

 import csv import itertools def mergedict(a,b): a.update(b) return a fields = [ 'org', '2015', '2014', '2013' ] dw = { 'orgname1': { '2015' : 2, '2014' : 1, '2013' : 1 }, 'orgname2': { '2015' : 1, '2014' : 2, '2013' : 3 }, 'orgname3': { '2015' : 1, '2014' : 3, '2013' : 1 } } with open("test_output.csv", "wb") as f: w = csv.DictWriter( f, fields ) w.writeheader() for k,d in sorted(dw.items()): w.writerow(mergedict({'org': k},d)) 

i added a tiny function mergedict() , which makes it another liner.