如何用Python3 Pandas按值分组列并生成指定嵌套JSON结构？

我们可以用Python实现这个转换逻辑，核心思路是按层级分组（大学→学院→系），同时为每个层级生成自增ID，最后组装成目标结构。

实现步骤 #

1. 遍历原始数据，按university、faculty、department三级维度分组
2. 为每个层级（大学、学院、系、学者）分配自增ID
3. 按目标格式组装嵌套结构

代码实现 #

import json

def transform_professor_data(raw_data):
    # 临时存储分组数据，避免重复创建层级条目
    university_map = {}
    
    for scholar in raw_data:
        univ_name = scholar["university"]
        faculty_name = scholar["faculty"]
        dept_name = scholar["department"]
        
        # 初始化大学节点
        if univ_name not in university_map:
            university_map[univ_name] = {"faculties": {}}
        
        # 初始化学院节点
        if faculty_name not in university_map[univ_name]["faculties"]:
            university_map[univ_name]["faculties"][faculty_name] = {"departments": {}}
        
        # 初始化系节点
        if dept_name not in university_map[univ_name]["faculties"][faculty_name]["departments"]:
            university_map[univ_name]["faculties"][faculty_name]["departments"][dept_name] = {"academicians": []}
        
        # 添加学者到对应系，自动生成学者ID
        target_dept = university_map[univ_name]["faculties"][faculty_name]["departments"][dept_name]
        target_dept["academicians"].append({
            "id": len(target_dept["academicians"]) + 1,
            "name": scholar["name"],
            "title": scholar["title"]
        })
    
    # 组装最终嵌套结构，生成各层级ID
    result = {"universities": []}
    univ_id = 1
    for univ_name, univ_data in university_map.items():
        faculty_list = []
        faculty_id = 1
        for faculty_name, faculty_data in univ_data["faculties"].items():
            dept_list = []
            dept_id = 1
            for dept_name, dept_data in faculty_data["departments"].items():
                dept_list.append({
                    "id": dept_id,
                    "name": dept_name,
                    "academicians": dept_data["academicians"]
                })
                dept_id += 1
            faculty_list.append({
                "id": faculty_id,
                "name": faculty_name,
                "departments": dept_list
            })
            faculty_id += 1
        result["universities"].append({
            "id": univ_id,
            "name": univ_name,
            "faculties": faculty_list
        })
        univ_id += 1
    
    return result

# 测试示例数据
raw_example = [
    {"name": "XX", "title": "PROFESÖR", "university": "GEBZE TEKNİK ÜNİVERSİTESİ", "faculty": "MÜHENDİSLİK FAKÜLTESİ", "department": "BİLGİSAYAR MÜHENDİSLİĞİ BÖLÜMÜ"},
    {"name": "YY", "title": "PROFESÖR", "university": "GEBZE TEKNİK ÜNİVERSİTESİ", "faculty": "MÜHENDİSLİK FAKÜLTESİ", "department": "ELEKTRONİK MÜHENDİSLİĞİ BÖLÜMÜ"},
    {"name": "AA", "title": "PROFESÖR", "university": "GEBZE TEKNİK ÜNİVERSİTESİ", "faculty": "MÜHENDİSLİK FAKÜLTESİ", "department": "ELEKTRONİK MÜHENDİSLİĞİ BÖLÜMÜ"}
]

transformed_result = transform_professor_data(raw_example)
print(json.dumps(transformed_result, ensure_ascii=False, indent=2))

代码说明 #

• 用字典university_map临时存储分组数据，避免重复创建大学/学院/系条目
• 各层级ID规则：大学ID全局自增，学院ID在所属大学内自增，系ID在所属学院内自增，学者ID在所属系内自增
• ensure_ascii=False确保土耳其语特殊字符正常保留，indent=2格式化输出便于查看

处理4.4万条大规模数据 #

该逻辑时间复杂度为O(n)（n为记录数），可高效处理你的数据集。如果数据存储在JSON文件中，可使用以下方式读取和写入：

# 读取原始数据文件
with open("professors_raw.json", "r", encoding="utf-8") as f:
    raw_data = json.load(f)

# 转换后写入目标文件
transformed_data = transform_professor_data(raw_data)
with open("professors_nested.json", "w", encoding="utf-8") as f:
    json.dump(transformed_data, f, ensure_ascii=False, indent=2)

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