Convert Pandas Dataframe to JSON format: how to group

Question

I have a Pandas Dataframe:

     nodes        x      y        z
0        1   0.0000  0.000   0.0000
1        2   0.0000  9.144   0.0000
2        3  19.5072  0.000   0.0000
3        4  19.5072  9.144   0.0000
4        5   9.7536  0.000   0.0000
..     ...      ...    ...      ...
175    176  19.5072  9.144  27.7368
176    177  19.5072  9.144  25.7556
177    178  19.5072  9.144  21.7932
178    179  19.5072  9.144  19.8120
179    180  19.5072  9.144  17.8308

to convert to JSON:

{
    "nodes": {
        "1": {
            "x": 0,
            "y": 0,
            "z": 0
        },
        "2": {
            "x": 0,
            "y": 9.144,
            "z": 0
        },
        "3": {
            "x": 19.5072,
            "y": 0,
            "z": 0
        },
        "4": {
            "x": 19.5072,
            "y": 9.144,
            "z": 0
        },
        "5": {
            "x": 9.7536,
            "y": 0,
            "z": 0
        },
    },
}

I just started to use python. After Google, I tried:

out = df.to_json(orient = 'records')
print(json.dumps(json.loads(out), indent=4))

It resulted in:

{
    "nodes": 1,
    "x": 0.0,
    "y": 0.0,
    "z": 0.0
},
{
    "nodes": 2,
    "x": 0.0,
    "y": 9.144,
    "z": 0.0
},
{
    "nodes": 3,
    "x": 19.5072,
    "y": 0.0,
    "z": 0.0
},

Please help. Thank you.

drec4s · Accepted Answer · 2020-11-12 11:54:55Z

1

You can take advantage of the groupby function to achieve your desired output:

out = {'nodes': df.groupby('nodes').last().to_dict(orient='index')}
print(json.dumps(out, indent=4))

Results in:

{
    "nodes": {
        "1": {
            "x": 0.0,
            "y": 0.0,
            "z": 0.0
        },
        "2": {
            "x": 0.0,
            "y": 9.144,
            "z": 0.0
        },
        "3": {
            "x": 19.5072,
            "y": 0.0,
            "z": 0.0
        },
        "4": {
            "x": 19.5072,
            "y": 9.144,
            "z": 0.0
        },
        "5": {
            "x": 9.7536,
            "y": 0.0,
            "z": 0.0
        }
    }
}

edited Nov 12, 2020 at 11:54

answered Nov 12, 2020 at 1:51

drec4s

8,1178 gold badges35 silver badges56 bronze badges

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

2 Comments

warem Over a year ago

@ drec4s. It works. Thank you. Could you please tell the function .last()?

warem Over a year ago

out = {'nodes': df.groupby('nodes').last().to_dict(orient='index')} worked; otherwise, you will get a warning

FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.

Collectives™ on Stack Overflow

Convert Pandas Dataframe to JSON format: how to group

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related