Python convert array of Jsons to Excell file within a single loop

Question

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

Answer 1

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