Issue in getting the required data from nested json and store it in a pandas datafram

[PrateekG]

Hi All,

I have following json data-

{
  'SuccessResponse': {
    'Head': {
      'RequestAction': 'GetCategoryTree',
      'RequestId': '',
      'ResponseType': 'Categories',
      'Timestamp': '2018-05-19T00:30:55+08:00'
    },
    'Body': [
      {
        'categoryId': 1902,
        'children': [
          {
            'categoryId': 10001930,
            'children': [
              {
                'categoryId': 10001958,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Accessories',
                'var': False
              },
              {
                'categoryId': 10001957,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Backpacks',
                'var': False
              },
              {
                'categoryId': 10001956,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Backpacks Trolley',
                'var': False
              },
              {
                'categoryId': 10001955,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Bags',
                'var': False
              }
            ],
            'leaf': False,
            'name': 'Kids Bags',
            'var': False
          },
          {
            'categoryId': 10001928,
            'children': [
              {
                'categoryId': 10001947,
                'children': [
                  {
                    'categoryId': 10001990,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Fashion backpacks',
                    'var': True
                  }
                ],
                'leaf': False,
                'name': 'Backpacks',
                'var': False
              },
              {
                'categoryId': 10001946,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Business Bags',
                'var': True
              },
              {
                'categoryId': 10001948,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Crossbody Bags',
                'var': True
              },
              {
                'categoryId': 10001945,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Messenger Bags',
                'var': True
              },
              {
                'categoryId': 10001949,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Tote Bags',
                'var': True
              },
              {
                'categoryId': 10001950,
                'children': [
                  {
                    'categoryId': 10001993,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Card Holders',
                    'var': True
                  },
                  {
                    'categoryId': 10001992,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Coin Holders & Pouches',
                    'var': True
                  },
                  {
                    'categoryId': 10001994,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Key Holders',
                    'var': True
                  },
                  {
                    'categoryId': 10001995,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Money Clips',
                    'var': True
                  },
                  {
                    'categoryId': 10001991,
                    'children': [
                      {
                        'categoryId': 10002040,
                        'children': [
                          
                        ],
                        'leaf': True,
                        'name': 'Fashion Wallets',
                        'var': True
                      }
                    ],
                    'leaf': False,
                    'name': 'Wallets',
                    'var': False
                  }
                ],
                'leaf': False,
                'name': 'Wallets & Accessories',
                'var': False
              }
            ],
            'leaf': False,
            'name': 'Men Bags',
            'var': False
          },
          {
            'categoryId': 10001931,
            'children': [
              {
                'categoryId': 10001961,
                'children': [
                  {
                    'categoryId': 10002017,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Briefcases',
                    'var': False
                  },
                  {
                    'categoryId': 10002020,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Laptop Backpacks',
                    'var': False
                  },
                  {
                    'categoryId': 10002019,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Laptop cases',
                    'var': False
                  },
                  {
                    'categoryId': 10002018,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Messenger Bags',
                    'var': False
                  }
                ],
                'leaf': False,
                'name': 'Laptop Bags',
                'var': False
              },
              {
                'categoryId': 10001959,
                'children': [
                  {
                    'categoryId': 10001998,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Kids Luggage',
                    'var': False
                  },
                  {
                    'categoryId': 10001997,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Luggage Sets',
                    'var': False
                  },
                  {
                    'categoryId': 10001996,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Suitcases',
                    'var': False
                  }
                ],
                'leaf': False,
                'name': 'Luggage',
                'var': False
              },
              {
                'categoryId': 10001960,
                'children': [
                  {
                    'categoryId': 10002015,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Compression Bags',
                    'var': False
                  },
                  {
                    'categoryId': 10002013,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Garment Bags',
                    'var': False
                  },
                  {
                    'categoryId': 10001999,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Luggage Carts',
                    'var': False
                  },
                  {
                    'categoryId': 10002000,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Luggage Locks',
                    'var': False
                  },
                  {
                    'categoryId': 10002001,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Luggage Scales',
                    'var': False
                  },
                  {
                    'categoryId': 10002005,
                    'children': [
                      {
                        'categoryId': 10002041,
                        'children': [
                          
                        ],
                        'leaf': True,
                        'name': 'Luggage Straps',
                        'var': False
                      },
                      {
                        'categoryId': 10002042,
                        'children': [
                          
                        ],
                        'leaf': True,
                        'name': 'Luggage Tags',
                        'var': False
                      }
                    ],
                    'leaf': False,
                    'name': 'Luggage Straps & Tags',
                    'var': False
                  },
                  {
                    'categoryId': 10002004,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Luggage protectors & covers',
                    'var': False
                  },
                  {
                    'categoryId': 10002010,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Organizer Sets',
                    'var': False
                  },
                  {
                    'categoryId': 10002016,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Other Packing Organizers',
                    'var': False
                  },
                  {
                    'categoryId': 10002008,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Other Travel Accessories',
                    'var': False
                  },
                  {
                    'categoryId': 10002002,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Passport Covers',
                    'var': False
                  },
                  {
                    'categoryId': 10002012,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Shoe Bags',
                    'var': False
                  },
                  {
                    'categoryId': 10002009,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Toiletries & Cosmetics Bags',
                    'var': False
                  },
                  {
                    'categoryId': 10002014,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Travel Size Bottles & Containers',
                    'var': False
                  },
                  {
                    'categoryId': 10002003,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Travel Wallets',
                    'var': False
                  },
                  {
                    'categoryId': 10002006,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Travel adapters & Converters',
                    'var': False
                  },
                  {
                    'categoryId': 10002007,
                    'children': [
                      {
                        'categoryId': 10002046,
                        'children': [
                          
                        ],
                        'leaf': True,
                        'name': 'Ear plugs',
                        'var': False
                      },
                      {
                        'categoryId': 10002045,
                        'children': [
                          
                        ],
                        'leaf': True,
                        'name': 'Eye masks',
                        'var': False
                      },
                      {
                        'categoryId': 10002044,
                        'children': [
                          
                        ],
                        'leaf': True,
                        'name': 'Travel pillows',
                        'var': False
                      },
                      {
                        'categoryId': 10002043,
                        'children': [
                          
                        ],
                        'leaf': True,
                        'name': 'Travel sets',
                        'var': False
                      }
                    ],
                    'leaf': False,
                    'name': 'Travel pillows & eye masks',
                    'var': False
                  },
                  {
                    'categoryId': 10002011,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Underwear Organizers',
                    'var': False
                  }
                ],
                'leaf': False,
                'name': 'Travel Accessories',
                'var': False
              },
              {
                'categoryId': 10001962,
                'children': [
                  {
                    'categoryId': 10002023,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Foldable & Drawstring bags',
                    'var': False
                  },
                  {
                    'categoryId': 10002022,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Waist Packs',
                    'var': False
                  },
                  {
                    'categoryId': 10002021,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Weekender bags',
                    'var': False
                  }
                ],
                'leaf': False,
                'name': 'Travel Bags',
                'var': False
              }
            ],
            'leaf': False,
            'name': 'Travel',
            'var': False
          },
          {
            'categoryId': 10001929,
            'children': [
              {
                'categoryId': 10001951,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Backpacks',
                'var': True
              },
              {
                'categoryId': 10001953,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Card Holders',
                'var': True
              },
              {
                'categoryId': 10001952,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Coin Purses & Pouches',
                'var': True
              },
              {
                'categoryId': 10001954,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Key Holders',
                'var': True
              }
            ],
            'leaf': False,
            'name': 'Unisex Bags',
            'var': False
          },
          {
            'categoryId': 10001927,
            'children': [
              {
                'categoryId': 10001942,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Backpacks',
                'var': True
              },
              {
                'categoryId': 10001941,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Clutches',
                'var': True
              },
              {
                'categoryId': 10001939,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Cross Body & Shoulder Bags',
                'var': True
              },
              {
                'categoryId': 10001940,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Top-Handle Bags',
                'var': True
              },
              {
                'categoryId': 10001938,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Tote Bags',
                'var': True
              },
              {
                'categoryId': 10001944,
                'children': [
                  {
                    'categoryId': 10001985,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Bag Charms & Accessories',
                    'var': True
                  },
                  {
                    'categoryId': 10001988,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Card Holders',
                    'var': True
                  },
                  {
                    'categoryId': 10001987,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Coin Purses & Pouches',
                    'var': True
                  },
                  {
                    'categoryId': 10001989,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Key Holders',
                    'var': True
                  },
                  {
                    'categoryId': 10001986,
                    'children': [
                      
                    ],
                    'leaf': True,
                    'name': 'Wallets',
                    'var': True
                  }
                ],
                'leaf': False,
                'name': 'Wallets & Accessories',
                'var': False
              },
              {
                'categoryId': 10001943,
                'children': [
                  
                ],
                'leaf': True,
                'name': 'Wristlets',
                'var': True
              }
            ],
            'leaf': False,
            'name': 'Women Bags',
            'var': False
          }
        ],
        'leaf': False,
        'name': 'Bags and Travel',
        'var': False
      }
	  
	]
	}
}

