Multi-Level Parse JSON to Excel in Python

advertisements

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"


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