2020年新冠状病毒可视化实战
一，爬取数据：
爬取网站：https://ncov.dxy.cn/ncovh5/view/pneumonia?mibrowser_back=0&share=0&source=xiaomi03
爬取代码（含省级市级数据）
import requests
import re
import time
from lxml import etree
import xlwt
url = 'https://ncov.dxy.cn/ncovh5/view/pneumonia?mibrowser_back=0&share=0&source=xiaomi03'
headers = {
"user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36"
}
def get_page(url):
try:
response = requests.get(url, headers=headers)
response.encoding = response.apparent_encoding
return response.text
except requests.ConnectionError as e:
print('Error:', e.args)
def get_info(page):
item = re.findall('try { window.getAreaStat = (.*?)}catch\(e\)', page, re.S)
import json
item1 = json.loads(item[0])
print(item[0])
f = xlwt.Workbook(encoding='utf-8')
sheet01 = f.add_sheet(u'sheet1', cell_overwrite_ok=True)
sheet01.write(0, 0, '省份名称') # 第一行第一列
sheet01.write(0, 1, '省份简称或城市名称')
sheet01.write(0, 2, '累计确诊人数')
sheet01.write(0, 3, '现存人数')
sheet01.write(0, 4, '治愈人数')
sheet01.write(0, 5, '死亡人数')
sheet01.write(0, 6, '地区ID编码')
i = 0
for json in item1:
data = {}
data1 = {}
data['provincename'] = json['provinceName']
data['provinceshortName'] = json['provinceShortName']
data['p_confirmedcount'] = json['confirmedCount']
data['currentConfirmedCount'] = json['currentConfirmedCount']
data['p_curedcount'] = json['curedCount']
data['p_deadcount'] = json['deadCount']
data['p_locationid'] = json['locationId']
sheet01.write(i + 1, 0, data['provincename'])
sheet01.write(i + 1, 1, data['provinceshortName'])
sheet01.write(i + 1, 2, data['p_confirmedcount'])
sheet01.write(i + 1, 3, data['currentConfirmedCount'])
sheet01.write(i + 1, 4, data['p_curedcount'])
sheet01.write(i + 1, 5, data['p_deadcount'])
sheet01.write(i + 1, 6, data['p_locationid'])
i += 1
for citiy_data in json['cities']:
data1['cityname'] = citiy_data['cityName']
data1['c_confirmedcount'] = citiy_data['confirmedCount']
data1['currentConfirmedCount'] = citiy_data['currentConfirmedCount']
data1['c_curedcount'] = citiy_data['curedCount']
data1['c_deadcount'] = citiy_data['deadCount']
data1['c_locationid'] = citiy_data['locationId']
sheet01.write(i + 1, 1, data1['cityname'])
sheet01.write(i + 1, 2, data1['c_confirmedcount'])
sheet01.write(i + 1, 3, data1['currentConfirmedCount'])
sheet01.write(i + 1, 4, data1['c_curedcount'])
sheet01.write(i + 1, 5, data1['c_deadcount'])
sheet01.write(i + 1, 6, data1['c_locationid'])
i += 1
print('p', end='')
current_time = time.strftime("%Y-%m-%d-%H-%M-%S", time.localtime())
f.save('shiji.xls' )
page = get_page(url)
get_info(page)
二，清洗入库：通过mysql的导入向导进行导入
三，数据可视化（含地图下钻到市）
jsp代码：
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html style="height: 100%">
<head>
<meta charset="UTF-8">
<title>疫情地图</title>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/echarts/dist/echarts.min.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/echarts/dist/extension/dataTool.min.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/echarts/map/js/china.js"></script>
<script type="text/javascript" src="js/jquery-3.3.1.js"></script>
</head>
<body style="height: 100%; margin: 0">
<div id="ditu" style="height:100%"></div>
<script type="text/javascript">
$(function() {
$.get('${pageContext.request.contextPath}/servlet?method=search_shi',
function(data) {
//alert("123")
init(data);
// alert("1234")
//alert(data);
});
});
function init(data) {
alert(data)
var allData=data;
var dom = document.getElementById("ditu");
var myChart = echarts.init(dom);
var app = {};
option = null;
//各省份的地图json文件
var provinces = {
'上海': 'ss/data-1482909900836-H1BC_1WHg.json',
'河北': 'ss/data-1482909799572-Hkgu_yWSg.json',
'山西': 'ss/data-1482909909703-SyCA_JbSg.json',
'内蒙古': 'ss/data-1482909841923-rkqqdyZSe.json',
'辽宁': 'ss/data-1482909836074-rJV9O1-Hg.json',
'吉林': 'ss/data-1482909832739-rJ-cdy-Hx.json',
'黑龙江': 'ss/data-1482909803892-Hy4__J-Sx.json',
'江苏': 'ss/data-1482909823260-HkDtOJZBx.json',
'浙江': 'ss/data-1482909960637-rkZMYkZBx.json',
'安徽': 'ss/data-1482909768458-HJlU_yWBe.json',
'福建': 'ss/data-1478782908884-B1H6yezWe.json',
'江西': 'ss/data-1482909827542-r12YOJWHe.json',
'山东': 'ss/data-1482909892121-BJ3auk-Se.json',
'河南': 'ss/data-1482909807135-SJPudkWre.json',
'湖北': 'ss/data-1482909813213-Hy6u_kbrl.json',
'湖南': 'ss/data-1482909818685-H17FOkZSl.json',
'广东': 'ss/data-1482909784051-BJgwuy-Sl.json',
'广西': 'ss/data-1482909787648-SyEPuJbSg.json',
'海南': 'ss/data-1482909796480-H12P_J-Bg.json',
'四川': 'ss/data-1482909931094-H17eKk-rg.json',
'贵州': 'ss/data-1482909791334-Bkwvd1bBe.json',
'云南': 'ss/data-1482909957601-HkA-FyWSx.json',
'西藏': 'ss/data-1482927407942-SkOV6Qbrl.json',
'陕西': 'ss/data-1482909918961-BJw1FyZHg.json',
'甘肃': 'ss/data-1482909780863-r1aIdyWHl.json',
'青海': 'ss/data-1482909853618-B1IiOyZSl.json',
'宁夏': 'ss/data-1482909848690-HJWiuy-Bg.json',
'新疆': 'ss/data-1482909952731-B1YZKkbBx.json',
'北京': 'ss/data-1482818963027-Hko9SKJrg.json',
'天津': 'ss/data-1482909944620-r1-WKyWHg.json',
'重庆': 'ss/data-1482909775470-HJDIdk-Se.json',
'香港': 'ss/data-1461584707906-r1hSmtsx.json',
'澳门': 'ss/data-1482909771696-ByVIdJWBx.json'
};
//各省份的数据
/* var allData = [{
name: '北京'
}, {
name: '天津'
}, {
name: '上海'
},{
name: '重庆',
value: 75
},{
name: '河北'
}, {
name: '河南'
}, {
name: '云南'
}, {
name: '辽宁'
}, {
name: '黑龙江'
}, {
name: '湖南'
}, {
name: '安徽'
}, {
name: '山东'
}, {
name: '新疆'
}, {
name: '江苏'
}, {
name: '浙江'
}, {
name: '江西'
}, {
name: '湖北'
}, {
name: '广西'
}, {
name: '甘肃'
}, {
name: '山西'
}, {
name: '内蒙古'
}, {
name: '陕西'
}, {
name: '吉林'
}, {
name: '福建'
}, {
name: '贵州'
}, {
name: '广东'
}, {
name: '青海'
}, {
name: '西藏'
}, {
name: '四川'
}, {
name: '宁夏'
}, {
name: '海南'
}, {
name: '台湾'
}, {
name: '香港'
}, {
name: '澳门'
}]; */
/* for (var i = 0; i < allData.length; i++) {
allData[i].value = Math.round(Math.random() * 100);
} */
loadMap('ss/data-1527045631990-r1dZ0IM1X.json', 'china');//初始化全国地图
var timeFn = null;
//单击切换到省级地图，当mapCode有值,说明可以切换到下级地图
myChart.on('click', function(params) {
clearTimeout(timeFn);
//由于单击事件和双击事件冲突，故单击的响应事件延迟250毫秒执行
timeFn = setTimeout(function() {
var name = params.name; //地区name
var mapCode = provinces[name]; //地区的json数据
if (!mapCode) {
alert('无此区域地图显示');
return;
}
loadMap(mapCode, name);
}, 250);
});
// 绑定双击事件，返回全国地图
myChart.on('dblclick', function(params) {
//当双击事件发生时，清除单击事件，仅响应双击事件
clearTimeout(timeFn);
//返回全国地图
loadMap('ss/data-1527045631990-r1dZ0IM1X.json', 'china');
});
/**
获取对应的json地图数据，然后向echarts注册该区域的地图，最后加载地图信息
@params {String} mapCode:json数据的地址
@params {String} name: 地图名称
*/
function loadMap(mapCode, name) {
$.get(mapCode, function(data) {
if (data) {
echarts.registerMap(name, data);
var option = {
tooltip: {
show: true,
formatter: function(params) {
if (params.data) return params.name + '确诊：' + params.data['value']
},
},
visualMap: {
type: 'continuous',
text: ['', ''],
showLabel: true,
left: '50',
min: 0,
max: 100,
inRange: {
color: ['#edfbfb', '#b7d6f3', '#40a9ed', '#3598c1', '#215096', ]
},
splitNumber: 0
},
series: [{
name: 'MAP',
type: 'map',
mapType: name,
selectedMode: 'false',//是否允许选中多个区域
label: {
normal: {
show: true
},
emphasis: {
show: true
}
},
data: allData
}]
};
myChart.setOption(option);
// curMap = {
// mapCode: mapCode,
// mapName: name
// };
} else {
alert('无法加载该地图');
}
});
};
if (option && typeof option === "object") {
myChart.setOption(option, true);
}
}
</script>
</body>
</html>
bean层代码：
package Bean;
public class bean_shi {
private String shi;
public String getShi() {
return shi;
}
public void setShi(String shi) {
this.shi = shi;
}
public int getQue_num() {
return que_num;
}
public void setQue_num(int que_num) {
this.que_num = que_num;
}
private int que_num;
public bean_shi(String shi,int que_num) {
this.shi=shi;
this.que_num=que_num;
}
}
dao层：
package Dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import Bean.bean_shengfen;
import Bean.bean_shi;
import Util.DBUtil;
public class dao {
public static ArrayList<bean_shengfen> select2_14()
{
ArrayList<bean_shengfen> bean2_14=new ArrayList<bean_shengfen>();
String sql="select * from quanguoyiqing where data= '" + "2月14日" +"'";
System.out.println(sql);
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
bean_shengfen userBean = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
String shengfen = rs.getString("shengfen");
int que_num = rs.getInt("que_num");
int si_num =rs.getInt("si_num");
int yu_num =rs.getInt("yu_num");
//System.out.println("123");
userBean = new bean_shengfen(shengfen,que_num,si_num,yu_num);
// System.out.println(departure_city+","+landing_city+","+flight_schedules+","+airlines+","+aircraft_models+","+departure_time+","+landing_time+","+departure_airport+","+landing_airport+","+punctuality_rate+","+average_delayed);
// Gson gson = new Gson();
// System.out.println(gson.toJson(userBean));
bean2_14.add(userBean);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return bean2_14;
}
public static ArrayList<bean_shi> select_shi()
{
ArrayList<bean_shi> bean_shi=new ArrayList<bean_shi>();
String sql="select * from yiqing_shi";
System.out.println(sql);
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
bean_shi userBean = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
String shi = rs.getString("shi");
int que_num = rs.getInt("que_num");
//System.out.println("123");
userBean = new bean_shi(shi,que_num);
// System.out.println(departure_city+","+landing_city+","+flight_schedules+","+airlines+","+aircraft_models+","+departure_time+","+landing_time+","+departure_airport+","+landing_airport+","+punctuality_rate+","+average_delayed);
// Gson gson = new Gson();
// System.out.println(gson.toJson(userBean));
bean_shi.add(userBean);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return bean_shi;
}
public static List<bean_shengfen> tongji(){
List<bean_shengfen> list =new ArrayList<bean_shengfen>();
try {
Connection conn = DBUtil.getConn();
String sql = "select data,sum(que_num)as total from quanguoyiqing group by data order by total";
// 获取Statement
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String data = resultSet.getString("data");
int total = resultSet.getInt("total");
bean_shengfen bs = new bean_shengfen(data,total);
list.add(bs);
}
resultSet.close();
statement.close();
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
return list;
}
}
servlet层：
package Servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.gson.Gson;
import Bean.bean_shengfen;
import Bean.bean_shi;
import Dao.dao;
/**
* Servlet implementation class servlet
*/
@WebServlet("/servlet")
public class servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public servlet() {
super();
// TODO Auto-generated constructor stub
}
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
req.setCharacterEncoding("utf-8");
String method = req.getParameter("method");
if ("search".equals(method)) {
search(req, resp);
} else if("search_shi".equals(method)) {
search_shi(req, resp);
} else if("tongji".equals(method)) {
tongji(req, resp);
}
}
private void tongji(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
request.setCharacterEncoding("utf-8");
List<bean_shengfen> list2 = dao.tongji();
Gson gson2 = new Gson();
String json = gson2.toJson(list2);
System.out.println("统计+"+json);
// System.out.println(json.parse);
response.setContentType("text/html; charset=utf-8");
response.getWriter().write(json);
}
private void search(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
//HttpSession session = req.getSession();
List<bean_shengfen> bean2_14 = dao.select2_14();
JSONArray total = new JSONArray();
JSONArray xizang = new JSONArray();
JSONArray data = new JSONArray();
System.out.println(bean2_14.size());
for(int i =0;i<bean2_14.size();i++){
JSONObject name1 = new JSONObject();
//JSONObject name2 = new JSONObject();
name1.put("name", bean2_14.get(i).getShengfen());
name1.put("value", bean2_14.get(i).getQue_num());
data.add(name1);
}
System.out.println(data);
System.out.println(data.toString());
resp.setContentType("application/json");
resp.setCharacterEncoding("utf-8");
resp.getWriter().write(data.toString());
}
private void search_shi(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
//HttpSession session = req.getSession();
List<bean_shi> bean_shi = dao.select_shi();
// JSONArray total = new JSONArray();
//JSONArray xizang = new JSONArray();
JSONArray data = new JSONArray();
System.out.println(bean_shi.size());
for(int i =0;i<bean_shi.size();i++){
JSONObject name1 = new JSONObject();
//JSONObject name2 = new JSONObject();
name1.put("name", bean_shi.get(i).getShi());
name1.put("value", bean_shi.get(i).getQue_num());
data.add(name1);
}
System.out.println(data);
System.out.println(data.toString());
resp.setContentType("application/json");
resp.setCharacterEncoding("utf-8");
resp.getWriter().write(data.toString());
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
util层：
package Util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 鏁版嵁搴撶殑宸ュ叿绫?
* @author zm
*
*/
public class DBUtil {
//eshop涓烘暟鎹簱鍚嶇О锛宒b_user涓烘暟鎹簱鐢ㄦ埛鍚峝b_password涓烘暟鎹簱瀵嗙爜
public static String db_url = "jdbc:mysql://localhost:3306/yiqing?characterEncoding=utf8&useSSL=true";//jdbc:mysql://localhost:3306/eshop?useUnicode=true&characterEncoding=UTF-8
public static String db_user = "root";
public static String db_password = "";
public static Connection getConn() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(db_url, db_user, db_password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void close(Statement state, Connection conn) {
if(state!=null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs, Statement state, Connection conn) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(state!=null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
效果展示：
注：此代码需要引用全国各省份地图json文件，以及echarts和jquery的js文件
