如何将嵌套JSON数据转换为符合要求的pd.DataFrame格式

首先修正你提供的示例JSON的语法错误（原示例存在缺逗号、数值用逗号做小数点的非法格式问题），再按以下代码处理即可得到目标格式的DataFrame：

import pandas as pd
import json

# 修正后的示例JSON，实际使用时可替换为从文件读取的逻辑
json_file = {
  "x1": [
    {
      "a": "XZ12ABC1834",
      "b": "J. Doe",
      "c": [
        {
          "Amount": -50,
          "Date": "2021-08-15T10:00:00.000Z",
          "CategoryId": "abc123",
          "CounterParty": "The Farm",
          "Description": "some description",
          "Counter": "XYZ456AZ",
          "Type": "bc"
        },{
          "Amount": -1,
          "Date": "2020-08-15T10:00:00.000Z",
          "CategoryId": "cde123",
          "CounterParty": "The pool",
          "Description": "some other description",
          "Counter": "WYZ12",
          "Type": "X"
        }
      ]
    },
    {
      "a": "XX34XX872",
      "b": "J. Doe",
      "c": [
        {
          "Amount": -1.50,
          "Date": "2019-05-15T10:00:00.000Z",
          "CategoryId": "QWR627",
          "CounterParty": "The City",
          "Description": "last other description",
          "Counter": "QWE123",
          "Type": "S"
        }
      ]
    }
  ]
}

# 如果你是从本地JSON文件读取，替换上面的json_file定义即可：
# with open("你的JSON文件路径.json", "r", encoding="utf-8") as f:
#     json_file = json.load(f)

# 处理逻辑
row_list = []
# 遍历顶层键作为var1字段
for var1, entry_list in json_file.items():
    for entry in entry_list:
        a_val = entry["a"]
        b_val = entry["b"]
        # 展开嵌套的c数组，每个元素对应DataFrame一行
        for c_detail in entry["c"]:
            row_list.append({
                "var1": var1,
                "a": a_val,
                "b": b_val,
                "amount": c_detail["Amount"],
                "date": c_detail["Date"],
                "CategoryID": c_detail["CategoryId"],
                "Counterparty": c_detail["CounterParty"],
                "Description": c_detail["Description"],
                "Counter": c_detail["Counter"],
                "Type": c_detail["Type"]
            })

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

# 输出验证
print(df)

注意事项 #

• 若实际使用中存在字段缺失的情况，可以将c_detail["字段名"]改为c_detail.get("字段名", "")，避免KeyError报错
• 输出的DataFrame列顺序和你要求的完全一致，可直接用于后续建模

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