使用Python将嵌套JSON转换为Excel [英] Convert Nested JSON to Excel using Python
本文介绍了使用Python将嵌套JSON转换为Excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想使用Python将嵌套JSON转换为Excel文件格式.我已经按照要求完成了工作，但我想实现如下的excel格式.
I want to convert Nested JSON to Excel file format using Python. I've done nearly as per requirements but I want to achieve excel format as below.
JSON
[
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Cooktops/zgbs/appliances/3741261",
"subCategory": [
],
"title": "Cooktops"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Dishwashers/zgbs/appliances/3741271",
"subCategory": [
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Built-Dishwashers/zgbs/appliances/3741281",
"subCategory": [
],
"title": "Built-In Dishwashers"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Portable-Countertop-Dishwashers/zgbs/appliances/3741301",
"subCategory": [
],
"title": "Portable & Countertop Dishwashers"
}
],
"title": "Dishwashers"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Freezers/zgbs/appliances/3741331",
"subCategory": [
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Chest-Freezers/zgbs/appliances/3741341",
"subCategory": [
],
"title": "Chest Freezers"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Upright-Freezers/zgbs/appliances/3741351",
"subCategory": [
],
"title": "Upright Freezers"
}
],
"title": "Freezers"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Ice-Makers/zgbs/appliances/2399939011",
"subCategory": [
],
"title": "Ice Makers"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Range-Hoods/zgbs/appliances/3741441",
"subCategory": [
],
"title": "Range Hoods"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Ranges/zgbs/appliances/3741411",
"subCategory": [
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Drop-Ranges/zgbs/appliances/3741421",
"subCategory": [
],
"title": "Drop-In Ranges"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Freestanding-Ranges/zgbs/appliances/3741431",
"subCategory": [
],
"title": "Freestanding Ranges"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Slide-Ranges/zgbs/appliances/2399946011",
"subCategory": [
],
"title": "Slide-In Ranges"
}
],
"title": "Ranges"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Refrigerators/zgbs/appliances/3741361",
"subCategory": [
],
"title": "Refrigerators"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Wall-Ovens/zgbs/appliances/3741481",
"subCategory": [
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Combination-Microwave-Wall-Ovens/zgbs/appliances/3741491",
"subCategory": [
],
"title": "Combination Microwave & Wall Ovens"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Double-Wall-Ovens/zgbs/appliances/3741501",
"subCategory": [
],
"title": "Double Wall Ovens"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Single-Wall-Ovens/zgbs/appliances/3741511",
"subCategory": [
],
"title": "Single Wall Ovens"
}
],
"title": "Wall Ovens"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Warming-Drawers/zgbs/appliances/2399955011",
"subCategory": [
],
"title": "Warming Drawers"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Washers-Dryers/zgbs/appliances/2383576011",
"subCategory": [
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Clothes-Dryers/zgbs/appliances/13397481",
"subCategory": [
],
"title": "Dryers"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Clothes-Washing-Machines/zgbs/appliances/13397491",
"subCategory": [
],
"title": "Washers"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Combination-Washers-Dryers/zgbs/appliances/13755271",
"subCategory": [
],
"title": "All-in-One Combination Washers & Dryers"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Stacked-Washer-Dryer-Units/zgbs/appliances/2399957011",
"subCategory": [
],
"title": "Stacked Washer & Dryer Units"
}
],
"title": "Washers & Dryers"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Wine-Cellars/zgbs/appliances/3741521",
"subCategory": [
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Built-Wine-Cellars/zgbs/appliances/3741551",
"subCategory": [
],
"title": "Built-In Wine Cellars"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Freestanding-Wine-Cellars/zgbs/appliances/3741541",
"subCategory": [
],
"title": "Freestanding Wine Cellars"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Furniture-Style-Wine-Cellars/zgbs/appliances/3741561",
"subCategory": [
],
"title": "Furniture-Style Wine Cellars"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Small-Wine-Cellars/zgbs/appliances/3741531",
"subCategory": [
],
"title": "Small Wine Cellars"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Wine-Cellar-Cooling-Systems/zgbs/appliances/3741581",
"subCategory": [
],
"title": "Wine Cellar Cooling Systems"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Wine-Rooms/zgbs/appliances/3741571",
"subCategory": [
],
"title": "Wine Rooms"
}
],
"title": "Wine Cellars"
},
{
"url": "https://www.amazon.com/Best-Sellers-Appliances-Home-Appliance-Warranties/zgbs/appliances/2242350011",
"subCategory": [
],
"title": "Appliance Warranties"
}
]我正在遍历所有子类别，如下所示:
I'm traversing all subCategories like this:
row = 1
def TraverseJSONTree(jsonObject, count=0):
title = jsonObject.get('title')
url = jsonObject.get('url')
print 'Title: ' + title + ' , Position: ' + str(count)
worksheet.write_string(row, count, title)
worksheet.write_string(row, 6, url)
global row
row+=1
subCategories = jsonObject.get('subCategory',[])
for category in subCategories:
TraverseJSONTree(category, count+1)
for jsonObject in json.loads(jsonArray):
TraverseJSONTree(jsonObject)
预期结果
推荐答案
row = 1
def TraverseJSONTree(jsonObject, main_title=None, count=0):
if main_title is None:
main_title = title = jsonObject.get('title')
else:
title = jsonObject.get('title')
url = jsonObject.get('url')
print 'Title: ' + title + ' , Position: ' + str(count)
if main_title is not None:
worksheet.write_string(row, 0, title)
worksheet.write_string(row, count, title)
worksheet.write_string(row, 6, url)
global row
row+=1
subCategories = jsonObject.get('subCategory',[])
for category in subCategories:
TraverseJSONTree(category, main_title, count+1)
for jsonObject in json.loads(jsonArray):
TraverseJSONTree(jsonObject)
它将返回您的预期输出，因为它需要检查类别是否存在，然后您必须将原始名称放在excel reamin中的第0列上.
it will return your expected output as it needs a check if category is there then you have to right the original title on the 0th col in excel reamin as same.
这篇关于使用Python将嵌套JSON转换为Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
