如何使用Python过滤JSON数据并提取指定字段

原始JSON结构 #

[
    {
        "comments_full": []
    },
    {
        "comments_full": [
            {
                "comment_id": "433934735000014",
                "comment_url": "https://facebook.com/433934735000014",
                "commenter_id": "100002886314120",
                "commenter_url": "https://facebook.com/loubnaharifi?fref=nf&rc=p&refid=52&__tn__=R",
                "commenter_name": "Loubna Harifi",
                "commenter_meta": null,
                "comment_text": "À 18h ça commence",
                "comment_time": 1636502400000,
                "comment_image": null,
                "comment_reactors": [
                    {
                        "name": "Bouygues Telecom",
                        "link": "https://facebook.com/bouyguestelecom/?fref=pb",
                        "type": "like"
                    }
                ],
                "comment_reactions": {
                    "like": 55,
                    "love": 12,
                    "haha": 4,
                    "wow": 1,
                    "sad": 1,
                    "angry": 4
                },
                "comment_reaction_count": 77,
                "replies": [
                    {
                        "comment_id": "433935588333262",
                        "comment_url": "https://facebook.com/433935588333262",
                        "commenter_id": "94533530492",
                        "commenter_url": "https://facebook.com/bouyguestelecom/?rc=p&refid=52&__tn__=%7ERR",
                        "commenter_name": "Bouygues Telecom",
                        "commenter_meta": null,
                        "comment_text": "Oui tout à fait ! RDV à 18h 🙂",
                        "comment_time": 1636502400000,
                        "comment_image": null,
                        "comment_reactors": [
                            {
                                "name": "Maryline Moss",
                                "link": "https://facebook.com/mary.poilue.92?fref=pb",
                                "type": "like"
                            },
                            {
                                "name": "Jess Robic",
                                "link": "https://facebook.com/JessicaRbc91?fref=pb",
                                "type": "like"
                            }
                        ],
                        "comment_reactions": {
                            "like": 55,
                            "love": 12,
                            "haha": 4,
                            "wow": 1,
                            "sad": 1,
                            "angry": 4
                        },
                        "comment_reaction_count": 77
                    }
                    // 省略其余同结构内容
                ]
            }
        ]
    }
]

目标提取字段 #

• comment_id
• commenter_name
• comment_text

原有代码问题 #

原有代码逻辑绕了不必要的弯路：先导出Excel再转CSV再转JSON，且没有对嵌套的comments_full数组做遍历解析，无法提取到嵌套层级里的目标字段。

修正后实现代码 #

方案1：从原始JSON文件直接处理 #

import pandas as pd
import json

# 路径配置，替换为你自己的本地路径
raw_json_path = "C:/Users/stefa/OneDrive/Bureau/Scrap website/Last test/原始数据.json"
output_path = "C:/Users/stefa/OneDrive/Bureau/Scrap website/Last test/提取结果.xlsx"

# 读取原始JSON
with open(raw_json_path, 'r', encoding='utf-8') as f:
    raw_data = json.load(f)

extracted_list = []
target_fields = ["comment_id", "commenter_name", "comment_text"]

for item in raw_data:
    comments_full = item.get("comments_full", [])
    if not comments_full:
        continue
    # 遍历所有主评论
    for comment in comments_full:
        # 提取主评论目标字段
        extracted_list.append({field: comment.get(field) for field in target_fields})
        # 如果需要同时提取评论下的回复，放开下面这段注释
        # for reply in comment.get("replies", []):
        #     extracted_list.append({field: reply.get(field) for field in target_fields})

# 导出结果
df_result = pd.DataFrame(extracted_list)
# 导出为Excel
df_result.to_excel(output_path, index=False)
# 如需导出为CSV，用下面这句
# df_result.to_csv(output_path.replace('.xlsx', '.csv'), index=False, encoding='utf-8-sig')

方案2：直接从已有df_ori处理 #

如果你已经把原始数据加载到了df_ori中，可以直接用下面的代码处理，不需要再读JSON文件：

import pandas as pd

output_path = "C:/Users/stefa/OneDrive/Bureau/Scrap website/Last test/提取结果.xlsx"
extracted_list = []
target_fields = ["comment_id", "commenter_name", "comment_text"]

for _, row in df_ori.iterrows():
    comments_full = row.get("comments_full", [])
    if not comments_full:
        continue
    for comment in comments_full:
        extracted_list.append({field: comment.get(field) for field in target_fields})
        # 需要提取回复同理放开注释
        # for reply in comment.get("replies", []):
        #     extracted_list.append({field: reply.get(field) for field in target_fields})

df_result = pd.DataFrame(extracted_list)
df_result.to_excel(output_path, index=False)

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