使用Pandas处理嵌套JSON时遇'int' object is not subscriptable错误求助

场景与数据样例 #

使用Pandas处理嵌套JSON数据，其中ico_list字段可能为空数组，也可能包含多个子对象：

空ico_list的JSON样例：

[{"export_id":"COL-EXP-1894","origin_office":"EXAMPLE","destination_office":"","incoterms":"","shipment_date":"","export_date":"2023-01-01","origin_port":"Buenaventura","destination_port":"New York/New Jersey","bl_number":null,"shipping_line":null,"shipping_mode":null,"vessel_name":null,"voyage_number":null,"reservation_number":null,"container_number":null,"seal_number":null,"eta":null,"etd":null,"export_status":"in_progress","ico_list":[]}]

包含子对象的ico_list样例：

[{"export_id":"COL-EXP-1894","origin_office":"EXAMPLE","destination_office":"","incoterms":"","shipment_date":"","export_date":"2023-01-01","origin_port":"Buenaventura","destination_port":"New York/New Jersey","bl_number":null,"shipping_line":null,"shipping_mode":null,"vessel_name":null,"voyage_number":null,"reservation_number":null,"container_number":null,"seal_number":null,"eta":null,"etd":null,"export_status":"in_progress","ico_list":[{"ico_id":"03-0178-436-23","contract_id":"CI-1046","customer":null,"origin_office":"example","destination_office":"example","incoterm":"CIF","quality":"ML","mark":"example","packaging_type":"Nitrogen-Flushed Vac-Packed Boxes - 35KG","packaging_capacity":35.0,"units":1,"quantity":35.0,"certification":null}]}]

问题代码 #

编写的提取数据代码如下：

if response.status_code == 200:
        data_str = response.text
        try:
            atlas_api_data = json.loads(data_str)
            df_atlas = pd.json_normalize(atlas_api_data)
            #print(df_atlas)
        except:
            print('ErrorOccured While Parsing JSON ATLAS API TO Dataframe')
        df_atlas2 = pd.json_normalize(df_atlas['ico_list'].loc[95])

    for i, row in df_atlas.iterrows():
        export_id = row['export_id']
        origin_office = row['origin_office']
        destination_office = row['destination_office']
        export_date = row['export_date']
        origin_port = row['origin_port']
        destination_port = row['destination_port']
        bl_number = row['bl_number']
        shipping_line = row['shipping_line']
        shipping_mode = row['shipping_mode']
        vessel_name = row['vessel_name']
        voyage_number = row['voyage_number']
        reservation_number = row['reservation_number']
        container_number = row['container_number']
        seal_number = row['seal_number']
        export_status = row['export_status']
        values = [export_id,origin_office,destination_office,export_date,origin_port,destination_port,
            bl_number,shipping_line,shipping_mode,vessel_name,voyage_number,reservation_number,container_number,
            seal_number,export_status]
        data_list.append(values)
        df_atlas2 = pd.json_normalize(df_atlas['ico_list'].loc[i])
        if df_atlas2.empty:
            print('Empty DF')
        else:
            for row_ico, j in df_atlas2.iterrows():
                ico_id = row_ico['ico_id']
                contract_id = row_ico['contract_id']
                customer = row_ico['customer']
                incoterm = row_ico['incoterm']
                quality = row_ico['quality']
                mark = row_ico['mark']
                packaging_type = row_ico['packaging_type']
                packaging_capacity = row_ico['packaging_capacity']
                units = row_ico['units']
                quantity = row_ico['quantity']
                certification = row_ico['certification']
                ico_values = [export_id,ico_id,contract_id,customer,incoterm,quality,mark,packaging_type,packaging_capacity,units,quantity,certification]
                data_ico_list.append(ico_values)

报错信息 #

遍历第二层df_atlas2时出现如下错误：

TypeError                                 Traceback (most recent call last)
Cell In [4], line 43
     41 else:
     42     for row_ico, j in df_atlas2.iterrows():
---> 43         ico_id = row_ico['ico_id']
     44         contract_id = row_ico['contract_id']
     45         customer = row_ico['customer']

TypeError: 'int' object is not subscriptable

解决方法 #

核心错误修正 #

iterrows()方法返回的是**(行索引, 行数据Series)**的元组，你搞反了变量顺序：把索引赋值给了row_ico，把行数据赋值给了j，导致用int类型的索引去取字段，触发报错。

修正第二层循环的变量顺序即可：

else:
    # 交换变量顺序，idx是索引，row_ico是行数据
    for idx, row_ico in df_atlas2.iterrows():
        ico_id = row_ico['ico_id']
        contract_id = row_ico['contract_id']
        customer = row_ico['customer']
        incoterm = row_ico['incoterm']
        quality = row_ico['quality']
        mark = row_ico['mark']
        packaging_type = row_ico['packaging_type']
        packaging_capacity = row_ico['packaging_capacity']
        units = row_ico['units']
        quantity = row_ico['quantity']
        certification = row_ico['certification']
        ico_values = [export_id,ico_id,contract_id,customer,incoterm,quality,mark,packaging_type,packaging_capacity,units,quantity,certification]
        data_ico_list.append(ico_values)

代码优化建议 #

手动循环提取字段效率低且易出错，推荐直接使用pd.json_normalize的record_path和meta参数一次性展开嵌套数据，无需手动遍历：

提取主数据

df_main = pd.json_normalize(atlas_api_data)
# 保留需要的主字段
main_columns = [
    'export_id', 'origin_office', 'destination_office', 'export_date',
    'origin_port', 'destination_port', 'bl_number', 'shipping_line',
    'shipping_mode', 'vessel_name', 'voyage_number', 'reservation_number',
    'container_number', 'seal_number', 'export_status'
]
df_main = df_main[main_columns]
# 转为列表（如果需要的话）
data_list = df_main.values.tolist()

提取ico子数据

df_ico = pd.json_normalize(
    atlas_api_data,
    record_path='ico_list',  # 指定嵌套数组的路径
    meta=['export_id']       # 携带主数据中的export_id关联字段
)
# 保留需要的子字段
ico_columns = [
    'export_id', 'ico_id', 'contract_id', 'customer', 'incoterm',
    'quality', 'mark', 'packaging_type', 'packaging_capacity',
    'units', 'quantity', 'certification'
]
df_ico = df_ico[ico_columns]
# 转为列表（如果需要的话）
data_ico_list = df_ico.values.tolist()

这种方式不仅代码更简洁，处理大规模数据时效率也更高。

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