多关联CSV表按规则计算课程/总平均分 转换为指定JSON格式咨询

问题背景与已有数据 #

现有4张结构化数据表 #

1. marks表（考试成绩表）

test_id  student_id  mark
     1           1    78
     2           1    87
     3           1    95
     4           1    32
     5           1    65
     6           1    78
     7           1    40
     1           2    78
     2           2    87
     3           2    15
     6           2    78
     7           2    40
     1           3    78
     2           3    87
     3           3    95
     4           3    32
     5           3    65
     6           3    78
     7           3    40

2. course表（课程表）

id name
   1    A
   2    B
   3    C

3. tests表（考试信息表）

id  course_id  weight
   1          1      10
   2          1      40
   3          1      50
   4          2      40
   5          2      60
   6          3      90
   7          3      10

4. students表（学生信息表）

id name
   1    A
   2    B
   3    C

规则说明 #

weight字段表示本次考试成绩占所属课程最终成绩的百分比，例如weight为50代表该考试成绩占课程最终成绩的50%。

目标输出JSON格式 #

{
  "students": [
    {
      "id": 1,
      "name": "A",
      "totalAverage": 72.03,
      "courses": [
        {
          "id": 1,
          "name": "Biology",
          "teacher": "Mr. D",
          "courseAverage": 90.1
        },
        {
          "id": 3,
          "name": "Math",
          "teacher": "Mrs. C",
          "courseAverage": 74.2
        },
        {
          "id": 2,
          "name": "History",
          "teacher": "Mrs. P",
          "courseAverage": 51.8
        }
      ]
    },
    {
      "id": 2,
      "name": "B",
      "totalAverage": 62.15,
      "courses": [
        {
          "id": 1,
          "name": "Biology",
          "teacher": "Mr. D",
          "courseAverage": 50.1
        },
        {
          "id": 3,
          "name": "Math",
          "teacher": "Mrs. C",
          "courseAverage": 74.2
        }
      ]
    },
    {
      "id": 3,
      "name": "C",
      "totalAverage": 72.03,
      "courses": [
        {
          "id": 1,
          "name": "Biology",
          "teacher": "Mr. D",
          "courseAverage": 90.1
        },
        {
          "id": 2,
          "name": "History",
          "teacher": "Mrs. P",
          "courseAverage": 51.8
        },
        {
          "id": 3,
          "name": "Math",
          "teacher": "Mrs. C",
          "courseAverage": 74.2
        }
      ]
    }
  ]
}

实现思路 #

1. 先理清表关联映射关系 #

• marks表的test_id 关联 tests表的id，获取考试对应课程ID、权重
• tests表的course_id 关联 course表的id，获取课程基础信息
• marks表的student_id 关联 students表的id，获取学生姓名
  注：原始course表未存储课程正式名称、教师信息，和示例的映射关系为：课程ID1→Biology/Mr.D、ID2→History/Mrs.P、ID3→Math/Mrs.C，可提前写固定映射字典匹配

2. 核心指标计算逻辑 #

单学生单课程加权平均（courseAverage）

计算公式：courseAverage = 求和(单次考试成绩 * 对应考试权重 / 100)
举个验证示例：学生1的课程1三次考试加权和为 7810% + 8740% +95*50% = 90.1，和示例完全匹配

学生总平均（totalAverage）

计算公式：totalAverage = 该学生所有课程courseAverage之和 / 参与课程总数
举个验证示例：学生1三个课程平均为90.1、51.8、74.2，总和216.1除以3得72.03，和示例完全匹配

3. 按结构组装JSON #

步骤如下：

• 遍历所有学生，生成每个学生的基础节点（id、name）
• 对每个学生，按课程分组所有考试成绩，计算每个课程的courseAverage，组装课程节点数组
• 用课程平均数组计算总平均，补充到学生节点
• 所有学生节点汇总到外层students数组即可

Python实现代码示例 #

import pandas as pd
import json

# 读取4张表数据
marks = pd.DataFrame([
    [1,1,78],[2,1,87],[3,1,95],[4,1,32],[5,1,65],[6,1,78],[7,1,40],
    [1,2,78],[2,2,87],[3,2,15],[6,2,78],[7,2,40],
    [1,3,78],[2,3,87],[3,3,95],[4,3,32],[5,3,65],[6,3,78],[7,3,40]
], columns=["test_id","student_id","mark"])

course = pd.DataFrame([[1,"A"],[2,"B"],[3,"C"]], columns=["id","name"])
# 课程映射：按示例补充正式名称、教师
course_map = {
    1: {"name":"Biology", "teacher":"Mr. D"},
    2: {"name":"History", "teacher":"Mrs. P"},
    3: {"name":"Math", "teacher":"Mrs. C"}
}

tests = pd.DataFrame([
    [1,1,10],[2,1,40],[3,1,50],[4,2,40],[5,2,60],[6,3,90],[7,3,10]
], columns=["id","course_id","weight"])

students = pd.DataFrame([[1,"A"],[2,"B"],[3,"C"]], columns=["id","name"])

# 关联表获取全量字段
merge_df = marks.merge(tests, left_on="test_id", right_on="id", how="left")
merge_df = merge_df.merge(students, left_on="student_id", right_on="id", how="left", suffixes=("","_stu"))

# 计算单考试加权成绩
merge_df["weighted_mark"] = merge_df["mark"] * merge_df["weight"] / 100

# 按学生+课程分组计算课程平均
course_avg = merge_df.groupby(["student_id","name","course_id"])["weighted_mark"].sum().reset_index()
course_avg.rename(columns={"weighted_mark":"courseAverage", "name":"stu_name"}, inplace=True)

# 组装结果
result = {"students": []}
for stu_id, stu_data in course_avg.groupby("student_id"):
    stu_name = stu_data["stu_name"].iloc[0]
    courses = []
    sum_avg = 0
    for _, row in stu_data.iterrows():
        c_info = course_map[row["course_id"]]
        course_node = {
            "id": int(row["course_id"]),
            "name": c_info["name"],
            "teacher": c_info["teacher"],
            "courseAverage": round(float(row["courseAverage"]),1)
        }
        courses.append(course_node)
        sum_avg += row["courseAverage"]
    # 计算总平均
    total_avg = round(sum_avg/len(courses),2)
    result["students"].append({
        "id": int(stu_id),
        "name": stu_name,
        "totalAverage": total_avg,
        "courses": courses
    })

# 输出JSON
print(json.dumps(result, indent=2, ensure_ascii=False))

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