Convert List of JSON to Dataframe to Multiple Excel Sheets

Question

I have list of multiple objects that I want to convert into excel and I need individual object to be on different sheets. I have looped the list then to convert to Dataframe see below list of json I wanted to convert

[
  [],
  [
    {
      "Tax": "69.767442",
      "Details": {
        "Attributes": "dejje",
        "Name": "Plate",
        "additionalAttributes6": "",
        "Nuber": "",
        "additionalAttributes8": "",
        "summaryDescription": "",
        "Discount": "",
        "Groups": "",
        "taxInclusive": "",
        "additionalAttributes7": ""
      }
    }
  ],
  [
    {
      "Tax": "69.767442",
      "Details": {
        "Attributes": "",
        "Name": "",
        "additionalAttributes6": "",
        "Nuber": "",
        "additionalAttributes8": "",
        "summaryDescription": "",
        "Discount": "",
        "Groups": "",
        "taxInclusive": "10.19",
        "additionalAttributes7": ""
      }
    }
  ]
]  

See below code that I am trying to loop to append and put on separate sheets base on each row of the above json

with open('data.json') as file:

    data = json.load(file)
    arg_mode = 'a' if 'out.xlsx' in os.getcwd() else 'w' # line added
    df = []
    index = 0
    for value in data:
        df[index] = pd.DataFrame(json_normalize(value, max_level=1))
        with pd.ExcelWriter('out.xlsx') as writer:
          df[index].to_excel(writer,mode=arg_mode,index=False,sheet_name=df[index],engine="openpyxl")
        index = index + 1

Some please help what I am doing wrong

Answer 1

You can try something like this with a little column renaming and using enumerate:

from pathlib import Path
import json

import pandas as pd


with open(file="data.json") as file:
    data = json.load(fp=file)

for idx, value in enumerate(iterable=data):
    arg_mode = "a" if [x for x in Path.cwd().glob(pattern="out.xlsx")] else "w"

    if value:
        with pd.ExcelWriter(path="out.xlsx", mode=arg_mode, engine="openpyxl") as writer:
            (pd
             .json_normalize(data=value, max_level=1)
             .rename(columns=lambda x: x.split(".")[-1])
             .to_excel(excel_writer=writer, index=False, sheet_name=str(idx))
             )