如何将含多列表字典的嵌套JSON转换为pandas DataFrame？

需要将以下嵌套JSON数据转换为指定结构的Pandas DataFrame：

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

实现代码 #

import pandas as pd

# 假设JSON数据已加载到变量json_data中
json_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" ]
         }
      ]
   }
 }

# 提取并整理数据
processed_data = []
for item in json_data["data"]["result"]:
    # 合并metric字典与拆分后的value字段
    row = item["metric"].copy()
    row["timestamp"] = item["value"][0]
    row["count"] = item["value"][1]
    processed_data.append(row)

# 转换为DataFrame并调整列顺序
df = pd.DataFrame(processed_data)
df = df[["__name__", "api", "instance", "job", "operation", "timestamp", "count"]]

# 查看结果
print(df)

最终输出格式 #

内容的提问来源于stack exchange，提问作者ZN K