对 Pandas 数据帧的深度嵌套 JSON 响应

Deeply nested JSON response to pandas dataframe(对 Pandas 数据帧的深度嵌套 JSON 响应)

问题描述

我是 python/pandas 的新手，在将嵌套的 JSON 转换为 Pandas 数据帧时遇到了一些问题.我正在向数据库发送查询并返回一个 JSON 字符串.

I am new to python/pandas and I am having some issues with converting a nested JSON to pandas dataframe. I am sending a query to a database and getting a JSON string back.

这是一个深度嵌套的 JSON 字符串，包含多个数组.来自数据库的响应包含数千行.以下是 JSON 字符串中一行的一般结构:

It's a deeply nested JSON string that contains several arrays. The response from the database contains thousands of rows. Here is the general structure of one row in the JSON string:

{
  "ID": "123456",
  "profile": {
    "criteria": [
      {
        "type": "type1",
        "name": "name1",
        "value": "7",
        "properties": []
      },
      {
        "type": "type2",
        "name": "name2",
        "value": "6",
        "properties": [
          {
            "type": "MAX",
            "name": "",
            "value": "100"
          },
          {
            "type": "MIN",
            "name": "",
            "value": "5"
          }
        ]
      },
      {
        "type": "type3",
        "name": "name3",
        "value": "5",
        "properties": []
      }
    ]
  }
}  
{
  "ID": "456789",
  "profile": {
    "criteria": [
      {
        "type": "type4",
        "name": "name4",
        "value": "6",
        "properties": []
      }
    ]
  }
}

我想使用 python pandas 将这个 JSON 字符串展平.我在使用 json_normalize 时遇到了问题，因为这是一个深度嵌套的 JSON 字符串:

I want to flatten this JSON string using python pandas. I had problems using json_normalize since this is a deeply nested JSON string:

from cassandra.cluster import Cluster
import pandas as pd
from pandas.io.json import json_normalize

def pandas_factory(colnames, rows):
    return pd.DataFrame(rows, columns=colnames)

cluster = Cluster(['xxx.xx.x.xx'], port=yyyy)
session = cluster.connect('nnnn')

session.row_factory = pandas_factory

json_string = session.execute('select json ......')
df = json_string ._current_rows
df_normalized= json_normalize(df)
print(df_normalized)

当我运行此代码时，我收到一个关键错误:

When i run this code, i get a Key error:

KeyError: 0

我需要帮助将此 JSON 字符串转换为只有一些选定列的数据框，看起来像这样:(其余数据可以跳过)

I need help converting this JSON string to a dataframe with only some selected columns that looks something like this: (The rest of the data can be skipped)

ID        |   criteria   | type   |   name   |   value   |

123456          1          type1      name1        7      
123456          2          type2      name2        6  
123456          3          type3      name3        5    
456789          1          type4      name4        6

我试图在这里找到类似的问题，但我似乎无法将其应用于我的 JSON 字符串.

I tried to find similar problems on here but I can't seem to apply it to my JSON string.

感谢任何帮助！:)

返回的 json 字符串是一个 查询响应对象: ResultSet .我认为这就是我在使用时遇到一些问题的原因:

The json string that is retured is a query response object: ResultSet . I think thats why I'm having some issues with using:

json_string= session.execute('select json profile from visning')
temp = json.loads(json_string)

并得到错误:

TypeError: the JSON object must be str, not 'ResultSet'

编辑 #2:

只是为了看看我在做什么，我使用以下方法打印了结果查询:

Just to see what I'm working with, I printed the the result query by using:

for line in session.execute('select json.....'):
    print(line)

得到这样的东西:

