如何将JSON响应转换为Pandas DataFrame并提取嵌套的性别、邮箱字段
补全后代码实现
针对contactIndividuals数组嵌套的场景,以下实现默认提取所有联系人的性别和家庭邮箱,多个值用分号拼接,可按需调整提取逻辑。
import pandas as pd df = pd.DataFrame( columns=[ "contactcustomerid", "contactType", "contactName", "contactaddress", "contactcity", "contactstate", "contactzip", "contactcountry", "giftAskAmount", "giftAskType", "lifetodateGiving", "yeartoDateGiving", "lastGiftAmount", "lastGiftDate", "contactGender", "contactEmail", ] ) if json_data: # 基础字段提取,同时兼容address为空的报错场景 row = { "contactcustomerid": json_data.get("id"), "contactType": json_data.get("contactType"), "contactName": json_data.get("name"), "contactaddress": json_data.get("address", {}).get("address1"), "contactcity": json_data.get("address", {}).get("city"), "contactstate": json_data.get("address", {}).get("state"), "contactzip": json_data.get("address", {}).get("postal"), "contactcountry": json_data.get("address", {}).get("country"), "giftAskAmount": json_data.get("giftAskAmount"), "giftAskType": json_data.get("giftAskType"), "lifetodateGiving": json_data.get("lifeToDateGiving"), "yeartoDateGiving": json_data.get("yearToDateGiving"), "lastGiftAmount": json_data.get("lastGiftAmount"), "lastGiftDate": json_data.get("lastGiftDate"), } # 提取嵌套的性别和邮箱字段 genders = [] emails = [] contact_individuals = json_data.get("contactIndividuals", []) for individual in contact_individuals: # 提取性别 gender_val = individual.get("gender") if gender_val: genders.append(gender_val) # 提取类型为Home Email的联系邮箱 contact_methods = individual.get("contactMethods", []) for method in contact_methods: if method.get("type") == "Home Email": email_val = method.get("value") if email_val: emails.append(email_val) # 赋值到行数据,多个值用分号分隔 row["contactGender"] = ";".join(genders) if genders else None row["contactEmail"] = ";".join(emails) if emails else None # 兼容新老版本pandas的行追加写法(旧版append方法已废弃) df = pd.concat([df, pd.DataFrame([row])], ignore_index=True) df = df.fillna(0)
可选调整逻辑
- 若仅需提取主联系人信息:循环时添加
if individual.get("isPrimary"):判断即可 - 若仅需保留第一个值:将
";".join(genders)替换为genders[0] if genders else None即可
内容的提问来源于stack exchange,提问作者Ujval Gandhi
相关产品推荐
相关产品推荐

