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

Question

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

You seem to be trying to format your JSON as code, basically a good idea, the formatting would be done with backticks instead of simple ticks. Also, you will probably have to provide more non-code explanation, before the system lets you format most of your post as code. stackoverflow.com/editing-help — Yunnosch
– Yunnosch, Commented Dec 11, 2022 at 21:37
Thanks for the inputs. I will keep that in mind.I referred the link which was helpful. Reference: geeksforgeeks.org/convert-nested-json-to-csv-in-python Thanks Yunnosch for the informative link — AchillesCK
– AchillesCK, Commented Dec 16, 2022 at 8:50
You can also check out json-to-df package which converts complex nested jsons to dataframes. Link: pypi.org/project/json-to-df — sourin_karmakar
– sourin_karmakar, Commented Mar 11 at 6:14

Bushmaster · Accepted Answer · 2022-12-11 21:36:36Z

1

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

edited Dec 11, 2022 at 21:36

answered Dec 11, 2022 at 20:57

Bushmaster

4,6364 gold badges11 silver badges34 bronze badges

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

1 Comment

AchillesCK Over a year ago

Thanks, Bushmaster.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related