北京市政百姓信件分析实战

目录

• 北京市政百姓信件分析实战
  • 1.爬虫
  • 2.数据清洗
  • 3.导入数据
  • 4.导出到本地mysql
  • 5.可视化展示

北京市政百姓信件分析实战

1.爬虫

import json
import requests
import demjson
from bs4 import BeautifulSoup
import jieba
import jieba.analyse as analyse
import csv

kv = {
    'Host': 'www.beijing.gov.cn',
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:71.0) Gecko/20100101 Firefox/71.0',
    'Accept': 'application/json, text/javascript, */*; q=0.01',
    'Accept-Language': 'zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2',
    'Accept-Encoding': 'gzip, deflate',
    'Content-Type': 'text/json',
    'X-Requested-With': 'XMLHttpRequest',
    'Content-Length': '155',
    'Origin': 'http://www.beijing.gov.cn',
    'Connection': 'keep-alive',
    'Referer': 'http://www.beijing.gov.cn/hudong/hdjl/'}
kv1 = {'user-agent': 'Mozilla/5.0'}

if __name__ == "__main__":
    page=1
    query = {
    }
    datas = json.dumps(query)

    while page<175:
        print(page)
        url = "https://www.beijing.gov.cn/hudong/hdjl/sindex/bjah-index-hdjl!replyLetterListJson.action?page.pageNo="+str(page)+"&page.pageSize=6&orgtitleLength=26"
        r = requests.post(url, data=datas, headers=kv)
        rr = demjson.decode(r.text);
        for item in rr["result"]:

            originalId=item["originalId"]#编号
            letterTypeName=item["letterTypeName"]#信件类型

            #咨询
            url1 = "http://www.beijing.gov.cn/hudong/hdjl/com.web.consult.consultDetail.flow?originalId="+originalId
            #建议
            url2 = "http://www.beijing.gov.cn/hudong/hdjl/com.web.suggest.suggesDetail.flow?originalId="+originalId

            if letterTypeName=="咨询":
                r1 = requests.get(url1, headers=kv1)
            else:
                r1 = requests.get(url2, headers=kv1)

            demo = r1.text
            soup = BeautifulSoup(demo, "html.parser")
            title=soup.find("strong").get_text().replace("\n","")
            fromPeople=soup.find_all("div", {"class": "col-xs-10 col-lg-3 col-sm-3 col-md-4 text-muted"})[0].get_text().lstrip('来信人：').lstrip().rstrip()
            fromTime=soup.find_all("div", {"class": "col-xs-5 col-lg-3 col-sm-3 col-md-3 text-muted"})[0].get_text().lstrip('时间：')
            problem=soup.find_all("div", {"class": "col-xs-12 col-md-12 column p-2 text-muted mx-2"})[0].get_text().lstrip().rstrip().replace("\r","").replace("\n","")
            office=soup.find_all("div", {"class": "col-xs-9 col-sm-7 col-md-5 o-font4 my-2"})[0].get_text().replace("\n","")
            answerTime=soup.find_all("div", {"class": "col-xs-12 col-sm-3 col-md-3 my-2"})[0].get_text().lstrip('答复时间：')
            answer=soup.find_all("div", {"class": "col-xs-12 col-md-12 column p-4 text-muted my-3"})[0].get_text().lstrip().rstrip().replace("\n","").replace("\r","")

            #json格式的导进去对不上
            # item1={
            #     'originalId':originalId,
            #     'letterTypeName':letterTypeName,
            #     'title':title,
            #     'fromPeople':fromPeople,
            #     'fromTime':fromTime,
            #     'problem':problem,
            #     'office':office,
            #     'answerTime':answerTime,
            #     'answer':answer
            # }
            # itemm=str(item1)

            #全部爬下来的
            itemm=originalId+"|"+letterTypeName+"|"+title+"|"+fromPeople+"|"+fromTime+"|"+problem+"|"+office+"|"+answerTime+"|"+answer

            fp = open("yijian.txt", 'a', encoding='utf-8')
            fp.write(itemm + '\n')
            fp.close()

        page=page+1

2.数据清洗

爬取下来的txt文件内容如下：

然后需要对txt文件进行清洗，这里由于爬下来的数据比较规范，所以不采用MapReduce进行清洗，选择使用csv导入分隔符由图可知选择使用‘|’，导入后会自动划分数据，如下图所示：

3.导入数据

然后将test.csv上传到Hadoop的指定目录，这里我上传到了/export/data/test.csv下，然后启动Hadoop、hive、beeline、zookeeper集群等，启动完成后在beeline里创建hive数据库：

create datebase edu3;

然后跳转到要使用的数据库并创建汇总表，用于将所有数据储存：

create table govdata(
leixing string,
biaoti string, 

laixinren string,
shijian string,  

number string, 
problem string, 
offic string,  
officpt string,  
officp string)
row format delimited  
fields terminated by ',';

