How to save Multi-layered dictionary to csv

Question

I have a three layed dictionary that I need to save it in csv format. I followed the code in this post. but i get only two rows with all the data dumped in one row.

How can I separate data and write them into separate cells.

{'Alpha': {'2010': {'216': 0.0, '217': 0.0, '218': 195.37308756510416}}}

There are other keys (Beta and Omega) at the same level as Alpha.

I'd like the final product to look like:

Alpha,2010,216,0.0
Alpha,2010,217,0.0
Alpha,2010,218,195.37308756510416
.....
Beta, .....

and preferably save it in a .csv file, but text file will do too.

This code is what I have tried.

with open('mycsvfile.csv', 'wb') as f: 
    w = csv.DictWriter(f, datadictionary.keys())
    w.writeheader()
    w.writerow(datadictionary)

Thanks

provide a sample output you looking for and what have you done — Vor, Jun 20 '14 at 19:56
Perhaps you could tell us what each part of the dictionary is supposed to be and what the csv should look like — Bob, Jun 20 '14 at 20:03

Corbell · Accepted Answer · 2014-06-21T03:28:37.903

First, a suggestion: if you're going to post some data that you're processing, omit all but a small representative sample - ain't nobody got time to ready through all those numbers scrolling off the edge of the screen.

Here's a smaller sample of your input:

{'Alpha': {'2010': {'216': 0.0,  '217': 0.0, '218': 195.4 } } }

One of the things you don't state is whether there could be other keys at the same level as 'Alpha' and again at the level of '2010'. If so, then your csv is going to have to include those values in each row also.

Here's a suggested csv format, with column headers:

section,year,key,value
Alpha,2010,216,0.0
Alpha,2010,217,0.0
Alpha,2010,218,195.37308756510416
... etc.

So how do you create this?

Just iterate through your dictionaries, and keep track of the outer dictionary keys to insert in each row:

text = "section,year,key,value\n"
for sectionName in myData:
    yearData = myData[sectionName]
    for year in yearData:
        keyValueDict = yearData[year]
        for key in keyValueDict:
            value = keyValueDict[key]
            text += sectionName + "," + year + "," + key + "," + str(value) + "\n"

To write the csv file:

f = open("mydata.csv", "w")
f.write(text)
f.close()

Thanks for your comments, I tried to elaborate more in the original post. So I'm assuming I can dump the `test` into a `csv` file? — FlyingMGET, Jun 20 '14 at 23:18
Yes just open a file and call write() with text as the content and it will be a csv file. — Corbell, Jun 21 '14 at 03:25

score 0 · Answer 2 · answered Jun 20 '14 at 20:23

If your output is supposed to look like this:

    ['Alpha', '2010', '217', 0.0]
    ['Alpha', '2010', '215', 0.0]
    ['Alpha', '2010', '205', 0.0]
    ['Alpha', '2010', '213', 0.0]

this code will do it (where dd is your dictionary):

    def do_keys(d, plst, lvl=0):
        for elem in d:
            plst[lvl] = elem
            if isinstance(d[elem], dict):
                do_keys(d[elem], plst, lvl+1)
            else:
                plst[-1] = d[elem]
                print plst

    do_keys(dd, ['']*4)

just replace the print pls with a write to your csv file

score 0 · Answer 3 · answered Jun 20 '14 at 20:47

This doesn't directly answer the question, but if possible, you should think of another format that can handle nested dictionaries naturally. Python has a JSON module, and it is quite simple to use. With A as your nested dictionary:

import json
print json.dumps(A, indent=True)

This gives (truncated):

{
 "Alpha": {
  "2010": {
   "216": 0.0, 
   "217": 0.0, 
   "215": 0.0, 
   ....
   "111": 0.0, 
   "169": 0.0
  }
 }
}

The advantage here is two-fold. JSON files are widely portable, and if need be the file is still human-readable. Saving it to a file is also easy:

with open("myfile.json",'w') as FOUT:
    js = json.dumps(A, indent=True)
    FOUT.write(js)

How to save Multi-layered dictionary to csv

3 Answers3