pandas学生信息管理示例

import pandas as pd
import matplotlib.pyplot as plt
import os

# 创建初始数据框
data = {
    "学号": [1001, 1002, 1003, 1004, 1005],
    "姓名": ["张三", "李四", "王五", "赵六", "孙七"],
    "性别": ["男", "女", "男", "男", "女"],
    "专业": ["计算机科学", "数据科学", "软件工程", "人工智能", "网络工程"],
    "数学": [90, 85, 88, 92, 80],
    "语文": [85, 88, 90, 87, 86],
    "英语": [88, 92, 85, 90, 91],
}

df = pd.DataFrame(data)
# 计算平均成绩
df["平均成绩"] = df[["数学", "语文", "英语"]].mean(axis=1)
print(df)


def add_student(df, student_id, name, gender, major, math, chinese, english):
    """添加学生信息"""
    # 检查学号是否已存在
    if student_id in df["学号"].values:
        print(f"学号 {student_id} 已存在，无法添加")
        return df

    # 创建新学生记录
    new_student = {
        "学号": student_id,
        "姓名": name,
        "性别": gender,
        "专业": major,
        "数学": math,
        "语文": chinese,
        "英语": english,
        "平均成绩": (math + chinese + english) / 3,
    }

    # 添加到DataFrame
    df = pd.concat([df, pd.DataFrame([new_student])], ignore_index=True)
    print("学生信息添加成功")
    return df


def update_student(df, student_id, field, new_value):
    """修改学生信息"""
    # 检查学号是否存在
    if student_id not in df["学号"].values:
        print(f"学号 {student_id} 不存在，无法修改")
        return df

    # 修改指定字段
    df.loc[df["学号"] == student_id, field] = new_value

    # 如果修改的是成绩，重新计算平均成绩
    if field in ["数学", "语文", "英语"]:
        df.loc[df["学号"] == student_id, "平均成绩"] = df.loc[
            df["学号"] == student_id, ["数学", "语文", "英语"]
        ].mean(axis=1)

    print(f"学生 {student_id} 的 {field} 已更新为 {new_value}")
    return df


def delete_student(df, student_id):
    """删除学生信息"""
    # 检查学号是否存在
    if student_id not in df["学号"].values:
        print(f"学号 {student_id} 不存在，无法删除")
        return df

    # 删除学生记录
    df = df[df["学号"] != student_id]
    print(f"学号为 {student_id} 的学生信息已删除")
    return df.reset_index(drop=True)  # 重置索引


def search_student(df, field, value):
    """查询学生信息"""
    # 根据字段和值查询
    result = df[df[field] == value]

    if result.empty:
        print(f"未找到 {field} 为 {value} 的学生")
        return None

    return result


def show_all_students(df):
    """显示所有学生信息"""
    if df.empty:
        print("没有学生信息")
        return

    print("\n所有学生信息:")
    print(df.to_string(index=False))


def analyze_scores(df):
    """分析学生成绩"""
    if df.empty:
        print("没有学生信息，无法分析")
        return

    # 计算各科统计数据
    stats = df[["数学", "语文", "英语", "平均成绩"]].describe()
    print("\n成绩统计分析:")
    print(stats)

    # 绘制各科平均分柱状图
    plt.figure(figsize=(10, 6))
    subjects = ["数学", "语文", "英语"]
    avg_scores = [df[subject].mean() for subject in subjects]

    plt.bar(subjects, avg_scores, color=["blue", "green", "red"])
    plt.title("各科平均分")
    plt.xlabel("科目")
    plt.ylabel("平均分")
    plt.ylim(0, 100)

    for i, score in enumerate(avg_scores):
        plt.text(i, score + 1, f"{score:.2f}", ha="center")

    plt.show()


def save_to_excel(df, filename="student_info.xlsx"):
    """保存数据到Excel文件"""
    df.to_excel(filename, index=False)
    print(f"数据已保存到 {filename}")