然后将刚才上传的csv文件导入到表里（这里要记得要在终端使用命令，不要在beeline里使用）：

load data local inpath '/export/data/test.csv' into table govdata;

可以用一下命令检查一下输出信息对不对，应该是可以变成理想的输出信息：

select substr(shijian,0,4) as dt,
count(1) as num from govdata group by substr(shijian,0,4);  

select leixing,
count(1) as num from govdata group by leixing order by num desc;

select govdata.offic,  
count(1) as num from govdata group by offic order by num desc;  

验证完输出信息后创建第一个表来储存第一张图，并将信息保存到这个表里：

create table lenum(  
year string,  
num int  
 )  
row format delimited  
fields terminated by ','  
stored as textfile;  

insert into table lenum  
select  
substr(shijian,0,4) as dt,  
count(1) as num  
from govdata  
group by substr(shijian,0,4)  
order by num;

然后是第二张表：

create table typenums(  
leixing string,  
num int  
)  
row format delimited  
fields terminated by ','  
stored as textfile;

insert into typenums 
select  
leixing,  
count(1) as num  
from govdata  
group by leixing  
order by num desc;

第三张表：

create table govnums(  
govname string,  
num int  
)  
row format delimited  
fields terminated by ','  
stored as textfile;

insert into govnums 
select  
govdata.offic,  
count(1) as num  
from govdata  
group by offic 
order by num desc;

最后可以执行数据库查询语句查看信息是否正确保存：

select * from 表名 limit 10;

4.导出到本地mysql

这里的导出使用sqoop来进行导出操作，直接在终端执行命令即可。

这里要记得要提前在本地数据库建好对应的表，表的数据类型要一一对应。

第一张表：

sqoop export \
--connect jdbc:mysql://47.92.246.30:3306/edu4out?characterEncoding=UTF-8 \
--username root \
--password Lht+2023kybs=v. \
--table lenumsql \
--export-dir /user/hive/warehouse/edu3.db/lenum/000000_0 \
--input-fields-terminated-by ','; 

第二张表：

sqoop export \
--connect jdbc:mysql://47.92.246.30:3306/edu4out?characterEncoding=UTF-8 \
--username root \
--password Lht+2023kybs=v. \
--table typenum \
--export-dir /user/hive/warehouse/edu3.db/typenums/000000_0 \
--input-fields-terminated-by ','; 

第三张表：

sqoop export \
--connect jdbc:mysql://47.92.246.30:3306/edu4out?characterEncoding=UTF-8 \
--username root \
--password Lht+2023kybs=v. \
--table govnum \
--export-dir /user/hive/warehouse/edu3.db/govnums/000000_0 \
--input-fields-terminated-by ','; 

汇总表：

sqoop export \
--connect jdbc:mysql://47.92.246.30:3306/edu4out?characterEncoding=UTF-8 \
--username root \
--password Lht+2023kybs=v. \
--table govdata \
--export-dir /user/hive/warehouse/edu3.db/govdata/test.csv \
--input-fields-terminated-by ','; 

5.可视化展示

这里采用springboot架构来进行设计，后端代码不再展示，仅展示前端代码，后端只需要查询数据库所有信息并传输给前端即可，记得导入vue.js、axios.js、echarts.js。

<!doctype html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>欢迎来到三刘一陈的网站</title>
    <!-- Favicon -->
    <link rel="shortcut icon" th:href="@{images/favicon.ico}"/>
    <!-- Bootstrap CSS -->
    <link rel="stylesheet" th:href="@{css/bootstrap.min.css}">
    <!-- Typography CSS -->
    <link rel="stylesheet" th:href="@{css/typography.css}">
    <!-- Style CSS -->
    <link rel="stylesheet" th:href="@{css/style.css}">
    <!-- Responsive CSS -->
    <link rel="stylesheet" th:href="@{css/responsive.css}">
    <script src="https://cdn.staticfile.org/echarts/4.3.0/echarts.min.js"></script>

</head>
<body class="sidebar-main-active right-column-fixed header-top-bgcolor">
<!-- loader Start -->
<div id="loading">
    <div id="loading-center">
    </div>
</div>
<!-- loader END -->
<!-- Wrapper Start -->
<div class="wrapper" id="app">
    <!-- Sidebar  -->
    <!--<div th:replace="~{commons/commons::sidebar}"></div>-->
    <!-- TOP Nav Bar -->
    <!--    <div th:replace="~{commons/commons::topnavbar}"></div>-->
    <!-- TOP Nav Bar END -->

    <!-- Page Content  -->
    <div >


        <div class="container-fluid">
            <div class="row">
                <div class="col-md-6 col-lg-7">
                    <div class="iq-card iq-card-block iq-card-stretch iq-card-height overflow-hidden">
                        <div class="iq-card-body p-0">
                            <div >
                                <div id="main1" style="width: 850px;height:700px;"></div>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="col-md-6 col-lg-5">
                    <div class="row">
                        <div class="iq-card iq-card-block iq-card-stretch iq-card-height">
                            <div class="iq-card-body p-0">
                                <div >
                                    <div id="main2" style="width: 675px;height:350px;"></div>
                                </div>
                            </div>
                        </div>


                        <div class="iq-card iq-card-block iq-card-stretch iq-card-height">
                            <div class="iq-card-body p-0">
                                <div >
                                    <div id="main3" style="width: 675px;height:350px;"></div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Wrapper END -->
