I have a json file and the outut is shown below:
{
"IdentityDocuments": [
{
"DocumentIndex": 1,
"IdentityDocumentFields": [
{
"Type": {
"Text": "FIRST_NAME"
},
"ValueDetection": {
"Text": "CLAUS",
"Confidence": 98.9039535522461
}
},
{
"Type": {
"Text": "LAST_NAME"
},
"ValueDetection": {
"Text": "SANTA",
"Confidence": 98.62335968017578
}
},
{
"Type": {
"Text": "MIDDLE_NAME"
},
"ValueDetection": {
"Text": "NOVA",
"Confidence": 99.01791381835938
}
},
{
"Type": {
"Text": "SUFFIX"
},
"ValueDetection": {
"Text": "",
"Confidence": 99.52413940429688
}
},
{
"Type": {
"Text": "CITY_IN_ADDRESS"
},
"ValueDetection": {
"Text": "",
"Confidence": 99.63380432128906
}
},
{
"Type": {
"Text": "ZIP_CODE_IN_ADDRESS"
},
"ValueDetection": {
"Text": "",
"Confidence": 99.66967010498047
}
},
{
"Type": {
"Text": "STATE_IN_ADDRESS"
},
"ValueDetection": {
"Text": "",
"Confidence": 99.638427734375
}
},
{
"Type": {
"Text": "STATE_NAME"
},
"ValueDetection": {
"Text": "",
"Confidence": 98.48577117919922
}
},
{
"Type": {
"Text": "DOCUMENT_NUMBER"
},
"ValueDetection": {
"Text": "GC000000",
"Confidence": 94.45845031738281
}
},
{
"Type": {
"Text": "EXPIRATION_DATE"
},
"ValueDetection": {
"Text": "",
"Confidence": 23.559080123901367
}
},
{
"Type": {
"Text": "DATE_OF_BIRTH"
},
"ValueDetection": {
"Text": "25 DEC IDEC",
"Confidence": 70.85164642333984
}
},
{
"Type": {
"Text": "DATE_OF_ISSUE"
},
"ValueDetection": {
"Text": "",
"Confidence": 42.78477478027344
}
},
{
"Type": {
"Text": "ID_TYPE"
},
"ValueDetection": {
"Text": "PASSPORT",
"Confidence": 99.32125091552734
}
},
{
"Type": {
"Text": "ENDORSEMENTS"
},
"ValueDetection": {
"Text": "",
"Confidence": 99.26473999023438
}
},
{
"Type": {
"Text": "VETERAN"
},
"ValueDetection": {
"Text": "",
"Confidence": 99.59288024902344
}
},
{
"Type": {
"Text": "RESTRICTIONS"
},
"ValueDetection": {
"Text": "",
"Confidence": 99.5494613647461
}
},
{
"Type": {
"Text": "CLASS"
},
"ValueDetection": {
"Text": "",
"Confidence": 99.61245727539062
}
},
{
"Type": {
"Text": "ADDRESS"
},
"ValueDetection": {
"Text": "",
"Confidence": 99.61029052734375
}
},
{
"Type": {
"Text": "COUNTY"
},
"ValueDetection": {
"Text": "",
"Confidence": 99.6097412109375
}
},
{
"Type": {
"Text": "PLACE_OF_BIRTH"
},
"ValueDetection": {
"Text": "",
"Confidence": 14.484281539916992
}
}
]
}
Wanted to populate values in excel like
| Type | Text |Confidence
| -------- | -------------- |--------------
| PLACE_OF_BIRTH | "" |14.484281539916992
| DATE_OF_BIRTH | ""
| -------- | -------------- |
CodePudding user response：
You can try pd.json_normalize then export dataframe to excel
import json
import pandas as pd
with open('data.json', 'r', encoding='utf-8') as f:
data = json.loads(f.read())
df = pd.json_normalize(data['IdentityDocuments'], record_path='IdentityDocumentFields')
df = df.rename(columns={'Type.Text': 'Type', 'ValueDetection.Text': 'Text', 'ValueDetection.Confidence': 'Confidence'})
df.to_excel("output.xlsx")
print(df)
Type Text Confidence
0 FIRST_NAME CLAUS 98.903954
1 LAST_NAME SANTA 98.623360
2 MIDDLE_NAME NOVA 99.017914
3 SUFFIX 99.524139
4 CITY_IN_ADDRESS 99.633804
5 ZIP_CODE_IN_ADDRESS 99.669670
6 STATE_IN_ADDRESS 99.638428
7 STATE_NAME 98.485771
8 DOCUMENT_NUMBER GC000000 94.458450
9 EXPIRATION_DATE 23.559080
10 DATE_OF_BIRTH 25 DEC IDEC 70.851646
11 DATE_OF_ISSUE 42.784775
12 ID_TYPE PASSPORT 99.321251
13 ENDORSEMENTS 99.264740
14 VETERAN 99.592880
15 RESTRICTIONS 99.549461
16 CLASS 99.612457
17 ADDRESS 99.610291
18 COUNTY 99.609741
19 PLACE_OF_BIRTH 14.484282
CodePudding user response：
You can achieve this using json with loads and json_normalize methods
import pandas as pd
import json
with open('data.json', 'r') as json_file:
json_value = json_file.read().replace('\n', '')
data = json.loads(json_value)
df = pd.json_normalize(data['IdentityDocuments'][0]['IdentityDocumentFields'])
df.to_excel('excel_from_json.xlsx')
Sample output from the generated excel:
