Export JSON from dataframe in Pandas

Question

I have a dataframe like this

A	B	tc-01	tc-02
bapc	act	1	1
bapc	bp	2	2
bapc	ly	3	3
bapc	vsLy	4	4
bapc	bsBp	5	5
nsr	act	6	6
nsr	bp	7	7
nsr	ly	8	8
nsr	vsLy	9	9
nsr	bsBp	10	10

How can I export dataframe to the json file like this

{               
    "expect": {         
        "tc-01": {      
            "bapc":{    
                "act":1,
                "bp":2,
                "ly":3,
                "vsLy":4,
                "bsBp":5
            },  
            "nsr":{ 
                "act":6,
                "bp":7,
                "ly":8,
                "vsLy":9,
                "bsBp":10
            }
        },
        "tc-02": {      
            "bapc":{    
                "act":1,
                "bp":2,
                "ly":3,
                "vsLy":4,
                "bsBp":5
            },  
            "nsr":{ 
                "act":6,
                "bp":7,
                "ly":8,
                "vsLy":9,
                "bsBp":10
            }
        }
    }           
}

It seems that pandas doesn't support this format out of the box so you have to write your own converter. — Michael Butscher
– Michael Butscher, Commented Sep 16, 2022 at 3:34

Scott Boston · Accepted Answer · 2022-09-16 04:07:14Z

2

Try something like this:

df.melt(['A', 'B']).set_index(['variable', 'A', 'B'])\
  .unstack('B')['value'].groupby('variable')\
  .apply(lambda x: x.droplevel(0).to_dict('index')).to_dict()

Output:

{'tc-01': {'bapc': {'act': 1, 'bp': 2, 'bsBp': 5, 'ly': 3, 'vsLy': 4},
  'nsr': {'act': 6, 'bp': 7, 'bsBp': 10, 'ly': 8, 'vsLy': 9}},
 'tc-02': {'bapc': {'act': 1, 'bp': 2, 'bsBp': 5, 'ly': 3, 'vsLy': 4},
  'nsr': {'act': 6, 'bp': 7, 'bsBp': 10, 'ly': 8, 'vsLy': 9}}}

answered Sep 16, 2022 at 4:07

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

Comments

cottontail · Accepted Answer · 2022-10-04 15:40:14Z

Use itertuples() to loop over the dataframe and build a dictionary iteratively. Iterating with itertuples() is probably the most efficient method for the operation required in the OP as well.

import json
res = {'expect': {}}
for a, b, tc01, tc02 in df.itertuples(index=False):
    # insert the key if it doesn't exist for the inner dicts
    # by using `setdefault()`
    res['expect'].setdefault('tc-01', {}).setdefault(a, {})[b] = tc01
    res['expect'].setdefault('tc-02', {}).setdefault(a, {})[b] = tc02
j_var = json.dumps(res, indent=4)
print(j_var)

{
    "expect": {
        "tc-01": {
            "bapc": {
                "act": 1,
                "bp": 2,
                "ly": 3,
                "vsLy": 4,
                "bsBp": 5
            },
            "nsr": {
                "act": 6,
                "bp": 7,
                "ly": 8,
                "vsLy": 9,
                "bsBp": 10
            }
        },
        "tc-02": {
            "bapc": {
                "act": 1,
                "bp": 2,
                "ly": 3,
                "vsLy": 4,
                "bsBp": 5
            },
            "nsr": {
                "act": 6,
                "bp": 7,
                "ly": 8,
                "vsLy": 9,
                "bsBp": 10
            }
        }
    }
}

Shubham Sharma · Accepted Answer · 2022-09-16 04:11:45Z

1

Lets do some reshaping with stack, unstack and pivot

s = df.pivot('A', 'B').stack(0)
s.assign(r=s.to_dict('records'))['r'].unstack().to_dict()

{'tc-01': {'bapc': {'act': 1, 'bp': 2, 'bsBp': 5, 'ly': 3, 'vsLy': 4},
           'nsr': {'act': 6, 'bp': 7, 'bsBp': 10, 'ly': 8, 'vsLy': 9}},
 'tc-02': {'bapc': {'act': 1, 'bp': 2, 'bsBp': 5, 'ly': 3, 'vsLy': 4},
           'nsr': {'act': 6, 'bp': 7, 'bsBp': 10, 'ly': 8, 'vsLy': 9}}}

answered Sep 16, 2022 at 4:11

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

Collectives™ on Stack Overflow

Export JSON from dataframe in Pandas

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related