如何将嵌套JSON文件完整转换为Pandas DataFrame？

问题描述 #

尝试用Python将嵌套JSON转换为DataFrame时，所有方案都会丢失部分数据，使用json_normalize仅能部分展开JSON结构。

部分JSON输入内容

[
    {
        "repository": "https://github.com/apache/commons-cli.git",
        "sha1": "34209ca517db46da273c2ee0ca1d8f532b599cbd",
        "url": "https://github.com/apache/commons-cli/commit/34209ca517db46da273c2ee0ca1d8f532b599cbd",
        "refactorings": []
    },
    {
        "repository": "https://github.com/apache/commons-cli.git",
        "sha1": "809bd30902215afdc80d8c911f5051e3e8a2da65",
        "url": "https://github.com/apache/commons-cli/commit/809bd30902215afdc80d8c911f5051e3e8a2da65",
        "refactorings": []
    },
    {
        "repository": "https://github.com/apache/commons-cli.git",
        "sha1": "4cca25d72b216bfc8f2e75e4a99afb608ceb6df8",
        "url": "https://github.com/apache/commons-cli/commit/4cca25d72b216bfc8f2e75e4a99afb608ceb6df8",
        "refactorings": [
            {
                "type": "Inline Variable",
                "description": "Inline Variable chr : Character in method package setOpt(opt Option) : void from class org.apache.commons.cli.CommandLine",
                "leftSideLocations": [
                    {
                        "filePath": "src/java/org/apache/commons/cli/CommandLine.java",
                        "startLine": 221,
                        "endLine": 221,
                        "startColumn": 19,
                        "endColumn": 54,
                        "codeElementType": "VARIABLE_DECLARATION_STATEMENT",
                        "description": "inlined variable declaration",
                        "codeElement": "chr : Character"
                    },
                    {
                        "filePath": "src/java/org/apache/commons/cli/CommandLine.java",
                        "startLine": 222,
                        "endLine": 222,
                        "startColumn": 9,
                        "endColumn": 45,
                        "codeElementType": "EXPRESSION_STATEMENT",
                        "description": "statement with the name of the inlined variable",
                        "codeElement": null
                    },
                    {
                        "filePath": "src/java/org/apache/commons/cli/CommandLine.java",
                        "startLine": 214,
                        "endLine": 224,
                        "startColumn": 5,
                        "endColumn": 6,
                        "codeElementType": "METHOD_DECLARATION",
                        "description": "original method declaration",
                        "codeElement": "package setOpt(opt Option) : void"
                    }
                ],
                "rightSideLocations": [
                    {
                        "filePath": "src/java/org/apache/commons/cli/CommandLine.java",
                        "startLine": 223,
                        "endLine": 223,
                        "startColumn": 9,
                        "endColumn": 54,
                        "codeElementType": "EXPRESSION_STATEMENT",
                        "description": "statement with the initializer of the inlined variable",
                        "codeElement": null
                    },
                    {
                        "filePath": "src/java/org/apache/commons/cli/CommandLine.java",
                        "startLine": 216,
                        "endLine": 225,
                        "startColumn": 5,
                        "endColumn": 6,
                        "codeElementType": "METHOD_DECLARATION",
                        "description": "method declaration with inlined variable",
                        "codeElement": "package setOpt(opt Option) : void"
                    }
                ]
            }
        ]
    }
]

现有代码片段

import json

import pandas as pd
from pandas.io.json import json_normalize  # package for flattening json in pandas df

# load json object
with open("output/common_cli.json") as f:
    d = json.load(f)

metadata = ["refactorings"]
nycphil = json_normalize(data=d["commits"], meta=metadata, errors="ignore")
x = nycphil.head(3)
df = pd.DataFrame(x)
print(df)
df.to_csv("output/test4.csv")

解决方法 #

现有代码存在两个核心问题：

1. 加载的JSON是数组结构，没有commits键，直接调用d["commits"]会抛出KeyError；
2. 默认的json_normalize只会展开一层嵌套，无法处理refactorings内部的leftSideLocations、rightSideLocations这类深层数组。

以下是修正后的代码，能完整展开所有嵌套数据：

import json
import pandas as pd
from pandas.io.json import json_normalize

# 加载JSON数据
with open("output/common_cli.json") as f:
    data = json.load(f)

# 第一步：展开refactorings数组，关联顶层的repository、sha1、url
df_refactorings = json_normalize(
    data,
    record_path="refactorings",
    meta=["repository", "sha1", "url"],
    errors="ignore"
)

# 第二步：展开leftSideLocations数组，关联已展开的refactorings字段
df_left = json_normalize(
    df_refactorings.to_dict("records"),
    record_path="leftSideLocations",
    meta=["repository", "sha1", "url", "type", "description"],
    errors="ignore",
    record_prefix="left_"
)

# 第三步：展开rightSideLocations数组，关联已展开的refactorings字段
df_right = json_normalize(
    df_refactorings.to_dict("records"),
    record_path="rightSideLocations",
    meta=["repository", "sha1", "url", "type", "description"],
    errors="ignore",
    record_prefix="right_"
)

# 合并左右位置数据（若需保留独立关联关系，也可选择分别保存）
final_df = pd.merge(df_left, df_right, on=["repository", "sha1", "url", "type", "description"], how="outer")

# 输出结果
print(final_df.head())
final_df.to_csv("output/full_data.csv", index=False)

代码说明

• 分三步逐层展开嵌套数组：先展开refactorings，再分别展开其内部的leftSideLocations和rightSideLocations；
• 使用record_prefix给深层字段添加前缀，避免左右位置字段重名；
• 通过merge将左右位置数据关联，保留所有原始信息；
• 对于没有refactorings的条目，会自动填充NaN，不会丢失数据。

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