使用Pandas Dataframe转换嵌套JSON为扁平文件时遇问题求助

我来帮你解决这个转换问题～首先得先修正你提供的JSON语法错误（原数据里有多余逗号、结构错位的问题），先把嵌套JSON整理成合法格式，比如：

{
  "teamname": "1",
  "team_size": "5",
  "team_status": "low",
  "members": [
    {
      "firstname": "John",
      "lastname": "Doe",
      "orgname": "Anon",
      "phone": "916-555-1234",
      "mobile": ""
    },
    {
      "firstname": "Jane",
      "lastname": "Doe",
      "orgname": "Anon",
      "phone": "916-555-4321",
      "mobile": "916-555-7890"
    }
  ]
}

接下来分两种常见场景给你解决方案：

1. 处理单个嵌套JSON对象 #

如果是单个这样的团队JSON结构，我们可以先把外层的团队信息提取出来，再和成员数组展开后的DataFrame合并：

import pandas as pd
import json

# 读取嵌套JSON（这里用字符串示例，实际可以用pd.read_json读取文件，或者open文件后加载）
nested_json = '''
{
  "teamname": "1",
  "team_size": "5",
  "team_status": "low",
  "members": [
    {
      "firstname": "John",
      "lastname": "Doe",
      "orgname": "Anon",
      "phone": "916-555-1234",
      "mobile": ""
    },
    {
      "firstname": "Jane",
      "lastname": "Doe",
      "orgname": "Anon",
      "phone": "916-555-4321",
      "mobile": "916-555-7890"
    }
  ]
}
'''
data = json.loads(nested_json)

# 把members数组展开成DataFrame
members_df = pd.json_normalize(data['members'])

# 把外层的团队信息添加到每个成员记录里
team_info = {k: data[k] for k in ['teamname', 'team_size', 'team_status']}
members_df = members_df.assign(**team_info)

# 看看最终的扁平数据
print(members_df)

执行后你会得到这样的扁平DataFrame，每个成员都带上了对应的团队信息：

2. 处理多个嵌套JSON对象的数组 #

如果你的JSON是多个团队对象组成的数组（比如多个不同团队的嵌套数据），可以直接用pd.json_normalize()的record_path和meta参数一键展开，超方便：

import pandas as pd
import json

multi_team_json = '''
[
  {
    "teamname": "1",
    "team_size": "5",
    "team_status": "low",
    "members": [
      {"firstname": "John", "lastname": "Doe", "orgname": "Anon", "phone": "916-555-1234", "mobile": ""},
      {"firstname": "Jane", "lastname": "Doe", "orgname": "Anon", "phone": "916-555-4321", "mobile": "916-555-7890"}
    ]
  },
  {
    "teamname": "2",
    "team_size": "3",
    "team_status": "high",
    "members": [
      {"firstname": "Bob", "lastname": "Smith", "orgname": "Anon", "phone": "916-555-0000", "mobile": "916-555-1111"}
    ]
  }
]
'''
data = json.loads(multi_team_json)

# record_path指定要展开的成员数组，meta指定要保留的外层团队字段
flat_df = pd.json_normalize(data, record_path='members', meta=['teamname', 'team_size', 'team_status'])

print(flat_df)

这样就能直接得到所有成员的扁平数据，每个成员都对应自己的团队信息。

非嵌套JSON的处理 #

如果你的非嵌套JSON是每条记录直接包含成员+团队信息（没有members数组，每条就是一个完整的扁平记录），那直接读取就行：

[
  {"firstname": "John", "lastname": "Doe", "orgname": "Anon", "phone": "916-555-1234", "mobile": "", "teamname": "1", "team_size": "5", "team_status": "low"},
  {"firstname": "Jane", "lastname": "Doe", "orgname": "Anon", "phone": "916-555-4321", "mobile": "916-555-7890", "teamname": "1", "team_size": "5", "team_status": "low"}
]

读取代码很简单：

import pandas as pd

# 直接读取JSON字符串或者文件
df = pd.read_json('your_flat_json_file.json')
# 或者如果是字符串的话：df = pd.read_json(flat_json_str)

小提醒 #

• 一定要保证JSON格式合法！多余的逗号、结构错位会直接导致解析失败，你可以用JSON校验工具先检查一下格式。
• 如果嵌套层级更深，比如还有多层嵌套，在pd.json_normalize()里可以用点号或者列表指定路径，比如record_path=['department', 'team', 'members']。

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