.json overwrites, rather than append??

[Clives]

Greetings,
I am trying to write a Pandas DataFrame to a json file.
The json file gets overwritten, rather than appended??

Can anyone spot the error??
Any pointers appreciated.

The pprint statement returns 1 row with 4 columns .... hundreds of!!
I only get a single json element??

The code snippet:

with open(outFile, 'w', newline='') as jsonfile:
    #filewriter = json.writer(jsonfile, delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL)
    #filewriter.writerow(['reportname', 'report-data', 'report', 'time-slices'])
    for service in srv.services.list():
        if service.properties.serviceName != " ":
            #print(service)
            #time_slices = 'time-slices'
            for report in service:
                query_string = "17224-0.0*" 
                qk_report = srv.usage.quick_report(since="LAST_MONTH", queries=query_string, metrics="report")
                flat = pd.json_normalize(qk_report)   #### use pd.json_normalize
                #pprint(flat)
                df = DataFrame(flat, columns=['report.reportname',  'report.report-data', 'report.report',  'report.time-slices'])    
                pprint(df)
                df.to_json(outFile, orient='table')

Larz60+ write Jun-02-2021, 03:09 PM:
Please make new threads in the correct forum. Refer to the Forum Structure for a full overview on what each forum is for.
Fixed for you this time. Please use bbcode tags on future posts.

[buran]

This is not an error, it is expected behavior. DataFrame.to_json dumps the single dataframe to JSON file.
You can merge all individual dataframes into single dataframe and only then dump this combined df int JSON file.

[Clives]

Greetings,
I read through the combine + merge methods.
My understanding, they are for merging separate files. eg file4 = (File1,File2,File3).

I am reading a single input that loops through separate streamed reports , not separate files.
I don't see how I merge the <b>streamed</b> outputs??

The updated code:

with open(outFile, 'w', newline='') as json_file:
    for service in srv.services.list():
        if service.properties.serviceName != " ":
            #print(service)
            #time_slices = 'time-slices'
            for report in service:
                qk_report = srv.usage.quick_report(since="LAST_DAY",   metrics="error")                     
                df = DataFrame(qk_report, columns=['report'])    
                #pprint(df)
                flat = pd.json_normalize(qk_report)   #### use pd.json_normalize
                flatten = flat.to_json()
                print(flatten)
                json.dump(flatten, json_file)
        print("End of Report")

    print("END REPORT")

Will it work, if I move the json.dump(flatten, json_file) from:

FROM
                json.dump(flatten, json_file)
        print("End of Report")

TO

        json.dump(flatten, json_file)
        print("End of Report")

Thought it would lose the output??

Thanks,
Clive

[Clives]

Another odd behaviour, it is expecting 4 columns.
It loops through all items, then loops again expecting another column: 'reportname'

The output for each row is:

{"report.reportname":{"0":"3da3d646cf434b9c838a822cd5c75d06"},"report.metadata":{"0":"{\"temp\":true,\"title\":\"3da3d646cf434b9c838a822cd5c75d06\",\"managerReport\":false}"},"report.time-slices":{"0":[1622989800000,1622991600000,1622993400000,1622995200000,1622997000000]},"report.report-data":{"0":[[{"resourceURI":"services\/","metric-type":"error","data":[null,null,null]}]]}}

[buran]

I don't see how I merge the <b>streamed</b> outputs??

You create a separate dataframe for each report. You need to merge these separate dataframes into single dataframe and then dump that combined dataframe into JSON. At least that is my understanding of what you try to achieve. Another thing is if you want to create newline-delimied/ndjson file where each row is separate JSON.

[buran]

Another odd behaviour, it is expecting 4 columns.
It loops through all items, then loops again expecting another column: 'reportname'

The output for each row is:

{"report.reportname":{"0":"3da3d646cf434b9c838a822cd5c75d06"},"report.metadata":{"0":"{\"temp\":true,\"title\":\"3da3d646cf434b9c838a822cd5c75d06\",\"managerReport\":false}"},"report.time-slices":{"0":[1622989800000,1622991600000,1622993400000,1622995200000,1622997000000]},"report.report-data":{"0":[[{"resourceURI":"services\/","metric-type":"error","data":[null,null,null]}]]}}

I don't follow you here.

[Clives]

The original data was 1 row with 4 columns. The flatten has created a single row???
report has 4 "objects":
report.reportname
report.metadata
report.time-slices
report.report-data

I expected each to have a key or use report.reportname as the key.

I don't understand why it expects 4 columns??

[buran]

Ассуминг what you show is your original data, what dataframe do you expect? I really don't understand. maybe someone else will step in.

[buran]

The original data was 1 row with 4 columns. The flatten has created a single row???

There is no concept of row in JSON. Still if it was 1 "row", how many rows in the dataframe do you expect?

[Clives]

There are approx 28 elements "rows" per Report.
It looped through approx 5 - 6 Reports, then fails expecting a second column??