Python: Writing Nested Dictionary to CSV

Question

I'm trying to write a nested dictionary to a .csv file. Here is 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 gets 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'd like a table with four columns: one for orgname and then three for the corresponding list elements.

Answer 1

Change:

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

To:

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

Otherwise, you try to write something like [orgname1, [2, 1, 1]] to the csv, while you mean [orgname1, 2, 1, 1].

As Padraic mentioned, you may want to change years = dw.values()[0].keys() to years = sorted(dw.values()[0].keys()) or years = fields[1:] to avoid random behaviour.

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 in sorting the fields in advance, since w.writerow() will assure the correct order. But it does make sense to sort the items themselves.

So putting together all the above suggestions and picking the best of each, i would come up with 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 mergedict() function that makes it a one liner further down.

Answer 5

I think this could be an easier way:

import csv

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", "w") as csv_file:
  csvwriter = csv.writer(csv_file)
  csvwriter.writerow(['org', '2015', '2014', '2013'])

  for org in dw:
     csvwriter.writerow(org, dw[org]['2015'], dw[org]['2014'], dw[org]['2013'])