Python Forum
Issue in getting the required data from nested json and store it in a pandas datafram
Thread Rating:
  • 2 Vote(s) - 3.5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Issue in getting the required data from nested json and store it in a pandas datafram
#1
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?
Reply
#2
(May-20-2018, 06:30 AM)PrateekG Wrote: 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.
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply
#3
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))
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  [Numpy] How to store different data type in one numpy array? water 7 288 Mar-26-2024, 02:18 PM
Last Post: snippsat
  Grouping in pandas/multi-index data frame Aleqsie 3 606 Jan-06-2024, 03:55 PM
Last Post: deanhystad
Smile How to further boost the data read write speed using pandas tjk9501 1 1,227 Nov-14-2022, 01:46 PM
Last Post: jefsummers
  Parse Nested JSON String in Python rwalde 4 2,859 Sep-08-2022, 10:32 AM
Last Post: rwalde
Thumbs Up can't access data from URL in pandas/jupyter notebook aaanoushka 1 1,830 Feb-13-2022, 01:19 PM
Last Post: jefsummers
Question Sorting data with pandas TheZaind 4 2,295 Nov-22-2021, 07:33 PM
Last Post: aserian
  Pandas Data frame column condition check based on length of the value aditi06 1 2,655 Jul-28-2021, 11:08 AM
Last Post: jefsummers
  [Pandas] Write data to Excel with dot decimals manonB 1 5,774 May-05-2021, 05:28 PM
Last Post: ibreeden
  Pandas replace function not working on datafram with floats bcrypto 1 2,812 Apr-12-2021, 08:59 PM
Last Post: bcrypto
  pandas.to_datetime: Combine data from 2 columns ju21878436312 1 2,418 Feb-20-2021, 08:25 PM
Last Post: perfringo

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020