# 多表合并汇总（仅标准库版） · 课堂演示件
#
# 用途：机房无法安装 pandas 时，用这一份替代 第11章演示_合并汇总.py。
#       处理逻辑与那一份完全相同，读取的是 原始表格_CSV 文件夹中的 CSV。
# 用法：直接运行，不需要安装任何第三方库。

import csv
from pathlib import Path

# ===== 设置区 =====
FOLDER = '原始表格_CSV'
OUTPUT = '汇总.csv'
GROUP_BY = '班级'
WEIGHT = (0.4, 0.6)
# =================

folder = Path(__file__).with_name(FOLDER)
fields = ['学号', '姓名', '班级', '平时分', '期末分']

# ① 列出全部 CSV 并排序
files = sorted(folder.glob('*.csv'))
print('找到', len(files), '份表格')

# ② 逐份读取并合并，同时记下每条记录的来源
rows = []
for one_file in files:
    with open(one_file, encoding='utf-8-sig', newline='') as fp:
        for record in csv.DictReader(fp):
            record['来源文件'] = one_file.name
            rows.append(record)
print('合并后共', len(rows), '行')

# ③ 清洗与计算
clean = []
missing = 0
for record in rows:
    # 全空行直接跳过
    if not any((record[k] or '').strip() for k in fields):
        continue
    # 去掉文本字段首尾的空格，全角空格同样会被去掉
    record['姓名'] = record['姓名'].strip()
    record['班级'] = record['班级'].strip()
    # 平时分为空的记为 0，并单独计数
    if not (record['平时分'] or '').strip():
        record['平时分'] = '0'
        missing = missing + 1
    record['总评'] = round(float(record['平时分']) * WEIGHT[0]
                          + float(record['期末分']) * WEIGHT[1], 2)
    clean.append(record)

print('去掉全空行后剩', len(clean), '行')
print('平时分为空的记录共', missing, '条，已按 0 计入')

# ④ 分组统计
groups = {}
for record in clean:
    groups.setdefault(record[GROUP_BY], []).append(record['总评'])

print()
print('班级        人数   总评平均分')
for key in sorted(groups):
    scores = groups[key]
    print(key, '  ', len(scores), '  ', round(sum(scores) / len(scores), 2))

# ⑤ 导出为新文件，原始 CSV 一个都不改动
out = Path(__file__).with_name(OUTPUT)
with open(out, 'w', encoding='utf-8-sig', newline='') as fp:
    writer = csv.DictWriter(fp, fieldnames=fields + ['总评', '来源文件'])
    writer.writeheader()
    for record in clean:
        writer.writerow(record)

print()
print('已导出', out.name, '，原始表格未作任何改动')
input('按回车键结束')