如何将嵌套JSON转为Pandas DataFrame，实现col3唯一标识行且列按层级命名

要实现将嵌套JSON转换为以col3唯一标识每行、列名按层级命名的Pandas DataFrame，可通过自定义扁平化函数处理嵌套结构，避免列表展开导致的行重复，同时严格生成层级列名。

步骤1：解析JSON数据 #

首先确保JSON格式合法（将原JSON中的单引号替换为双引号，或用ast.literal_eval解析），提取核心的data数组。

步骤2：自定义扁平化函数 #

该函数递归处理嵌套字典，对列表元素添加索引后缀（而非展开成多行），确保每行对应唯一的col3，同时生成层级化列名（如data_col4_col5_0）。

import pandas as pd
import ast

def flatten_json(nested_json, prefix=''):
    out = {}
    for key, value in nested_json.items():
        current_prefix = f"{prefix}_{key}" if prefix else key
        if isinstance(value, dict):
            # 递归处理嵌套字典
            out.update(flatten_json(value, current_prefix))
        elif isinstance(value, list):
            # 对列表元素添加索引后缀，避免展开多行
            for idx, item in enumerate(value):
                if isinstance(item, dict):
                    out.update(flatten_json(item, f"{current_prefix}_{idx}"))
                else:
                    out[f"{current_prefix}_{idx}"] = item
        else:
            out[current_prefix] = value
    return out

# 解析JSON（原JSON需修正为双引号格式，或用ast.literal_eval处理单引号）
json_data = ast.literal_eval('''{
   "total_numbers": 2,
   "data":[
      {
         "col3":"2",
         "col4":[
            {
               "col5":"P",
               "col6":"H"
            },
            {
               "col5":"P1",
               "col6":"H1"
            }
         ],
         "col7":"2023-06-19T09:29:28.786Z",
         "col9":{
            "col10":"TEST",
            "col11":"test@email.com",
            "col12":"True",
            "col13":"999",
            "col14":"9999"
         },
         "col15":"2023-07-10T04:46:43.003Z",
         "col16":false,
         "col17":[
            {
               "col18":"S",
               "col19":"H"
            }
         ],
         "col20":true,
         "col21":{
            "col22":"sss",
            "col23":"0.0.0.0",
            "col24":"lll"
         },
         "col25":0,
         "col26":{
            "col27":{
               "col28":"Other"
            },
            "col29":"Other",
            "col30":"cccc"
         },
         "col31":{
            "col32":[
               {
                  "col33":"123456789",
                  "col34":"2023-07-14T02:52:20.166Z",
                  "col36":true,
                  "col38":{
                     "col40":[
                        {
                           "col41":"99999999999"
                        },
                        {
                           "col41":"34534543535"
                        }
                     ]
                  },
                  "col55":"878787878"
               },
               {
                  "col47":"112233445566",
                  "col48":"2023-07-24T09:26:03.425Z",
                  "col50":true,
                  "col52":{
                     "col53":[
                        {
                           "col54":"99999999999"
                        }
                     ]
                  },
                  "col55":"878787878"
               }
            ]
         }
      },
      {
         "col3":"3",
         "col4":[
            {
               "col5":"P",
               "col6":"H"
            }
         ],
         "col7":"2023-06-19T09:29:28.786Z",
         "col9":{
            "col10":"TEST",
            "col11":"test@email.com",
            "col12":"True",
            "col13":"999",
            "col14":"9999"
         },
         "col15":"2023-07-10T04:46:43.003Z",
         "col16":false,
         "col17":[
            {
               "col18":"S",
               "col19":"H"
            }
         ],
         "col20":true,
         "col21":{
            "col22":"sss",
            "col23":"0.0.0.0",
            "col24":"lll"
         },
         "col25":0,
         "col26":{
            "col27":{
               "col28":"Other"
            },
            "col29":"Other",
            "col30":"cccc"
         },
         "col31":{
            "col32":[
               {
                  "col33":"123456789",
                  "col34":"2023-07-14T02:52:20.166Z",
                  "col36":true,
                  "col38":{
                     "col40":[
                        {
                           "col41":"99999999999"
                        },
                        {
                           "col41":"34534543535"
                        }
                     ]
                  },
                  "col55":"878787878"
               },
               {
                  "col47":"112233445566",
                  "col48":"2023-07-24T09:26:03.425Z",
                  "col50":true,
                  "col52":{
                     "col53":[
                        {
                           "col54":"99999999999"
                        }
                     ]
                  },
                  "col55":"878787878"
               }
            ]
         }
      }
   ]
}''')

# 提取data数组并扁平化每个元素
flattened_items = [flatten_json(item, prefix='data') for item in json_data['data']]

# 转换为DataFrame
df = pd.DataFrame(flattened_items)

# 验证col3唯一性
assert df['data_col3'].is_unique

关键说明 #

• 列命名规则：所有列名以data为前缀，层级间用下划线连接，列表元素添加索引后缀（如data_col4_col5_0表示data→col4列表中第0个元素的col5字段）。
• 行唯一性保障：通过给列表元素添加索引后缀，避免将列表展开为多行，确保每个col3对应唯一行。
• 灵活调整：若需将列表元素拼接为字符串（而非生成多列），可修改扁平化函数中列表处理逻辑，例如用逗号拼接所有元素：

  elif isinstance(value, list):
      if all(isinstance(item, (str, int, float, bool)) for item in value):
          out[current_prefix] = ', '.join(map(str, value))
      else:
          # 嵌套字典仍按索引后缀处理
          for idx, item in enumerate(value):
              if isinstance(item, dict):
                  out.update(flatten_json(item, f"{current_prefix}_{idx}"))
  

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