大数据技术与应用案例测试--电子商务大数据分析
一、测试要求:
1、 数据采集(要求至少爬取三千条记录,时间跨度超过一星期):(10分)
要求Python 编写程序爬取京东手机的评论数据,生成Json形式的数据文件。
-
- python代码(一次只是爬取单个商品的用户评论、本次爬取了三个产品的用户评论):
- 需要修改的参数:agents、url、cookie、phone_id
- 爬取数据的相关格式请自行修改
import urllib.request import json import random import time as time0 import re, os import pandas as pd # 设置代理 agents = [ "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.51 Safari/537.36", "Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/19.77.34.5 Safari/537.1", "Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/535.24 (KHTML, like Gecko) Chrome/19.0.1055.1 Safari/535.24" ] def product_reviews(product_id=None, p=0, maxPage=99): root_dir = '畅想_详细字典' # 判断之前是否爬取过这个型号手机的评论(一种型号的手机,颜色和内存不同,但评论共享) os.makedirs(root_dir, exist_ok=True) phone_list = os.listdir(root_dir) phone_txt = str(product_id) + '.txt' if phone_txt in phone_list: print(product_id) return [] # 对每一页循环爬取 # "maxPage": 45 k_head = 0 while p < maxPage: # 所有品牌评论 # url = 'https://club.jd.com/comment/productPageComments.action?callback=fetchJSON_comment98&productId={}&score=0&sortType=5&page={}&pageSize={}&isShadowSku=0&rid=0&fold=1' # 只看当前商品的评论 # url = 'https://club.jd.com/comment/skuProductPageComments.action?callback=fetchJSON_comment98&productId={}&score=0&sortType=5&page={}&pageSize={}&isShadowSku=0&fold=1' # url = 'https://club.jd.com/comment/skuProductPageComments.action?callback=fetchJSON_comment98&productId={}&score=0&sortType=5&page={}&pageSize={}&isShadowSku=0&fold=1' url = 'https://club.jd.com/comment/skuProductPageComments.action?callback=fetchJSON_comment98&productId={}&score=0&sortType=5&page={}&pageSize={}&isShadowSku=0&fold=1' url = url.format(product_id, p, maxPage) # print(url) # 仿造请求头,骗过浏览器 # cookie可以查找自己浏览器中的cookie,直接复制过来 # cookie = '__jdu=16086454389902142527500; shshshfpa=428bc42a-e70a-655c-93f6-e3691985be43-1608645441; areaId=5; PCSYCityID=CN_130000_0_0; shshshfpb=zZdUWw6j4E+CLU7Oc2T9TPw==; jwotest_product=99; ipLoc-djd=5-142-42547-54561; unpl=JF8EAMlnNSttWR5cBhkFSREXSQ8HW10JS0RQam5RV1hcSlwMGFYfF0d7XlVdXhRKFR9vZRRUWlNKUA4aACsSEXteXVdZDEsWC2tXVgQFDQ8VXURJQlZAFDNVCV9dSRZRZjJWBFtdT1xWSAYYRRMfDlAKDlhCR1FpMjVkXlh7VAQrAhwTGUxYUFtfAUMfAmxnAFdZW01QBBoyKxUge21cX18PQxEzblcEZB8MF1YNEgMdEV1LWlVXWg1PEgFmbw1VXlhOVwEYBB8TEXtcZF0; __jdv=76161171|baidu-pinzhuan|t_288551095_baidupinzhuan|cpc|0f3d30c8dba7459bb52f2eb5eba8ac7d_0_0d9236c263cb4101bb48e2450982e54f|1647159858849; token=073f0cf5421b21eb907cb3d463424c0d,2,915089; __tk=yLrdvVbqNid3IaWAIDreOMTKOBrtHJ5QyghVqEbPzizsHLThyLr4OB5TIgKtwMblyg33rRbC,2,915089; shshshfp=3f1b5dd4917b58fe63ba3cdc71c35d33; shshshsID=2e60157b7de85966e3acbd0dc0063568_1_1647161877700; __jda=122270672.16086454389902142527500.1608645438.1647158698.1647161878.18; __jdb=122270672.1.16086454389902142527500|18.1647161878; __jdc=122270672; ip_cityCode=142; JSESSIONID=77C90B3806506F4FE8DA83EFC6A843FB.s1; 3AB9D23F7A4B3C9B=PYWTVSEEI7W7KUBCKF6CBWAXHNRJIWPF2VDYXRDH7USOJ4XIOOKLRQ4Z5JEMWWSSIBFD6MGMFEV5I2UWS2R6ZA6STM' # cookie = '__jdu=16086454389902142527500; shshshfpa=428bc42a-e70a-655c-93f6-e3691985be43-1608645441; areaId=5; PCSYCityID=CN_130000_0_0; shshshfpb=zZdUWw6j4E+CLU7Oc2T9TPw==; jwotest_product=99; ipLoc-djd=5-142-42547-54561; unpl=JF8EAMlnNSttWR5cBhkFSREXSQ8HW10JS0RQam5RV1hcSlwMGFYfF0d7XlVdXhRKFR9vZRRUWlNKUA4aACsSEXteXVdZDEsWC2tXVgQFDQ8VXURJQlZAFDNVCV9dSRZRZjJWBFtdT1xWSAYYRRMfDlAKDlhCR1FpMjVkXlh7VAQrAhwTGUxYUFtfAUMfAmxnAFdZW01QBBoyKxUge21cX18PQxEzblcEZB8MF1YNEgMdEV1LWlVXWg1PEgFmbw1VXlhOVwEYBB8TEXtcZF0; __jdv=76161171|baidu-pinzhuan|t_288551095_baidupinzhuan|cpc|0f3d30c8dba7459bb52f2eb5eba8ac7d_0_0d9236c263cb4101bb48e2450982e54f|1647163488743; __jda=122270672.16086454389902142527500.1608645438.1647158698.1647161878.18; __jdc=122270672; token=38fe0b7e85bcc8e668e7ee86b94f7374,2,915090; __tk=TLY2iIi5TUvviAa4TUqFiDfJinTvVIi5Skl5VIVKVUl2VnSEiLe2iG,2,915090; shshshfp=3f1b5dd4917b58fe63ba3cdc71c35d33; ip_cityCode=142; shshshsID=2e60157b7de85966e3acbd0dc0063568_7_1647163524505; __jdb=122270672.7.16086454389902142527500|18.1647161878; 3AB9D23F7A4B3C9B=PYWTVSEEI7W7KUBCKF6CBWAXHNRJIWPF2VDYXRDH7USOJ4XIOOKLRQ4Z5JEMWWSSIBFD6MGMFEV5I2UWS2R6ZA6STM; JSESSIONID=ED19674156BF5FC641C366B7E0FFAAD2.s1' cookie = '__jdu=16086454389902142527500; shshshfpa=428bc42a-e70a-655c-93f6-e3691985be43-1608645441; areaId=5; PCSYCityID=CN_130000_0_0; shshshfpb=zZdUWw6j4E+CLU7Oc2T9TPw==; jwotest_product=99; ipLoc-djd=5-142-42547-54561; unpl=JF8EAMlnNSttWR5cBhkFSREXSQ8HW10JS0RQam5RV1hcSlwMGFYfF0d7XlVdXhRKFR9vZRRUWlNKUA4aACsSEXteXVdZDEsWC2tXVgQFDQ8VXURJQlZAFDNVCV9dSRZRZjJWBFtdT1xWSAYYRRMfDlAKDlhCR1FpMjVkXlh7VAQrAhwTGUxYUFtfAUMfAmxnAFdZW01QBBoyKxUge21cX18PQxEzblcEZB8MF1YNEgMdEV1LWlVXWg1PEgFmbw1VXlhOVwEYBB8TEXtcZF0; __jdv=76161171|baidu-pinzhuan|t_288551095_baidupinzhuan|cpc|0f3d30c8dba7459bb52f2eb5eba8ac7d_0_0d9236c263cb4101bb48e2450982e54f|1647163488743; __jdc=122270672; __jda=122270672.16086454389902142527500.1608645438.1647158698.1647161878.18; shshshfp=3f1b5dd4917b58fe63ba3cdc71c35d33; ip_cityCode=142; token=a50c44639af4e4879d72140e2e4b8af5,2,915091; __tk=kYj5AUeEjDftAUgyAVAqkVa1BVfqBVAsBVAqAVnFAz4,2,915091; shshshsID=2e60157b7de85966e3acbd0dc0063568_8_1647164950694; __jdb=122270672.8.16086454389902142527500|18.1647161878; 3AB9D23F7A4B3C9B=PYWTVSEEI7W7KUBCKF6CBWAXHNRJIWPF2VDYXRDH7USOJ4XIOOKLRQ4Z5JEMWWSSIBFD6MGMFEV5I2UWS2R6ZA6STM; JSESSIONID=D3446CA1DE4A705EFCCCCC073D47B42D.s1' headers = { 'User-Agent': ''.join(random.sample(agents, 1)), 'Referer': 'https://item.jd.com/', 'Cookie': cookie } # 发起请求 request = urllib.request.Request(url=url, headers=headers) time0.sleep(2.5) # 得到响应ti'm try: content = urllib.request.urlopen(request).read().decode('gbk') except: print('第%d页评论代码出错' % p) p = p + 1 continue # 去掉多余得到json格式 content = content.strip('fetchJSON_comment98vv995();') # 评论的最大页数 try: maxPage = int(re.findall('"maxPage":(.*?),"', content, re.S)[0]) except: pass try: obj = json.loads(content) except: print('信号不好,再次尝试!') print([content]) print(url) continue comments = obj['comments'] # 产品评论总结 productCommentSummary = obj['productCommentSummary'] dict_pars_info = {} # 平均分 # dict_pars_info['平均分'] = str(productCommentSummary['averageScore']) # 好评率 dict_pars_info['好评率'] = str(productCommentSummary['goodRate']) # 当前总评论数 dict_pars_info['中评率'] = str(productCommentSummary['generalRate']) # 默认评论数 dict_pars_info['差评率'] = str(productCommentSummary['poorRate']) # 好评、中评、差评 dict_pars_info['好评数'] = str(productCommentSummary['score5Count']) dict_pars_info['中评数'] = str(productCommentSummary['score3Count']) dict_pars_info['差评数'] = str(productCommentSummary['score1Count']) if len(comments) > 0: # print(comments) for comment in comments: # print(comment) id = comment['id'] guid = comment['guid'] content = comment['content'] creationTime = comment['creationTime'] score = comment['score'] nickname = comment['nickname'] plusAvailable = comment['plusAvailable'] days = comment['days'] try: mobileVersion = comment['mobileVersion'] except: mobileVersion = '' item = { 'id': id, 'guid': guid, 'content': content, 'creationTime': creationTime, 'score': score, 'nickname': nickname, 'plusAvailable': plusAvailable, 'mobileVersion': mobileVersion, 'days': days, } item.update(dict_pars_info) # print(item) string = str(item) # 1.保存为csv格式 item_dataframe = pd.DataFrame([item]) # print(item_dataframe) if k_head == 0: item_dataframe.to_csv(root_dir + '/%d.csv' % product_id, mode='w', header=True, index=False, encoding='gbk') k_head += 1 else: item_dataframe.to_csv(root_dir + '/%d.csv' % product_id, mode='a', header=False, index=False, encoding='gbk') # 2.保存成txt fp = open(root_dir + '/%d.txt' % product_id, 'a', encoding='gbk') fp.write(string + '\n') fp.close() print('%s-page---finish(%s/%s)' % (p, p, maxPage)) else: return [] p = p + 1 if __name__ == '__main__': phone_id = 100015154663 # phone_id = 100026796994 # phone_id = 100016944073 product_reviews(product_id=phone_id)
运行截图:
2、数据预处理:要求使用MapReduce或者kettle实现源数据的预处理,对大量的Json文件,进行清洗,以得到结构化的文本文件。(本人使用kettle进行的数据清洗)
3、 数据统计:生成Hive用户评论数据:
(1) 在Hive创建一张表,用于存放清洗后的数据,表名为pinglun,(创建数据表SQL语句),创建成功导入数据截图:
sql语句:
create table pinglun(id string,commentcount int,goodcount int,generalcount int,poorcount int,goodrateshow float,generalrateshow float,poorrateshow float,guid string,content string,creationTime string,score int,nickname string,plusAvailable string,mobileVersion string,days int)row format delimited fields terminated by ',';
需求1:分析用户使用移动端购买还是PC端购买,及移动端和PC端的用户比例,生成ismobilehive表,存储统计结果;创建数据表SQL语句,创建成功导入数据截图
sql语句:(所出结果均使用代码中所爬取数据)
create table ismobile(buylevel string,buynum int);
select count(*) from pinglun where mobileVersion='';
insert into ismobile(buylevel,buynum) values('0',136);
select count(*) from pinglun where mobileVersion!='';
insert into ismobile(buylevel,buynum) values('1',1853);
需求2:分析用户评论周期(收到货后,一般多久进行评论),生成dayssql表,存储统计结果;创建数据表SQL语句,创建成功导入数据截图
sql语句:create table dayssql as select days,count(*) from pinglun group BY days;
需求3:分析会员级别(判断购买此商品的用户级别),生成userlevelname_out表,存储统计结果;创建数据表SQL语句,创建成功导入数据截图
sql语句:create table userlevelname_out as plusAvailable,count(*) from pinglun group BY plusAvailable;
需求4:分析每天评论量,生成creationtime_out表,存储统计结果;创建数据表SQL语句,创建成功导入数据截图
sql语句:create table creationtime_out as select to_date(creationtime),count(*) from pinglun group BY to_date(creationtime);
需求5:日期格式标准化后数据表前后对照截图 功能为:去掉评论时间的时分秒,只保留年月日
4、 利用Sqoop进行数据迁移至Mysql数据库:(5分)
五个表导入mysql数据库中五个表截图。
5、 数据可视化:利用JavaWeb+Echarts完成数据图表展示过程(20分)
需求1:可视化展示截图
需求2:可视化展示截图
需求3:可视化展示截图
需求4:可视化展示截图
6、 中文分词实现用户评价分析。(20分)
(1) 本节通过对商品评论表中的差评数据,进行分析,筛选用户差评点,以知己知彼。(筛选差评数据集截图)
Sql:create table poorpinglun as select * from pinglun where score < 4;
(2) 利用 python 结巴分词实现用户评价信息中的中文分词及词频统计;(分词后截图)
# -*- coding: utf-8 -*- import pandas as pd import pymysql import jieba def getdata(): dbconn=pymysql.connect(host="127.0.0.1", database="sparktest", user="root", password="lin0613", port=3306, charset='utf8') #sql语句 sqlcmd="select content from poorpinglun limit 177" #利用pandas 模块导入mysql数据 titles=pd.read_sql(sqlcmd,dbconn) keywords ="" print(titles.values) for i in range(len(titles)): str =(",").join(titles.values[i]) word_list = jieba.cut(str) keywords = list(word_list) count = 0 for count in range(len(keywords)): if checkword(keywords[count]): flag = checkre(pymysql.connect(host="127.0.0.1", database="sparktest", user="root", password="lin0613", port=3306, charset='utf8'), keywords[count]) if flag: save_keywords(pymysql.connect(host="127.0.0.1", database="sparktest", user="root", password="lin0613", port=3306, charset='utf8'), keywords[count]) print(keywords[count]) else: updatenum(pymysql.connect(host="127.0.0.1", database="sparktest", user="root", password="lin0613", port=3306, charset='utf8'), keywords[count]) else: print("未知词语") def checkword(word): invalid_words = [',', '.', ',', '。', ':', '“', '”', '"', '?', '?', '《', '》', '(', '{', ')', '}', '!', '%', '℃', '¥', '#'] if word.lower() in invalid_words: return False else: return True def save_keywords(db, keyword): # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 插入语句 sql = "INSERT INTO key_pinglun1(keyword,num) VALUES ('%s',1)" % (keyword) try: # 执行sql语句 cursor.execute(sql) # 执行sql语句 print("true") db.commit() except: print("数据插入失败") # 发生错误时回滚 db.rollback() # 关闭数据库连接 db.close() def updatenum(db,keyword): # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 插入语句 sql = "update key_pinglun1 set num=num+1 where keyword = '%s' " % keyword try: # 执行sql语句 cursor.execute(sql) # 执行sql语句 db.commit() except: print("数据更新失败") # 发生错误时回滚 db.rollback() # 关闭数据库连接 db.close() def checkre(db, keyword): # 使用cursor()方法获取操作游标 cursor = db.cursor() ket = [] # SQL 插入语句 ket = [] sql = "select keyword from key_pinglun1 where keyword = '%s'" % keyword try: # 执行sql语句 cursor.execute(sql) ket = list(cursor.fetchall()) db.commit() except: print("查询数据失败") # 发生错误时回滚 db.rollback() # 关闭数据库连接 db.close() if ket: return False else: return True if __name__ == '__main__': getdata()
(3)在 hive 中新建词频统计表并加载分词数据;
④柱状图可视化展示用户差评的统计前十类。
⑤用词云图可视化展示用户差评分词。
7、利用Spark进行实时数据分析。(20分)
本实验以京东商品评论为目标网站,架构采用爬虫+Flume+Kafka+Spark Streaming+Mysql,实现数据动态实时的采集、分析、展示数据。