校园学生观看视频时长计算：程序化处理工作流分享

校园学生观看视频时长计算：程序化处理工作流分享

流程图

以时间为划分依据，贯穿整个学年

寒假开学到暑假

数据处理

``

# 功能描述：先删除毕业班的学生（就是删21级的），再根据两年制学生的excel名单删除两年制的毕业班学生
# 用的时候改一下folder_path和graduating_class_df的excel文件地址就行,不过两年制excel要自己简单弄一弄，删除重复值之类的
# 作者：yby
# 版本：1.0
import os
import pandas as pd

def filter_by_graduating_classes(df, graduating_classes):  # 定义函数根据毕业班名单删除数据
    return df[~df['行政班'].isin(graduating_classes)]

def filter_by_regex(df, regex_pattern):  # 定义函数根据正则表达式`21[1-5]`删除数据
    return df[~df['行政班'].str.contains(regex_pattern, na=False)]

folder_path = "D:\桌面文件\数据导出2023年8月26日-2024年5月31日"   # 设定包含所有学生excel文件的文件夹路径，就是将所有的课程excel文件放在同一个文件夹 
graduating_class_df = pd.read_excel('D:\\桌面文件\\两年制.xlsx')  # 毕业班

graduating_class_names = graduating_class_df['行政班'].tolist()  # 假设毕业班人员表中有一个列叫'行政班'，这个是自己创建的excel，添加两年制的名单的

for filename in os.listdir(folder_path):  # 遍历文件夹内的所有Excel文件
    if filename.endswith('.xlsx') or filename.endswith('.xls'):  # 确保文件是Excel文件
        file_path = os.path.join(folder_path, filename)  # 构建完整的文件路径
        try:
            all_students_scores_df = pd.read_excel(file_path)

            non_graduating_class_scores_df = filter_by_graduating_classes(all_students_scores_df, graduating_class_names)  # 根据两年制名单在删除22级的两年制

            final_non_graduating = filter_by_regex(non_graduating_class_scores_df, r'21[1-5]')  # 使用正则表达式进一步在“行政班”那列筛选 211，212.213一直到215，如果有216就自己改一下，就是把21级的全删除，从而达到删除毕业班的效果

            base_filename = os.path.splitext(filename)[0]
            new_filename = f"{base_filename}1.xlsx"  # 构造新文件名，添加后缀'1'
            new_file_path = os.path.join(folder_path, new_filename)

            final_non_graduating.to_excel(new_file_path, index=False)  # 将筛选后的结果保存到新的Excel文件中，保存位置有时候我也很迷，保存位置好像是绝对路径，我也没搞懂，不过问题不大
            print(f"Processed {filename} and saved to {new_filename}")

        except Exception as e:
            print(f"Error processing {filename}: {e}")

数据校对

然后就是再做一下啊校对加个保险，把我做的跟另一个用程序进行比对

``

# 功能描述：用于对比两个表头名一致的excel文件，可以反映出两个excel文件多出的不一样数据是那些，方便找错误
# 改一下file1和file2的文件地址即可
# 作者：yby
# 版本：1.0
import pandas as pd
import numpy as np

def read_excel(file_path):
    """读取Excel文件并返回DataFrame"""
    return pd.read_excel(file_path)

def clean_data(df):
    """清洗和标准化数据"""
    for col in df.columns:
        if df[col].dtype == 'object':
            df[col] = df[col].map(lambda x: x.strip() if isinstance(x, str) else x)
    df.fillna(np.nan, inplace=True)  # 使用 NaN 替换空值
    return df

def compare_dataframes(df1, df2):
    """比较两个DataFrame，返回仅在某一张表中的数据"""
    unique_in_df1 = []
    unique_in_df2 = []

    for col in df1.columns:
        if col in df2.columns:
            unique_in_df1.extend(df1[~df1[col].isin(df2[col])][col].dropna().tolist())
            unique_in_df2.extend(df2[~df2[col].isin(df1[col])][col].dropna().tolist())
        else:
            unique_in_df1.extend(df1[col].dropna().tolist())

    for col in df2.columns:
        if col not in df1.columns:
            unique_in_df2.extend(df2[col].dropna().tolist())

    return unique_in_df1, unique_in_df2

def main(file1, file2):
    df1 = read_excel(file1)
    df2 = read_excel(file2)

    df1 = clean_data(df1)
    df2 = clean_data(df2)

    unique_in_df1, unique_in_df2 = compare_dataframes(df1, df2)

    if not unique_in_df1 and not unique_in_df2:
        print("两个文件的数据完全一致")
    else:
        if unique_in_df1:
            print(f"仅在文件 {file1} 中的数据:")
            for item in unique_in_df1:
                print(item)
        if unique_in_df2:
            print(f"仅在文件 {file2} 中的数据:")
            for item in unique_in_df2:
                print(item)

# 使用方法
file1 = r"D:\桌面文件.xlsx"
file2 = r"D:\桌面文件\文件夹总览\.xlsx"
main(file1, file2)

暑假期间

数据处理

``

# 功能描述：计算非毕业班学生的观看时长并且计算得分
# 需要导入一个含不同课程的文件夹和一个两年制的学生名单，你会得到加分结果和详细的每课程的观看时长
# 作者：yby
import pandas as pd
import os
import glob

# 文件夹路径
folder_path = r"D:\桌面文件\YU的文件"

# 获取所有Excel文件的列表
files = glob.glob(os.path.join(folder_path, '*.xlsx'))
temp_files = glob.glob(os.path.join(folder_path, '~$*.xlsx'))

# 检查是否存在临时文件
if temp_files:
    print("请关闭以下Excel文件以确保所有数据被正确读取：")
    for temp_file in temp_files:
        print(temp_file)
    raise Exception("存在未关闭的Excel文件，请关闭这些文件后重试。")

# 初始化一个空的列表来存储数据框
dfs = []

# 需要保留的列
columns_to_keep = ['学号', '姓名', '行政班', '学院', '观看课程名称', '观看时长']

# 遍历文件列表
for file_path in files:
    try:
        # 读取Excel文件时只选择需要的列
        df = pd.read_excel(file_path, usecols=columns_to_keep)
        dfs.append(df)
        print(f"成功读取文件: {file_path}")
    except PermissionError as e:
        print(f"无法访问文件: {file_path}. 错误: {e}")
    except Exception as e:
        print(f"读取文件时出错: {file_path}. 错误: {e}")

# 检查是否有数据框成功读取
if dfs:
    # 纵向合并所有数据框
    merged_df = pd.concat(dfs, ignore_index=True)

    # 填充缺失值为0（如果有的话）
    merged_df.fillna(0, inplace=True)

    # 计算每个学生的总观看时长
    total_watching_time = merged_df.groupby(['学号', '姓名', '行政班', '学院'], as_index=False)['观看时长'].sum()

    # 重命名列名以反映总观看时长
    total_watching_time.rename(columns={'观看时长': '总观看时长'}, inplace=True)

    # 计算得分，向下取整，并限制最大得分为15
    total_watching_time['得分'] = (total_watching_time['总观看时长'] // 60).clip(upper=15)

    # 保存到新的Excel文件
    total_watching_time.to_excel('总观看时长得分.xlsx', index=False)

    # 如果还需要保留课程名称的具体记录，可以保存一个详细的合并后的文件
    merged_df.to_excel('详细观看时长.xlsx', index=False)
else:
    print("没有读取到任何Excel文件。")