REST API嵌套JSON转Pandas DataFrame：如何深度展开多层嵌套？
2026-7-10
问题描述
通过REST API获取到嵌套JSON数据，需要将其展开为扁平结构，但目前仅能完成第一层展开，无法处理edata中topping列的深层嵌套。当前操作步骤为：将数据转为Pandas DataFrame，展开bdata、edata节点，但topping列的嵌套内容无法展开。
示例JSON数据
{ "adata": { "1": { "xid": "012", "xtype": "donut", "xname": "xCake", "xppu": 0.55 }, "2": { "xid": "015", "xtype": "donut", "xname": "Cake", "xppu": 0.565 }, "3": { "xid": "018", "xtype": "donut", "xname": "Cakex", "xppu": 0.559 } }, "bdata": { "1": [ { "yid": "00012", "ytype": "donut", "yname": "Cake", "yppu": 0.55 }, { "yid": "00023", "ytype": "donut", "yname": "Raised", "yppu": 0.554 }, { "yid": "00024", "ytype": "donut", "yname": "Raised", "yppu": 0.554 } ], "2": [ { "yid": "00015", "ytype": "donut", "yname": "Cake", "yppu": 0.565 }, { "yid": "00026", "ytype": "donut", "yname": "Raised", "yppu": 0.557 }, { "yid": "00027", "ytype": "donut", "yname": "Raised", "yppu": 0.525 } ], "3": [ { "yid": "00018", "ytype": "donut", "yname": "Cake", "yppu": 0.559 }, { "yid": "00039", "ytype": "donut", "yname": "Old Fashioned", "yppu": 0.558 } ] }, "edata": { "1": [ { "eid": "03001", "etype": "donut", "name": "Cake", "ppu": 0.55, "topping": [ {"id": "51", "type": "None"}, {"id": "002", "type": "zGlazed"}, {"id": "05", "type": "Sugar"}, {"id": "5007", "type": "Powdered Sugar"}, {"id": "06", "type": "Chocolate with Sprinkles"}, {"id": "53", "type": "Chocolate"}, {"id": "04", "type": "Maple"} ] }, { "eid": "0302", "etype": "donut", "name": "Raised", "ppu": 0.55 }, { "eid": "0302", "etype": "donut", "name": "Raisedz", "ppu": 0.55, "topping": "None" }, { "eid": "03003", "etype": "donut", "name": "zOld Fashioned", "ppu": 0.55, "topping": [ {"id": "501", "type": "Nonex"}, {"id": "52", "type": "xGlazed"}, {"id": "503", "type": "Chocolatez"} ] } ], "2": [ { "eid": "00401", "etype": "donut", "name": "Cake", "ppu": 0.55, "topping": [ {"id": "01", "type": "None"}, {"id": "2", "type": "xGlazed"}, {"id": "55", "type": "xSugar"}, {"id": "507", "type": "Powdered Sugar"}, {"id": "506", "type": "xChocolate with Sprinkles"}, {"id": "03", "type": "xChocolate"}, {"id": "54", "type": "xMaple"} ] }, { "eid": "042", "etype": "donut", "name": "Raised", "ppu": 0.55 }, { "eid": "042", "etype": "donut", "name": "Raisedx", "ppu": 0.55, "topping": "None" } ], "3": [ { "eid": "051", "etype": "donut", "name": "Cake", "ppu": 0.55, "topping": [ {"id": "50407", "type": "Powdered Sugarx"}, {"id": "50406", "type": "Chocolate with Sprinklesx"}, {"id": "50403", "type": "Chocolatex"}, {"id": "50404", "type": "Maplex"} ] }, { "eid": "050403", "etype": "donut", "name": "Old Fashioned", "ppu": 0.55, "topping": [ {"id": "5071", "type": "None"}, {"id": "5072", "type": "Glazedx"}, {"id": "5703", "type": "Chocolatex"} ] } ] } }
期望的扁平结构示例
eid etype name ppu id type xid xtype xname xppu yid ytype yname yppu 0 03001 donut Cake 0.55 51 None 012 donut xCake 0.55 00012 donut Cake 0.55 1 03001 donut Cake 0.55 002 zGlazed 012 donut xCake 0.55 00012 donut Cake 0.55 2 03001 donut Cake 0.55 05 Sugar 012 donut xCake 0.55 00012 donut Cake 0.55 3 03001 donut Cake 0.55 5007 Powdered Sugar 012 donut xCake 0.55 00012 donut Cake 0.55 4 03001 donut Cake 0.55 06 Chocolate with Sprinkles 012 donut xCake 0.55 00012 donut Cake 0.55 5 03001 donut Cake 0.55 53 Chocolate 012 donut xCake 0.55 00012 donut Cake 0.55 6 03001 donut Cake 0.55 04 Maple 012 donut xCake 0.55 00012 donut Cake 0.55 7 0302 donut Raised 0.55 NaN None 015 donut Cake 0.565 00015 donut Cake 0.565 8 0302 donut Raisedz 0.55 None None 015 donut Cake 0.565 00015 donut Cake 0.565
解决方案
通过以下步骤实现全层级扁平展开，重点处理topping列的多种嵌套/非嵌套情况：
步骤1：导入依赖并加载数据
import pandas as pd import json # 从文件加载JSON数据，或直接使用API响应结果 with open('data.json', 'r') as f: data = json.load(f)
步骤2：展开顶层节点并保留关联主键
将adata、bdata、edata分别转换为DataFrame，保留共同主键（"1"、"2"、"3"）用于后续关联：
# 处理adata：转换为DataFrame并保留主键key df_adata = pd.DataFrame.from_dict(data['adata'], orient='index').reset_index().rename(columns={'index': 'key'}) # 处理bdata：展开列表并保留主键key df_bdata = pd.DataFrame([ {'key': k, **item} for k, v in data['bdata'].items() for item in v ]) # 处理edata：展开列表并保留主键key df_edata = pd.DataFrame([ {'key': k, **item} for k, v in data['edata'].items() for item in v ])
步骤3：统一处理topping列并展开
topping列存在列表、字符串"None"、缺失值三种情况，先归一化为列表格式再展开：
def normalize_topping(topping): if pd.isna(topping): return [{'id': None, 'type': 'None'}] elif isinstance(topping, str): return [{'id': topping, 'type': 'None'}] elif isinstance(topping, list): return topping else: return [{'id': None, 'type': 'None'}] # 应用归一化函数 df_edata['topping'] = df_edata['topping'].apply(normalize_topping) # 展开topping列表 df_edata_expanded = df_edata.explode('topping', ignore_index=True) # 将topping的字典内容拆分为单独列 df_edata_expanded = pd.concat([ df_edata_expanded.drop('topping', axis=1), df_edata_expanded['topping'].apply(pd.Series) ], axis=1)
步骤4：关联所有DataFrame得到最终结果
通过key列关联三个DataFrame，调整列顺序匹配期望结构：
# 关联adata和bdata df_merged = pd.merge(df_adata, df_bdata, on='key', how='inner') # 关联edata展开后的结果 final_df = pd.merge(df_merged, df_edata_expanded, on='key', how='inner') # 移除key列并调整列顺序 final_df = final_df.drop('key', axis=1)[[ 'eid', 'etype', 'name', 'ppu', 'id', 'type', 'xid', 'xtype', 'xname', 'xppu', 'yid', 'ytype', 'yname', 'yppu' ]] # 查看最终结果 print(final_df.head(10))
运行上述代码后，将得到与期望示例一致的全扁平结构数据。
内容的提问来源于stack exchange，提问作者Pavan Aithal
相关产品推荐
相关产品推荐
