Python Dictionary to Json with dataframe.to

I am trying to convert dictionary to json, and one of dictionary values is from dataframe.to_json, and I got some strange output as following:

Here is the code

import json
import pandas as pd

my_dict = {}

my_dict["ClassName"] = "First class"
# get student list
df = pd.read_csv("./test.csv")
my_dict["StudentList"] = df.to_json(orient='records')

# output
with open("./output.json", 'w') as fp:
    json.dump(my_dict, fp, indent=4)

Here is the input file ./test.csv

Name,Age
Joe,20
Emily,22
John,21
Peter,23

Here is the output file ./output.json

{
    "ClassName": "First class",
    "StudentList": "[{\"Name\":\"Joe\",\"Age\":20},{\"Name\":\"Emily\",\"Age\":22},{\"Name\":\"John\",\"Age\":21},{\"Name\":\"Peter\",\"Age\":23}]"
}

Here is what I need:

{
    "ClassName": "First class",
    "StudentList": [{"Name":"Joe","Age":20},{"Name":"Emily","Age":22},{"Name":"John","Age":21},{"Name":"Peter","Age":23}]
}

Thanks for any help.

CodePudding user response：

Use df.to_dict instead of df.to_json:

my_dict["StudentList"] = df.to_dict(orient='records')

to_json just returns a string representation of the JSON, while to_dict returns an actual JSON object.