custom JSON format output from pandas dataframe

Question

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?

This could help you get started: df.groupby('Category').apply(lambda g: g.drop('Category', axis=1).to_dict(orient='records')).to_dict() — IanS
– IanS, Commented Jan 12, 2017 at 12:50

Ram Kumar V · Accepted Answer · 2017-01-16 09:30:52Z

4

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)

answered Jan 16, 2017 at 9:30

Ram Kumar V

1233 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

custom JSON format output from pandas dataframe

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related