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