数智教育学生画像和行为分析

作者: sunny_shen | 来源:发表于2020-09-21 16:47 被阅读0次

本文主要记录了整个数智教育项目从背景理解到最终可视化报表的实现过程。完成内容包括利用Python进行数据源的清洗处理，然后通过pymysql工具包将数据导入Mysql，再将tableau连接至数据库，并根据具体分析要点编写sql查询语句获取数据库中的相关表数据，最终实现可视化分析报表。

可视化分析报表结果：链接
节选仪表板：

目录：
一、项目背景
二、项目目标
三、数据探索
四、数据清洗
五、数据分析

一、项目背景

本项目来自阿里天池举办的“数智教育”数据可视化创新大赛，比赛旨在对学校教育数据进行数据分析和可视化，探索面向学生、老师、校园等的数据分析体系，设计并形成数据分析门户，从而服务精细化教学管理工作。

赛题要求完成教育数据的清洗处理工作和数据分析的指标体系开发，指标开发范围需包括但不仅限于以下三方面：
（1）学生域数据分析，具体包括学生基本信息分析、学生成绩分析、学生消费分析、学生考勤分析；
（2）课程域数据分析，具体包括课程相关数据分析、学生7选3选课分析；
（3）群体域数据分析，具体包括班级域数据分析、班级内部学生成绩分析。

大赛官网：https://tianchi.aliyun.com/competition/entrance/231704/introduction

二、项目目标

通过对赛题的梳理，可以明确两点，第一，学校教育数据分析需要分层，即面向不同的受众，应呈现不同详细级别的可视化报表，比如，校长或校领导关注的是整个校园层面的信息，而不会细微到具体每个学生；第二，学校教育数据主要来源于两个方面，一方面是学生、老师、课程的基本信息，另一方面是学生行为信息包括成绩、消费、考勤等。

因此，明确此次项目目标：分别在校园层面、年级层面、班级层面共三个层面针对学生/老师/课程的基本信息和学生行为信息进行探索分析，并结合教育领域的实际应用场景比如改善学生成绩、服务教务设计、优化学生服务等等提出合理建议，以提升教学管理工作的效率和质量。

进一步地，对项目目标做如下拆解：
校园层面：学生信息、教学资源、宿舍信息、消费信息、考勤情况；
年级层面：成绩分析、选课分析、消费分析、考勤分析；
班级层面：师生信息、成绩分析、消费分析、考勤分析。

三、数据探索

3.1 查看源数据

查看源数据的相关信息，比如包含哪些表、表中字段类型与含义、行记录及其含义、数据量级等等。

源数据共有7个表，具体地，1_teacher表包含教师信息、2_studentinfo表包含学生基本信息、3_kaoqin表和4_kaoqintype表与学生考勤信息相关、5_chengji表和6_exam_type表与学生考试信息相关、7_consumption表包含学生消费信息。各表中的字段含义，可参见比赛官网。

3.2 探索各表之间的关联

首先，3_kaoqin表和4_kaoqintype表之间通过考勤类型id和考勤事件id进行关联，5_chengji表和6_exam_type表之间通过考试类型id进行关联；然后，学生表、教师表、考勤表、成绩表、消费表之间可以通过学生id、班级id、学科id、学期等之间进行关联。各表之间具体的关联关系可参见下图。

3.3 归类整理数据指标

对上述各表中的数据指标进行归类整理，如下图所示。首先，可以从两大方面划分上述数据指标，一方面是学生的属性信息，另一方面是学生的行为信息。学生的属性信息又可以分为两类：一是学生的个人信息，比如学号、姓名、性别等，二是学生的群体信息，比如所属班级、所学学科、老师是谁。学生的行为信息又可以分为三类：一是考试信息，比如考试编码、考试学期、考试成绩等，二是考勤信息，比如考勤时间、考勤名称、考勤类型等，三是消费信息，比如消费时间、消费金额等。

四、数据清洗

在数据探索阶段对源数据有了基本了解后，还需要看看数据质量是否存在问题，比如缺失值、异常值、数据格式等问题，由于源数据中有些表的行记录数高达47W+，因此采用python进行数据清洗会更便捷。

4.1 缺失值

（1）首先，查看各表中字段的缺失情况，可以发现1_teacher表、4_kaoqintype表、6_exam_type表、7_consumption表没有缺失值。

（2）2_student_info表有缺失值。

table_2_student_info.isnull().sum()/len(table_2_student_info)

发现：
1.bf_BornDate出生日期（年）缺失度为0.047。随机缺失、缺失比例低、字段为离散型，可考虑填充处理；考虑到同年级的学生的年龄差异不大，因此，使用同年级的学生出生日期的众数进行填充。

2.bf_NativePlace家庭住址（省市或省）缺失度为0.07。随机缺失、缺失比例低、字段为离散型，可考虑填充处理；使用总本中占比最高的项目值进行填充。

