{ "cells": [ { "cell_type": "markdown", "id": "cd540703", "metadata": {}, "source": [ "# 需求(和20210718中类似)\n", "## 1、统计人数\n", "截止到2021年7月17日晚上20:30左右,当天有学生XX人次、XX名教师、XX名校长完成了测试;累计共有学生XX人次、XX名教师、XX名校长完成了测试。\n", "## 2、作答时间\n", "智能计算素养和问题解决素养两个专题的规定测试时间均为60分钟,截止目前完成测试学生实际作答的平均时间为XX分钟。\n", "\n", "(一)智能计算素养专题\n", "\n", "智能计算素养专题的题目在预测试中作答时长均值为40-50分钟,少数同学的作答时长在30分钟以下和1小时以上。7月17日当天,绵阳地区有XX名学生的实际作答时长小于等于10分钟,XX名学生的实际作答时长小于等于20分钟;XX名学生的实际作答时长小于等于30分钟。总体作答时长分布如下图:\n", "\n", "【一个7月17日当天学生完成作答智能计算素养专题的时长分布饼状图,时长单位为分钟,每10分钟一块就可以,每一块上标注上所占比例,超过60分钟的就一块“60分钟及以上”】\n", "截止到7月17日20:30左右,累计共有XX名学生的实际作答时长小于等于10分钟,XX名学生的实际作答时长小于等于20分钟;XX名学生的实际作答时长小于等于30分钟。总体作答时长分布如下图:\n", "\n", "【一个累计学生完成作答智能计算素养专题的时长分布饼状图,时长单位为分钟,每10分钟一块就可以,每一块上标注上所占比例,超过60分钟的就一块“60分钟及以上”】\n", "\n", "(二)问题解决素养专题\n", "\n", "问题解决素养专题的题目在预测试中33%的学生作答时间超过45分钟,23%的学生超过50分钟,9%的学生超过60分钟,大部分学生都需要50分钟才能完成测试。7月17日当天,绵阳地区有XX名学生的实际作答时长小于等于10分钟,XX名学生的实际作答时长小于等于20分钟;XX名学生的实际作答时长小于等于30分钟。总体作答时长分布如下图:\n", "\n", "【一个7月17日当天学生完成作答问题解决专题的时长分布饼状图,时长单位为分钟,每10分钟一块就可以,每一块上标注上所占比例,超过60分钟的就一块“60分钟及以上”】\n", "\n", "截止到7月17日20:30左右,累计共有XX名学生的实际作答时长小于等于10分钟,XX名学生的实际作答时长小于等于20分钟;XX名学生的实际作答时长小于等于30分钟。总体作答时长分布如下图:\n", "\n", "【一个累计学生完成作答问题解决素养专题的时长分布饼状图,时长单位为分钟,每10分钟一块就可以,每一块上标注上所占比例,超过60分钟的就一块“60分钟及以上”】\n", "\n", "## 3、作答学校分布\n", "7月17日当天,作答时长小于等于10分钟的学生所在学校(大于10人):\n", "\n", "学校名称 | 人数\n", "\n", "\n", "累计到7月17日20:30左右,作答时长小于等于10分钟的学生所在学校(大于50人):\n", "\n", "学校名称 | 人数\n", "## 4、输出EXCEL文件\n", "添加一列做题时长" ] }, { "cell_type": "markdown", "id": "7237d51e", "metadata": {}, "source": [ "# 首先引入第三方库" ] }, { "cell_type": "code", "execution_count": null, "id": "e08a6fa8", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import json \n", "import numpy as np\n", "import ast\n", "from datetime import datetime\n", "import plotly.graph_objs as go\n", "from plotly.offline import plot\n", "import plotly.offline as offline\n", "import plotly.figure_factory as ff\n", "from pandas.core.indexes import interval\n", "import re" ] }, { "cell_type": "markdown", "id": "fecee3d4", "metadata": {}, "source": [ "# 提前定义每一行的计算时间函数" ] }, { "cell_type": "code", "execution_count": 84, "id": "18abbbe7", "metadata": {}, "outputs": [], "source": [ "# 2021-07-16T19:31:13+08:00\n", "def get_interval_per_row(index, df):\n", " row_data = df.loc[index,:]\n", " start_time = row_data['start_time']\n", " if start_time != start_time:\n", " return -1\n", " start_time = datetime.strptime(str(start_time),\"%Y-%m-%dT%H:%M:%S+08:00\")\n", "\n", " expire_time = row_data['expire_time']\n", " if expire_time != expire_time:\n", " return -1\n", " expire_time = datetime.strptime(str(expire_time),\"%Y-%m-%dT%H:%M:%S+08:00\")\n", "\n", " stop_time = row_data['stop_time']\n", " if stop_time != stop_time:\n", " return -1\n", " stop_time = datetime.strptime(str(stop_time),\"%Y-%m-%dT%H:%M:%S+08:00\")\n", "\n", " total_minu = (stop_time - start_time).seconds / 60.0\n", " return total_minu" ] }, { "cell_type": "markdown", "id": "0ad5184c", "metadata": {}, "source": [ "# 提前定义转码task_answers字段的函数" ] }, { "cell_type": "code", "execution_count": 85, "id": "60a879c9", "metadata": {}, "outputs": [], "source": [ "def remove_str_per_row(data_per_row):\n", " frame_list = ast.literal_eval(data_per_row)\n", " frame_dic_list = []\n", " for index in range(len(frame_list)):\n", " temp = json.loads(frame_list[index])\n", " if 'frame' in temp.keys():\n", " if 'data' in temp.keys():\n", " frame_dic_list.append(list(temp['frame']['data'].values())) \n", " else:\n", " frame_dic_list.append(list(temp['frame'].values())) \n", " else:\n", " frame_dic_list.append(temp) \n", " return frame_dic_list" ] }, { "cell_type": "markdown", "id": "ef906be5", "metadata": {}, "source": [ "# 读取数据" ] }, { "cell_type": "code", "execution_count": 86, "id": "b22e2b60", "metadata": { "tags": [] }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\brook\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3165: DtypeWarning:\n", "\n", "Columns (3) have mixed types.Specify dtype option on import or set low_memory=False.\n", "\n" ] } ], "source": [ "df_main = pd.read_csv('./data/data.csv') \n" ] }, { "cell_type": "markdown", "id": "97ab36c7", "metadata": {}, "source": [ "# 测试函数运行是否正常" ] }, { "cell_type": "code", "execution_count": 87, "id": "e6f54eb4", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " | user | \n", "type | \n", "contest_id | \n", "ticket_id | \n", "start_time | \n", "expire_time | \n", "stop_time | \n", "mobile_alert | \n", "task_answers | \n", "user_agent | \n", "client_ip | \n", "school | \n", "tag | \n", "name | \n", "owner | \n", "state | \n", "is_parent | \n", "
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | \n", "mianyang/智能计算素养/90000600614 | \n", "ticket-user | \n", "mianyang/智能计算素养 | \n", "90000600614 | \n", "2021-07-19T20:13:36+08:00 | \n", "2021-07-19T23:33:36+08:00 | \n", "NaN | \n", "NaN | \n", "[] | \n", "Mozilla/5.0 (Windows NT 6.1; Win64; x64) Apple... | \n", "118.122.131.158 | \n", "四川省绵阳外国语学校 | \n", "我是学生,高中生问卷一,高中生问卷二,高中生问卷三,高中生问卷四,高中生问卷五,建模素养,智... | \n", "周驿杭 | \n", "mianyang | \n", "NaN | \n", "0 | \n", "
1 | \n", "mianyang/智能计算素养/90000904110 | \n", "ticket-user | \n", "mianyang/智能计算素养 | \n", "90000904110 | \n", "2021-07-19T20:13:31+08:00 | \n", "2021-07-19T23:33:31+08:00 | \n", "NaN | \n", "NaN | \n", "[] | \n", "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKi... | \n", "119.6.121.150 | \n", "绵阳中学实验学校 | \n", "我是学生,高中生问卷一,高中生问卷二,高中生问卷三,高中生问卷四,高中生问卷五,建模素养,智... | \n", "于子督 | \n", "mianyang | \n", "NaN | \n", "0 | \n", "
2 | \n", "mianyang/智能计算素养/99270100529 | \n", "ticket-user | \n", "mianyang/智能计算素养 | \n", "99270100529 | \n", "2021-07-19T20:13:30+08:00 | \n", "2021-07-19T23:33:30+08:00 | \n", "NaN | \n", "NaN | \n", "[] | \n", "Mozilla/5.0 (Windows NT 6.1; Win64; x64) Apple... | \n", "61.157.144.199 | \n", "四川省平武中学 | \n", "我是学生,高中生问卷一,高中生问卷二,高中生问卷三,高中生问卷四,高中生问卷五,建模素养,智... | \n", "汪洋 | \n", "mianyang | \n", "NaN | \n", "0 | \n", "
3 | \n", "mianyang/问题解决素养/90000209122 | \n", "ticket-user | \n", "mianyang/问题解决素养 | \n", "90000209122 | \n", "2021-07-19T20:13:30+08:00 | \n", "2021-07-19T21:13:30+08:00 | \n", "NaN | \n", "NaN | \n", "[] | \n", "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebK... | \n", "61.139.95.226 | \n", "四川省绵阳中学 | \n", "我是学生,高中生问卷一,高中生问卷二,高中生问卷三,高中生问卷四,高中生问卷五,建模素养,智... | \n", "易柯妍 | \n", "mianyang | \n", "NaN | \n", "0 | \n", "
4 | \n", "mianyang/问题解决素养/90030203327 | \n", "ticket-user | \n", "mianyang/问题解决素养 | \n", "90030203327 | \n", "2021-07-19T20:13:30+08:00 | \n", "2021-07-19T21:13:30+08:00 | \n", "NaN | \n", "NaN | \n", "[] | \n", "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.3... | \n", "61.157.138.2 | \n", "绵阳东辰国际学校 | \n", "我是学生,高中生问卷一,高中生问卷二,高中生问卷三,高中生问卷四,高中生问卷五,建模素养,智... | \n", "罗盉权 | \n", "mianyang | \n", "NaN | \n", "0 | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
45554 | \n", "mianyang/高中校长问卷/99900050001 | \n", "ticket-user | \n", "mianyang/高中校长问卷 | \n", "99900050001 | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "[] | \n", "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.3... | \n", "61.157.144.142 | \n", "绵阳普明中学 | \n", "校长 | \n", "马恒贵 | \n", "mianyang | \n", "NaN | \n", "0 | \n", "
45555 | \n", "mianyang/高中校长问卷/99900060001 | \n", "ticket-user | \n", "mianyang/高中校长问卷 | \n", "99900060001 | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "[] | \n", "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebK... | \n", "182.137.107.159 | \n", "四川省绵阳外国语学校 | \n", "校长 | \n", "漆高明 | \n", "mianyang | \n", "NaN | \n", "0 | \n", "
45556 | \n", "mianyang/高中校长问卷/99924010001 | \n", "ticket-user | \n", "mianyang/高中校长问卷 | \n", "99924010001 | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "[] | \n", "Mozilla/5.0 (Windows NT 6.1; Win64; x64) Apple... | \n", "61.157.144.169 | \n", "四川省绵阳市安州中学 | \n", "校长 | \n", "唐子军 | \n", "mianyang | \n", "NaN | \n", "0 | \n", "
45557 | \n", "mianyang/高中校长问卷/99925010001 | \n", "ticket-user | \n", "mianyang/高中校长问卷 | \n", "99925010001 | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "[] | \n", "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebK... | \n", "61.157.144.137 | \n", "四川省梓潼中学校 | \n", "校长 | \n", "刘翔宇 | \n", "mianyang | \n", "NaN | \n", "0 | \n", "
45558 | \n", "mianyang/高中校长问卷/99926010001 | \n", "ticket-user | \n", "mianyang/高中校长问卷 | \n", "99926010001 | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "[] | \n", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) Appl... | \n", "61.157.144.155 | \n", "四川省北川中学 | \n", "校长 | \n", "刘亚春 | \n", "mianyang | \n", "NaN | \n", "0 | \n", "
45559 rows × 17 columns
\n", "