# 多表合并汇总 · 课堂演示件
#
# 用法：
#   ① 先运行 第11章演示_生成测试数据.py，得到 原始表格 文件夹
#   ② 安装第三方库：pip install pandas openpyxl
#   ③ 运行本文件。原始表格一个都不会被改动，结果写入 汇总.xlsx

from pathlib import Path

import pandas as pd

# ===== 设置区：只需要修改以下四项 =====
FOLDER = '原始表格'        # 存放原始表的文件夹，与本文件位于同一位置
OUTPUT = '汇总.xlsx'       # 导出的文件名
GROUP_BY = '班级'          # 按哪一个字段分组统计
WEIGHT = (0.4, 0.6)        # 总评权重：平时分占 40%，期末分占 60%
# ====================================

folder = Path(__file__).with_name(FOLDER)

# ① 列出全部表格文件并排序，使每次合并的顺序一致
files = sorted(folder.glob('*.xlsx'))
print('找到', len(files), '份表格')

# ② 逐份读取并合并。额外记下每条记录来自哪份表，供抽查时溯源
tables = []
for one_file in files:
    one = pd.read_excel(one_file)
    one['来源文件'] = one_file.name
    print('  读取', one_file.name, '共', len(one), '行')
    tables.append(one)

data = pd.concat(tables, ignore_index=True)
print('合并后共', len(data), '行')

# ③ 清洗一：去掉整行都为空的记录
data = data.dropna(subset=['学号', '姓名', '班级', '平时分', '期末分'], how='all')
print('去掉全空行后剩', len(data), '行')

# ④ 清洗二：去掉文本字段首尾的空格，全角空格同样会被去掉
for col in ['姓名', '班级']:
    data[col] = data[col].str.strip()

# ⑤ 缺失值：平时分为空的记为 0，并单独报告条数
missing = int(data['平时分'].isna().sum())
data['平时分'] = data['平时分'].fillna(0)
print('平时分为空的记录共', missing, '条，已按 0 计入')

# ⑥ 计算总评
data['总评'] = data['平时分'] * WEIGHT[0] + data['期末分'] * WEIGHT[1]

# ⑦ 分组统计：按班级计算人数与总评平均分
summary = data.groupby(GROUP_BY).agg(
    人数=('学号', 'count'),
    总评平均分=('总评', 'mean'),
).round(2).reset_index()
print()
print(summary.to_string(index=False))

# ⑧ 抽查：随机取三条记录，打印来源与各项原始数值，供人工回原表核对
print()
print('抽查三条（请回到原表逐项核对）：')
for _, row in data.sample(3).iterrows():
    print('  ', row['来源文件'], row['学号'], row['姓名'],
          '平时', row['平时分'], '期末', row['期末分'], '总评', round(row['总评'], 2))

# ⑨ 导出：写入新文件，原始表格一个都不改动
out = Path(__file__).with_name(OUTPUT)
with pd.ExcelWriter(out) as writer:
    data.to_excel(writer, sheet_name='合并明细', index=False)
    summary.to_excel(writer, sheet_name='按班级汇总', index=False)
print()
print('已导出', out.name, '，原始表格未作任何改动')

input('按回车键结束')