24

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.

MERose
  • 4,048
  • 7
  • 53
  • 79
Zack
  • 517
  • 1
  • 4
  • 14

5 Answers5

24

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)
Robᵩ
  • 163,533
  • 20
  • 239
  • 308
  • 1
    you deserve the best comment actually, because you where the first complete solution and i like the way you used the the dicts update function. i adapted that in my answer in a separate function to keep it a one liner afterwards. so my answer is pretty much yours, changed to file output and added headers :) – hexerei software Apr 01 '15 at 22:12
14

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
Łukasz Rogalski
  • 22,092
  • 8
  • 59
  • 93
  • i like your answer best, straight forward :) you could consider sorting `dw` by its keys since in your output the order of the orgnames has changed from input. using `sorted(dw.items())` fixes this – hexerei software Apr 01 '15 at 22:07
  • 7
    Dear Python 3 users who don't want to embarrass themselves spending hours on this like I just did when all you have to do is simply switch `open("test_output.csv", "wb")` for `open("test_output.csv", "w", newline='')` and the above solution will work flawlessly. You'll get a `a bytes-like object is required, not 'str' when writing to a file' ` otherwise. Don't be a nana and try to encode your whole dictionary into `ascii` like me. – Frikster Jul 26 '17 at 04:11
  • If my value of any key is 0 then I got key as value like orgname1,orgname1,1,1 if 2015: 0 is for orgname1. So any solution for this. @Łukasz Rogalski – Deep Dalsania May 29 '20 at 10:00
6

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.

matsjoyce
  • 5,744
  • 6
  • 31
  • 38
3

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.

hexerei software
  • 3,100
  • 2
  • 15
  • 19
1

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'])
squirrelnuts
  • 11
  • 2