Issue parsing JSON file using Python

Question

I have a pretty big JSON file that has a folder, class and object structure that I need to read and export the output to a dataframe. When I try my logic, I am able to get to the first elements but if it has multiple elements, I am unable to see them.

I tried going through multiple posts but had no luck so far. Could anyone guide me on where I am going wrong or if there is a better way to do this ?

My sample JSON file:

{
    "root": {
        "id": 123456,
        "outline": {
            "folder": [
                {
                    "name": "First Folder",
                    "folder": [
                        {
                            "name": "First Class",
                            "item": [
                                {
                                    "@dataType": "String",
                                    "name": "ID1"
                                }
                            ]
                        },
                        {
                            "name": "Second Class",
                            "item": [
                                {
                                    "@dataType": "Numeric",
                                    "name": "FACTOR1"
                                }
                            ]
                        }
                    ]
                 },
                 {
                    "name": "Second Folder",
                    "folder": [
                        {
                            "name": "First Class",
                            "item": [
                                {
                                    "@dataType": "String",
                                    "name": "ID2"
                                }
                            ]
                        },
                        {
                            "name": "Second Class",
                            "item": [
                                {
                                    "@dataType": "Numeric",
                                    "name": "FACTOR2"
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    }
}

Expected output to a dataframe:

['First Folder', 'First Class', 'ID1']
['First Folder', 'Second Class', 'FACTOR1']
['Second Folder', 'First Class', 'ID2']
['Second Folder', 'Second Class', 'FACTOR2']

My python code so far:

import json

with open('Sample_File.json') as f_in:
    data = json.load(f_in)

for x in data['root']['outline']['folder']:
    print('Folder Name : {}'.format(x['name']))
    counter = 0
    for y in x['folder'][counter]['item']:
        print('Class Name : {}, Object Name : {}'.format(x['folder'][counter]['name'], y['name']))
    counter = counter + 1

My current output which is not right:

['First Folder', 'First Class', 'ID1']
['Second Folder', 'First Class', 'ID2']

andreis11 · Accepted Answer · 2020-04-16 20:40:04Z

1

This should do the trick:

import json

with open('Sample_File.json') as f_in:
    data = json.load(f_in)

final_list = []

for x in data['root']['outline']['folder']:
  for y in x['folder']:
    for z in y['item']:
      final_list.append([x['name'],y['name'],z['name']])


for x in final_list:
  print(x)

Output:

['First Folder', 'First Class', 'ID1']
['First Folder', 'Second Class', 'FACTOR1']
['Second Folder', 'First Class', 'ID2']
['Second Folder', 'Second Class', 'FACTOR2']

Or if you want to just print them:

for x in data['root']['outline']['folder']:
  for y in x['folder']:
    for z in y['item']:
      print(x['name'],y['name'],z['name'])

Later edit (missing keys):

Note: for elements inside lists ([..]) you don't have to worry. But to be on the safe side, let's say you a json like this:

{"root": {
    "id": 123456,
    "outline": {
        "folder": [
            {
                "name": "First Folder",
                "folder": [
                    {
                        "name": "First Class",
                        "item": [
                            {
                                "@dataType": "String",
                                "name": "ID1"
                            }
                        ]
                    }
                ]
             },
             {
                "name": "Second Folder"
            }
        ]
    }
}
}

You would get this : KeyError: 'folder'

You could use the try/except statements that I've mentioned.

try:
  for x in data['root']['outline']['folder']:
    for y in x['folder']:
      for z in y['item']:
        final_list.append([x['name'],y['name'],z['name']])
except KeyError:
  pass

Output:

['First Folder', 'First Class', 'ID1']

The pass says if error is triggered just continue like nothing happened and do no nothing (but could be replaced to do something, the 'pass').

edited Apr 16, 2020 at 20:40

answered Apr 16, 2020 at 19:36

andreis11

1,1611 gold badge6 silver badges11 bronze badges

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

3 Comments

Prashanth Over a year ago

Thanks @andreis11. One question though - say if one of the elements in the huge json file is missing, say, the second 'folder' element is missing, how can we handle that through an exception ?

andreis11 Over a year ago

You're welcome. With a try/catch expressions capturing the Keyvalue error. Will update the answer with a sample.

andreis11 Over a year ago

@Prashanth Added some explanation and deformed json. As mentioned in the answer, for items in lists [...] you don't have to worry as long as there's one element. Even so, with the try/catch maneuver you should be on the safe spot.

Андрей Браилов · Accepted Answer · 2020-04-16 19:40:35Z

1

at the end of 1st iteration, after counter = counter + 1 statement variable counter equals to 1. then on the second iteration after print function, counter again become 0. So you have to code it like this

import json

with open('Sample_File.json') as f_in:
    data = json.load(f_in)

for x in data['root']['outline']['folder']:
    print(f'Folder Name : {x["name"]}')
    for counter in range(len(x['folder'])):
        for y in x['folder'][counter]['item']:
            print(f'Class Name : {x["folder"][counter]["name"]}, Object Name : {y["name"]}')

answered Apr 16, 2020 at 19:40

Андрей Браилов

111 bronze badge

Collectives™ on Stack Overflow

Issue parsing JSON file using Python

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related