开学测试——电子商务大数据分析

标签：数据分析 comment 电子商务 string -- int 评论 table 开学

一、测试要求：

1、 数据采集（要求至少爬取三千条记录，时间跨度超过一星期）：（10分）

要求Python 编写程序爬取京东手机的评论数据，生成Json形式的数据文件。

京东商城部分数据格式如下图所示：

1.    "productCommentSummary": {  
2.    "goodRateShow": 95, //好评率  
3.    "poorRateShow": 3, //差评率  
4.    "poorCountStr": "1900+",  
5.    "averageScore": 5, //平均分  
6.    "generalCountStr": "1600+",  
7.    "oneYear": 0,  
8.    "showCount": 21000,  
9.    "showCountStr": "2.1万+",  
10.    "goodCount": 64000, //好评数  
11.    "generalRate": 0.024,  
12.    "generalCount": 1600, //中评数  
13.    "skuId": 4432058,  
14.    "goodCountStr": "6.4万+",  
15.    "poorRate": 0.028,  
16.    "afterCount": 2400,  
17.    "goodRateStyle": 142,  
18.    "poorCount": 1900, //差评数  
19.    "skuIds": null,  
20.    "poorRateStyle": 4,  
21.    "generalRateStyle": 4,  
22.    "commentCountStr": "6.8万+",  
23.    "commentCount": 68000,  
24.    "productId": 4432058, //商品id  
25.    "afterCountStr": "2400+",  
26.    "goodRate": 0.948,  
27.    "generalRateShow": 2 //中评率  
28.    },  
comments中一条评论的结构：

1.    “id": 10432588299,  
2.    “guid": "6c1d83b1-ac45-4189-a041-774eaff87df9",  
3.    “content": "割手，相当的割手，无语了", //评论内容 √  
4.    “creationTime": "2017-05-22 23:37:24", //写评论的时间 √  
5.    “isTop": false,                        //是否置顶  
6.    “referenceTime": "2017-05-20 18:35:11", //收货时间 √  
7.    “firstCategory": 9987,                 //第一分类 √  
8.    “secondCategory": 653,                 //第二分类 √  
9.    “thirdCategory": 655,                  //第三分类 √  
10.    “replyCount": 0,  
11.    “score": 3,                            //打分 √  
12.    “nickname": "j***柜",                  //昵称  √  
13.    “userClient": 2,  
14.    “productColor": "碳黑色",  
15.    “productSize": "32GB",  
16.    “userLevelName": "金牌会员",           //会员级别 √  
17.    “plusAvailable": 0,  
18.    “productSales": [  
19.       {  
20.           "dim": 3,  
21.           "saleName": "选择套装",  
22.           "saleValue": "官方标配"  
23.       }  
24.    ,  
25.    “userClientShow": "来自京东iPhone客户端",//评论设备  
26.    “isMobile": true,                       //是否移动端  
27.    “days": 2,                              //评论时间距【收货/下单】时间多长时间  
28.    “afterDays": 0  

使用python进行数据爬取，在爬取数据的同时进行数据清洗

import requests
import json
import csv
from lxml import etree
from bs4 import BeautifulSoup
import time

comment_url = 'https://club.jd.com/comment/productPageComments.action?callback'

