CODEWITHSUNDEEP
pythonjsonexcel
George Salukvadze
George Salukvadze

Reputation: 75

Parse multilevel JSON to Excel in python

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"

Upvotes: 0

Views: 506

Answers (2)

AsafSH
AsafSH

Reputation: 695

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()

Upvotes: 1

Kyle
Kyle

Reputation: 2545

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.

Upvotes: 0

Related Questions