图解大数据 | 综合案例-使用Spark分析挖掘零售交易数据

sudo apt-get install python3-pip
pip3 install bottle

hdfs dfs -put E_Commerce_Data.csv

cd /usr/local/spark #进入Spark安装目录
./bin/pyspark

df=spark.read.format('com.databricks.spark.csv').options(header='true', inferschema='true').load('E_Commerce_Data.csv')

df.count()

df.printSchema()

df.createOrReplaceTempView("data")

clean=df.filter(df["CustomerID"]!=0).filter(df["Description"]!="")

clean.count()

clean.write.format("com.databricks.spark.csv").options(header='true',inferschema='true').save('E_Commerce_Data_Clean.csv')

# -*- coding: utf-8 -*-
from pyspark import SparkContext
from pyspark.sql import SparkSession
from pyspark.sql.types import StringType, DoubleType, IntegerType, StructField, StructType
import json
import os

sc = SparkContext('local', 'spark_project')
sc.setLogLevel('WARN')
spark = SparkSession.builder.getOrCreate()

df = spark.read.format('com.databricks.spark.csv').options(header='true', inferschema='true').load('E_Commerce_Data_Clean.csv')
df.createOrReplaceTempView("data")

def save(path, data):
    with open(path, 'w') as f:
        f.write(data)

def countryCustomer():
    countryCustomerDF = spark.sql("SELECT Country,COUNT(DISTINCT CustomerID) AS countOfCustomer FROM data GROUP BY Country ORDER BY countOfCustomer DESC LIMIT 10")
    return countryCustomerDF.collect()

[国家名称,客户数]

def countryQuantity():
    countryQuantityDF = spark.sql("SELECT Country,SUM(Quantity) AS sumOfQuantity FROM data GROUP BY Country ORDER BY sumOfQuantity DESC LIMIT 10")
    return countryQuantityDF.collect()

[国家名称,销量]

def countrySumOfPrice():
    countrySumOfPriceDF = spark.sql("SELECT Country,SUM(UnitPrice*Quantity) AS sumOfPrice FROM data GROUP BY Country")
    return countrySumOfPriceDF.collect()

[国家名称,总销售额]

def stockQuantity():
    stockQuantityDF = spark.sql("SELECT StockCode,SUM(Quantity) AS sumOfQuantity FROM data GROUP BY StockCode ORDER BY sumOfQuantity DESC LIMIT 10")
    return stockQuantityDF.collect()

[商品编号,销量]

def wordCount():
    wordCount = spark.sql("SELECT LOWER(Description) as description from data").rdd.flatMap(lambda line:line['description'].split(" ")).map(lambda word:(word,1)).reduceByKey(lambda a,b:a+b).repartition(1).sortBy(lambda x:x[1],False)

    wordCountSchema = StructType([StructField("word", StringType(), True),StructField("count", IntegerType(), True)])
    wordCountDF = spark.createDataFrame(wordCount, wordCountSchema)
    wordCountDF = wordCountDF.filter(wordCountDF["word"]!='')
    return wordCountDF.take(300)

[关键词,次数]

def countryReturnInvoice():
    countryReturnInvoiceDF = spark.sql("SELECT Country,COUNT(DISTINCT InvoiceNo) AS countOfReturnInvoice FROM data WHERE InvoiceNo LIKE 'C%' GROUP BY Country ORDER BY countOfReturnInvoice DESC LIMIT 10")
    return countryReturnInvoiceDF.collect()

[国家名称,退货订单数]

def formatData():
    tradeRDD = df.select("InvoiceDate","Quantity","UnitPrice",).rdd
    result1 = tradeRDD.map(lambda line: (line['InvoiceDate'].split(" ")[0], line['Quantity'] , line['UnitPrice']))
    result2 = result1.map(lambda line: (line[0].split("/"), line[1], line[2]))
    result3 = result2.map(lambda line: (line[0][2], line[0][0] if len(line[0][0])==2 else "0"+line[0][0], line[0][1] if len(line[0][1])==2 else "0"+line[0][1], line[1], line[2]))
    return result3

def tradePrice():
    result3 = formatData()
    result4 = result3.map(lambda line:(line[0]+"-"+line[1],line[3]*line[4]))
    result5 = result4.reduceByKey(lambda a,b:a+b).sortByKey()
    schema = StructType([StructField("date", StringType(), True),StructField("tradePrice", DoubleType(), True)])
    tradePriceDF = spark.createDataFrame(result5, schema)
    return tradePriceDF.collect()

[日期,销售额]

def saleQuantity():
    result3 = formatData()
    result4 = result3.map(lambda line:(line[0]+"-"+line[1]+"-"+line[2],line[3]))
    result5 = result4.reduceByKey(lambda a,b:a+b).sortByKey()
    schema = StructType([StructField("date", StringType(), True),StructField("saleQuantity", IntegerType(), True)])
    saleQuantityDF = spark.createDataFrame(result5, schema)
    return saleQuantityDF.collect()

[日期,销量]

