Parse multilevel JSON to Excel in python

Question

I'm trying to parse multilevel JSON to Excel using xlwt. JSON format is like this:

{
     "A": {
              "A1": 0, "A2": 0
          },
     "B": {
              "B1": 0, "B2": 0
          }
}

etc.

I've tried following (obviously after opening and loading JSON into Python dictionary):

for k in data:
    for l in data.keys():
        for j in l.keys():
            o = []
            o = j
            ws.write(0, l.keys().index(j)+1, l[j])
        ws.write(data.keys().index(k)+1, l.keys().index(o)+1, o)
    ws.write(data.keys().index(k)+1, 0, k[l])

But I receive "unicode" object doesn't have attribute "keys"

Please edit your question and add more information. Which line produces the error? Also, how are you creating the dictionary? — Jezor
– Jezor, Commented Apr 11, 2017 at 16:57

AsafSH · Accepted Answer · 2017-06-09 20:29:08Z

1

You could use the package StyleFrame.

First convert the data to format that can be used in StyleFrame object and then just use the StyleFrame object.

from StyleFrame import StyleFrame

data = {'A': {'A1': 0, 'A2': 0}, 'B': {'B1': 0, 'B2': 0}}
formated_data = {col: [data[col][row] for row in sorted(data[col])] for col in data}

StyleFrame(data).to_excel('output.xlsx',
                          row_to_add_filters=0,
                          columns_and_rows_to_freeze='A2').save()

edited Jun 9, 2017 at 20:29

answered Jun 9, 2017 at 19:46

AsafSH

6956 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kyle · Accepted Answer · 2017-04-11 22:07:31Z

0

I just did a little testing. See the test case below to highlight how to loop your multilevel dictionary. I can't tell exactly the logic of what you are writing, so I will give a generalized answer:

In [44]: data
Out[44]: {'A': {'A1': 0, 'A2': 0}, 'B': {'B1': 0, 'B2': 0}}

In [45]: for i in data:
    ...:     print(data[i])
    ...:     
{'A1': 0, 'A2': 0}
{'B1': 0, 'B2': 0}

#You can see that `i` represents  the key, so you access the value paired with that key by doing `data[i]`. In this case, that value is another dictionary.

In [46]: for i in data:
    ...:     for j in data[i]:
    ...:         print(data[i][j])
    ...:         
0
0
0
0

#This is far less illustrative than my contrived example, but `j` represents the key in the dictionary `data[i]`. So this second loop goes through and prints each value of the nested dictionaries.

Let me know if this isn't clear.

edited Apr 11, 2017 at 22:07

answered Apr 11, 2017 at 17:33

Kyle

2,5452 gold badges19 silver badges33 bronze badges

3 Comments

Jezor Over a year ago

I didn't become a programmer yesterday, yet I have no idea how this code is supposed to answer the question. Maybe I'm just too stupid, but some explanation of what's going on here would be helpful.

Kyle Over a year ago

It shows generally how to loop through a multilevel dictionary, which appears to be what the question is asking. The structure of the sample dict is exactly the same structure as OP. I can change it to be the exact same if that is more clear. I'll try to clarify a bit in the answer.

Jezor Over a year ago

Well, that's one of the most basic things in Python. He's trying to put it in a spread sheet, but he didn't specify how exactly he is planning to do that...

Collectives™ on Stack Overflow

Parse multilevel JSON to Excel in python

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related