我正在学习处理我认为相当复杂的JSON结构，并试图把它加载到一个数据框中。我希望每个outcome.id对应一条记录。下面是示例JSON结构

{
    "_id": 12345,
    "reports": [
        {
            "body": "\n***\nGeneral report text.",
            "outcome": {
                "comments": [],
                "id": "1",
                "status": {
                    "failed": {
                        "both": 0,
                        "human": 0,
                        "auto": 0,
                        "total": 0
                    },
                    "open": {
                        "both": 0,
                        "human": 0,
                        "auto": 0,
                        "total": 0
                    },
                    "passed": {
                        "both": 0,
                        "human": 0,
                        "auto": 1,
                        "total": 1
                    },
                    "code": {
                        "_input": 0,
                        "_output": 0,
                        "canceled": 0
                    },
                    "total": 1
                }
            },
            "type": "outcome"
        },
        {
            "body": "\n***\nGeneral report text.",
            "outcome": {
                "comments": [],
                "id": "2",
                "status": {
                    "failed": {
                        "both": 0,
                        "human": 0,
                        "auto": 0,
                        "total": 0
                    },
                    "open": {
                        "both": 0,
                        "human": 0,
                        "auto": 0,
                        "total": 0
                    },
                    "passed": {
                        "both": 0,
                        "human": 0,
                        "auto": 1,
                        "total": 1
                    },
                    "code": {
                        "_input": 0,
                        "_output": 0,
                        "canceled": 0
                    },
                    "total": 1
                }
            },
            "type": "outcome"
        }
    ]
}

数据框中期望的格式是

report_id | outcome.id | body | outcome.comments | status.failed.both | status.failed.human | status.failed.auto so on and so forth

我希望把所有状态都放在一条记录中（按outcome.id分组），不太确定如何把它们归一化成单条记录。

我已经尝试了这个，但并未得到期望的数据框结构

df_reports = pd.json_normalize(data,record_path=['reports', 'outcome'],
        meta=[
            ['reports','body'],
            ['outcome','comment'],
            ['outcome','id'],
            ['outcome','status']
        ]

解决方案

使用 record_path=['reports'] 大致能实现你想要的效果。唯一的例外是它没有包含report_id列。你可以再加一行来提供该列。

import pandas as pd
import json

# This is a file containing the JSON from the question.
# If you already have the JSON loaded into the variable 'data'
# you don't need this step
with open('test1165.json', 'rb') as f:
    data = json.load(f)

report_df = pd.json_normalize(data, record_path=['reports'])
report_df.insert(0, 'report_id', data['_id'])

print(report_df.to_string())

打印 report_df 时会得到以下内容：

   report_id                         body     type outcome.comments outcome.id  outcome.status.failed.both  outcome.status.failed.human  outcome.status.failed.auto  outcome.status.failed.total  outcome.status.open.both  outcome.status.open.human  outcome.status.open.auto  outcome.status.open.total  outcome.status.passed.both  outcome.status.passed.human  outcome.status.passed.auto  outcome.status.passed.total  outcome.status.code._input  outcome.status.code._output  outcome.status.code.canceled  outcome.status.total
0      12345  \n***\nGeneral report text.  outcome               []          1                           0                            0                           0                            0                         0                          0                         0                          0                           0                            0                           1                            1                           0                            0                             0                     1
1      12345  \n***\nGeneral report text.  outcome               []          2                           0                            0                           0                            0                         0                          0                         0                          0                           0                            0                           1                            1                           0                            0                             0                     1