Python Converting Nested JSON with Array of Objects, and Objects to Excel-CodePudding

Can someone help me in converting the below JSON file to Excel?

Excel Output format

metaData.user report.from report.to lossInfo.locationName.Name lossInfo.locationName.locNbr lossInfo.locationDetails.locNm lossInfo.locationDetails.locAddress.locCity

Test User 12-12-2021 12-12-2022 test1 12 xyz abc

Test User 12-12-2021 12-12-2022 test2 121 xyz1 abc1

{
    "metaData": {
        "formName": "A1",
        "user": "Test User"
    },
    "report": {
        "from": "12/12/2021",
        "to": "12/12/2022"
    },
    "lossInfo": [
        {
            "locationName": {
                "name": "test1",
                "locNbr": "12"
            },
            "locationDetails": {
                "locNm": "xyz",
                "locAddress": {
                    "locCity": "abc",
                    "locStateCd": "abcd"
                },
                "state": "ab",
                "lossLocation": "cd"
            }
        },
        {
            "locationName": {
                "name": "test11",
                "locNbr": "121"
            },
            "locationDetails": {
                "locNm": "xyz1",
                "locAddress": {
                    "locCity": "abc1",
                    "locStateCd": "abcd1"
                },
                "state": "ab1",
                "lossLocation": "cd1"
            }
        }
    ]
}

CodePudding user response：

The json format you gave is wrong. There is no comma before the key named locationDetails. You can use json_normalize after you fix it.

df = pd.json_normalize(json_data,meta=['lossInfo']).explode('lossInfo').reset_index(drop=True)
df = df.join(pd.json_normalize(df.pop('lossInfo')))
'''
|    | metaData.formName   | metaData.user   | report.from   | report.to   | locationName.name   |   locationName.locNbr | locationDetails.locNm   | locationDetails.locAddress.locCity   | locationDetails.locAddress.locStateCd   | locationDetails.state   | locationDetails.lossLocation   |
|---:|:--------------------|:----------------|:--------------|:------------|:--------------------|----------------------:|:------------------------|:-------------------------------------|:----------------------------------------|:------------------------|:-------------------------------|
|  0 | A1                  | Test User       | 12/12/2021    | 12/12/2022  | test1               |                    12 | xyz                     | abc                                  | abcd                                    | ab                      | cd                             |
|  1 | A1                  | Test User       | 12/12/2021    | 12/12/2022  | test11              |                   121 | xyz1                    | abc1                                 | abcd1                                   | ab1                     | cd1                            |
'''