数据可视化练习(2)
任务二：多个饼图呈现各省份不同等级住宿场所占比
企业消费平台为了更好地发展企业业务，向企业客户推荐符合其定位的协议住宿场所，需要分析上题中Top5省份的“三星级/舒适”、“四星级/高档”和“五星级/豪华”住宿场所以及“其它类别”住宿场所（除上述三类外，其余类型住宿场所均归为“其它类别”）的占比情况。请根据指定表中数据，以指定图例进行呈现。
详细描述：
请根据数据库中相关数据集中省份、星级等相关字段统计各省份不同等级住宿场所的占比，绘制多个饼图。
具体任务要求：
1） 根据表格相关字段分别统计上题Top5省份不同星级住宿场所的占比，打印输出各省份名称以及各星级住宿场所的占比情况；
打印语句格式如下：
==省份：A=舒适型住宿占比：**===
==省份：A=高档型住宿占比：**===
==省份：A=豪华型住宿占比：**===
==省份：B=舒适型住宿占比：**===
==省份：B=高档型住宿占比：**===
==省份：B=豪华型住宿占比：**===
……
2） 使用Flask框架，结合Echarts在组合图中绘制多个饼图，每个省份各星级住宿场所占比情况分别用一个饼图进行呈现，将可视化结果截图并保存。
1)ralateLevels.py
import datetime
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
mysql = create_engine('mysql+mysqlconnector://root:123456@127.0.0.1:3306/hotel')
df_orderNum = pd.read_csv(
'E:\\Python作业\\keshihua\\enterpriseCustomize\\task2\\csv\\2022-06-22_1655885919.934801df_data.csv')
sql = 'select province,star from platform_rate'
df_platformRate = pd.read_sql(sql, mysql)
df_starNum = pd.DataFrame({'starNum': []})
topFiveProvince = []
for i in range(5):
topFiveProvince.append(df_orderNum['province'][i])
def change_name(x):
if x == '三星级':
return '舒适型'
elif x == '四星级':
return '高档型'
elif x == '五星级':
return '豪华型'
elif x == '舒适型':
return '舒适型'
elif x == '高档型':
return '高档型'
elif x == '豪华型':
return '豪华型'
else:
return '其它类别'
df_platformRate['star'] = df_platformRate['star'].apply(change_name)
df_topFiveProvince = pd.DataFrame({'province': [], 'star': []})
for i in topFiveProvince:
for j in df_platformRate.groupby(df_platformRate['province']):
if j[0] == i:
df_topFiveProvince = pd.concat([df_topFiveProvince, j[1]])
df_topFiveProvince_1 = df_topFiveProvince.groupby([df_topFiveProvince['star'], df_topFiveProvince['province']])
df_topFiveProvince_2 = df_topFiveProvince.groupby( df_topFiveProvince['province'])
count = []
sum_count = []
level = []
province = []
for i in df_topFiveProvince_2:
sum_count.append(i[1]['province'].count())
for i in df_topFiveProvince_1:
count.append(i[1].count())
level.append(i[0][0])
province.append(i[0][1])
print("==省份：" + i[0][1] + "=" + i[0][0] + "住宿占比：**===")
print(count)
print(level)
print(province)
print(sum_count)
df_output = pd.DataFrame({'province': province, 'star': level, 'starNum': count})
print(df_output)
Now_Time = datetime.datetime.now()
filepath = "csv//" + str(Now_Time.date()) + "_" + str(Now_Time.timestamp()) + "df_ralateLevels.csv"
df_output.to_csv(filepath, index=False, header=['province', 'star', 'starNum'])
2）Echarts_pies.html
<!DOCTYPE html>
<html lang="en" style="height:100%">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="../static/echarts.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/echarts/5.3.2/echarts.min.js"></script>
</head>
<body style="height:100%;width:100%">
<!-- 创建div容器用于存放图形 -->
<div id="box" style="width:1000px;height: 700px">fucking window</div>
<!-- 引入echarts资源 -->
<script>
var myChart = echarts.init(document.getElementById("box"));
var option;
// 从后端获取数据
var province = [{% for item in province %}'{{ item }}', {% endfor %}];
var star = [{% for item in star %}'{{ item }}', {% endfor %}];
var starNum = [{% for item in starNum %}{{ item }}, {% endfor %}];
var objects = {{ objects|safe }};
// 配置图形参数
option = {
title: [
{
text: '前五省市酒店等级比例',
top: '25%',
left: '35%'
},
{
subtext: '{{province[0]}}',
left: '15%',
top: '75%',
textAlign: 'center'
},
{
subtext: '{{province[1]}}',
left: '30%',
top: '75%',
textAlign: 'center'
},
{
subtext: '{{province[2]}}',
left: '45%',
top: '75%',
textAlign: 'center'
},
{
subtext: '{{province[3]}}',
left: '60%',
top: '75%',
textAlign: 'center'
},
{
subtext: '{{province[4]}}',
left: '75%',
top: '75%',
textAlign: 'center'
}
],
dataset: [
{
source: [
['star', 'starNum', 'province'], {%for i in range(20) %}
{{ objects[i]| safe }}, {% endfor %}
]
},
{
transform: {
type: 'filter',
config: { dimension: 'province', value: '{{province[0]}}' }
}
},
{
transform: {
type: 'filter',
config: { dimension: 'province', value: '{{province[1]}}' }
}
},
{
transform: {
type: 'filter',
config: { dimension: 'province', value: '{{province[2]}}' }
}
},
{
transform: {
type: 'filter',
config: { dimension: 'province', value: '{{province[3]}}' }
}
},
{
transform: {
type: 'filter',
config: { dimension: 'province', value: '{{province[4]}}' }
}
},
],
series: [
{
type: 'pie',
radius: 40,
center: ['50%', '15%'],
datasetIndex: 1
},
{
type: 'pie',
radius: 40,
center: ['50%', '30%'],
datasetIndex: 2
},
{
type: 'pie',
radius: 40,
center: ['50%', '45%'],
datasetIndex: 3
},
{
type: 'pie',
radius: 40,
center: ['50%', '60%'],
datasetIndex: 4
},
{
type: 'pie',
radius: 40,
center: ['50%', '75%'],
datasetIndex: 5
},
],
// Optional. Only for responsive layout:
media: [
{
query: { minAspectRatio: 1 },
option: {
series: [
{ center: ['15%', '50%'] },
{ center: ['30%', '50%'] },
{ center: ['45%', '50%'] },
{ center: ['60%', '50%'] },
{ center: ['75%', '50%'] }
]
}
},
{
option: {
series: [
{ center: ['50%', '15%'] },
{ center: ['50%', '30%'] },
{ center: ['50%', '45%'] },
{ center: ['50%', '60%'] },
{ center: ['50%', '75%'] },
]
}
}
]
};
// 将配置的参数传递给echarts对象
option && myChart.setOption(option);
</script>
</body>
</html>
3)app.py
from flask import Flask, render_template
import pandas as pd
import os
# 获取数据
Names_last_csv = os.listdir(r"E:\Python作业\keshihua\enterpriseCustomize\task2\csv")
# print(Name_last_csv)
Name_last_csv_01 = '\\' + Names_last_csv[-1]
Name_last_csv_02 = r'E:\Python作业\keshihua\enterpriseCustomize\task2\csv' + Name_last_csv_01
print(Name_last_csv_02)
data = pd.read_csv(Name_last_csv_02)
# 获取数据的列
columns = data.columns
# 提取数据
province = data['province'].tolist()
starNum = data['starNum'].tolist()
star = data['star'].tolist()
item = []
objects = []
for i in range(len(province)):
item.append(star[i])
item.append(starNum[i])
item.append(province[i])
objects.append(item)
item = []
# 创建flask实例
app = Flask(__name__)
# 创建视图
@app.route("/")
def echarts_01():
# 数据传输到前端
# return render_template("Echarts_pies.html", province=province, starNum=starNum, star=star)
return render_template("Echarts_pies.html", province=province, starNum=starNum, star=star, objects=objects)
#
# 运行程序
if __name__ == '__main__':
app.run(debug=True)
