I need to get the below Json data into a pandas DataFrame. This is a case of nested JSON which consists of multiple lists and dicts .
{ "status" : "success", "data" : { "resultType" : "vector", "result" : [ { "metric" : { "__name__":"request_time_summary_count", "api":"USSD", "instance":"10.104.3.50:8080", "job":"service-endpoints", "operation":"MO" }, "value": [ 1660136610.587, "3" ] }, { "metric" : { "__name__":"request_time_summary_count", "api":"USSD", "instance":"service.default.svc:8080", "job":"ETD-ussd", "operation":"MO" }, "value" : [ 1660136610.587, "4" ] } ] } }
Expected format as follows: enter image description here
Advertisement
Answer
Try:
data = { "status": "success", "data": { "resultType": "vector", "result": [ { "metric": { "__name__": "request_time_summary_count", "api": "USSD", "instance": "10.104.3.50:8080", "job": "service-endpoints", "operation": "MO", }, "value": [1660136610.587, "3"], }, { "metric": { "__name__": "request_time_summary_count", "api": "USSD", "instance": "service.default.svc:8080", "job": "ETD-ussd", "operation": "MO", }, "value": [1660136610.587, "4"], }, ], }, } df = pd.DataFrame(data["data"]["result"]) df = pd.concat([df.pop("metric").apply(pd.Series), df], axis=1) print(df)
Prints:
__name__ api instance job operation value 0 request_time_summary_count USSD 10.104.3.50:8080 service-endpoints MO [1660136610.587, 3] 1 request_time_summary_count USSD service.default.svc:8080 ETD-ussd MO [1660136610.587, 4]