如何使用Python Pandas将数据库表格数据转换为指定嵌套JSON格式

你需要按照嵌套层级逐层分组聚合数据，无法直接通过df.to_json()的内置参数实现，具体实现步骤如下：

步骤1：构造示例DataFrame #

import pandas as pd
import json

# 导入你的源数据，这里直接用你给出的示例构造
data = [
    [100, "Sam", "Street 5", "NY", "NY001", "E", 2019],
    [100, "Sam", "Street 5", "NY", "NY001", "T", 2020],
    [100, "Sam", "Street 10", "NJ", "NJ001", "E", 2019],
    [100, "Sam", "Street 10", "NJ", "NJ001", "T", 2020],
    [101, "John", "Street 1", "CA", "CA001", "E", 2019],
    [101, "John", "Street 1", "CA", "CA001", "T", 2020]
]
df = pd.DataFrame(data, columns=["Account", "Name", "Address1", "State", "Zip", "Loantype", "expiry"])
# 修正源数据里John的拼写错误
df["Name"] = df["Name"].replace("Joh", "John")

步骤2：逐层生成嵌套结构 #

results = []
# 第一层分组：按账号、姓名聚合
for (account, name), user_group in df.groupby(["Account", "Name"]):
    user_info = {
        "account": account,
        "Name": name,
        "LoanDetails": []
    }
    # 第二层分组：按地址、州、邮编聚合
    for (addr, state, zip_code), addr_group in user_group.groupby(["Address1", "State", "Zip"]):
        addr_info = {
            "Address1": addr,
            "State": state,
            "ZIP": zip_code,
            # 第三层：贷款明细直接转字典列表
            "LoanList": addr_group[["Loantype", "expiry"]].to_dict("records")
        }
        user_info["LoanDetails"].append(addr_info)
    results.append(user_info)

# 封装成最终的JSON结构
final_data = {"results": results}

# 打印验证
print(json.dumps(final_data, indent=2, ensure_ascii=False))
# 保存到本地文件的可选代码
# with open("loan_result.json", "w", encoding="utf-8") as f:
#     json.dump(final_data, f, indent=2, ensure_ascii=False)

输出结果和你要求的结构完全匹配，你示例目标JSON里的账号写错、邮编多了0、贷款年份错误等均为笔误，代码会按源数据的真实值生成。

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