How to load JSON to Dataframe with key value pair as two colums-CodePudding

I have some JSON, that I would like to load into a dataframe, but would like to retain the key/value pair structure.

I've tried this:

    body = '''{
        "groupId": "1",
        "categories":[
            {
                "model":"xxx",
                "colour":"Black",
                "width":"100",
                "height":"200"
            }
        ]
    }'''
    
    categories = json.loads(body)['categories']
    
    dfQuery = pd.DataFrame(categories)
    
    print(dfQuery)

and that gives me this:

But I need it pivoted and to alias the columns so that it appears like this:

I've tried transposing, but can't work out how to alias the 2 columns:

dfQuery = pd.DataFrame.from_dict(categories).T

CodePudding user response：

Transposing is right, but you also need to reset the index and rename the columns:

dfQuery = dfQuery.T.reset_index().rename({'index': 'type', 0: 'value'}, axis=1)

Output:

>>> dfQuery
     type  value
0   model    xxx
1  colour  Black
2   width    100
3  height    200