3

I have a Pandas DataFrame like below: 

ID | Category | Description | Score
-----------------------------------
1  |    1     | Desc 1      | 20.0
2  |    1     | Desc 2      | 30.0
3  |    1     | Desc 3      | 30.0
4  |    2     | Desc 4      | 50.0
5  |    2     | Desc 5      | 50.0
6  |    3     | Desc 6      | 55.0

From this DataFrame, I have to get a JSON output in below format:

{
"name": "Category",
"children":             
    [
        {
            "name": "1",
            "children": 
            [
                {
                "name": "ID: 1",
                "Description": "Desc 1",
                "Score": 20.0
                } 
                {
                "name": "ID: 2",
                "Description": "Desc 2",
                "Score": 30.0
                } 
                {
                "name": "ID: 3",
                "Description": "Desc 3",
                "Score": 30.0
                } 
            ]
        },
        {
            "name": "2",
            "children": 
            [
                {
                "name": "ID: 4",
                "Description": "Desc 4",
                "Score": 50.0
                } 
                {
                "name": "ID: 5",
                "Description": "Desc 5",
                "Score": 50.0
                } 
            ]
        }
        {
            "name": "3",
            "children": 
            [
                {
                "name": "ID: 6",
                "Description": "Desc 6",
                "Score": 55.0
                } 
            ]               
        }
    ]
}

"name" and "children" should appear as shown above (even though these are not present as columns in the DataFrame).
I am new to this and don't have much idea on how to go about getting this output. I searched here and went through several similar posts.
I specifically looked into the following post: Userdefined Json Format From Pandas DataFrame which is similar to what I want. The answer mentioned in this post is not working though. I couldn't figure out how to proceed from there onward to get my desired output.
Could you please guide me on how to achieve this?

Community
  • 1
  • 1
Ram Kumar V
  • 103
  • 3
  • 8

1 Answers1

3

thanks @IanS
I took idea from your code and I used the below snippet to get my output: 

cList = []
groupDict = outputDF.groupby('Category').apply(lambda g: g.drop('Category', axis=1).to_dict(orient='records')).to_dict()
for key, value in groupDict.items():
    cList.append(dict(name=str(key)), children=value)
finalJSON = dict(name='Category', children=cList)
finalJSON = json.dumps(finalJSON)
print(finalJSON)
Ram Kumar V
  • 103
  • 3
  • 8