[json] Convert Pandas DataFrame to JSON format

I have a Pandas DataFrame with two columns – one with the filename and one with 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"}]

I'm just wondering whether there is an option to get the JSON file in the desired format. Any help would be appreciated.

use this formula to convert a pandas DataFrame to a list of dictionaries :

import json
json_list = json.loads(json.dumps(list(DataFrame.T.to_dict().values())))

In newer versions of pandas (0.20.0+, I believe), this can be done directly:

df.to_json('temp.json', orient='records', lines=True)

Direct compression is also possible:

df.to_json('temp.json.gz', orient='records', lines=True, compression='gzip')

Try this one:

json.dumps(json.loads(df.to_json(orient="records")))

To transform a dataFrame in a real json (not a string) I use:

    from io import StringIO
    import json
    import DataFrame

    buff=StringIO()
    #df is your DataFrame
    df.to_json(path_or_buf=buff,orient='records')
    dfJson=json.loads(buff)

convert data-frame to list of dictionary

list_dict = []

for index, row in list(df.iterrows()):
    list_dict.append(dict(row))

save file

with open("output.json", mode) as f:
    f.write("\n".join(str(item) for item in list_dict))

instead of using dataframe.to_json(orient = “records”) use dataframe.to_json(orient = “index”) my above code convert the dataframe into json format of dict like {index -> {column -> value}}

Here is small utility class that converts JSON to DataFrame and back: Hope you find this helpful.

# -*- coding: utf-8 -*-
from pandas.io.json import json_normalize

class DFConverter:

    #Converts the input JSON to a DataFrame
    def convertToDF(self,dfJSON):
        return(json_normalize(dfJSON))

    #Converts the input DataFrame to JSON 
    def convertToJSON(self, df):
        resultJSON = df.to_json(orient='records')
        return(resultJSON)

I think what the OP is looking for is:

with open('temp.json', 'w') as f:
    f.write(df.to_json(orient='records', lines=True))

This should do the trick.