# 获取评论
def get_comment(productid, name):
    headers = {
        # 'cookie': 'shshshfpa=4e6c0f90-587c-a46f-5880-a7debd7d4393-1544616560; __jdu=1126324296; PCSYCityID=412; user-key=44089d07-befa-4522-87fc-bcc039ec7045; pinId=qopcdCj6kcR3U84v0KTTbrV9-x-f3wj7; pin=jd_769791719e9e9; unick=jd_769791719e9e9; _tp=nc%2FbpB%2BkeSbk3jZ6p2H0FlWrdUa1gbgi16QiQ7NBXKY%3D; _pst=jd_769791719e9e9; cn=9; ipLoc-djd=1-72-2799-0; mt_xid=V2_52007VwMSUVpaUV8cQR5sUWMDEgUIUVBGGEofWhliABNUQQtQWkpVHVVXb1ZGB1lYW11LeRpdBW4fElFBW1VLH0ESXgJsAhpiX2hSahxLGFsFZwcRUG1bWlo%3D; shshshfpb=bRnqa4s886i2OeHTTR9Nq6g%3D%3D; unpl=V2_ZzNtbUZTSxJ3DURTLk0LAmJXFVlKAkdAIQ1PUXseCVIzU0UKclRCFXwURldnGlUUZwcZXERcQRdFCHZXchBYAWcCGllyBBNNIEwHDCRSBUE3XHxcFVUWF3RaTwEoSVoAYwtBDkZUFBYhW0IAKElVVTUFR21yVEMldQl2VHsaWwdkBhFVRWdzEkU4dl17HVwDYDMTbUNnAUEpAUJRfRpcSGcDEVpAVEYWfQ92VUsa; __jda=122270672.1126324296.1544405080.1545968922.1545980857.16; __jdc=122270672; ceshi3.com=000; TrackID=11EpDXYHaqwJE15W6paeMk_GMm05o3NUUeze9XyIcFs33GGxX8knxMpxWTeID75qSiUlj31s8CtKJs4hJUV-7CvKuiOEyDd8bvOCH7zzigeI; __jdv=122270672|baidu-pinzhuan|t_288551095_baidupinzhuan|cpc|0f3d30c8dba7459bb52f2eb5eba8ac7d_0_55963436def64e659d5de48416dfeaff|1545980984854; 3AB9D23F7A4B3C9B=OA3G4SO3KYLQB6H3AIX36QQAW34BF376WJN66IUPEQAG6FUA2NWGM6R6MBDL32HLDG62WL2FICMYIVMOU6ISUWHKPE; shshshfp=1ed96ad08a7585648cd5017583df22bd; _gcl_au=1.1.162218981.1545981094; JSESSIONID=305879A97D4EA21F4D5C4207BB81423F.s1; shshshsID=c8c51ee0c5b1ddada7c5544abc3eea8a_5_1545981289039; __jdb=122270672.11.1126324296|16.1545980857; thor=3A30EBABA844934A836AC9AA37D0F4B85306071BD7FC64831E361A626E76F6977EC7794D06F2A922AEABF7D3D7DC22FBE2EB6B240F81A13F5A609368D4185BA0081D7C34A93760063D2F058F5B916835B4960EC8A9122008745971D812BA9E4AE48542CCC5A42E5CD786CC93770E520E36F950614C06A7EB05C8E1DD93EEA844B2EBA9B0136063FCFB6B7C83AECA828774041A9FED7BD98496689496122822FF',
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36",
        "Referer": "https://item.jd.com/%s.html" % (productid)
    }
    for i in range(100):  # 此处设置爬取几页的评论
        page = i
        params = {
            "productId": 100031406046,  # 商品id
            'score': 0,  # 如果想要爬取全部评论设置为0，好评为3，中评为2，差评为1，晒图评价为4，追评为5
            'sortType': 5,
            'page': page,
            'pageSize': 10,
        }
        comment_resp = requests.get(url=comment_url, params=params, headers=headers)
        comment_str = comment_resp.text
        if comment_str == '':
            print("获取内容为空")
        comment_dict = json.loads(comment_str)
        comments = comment_dict['comments']
        load(comments, name, productid)


# 数据存储
def load(comments, name, productid):
    for comment in comments:
        nickname = comment['nickname']
        content = comment['content'].replace(',','，').replace('\n', '').replace('\r', '') #对数据进行处理
        g_uid = comment['guid']
        creationTime = comment['creationTime']
        is_Top = comment['isTop']
        plus = comment['plusAvailable']
        referenceTime = comment['referenceTime']
        score = comment['score']
        days = comment['days']
        is_Mobile = comment['userClient']

        if(is_Top == False):
            is_Top = "非置顶"
        else:
            is_Top = "置顶"

        if(is_Mobile == 2):
            is_Mobile = "IOS客户端"
        elif (is_Mobile == 0):
            is_Mobile = "PC端"
        elif(is_Mobile == 4):
            is_Mobile = "Android客户端"

        if(plus == 201):
            plus = "PLUS会员"
        else:
            plus = "非会员"


        test = g_uid \
               + ',' + nickname \
               + ',' + content \
               + ',' + str(score) \
               +',' + creationTime \
               + ',' + str(is_Top) \
               + ',' + plus \
               + ',' + referenceTime \
               + ',' + str(days) \
               + ',' + str(is_Mobile)

        # print(infor)
        with open('C:/Users/jzz/Desktop/' + '%s' % (name) + '.csv', 'a',
                  newline='')as csv_file:
            text = []
            text.append(test)
            writer = csv.writer(csv_file)
            writer.writerow(text)


