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
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))
)