kettle+mysql+Python（Pandas，Patplotlip）实现大数据预处理（饼图，柱状图）

2022-02-24 05:47:40

环境准备

kettle
mysql数据库
Python环境

要求

使用预处理工具（kettle）将提供的文本数据进行清理，并导入到数据库；
对清洗完成的数据进行分析统计，统计各个城市的用户在流量与语音的平均支出，并使用Python绘制成图表，保存结果；
统计各个年龄阶段的用户在流量与语音的平均支出，并使用Python绘制成图表，保存结果

提前看结果

获取数据

数据为两个不同csv文件

获取百度网盘
链接：https://pan.baidu.com/s/1ocLrIhY5rHAzwdKdvZr13g
提取码：klyo

处理数据

1.打开kettle 工具

新建转换
突出如下图步骤
进行步骤配置
3.1 、CSV文件输入sim卡客户信息表

3.2 CSV文件输入2 (和上面操作一样，选择sim卡客户消费记录.csv即可)

3.3 排序记录

3.4 排序记录2

3.5记录连接2

3.6 字段选择

3.7 表输出（我这里用的是Navicat管理工具，其他方法相同）
3.7.1 创建一个kettle数据库以及qimozy表

给一个sql吧！
直接运行即可

DROP TABLE IF EXISTS `qimozy`;
CREATE TABLE `qimozy`  (
  `用户id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `性别` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `年龄` bigint(0) NULL DEFAULT NULL,
  `归属地` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `在网时长` bigint(0) NULL DEFAULT NULL,
  `状态` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `总收入` double NULL DEFAULT NULL,
  `增值收入` double NULL DEFAULT NULL,
  `流量收入` double NULL DEFAULT NULL,
  `短信收入` double NULL DEFAULT NULL,
  `彩信收入` double NULL DEFAULT NULL,
  `语音收入` double NULL DEFAULT NULL,
  `付费模式(预付费/后付费)` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `是否欠费` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

3.7.2 回到配置表输出

注意：如果这里连接不上可能是没有mysql驱动，将mysql驱动复制到kettle的lib目录下重启kettle即可连接
驱动下载：
链接：https://pan.baidu.com/s/1hYHRXuOu1bEA5fnLbZ9rBw
提取码：wrvf

3.7.3 查看字段是否对应

3.8 运行

可点击 Preview data 查看每个步骤的数据

可看到我们数据已经导入到mysql数据库

操控数据库

附上sql语句

饼图所需

select 归属地,avg(流量收入) from qimozy group by 归属地

柱状图所需

SELECT CASE 
            WHEN 年龄<=10 THEN 'A10' 
            WHEN 年龄>= 11 AND 年龄<=20 THEN 'A20' 
            WHEN 年龄>= 21 AND 年龄<=30 THEN 'A30'
            WHEN 年龄>= 31 AND 年龄<=40 THEN 'A40' 
            WHEN 年龄>= 41 AND 年龄<=50 THEN 'A50' 
            WHEN 年龄>= 51 AND 年龄<=60 THEN 'A60' 
            WHEN 年龄>= 61 AND 年龄<=70 THEN 'A70'
            ELSE 'other' END AS '年龄段', 
            AVG(流量收入) AS '平均流量', 
            AVG(语音收入) AS '平均语音',
            AVG(短信收入) AS '平均短信',
            AVG(彩信收入) AS '平均彩信'
            FROM qimozy 
            GROUP BY 年龄段  
            order by 年龄段

Python绘图

这里直接附上全部代码吧

import pymysql
from matplotlib import  pyplot as plt
from pandas import Series,DataFrame
import numpy as np
#添加图形对象
fig = plt.figure()
ax = fig.add_axes([0,0,1,1])
PlaceOfOwnership = []  #归属地列表
FlowAvg = [] #流量平均值
age = [] #年龄
avg_flow = [] #平均流量
avg_voice = [] #平均语音
avg_sms = [] #平均短信
avg_mms = [] #平均彩信
try:
    conn = pymysql.connect(host= '127.0.0.1',port=3307,user = "root",passwd = "123456",db="kettle",charset="utf8")  #数据库连接
    if (conn):
        print("数据库连接成功")
    cur = conn.cursor()  #指针游标
    # sql = "select * from qimozy"
    sql_grup = "select 归属地,avg(流量收入) from qimozy group by 归属地" #查询每个归属地的平均流量
    cur.execute(sql_grup)  #执行sql语句
    alldata = cur.fetchall()  #接收语句
    for data in alldata:    #遍历数据
        # print(data)
        #将数据加入到列表中
        PlaceOfOwnership.append(data[0])
        FlowAvg.append(int(data[1]))
        #查询每个年龄段的各个平均值
    sql_age = "SELECT CASE \
            WHEN 年龄<=10 THEN 'A10' \
            WHEN 年龄>= 11 AND 年龄<=20 THEN 'A20' \
            WHEN 年龄>= 21 AND 年龄<=30 THEN 'A30'\
            WHEN 年龄>= 31 AND 年龄<=40 THEN 'A40' \
            WHEN 年龄>= 41 AND 年龄<=50 THEN 'A50' \
            WHEN 年龄>= 51 AND 年龄<=60 THEN 'A60' \
            WHEN 年龄>= 61 AND 年龄<=70 THEN 'A70'\
            ELSE 'other' END AS '年龄段', \
            AVG(流量收入) AS '平均流量', \
            AVG(语音收入) AS '平均语音',\
            AVG(短信收入) AS '平均短信',\
            AVG(彩信收入) AS '平均彩信'\
            FROM qimozy \
            GROUP BY 年龄段  \
            order by 年龄段 "

    cur.execute(sql_age) #执行语句
    alldata2 = cur.fetchall() #获取值
    for data2 in alldata2:  #遍历值
        #将值加入到数组中
        age.append(data2[0])
        avg_flow.append(int(data2[1]))
        avg_voice.append(int(data2[2]))
        avg_sms.append(int(data2[3]))
        avg_mms.append(int(data2[4]))
except Exception as e: #异常处理
    print("Error"+e)
finally:
    cur.close() #关闭游标
    conn.close() #关闭数据库连接

# print(PlaceOfOwnership)
# print(FlowAvg)
print(age)
print(avg_flow)
print(avg_voice)
print(avg_sms)
print(avg_mms)

#一、绘制饼状图
#使得X/Y轴的间距相
ax.axis('equal')
#设置字体不然不会中文显示错误
plt.rcParams['font.sans-serif']=['SimHei'] #显示中文标签
plt.rcParams['axes.unicode_minus']=False
#准备数据
langs = PlaceOfOwnership
students = FlowAvg
#绘制饼状图
plt.title("各个地区用户流量平均支出占比")
ax.pie(students,labels=langs,autopct='%1.2f%%')
plt.show()

# 二、绘制柱状图
width = 0.2
index = np.arange(len(age))

r1 = plt.bar(age,avg_flow,width,color='r',label='流量')
r2 = plt.bar(index+width,avg_voice,width,color='b',label='语音')
r3 = plt.bar(index+width+width,avg_sms,width,color='c',label='短息')
r4 = plt.bar(index+width+width+width,avg_mms,width,color='g',label='彩信')
plt.title('各个年龄阶段用户平均支出情况')
plt.xlabel('年龄段')
plt.ylabel('平均值')
#显示图像
plt.legend()
plt.show()

如果包没有安装的打开终端运行下面命令

pip install pymysql
pip install matplotlib
pip install pandas
pip install numpy

没有安装谁，如果安装不上在后面加入清华源

注意更该自己的数据库连接

6.运行Python代码

得到结果：

码农公寓