Now I want to fetch 'categoryId','name' from above nested json and store them in a pandas Dataframe.

I have tried with following in Python3.6 -

dfCat = json_normalize(json_data['SuccessResponse']['Body'],'children')

But couldn't get all values of required columns due to this nested json data.

Can anyone please try with above json data and share me the required dataframe?

[volcano63]

Can anyone please try with above json data and share me the required dataframe?

You are kidding, right? Or you want to pay someone to do it for you?

You can, of course, write a recursive function - that will traverse your JSON searching for name keys - and attaching ID of the parent record together with the name to your dataset.

[scidam]

The problem seems to be not very hard. So, a json-string could be transformed into Python dictionary, in your case -- nested dictionaries that have specific format. Now, you need to traverse
these dictionaries in-depth. The minimal code example I just wrote should help you:

data = {
    'children': [ {
                  'children': [{'children': [], 'value': 1}, {'children': [], 'value': 4}],
                  'value': 3 
                  },
                  {
                  'children': [],
                  'value' : 2
                  }
                ],
     'value': 0
     }

def traverse_in_depth(data):
    nodes = data['children'] if 'children' in data else []
    print('Parsing root node:', data['value']) 
    while nodes:
       node = nodes.pop()
       if 'children' in node:
           if node['children']:
               nodes.extend(node['children'])
       print('Parsed node value: ', node['value'])
       # you can do anything with the node here!!! e.g. save
       # node['name'], node['id'] to data frame etc.  

import copy
traverse_in_depth(copy.deepcopy(data))