你如何使用python決議嵌套的JSON

我有這個 json 輸出檔案：

我有這個資料框，其中包含服務器資訊和配置資料來呼叫 api：

print(hyperEntitydf)

ServerName      Env                                 EnvId         HostId 
server123       prod  00000000000123                111111111      hhhh0000
server000       uat  000000000005555                2222222        y00000000

對于每臺服務器，我需要呼叫一個 api 來獲取每臺服務器的資料，如下所示：

for i in range(0, len(hyperEntitydf)):
    try:
        #api url
        processUrl="example.com"/ hyperEntitydf.iloc[i]['EnvId'] "/api/v2/metrics/query?metricSelector=" metric ":max&resolution=inf&from=" from_epoch "&to=" to_epoch "&entitySelector=Type("PROCESS_GROUP_INSTANCE"),fromRelationships.isProcessof(entityId("" hyperEntitydf.iloc[i]['HostId'] ""))"
        resp1 = requests.get(processUrl, verify=False,  headers={'Authorization': "Api-Token "   hyperEntitydf.iloc[i]['Token']}).json()
        for r in resp1['result']:
            metricId = r['metricId']
            for d in r['data']:
                dimension = d['dimensions'][0] 
                timestamps = d['timestamps']
                values = d['values']
                for t, v in zip(timestamps, values):
                    data.append({'metricId': metricId, 'dimensions': dimension,  'timestamps': t, 'values': v})
                    df = pd.DataFrame.from_records(data)
                    
                    df.columns=['Metric','ProcId','TimeStamp','Value']
                    df['Server']=hyperEntitydf.iloc[i]['ServerName']
                    finalDF = finalDF.append(df)
                    
    except Exception as e:
        print(e)

##respn1 輸出是這樣的： print(resp1)

{
  "totalCount": 39,
  "nextPageKey": null,
  "resolution": "Inf",
  "result": [
    {
      "metricId": "builtin:tech.generic.cpu.usage:max",
      "data": [
        {
          "dimensions": [
            "PROCESS_GROUP_123"
          ],
          "dimensionMap": {
            "dt.entity.process_group_instance": "PROCESS_GROUP_123"
          },
          "timestamps": [
            1647272580000
          ],
          "values": [
            1.3535032272338867
          ]
        },
        {
          "dimensions": [
            "PROCESS_GROUP_0000"
          ],
          "dimensionMap": {
            "dt.entity.process_group_instance": "PROCESS_GROUP_0000"
          },
          "timestamps": [
            1647272580000
          ],
          "values": [
            0.0390625
          ]
        },
        {
          "dimensions": [
            "PROCESS_GROUP_5555"
          ],
          "dimensionMap": {
            "dt.entity.process_group_instance": "PROCESS_GROUP_5555"
          },
          "timestamps": [
            1647272580000
          ],
          "values": [
            0
          ]
        }
      ]
    }
  ]
}

一旦我執行了這段代碼，看起來我正在為每臺服務器復制資料。

我的資料框需要如下所示：

Metric                              ProcId              TimeStamp       Value       Server
builtin:tech.generic.cpu.usage:max  PROCESS_GROUP_123   1647272580000   1.501560092 server123
builtin:tech.generic.cpu.usage:max  PROCESS_GROUP_0000  1647272580000   0.0390625   server123

正如用戶@not_speshal 所建議的那樣。我試圖決議 resp1 輸出如下：

df4 = (pd.json_normalize(resp1["result"], 
                       record_path="data", 
                       meta="metricId")
        .explode(["dimensions", "timestamps", "values"])
        .drop("dimensions", axis=1)
      )
df4.columns = ["Timestamp", "Value", "ProcId", "Metric"]
print(df4)

我收到此錯誤：

ValueError                                Traceback (most recent call last)
<ipython-input-67-6c175c457389> in <module>
----> 1 df4 = (pd.json_normalize(resp1["result"], 
      2                        record_path="data",
      3                        meta="metricId")
      4         .explode(["dimensions", "timestamps", "values"])
      5         .drop("dimensions", axis=1)

~\Anaconda3\lib\site-packages\pandas\core\frame.py in explode(self, column, ignore_index)
   7276         """
   7277         if not (is_scalar(column) or isinstance(column, tuple)):
-> 7278             raise ValueError("column must be a scalar")
   7279         if not self.columns.is_unique:
   7280             raise ValueError("columns must be unique")

ValueError: column must be a scalar

有什么想法我在這里做錯了嗎？

uj5u.com熱心網友回復：

IIUC，使用以下方法決議回圈中的每個 json json_normalize：

df = (pd.json_normalize(resp1["result"], 
                       record_path="data", 
                       meta="metricId")
        .drop("dimensions", axis=1)
      )
df.columns = ["Timestamp", "Value", "ProcId", "Metric"]

>>> df
         Timestamp                 Value              ProcId  \
0  [1647272580000]  [1.3535032272338867]   PROCESS_GROUP_123   
1  [1647272580000]           [0.0390625]  PROCESS_GROUP_0000   
2  [1647272580000]                   [0]  PROCESS_GROUP_5555   

                               Metric  
0  builtin:tech.generic.cpu.usage:max  
1  builtin:tech.generic.cpu.usage:max  
2  builtin:tech.generic.cpu.usage:max  

標籤：Python json

上一篇：python從另一個json檔案中完成檔案中的json物件

下一篇：使用通過ID連接資料的2個json陣列