Convert Nested JSON to Excel using Python
<p>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.</p>

<p><strong>JSON</strong></p>

<p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-js lang-js prettyprint-override"><code>[
  {
    "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 &amp; 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 &amp; 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 &amp; Dryers"
      },
      {
        "url": "https://www.amazon.com/Best-Sellers-Appliances-Stacked-Washer-Dryer-Units/zgbs/appliances/2399957011",
        "subCategory": [
          
        ],
        "title": "Stacked Washer &amp; Dryer Units"
      }
    ],
    "title": "Washers &amp; 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"
  }
]</code></pre>
</div>
</div>
</p>

<p>I'm traversing all subCategories like this:</p>

<pre><code>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)
</code></pre>

<p><a href="http://i.stack.imgur.com/pejPe.png" rel="nofollow"><img src="http://i.stack.imgur.com/pejPe.png" alt="enter image description here"></a></p>

<p><strong>Expected Result</strong></p>

<p><a href="http://i.stack.imgur.com/bLQcX.png" rel="nofollow"><img src="http://i.stack.imgur.com/bLQcX.png" alt="enter image description here"></a></p>

<pre><code>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)
</code></pre>

<p>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.</p>

python json excel