def buyReturn():
    returnDF = spark.sql("SELECT Country AS Country,COUNT(DISTINCT InvoiceNo) AS countOfReturn FROM data WHERE InvoiceNo LIKE 'C%' GROUP BY Country")
    buyDF = spark.sql("SELECT Country AS Country2,COUNT(DISTINCT InvoiceNo) AS countOfBuy FROM data WHERE InvoiceNo NOT LIKE 'C%' GROUP BY Country2")
    buyReturnDF = returnDF.join(buyDF, returnDF["Country"] == buyDF["Country2"], "left_outer")
    buyReturnDF = buyReturnDF.select(buyReturnDF["Country"],buyReturnDF["countOfBuy"],buyReturnDF["countOfReturn"])
    return buyReturnDF.collect()

[国家名称,购买订单数,退货订单数]

def unitPriceSales():
    unitPriceSalesDF = spark.sql("SELECT StockCode,AVG(DISTINCT UnitPrice) AS avgUnitPrice,SUM(Quantity) AS sumOfQuantity FROM data GROUP BY StockCode")
    return unitPriceSalesDF.collect()

[商品编号,平均单价,销量]

if __name__ == "__main__":

    base = "static/"
    if not os.path.exists(base):
        os.mkdir(base)

    m = {
        "countryCustomer": {
            "method": countryCustomer,
            "path": "countryCustomer.json"
        },
        "countryQuantity": {
            "method": countryQuantity,
            "path": "countryQuantity.json"
        },
        "countrySumOfPrice": {
            "method": countrySumOfPrice,
            "path": "countrySumOfPrice.json"
        },
        "stockQuantity": {
            "method": stockQuantity,
            "path": "stockQuantity.json"
        },
        "wordCount": {
            "method": wordCount,
            "path": "wordCount.json"
        },
        "countryReturnInvoice": {
            "method": countryReturnInvoice,
            "path": "countryReturnInvoice.json"
        },
        "tradePrice": {
            "method": tradePrice,
            "path": "tradePrice.json"
        },
        "saleQuantity": {
            "method": saleQuantity,
            "path": "saleQuantity.json"
        },
        "buyReturn": {
            "method": buyReturn,
            "path": "buyReturn.json"
        },
        "unitPriceSales": {
            "method": unitPriceSales,
            "path": "unitPriceSales.json"
        }
    }

    for k in m:
        p = m[k]
        f = p["method"]
        save(base + m[k]["path"], json.dumps(f()))
        print ("done -> " + k + " , save to -> " + base + m[k]["path"])

cd /usr/local/spark
./bin/spark-submit sales_data_analysis.py

from bottle import route, run, static_file
import json

@route('/static/<filename>')
def server_static(filename):
    return static_file(filename, root="./static")

@route("/<name:re:.*\.html>")
def server_page(name):
    return static_file(name, root=".")

@route("/")
def index():
    return static_file("index.html", root=".")

run(host="0.0.0.0", port=8888)

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width,height=device-height">
    <title>E-Commerce-Data 在线零售业务数据分析</title>
<style>
/* 省略 */
    </style>
</head>

<body>
    <div class="container">
    /* 只展示第一个统计结果的代码，其余省略 */
        <div class="chart-group">
            <h3>(1) 客户数最多的10个国家
            <br>
            <small style="font-size: 72%;">
                ——英国的客户最多，达到3950个，数量远大于其他国家；其次是德国、法国、西班牙等
            </small>
            </h3>
            <iframe src="countryCustomer.html" class="frame" frameborder="0"></iframe>
        </div>
    </div>
    <script>document.body.clientHeight;</script>
</body>
</html>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <style>
/* 省略 */
    </style>
</head>

<body>
<div id="chart" style="width:95%;height:95%;"></div>
<script src="static/jquery-3.2.1.min.js"></script>
<script src="static/echarts-4.7.0.min.js"></script>
<script>
    var myChart = echarts.init(document.getElementById('chart'));
    myChart.setOption(
    {
        color: ['#3398DB'],
        tooltip: {
            trigger: 'axis',
            axisPointer: {
                type: 'shadow'
            }
        },
        grid: {
            left: '3%',
            right: '4%',
            bottom: '3%',
            containLabel: true
        },
        xAxis: [
            {
                name: '国家',
                data: [],
                axisTick: {
                    alignWithLabel: true
                },
                axisLabel: {  
                   interval:0,  
                   rotate:40  
                }
            }
        ],
        yAxis: [
            {
                name: '客户数',
            }
        ],
        series: [
            {
                name: '客户数',
                type: 'bar',
                barWidth: '60%',
                data: []
            }
        ]
    });
    myChart.showLoading();
    $.getJSON("/static/countryCustomer.json", data => {
        var names=[];
        var nums=[];

        data = data.map(v => ({
            country: v[0],
            customer: parseInt(v[1]),
        }))

        for(var i=0;i<data.length;i++){
            names.push(data[i].country);
            nums.push(data[i].customer);
        }
        myChart.setOption({
            xAxis: {
                data: names
            },
            series: [{
                data: nums
            }]
        });
        myChart.hideLoading();
    })
</script>
</body>
</html>

python3 web.py

Bottle v0.12.18 server starting up (using WSGIRefServer())...
Listening on http://0.0.0.0:9999/
Hit Ctrl-C to quit.

#!/bin/bash
cd /usr/local/spark
./bin/spark-submit sales_data_analysis.py
python3 web.py