Get nested Json data into pandas DataFrame-CodePudding

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

CodePudding user response：

You can use the json_normalize() method

CodePudding user response：

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]