The json format you gave is wrong. There is no comma before the key named locationDetails. You can use json_normalize after you fix it.

df = pd.json_normalize(json_data,meta=['lossInfo']).explode('lossInfo').reset_index(drop=True)
df = df.join(pd.json_normalize(df.pop('lossInfo')))
'''
|    | metaData.formName   | metaData.user   | report.from   | report.to   | locationName.name   |   locationName.locNbr | locationDetails.locNm   | locationDetails.locAddress.locCity   | locationDetails.locAddress.locStateCd   | locationDetails.state   | locationDetails.lossLocation   |
|---:|:--------------------|:----------------|:--------------|:------------|:--------------------|----------------------:|:------------------------|:-------------------------------------|:----------------------------------------|:------------------------|:-------------------------------|
|  0 | A1                  | Test User       | 12/12/2021    | 12/12/2022  | test1               |                    12 | xyz                     | abc                                  | abcd                                    | ab                      | cd                             |
|  1 | A1                  | Test User       | 12/12/2021    | 12/12/2022  | test11              |                   121 | xyz1                    | abc1                                 | abcd1                                   | ab1                     | cd1                            |
'''

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)

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.

Expanding to what Corralien wrote, try something like this:

import json
import pandas as pd

data = json.loads(response_json)
df = pd.concat({k: pd.json_normalize(v) for k, v in data.items()}).droplevel(1)

df = pd.concat((df, pd.concat({k: pd.json_normalize(v) for k, v in df['service.services'].items()}).droplevel(1).add_prefix('service.')), axis=1).drop(columns='service.services')

This works under the assumption you will always have a list under the service.services column.

Output:

|    |   Price |   category |   service.id | service.name   | service.description   | service.Validity   |   service.order | service.selection   |   creditTO.id |   creditTO.duration | creditTO.Type   |   creditTO.Tax |   creditTO.total | creditTO.promotion   |   service.id | service.financeable   | service.Execution   |   service.serviceId | service.label   |   service.benefit.id | service.benefit.name   | service.benefit.Priced   |
|:---|--------:|-----------:|-------------:|:---------------|:----------------------|:-------------------|----------------:|:--------------------|--------------:|--------------------:|:----------------|---------------:|-----------------:|:---------------------|-------------:|:----------------------|:--------------------|--------------------:|:----------------|---------------------:|:-----------------------|:-------------------------|
| A  |     200 |        620 |           15 | KAL            | Description           |                    |               0 | False               |             0 |                   6 | standard        |             51 |              400 | False                |          100 | True                  |                     |                 112 | Colab           |                  235 | ZSX                    |                          |
| B  |     200 |        620 |           15 | BTX            | Description           |                    |               0 | False               |             0 |                   9 | standard        |             51 |              400 | False                |          100 | True                  |                     |                 112 | Colab           |                  235 | ZSX                    |                          |
| C  |     600 |        620 |           15 | FLS            | Description           |                    |               0 | False               |             0 |                  12 | standard        |             51 |              400 | False                |          100 | True                  |                     |                 112 | Colab           |                  235 | ZSX                    |                          |
| D  |     705 |        620 |           15 | TRW            | Description           |                    |               0 | False               |             0 |                  18 | standard        |             67 |              245 | False                |          100 | True                  |                     |                 112 | Colab           |                  235 | ZSX                    |                          |

You have many options, I'll provide you with them using the pandas library, choose which one is more suitable to you

• Pandas Library
• Pandas to_json() method

Example Code:

import pandas as pd

data = [
    ["A", "P1", "T1", "P1.com"],
    ["A", "P2", "T2", "P2.com"],
    ["A", "P2", "T3", "P2.com"],
    ["B", "Q1", "T1", "Q1.com"],
    ["B", "Q2", "T2", "Q2.com"],
    ["C", "R1", "T1", "R1.com"],
    ["C", "R2", "T2", "R2.com"],
]
cols = ['name', 'project', 'type', 'link']
# Create the pandas DataFrame
df = pd.DataFrame(data, columns=cols)

In order to convert it to JSON apply one of the following:

parsed_split = json.loads(df.to_json(orient="split"))
parsed_records = json.loads(df.to_json(orient="records"))
parsed_index = json.loads(df.to_json(orient="index"))
parsed_columns = json.loads(df.to_json(orient="columns"))
parsed_values = json.loads(df.to_json(orient="values"))
parsed_table = json.loads(df.to_json(orient="table"))

If none of the following meets your requirement, for sure you'll be able to loop over one of them in order to achieve your solution, if you're struggling with that, comment and I'll update it for you.

Keep us updated, @Anoosha

Update 1: Multi Sheet Excel

import pandas as pd

sheets = ["A", "B"]
xls = pd.ExcelFile('path_to_file.xls')
for sheet in sheets:
    df = pd.read_excel(xls, sheet) # Or use without loop if you want