user3447653 user3447653 - 3 months ago 10
JSON Question

Convert pandas dataframe to json format

I have a pandas dataframe with two columns – one with file name and the hour in which it was generated.

File Hour

F1 1
F1 2
F2 1
F3 1


I am trying to convert it to a json file with the following format:

{“File”:”F1”,”Hour”:”1”}
{“File”:”F1”,”Hour”:”2”}
{“File”:”F2”,”Hour”:”1”}
{“File”:”F3”,”Hour”:”1”}


When I use the command dataframe.to_json(orient = “records”), I get the records in the below format:

[{“File”:”F1”,”Hour”:”1”},
{“File”:”F1”,”Hour”:”2”},
{“File”:”F2”,”Hour”:”1”},
{“File”:”F3”,”Hour”:”1”}]


Just wondering whether there is an option to get the json file in the desired format. Any help would be appreciated.

Answer

The output that you get after DF.to_json is a string. So, you can simply slice it according to your requirement and remove the commas from it too.

out = df.to_json(orient='records')[1:-1].replace('},{', '} {')

To write the output to a text file, you could do:

with open('file_name.txt', 'w') as f:
    f.write(out)