如何自动识别嵌套JSON并explode展开为DataFrame列
2026-8-30
问题描述
需要将XML解析得到的多层嵌套、包含动态数量列表结构的JSON数据，完全展开为扁平结构的Pandas DataFrame。由于不同输入数据中列表的位置、数量不固定，无法提前硬编码需要执行explode操作的列名，需实现自动识别列表列、递归展开的通用逻辑。
示例数据结构
测试用JSON数据如下：
{ "Research": { "@xmlns": "http://www.xml.org/2013/2/XML", "@language": "eng", "@createDateTime": "2022-03-25T10:12:39Z", "@researchID": "abcd", "Product": { "@productID": "abcd", "StatusInfo": { "@currentStatusIndicator": "Yes", "@statusDateTime": "2022-03-25T12:18:41Z", "@statusType": "Published" }, "Source": { "Organization": { "@primaryIndicator": "Yes", "@type": "SellSideFirm", "OrganizationID": [ { "@idType": "L1", "#text": "D827C98E315F" }, { "@idType": "TR", "#text": "3202" }, { "@idType": "TR", "#text": "SZA" } ], "OrganizationName": { "@nameType": "Legal", "#text": "Citi" }, "PersonGroup": { "PersonGroupMember": { "@primaryIndicator": "Yes", "@sequence": "1", "Person": { "@personID": "tr56", "FamilyName": "Wang", "GivenName": "Bond", "DisplayName": "Bond Wang", "Biography": "Bond Wang is a", "BiographyFormatted": "Bond Wang", "PhotoResourceIdRef": "AS44556" } } } } }, "Content": { "Title": "Premier", "Abstract": "None", "Synopsis": "Premier’s solid 1H22 result .", "Resource": [ { "@language": "eng", "@primaryIndicator": "Yes", "@resourceID": "9553", "Length": { "@lengthUnit": "Pages", "#text": "17" }, "MIMEType": "text/html", "URL": "https://www.DFKJG.com/rendition/eppublic" }, { "@language": "eng", "@primaryIndicator": "No", "@resourceID": "4809", "Length": { "@lengthUnit": "Pages", "#text": "17" }, "MIMEType": "ABS/pdf", "Name": "asdf.pdf", "Comments": "fr5.pdf" }, { "@language": "eng", "@primaryIndicator": "No", "@resourceID": "6d13a965723e", "Length": { "@lengthUnit": "Pages", "#text": "17" }, "MIMEType": "text/html", "URL": "https://www.dfgdfg.com/" }, { "@primaryIndicator": "No", "@resourceID": "709c7bdb1c99", "MIMEType": "tyy/image", "URL": "https://ir.ght.com" }, { "@primaryIndicator": "No", "@resourceID": "gfjhgj", "MIMEType": "gtty/image", "URL": "https://ir.gtty.com" } ] }, "Context": { "@external": "Yes", "IssuerDetails": { "Issuer": { "@issuerType": "Corporate", "@primaryIndicator": "Yes", "SecurityDetails": { "Security": { "@estimateAction": "Revision", "@primaryIndicator": "Yes", "@targetPriceAction": "Increase", "SecurityID": [ { "@idType": "RIC", "@idValue": "PMV.AX", "@publisherDefinedValue": "RIC" }, { "@idType": "Bloomberg", "@idValue": "PMV@AU" }, { "@idType": "SEDOL", "@idValue": "6699781" } ], "SecurityName": "Premier Investments Ltd", "AssetClass": { "@assetClass": "Equity" }, "AssetType": { "@assetType": "Stock" }, "SecurityType": { "@securityType": "Common" }, "Rating": { "@rating": "NeutralSentiment", "@ratingType": "Rating", "@aspect": "Investment", "@ratingDateTime": "2020-07-31T08:24:37Z", "RatingEntity": { "@ratingEntity": "PublisherDefined", "PublisherDefinedValue": "Citi" } } } }, "IssuerID": { "@idType": "PublisherDefined", "@idValue": "PMV.AX", "@publisherDefinedValue": "TICKER" }, "IssuerName": { "@nameType": "Legal", "NameValue": "Premier Investments Ltd" } } }, "ProductDetails": { "@periodicalIndicator": "No", "@publicationDateTime": "2022-03-25T12:18:41Z", "ProductCategory": { "@productCategory": "Report" }, "ProductFocus": { "@focus": "Issuer", "@primaryIndicator": "Yes" }, "EntitlementGroup": { "Entitlement": [ { "@includeExcludeIndicator": "Include", "@primaryIndicator": "No", "AudienceTypeEntitlement": { "@audienceType": "PublisherDefined", "@entitlementContext": "TR", "#text": "20012" } }, { "@includeExcludeIndicator": "Include", "@primaryIndicator": "No", "AudienceTypeEntitlement": { "@audienceType": "PublisherDefined", "@entitlementContext": "TR", "#text": "2001" } } ] } }, "ProductClassifications": { "Discipline": { "@disciplineType": "Investment", "@researchApproach": "Fundamental" }, "Subject": { "@publisherDefinedValue": "TREPS", "@subjectValue": "PublisherDefined" }, "Country": { "@code": "AU", "@primaryIndicator": "Yes" }, "Region": { "@primaryIndicator": "Yes", "@emergingIndicator": "No", "@regionType": "Australasia" }, "AssetClass": { "@assetClass": "Equity" }, "AssetType": { "@assetType": "Stock" }, "SectorIndustry": [ { "@classificationType": "GICS", "@code": "25201040", "@focusLevel": "Yes", "@level": "4", "@primaryIndicator": "Yes", "Name": "Household Appliances" }, { "@classificationType": "GICS", "@code": "25504020", "@focusLevel": "Yes", "@level": "4", "@primaryIndicator": "Yes", "Name": "Computer & Electronics Retail" }, { "@classificationType": "GICS", "@code": "25504040", "@focusLevel": "Yes", "@level": "4", "@primaryIndicator": "Yes", "Name": "Specialty Stores" }, { "@classificationType": "GICS", "@code": "25504030", "@focusLevel": "Yes", "@level": "4", "@primaryIndicator": "Yes", "Name": "Home Improvement Retail" }, { "@classificationType": "GICS", "@code": "25201050", "@focusLevel": "Yes", "@level": "4", "@primaryIndicator": "Yes", "Name": "Housewares & Specialties" } ] } } } } }
现有实现问题
当前代码硬编码待展开列名，无法适配动态变化的输入结构，代码如下：
import xmltodict as xmltodict from pprint import pprint import pandas as pd import json from tabulate import tabulate dict =(xmltodict.parse("""xml data""")) json_str = json.dumps(dict) resp = json.loads(json_str) print(resp) df = pd.json_normalize(resp) cols=['Research.Product.Source.Organization.OrganizationID','Research.Product.Content.Resource','Research.Product.Context.IssuerDetails.Issuer.SecurityDetails.Security.SecurityID','Research.Product.Context.ProductDetails.EntitlementGroup.Entitlement','Research.Product.Context.ProductClassifications.SectorIndustry'] def expplode_columns(df, cols): df_e = df.copy() for c in cols: df_e = df_e.explode(c, ignore_index=True) return df_e df2 = expplode_columns(df, cols) print(tabulate(df2, headers="keys", tablefmt="psql")) # df2.to_csv('dataframe.csv', header=True, index=False)
解决方案
核心逻辑为循环自动识别DataFrame中值为列表类型的列，逐列执行explode，直到所有列都不存在列表值为止；explode完成后对嵌套字典类型的列再次做归一化展开，保证最终输出完全扁平的表结构。
完整实现代码：
import xmltodict import pandas as pd import json from tabulate import tabulate def auto_flatten_json_to_df(json_data): # 初始归一化 df = pd.json_normalize(json_data) while True: # 识别所有包含列表值的列（跳过空值） list_cols = [] for col in df.columns: has_list = any(isinstance(val, list) for val in df[col].dropna()) if has_list: list_cols.append(col) # 无待展开列表列时退出循环 if not list_cols: break # 逐列执行explode for col in list_cols: df = df.explode(col, ignore_index=True) # 重新归一化，展开explode后产生的嵌套字典列 df = pd.json_normalize(df.to_dict(orient='records')) return df # 调用示例（替换为实际XML读取逻辑即可） # with open("your_data.xml", "r", encoding="utf-8") as f: # raw_dict = xmltodict.parse(f.read()) # 测试用读取本地JSON with open("test.json", "r", encoding="utf-8") as f: raw_dict = json.load(f) result_df = auto_flatten_json_to_df(raw_dict) print(tabulate(result_df, headers="keys", tablefmt="psql")) # result_df.to_csv('flatten_result.csv', header=True, index=False)
逻辑说明
- 自动识别列：每轮循环遍历所有列，只要列中存在非空列表值就标记为待展开列，无需提前硬编码列名
- 循环展开：explode操作后会产生新的嵌套字典/列表结构，循环会持续运行直到不存在任何列表类型的列
- 二次归一化：每次explode完成后重新调用
json_normalize，把拆出的字典结构自动拆为独立列，保证结果完全扁平 - 动态适配：无论输入数据有多少层嵌套、多少个列表字段，都可以自动完成展开，不需要提前预知数据结构
内容的提问来源于stack exchange，提问作者Atharv Thakur
相关产品推荐
相关产品推荐
