Convert Nested Json To Excel Using Python

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 I'm traversing all subCateg

Solution 1:

row = 1defTraverseJSONTree(jsonObject, main_title=None, count=0):
    if main_title isNone:
        main_title = title = jsonObject.get('title')
    else:
        title = jsonObject.get('title')
    url = jsonObject.get('url')

    print'Title: ' + title + ' , Position: ' + str(count)

    if main_title isnotNone:
        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)

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.

Solution 2:

Modification : Simplest way to do this would be to use csv module, say we have the whole json in the variable 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)))

You will have the desired csv in the same location. This works for only two subcategories (because i have checked the data given by you and say there were only two categories (ie 1 and 1.1)) but in case you want for more than repeat the same(I know it's not the most efficient way couldn't think of any in such a short time)

You can also use pandas module to convert the dictionary import pandas as pd pd.DataFrame.from_dict(dcitionaty_element)

And then do it on all the dictionaries in that json and merge them and save it to a csv file.