convert pandas dataframe to json with columns as key

Question

I have a data frame like this:

df:
Col1    col2    col3    col4
 A       1        2       3
 A       4        5       6
 A       7        8       9
 B       3        2       1
 B       4        4       4

I want to create a nested json file for each col1 values, the inside it there will be col2, col3, col4 as keys and the value of those columns,

The output should look like:

{
"col1": A,
"Other_details": {
    "col2": 1,
    "col3": 2,
    "col4": 3
},{
    "col2": 4,
    "col3": 5,
    "col4": 6
},{
    "col2": 7,
    "col3": 8,
    "col4": 9
},
},
{
"col1": B,
"Other_details": {
    "col2": 3,
    "col3": 2,
    "col4": 1
},{
    "col2": 4,
    "col3": 4,
    "col4": 4
}
}

How to do it most efficient way

jezrael · Accepted Answer · 2019-03-05 14:27:11Z

2

Use DataFrame.groupby with DataFrame.apply and DataFrame.to_dict all columns with no Col1 filtered by Index.difference, create DataFrame by DataFrame.reset_index and last use DataFrame.to_dict for dictionary output or DataFrame.to_json for json output:

cols = df.columns.difference(['Col1'])
d = (df.groupby('Col1')[cols]
        .apply(lambda x: x.to_dict('r'))
        .reset_index(name='Other_details')
        .to_dict(orient='records'))

cols = df.columns.difference(['Col1'])
d = (df.groupby('Col1')[cols]
        .apply(lambda x: x.to_dict('r'))
        .reset_index(name='Other_details')
        .to_json(orient='records'))

answered Mar 5, 2019 at 14:27

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

convert pandas dataframe to json with columns as key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related