kettle+mysql+Python（Pandas，Patplotlip）實作大資料預處理（餅圖，柱狀圖）

環境準備

kettle
mysql資料庫
Python環境

要求

1. 使用預處理工具（kettle）將提供的文本資料進行清理，并匯入到資料庫；
2. 對清洗完成的資料進行分析統計，統計各個城市的用戶在流量與語音的平均支出，并使用Python繪制成圖表，保存結果；
3. 統計各個年齡階段的用戶在流量與語音的平均支出，并使用Python繪制成圖表，保存結果

提前看結果

獲取資料

資料為兩個不同csv檔案

獲取百度網盤
鏈接：https://pan.baidu.com/s/1ocLrIhY5rHAzwdKdvZr13g
提取碼：klyo

處理資料

1.打開kettle 工具

1. 新建轉換
   
2. 突出如下圖步驟
   
3. 進行步驟配置
   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吧！
   直接運行下面sql陳述句即可創建出表和欄位

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `qimozy`;
CREATE TABLE `qimozy`  (
  `用戶id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `性別` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `年齡` bigint(0) NULL DEFAULT NULL,
  `歸屬地` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `在網時長` bigint(0) NULL DEFAULT NULL,
  `狀態` char(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `總收入` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `增值收入` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `流量收入` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `短信收入` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `彩信收入` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `語音收入` double(100, 0) NULL DEFAULT NULL,
  `付費模式(預付費/后付費)` varchar(100) 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代碼

得到結果：