Row(json='{"ID": null, "profile": null}')
Row(json='{"ID": "123", "profile": {"criteria": [{"type": "type1", "name": "name1", "value": "10", "properties": []}, {"type": "type2", "name": "name2", "value": "50", "properties": []}, {"type": "type3", "name": "name3", "value": "40", "properties": []}]}}')
Row(json='{"ID": "456", "profile": {"criteria": []}}')
Row(json='{"ID": "789", "profile": {"criteria": [{"type": "type4", "name": "name4", "value": "5", "properties": []}]}}')
Row(json='{"ID": "987", "profile": {"criteria": [{"type": "type5", "name": "name5", "value": "70", "properties": []}, {"type": "type6", "name": "name6", "value": "60", "properties": []}, {"type": "type7", "name": "name7", "value": "2", "properties": []}, {"type": "type8", "name": "name8", "value": "7", "properties": []}]}}')

我遇到的问题是将此结构转换为可在 json.loads() 中使用的 json 字符串:

The issue I'm having is converting this structure to a json string that can be used in json.loads():

json_string= session.execute('select json profile from visning')
json_list = list(json_string)
string= ''.join(list(map(str, json_list)))
temp = json.loads(string) <-- creates error json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)

编辑 #3:

按照下面评论中的要求，打印

As requested below in the comments, printing

for line in session.execute('select json.....'):
print((line.json))

得到输出:

{"ID": null, "profile": null}
{"ID": "123", "profile": {"criteria": [{"type": "type1", "name": "name1", "value": "10", "properties": []}, {"type": "type2", "name": "name2", "value": "50", "properties": []}, {"type": "type3", "name": "name3", "value": "40", "properties": []}]}}
{"ID": "456", "profile": {"criteria": []}}
{"ID": "789", "profile": {"criteria": [{"type": "type4", "name": "name4", "value": "5", "properties": []}]}}
{"ID": "987", "profile": {"criteria": [{"type": "type5", "name": "name5", "value": "70", "properties": []}, {"type": "type6", "name": "name6", "value": "60", "properties": []}, {"type": "type7", "name": "name7", "value": "2", "properties": []}, {"type": "type8", "name": "name8", "value": "7", "properties": []}]}}

推荐答案

一个硬编码的例子...

A hardcoded example...

import pandas as pd

temp = [{
  "ID": "123456",
  "profile": {
    "criteria": [
      {
        "type": "type1",
        "name": "name1",
        "value": "7",
        "properties": []
      },
      {
        "type": "type2",
        "name": "name2",
        "value": "6",
        "properties": [
          {
            "type": "MAX",
            "name": "",
            "value": "100"
          },
          {
            "type": "MIN",
            "name": "",
            "value": "5"
          }
        ]
      },
      {
        "type": "type3",
        "name": "name3",
        "value": "5",
        "properties": []
      }
    ]
  }
},
{
  "ID": "456789",
  "profile": {
    "criteria": [
      {
        "type": "type4",
        "name": "name4",
        "value": "6",
        "properties": []
      }
    ]
  }
}]

cols = ['ID', 'criteria', 'type', 'name', 'value']

rows = []
for data in temp:
    data_id = data['ID']
    criteria = data['profile']['criteria']
    for d in criteria:
        rows.append([data_id, criteria.index(d)+1, *list(d.values())[:-1]])

df = pd.DataFrame(rows, columns=cols)

这绝不是优雅的.它更像是一个快速而肮脏的解决方案，因为我不知道 JSON 数据的确切格式 - 但是，根据您提供的内容，我上面的代码将生成所需的 DataFrame.

This is by no means elegant. It is more of a quick and dirty solution, as I don't know how the JSON data is exactly formatted - however, based on what you've provided, my code above will produce the desired DataFrame.

       ID  criteria   type   name value
0  123456         1  type1  name1     7
1  123456         2  type2  name2     6
2  123456         3  type3  name3     5
3  456789         1  type4  name4     6

此外，如果您需要加载"JSON 数据，您可以像这样使用 json 库:

Additionally, if you need to 'load' the JSON data, you can use the json library like so:

import json

temp = json.loads(json_string)

# Or from a file...
with open('some_json.json') as json_file:
    temp = json.load(json_file)

请注意json.loads和json.load的区别.

这篇关于对 Pandas 数据帧的深度嵌套 JSON 响应的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！