如何将pandas DataFrame存为JSON并将首列账号设为JSON访问键

原有方案问题原因 #

• 导出JSON后用字符串替换索引的逻辑完全不可行：字符串替换会无差别匹配全文本中所有符合条件的内容，无法区分顶级键和字段值中出现的相同字符，必然导致数据错乱。
• to_json默认只能导出扁平结构或固定层级结构，对于需要的多层嵌套（账户信息、交易历史、双卡嵌套信息）支持度很差，单元格中存储的字典会被序列化为字符串，无法生成合法的嵌套JSON结构。
• 账号、卡号、手机号这类超长数字如果不提前转字符串类型，JSON序列化时会出现数字精度丢失，后几位自动变为0。

实现步骤 #

1. 读入数据并预处理索引 #

读入CSV后直接将账号列设为DataFrame索引，从根源上避免后续替换顶级键的操作，同时把所有长数字字段转为字符串规避精度问题。

import pandas as pd
import json
from datetime import datetime

# 读入源CSV文件，替换为你的实际文件路径
df = pd.read_csv("atm_source_data.csv")
# 长数字字段统一转字符串，避免精度丢失
long_num_cols = ["account_no", "uidai", "mobile_no", "pan", 
                 "debit_card_no", "debit_cvv", "credit_card_no", "credit_cvv"]
for col in long_num_cols:
    if col in df.columns:
        df[col] = df[col].astype(str)
# 将账号设为索引，导出时自动成为JSON顶级键
df = df.set_index("account_no")

注意：不要省略长数字转字符串的步骤，否则JSON解析时超过16位的数字会自动丢失精度，出现卡号、账号后几位变为0的问题。

2. 逐行组装嵌套字典结构 #

不要依赖to_json自动生成结构，逐行遍历DataFrame组装Python字典，灵活度最高，可以完全匹配目标JSON层级，同时初始化空的交易历史字段，方便后续ATM业务写入交易记录。

result = {}

for acc_no, row in df.iterrows():
    # 组装基础账户信息，字段名和目标JSON完全对齐
    account_info = {
        "account_no": acc_no,
        "account_type": row.get("account_type", "saving"),
        "branch": row.get("branch", ""),
        "branch_address": row.get("branch_address", ""),
        "ifsc": row.get("ifsc", ""),
        "uidai": row.get("uidai", ""),
        "mobile_no": row.get("mobile_no", ""),
        "email": row.get("email", ""),
        "fname": row.get("fname", ""),
        "mname": row.get("mname", ""),
        "sname": row.get("sname", ""),
        "acc_balance": float(row.get("acc_balance", 0)),
        "dob": row.get("dob", ""),
        "pan": row.get("pan", ""),
        "occupation": row.get("occupation", ""),
        "address": row.get("address", ""),
        "ac_open_date": row.get("ac_open_date", ""),
        "blood_group": row.get("blood_group", ""),
        # 初始化账户交易历史为空字典
        "history": {},
        "cards_allocated": {
            "credit": {},
            "debit": {}
        }
    }

    # 填充借记卡信息（如果CSV中有对应字段）
    if "debit_card_no" in df.columns and pd.notna(row["debit_card_no"]):
        account_info["cards_allocated"]["debit"] = {
            "debit_card_type": row.get("debit_card_type", "visa"),
            "debit_card_no": row["debit_card_no"],
            "name_on_card": f"{row['fname']} {row['mname']} {row['sname']}".lower(),
            "daily_debit_limit": float(row.get("daily_debit_limit", 20000)),
            "card_issue_date": row.get("debit_issue_date", ""),
            "card_exp_date": row.get("debit_exp_date", ""),
            "cvv": row.get("debit_cvv", ""),
            "debit_card_password": row.get("debit_password", ""),
            # 初始化借记卡交易历史
            "history": {}
        }

    # 填充信用卡信息（如果CSV中有对应字段）
    if "credit_card_no" in df.columns and pd.notna(row["credit_card_no"]):
        account_info["cards_allocated"]["credit"] = {
            "credit_card_type": row.get("credit_card_type", "platinum"),
            "credit_card_no": row["credit_card_no"],
            "name_on_card": f"{row['fname']} {row['mname']} {row['sname']}".lower(),
            "card_limit": float(row.get("credit_limit", 20000)),
            "amount_used": float(row.get("amount_used", 0)),
            "amount_to_pay": float(row.get("amount_to_pay", 0)),
            "card_issue_date": row.get("credit_issue_date", ""),
            "card_exp_date": row.get("credit_exp_date", ""),
            "cvv": row.get("credit_cvv", ""),
            "credit_card_password": row.get("credit_password", ""),
            "monthly_emi": float(row.get("monthly_emi", 0)),
            # 初始化信用卡交易历史
            "history": {}
        }

    result[acc_no] = account_info

3. 导出为JSON文件 #

组装完成的字典直接用标准库json模块导出即可，支持缩进格式化，编码正常不会出现乱码。

with open("atm_accounts.json", "w", encoding="utf-8") as f:
    json.dump(result, f, ensure_ascii=False, indent=4)

交易历史写入方法 #

后续开发ATM模拟器时，产生交易直接以dd_mm_yyyy_hh_mm_ss格式的时间戳为键，写入对应层级的history字段即可，不需要修改原始DataFrame：

# 示例：给指定账户加一条存款交易
time_key = datetime.now().strftime("%d_%m_%Y_%H_%M_%S")
result["12312312313"]["history"][time_key] = {
    "transition_id": "生成的全局唯一交易ID",
    "added": 5000,
    "withdrawn": 0
}
# 借记卡、信用卡交易同理，写入cards_allocated对应卡类型下的history字段即可

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