如何用Pandas将含不完整行的CSV转换为嵌套JSON

架构老林

2026-5-28

我明白你现在的需求——把分散的CSV/Excel数据按subject_id聚合，把同一主体下的访问、样本信息嵌套成结构化的JSON，还要处理那些NaN的无关列对吧？我之前也处理过类似的多表整合嵌套场景，给你一套可行的方案，咱们一步步来：

先对齐数据示例（基于你提到的实际场景）

假设你的实际CSV数据长这样（包含重复的访问信息、多样本、NaN无关列）：

subject_id,visit_id,visit_date,sample_id,sample_type,test_result,subject_age,visit_note
1,VIS001,2023-01-05,SMP001,blood,7.2,35,NaN
1,VIS001,2023-01-05,SMP002,urine,6.8,35,NaN
1,VIS002,2023-02-10,SMP003,blood,7.0,35,"Follow up"
2,VIS003,2023-01-15,SMP004,blood,6.9,28,NaN

你期望的嵌套JSON输出大概是这样：

[
  {
    "subject_id": 1,
    "subject_age": 35,
    "visits": [
      {
        "visit_id": "VIS001",
        "visit_date": "2023-01-05",
        "samples": [
          {"sample_id": "SMP001", "sample_type": "blood", "test_result": 7.2},
          {"sample_id": "SMP002", "sample_type": "urine", "test_result": 6.8}
        ]
      },
      {
        "visit_id": "VIS002",
        "visit_date": "2023-02-10",
        "visit_note": "Follow up",
        "samples": [
          {"sample_id": "SMP003", "sample_type": "blood", "test_result": 7.0}
        ]
      }
    ]
  },
  {
    "subject_id": 2,
    "subject_age": 28,
    "visits": [
      {
        "visit_id": "VIS003",
        "visit_date": "2023-01-15",
        "samples": [
          {"sample_id": "SMP004", "sample_type": "blood", "test_result": 6.9}
        ]
      }
    ]
  }
]

解决方案代码实现

步骤1：读取并合并多文件，清洗数据

如果是多个CSV/Excel，先把它们全部合并成一个DataFrame，再处理NaN：

import pandas as pd
import json

# 读取多个文件（示例：所有csv/xlsx文件）
file_list = ["data1.csv", "data2.csv", "data3.xlsx"]
dfs = []
for file in file_list:
    if file.endswith(".csv"):
        dfs.append(pd.read_csv(file))
    elif file.endswith(".xlsx"):
        dfs.append(pd.read_excel(file))
# 合并成一个大表
df = pd.concat(dfs, ignore_index=True)

# 清洗：把全为NaN的列直接删掉（避免后续嵌套冗余）
df = df.dropna(axis=1, how="all")
# 把剩余的NaN转成Python的None，方便JSON序列化
df = df.where(pd.notnull(df), None)

步骤2：按层级分组构建嵌套结构

核心是先按subject_id分组，再在每个分组里按visit_id（或访问唯一标识）分组，把样本信息嵌套到访问里：

def build_sample_list(visit_group):
    # 提取样本专属列，过滤掉主体/访问级别的字段
    sample_cols = ["sample_id", "sample_type", "test_result"]
    # 只保留有有效数据的样本行（如果有的话）
    return visit_group[sample_cols].dropna(axis=1, how="all").to_dict("records")

def build_visit_info(visit_group):
    # 提取访问级别的信息（去重，因为同一visit的行重复这些字段）
    visit_cols = ["visit_id", "visit_date", "visit_note"]
    visit_info = visit_group[visit_cols].drop_duplicates().to_dict("records")[0]
    # 嵌套样本列表
    visit_info["samples"] = build_sample_list(visit_group)
    return visit_info

def build_subject_info(subject_group):
    # 提取主体级别的信息
    subject_info = {
        "subject_id": subject_group["subject_id"].iloc[0],
        "subject_age": subject_group["subject_age"].iloc[0]
    }
    # 按visit_id分组，构建该主体的所有访问列表
    subject_info["visits"] = subject_group.groupby("visit_id").apply(build_visit_info).tolist()
    return subject_info

# 生成最终的嵌套数据结构
nested_data = df.groupby("subject_id").apply(build_subject_info).tolist()

步骤3：输出为格式化的JSON

最后把嵌套数据转成JSON，注意处理日期类型（如果你的visit_date是datetime格式）：

# 自定义序列化函数，处理Pandas的Timestamp类型
def serialize_custom(obj):
    if isinstance(obj, pd.Timestamp):
        return obj.strftime("%Y-%m-%d")
    raise TypeError(f"Type {type(obj)} not serializable")

# 写入JSON文件
with open("subject_database.json", "w", encoding="utf-8") as f:
    json.dump(nested_data, f, default=serialize_custom, indent=2, ensure_ascii=False)

一些适配你场景的小技巧

如果你的访问唯一标识不是单一的visit_id，而是visit_id+visit_date组合，可以把groupby("visit_id")改成groupby(["visit_id", "visit_date"])，然后在build_visit_info里调整访问信息的提取逻辑。
如果有更多层级的嵌套（比如样本下还有检测项），可以在build_sample_list里再加一层分组逻辑。
如果有些列的归属不确定，可以先打印df.columns，手动划分主体、访问、样本三级的字段列表，避免嵌套错误。

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