converting nested json into dataframe with a specific output

Question

Let's say I have some JSON as below

response = {
        "totalrecords": 2,
        "data": [
            {
                "stateCd": "U.K",
                "stateName": "uttarakhand",
                "details": {
                    "id": [
                        "2312-k",
                        "2312-k"
                    ],
                    "date": [
                        "10-OCT-2019",
                        "11-OCT-2019"
                    ],
                    "icp": [
                        2233,
                        6443
                    ],
                    "icpr": [
                        3.434,
                        23.232
                    ]
                }
            },
            {
                "stateCd": "U.P",
                "stateName": "uttar pradesh",
                "details": {
                    "id": [
                        "2712-k",
                        "5412-k"
                    ],
                    "date": [
                        "10-OCT-2019",
                        "11-OCT-2019"
                    ],
                    "icp": [
                        2233,
                        6443
                    ],
                    "icpr": [
                        32.434,
                        31.232
                    ]
                }
            }
        ]
    }

I wanted to convert it into data frame as below

enter image description here

but while trying to convert it into dataframe using pandas.json_normalize() I am unable to reach my desired output

what I have tried:

data_trunc=response['data'] # to extract data from response
pd.json_normalize(data_trunc)

enter image description here

pd.json_normalize(data_trunc,record_path=['details','id'],meta=['stateCd','stateName'])

enter image description here

but this don't include date, icp, icpr columns

so I have tried different permutation and combination

    pd.json_normalize(data_trunc,record_path=[['details','id'],['date']],meta=['stateCd','stateName'])

pd.json_normalize(data_trunc,record_path=[['details','id'],['details'.'date']],meta=['stateCd','stateName'])

but landed up to same error TypeError: unhashable type: 'list'

Your response = {...} is not actually JSON, it is a Python dict, which looks like JSON when formatted like this. (although that's okay here, as pandas.json_normalize() does take dict input) — djvg
– djvg, Commented Jun 10, 2021 at 14:12

Pygirl · Accepted Answer · 2021-06-10 13:48:33Z

2

You need to explode.

pd.json_normalize(data_trunc).apply(pd.Series.explode)

	stateCd	stateName	details.id	details.date	details.icp	details.icpr
0	U.K	uttarakhand	2312-k	10-OCT-2019	2233	3.434
0	U.K	uttarakhand	2312-k	11-OCT-2019	6443	23.232
1	U.P	uttar pradesh	2712-k	10-OCT-2019	2233	32.434
1	U.P	uttar pradesh	5412-k	11-OCT-2019	6443	31.232

answered Jun 10, 2021 at 13:48

Pygirl

13.4k6 gold badges36 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

converting nested json into dataframe with a specific output

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