一、代码分析
结果可视化使用Python+Mysql+Flask+ECharts进行绘制。创建一个Flask web应用程序的前端部分,包含一个HTML页面,用于展示北京市短租房的分析数据。页面中使用了ECharts来生成图表,并且通过AJAX调用获取JSON数据来更新图表。整体思路可以分为以下几个步骤:
第一,环境准备。安装Python,并配置环境(如:pip安装Flask、pymysql等),安装ECharts。
第二,连接MySQL数据库。使用Python的pymysql库连接MySQL数据库,创建数据库连接池以提高性能。
第三,数据查询。使用SQL语句从MySQL数据库中查询需要可视化的数据,将查询结果存储在Python的列表中。
import pymysql
import time
def get_conn():
conn = pymysql.connect(host='localhost', user='root', password='123456', database='Design', charset="utf8")
cursor = conn.cursor()
return conn, cursor
def close_conn(conn, cursor):
cursor.close()
conn.close()
def query(sql, *args):
conn, cursor = get_conn()
cursor.execute(sql, args)
res = cursor.fetchall()
close_conn(conn, cursor)
return res
def get_time():
time_str = time.strftime('%Y{}%m{}%d{} %X ')
return time_str.format('年', '月', '日')
def get_l1_data():
sql = 'select * from Amenities order by count desc limit 20'
res = query(sql)
return res
def get_r1_data():
sql = 'select * from property_type limit 15'
res = query(sql)
return res
def get_l2_data():
sql = 'select * from room_type'
res = query(sql)
return res
def get_r2_data():
sql = 'select room_num,host_num from host_house order by host_num desc limit 15'
res = query(sql)
return res
def get_c1_data():
sql ="select host_name,property_type,room_type,house_sum from host_houseinfo"
res = query(sql)
return res
def get_c2_data():
sql_name = 'select * from namewordcount order by count desc limit 400'
res_name = query(sql_name)
sql_jt = 'select * from transitwordcount order by count desc limit 400'
res_jt = query(sql_jt)
sql_hd = 'select * from interwordcount order by count desc limit 400'
res_hd = query(sql_hd)
# 将结果分别存储在一个字典中,以便区分
res = {
'nc': res_name,
'jt': res_jt,
'hd': res_hd
}
return res
def cyt():
# 从name表读取数据
sql_name = 'select * from namewordcount order by count desc limit 400'
res_name = query(sql_name)
# 从jt表读取数据
sql_jt = 'select * from transitwordcount order by count desc limit 400'
res_jt = query(sql_jt)
# 从hd表读取数据
sql_hd = 'select * from interwordcount order by count desc limit 400'
res_hd = query(sql_hd)
# 将结果分别存储在一个字典中,以便区分
res = {
'nc': res_name,
'jt': res_jt,
'hd': res_hd
}
return res
def map():
sql = 'select * from lng_lat limit 2000'
res = query(sql)
return res
def price_l1():
sql = 'select * from region_price'
res = query(sql)
return res
def price_l2():
sql = 'select * from house_price'
res = query(sql)
return res
def price_r1():
sql = 'select * from pro_priceavg'
res = query(sql)
return res
def price_r2():
sql = 'select price,reviews from price_reviews order by reviews desc limit 10'
res = query(sql)
return res
def price_c1():
sql = 'select * from propose'
res = query(sql)
return res
def price_c2():
sql = 'select * from area'
res = query(sql)
return res
def area_l1():
sql = 'select * from (select distinct neighbourhood, room_sum AS shared_sum from type where room_type = "Shared room") a left join (select distinct neighbourhood, room_sum AS private_sum from type where room_type = "Private room") b on a.neighbourhood = b.neighbourhood left join (select distinct neighbourhood, room_sum AS entire_sum from type where room_type = "Entire home/apt") c on a.neighbourhood = c.neighbourhood'
res = query(sql)
return res
def area_l2():
sql = "select * from price_area"
res = query(sql)
return res
def area_r1():
sql = "select * from host_area"
res = query(sql)
return res
def area_r2():
sql = 'select * from house_sum'
res = query(sql)
return res
def last():
sql = "select * from last_review"
res = query(sql)
return res
第四,创建Flask应用。创建一个基于Flask框架的Web应用程序,主要功能是通过不同的路由(route)来渲染HTML页面和提供JSON数据。步骤如下:
- 定义应用程序:app = Flask(__name__),这行代码创建了一个Flask web应用程序实例。
- 路由定义:
/:默认路由,渲染index.html页面。
/map:渲染map.html页面。
/cy:渲染cy.html页面。
/last:调用mysql.last()函数获取数据,并渲染last_review.html页面。
/time:调用mysql.get_time()函数获取时间信息。
第五,JSON数据接口。/l1、/r1、/l2、/r2、/c1、/c2、/mapdata、/yun、/price_l1、/price_r1、/price_l2、/price_r2、/price_c1、/price_c2、/area_l1、/area_l2、/area_r1、/area_r2:这些路由提供JSON格式的数据,通过jsonify函数返回。这些数据来自于mysql模块的多个函数,如get_l1_data、get_r1_data等。
第六,静态文件。/static/beijing.json:渲染静态文件beijing.json。
第七,运行应用程序:if __name__ == '__main__':。
show.py代码如下:
from flask import Flask, jsonify, send_from_directory
from flask import Flask, render_template
import mysql
app = Flask(__name__)
#界面
@app.route('/')
def index():
return render_template('index.html')
@app.route('/map')
def beijing():
return render_template('map.html')
@app.route('/cy')
def cy():
return render_template('cy.html')
@app.route('/last')
def last():
item = mysql.last()
data = {}
years = []
for area, year, house_sum in item:
year = str(year)
if year not in years:
years.append(year)
if year not in data:
data[year] = []
data[year].append({'area': area, 'house_sum': house_sum})
dataall = [data.get(year, []) for year in years]
return render_template('last_review.html', year=years, data=dataall)
@app.route('/time')
def time():
return mysql.get_time()
# json
@app.route('/l1')
def get_l1_data():
name, value = [], []
for n, v in mysql.get_l1_data():
name.append(n)
value.append(int(v))
return jsonify({'name': name, 'value': value})
@app.route('/r1')
def get_r1_data():
name, value = [], []
for n, v in mysql.get_r1_data():
name.append(n)
value.append(int(v))
return jsonify({'name': name, 'value': value})
@app.route('/l2')
def get_l2_data():
data = mysql.get_l2_data()
d = []
for i in data:
n = i[0]
v = int(i[1])
d.append({"value": v,"name": n})
return jsonify({"room": d})
@app.route('/r2')
def get_r2_data():
item = mysql.get_r2_data()
data1,data = [], []
for item in item:
data1.append(item[0])
data.append(item[1])
return jsonify({"data":data,"data1":data1})
@app.route('/c1')
def get_c1_data():
data = mysql.get_c1_data()
return jsonify({'data':data})
@app.route('/c2')
def get_c2_data():
c = mysql.get_c2_data()
data = {}
for category, items in c.items():
dict_list = [{"name": item[0], "value": item[1]} for item in items]
data[category] = dict_list
return jsonify(data)
@app.route('/mapdata')
def map():
data = mysql.map()
data1 = []
for i in data:
lng = i[0]
lat = i[1]
data1.append({"lng": lng, "lat": lat})
return jsonify({"data": data1})
@app.route('/yun')
def yun():
c = mysql.cyt()
# 初始化一个空字典,用于存储处理后的数据
data = {}
for category, items in c.items():
# 使用字典推导式将元组列表转换为字典列表
dict_list = [{"name": item[0], "value": item[1]} for item in items]
# 将处理后的数据添加到data字典中
data[category] = dict_list
# 返回一个包含新数据结构的JSON响应
return jsonify(data)
@app.route('/price_l1')
def get_price_l1_data():
data = mysql.price_l1()
hood,max,min,avg = [],[],[],[]
for item in data:
hood.append(item[0])
max.append(item[1])
min.append(item[2])
avg.append(item[3])
return jsonify({'hood':hood,'max':max,'min':min,'avg':avg})
@app.route('/price_r1')
def get_price_r1_data():
name, value = [], []
for n, v in mysql.price_r1():
name.append(n)
value.append(int(v))
return jsonify({'name': name, 'value': value})
@app.route('/price_l2')
def get_price_l2_data():
data = mysql.price_l2()
d = []
for i in data:
n = i[0]
v = int(i[1])
d.append({"value": v,"name": n})
return jsonify({"data": d})
@app.route('/price_r2')
def get_price_r2_data():
item = mysql.price_r2()
data1,data = [], []
for item in item:
data1.append(item[0])
data.append(item[1])
return jsonify({"data":data,"data1":data1})
@app.route('/price_c1')
def get_price_c1_data():
data = mysql.price_c1()
return jsonify({'data':data})
@app.route('/static/beijing.json')
def beijing_json():
# send_from_directory函数用于安全地发送静态文件
return send_from_directory('static', 'beijing.json')
@app.route('/price_c2')
def get_price_c2_data():
data = mysql.price_c2()
return jsonify({'data':data})
@app.route('/area_l1')
def get_area_l1_data():
name, value,name1, value1,name2, value2 = [], [],[],[],[],[]
for n, v,n1,v1,n2,v2 in mysql.area_l1():
name.append(n)
value.append(int(v))
name1.append(n1)
value1.append(int(v1))
name2.append(n2)
value2.append(int(v2))
return jsonify({'name': name, 'value': value,'name1': name1, 'value1': value1,'name2': name2, 'value2': value2})
@app.route('/area_l2')
def get_area_l2_data():
data = mysql.area_l2()
d = []
for i in data:
n = i[0]
v = int(i[1])
d.append({"value": v,"name": n})
return jsonify({"data": d})
@app.route('/area_r1')
def get_area_r1_data():
data = mysql.area_r1()
a = 300
value,d = [],[]
for i in data:
n = i[0]
v = int(i[1])
max_value = v+a
value.append(v)
d.append({"max": max_value, "name": n})
return jsonify({'value': value,'data':d})
@app.route('/area_r2')
def get_area_r2_data():
data = mysql.area_r2()
d = []
for i in data:
n = i[0]
v = int(i[1])
d.append({"value": v,"name": n})
return jsonify({"data": d})
if __name__ == '__main__':
app.run(debug=True)
第八,编写JavaScript文件。代码的主要功能是使用 ECharts(一个使用 JavaScript 实现的开源可视化库)来初始化并更新一个图表。代码实现了一个图表的动态数据加载和展示功能。get_l1_data 函数,这是一个异步函数(使用 jQuery 的 $.ajax 方法),它从服务端获取数据,数据以 JSON 格式返回,其中包含图表需要的 x 轴标签(name)和 y 轴数值(value)。数据接收与处理,在 $.ajax 的 success 回调函数中,数据被处理并用于更新图表。具体来说,它更新了图表的 y 轴数据(ec_left1_option.yAxis.data)和 系列中的数据(ec_left1_option.series[0].data),然后调用 ec_left1.setOption(ec_left1_option) 来应用这些更改。ec_left1 初始化,图表被初始化为一个 ECharts 实例,它绑定到页面上 id 为 'l1' 的元素上,并且指定了主题样式为 “dark”。ec_left1_option 配置,这是用于配置图表的选项对象。它包括图表的标题、颜色、提示框(tooltip)、工具箱(toolbox)、网格(grid)、坐标轴(xAxis 和 yAxis)和系列(series)。ec_left1.setOption(ec_left1_option),这是用于应用图表配置选项的方法,它会更新现有图表或创建一个新的图表。
如下是ec_left1.js:
function get_l1_data(){
$.ajax({
url:'/l1',
success:function(data){
ec_left1_option.yAxis.data=data.name
ec_left1_option.series[0].data=data.value
ec_left1.setOption(ec_left1_option)
}
});
}
get_l1_data();
var ec_left1 = echarts.init(document.getElementById('l1'),"dark");
var ec_left1_option = {
title : {
text: '租客关注的便利措施',
textStyle : { color : 'white'},
},
color: ['#3398DB'],
tooltip: {
trigger: 'axis',
axisPointer: {type: 'shadow'}
},
toolbox: {
show: true,
feature: {
dataView: { show: true, readOnly: false },
magicType: { show: true, type: ['bar', 'line'] },
restore: { show: true },
saveAsImage: { show: true }
}
},
grid: {
left: '-10%',
right: '8%',
bottom: '1%',
containLabel: true
},
xAxis: {
type: 'value',
boundaryGap: [0.05, 0.1],
name:"次数",
type: 'value',
color : 'white',
animationDuration: 300,
animationDurationUpdate: 300,
},
yAxis: {
type: 'category',
data: [],
axisLabel: {
rotate: -1, // 旋转x轴标签,避免重叠
interval: 0 // 显示所有x轴标签
},
},
series: [{
name: '出现次数',
type: 'bar',
data: [],
barMaxWidth:"50%",
itemStyle: {
normal: {
color: function(params) {
// 自定义颜色
var colorList = ['#CDB5CD'];
return colorList[params.dataIndex % colorList.length];
}
}
}
}]
};
ec_left1.setOption(ec_left1_option)
其他JavaScript代码:
第九,编写前端HTML页面。导航栏,包含三个导航链接,用于切换不同的标签页。<div class="l1" id="l1"></div> 等,用于显示图表的容器。<div class="but">,包含几个按钮,用于触发不同的操作,如词云图的显示、跳转到其他页面等。ECharts 初始化通过使用相应的 JavaScript 文件来配置和更新图表。在html页面中使用了 jQuery 来处理 DOM 加载完成的事件,以及导航链接的点击事件。当 DOM 加载完成后,会自动激活第一个标签页。点击导航链接会切换当前激活的标签页。页面中还引用了多个 JavaScript 文件,这些文件包含了处理数据、初始化图表和更新图表的逻辑。例如,ec_left1.js 可能包含了初始化 id 为 'l1' 的图表的逻辑。
整体来说,这是一个使用 ECharts 和 jQuery 构建的交互式数据分析网页,用户可以通过切换导航标签来查看不同的数据视图。
如下是index.html代码:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>北京市短租房</title>
<link rel="stylesheet" href="../static/css/main.css">
<script src="https://assets.pyecharts.org/assets/v5/echarts.min.js"></script>
<script src="../static/jquery/jquery-3.6.0.min.js"></script>
<script src="../static/js/echarts-wordcloud.min.js"></script>
<script type="text/javascript" src="http://api.map.baidu.com/api?v=2.0&ak=5ieMMexWmzB9jivTq6oCRX9j&callback"></script>
</head>
<body>
<div class="index_nav">
<ul>
<li class="tab-link" data-target="tab1">整体分析</li>
<li class="tab-link" data-target="tab2">价格分布</li>
<li class="tab-link" data-target="tab3">区域分布</li>
</ul>
</div>
<div class="inner active" id="tab1">
<div class="title">北京市短租房分析</div>
<div class="tim"></div>
<div class="l1" id="l1"></div>
<div class="l2" id="l2"></div>
<div class="c1">
<div class="num1"><h1></h1></div>
<div class="num"><h1></h1></div>
<div class="num"><h1></h1></div>
<div class="num"><h1></h1></div>
<div class="txt"><h2>房东名</h2></div>
<div class="txt"><h2>房源类型</h2></div>
<div class="txt"><h2>房源房间类型</h2></div>
<div class="txt"><h2>房屋数</h2></div>
</div>
<div class="c2" id="c2"></div>
<div class="but">
<button class="bu" id="nc">名称词云图</button>
<button class="bu" id="jt">交通词云图</button>
<button class="bu" id="hd">互动词云图</button>
<button class="bu" onclick="location.href='http://127.0.0.1:5000/cy'">词云图清晰版</button>
<button class="bu" onclick="location.href='http://127.0.0.1:5000/last'">year-房屋评论数</button>
<button class="bu" onclick="location.href='http://127.0.0.1:5000/map'">短租房分布地图</button>
</div>
<div class="r1" id="r1"></div>
<div class="r2" id="r2"></div>
<script src="../static/js/get_time.js"></script>
<script src="../static/js/ec_c1.js"></script>
<script src="../static/js/ec_c2.js"></script>
<script src="../static/js/ec_left1.js"></script>
<script src="../static/js/ec_left2.js"></script>
<script src="../static/js/ec_right1.js"></script>
<script src="../static/js/ec_right2.js"></script>
</div>
<div class="inner active" id="tab2">
<div class="title">北京市短租房分析</div>
<div class="tim"></div>
<div class="l1" id="price_l1"></div>
<div class="l2" id="price_l2"></div>
<div class="c1">
<div class="price_num"><h1></h1></div>
<div class="price_num"><h1></h1></div>
<div class="price_num"><h1></h1></div>
<div class="price_num"><h1></h1></div>
<div class="txt"><h2>房源类型</h2></div>
<div class="txt"><h2>卧室个数</h2></div>
<div class="txt"><h2>价格平均值</h2></div>
<div class="txt"><h2>清洁费平均值</h2></div>
</div>
<div class="c22" id="price_c2"></div>
<div class="r1" id="price_r1"></div>
<div class="r2" id="price_r2"></div>
<script src="../static/js/get_time.js"></script>
<script src="../static/js/price_c1.js"></script>
<script src="../static/js/price_c2.js"></script>
<script src="../static/js/price_left1.js"></script>
<script src="../static/js/price_left2.js"></script>
<script src="../static/js/price_right1.js"></script>
<script src="../static/js/price_right2.js"></script>
</div>
<div class="inner active" id="tab3">
<div class="title">北京市短租房分析</div>
<div class="tim"></div>
<div class="l1" id="area_l1"></div>
<div class="l2" id="area_l2"></div>
<div class="map_con left" id="car_control">
<div class="left map_left" >
<div class="map_box" id="map_box"></div>
</div>
</div>
<div class="r1" id="area_r1"></div>
<div class="r2" id="area_r2"></div>
<script src="../static/js/get_time.js"></script>
<script src="../static/js/area_c.js"></script>
<script src="../static/js/area_l1.js"></script>
<script src="../static/js/area_l2.js"></script>
<script src="../static/js/area_r1.js"></script>
<script src="../static/js/area_r2.js"></script>
</div>
<script>
// 等待DOM加载完成
document.addEventListener('DOMContentLoaded', function() {
// 默认激活第一个tab
var firstTabLink = document.querySelector('.tab-link[data-target="tab1"]');
firstTabLink.click(); // 模拟点击第一个tab
});
document.querySelectorAll('.tab-link').forEach(link => {
link.addEventListener('click', () => {
const target = link.getAttribute('data-target');
document.querySelectorAll('.inner').forEach(content => {
if (content.id === target) {
content.classList.add('active');
} else {
content.classList.remove('active');
}
});
});
});
</script>
</body>
</html>
css部分代码如下:
.l_left{float: left;}
.r_right{float: right}
.inner {
display: none;
}
.inner.active {
display: block;
}
.index_nav li {
display: inline;
margin-right: 12px;
cursor: pointer;
border: 1px solid #0E94EA; /* 边框 */
}
.index_nav ul li{font-size: 1em;color: #fff;opacity: 0.8;padding-top:8px;padding-left:10px;padding-right:10px;padding-bottom:5px;cursor: pointer;text-shadow:0 6px 8px #00225b }
.index_nav ul .l_left{margin-left: 3%}
.index_nav ul .r_right{margin-right: 3%}
body{
margin: 0;
background-color: #100c2a;
}
.title{
position: absolute;
width: 40%;
height: 10%;
top: 0;
left: 30%;
color: white;
font-size: 45px;
display: flex;
align-items: center;
justify-content: center;
}
.l1{
position: absolute;
width: 30%;
height: 45%;
top: 10%;
left: 0;
/*background-color: aquamarine; */
}
.l2{
position: absolute;
width: 30%;
height: 45%;
top: 55%;
left: 0;
/* background-color: blue; */
}
.c1{
position: absolute;
width: 40%;
height: 20%;
top: 10%;
left: 30%;
ackground-color: blue;
}
.c1 .num {
height: 30px; /* 设置固定高度 */
overflow: hidden; /* 隐藏超出尺寸的内容 */
white-space: nowrap; /* 确保内容不会换行 */
text-overflow: ellipsis; /* 超出内容显示省略号 */
}
.c1 .price_num {
height: 30px; /* 设置固定高度 */
overflow: hidden; /* 隐藏超出尺寸的内容 */
white-space: nowrap; /* 确保内容不会换行 */
text-overflow: ellipsis; /* 超出内容显示省略号 */
}
.num{
width: 25%;
float: left;
display: flex;
align-items: center;
justify-content: center;
color: gold;
font-size: 9px;
margin-top: 25px;
}
.price_num{
width: 25%;
float: left;
display: flex;
align-items: center;
justify-content: center;
color: gold;
font-size: 9px;
margin-top: 25px;
}
.c1 .num1 {
height: 25px; /* 设置固定高度 */
overflow: hidden; /* 隐藏超出尺寸的内容 */
white-space: nowrap; /* 确保内容不会换行 */
text-overflow: ellipsis; /* 超出内容显示省略号 */
}
.c1 .num1 h1 {
position: absolute;
white-space: nowrap;
animation: scrollLeft 3s linear infinite; /* 5秒内完成一次滚动,线性变化,无限次循环 */
}
.c1 .price_num1 h1 {
position: absolute;
white-space: nowrap;
animation: scrollLeft 3s linear infinite; /* 5秒内完成一次滚动,线性变化,无限次循环 */
}
@keyframes scrollLeft {
0% {
transform: translateX(0%);
}
100% {
transform: translateX(-0%);
}
}
/* 使用 :hover 伪类来暂停滚动 */
.c1 .num1:hover h1 {
animation-play-state: paused;
}
.num1{
width: 25%;
float: left;
display: flex;
align-items: center;
justify-content: center;
color: gold;
font-size: 9px;
margin-top: 25px;
}
.txt{
width: 25%;
float: left;
display: flex;
align-items: center;
justify-content: center;
font-family: "幼圆";
color: whitesmoke;
font-size: 14px;
margin-top: 20px; /* 可以根据需要调整距离 */
}
.c2{
position: absolute;
width: 40%;
height: 70%;
top: 25%;
left: 30%;
background-color:transparent;
}
.c22{
position: absolute;
width: 40%;
height: 70%;
top: 30%;
left: 30%;
background-color:transparent;
}
.map_con{
position: absolute;
width: 40%;
height:600px;
top: 10%;
left: 30%;
}
.r1{
position: absolute;
width: 30%;
height: 45%;
top: 10%;
right: 0;
/* background-color: burlywood; */
}
.r2{
position: absolute;
width: 30%;
height: 45%;
top: 55%;
right: 0;
/* background-color: brown; */
}
.tim{
position: absolute;
/* width: 30%; */
height: 10%;
top: 5%;
right: 2%;
/* background-color: blueviolet; */
font-size: 20px;
color: whitesmoke;
}
/* 容器样式 */
.but {
position: absolute;
width: 45%;
top: 96%;
left: 35%;
}
/* 按钮样式 */
.bu {
padding: 5px 5px; /* 内边距 */
margin-left: 2px;
font-size: 12px; /* 字体大小 */
color: white; /* 文字颜色 */
background-color: #100c2a; /* 背景颜色 rgba(14, 148, 234, 0.2)*/
border: 1px solid #0E94EA; /* 边框 */
border-radius: 5px; /* 圆角边框 */
cursor: pointer; /* 鼠标悬停时显示指针手型 */
outline: none; /* 点击时无轮廓线 */
}
/* 鼠标悬停时的按钮样式 */
.bu:hover {
background-color: rgba(14, 148, 234, 0.2); /* 鼠标悬停时的背景颜色 */
}
select{
position:relative;
margin-top: 420px;
}
其他css如下:
第十,部署应用。将Flask应用部署到服务器上,确保MySQL数据库可被应用访问。接着打开浏览器,访问部署好的Flask应用,前端HTML页面会显示根据ECharts绘制的图表,展示MySQL数据库中的数据。