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.
5 Answers
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)
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
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.
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.
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'])
