如何在Python中正确地展开此json响应，以将所需的数据放入数据帧中？

Question

因此，我正在使用Cisco Prime Infrastructure的API，我已经穷途末路了。我正在编写的脚本应该从getReport操作中获取一个报告，对其进行解码（不确定这个词是否正确），然后将其放入一个数据帧中。问题是，有垃圾包裹着它。下面是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"
    } ]
  }
}

我希望我的脚本只使用嵌套在“dataRows”下的信息，但我不知道该怎么做。到目前为止，我有：

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

我得到的回报是：

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

[1 rows x 4 columns]

我试过只使用请求中的.text方法，我试过使用另一个json扁平化库（json_flatte），并带有排除某些键的选项，我正在考虑以某种方式在python中使用sed。它不需要为其他报告工作，只有一个报告，所以我有一些余地来指定任何特定的键或诸如此类的内容。你们怎么解决这个问题

Answer 1

我的假设是，您希望基于传入的值构建一个panda DF。加载可能会有所帮助，因为“object_hook”可用于提供自定义反序列化

import pandas as pd
import numpy as np
import json


def filter_cols(element):
    # deal with each dataRow and build your the structure that will get into the data frame
    return {
        element['attributeName']: element['dataValue'],
    }



if __name__ == '__main__':
    # from api response
    # content = json.loads(response.text)

    # doing this because I have your data in a file
    with open("data.json", "r") as read_file:
        content = json.load(read_file)

    f = pd.DataFrame.from_dict(
        json.loads(json.dumps(content['mgmtResponse']['reportDataDTO'][0]['dataRows']), object_hook=filter_cols))

Answer 2

你可以试试这样的。您似乎对json响应的格式感到有点困惑，我试图解决我在下面看到的结构，但请仔细看看所有内容是如何嵌套的

import requests
import pandas as pd
df = pd.DataFrame()
req_session = requests.Session()
response = req_session.get('<url>')
df.append(response.json()['mgmtResponse']['reportDataDTO'][0]['dataRows'])

Answer 3

您应该能够通过以下方式获得dataRows内容：

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

输出

{
    "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"
                    }
                ]
            }
        }
    ]
}