I am currently trying to convert many Arrays of Jsons, with a single loop, to an Excell file, using pandas.
Input: [json 1,json 2, json 3] [json 4, json 5, json 5] etc.
code:
for t in liste:
response = client.list_discovered_resources(
resourceType=t,
limit=0,
includeDeletedResources=False,
)
if response['resourceIdentifiers']:
print('\n ******************** Resources of Type ',t,'\n')
print(jsbeautifier.beautify(str(response['resourceIdentifiers'])))
# at this point, we have gotten many arrays of jsons displayed in the output.
pd.DataFrame(response['resourceIdentifiers']).to_excel("output.xlsx")
as you can see, each ****response['resourceIdentifiers']****represents an array of jsons. When I run this loop, I only get the last array of jsons ( producde in the last loop iteration ) displayed in the Excell file.
I want, to have the arrays displayed in the same file.
can someone help me with this issue ? Thank you so much in advane
CodePudding user response:
Try:
list_of_dfs = []
for t in liste:
response = client.list_discovered_resources(
resourceType=t,
limit=0,
includeDeletedResources=False,
)
if response['resourceIdentifiers']:
print('\n ******************** Resources of Type ', t, '\n')
print(jsbeautifier.beautify(str(response['resourceIdentifiers'])))
list_of_dfs.append(pd.DataFrame(response['resourceIdentifiers']))
final_df = pd.concat(list_of_dfs, ignore_index=True).to_excel("output.xlsx")
Use this if you want to save each dataframe in separate sheet.
writer = pd.ExcelWriter('data.xlsx',engine='xlsxwriter')
for index, t in enumerate(liste):
response = client.list_discovered_resources(
resourceType=t,
limit=0,
includeDeletedResources=False,
)
if response['resourceIdentifiers']:
print('\n ******************** Resources of Type ', t, '\n')
print(jsbeautifier.beautify(str(response['resourceIdentifiers'])))
df = pd.DataFrame(response['resourceIdentifiers'])
df.to_excel(writer, sheet_name=f'df_{index}', startrow=0, startcol=0, index=False)
writer.save()
CodePudding user response:
You are overwriting the Excel file. Try to first take all jsons and put them into one pandas.DataFrame and then save this one DataFrame into Excel