def load_from_excel(filename="student_info.xlsx"):
    """从Excel文件加载数据"""
    if os.path.exists(filename):
        df = pd.read_excel(filename)
        print(f"已从 {filename} 加载数据")
        return df
    else:
        print(f"文件 {filename} 不存在，创建新的数据框")
        return pd.DataFrame(
            columns=pd.Index(
                ["学号", "姓名", "性别", "专业", "数学", "语文", "英语", "平均成绩"]
            )
        )


def main():
    # 尝试加载已有数据，如果不存在则创建新的
    try:
        df = load_from_excel()
    except (FileNotFoundError, pd.errors.EmptyDataError, pd.errors.ParserError):
        # 创建示例数据
        data = {
            "学号": [1001, 1002, 1003, 1004, 1005],
            "姓名": ["张三", "李四", "王五", "赵六", "孙七"],
            "性别": ["男", "女", "男", "男", "女"],
            "专业": ["计算机科学", "数据科学", "软件工程", "人工智能", "网络工程"],
            "数学": [90, 85, 88, 92, 80],
            "语文": [85, 88, 90, 87, 86],
            "英语": [88, 92, 85, 90, 91],
        }
        df = pd.DataFrame(data)
        df["平均成绩"] = df[["数学", "语文", "英语"]].mean(axis=1)

    while True:
        print("\n===== 学生信息管理系统 =====")
        print("1. 添加学生信息")
        print("2. 修改学生信息")
        print("3. 删除学生信息")
        print("4. 查询学生信息")
        print("5. 显示所有学生")
        print("6. 成绩统计分析")
        print("7. 保存数据")
        print("8. 加载数据")
        print("0. 退出系统")

        choice = input("请选择操作: ")

        if choice == "1":
            # 添加学生信息
            student_id = int(input("请输入学号: "))
            name = input("请输入姓名: ")
            gender = input("请输入性别: ")
            major = input("请输入专业: ")
            math = float(input("请输入数学成绩: "))
            chinese = float(input("请输入语文成绩: "))
            english = float(input("请输入英语成绩: "))

            df = add_student(
                df, student_id, name, gender, major, math, chinese, english
            )

        elif choice == "2":
            # 修改学生信息
            student_id = int(input("请输入要修改的学生学号: "))
            print("可修改的字段: 姓名, 性别, 专业, 数学, 语文, 英语")
            field = input("请输入要修改的字段: ")
            new_value = input("请输入新的值: ")

            # 如果是成绩字段，转换为浮点数
            if field in ["数学", "语文", "英语"]:
                new_value = float(new_value)

            df = update_student(df, student_id, field, new_value)

        elif choice == "3":
            # 删除学生信息
            student_id = int(input("请输入要删除的学生学号: "))
            df = delete_student(df, student_id)

        elif choice == "4":
            # 查询学生信息
            print("可查询的字段: 学号, 姓名, 性别, 专业")
            field = input("请输入查询字段: ")
            value = input("请输入查询值: ")

            # 如果查询学号，转换为整数
            if field == "学号":
                value = int(value)

            result = search_student(df, field, value)
            if result is not None:
                print("\n查询结果:")
                print(result.to_string(index=False))

        elif choice == "5":
            # 显示所有学生
            show_all_students(df)

        elif choice == "6":
            # 成绩统计分析
            analyze_scores(df)

        elif choice == "7":
            # 保存数据
            filename = (
                input("请输入保存的文件名(默认为student_info.xlsx): ")
                or "student_info.xlsx"
            )
            save_to_excel(df, filename)

        elif choice == "8":
            # 加载数据
            filename = (
                input("请输入要加载的文件名(默认为student_info.xlsx): ")
                or "student_info.xlsx"
            )
            df = load_from_excel(filename)

        elif choice == "0":
            # 退出系统
            save = input("是否保存数据后退出? (y/n): ")
            if save.lower() == "y":
                save_to_excel(df)
            print("谢谢使用，再见!")
            break

        else:
            print("无效的选择，请重新输入")


if __name__ == "__main__":
    main()