如何用pandas将扁平化Excel转为含Substance category层级的嵌套JSON

你现有代码只完成了「样本+物质分类」的平级结构输出，要给物质分类增加嵌套层级，只需要在现有分组逻辑基础上，再按样本维度做一次二次分组组装即可，可直接运行的代码如下：

import pandas as pd
import json

# 第一步：沿用你原有逻辑，先完成物质分类和对应物质、数值的映射
category_level = (df.groupby(['Sample','Substance category'])
       .apply(lambda x: x[['Substance','Value']].to_dict('records'))
       .reset_index()
       .rename(columns={0:'SubstanceItems'}))

# 第二步：按Sample维度二次分组，组装三层嵌套结构
final_result = []
for sample_val, sample_group in category_level.groupby('Sample'):
    sample_node = {
        "Sample": sample_val,
        "SubstanceCategories": []
    }
    # 填充当前样本下的所有物质分类节点
    for _, row in sample_group.iterrows():
        sample_node["SubstanceCategories"].append({
            "Substance category": row["Substance category"],
            "Substance": row["SubstanceItems"]
        })
    final_result.append(sample_node)

# 生成格式化的JSON字符串
json_output = json.dumps(final_result, indent=2, ensure_ascii=False)

输出结构说明 #

运行后生成的JSON为三层嵌套结构，样本作为最外层节点，每个样本下挂载所有对应的物质分类，每个分类下再挂载对应的物质和检测值，结构示例：

[
  {
    "Sample": "1A",
    "SubstanceCategories": [
      {
        "Substance category": "Additive",
        "Substance": [
          {"Substance": "Irgafos 168", "Value": 0},
          {"Substance": "Alkylphenylphosphate", "Value": 0}
        ]
      },
      {
        "Substance category": "Alkali",
        "Substance": [
          {"Substance": "Calcium", "Value": 2},
          {"Substance": "Kalium", "Value": 2},
          {"Substance": "Lithium", "Value": 1},
          {"Substance": "Magnesium", "Value": 2},
          {"Substance": "Natrium", "Value": 3}
        ]
      }
    ]
  },
  {
    "Sample": "2A",
    "SubstanceCategories": [
      {
        "Substance category": "Additive",
        "Substance": [
          {"Substance": "Irgafos 168", "Value": 2},
          {"Substance": "Alkylphenylphosphate", "Value": 3}
        ]
      },
      {
        "Substance category": "Alkali",
        "Substance": [
          {"Substance": "Calcium", "Value": 2},
          {"Substance": "Kalium", "Value": 3},
          {"Substance": "Lithium", "Value": 1},
          {"Substance": "Magnesium", "Value": 2},
          {"Substance": "Natrium", "Value": 3}
        ]
      }
    ]
  }
]

调整说明 #

• 可根据业务需要直接修改代码中的键名，比如将SubstanceCategories替换为你需要的字段名称
• 若需要输出压缩后的无缩进JSON，删除json.dumps中的indent=2参数即可
• ensure_ascii=False用于避免中文被转义为Unicode编码，全英文字段场景下可移除该参数

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