+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: Issue in getting the required data from nested json and store it in a pandas datafram (/thread-10423.html)
Issue in getting the required data from nested json and store it in a pandas datafram - PrateekG - May-20-2018
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?
RE: Issue in getting the required data from nested json and store it in a pandas datafram - volcano63 - May-20-2018
(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.
RE: Issue in getting the required data from nested json and store it in a pandas datafram - scidam - May-20-2018
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))