CODEWITHSUNDEEP
pythonjsonpandasdataframe
Kallol
Kallol

Reputation: 2189

convert pandas dataframe to json with columns as key

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

Upvotes: 2

Views: 6738

Answers (1)

jezrael
jezrael

Reputation: 862511

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'))

Upvotes: 2

Related Questions