# 获取搜索商品名界面的每个商品的序号
def get_number(name):
    headers = {
         'cookie': 'shshshfpa=4e6c0f90-587c-a46f-5880-a7debd7d4393-1544616560; __jdu=1126324296; PCSYCityID=412; user-key=44089d07-befa-4522-87fc-bcc039ec7045; pinId=qopcdCj6kcR3U84v0KTTbrV9-x-f3wj7; pin=jd_769791719e9e9; unick=jd_769791719e9e9; _tp=nc%2FbpB%2BkeSbk3jZ6p2H0FlWrdUa1gbgi16QiQ7NBXKY%3D; _pst=jd_769791719e9e9; cn=9; ipLoc-djd=1-72-2799-0; mt_xid=V2_52007VwMSUVpaUV8cQR5sUWMDEgUIUVBGGEofWhliABNUQQtQWkpVHVVXb1ZGB1lYW11LeRpdBW4fElFBW1VLH0ESXgJsAhpiX2hSahxLGFsFZwcRUG1bWlo%3D; shshshfpb=bRnqa4s886i2OeHTTR9Nq6g%3D%3D; unpl=V2_ZzNtbUZTSxJ3DURTLk0LAmJXFVlKAkdAIQ1PUXseCVIzU0UKclRCFXwURldnGlUUZwcZXERcQRdFCHZXchBYAWcCGllyBBNNIEwHDCRSBUE3XHxcFVUWF3RaTwEoSVoAYwtBDkZUFBYhW0IAKElVVTUFR21yVEMldQl2VHsaWwdkBhFVRWdzEkU4dl17HVwDYDMTbUNnAUEpAUJRfRpcSGcDEVpAVEYWfQ92VUsa; __jda=122270672.1126324296.1544405080.1545968922.1545980857.16; __jdc=122270672; ceshi3.com=000; TrackID=11EpDXYHaqwJE15W6paeMk_GMm05o3NUUeze9XyIcFs33GGxX8knxMpxWTeID75qSiUlj31s8CtKJs4hJUV-7CvKuiOEyDd8bvOCH7zzigeI; __jdv=122270672|baidu-pinzhuan|t_288551095_baidupinzhuan|cpc|0f3d30c8dba7459bb52f2eb5eba8ac7d_0_55963436def64e659d5de48416dfeaff|1545980984854; 3AB9D23F7A4B3C9B=OA3G4SO3KYLQB6H3AIX36QQAW34BF376WJN66IUPEQAG6FUA2NWGM6R6MBDL32HLDG62WL2FICMYIVMOU6ISUWHKPE; shshshfp=1ed96ad08a7585648cd5017583df22bd; _gcl_au=1.1.162218981.1545981094; JSESSIONID=305879A97D4EA21F4D5C4207BB81423F.s1; shshshsID=c8c51ee0c5b1ddada7c5544abc3eea8a_5_1545981289039; __jdb=122270672.11.1126324296|16.1545980857; thor=3A30EBABA844934A836AC9AA37D0F4B85306071BD7FC64831E361A626E76F6977EC7794D06F2A922AEABF7D3D7DC22FBE2EB6B240F81A13F5A609368D4185BA0081D7C34A93760063D2F058F5B916835B4960EC8A9122008745971D812BA9E4AE48542CCC5A42E5CD786CC93770E520E36F950614C06A7EB05C8E1DD93EEA844B2EBA9B0136063FCFB6B7C83AECA828774041A9FED7BD98496689496122822FF',
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36",
    }
    response = requests.get("https://search.jd.com/Search?keyword=%s&enc=utf-8" % (name), headers=headers)
    html = BeautifulSoup(response.text, 'lxml')
    list = html.find_all("li", class_='gl-item')
    numbers = []
    for number in list:
        numbers.append(int(number.get("data-sku")))
    return numbers


def main():
    get_comment(100031406046, "华为")
    #time.sleep(0.5)
    print("爬取完毕")


main()

 

2、数据预处理：要求使用MapReduce或者kettle实现源数据的预处理，对大量的Json文件，进行清洗，以得到结构化的文本文件。（10分）

（1）去除用户评论表的重复记录；

（2）按照清洗后的数据格式要求提取相应的数据字段。

 

 

 

此处的数据清洗已经在第一步完成

还可以使用kettle进行清洗（开源可视化EL工具）

3、 数据统计：生成Hive用户评论数据：（15分）

（1）在Hive创建一张表，用于存放清洗后的数据，表名为pinglun，字段名、字符类型、字段解释如下：

1.    productid        string  产品ID  
2.    commentcount     int     评论数  
3.    goodcount        int     好评数  
4.    generalcount     int     中评数  
5.    poorcount        int     差评数  
6.    goodrateshow     float   好评率  
7.    generalrateshow  float   中评率  
8.    poorrateshow     float   差评率  
9.    guid             string  随机生成ID  
10.    content          string  评论内容  
11.    creationtime     string  写评论的时间  
12.    score            int     打分  
13.    nickname         string  昵称  
14.    userlevelname    string  会员级别  
15.    userclientshow   string  评论设备  
16.    ismobile         string  是否移动端  
17.    days             int     评论时间距【收货/下单】时间多长时间  

