Home > Blockchain >  Export JSON from dataframe in Pandas
Export JSON from dataframe in Pandas

Time:09-16

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
            }
        }
    }           
}

CodePudding user response:

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}}}

CodePudding user response:

Use itertuples() to loop over the dataframe and build a dictionary iteratively.

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
import json
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
            }
        }
    }
}

CodePudding user response:

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}}}
  • Related