Not able to get all the columns using json normalise-CodePudding

I have been trying to get all the records from the below-nested dictionary but not being able to fetch fields like pkey, code.

{'instrument': {'instrumentId': 15873, 'instrumentSymbol': 'EXCH-XCBT-CBA', 'instrumentType': 'holiday', 'created': '2020-01-10T06:39:27Z'}, 'mappings': [{'pkey': 40295, 'provider': 'pv', 'system': 'holidays', 'mapping': {'code': 'CBA'}, 'created': '2020-01-10T06:39:28', 'updated': '2020-01-10T06:39:28', 'startDate': '2020-01-10', 'endDate': None}]}

import requests
import json, pandas as pd
from pandas.io.json import json_normalize
import csv
import urllib

URL = "https://dsv.ihsmvals.com/instruments/v2.0/mappings/instruments/all/provider/pv/system/holidays"
response = urllib.request.urlopen(URL)

print(response)
text = response.read()
json_data = json.loads(text)
df_rest = pd.DataFrame(json_data)
df=pd.json_normalize(json_data,'mappings')
complete_df = pd.concat([df_rest,df],axis=1)
complete_df.to_csv("output.csv")

This is how the output looks at the moment missing few relevant fields like pkey, code

CodePudding user response：

Here the solution for the entire data:

import pandas as pd
import json

f = open("api_data.json", "r")
data = json.load(f)
arr1 = []
arr2 = []
for item in data['mappings']:
    tmp1 = {'instrument': [item['instrument']]}
    tmp2 = {'mappings': [item['mappings'][0]]}
    arr1.append(tmp1)
    arr2.append(tmp2)


df_rest = pd.json_normalize(arr1,'instrument')
df_rest2 = pd.json_normalize(arr2,'mappings')
df=pd.json_normalize(data,'mappings')
complete_df = pd.concat([df_rest, df_rest2], axis=1)
print(complete_df)