140 lines
5.6 KiB
Python
140 lines
5.6 KiB
Python
import pandas as pd
|
|
from datetime import datetime
|
|
|
|
# 读取所有数据
|
|
print("正在读取数据...")
|
|
consumption_df = pd.read_excel(r'e:\project\python\工具\智慧校园\本部\消费数据\2024学生.xlsx')
|
|
grade_df = pd.read_excel(r'e:\project\python\工具\智慧校园\本部\消费数据\年级表.xlsx')
|
|
class_df = pd.read_excel(r'e:\project\python\工具\智慧校园\本部\消费数据\班级表.xlsx')
|
|
|
|
print(f"消费数据: {consumption_df.shape[0]} 条记录")
|
|
print(f"年级表: {grade_df.shape[0]} 个年级")
|
|
print(f"班级表: {class_df.shape[0]} 个班级")
|
|
|
|
# 创建年级ID到名称的映射
|
|
grade_map = dict(zip(grade_df['id'], grade_df['grade_name']))
|
|
|
|
# 创建班级ID到名称的映射
|
|
class_map = dict(zip(class_df['id'], class_df['class_name']))
|
|
|
|
# 创建班级ID到年级ID的映射（用于验证）
|
|
class_to_grade_map = dict(zip(class_df['id'], class_df['grade_id']))
|
|
|
|
print("\n正在处理消费数据...")
|
|
|
|
# 处理消费数据
|
|
processed_data = []
|
|
|
|
for _, row in consumption_df.iterrows():
|
|
grade_id = row['gradeId']
|
|
class_id = row['classId']
|
|
|
|
# 获取年级和班级名称
|
|
grade_name = grade_map.get(grade_id, f'未知年级({grade_id})')
|
|
class_name = class_map.get(class_id, f'未知班级({class_id})')
|
|
|
|
# 处理时间格式
|
|
create_time = row['createTime']
|
|
pay_time = row['payTime']
|
|
|
|
if pd.notna(create_time):
|
|
if isinstance(create_time, str):
|
|
create_time_str = create_time
|
|
else:
|
|
create_time_str = create_time.strftime('%Y-%m-%d %H:%M:%S')
|
|
else:
|
|
create_time_str = ''
|
|
|
|
if pd.notna(pay_time):
|
|
if isinstance(pay_time, str):
|
|
pay_time_str = pay_time
|
|
else:
|
|
pay_time_str = pay_time.strftime('%Y-%m-%d %H:%M:%S')
|
|
else:
|
|
pay_time_str = ''
|
|
|
|
# 处理支付状态
|
|
pay_state_map = {0: '确认中', 1: '支付成功', 2: '已取消', 3: '退款'}
|
|
pay_state = pay_state_map.get(row['payState'], f'未知状态({row["payState"]})')
|
|
|
|
# 处理充值状态
|
|
change_state_map = {0: '充值中', 1: '已取消', 2: '充值成功', 3: '充值失败'}
|
|
change_state = change_state_map.get(row['changeState'], f'未知状态({row["changeState"]})')
|
|
|
|
record = {
|
|
'学号': str(row['stuNo']),
|
|
'姓名': row['stuName'],
|
|
'年级ID': grade_id,
|
|
'年级名称': grade_name,
|
|
'班级ID': class_id,
|
|
'班级名称': class_name,
|
|
'支付金额(元)': row['totalFee'] / 100,
|
|
'支付状态': pay_state,
|
|
'充值状态': change_state,
|
|
'创建时间': create_time_str,
|
|
'支付时间': pay_time_str,
|
|
'物理卡号': str(int(row['serialNo'])) if pd.notna(row['serialNo']) else '',
|
|
'订单编号': row['outTradeNo'],
|
|
'微信订单号': row['transactionId'] if pd.notna(row['transactionId']) else '',
|
|
'支付手机号': str(row['wxPhone']),
|
|
'teamId': row['teamId']
|
|
}
|
|
processed_data.append(record)
|
|
|
|
# 创建DataFrame
|
|
result_df = pd.DataFrame(processed_data)
|
|
|
|
# 生成输出文件名
|
|
output_file = r'e:\project\python\工具\智慧校园\本部\消费数据\学生消费记录整理.xlsx'
|
|
|
|
# 写入Excel，使用多个sheet
|
|
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
|
|
# Sheet 1: 详细记录
|
|
result_df.to_excel(writer, sheet_name='详细记录', index=False)
|
|
|
|
# Sheet 2: 按年级汇总
|
|
grade_summary = result_df.groupby(['年级ID', '年级名称']).agg({
|
|
'学号': 'nunique',
|
|
'支付金额(元)': 'sum',
|
|
'姓名': 'count'
|
|
}).reset_index()
|
|
grade_summary.columns = ['年级ID', '年级名称', '学生人数', '总金额(元)', '消费笔数']
|
|
grade_summary = grade_summary.sort_values('年级ID')
|
|
grade_summary.to_excel(writer, sheet_name='按年级汇总', index=False)
|
|
|
|
# Sheet 3: 按班级汇总
|
|
class_summary = result_df.groupby(['班级ID', '班级名称', '年级名称']).agg({
|
|
'学号': 'nunique',
|
|
'支付金额(元)': 'sum',
|
|
'姓名': 'count'
|
|
}).reset_index()
|
|
class_summary.columns = ['班级ID', '班级名称', '年级名称', '学生人数', '总金额(元)', '消费笔数']
|
|
class_summary = class_summary.sort_values(['年级名称', '班级ID'])
|
|
class_summary.to_excel(writer, sheet_name='按班级汇总', index=False)
|
|
|
|
# Sheet 4: 按学生汇总
|
|
student_summary = result_df.groupby(['学号', '姓名', '年级名称', '班级名称']).agg({
|
|
'支付金额(元)': 'sum',
|
|
'创建时间': 'count'
|
|
}).reset_index()
|
|
student_summary.columns = ['学号', '姓名', '年级名称', '班级名称', '总金额(元)', '消费笔数']
|
|
student_summary = student_summary.sort_values(['年级名称', '班级名称', '学号'])
|
|
student_summary.to_excel(writer, sheet_name='按学生汇总', index=False)
|
|
|
|
print(f"\n✅ 数据整理完成！")
|
|
print(f"📁 输出文件: {output_file}")
|
|
print(f"\n📊 统计信息:")
|
|
print(f" - 总记录数: {len(result_df)}")
|
|
print(f" - 唯一学生数: {result_df['学号'].nunique()}")
|
|
print(f" - 总金额: {result_df['支付金额(元)'].sum():.2f} 元")
|
|
print(f"\n📋 Excel包含以下工作表:")
|
|
print(f" 1. 详细记录 - 所有消费明细")
|
|
print(f" 2. 按年级汇总 - 各年级消费统计")
|
|
print(f" 3. 按班级汇总 - 各班级消费统计")
|
|
print(f" 4. 按学生汇总 - 各学生消费统计")
|
|
|
|
# 显示年级分布
|
|
print(f"\n🏫 年级分布:")
|
|
grade_dist = result_df.groupby('年级名称')['学号'].nunique().sort_values(ascending=False)
|
|
for grade, count in grade_dist.items():
|
|
print(f" {grade}: {count} 人") |