create table pinglun(
guid string,
nickname string,
content string,
score int,
creationTime string,
isTop string,
plus string,
referenceTime string,
days int,
ismobile string
)
row format delimited fields terminated by ',';

load data local inpath '/data/jd/jd.csv' into table pinglun;

需求1：分析用户使用移动端购买还是PC端购买，及移动端和PC端的用户比例，生成ismobilehive表，存储统计结果；

create table ismobilehive(
clientname string,
num int
)row format delimited fields terminated by ',';

insert into table ismobilehive select ismobile,count(*) from pinglun group by ismobile;

--查看结果
select * from ismobilehive;

 

需求2：分析用户评论周期（收到货后，一般多久进行评论），生成dayssql表，存储统计结果；

create table dayssql(
　　day string,
　　num int
)row format delimited fields terminated by ',';

insert into table dayssql select days,count(*) as num from pinglun group by days order by num desc;

--结果
select * from dayssql;

 

 

 

需求3：分析会员级别（判断购买此商品的用户级别），生成userlevelname_out表，存储统计结果；

create table userlevelname_out(
　　userlevel string,
　　num int
)row format delimited fields terminated by ',';

insert into table userlevelname_out select plus,count(*) as num from pinglun group by plus;

select * from userlevelname_out;

 

需求4：分析每天评论量，生成creationtime_out表，存储统计结果；

create table creationtime_out(
　　time string,
　　num int
)row format delimited terminated by ',';

insert into table creatiotime_out select substr(creationTime,0,10) as time,count(*) as num from pinglun group by substr(creationTime,0,10);

select * from creationtime_out;

 

 

 

需求5：日期格式标准化

功能为：去掉评论时间的时分秒，只保留年月日

create table pinglun01(
guid string,
nickname string,
content string,
score int,
creationTime string,
isTop string,
plus string,
referenceTime string,
days int,
ismobile string
)
row format delimited fields terminated by ',';

insert into table pinglun01 select guid,nickname,content,score,substr(creationTime,0,10),isTop,plus,substr(referenceTime,0,10),days,ismobilefrom pinglun;

 

 

 

 

3、 利用Sqoop进行数据迁移至Mysql数据库：（5分）

将上述五个表倒入到相对应的mysql数据表中。

可以使用Sqoop（需要安装）,或者导出文件再次进行数据导入

sqoop export --connect jdbc:mysql://192.168.88.1:3306/test01 --username root --password whyjlbcdy2001 --table ismobilehive --export-dir /user/hive/warehouse/mi12pro.db/ismobilehive --input-fields-terminated-by ',' --input-null-string '\\N' --input-null-non-string '\\N' --m 1

sqoop export --connect jdbc:mysql://192.168.88.1:3306/test01 --username root --password why --table dayssql --export-dir /user/hive/warehouse/mi12pro.db/dayssql --input-fields-terminated-by ',' --input-null-string '\\N' --input-null-non-string '\\N' --m 1

sqoop export --connect jdbc:mysql://192.168.88.1:3306/test01 --username root --password why --table userlevelname_out --export-dir /user/hive/warehouse/mi12pro.db/userlevelname_out

sqoop export --connect jdbc:mysql://192.168.88.1:3306/test01 --username root --password why --table creationtime_out --export-dir /user/hive/warehouse/mi12pro.db/creationtime_out

sqoop export --connect jdbc:mysql://192.168.88.1:3306/test01 --username root --password why --table mi12pro --export-dir /user/hive/warehouse/mi12pro.db/comment02 --input-fields-terminated-by ',' --input-null-string '\\N' --input-null-non-string '\\N' --m 1

 

3、 数据可视化：利用JavaWeb+Echarts完成数据图表展示过程（20分）

（实现前五步，获得60分）

需求1：把用户对京东进行评论时使用的是客户端还是移动端的统计数据，用饼图进行数据展示，从而达到让观者能从中熟悉某个项目与整个数据组间所存在的比例关系的目的。

 

 

需求2：把用户在收到货后，一般多久进行评论，即用户评论周期用柱状图展示，可以达到展现数据并将数据进行比较的目的。

 

 

需求3：将购买某商品的用户级别进行统计的结果数据用饼状图展示，从而可以展现用户级别的比例构成关系，让观者能从中熟悉某个级别的用户数量与所有购买用户所存在的比例关系。

 

 

需求4：将某件商品的每天的评论量的统计数据用折线图进行展现，可以展现出这个商品每天的评论量的变化趋势。

数据可视化比较简单，直接可视化即可

 

标签：数据分析,comment,电子商务,string,--,int,评论,table,开学
From： https://www.cnblogs.com/jzz-111jy/p/17129187.html