如何将含可选空字段的动态JSON响应存入Pandas DataFrame？

我有一个动态JSON响应，其中bccRecipients、replyTo、ccRecipients等多数字段有时为空数组，有时包含有效值。示例JSON如下：

{
      "hasAttachments": false,
      "sender": {
        "emailAddress": {
          "name": "John Henry",
          "address": "john@abc.com"
        }
      },
      "from": {
        "emailAddress": {
          "name": "Mike Tyson",
          "address": "mike@xyz.com"
        }
      },
      "toRecipients": [
        {
          "emailAddress": {
            "name": "Himan",
            "address": "himan@pqrst.com"
          }
        }
      ],
      "ccRecipients": [],
      "bccRecipients": [],
      "replyTo": [],
      "flag": {
        "flagStatus": "notFlagged"
      }
}

目前我已创建了指定列名的空DataFrame：

import pandas as pd
email_metadata = pd.DataFrame(columns=["Subject","SenderEmailAddress","SenderName","FromEmailAddress","FromName","ToRecipients","HasAttachments","ccRecipients","bccRecipients"])

需求：若ccRecipients这类数组字段为空，则存入Null/NaN；若包含多个值，则保留所有值。例如：

• 多值场景：ccRecipients对应的emailAddress.name为["Mike", "John"]
• 空数据场景：ccRecipients字段存入NaN

请问如何实现将该动态JSON响应存入上述DataFrame？

可以写一个处理单条JSON数据的函数，逐个解析字段，针对数组类型的字段做空值判断和多值提取，最后将处理后的数据追加到DataFrame中。

步骤1：定义解析函数 #

def parse_email_json(email_json):
    # 初始化空字典存储解析后的数据
    parsed_data = {}
    
    # 处理单值字段，用get方法兼容可能缺失的字段
    parsed_data["Subject"] = email_json.get("subject", pd.NA)
    parsed_data["SenderEmailAddress"] = email_json["sender"]["emailAddress"]["address"]
    parsed_data["SenderName"] = email_json["sender"]["emailAddress"]["name"]
    parsed_data["FromEmailAddress"] = email_json["from"]["emailAddress"]["address"]
    parsed_data["FromName"] = email_json["from"]["emailAddress"]["name"]
    parsed_data["HasAttachments"] = email_json["hasAttachments"]
    
    # 封装数组字段处理逻辑
    def extract_recipients(recipient_list):
        if not recipient_list:  # 判断数组是否为空
            return pd.NA
        # 提取所有收件人名称（如需邮箱可调整为f"{name} <{address}>"格式）
        return [item['emailAddress']['name'] for item in recipient_list]
    
    parsed_data["ToRecipients"] = extract_recipients(email_json["toRecipients"])
    parsed_data["ccRecipients"] = extract_recipients(email_json["ccRecipients"])
    parsed_data["bccRecipients"] = extract_recipients(email_json["bccRecipients"])
    
    return parsed_data

步骤2：解析JSON并追加到DataFrame #

# 示例JSON数据
sample_json = {
      "hasAttachments": False,
      "sender": {
        "emailAddress": {
          "name": "John Henry",
          "address": "john@abc.com"
        }
      },
      "from": {
        "emailAddress": {
          "name": "Mike Tyson",
          "address": "mike@xyz.com"
        }
      },
      "toRecipients": [
        {
          "emailAddress": {
            "name": "Himan",
            "address": "himan@pqrst.com"
          }
        }
      ],
      "ccRecipients": [],
      "bccRecipients": [],
      "replyTo": [],
      "flag": {
        "flagStatus": "notFlagged"
      }
}

# 解析单条数据并追加到DataFrame
parsed_email = parse_email_json(sample_json)
email_metadata = pd.concat([email_metadata, pd.DataFrame([parsed_email])], ignore_index=True)

补充说明 #

• 数组字段通过extract_recipients函数统一处理：空数组返回pd.NA（Pandas标准空值），非空则提取所有收件人信息，格式可按需调整。
• 单值字段用get方法处理可能缺失的字段（比如Subject），避免抛出KeyError。
• 若需批量处理多条JSON，只需循环调用parse_email_json并依次追加即可。

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