如何将API返回的嵌套JSON数据赋值到预定义列的Pandas DataFrame中

核心错误原因 #

• 你直接用combined_output_temp_df = output_data_test["customers"][0]["id"]覆盖了预定义的空DataFrame变量，原本的DataFrame结构直接被替换成了单个字符串值，自然没法得到预期的表格结构。
• 你没有对返回的嵌套JSON做结构展平处理：原始数据里有嵌套字典（preferences、mailing_address）、嵌套列表（phone_numbers、addresses），需要先把这些层级的字段展开成和你预定义列名对应的一维结构，才能写入DataFrame。
• 你没有调用DataFrame的写入方法，直接赋值变量只会覆盖原有对象，不会往原有空DataFrame里插入行。

解决步骤 #

1. 编写嵌套结构展平函数 #

处理单条customer数据，把嵌套字段转换成你预定义的列名格式，同时兼容字段缺失的情况：

import pandas as pd

def flatten_customer(customer):
    # 初始化展平后的字典，默认值为None兼容缺失字段
    flat = {}
    # 处理一级字段
    first_level_cols = ["id", "first_name", "last_name", "middle_initial", "email", "username", "created_at", "blocked_payments"]
    for col in first_level_cols:
        flat[col] = customer.get(col, None)
    
    # 处理preferences嵌套字典
    pref = customer.get("preferences", {})
    flat["preference_email_invoices"] = pref.get("email_invoices", None)
    flat["preference_print_invoices"] = pref.get("print_invoices", None)
    flat["preference_exclude_from_insurance_auto_enroll_on"] = pref.get("exclude_from_insurance_auto_enroll_on", None)
    
    # 处理mailing_address嵌套字典
    mail_addr = customer.get("mailing_address", {})
    flat["mailing_address_id"] = mail_addr.get("id", None)
    flat["mailing_address_address1"] = mail_addr.get("address1", None)
    flat["mailing_address_address2"] = mail_addr.get("address2", None)
    flat["mailing_address_city"] = mail_addr.get("city", None)
    flat["mailing_address_state"] = mail_addr.get("state", None)
    flat["mailing_address_latitude"] = mail_addr.get("latitude", None)
    
    # 处理phone_numbers、addresses列表，缺失时默认插入空行占位
    phone_numbers = customer.get("phone_numbers", [{"id": None, "primary": None}])
    addresses = customer.get("addresses", [{"id": None, "address1": None, "address2": None, "city": None, "state": None, "invalid_data": None, "label": None}])
    
    # 生成所有电话+地址组合的行（一个客户多个电话/地址对应多行）
    rows = []
    for phone in phone_numbers:
        for addr in addresses:
            row = flat.copy()
            row["phone_number_id"] = phone.get("id", None)
            row["phone_number_primary"] = phone.get("primary", None)
            row["address_id"] = addr.get("id", None)
            row["address_address1"] = addr.get("address1", None)
            row["address_address2"] = addr.get("address2", None)
            row["address_city"] = addr.get("city", None)
            row["address_state"] = addr.get("state", None)
            row["address_invalid_data"] = addr.get("invalid_data", None)
            row["address_label"] = addr.get("label", None)
            rows.append(row)
    return rows

2. 批量处理所有客户数据生成目标DataFrame #

# 处理所有customer数据
all_rows = []
for customer in output_data_test["customers"]:
    all_rows.extend(flatten_customer(customer))

# 直接生成符合你列定义的DataFrame，不需要提前创建空对象
combined_output_temp_df = pd.DataFrame(all_rows, columns = [
        "id",
        "first_name",
        "last_name",
        "middle_initial",
        "email",
        "preference_email_invoices",
        "preference_print_invoices",
        "preference_exclude_from_insurance_auto_enroll_on",
        "username",
        "created_at",
        "blocked_payments",
        "phone_number_id",
        "phone_number_primary",
        "mailing_address_id",
        "mailing_address_address1",
        "mailing_address_address2",
        "mailing_address_city",
        "mailing_address_state",
        "mailing_address_latitude",
        "address_id",
        "address_address1", 
        "address_address2",
        "address_city",
        "address_state",
        "address_invalid_data",
        "address_label"
])

额外说明 #

你之前用explode报错的原因是explode只能处理单列的列表字段，你同时有phone_numbers和addresses两个列表字段，需要先把两个字段分别处理后再做笛卡尔合并，直接调用explode会因为行列长度不匹配报错。

内容的提问来源于stack exchange，提问作者Chicken Sandwich No Pickles