logo标签列表

使用Python将嵌套的JSON转换为Excel

pythonjsonexcel
3
我想使用Python将嵌套的JSON转换为Excel文件格式。我已经按照要求完成了几乎所有工作，但我想要如下所示的Excel格式。 

[
  {
    "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"
  }
]

我正在这样遍历所有子类别：
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)

enter image description here

预期结果

enter image description here

使用pandas会很容易。 - bhansa
1
你能告诉我怎么做吗？我已经基本按照要求完成了。我是Python的新手。 - Asad Haider
2个回答
2

修改： 最简单的方法是使用csv模块，假设我们将整个json保存在变量a中。

import csv
import cPickle as pickle 

fieldnames = ['Category1', 'Category1.1', 'url']
csvfile = open("category.csv", 'wb')
csvfilewriter = csv.DictWriter(csvfile, fieldnames=fieldnames,dialect='excel', delimiter=',')
csvfilewriter.writeheader()

for b in a:     
    data = []
    data.append(b['title'])
    data.append("")
    data.append(b['url'])
    csvfilewriter.writerow(dict(zip(fieldnames,data)))
    data = []
    for i in xrange(len(b['subCategory'])):
        data.append(b['title'])
        data.append(b['subCategory'][i]['title'])
        data.append(b['subCategory'][i]['url'])
        csvfilewriter.writerow(dict(zip(fieldnames,data)))

你将在相同的位置得到所需的csv文件。这仅适用于两个子类别（因为我已经检查了您提供的数据，并且发现只有两个类别（即1和1.1）），但是如果您想要更多，请重复相同的步骤（我知道这不是最有效的方法，在这么短的时间内想不到更好的方法）
您还可以使用pandas模块来转换字典 import pandas as pd pd.DataFrame.from_dict(dcitionaty_element)
然后对json中的所有字典执行此操作并合并它们，然后将其保存到csv文件中。
如果有子类别的子类别，这个会起作用吗？ - Asad Haider
如果您知道子类别的最大数量，那么我可以再次使用for循环来完成。 - Neeraj Komuravalli
Python 3 提示：csvfile = open("category.csv", 'w') - Saeed
2
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中的第0列中编写原始标题并保持不变。
它没有像上面所述的那样给出期望的输出。我已经检查了您的脚本，它给了我这个链接：https://docs.google.com/spreadsheets/d/1BBlXNiCqn-Z19cCsK_kzpS4fpy4Rknek2ubyvEj4c5E/edit?usp=sharing - Asad Haider
我需要的应该像这样：https://docs.google.com/spreadsheets/d/1TT5alzmW9tMSR1sOFoi1YgYz_IwnTGUvhh1esHhk8QM/edit?usp=sharing如果有更多的子类别，它也应该适用。 - Asad Haider
1
啊，愚蠢的错误，只需在以下行中使用main_title更新标题：如果main_title不为None： worksheet.write_string(row, 0, main_title) - d-ashu
谢谢，如果只有一个子类别，它可以正常工作。如果有多个子到子类别，我该如何使其工作？ - Asad Haider
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接