3.bf_zhusu是否住校、bf_qinshihao寝室号，这2个字段缺失度均为0.598。进一步发现，bf_zhusu值为1时，对应的bf_qinshihao有值，可以认为住宿的有寝室号，非住校的学生无寝室号。因此，这2个字段属于规律性缺失，根据这种规律进行填充，对bf_zhusu字段填充0表示“不住校”，对bf_qinshihao字段填充0，表示“无寝室号”。

4.bf_leaveSchool是否退学，缺失度为0.98。依据日常生活经验，一个学校的学生退学率要远低于在校率，那么，缺失就表示学生没有退学，是规律性缺失；源数据中用1表示“退学”，那么，用0填充来表示 “没有退学”。

（3）3_kaoqin表有缺失值。

table_3_kaoqin.isnull().sum()/len(table_3_kaoqin)

发现：
qj_term考勤学期，缺失度为0.04。随机缺失，但是可以看到DataDateTime日期时间这个字段是完整的，因此可以考虑参考这个字段，对qj_term进行填充。

经检查发现，qj_term缺失的记录中，对应的日期主要集中在2014/3/10至2014/6/20，此外，单独有条记录的日期为2014/9/29。那么，对于第一种情况，日期集中在2014/3/10至2014/6/20的记录，其qj_term填充为“2013-2014-2”；对于第二种情况，单独的一条日期为2014/9/29的记录，其qj_term填充为“2014-2015-1”。

（4）5_chengji有缺失值。

table_5_chengji.isnull().sum()/len(table_5_chengji)

发现：
1.mes_sub_id考试学科id和mes_sub_name考试学科名称是一一对应的关系，这两个字段缺失度为0.04。经检查认为是随机缺失，不同考试不同学生缺失的考试科目名称各不相同，因此这里不能直接用总体中占比最高的项目值进行填充。先考虑mes_sub_id用0.0填充，mes_sub_name用“缺失”填充，后续根据其他信息进行纠正。

2.Z分数、T分数、等第分数，这3个字段缺失度相同。mes_Score考试成绩、Z分数、T分数、等第分数均是用于衡量学生考试结果的指标，这里为简便起见，先不分析Z分数、T分数、等第分数这个三个值，所以考虑，直接删除这三个字段。

4.2 异常值

4.2.1检查度量值是否存在异常

这里先看下度量值是否存在异常，比如学生的年龄、考试成绩、消费金额等，采用的方法是计算描述统计值。

1.查看学生的年龄

# 查看学生年龄是否存在异常
s_age = table_2_student_info.loc[~table_2_student_info['bf_BornDate'].isnull(),'bf_BornDate'].astype('int')
(2018-s_age).describe()

可以看出，学生的年龄平均值为16岁左右、中位数也为16岁、最大值为19岁，但是最小值为8岁，这里猜想，一种可能是真的有8岁天才少年上高中，另一种可能就是数据异常。具体查看，发现年龄为8岁的记录只有一条，这里无法判定属于上述哪种情况，先不处理。

2.查看学生的考试成绩

# mes_Score,考试成绩(-1为作弊，-2为缺考，-3为免考)
# 将异常情况排除后，再查看成绩的统计值
table_5_chengji.loc[~table_5_chengji['mes_Score'].isin([-1,-2,-3]),'mes_Score'].describe()

可以看出，排除异常情况后学生的成绩均值为69.9，最大值为150，但是最小值出现了0分，一般情况下，正常参加考试的考生成绩不会为0，具体查看这些成绩为0的记录，也没有发现明显异常，猜想可能某些学生交了白卷，故不作处理。

3.查看学生的消费金额

# 查看学生单笔消费金额，是否异常
((-1)*table_7_consumption['MonDeal']).describe()

这里的数据是本学年（2018-2019-1学期）学生的一卡通消费信息，可以看到学生单笔消费记录的平均值为8.37元，最小值为0.01元，75%分位数为10.9，而最大值为404.2元，算是一笔异常大的消费金额。

4.2.2检查维度值是否出现异常

1.学生表中有5个学生的cla_Name异常。

这三种情况对应的cla_id是944、945、947，但是， teacher表中的cla_id并没有这三种班级ID，kaoqin表中也没有这样的班级ID。因此，可以认为这5个学生的班级名称是缺失或待定的，当以“班级”为研究对象时，可不考虑这样的学生记录。

2.学生表中bf_NativePlace家庭住址（省市或省）有异常值。
有少量学生的家庭住址记录的是“汉族”、“汉”，属于异常情况。对这列字段进行分类整理，精确到“省”。使用“未知”字符串对异常值进行填充。

4.3格式转换

这里主要将文本格式转换为日期格式，比如考勤的日期和时间、考试开始时间、消费时间等。Pandas将csv文件读取进来将日期时间型的字段判断为object型，因此，需要手动进行转换。

最后，用python对数据清洗处理完之后，还需要借助pymysql包将清洗好的表分别导入mysql数据库，以便后续tableau连接数据库进行分析和可视化。导入数据库中的表共有5个：teacher表、student_info表、kaoqin_table表、chengji_table表、consumption表。

