一、数据读取
import pandas as pd
import pymysql
config = {
'host':'localhost',
'port':3306,
'user':'root',
'password':'123456'
}
db = pymysql.connect(**config)
sql=('''SELECT * from PER_INFO.T_SCORE''')
data=pd.read_sql(sql,db)
db.close()
data
还是用之前的老数据
二、柱状图加线图from pyecharts import options as opts
from pyecharts.charts import Bar, Grid, Line, Liquid, Page, Pie ,Tab
data_avg=data.groupby(['CLASS_ID','SUBJECT'])['SCORE'].agg(['count','mean']).round(2).unstack()
data_avg
bar =Bar()
bar.add_xaxis(list(data_avg['count'].columns))
for i in data_avg.index:
bar.add_yaxis(i,list(data_avg.loc[i]['count']))
line =Line()
line.add_xaxis(list(data_avg['mean'].columns))
line.extend_axis(yaxis=opts.AxisOpts(name='平均分',position='right'))
for i in data_avg.index:
line.add_yaxis(i,list(data_avg.loc[i]['mean']),yaxis_index=1,z=10)
line.overlap(bar)
line.render('index.html')
line.render_notebook()
分班级统计了各个科目的平均分,这个二班英语有点差呀~
三、饼图加选项卡
data_cut=data.groupby(['SUBJECT',pd.cut(data.SCORE,5)])['SCORE'].count().unstack()
data_cut
tab=Tab()
for i in data_cut.index:
data_t=data_cut.loc[i].fillna(0).to_dict()
data_t=[[str(k),v] for k,v in data_t.items()]
pie=Pie()
pie.add('',data_t)
pie.set_series_opts(label_opts=opts.LabelOpts(formatter='{b}: {c}'))
tab.add(pie,i)
tab.render('tab.html')
tab.render_notebook()
按科目查看同学们的分数区间分布
四、创建局域网网址
新建一个 Flask 项目$ mkdir pyecharts-flask-demo
$ cd pyecharts-flask-demo
$ mkdir templates
将上面两步生成的静态html文件存入templates中,在根目录下新建app.py文件,内部代码如下:
from flask import Flask,render_template
app = Flask(__name__)
@app.route('/report/')
def index():
return render_template('index.html')
@app.route('/report/')
def login(name):
return render_template(name)
if __name__ == '__main__':
app.run(host='0.0.0.0', port=5000)
运行app.py
访问网址 http://ip:5000/report/tab.html 即可进行网页报表浏览
五、设置消息自动推送
我们可以利用之前的机器人自动推送设置,进行上述报表的自动化推送及刷新,领导和同事们可以实现点击即可见
import requests
def post_robot(data):
r = requests.post(url='机器人地址',
json=data)
content = {
'msgtype': 'news',
'news': {
'articles': [
{ 'title': '各科目分数段分布',
'url': 'http://ip:5000/report/tab.html'},
{'title': '各班级平均分数对比',
'url': 'http://ip:5000/report/page.html'},
]
}
}
post_robot(content)效果:
将上述代码整合后进行定时刷新、定点推送就实现了最简单的报表网页化,下班时间又可以提前了!