89 lines
3.1 KiB
Python
89 lines
3.1 KiB
Python
import pandas as pd
|
|
import json
|
|
from datetime import datetime
|
|
|
|
# 读取Excel文件
|
|
file_path = r'e:\project\python\工具\智慧校园\本部\消费数据\2024学生.xlsx'
|
|
df = pd.read_excel(file_path)
|
|
|
|
print("=" * 80)
|
|
print("学生消费数据提取")
|
|
print("=" * 80)
|
|
|
|
# 1. 提取年级ID和班级ID（供用户查表）
|
|
grade_ids = sorted(df['gradeId'].unique().tolist())
|
|
class_ids = sorted(df['classId'].unique().tolist())
|
|
|
|
print(f"\n【年级ID列表】(共{len(grade_ids)}个):")
|
|
print(grade_ids)
|
|
|
|
print(f"\n【班级ID列表】(共{len(class_ids)}个):")
|
|
print(class_ids)
|
|
|
|
# 2. 提取学生消费信息
|
|
student_records = []
|
|
|
|
for _, row in df.iterrows():
|
|
record = {
|
|
'学号': str(row['stuNo']),
|
|
'姓名': row['stuName'],
|
|
'年级ID': row['gradeId'],
|
|
'班级ID': row['classId'],
|
|
'支付金额(元)': row['totalFee'] / 100, # 转换为元
|
|
'支付状态': '支付成功' if row['payState'] == 1 else '其他',
|
|
'创建时间': row['createTime'].strftime('%Y-%m-%d %H:%M:%S') if pd.notna(row['createTime']) else '',
|
|
'支付时间': row['payTime'].strftime('%Y-%m-%d %H:%M:%S') if pd.notna(row['payTime']) else '',
|
|
'物理卡号': str(row['serialNo']) if pd.notna(row['serialNo']) else '',
|
|
'teamId': row['teamId']
|
|
}
|
|
student_records.append(record)
|
|
|
|
# 3. 统计信息
|
|
print(f"\n【数据统计】")
|
|
print(f"总记录数: {len(student_records)}")
|
|
print(f"唯一学生数: {df['stuNo'].nunique()}")
|
|
print(f"总金额: {df['totalFee'].sum() / 100:.2f} 元")
|
|
|
|
# 4. 按年级统计
|
|
print(f"\n【按年级统计】")
|
|
grade_stats = df.groupby('gradeId').agg({
|
|
'stuNo': 'nunique',
|
|
'totalFee': 'sum'
|
|
}).reset_index()
|
|
grade_stats.columns = ['年级ID', '学生人数', '总金额(分)']
|
|
grade_stats['总金额(元)'] = grade_stats['总金额(分)'] / 100
|
|
print(grade_stats.to_string(index=False))
|
|
|
|
# 5. 按班级统计
|
|
print(f"\n【按班级统计】")
|
|
class_stats = df.groupby('classId').agg({
|
|
'stuNo': 'nunique',
|
|
'totalFee': 'sum'
|
|
}).reset_index()
|
|
class_stats.columns = ['班级ID', '学生人数', '总金额(分)']
|
|
class_stats['总金额(元)'] = class_stats['总金额(分)'] / 100
|
|
print(class_stats.to_string(index=False))
|
|
|
|
# 6. 保存详细记录到JSON
|
|
output_file = r'e:\project\python\工具\智慧校园\本部\消费数据\student_records.json'
|
|
with open(output_file, 'w', encoding='utf-8') as f:
|
|
json.dump(student_records, f, ensure_ascii=False, indent=2)
|
|
|
|
print(f"\n【输出文件】")
|
|
print(f"详细记录已保存到: {output_file}")
|
|
|
|
# 7. 保存ID映射表（等待用户补充年级和班级名称）
|
|
id_mapping = {
|
|
'grade_ids': grade_ids,
|
|
'class_ids': class_ids,
|
|
'note': '请查询数据库补充年级名称和班级名称'
|
|
}
|
|
|
|
id_mapping_file = r'e:\project\python\工具\智慧校园\本部\消费数据\id_mapping.json'
|
|
with open(id_mapping_file, 'w', encoding='utf-8') as f:
|
|
json.dump(id_mapping, f, ensure_ascii=False, indent=2)
|
|
|
|
print(f"ID映射表已保存到: {id_mapping_file}")
|
|
print("\n请查询数据库，告诉我以下ID对应的名称：")
|
|
print(f"年级ID: {grade_ids}")
|
|
print(f"班级ID: {class_ids}") |