将嵌套json转换为具有特定输出的数据帧

Question

假设我有一些JSON，如下所示

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

我想把它转换成数据帧，如下所示

但是在尝试使用pandas.json_normalize()将其转换为数据帧时 我无法达到我想要的产量

我所尝试的：

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

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

但这不包括date、icp、icpr列

所以我尝试了不同的排列和组合

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

但最终还是出现了同样的错误TypeError: unhashable type: 'list'

Answer 1

你需要爆发

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

---

^{tb1}$