校园学生观看视频时长计算:程序化处理工作流分享
流程图
以时间为划分依据,贯穿整个学年
寒假开学到暑假
数据处理
``
# 功能描述:先删除毕业班的学生(就是删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文件。")