<!-- Footer -->
<!--<div th:replace="~{commons/commons::footer}"></div>-->
<!-- Footer END -->
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<div th:replace="~{commons/commons::js}"></div>

<script th:src="@{js/vue.js}"></script>
<script th:src="@{js/axios.js}"></script>

<script>
    new Vue({


        el: "#app",
        mounted() {

            var myChart1 = echarts.init(document.getElementById('main1'));
            var myChart2 = echarts.init(document.getElementById('main2'));
            var myChart3 = echarts.init(document.getElementById('main3'));


            // 绘制图表1
            var option1=({

                title: {text: '柱状图'},    //{text: 'ECharts入门示例'},
                xAxis: {
                    type: 'category',
                    data: ''
                },
                yAxis: {
                    type: 'value'
                },
                series:[
                    {
                        data: '',
                        type: 'bar',
                        showBackground: true,
                        backgroundStyle: {
                            color: 'rgb(2,145,248)'
                        }
                    }
                ]
            });
            // 绘制图表2
            var option2=({
                title: {text: '折线图'},
                xAxis: {
                    type: 'category',
                    data: ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
                },
                yAxis: {
                    type: 'value'
                },
                series: [
                    {
                        data: [820, 932, 901, 934, 1290, 1330, 1320],
                        type: 'line',
                        smooth: true
                    }
                ]
            });
            // 绘制图表3
            var option3=({
                title: {text: '饼图'},
                tooltip: {
                    trigger: 'item'
                },
                legend: {
                    top: '5%',
                    left: 'center'
                },
                series: [
                    {
                        data: [
                            { value: 1048, name: 'Search Engine' },
                            { value: 735, name: 'Direct' },
                            { value: 580, name: 'Email' },
                            { value: 484, name: 'Union Ads' },
                            { value: 300, name: 'Video Ads' }
                        ],
                        name: 'Access From',
                        type: 'pie',
                        radius: ['40%', '70%'],
                        avoidLabelOverlap: false,
                        itemStyle: {
                            borderRadius: 10,
                            borderColor: '#fff',
                            borderWidth: 2
                        },
                        label: {
                            show: false,
                            position: 'center'
                        },
                        emphasis: {
                            label: {
                                show: true,
                                fontSize: 40,
                                fontWeight: 'bold'
                            }
                        },
                        labelLine: {
                            show: false
                        },

                    }
                ]
            });

            axios.get('http://localhost:8080/gov/queryGov').then(resp => {
                var govname=[];
                for(var i=0;i<resp.data.length;i++){
                    govname.push(resp.data[i].govname);
                }
                console.log(govname);
                option1.xAxis.data=govname;

                var num=[];
                for(var i=0;i<resp.data.length;i++){
                    num.push(resp.data[i].num);
                }
                console.log(num);
                option1.series[0].data=num;

                myChart1.setOption(option1);
            });

            axios.get('http://localhost:8080/le/queryLe').then(resp => {
                var time=[];
                for(var i=0;i<resp.data.length;i++){
                    time.push(resp.data[i].time);
                }
                console.log(time);
                option2.xAxis.data=time;

                var number=[];
                for(var i=0;i<resp.data.length;i++){
                    number.push(resp.data[i].number);
                }
                console.log(number);
                option2.series[0].data=number;

                myChart2.setOption(option2);
            });

            axios.get('http://localhost:8080/type/queryType').then(resp => {
                var leixing=[];
                // for(var i=0;i<resp.data.length;i++){
                //     leixing.push(resp.data[i].leixing);
                // }
                // option3.series[0].data[1].name=leixing;
                //
                // var num=[];
                // for(var i=0;i<resp.data.length;i++){
                //     num.push(resp.data[i].num);
                // }
                // console.log(num);
                // option3.series[0].data[0].value=num;

                //{ value: 1048, name: 'Search Engine' }
                option3.series[0].data=[];
                for(var i=0;i<resp.data.length;i++){
                    let str='{ value:'+resp.data[i].num+',name:"'+ resp.data[i].leixing +'"}';
                    // let str='{ value:333,name:"aaa" }';
                    option3.series[0].data.push(eval('('+str+')'));
                    console.log(eval('('+str+')'));
                }


                myChart3.setOption(option3);
            });

        },
    })

</script>

</body>
</html>

最终界面展示如下：