如何将多层嵌套不一致字典展平并转换为指定格式DataFrame

首先得提一下，你原来的JSON字符串里有#end of kit这类注释，json.loads是不支持解析注释的，所以第一步得先把这些注释清理掉，不然会直接报错。接下来我们一步步实现你想要的格式：

步骤1：导入依赖并预处理JSON数据 #

import json
import pandas as pd

# 清理原始JSON中的注释内容
d = """[ { "id": 51, "kits": [ { "id": 57, "kit": "KIT1182A", "items": [ { "id": 254, "product": { "name": "Plastic Pallet", "short_code": "PP001", "priceperunit": 2500, "volumetric_weight": 21.34 }, "quantity": 5 }, { "id": 258, "product": { "name": "Separator Sheet", "short_code": "FSS001", "priceperunit": 170, "volumetric_weight": 0.9 }, "quantity": 18 } ], "quantity": 5 }, { "id": 58, "kit": "KIT1182B", "items": [ { "id": 259, "product": { "name": "Plastic Pallet", "short_code": "PP001", "priceperunit": 2500, "volumetric_weight": 21.34 }, "quantity": 5 }, { "id": 260, "product": { "name": "Plastic Sidewall", "short_code": "PS001", "priceperunit": 1250, "volumetric_weight": 16.1 }, "quantity": 5 }, { "id": 261, "product": { "name": "Plastic Lid", "short_code": "PL001", "priceperunit": 1250, "volumetric_weight": 9.7 }, "quantity": 5 } ], "quantity": 7 } ], "warehouse": "Yantraksh Logistics Private limited_GGNPC1", "receiver_client": "Lumax Cornaglia Auto Tech Private Limited", "transport_by": "Kiran Roadways", "transaction_type": "Return", "transaction_date": "2020-08-13T04:34:11.678000Z", "transaction_no": 1180, "is_delivered": false, "driver_name": "__________", "driver_number": "__________", "lr_number": 0, "vehicle_number": "__________", "freight_charges": 0, "vehicle_type": "Part Load", "remarks": "0", "flow": 36, "owner": 2 } ]"""

# 解析清理后的JSON
data = json.loads(d)

步骤2：编写数据转换逻辑 #

核心思路是把每个kit拆成单独一行，同时保留交易的公共字段，再把kit内的items展开成productN和quantityN的列：

processed_data = []

# 遍历每个交易记录
for transaction in data:
    # 提取需要的公共字段
    common_fields = {
        "transaction_no": transaction["transaction_no"],
        "is_delivered": transaction["is_delivered"],
        "flow": transaction["flow"],
        "transaction_date": transaction["transaction_date"],
        "receiver_client": transaction["receiver_client"],
        "warehouse": transaction["warehouse"]
    }
    
    # 遍历每个kit，生成独立行数据
    for kit in transaction["kits"]:
        row = common_fields.copy()
        # 添加kit自身的信息
        row["kits"] = kit["kit"]
        row["quantity"] = kit["quantity"]
        
        # 按顺序展开kit内的items，生成productN、quantityN字段
        for idx, item in enumerate(kit["items"], start=1):
            row[f"product{idx}"] = item["product"]["short_code"]
            row[f"quantity{idx}"] = item["quantity"]
        
        processed_data.append(row)

# 转换为目标格式的DataFrame
result_df = pd.DataFrame(processed_data)

步骤3：查看结果并导出 #

运行代码后，result_df就会呈现你预期的结构：

print(result_df)

输出示例：

transaction_no  is_delivered  flow               transaction_date                          receiver_client                                      warehouse      kits  quantity product1  quantity1 product2  quantity2 product3  quantity3
0            1180         False    36  2020-08-13T04:34:11.678000Z  Lumax Cornaglia Auto Tech Private Limited  Yantraksh Logistics Private limited_GGNPC1  KIT1182A         5    PP001          5   FSS001         18       NaN        NaN
1            1180         False    36  2020-08-13T04:34:11.678000Z  Lumax Cornaglia Auto Tech Private Limited  Yantraksh Logistics Private limited_GGNPC1  KIT1182B         7    PP001          5    PS001          5    PL001          5

最后导出到CSV文件：

result_df.to_csv("out.csv", index=False)

为什么你的原flatten函数不适用？ #

你之前的flatten函数会把所有嵌套列表（包括kits和items）都展开成同一行的字段（比如kits_1_kit、kits_2_kit），但我们需要的是每个kit作为独立数据行，因此不能直接用全展开的方式，而是需要通过两层循环（交易→kit）来拆分数据。

内容的提问来源于stack exchange，提问作者Yantra Logistics