[FIXED] How do I properly unwrap this json response in Python to get the data I need into a Pandas DataFrame?

Issue

So, I'm working with Cisco Prime Infrastructure's API and I'm at the end of my rope. The script I'm writing is supposed to take a report from its getReport operation, decode it (not sure if that's the right word), and put it into a Pandas DataFrame. The problem is, there's junk wrapped around it. Here's an example of the response json:

{
  "mgmtResponse" : {
    "@requestUrl" : "https://localhost/webacs/api/v4/op/reportService/getReport?reportTitle=MyReport",
    "@responseType" : "operation",
    "@rootUrl" : "https://localhost/webacs/api/v4/op",
    "reportDataDTO" : [ {
      "childReports" : {
        "childReport" : [ ]
      },
      "dataRows" : {
        "dataRow" : [ {
          "entries" : {
            "entry" : [ {
              "attributeName" : "String value",
              "dataValue" : "String value",
              "displayName" : "String value"
            }, {
              "attributeName" : "Another string value",
              "dataValue" : "Another string value",
              "displayName" : "Another string value"
            } ]
          }
        }, {
          "entries" : {
            "entry" : [ {
              "attributeName" : "String value",
              "dataValue" : "String value",
              "displayName" : "String value"
            }, {
              "attributeName" : "Another string value",
              "dataValue" : "Another string value",
              "displayName" : "Another string value"
            } ]
          }
        } ]
      },
      "descriptorName" : "String value",
      "pageCount" : 15,
      "pageIndex" : 15,
      "reportDate" : "String value",
      "reportName" : "String value",
      "reportTitle" : "String value",
      "reportUrl" : "String value"
    } ]
  }
}

I want my script to only use the information nested under "dataRows", but I can't figure out what to do. So far I have this:

response = rq.get(url, auth=(cpi_user,cpi_password), verify=False, timeout = 300)
    print(response.status_code)

    if (response.status_code == rq.codes.ok):
        responseJSON = response.json()
        rogue_ap_flatten = json_normalize(responseJSON)
        print (rogue_ap_flatten)
        rogues = pd.DataFrame(rogue_ap_flatten)
        print(rogues.head(50))
        return rogues

And all I get in return is:

                               mgmtResponse.@requestUrl  ...                         mgmtResponse.reportDataDTO
    0  https://prime/webacs/api/v4/op/reportS...  ...  [{'childReports': {'childReport': []}, 'dataRo...

[1 rows x 4 columns]

I've tried using just the .text method from requests, I've tried using another json flattening library (json_flatten) with the option to exclude certain keys, and I'm considering just using sed in python somehow. It doesn't need to work for other reports, just one, so I have some leeway to specify any specific keys or whatnot. How would y'all tackle this problem?

Solution

You should be able to get to just the dataRows stuff using something like this:

import json
data = {<your data>}
print(json.dumps(result['mgmtResponse']['reportDataDTO'][0]['dataRows'], indent=4))

Output

{
    "dataRow": [
        {
            "entries": {
                "entry": [
                    {
                        "attributeName": "String value",
                        "dataValue": "String value",
                        "displayName": "String value"
                    },
                    {
                        "attributeName": "Another string value",
                        "dataValue": "Another string value",
                        "displayName": "Another string value"
                    }
                ]
            }
        },
        {
            "entries": {
                "entry": [
                    {
                        "attributeName": "String value",
                        "dataValue": "String value",
                        "displayName": "String value"
                    },
                    {
                        "attributeName": "Another string value",
                        "dataValue": "Another string value",
                        "displayName": "Another string value"
                    }
                ]
            }
        }
    ]
}

Answered By - David Cullen

Answer Checked By - Katrina (PHPFixing Volunteer)