五、数据分析

将tableau与本地数据库连接，基于上述5个表写sql语句获取不同分析主题的数据源。

Tableau主要操作技巧：
1、交互操作：参数控件、计算字段、筛选器的结合使用、仪表板的筛选器操作、在工具提示中插入图表、仪表板的按钮对象；
2、详细级别表达式：fixed表达式、rank表计算排序函数、lookup表计算函数结合参数、计算字段（依据参数的逻辑值）、筛选器实现不受视图限制的rank排名；
3、各种图表：条形图、折线图、日历图、凹凸图、饼图、气泡图、地图等。

根据项目目标，数智教育分析报表总计有13页，包括校园层面的学生信息、教学资源、宿舍信息、消费信息、考勤情况；年级层面的成绩分析、选课分析、消费分析、考勤分析；班级层面的师生信息、成绩分析、消费分析、考勤分析。校园层面可以面向校长或校领导，年级层面可以面向年级主任或校领导，班级层面可以面向班主任、老师、学生等。

5.1 校园层面

5.1.1学生信息

学生信息仪表板展示内容：

从是否退学、年龄段、年级、政治面貌、家庭住址（省）等维度展示当前学生人数
从性别、是否住校、民族等维度展示当前学生人数及占比

5.1.2教学资源

教学资源仪表板展示内容：

从校园整体角度展示本学期的班级个数、开设课程数、任职老师数等指标
利用年级、班级名称筛选器，实现支持查看任一班级的学生人数
各年级的班级个数&各班级的学生人数
按照降序方式展示各学科的老师数
各学科的任课老师数与开课班级数的对比分析

5.1.3宿舍信息

宿舍信息仪表板展示内容：

按性别维度展示寝室个数、住校人数、住校率等指标
利用男生寝室号/女生寝室号筛选器，实现支持查看任一寝室的住宿人数；利用仪表板的筛选器操作，实现一个工作表筛选另一个工作表，支持通过鼠标悬停来查看任一寝室的住宿学生详情
男女生寝室的各规格寝室个数对比分析

5.1.4消费信息

消费信息仪表板展示内容：

校园卡消费指标，包括本学期的总计消费金额、消费人数、人均消费金额；利用参数控件展示平均每天/每周/每月的消费金额
按照性别、年级、是否住校等维度，展示校园卡消费金额及占比
按照24小时、月份展示校园卡消费金额趋势
将月份筛选器用于选定工作表，支持同时展示每个月的按天消费趋势和消费日历图

5.5.5考勤情况

考勤情况仪表板展示内容：

按照考勤类型如进校日常考勤、离校日常考勤、迟到考勤、校服违规考勤等展示考勤人数和考勤次数
按天展示学生迟到率和校服违规率
利用参数控件，实现支持查看校服违规和迟到违规的TOPN班级排名
历史各学期迟到次数和校服违规次数的对比分析

5.2 年级层面

5.2.1成绩分析

成绩分析仪表板展示内容：

利用年级筛选器，实现支持查询各个年级的考试指标，包括：考试名称、考试开始时间、参考班级数、正常参考学生数
利用仪表板的筛选器操作，实现针对任意一场考试查询该年级各班的考试指标，包括：按总分排名进入年级TOP30人数、班级平均总分、班级排名（按平均总分）
将参数控件、新建字段（直接引用参数值）、筛选器结合使用，实现在同一视图区域切换查看各年级的若干场重要考试的班级前10排名变化

5.2.2选课分析

选课分析仪表板展示内容：

展示7选3不同组合形式对应的选取人数、人均分数
展示7门单科的选取人数、人均分数
展示高三各班的7选3不同组合形式对应的选取人数及占比

5.2.3消费分析

消费分析仪表板展示内容：

利用年级筛选器，实现查看任一年级的学生消费指标，包括：消费金额、消费次数、消费人数
展示任一年级下的各班级消费金额占比及排名，对比分析各班级的平均消费金额和平均消费次数
展示任一年级下的学生消费趋势（按周）、学生消费金额分布情况、学生消费次数分布情况；并利用仪表板的筛选器操作进行联动查询，以便对该年级下的任一班级实现上述相同的分析

5.2.4考勤分析

考勤分析仪表板展示内容：

利用年级筛选器，实现查看任一年级的学生违规考勤指标，包括两种违规类型下的违规人数、违规次数
展示任一年级下的学生迟到次数分布情况、学生校服违规次数分布情况
对比分析两种违规类型下的学生违规次数按周的趋势变化
对比分析任一年级下的各个班级在逐个月份下的迟到违规次数和校服违规次数

5.3 班级层面

5.3.1师生信息

师生信息仪表板展示内容：

利用参数控件实现支持查询任一班级的学生概况，包括入班人数以及从是否退学、性别、是否住校等维度展开的学生人数
展开该班级下的所有学生信息明细、学生住宿信息明细、授课老师明细

5.3.2成绩分析