如何将多层嵌套JSON转换为指定列结构的pandas DataFrame

你只需要把三层嵌套的API返回结果打平为逐行的字典记录，再传入pandas即可生成目标结构的DataFrame，具体实现代码如下：

import pandas as pd

# 接口返回的原始数据
api_output = {"whitelist":
                        [{"queryWord": "gun",
                        "topn":[{"contextWord": "1", "score": 0.1},
                                {"contextWord": "2", "score": 0.2}]},
                                        
                        {"queryWord": "trench concrete",
                        "topn":[{"contextWord": "1", "score": 0.11},
                                {"contextWord": "5", "score": 0.6}]}],

                "blacklist":
                        [{"queryWord": "tripod",
                        "topn":[{"contextWord": "1", "score": 0.5},
                                {"contextWord": "5", "score": 0.4}]},

                        {"queryWord": "nail",
                        "topn":[{"contextWord": "1", "score": 0.2},
                                {"contextWord": "5", "score": 0.43}]},
                                        
                        {"queryWord": "cover plastic",
                        "topn":[{"contextWord": "1", "score": 0.65},
                                {"contextWord": "5", "score": 0.95}]}]}

# 存储打平后的行记录
data_rows = []

# 遍历第一层：区分黑白名单
for list_category, word_groups in api_output.items():
    # 遍历第二层：拿到每个查询词
    for group in word_groups:
        current_query = group["queryWord"]
        # 遍历第三层：拿到每个上下文词和对应得分
        for match_item in group["topn"]:
            data_rows.append({
                "List": list_category,
                "QueryWord": current_query,
                "ContextWord": match_item["contextWord"],
                "Score": match_item["score"]
            })

# 生成DataFrame，指定列顺序保证和需求一致
df = pd.DataFrame(data_rows, columns=["List", "QueryWord", "ContextWord", "Score"])

运行代码后得到的DataFrame完全匹配要求的字段结构：

• List列：标记记录属于whitelist（白名单）或blacklist（黑名单）
• QueryWord列：存储对应的查询词内容
• ContextWord列：存储匹配到的上下文词编号
• Score列：存储对应匹配的得分值

如果需要做后续的排序、筛选操作，直接对生成的df对象调用pandas对应方法即可。

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