Get nested Json data into pandas DataFrame

Question

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

Reda Lamtoueh · Accepted Answer · 2022-09-28 10:06:11Z

1

You can use the json_normalize() method

answered Sep 28, 2022 at 10:06

Reda Lamtoueh

112 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thomas Kimber Over a year ago

I'm yet to try this out - is this a new feature in Pandas? Any usage examples would be welcome - thanks for posting!

Reda Lamtoueh Over a year ago

check out this link to know more on how to use it geeksforgeeks.org/…

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Andrej Kesely · Accepted Answer · 2022-09-28 13:18:58Z

0

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]

answered Sep 28, 2022 at 13:18

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

2 Comments

ZN K Over a year ago

Thanks a lot. It works. But one more thing , The entire dataset needs to be taken , 'status' and 'data.resultType' as well.

Andrej Kesely Over a year ago

@ZNK Then do afterwards df['status'] = data['status'] and df['data.resultType'] = data['data']['resultType']

Collectives™ on Stack Overflow

Get nested Json data into pandas DataFrame

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related