一、軟件及對應版本
- Python 3.6.1 |Anaconda custom
- Flask 0.12.2
- Echarts 4.0
二、項目目錄
三、代碼展示
from flask import Flask,render_template, url_for
import pymysql
import pandas as pd
app = Flask(__name__)
app.jinja_env.filters['zip'] = zip
def conn_db():
conn = pymysql.connect(host="your_host", user="your_username", password="your_password", db="your_database", charset="utf8")
return conn
def get_data(conn=conn_db(), n=10):
# Get the top10 products
sql = "select ProductMName, date_format(FeedbackDate, '%Y-%m') Month, count(FeedBack) Count from Feedbacks group by ProductMName, Month order by Count desc, Month asc"
df = pd.read_sql(sql, conn)
conn.close()
product_count = []
for index, value in enumerate(df["ProductMName"].unique()):
product_count.append([value, index+1])
product_count = dict(product_count)
df["Rank"] = df["ProductMName"].map(lambda x: product_count[x])
df = df[df["Rank"]<=n][["ProductMName", "Month", "Count"]]
df.sort_values(by="Month", inplace=True)
df_TotleCount = df.groupby(["ProductMName"])["Count"].sum()
df_TotleCount.sort_values(ascending=False, inplace=True)
df_pivot = pd.pivot_table(df, index="ProductMName", columns="Month", values="Count", fill_value=0)
return df_TotleCount, df_pivot
@app.route('/')
def display_FeedbackCountByProject():
df_TotleCount, df_pivot = get_data(n=10)
TotleCount = df_TotleCount.values
ProductMName = df_pivot.index.tolist()
Month = df_pivot.columns.tolist()
Count = df_pivot.values.tolist()
return render_template('FeedbackCountByProject.html', ProductMName=ProductMName, Month=Month, Count=Count, TotleCount=TotleCount)
if __name__ == "__main__":
app.run(debug = True)
- FeedbackCountByProject.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<script src="{{ url_for('static', filename='echarts.min.js') }}"></script>
<title></title>
<style>
*{
margin: 0; padding: 0;
}
</style>
</head>
<body>
<div id="top" style="width: 1200px; height: 400px; padding: 20px;"></div>
<div id="bottom" style="width: 1200px; height: 400px; padding: 20px;"></div>
<script type="text/javascript">
var myTop = echarts.init(document.getElementById('top'));
var myBottom = echarts.init(document.getElementById('bottom'));
optionTop = {
title: {
text: 'Top 10 Project By Date',
left: 0,
top: -5,
subtextStyle: {
fontWeight: 'bolder'
},
},
toolbox: {
show: true,
right: 80,
feature: {
saveAsImage: {}
}
},
legend: {
show: true,
data: ['', '', {% for p in ProductMName %}'{{ p }}', {% endfor %}],
right: 0
},
tooltip: {
trigger: 'axis'
},
grid: {
bottom: 90
},
xAxis: {
type: 'category',
boundaryGap: false,
name: 'Month of FeedbackDate',
nameLocation: 'center',
nameGap: 22,
data: [{% for m in Month %}'{{ m }}', {% endfor %}],
silent: false,
splitLine: {
show: false
},
splitArea: {
show: false
},
nameTextStyle: {
fontWeight: 'bolder'
}
},
yAxis: {
type: 'value',
name: 'FeedbackCnt',
nameLocation: 'center',
nameGap: 42,
splitArea: {
show: false
},
nameTextStyle: {
fontWeight: 'bolder'
}
},
series: [
{% for pr, cnt in ProductMName|zip(Count) %}{
name: '{{ pr }}',
type: 'line',
data: {{ cnt }},
symbol: 'circle',
symbolSize: 1
}, {% endfor %}
]
};
optionBottom = {
title: {
text: 'Top 10 Project By Feedback Count',
left: 0,
top: -5,
subtextStyle: {
fontWeight: 'bolder'
},
},
toolbox: {
show: true,
feature: {
saveAsImage: {}
}
},
legend: {
show: false
},
tooltip: {
trigger: 'axis'
},
grid: {
bottom: 90
},
xAxis: {
type: 'category',
data: [{% for p in ProductMName %}'{{ p }}', {% endfor %}],
name: 'Products',
nameLocation: 'center',
nameGap: 22,
silent: false,
splitLine: {
show: false
},
splitArea: {
show: false
},
nameTextStyle: {
fontWeight: 'bolder'
},
axisLabel: {
interval: 0,
formatter:function(value){
var ret = "";
var maxLength = 18;
var valLength = value.length;
var rowN = Math.ceil(valLength / maxLength);
if (rowN > 1)
{
for (var i=0; i<rowN; i++){
var temp = "";
var start = i * maxLength;
var end = start + maxLength;
temp = value.substring(start, end) + "\n";
ret += temp;
}
return ret;
}else{
return value;
}
}
}
},
yAxis: {
type: 'value',
name: 'FeedbackCnt',
nameLocation: 'center',
nameGap: 42,
splitArea: {
show: false
},
nameTextStyle: {
fontWeight: 'bolder'
}
},
series: [
{
type: 'bar',
data: [{% for tcnt in TotleCount %}{{ tcnt }}, {% endfor %}]
}
]
};
myTop.setOption(optionTop);
myBottom.setOption(optionBottom);
</script>
</body>
</html>
四、圖表展示
Top 10 Project By Date.png
Top 10 Project By Feedback Count.png
