# pyspark读取数据
# inferSchema=True:自动推断schema
# header=True:读取数据时,将第一行作为表头
# mode='DROPMALFORMED':将不符合schema的行删除
df = spark.read.csv('/Volumes/workspace/usahousing/usahousing/USA_Housing.csv', inferSchema=True, header=True, mode='DROPMALFORMED')
display(df.limit(2))

# 将DataFrame的column name进行标准化处理
# 我们将空格、短横线和斜线替换成下划线
# 只保留字母、数字和下划线
def sanitize_column_name(name):
    """Drops unwanted characters from column names.
    We replace spaces, dashes and slashes with underscores.
    and only keep alphanumeric characters.
    """
    answer = name
    for i, j in ((" ", "_"), ("-", "_"), ("/", "_"), ("&", "and")):
        answer = answer.replace(i, j)
    return "".join([
        char 
        for char in answer 
        if char.isalpha() or char.isdigit() or char == "_"])
# df.toDF(): 返回一个列名已更新的新 DataFrame
df = df.toDF(*[sanitize_column_name(col) for col in df.columns])

# 将Avg_Area_Income和Avg_Area_House_Age两列从string类型转换为double类型
from pyspark.sql.types import DoubleType
df = df.withColumn("Avg_Area_Income", df["Avg_Area_Income"].cast(DoubleType()))
df = df.withColumn("Avg_Area_House_Age", df["Avg_Area_House_Age"].cast(DoubleType()))
df.printSchema()

root
 |-- Avg_Area_Income: double (nullable = true)
 |-- Avg_Area_House_Age: double (nullable = true)
 |-- Avg_Area_Number_of_Rooms: double (nullable = true)
 |-- Avg_Area_Number_of_Bedrooms: double (nullable = true)
 |-- Area_Population: double (nullable = true)
 |-- Price: double (nullable = true)
 |-- Address: string (nullable = true)

# 清洗数据
df = df.dropna()
USAhousing = df
from pyspark.ml.feature import RFormula
from pyspark.ml.regression import LinearRegression
from pyspark.ml.evaluation import RegressionEvaluator
from pyspark.ml import Pipeline, Model

# 将数据分成训练数据和测试数据
# train_data 和 test_data 分别占总数据的 60% 和 40%
train_data, test_data = df.randomSplit([0.6, 0.4], 24)
x_cols = list(set(USAhousing.columns) - {'Price', 'Address'})
formula = "{} ~ {}".format("Price", " + ".join(x_cols))
print("formula: {}".format(formula))
# RFormula will automatically assemble the columns into a vector.
pipeline = Pipeline(
    stages=[
        RFormula(formula=formula, featuresCol='features'),
        LinearRegression(labelCol='Price')
    ]
)
fitted_model = pipeline.fit(train_data)
fitted_model

formula: Price ~ Avg_Area_Number_of_Bedrooms + Avg_Area_Income + Avg_Area_Number_of_Rooms + Area_Population + Avg_Area_House_Age

Downloading artifacts:   0%|          | 0/55 [00:00<?, ?it/s]

Uploading artifacts:   0%|          | 0/4 [00:00<?, ?it/s]

PipelineModel_a2622311ac77

# 使用训练好的模型对测试数据进行预测
d = fitted_model.transform(test_data)
display(d.limit(2))

# 从pipeline中检索拟合好的模型
lm = fitted_model.stages[-1]
# model coefficient, intercept
print("Coefficients: {}".format(lm.coefficients))
print("Intercept: {}".format(lm.intercept))
print(f"R^2 fit metric {lm.summary.r2}")

Coefficients: [1998.606677232051,21.502388556887606,120617.74657879196,15.23501171427518,164683.75900259122]
Intercept: -2629038.655070738
R^2 fit metric 0.916680702340295

# 线性回归模型，使用交叉验证进行网格搜索
# 定义RFormula公式，用于将Price作为标签列，其他列作为特征列
formula = "{} ~ {}".format("Price", " + ".join(x_cols))
print("formula: {}".format(formula))
rformula = RFormula(formula=formula)
lr = LinearRegression(labelCol = 'Price')
pipeline = Pipeline(stages=[rformula, lr])

formula: Price ~ Avg_Area_Number_of_Bedrooms + Avg_Area_Income + Avg_Area_Number_of_Rooms + Area_Population + Avg_Area_House_Age

from pyspark.ml.tuning import ParamGridBuilder
# Define parameter grid
# 在params中有三个参数组合,分别是alpha=0.0, alpha=0.5, alpha=1.0
# LinearRegression 线性回归模型
params = ParamGridBuilder() \
    .addGrid(lr.elasticNetParam, [0.0, 0.5, 1.0]) \
    .build()
# 创建一个回归评估器，用于评估模型在测试数据上的性能
evaluator = RegressionEvaluator() \
    .setMetricName("r2") \
    .setPredictionCol("prediction") \
    .setLabelCol("label")

from pyspark.ml.tuning import CrossValidator

# Run CrossValidator (no data folds)
# 创建一个交叉验证器，用于选择最佳模型
# 交叉验证器将数据集分成训练集和测试集，并在训练集上训练模型，然后在测试集上评估模型的性能
# 交叉验证器会尝试所有可能的参数组合，并选择在测试集上性能最好的参数组合
# 交叉验证器使用r2作为评估指标
# 这里将数据集分成3个折叠，每个折叠用于一次训练和一次测试
cv = CrossValidator() \
    .setEstimator(pipeline) \
    .setEvaluator(evaluator) \
    .setEstimatorParamMaps(params)

fitted_grid = cv.fit(train_data)

Downloading artifacts:   0%|          | 0/85 [00:00<?, ?it/s]

Uploading artifacts:   0%|          | 0/4 [00:00<?, ?it/s]

Downloading artifacts:   0%|          | 0/55 [00:00<?, ?it/s]

Uploading artifacts:   0%|          | 0/4 [00:00<?, ?it/s]

import numpy as np
# 简单模型总结
# 导入 numpy 库，用于数值计算
# 获取拟合网格中模型的数量
num_models = len(fitted_grid.getEstimatorParamMaps())
# 打印模型的数量
print("Number of models in fitted grid: {}".format(num_models))
# 打印模型评估指标的平均值
print(f"Model metrics are: {fitted_grid.avgMetrics}")

Number of models in fitted grid: 3
Model metrics are: [0.9161690215115436, 0.9161690215115436, 0.9161690215115436]

# 简单模型总结
# 从拟合好的网格中获取最佳模型
# fitted_grid.bestModel 返回的是一个 PipelineModel 对象，
# 它包含了最佳的 RFormula 模型和线性回归模型
# fitted_grid.bestModel.stages 返回的是一个列表，包含了 Pipeline 中的所有模型
# fitted_grid.bestModel.stages[1] 返回的是 Pipeline 中的第二个模型，也就是线性回归模型
model = fitted_grid.bestModel.stages[1]

print("Coefficients: {}".format(model.coefficients))
print("Intercept: {}".format(model.intercept))
print(f"R^2 fit metric {model.summary.r2}")

Coefficients: [1998.606677232051,21.502388556887606,120617.74657879196,15.23501171427518,164683.75900259122]
Intercept: -2629038.655070738
R^2 fit metric 0.916680702340295

## 使用 k 折交叉验证进行网格搜索
- 与上述相同，但在数据的 k 个子集上进行测试
- CrossValidator 计算通过在 k 个不同的数据集对上拟合 Estimator 而产生的 k 个模型的平均评估指标
- 最后，它使用最佳参数和整个数据集重新拟合 Estimator（管道）

# Run CrossValidator (k=3)
# 创建一个交叉验证器，用于选择最佳模型
# 交叉验证器将数据集分成训练集和测试集，并在训练集上训练模型，然后在测试集上评估模型的性能
# 交叉验证器会尝试所有可能的参数组合，并选择在测试集上性能最好的参数组合
# 交叉验证器使用r2作为评估指标
# 这里将数据集分成3个折叠，每个折叠用于一次训练和一次测试

number_of_folders = 3
cv = CrossValidator() \
    .setEstimator(pipeline) \
    .setEvaluator(evaluator) \
    .setEstimatorParamMaps(params) \
    .setNumFolds(number_of_folders)

fitted_grid = cv.fit(train_data)

Downloading artifacts:   0%|          | 0/85 [00:00<?, ?it/s]

Uploading artifacts:   0%|          | 0/4 [00:00<?, ?it/s]

Downloading artifacts:   0%|          | 0/55 [00:00<?, ?it/s]

Uploading artifacts:   0%|          | 0/4 [00:00<?, ?it/s]

# 简单模型总结
# 导入 numpy 库，用于数值计算
# 获取拟合网格中模型的数量

import numpy as np
num_models = len(fitted_grid.getEstimatorParamMaps())
print("Number of models in fitted grid: {}".format(num_models))
print(f"Model metrics are: {fitted_grid.avgMetrics}")

Number of models in fitted grid: 3
Model metrics are: [0.9161690215115436, 0.9161690215115436, 0.9161690215115436]

# summary of regression model
model = fitted_grid.bestModel.stages[1]

print("Coefficients: {}".format(model.coefficients))
print("Intercept: {}".format(model.intercept))
print(f"R^2 fit metric {model.summary.r2}")

Coefficients: [1998.606677232051,21.502388556887606,120617.74657879196,15.23501171427518,164683.75900259122]
Intercept: -2629038.655070738
R^2 fit metric 0.916680702340295

# 交叉验证器，用于选择最佳模型
# 交叉验证器将数据集分成训练集和测试集，并在训练集上训练模型，然后在测试集上评估模型的性能
# 交叉验证器会尝试所有可能的参数组合，并选择在测试集上性能最好的参数组合
# 交叉验证器使用r2作为评估指标
# 这里将数据集分成6个折叠，每个折叠用于一次训练和一次测试
number_of_folders = 6
cv = CrossValidator() \
    .setEstimator(pipeline) \
    .setEvaluator(evaluator) \
    .setEstimatorParamMaps(params) \
    .setNumFolds(number_of_folders)

fitted_grid = cv.fit(train_data)

Downloading artifacts:   0%|          | 0/85 [00:00<?, ?it/s]

Uploading artifacts:   0%|          | 0/4 [00:00<?, ?it/s]

Downloading artifacts:   0%|          | 0/55 [00:00<?, ?it/s]

Uploading artifacts:   0%|          | 0/4 [00:00<?, ?it/s]

import numpy as np
# 获取拟合网格中模型的数量
num_models = len(fitted_grid.getEstimatorParamMaps())
print("Number of models in fitted grid: {}".format(num_models))
print(f"Model metrics are: {fitted_grid.avgMetrics}")

Number of models in fitted grid: 3
Model metrics are: [0.916199039014269, 0.916199039014269, 0.916199039014269]

# Cross validate with multiple models
from pyspark.ml.feature import RFormula
from pyspark.ml.regression import LinearRegression, DecisionTreeRegressor, GeneralizedLinearRegression, GeneralizedLinearRegressionModel
from pyspark.ml import Pipeline, Model
from pyspark.ml.evaluation import RegressionEvaluator
from pyspark.ml.tuning import ParamGridBuilder, CrossValidator

# 交叉验证多个模型
# 指定模型、它们的参数和通用管道
# 通用管道对象
formula = "{} ~ {}".format("Price", " + ".join(x_cols))
print("Formula: {}".format(formula))
r_formula = RFormula(formula=formula)
pipeline = Pipeline(stages=[])
basePipeline = [rformula]
param_grid = []

lr = LinearRegression()
param_grid += ParamGridBuilder() \
    .baseOn({pipeline.stages: basePipeline + [lr]}) \
    .addGrid(lr.regParam, [0.1, 0.3, 0.8]) \
    .build()

dt = DecisionTreeRegressor()
param_grid += ParamGridBuilder() \
    .baseOn({pipeline.stages: basePipeline + [dt]}) \
    .addGrid(dt.minInstancesPerNode, [1, 5]) \
    .build()

glr = GeneralizedLinearRegression()
param_grid += ParamGridBuilder() \
    .baseOn({pipeline.stages: basePipeline + [glr]}) \
    .addGrid(glr.regParam, [0, 0.8]) \
    .build()
param_grid

Formula: Price ~ Avg_Area_Number_of_Bedrooms + Avg_Area_Income + Avg_Area_Number_of_Rooms + Area_Population + Avg_Area_House_Age

[{Param(parent='Pipeline_2034d933a6a9', name='stages', doc='a list of pipeline stages'): [RFormula_8806986a86ec,
   LinearRegression_08c1a4c12b7f],
  Param(parent='LinearRegression_08c1a4c12b7f', name='regParam', doc='regularization parameter (>= 0).'): 0.1},
 {Param(parent='Pipeline_2034d933a6a9', name='stages', doc='a list of pipeline stages'): [RFormula_8806986a86ec,
   LinearRegression_08c1a4c12b7f],
  Param(parent='LinearRegression_08c1a4c12b7f', name='regParam', doc='regularization parameter (>= 0).'): 0.3},
 {Param(parent='Pipeline_2034d933a6a9', name='stages', doc='a list of pipeline stages'): [RFormula_8806986a86ec,
   LinearRegression_08c1a4c12b7f],
  Param(parent='LinearRegression_08c1a4c12b7f', name='regParam', doc='regularization parameter (>= 0).'): 0.8},
 {Param(parent='Pipeline_2034d933a6a9', name='stages', doc='a list of pipeline stages'): [RFormula_8806986a86ec,
   DecisionTreeRegressor_efd8fdf43c37],
  Param(parent='DecisionTreeRegressor_efd8fdf43c37', name='minInstancesPerNode', doc='Minimum number of instances each child must have after split. If a split causes the left or right child to have fewer than minInstancesPerNode, the split will be discarded as invalid. Should be >= 1.'): 1},
 {Param(parent='Pipeline_2034d933a6a9', name='stages', doc='a list of pipeline stages'): [RFormula_8806986a86ec,
   DecisionTreeRegressor_efd8fdf43c37],
  Param(parent='DecisionTreeRegressor_efd8fdf43c37', name='minInstancesPerNode', doc='Minimum number of instances each child must have after split. If a split causes the left or right child to have fewer than minInstancesPerNode, the split will be discarded as invalid. Should be >= 1.'): 5},
 {Param(parent='Pipeline_2034d933a6a9', name='stages', doc='a list of pipeline stages'): [RFormula_8806986a86ec,
   GeneralizedLinearRegression_8db3068d3f46],
  Param(parent='GeneralizedLinearRegression_8db3068d3f46', name='regParam', doc='regularization parameter (>= 0).'): 0.0},
 {Param(parent='Pipeline_2034d933a6a9', name='stages', doc='a list of pipeline stages'): [RFormula_8806986a86ec,
   GeneralizedLinearRegression_8db3068d3f46],
  Param(parent='GeneralizedLinearRegression_8db3068d3f46', name='regParam', doc='regularization parameter (>= 0).'): 0.8}]

# Specify the common (regression) model evaluator
evaluator = RegressionEvaluator() \
    .setMetricName("r2") \
    .setLabelCol("label") \
    .setPredictionCol("prediction")

# run crossvalidator (k=3)
number_of_folders = 3
cv = CrossValidator() \
    .setEstimator(pipeline) \
    .setEvaluator(evaluator) \
    .setEstimatorParamMaps(param_grid) \
    .setNumFolds(number_of_folders)

fitted_grid = cv.fit(train_data)

# 简单模型总结
# 导入 numpy 库，用于数值计算
# 获取拟合网格中模型的数量
import numpy as np
num_models = len(fitted_grid.getEstimatorParamMaps())
print("Number of models in fitted grid: {}".format(num_models))
print(f"Model metrics are: {fitted_grid.avgMetrics}")

Number of models in fitted grid: 7
Model metrics are: [0.9161690222133184, 0.9161690236163919, 0.916169027121294, 0.6519340361458211, 0.6529787791031572, 0.9161690215115436, 0.916169027121294]

import re
from pyspark.ml.tuning import CrossValidatorModel

def paramGrid_model_name(model):
    params = [v for v in model.values() if type(v) is not list]
    name = [v[-1] for v in model.values() if type(v) is list][0]
    name = re.match(r'([a-zA-Z]*)', str(name)).groups()[0]
    return f"{name}{params}"

def cv_metrics(cv: CrossValidatorModel):
    measures = zip(cv.avgMetrics, [paramGrid_model_name(m) for m in cv.getEstimatorParamMaps()])
    metrics, model_names = zip(*measures)
    return metrics, model_names

# 从拟合好的网格中获取所有模型的评估指标和名称
metrics, model_names = cv_metrics(fitted_grid)
metric_name = fitted_grid.getEvaluator().getMetricName()

# 模型指标比较图
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
sns.set_context("notebook")
sns.set_style('white')
sns.set_palette('bright')

def add_metric_labels(metrics):
    for i in range(len(metrics)):
        plt.text(i, metrics[i], f"{metrics[i]:.3f}", ha='center', va='bottom')

pdf = pd.DataFrame(zip(metrics, model_names), columns=['r2', 'model'])
sns.barplot(data=pdf, x='model', y='r2').set(title="Model Metrics")
plt.xticks(rotation=45)
add_metric_labels(metrics)

model = fitted_grid.bestModel.stages[1]

if isinstance(model, GeneralizedLinearRegressionModel):
    # 如果模型是广义线性回归模型，则直接打印模型的 summary（包含详细统计信息）
    print(model.summary)
else:
    # 否则，打印模型的系数、截距和 R^2 拟合优度
    print("Coefficients: {}".format(model.coefficients))
    print("Intercept: {}".format(model.intercept))
    print(f"R^2 fit metric {model.summary.r2}")

Coefficients: [1998.7406281513245,21.502338835170594,120617.38723836852,15.234976572111597,164683.38385046888]
Intercept: -2629029.7626482025
R^2 fit metric 0.9166807023352761

# 使用交叉验证选择的最佳模型对测试数据进行预测
# fitted_grid.bestModel 返回的是一个 PipelineModel 对象，
# 它包含了最佳的 RFormula 模型和线性回归模型
# 使用 fitted_grid.bestModel.transform() 方法对测试数据进行预测

predictions = fitted_grid.bestModel.transform(test_data)
display(predictions.select("label", "prediction"))

# 评估最佳模型在测试数据上的性能
# 使用 RegressionEvaluator 评估模型在测试数据上的性能
# RegressionEvaluator 使用 r2 作为评估指标
# 这里使用测试数据 predictions 作为输入数据
r2 = RegressionEvaluator(labelCol='label', predictionCol='prediction', metricName='r2')
r2 = r2.evaluate(predictions)
print(f"R^2 fit metric {r2}")

R^2 fit metric 0.9199146361186678

# 实际值与预测值对比图
pdf = predictions.toPandas()
sns.lmplot(data=pdf, x='Price', y='prediction', line_kws={'color': 'red'}, height=5, aspect=1).set(title="Actual vs Predicted")

<seaborn.axisgrid.FacetGrid at 0x7fbe6b9d3800>

## CrossValidator 简化了模型比较
- 在数据折叠上运行和测试，以减少模型过度拟合
- 运行模型参数
- 运行多个模型，使用管道作为参数

%sh
#wget https://raw.githubusercontent.com/nsethi31/Kaggle-Data-Credit-Card-Fraud-Detection/master/creditcard.csv

# credit card fraud detection
# 数据集包含2013年9月欧洲持卡人通过信用卡进行的交易。
# 此数据集显示两天内发生的交易，其中284,807笔交易中有492笔被标记为欺诈。
# 数据集是高度不平衡的，正类（欺诈）占所有交易的0.172%。
# 它只包含作为PCA转换结果的数字输入变量。
# 由于保密问题，未提供有关原始特征和更多背景信息的特征。
# 特征V1、V2、… V28是PCA获得的主要成分，唯一没有通过PCA转换的特征是“时间和“金额”。
# 特征“时间”包含每个事务与数据集中第一个事务之间经过的秒数。
# 特征“金额”是交易金额，此特征可用于示例依赖型成本敏感学习。
# 特征“类”是响应变量，如果出现欺诈则取值1，否则取值0。

# 读取数据
df = spark.read.csv('/FileStore/tables/creditcard.csv', inferSchema=True, header=True, mode='DROPMALFORMED')
display(df.limit(2))
cc_fraud = df

# 信用卡欺诈检测
# 导入必要的库
# RFormula: 用于将数据转换为 Spark MLlib 可以使用的格式
# LogisticRegression: 逻辑回归模型，用于二分类问题
# Pipeline: 用于将多个 Spark MLlib 步骤组合成一个工作流
# Model: 用于存储训练好的模型
from pyspark.ml.feature import RFormula
from pyspark.ml.classification import LogisticRegression
from pyspark.ml import Pipeline, Model

train_data, test_data = cc_fraud.randomSplit([0.6, 0.4], seed=24)

x_cols = list(set(cc_fraud.columns) - {'Class', 'Time', 'weight'})
formula='Class ~ ' + ' + '.join(x_cols)
print("Formula: {}".format(formula))

pipeline = Pipeline(stages=[
    RFormula(formula=formula, featuresCol='features'),
    LogisticRegression()])

fitted_pipeline = pipeline.fit(train_data)

Formula: Class ~ V12 + V3 + V16 + V10 + V25 + V15 + V5 + V22 + V14 + V19 + Amount + V9 + V7 + V6 + V18 + V28 + V11 + V21 + V20 + V4 + V8 + V13 + V1 + V23 + V17 + V2 + V26 + V24 + V27

Downloading artifacts:   0%|          | 0/55 [00:00<?, ?it/s]

Uploading artifacts:   0%|          | 0/4 [00:00<?, ?it/s]

# 逻辑回归模型总结
# 从pipeline中检索拟合好的模型
lm = fitted_pipeline.stages[1]
# training summary
print(lm.summary)
print(lm.summary.accuracy)

<pyspark.ml.classification.BinaryLogisticRegressionTrainingSummary object at 0x7f38a4c540e0>
0.9992459976503183

# 分类模型总结

def classification_model_summary(lm, formula, columns):
    print("Classification model summary")
    print(f"Equation\n\t{formula}\n")
    trainingSummary = lm.summary
    accuracy = trainingSummary.accuracy
    falsePositiveRate = trainingSummary.weightedFalsePositiveRate
    truePositiveRate = trainingSummary.weightedTruePositiveRate
    fMeasure = trainingSummary.weightedFMeasure()
    precision = trainingSummary.weightedPrecision
    recall = trainingSummary.weightedRecall
    print(f"Accuracy: {accuracy:.2f} FPR: {falsePositiveRate:.2f} TPR: {truePositiveRate:.2f}")
    print(f"F-measure: {fMeasure:.2f} Precision: {precision:.2f} Recall: {recall:.2f}")
    print(f"{'Labels':>20}")
    print("    "+''.join([f"{l:>10}" for l in lm.summary.labels]))
    print("F-measure"+''.join([f"{l:>10.3f}" for l in lm.summary.fMeasureByLabel()]))
    print("Precision"+''.join([f"{l:>10.3f}" for l in lm.summary.precisionByLabel]))
    print("Recall   "+''.join([f"{l:>10.3f}" for l in lm.summary.recallByLabel]))
classification_model_summary(lm, formula, x_cols)

Classification model summary
Equation
	Class ~ V12 + V3 + V16 + V10 + V25 + V15 + V5 + V22 + V14 + V19 + Amount + V9 + V7 + V6 + V18 + V28 + V11 + V21 + V20 + V4 + V8 + V13 + V1 + V23 + V17 + V2 + V26 + V24 + V27

Accuracy: 1.00 FPR: 0.37 TPR: 1.00
F-measure: 1.00 Precision: 1.00 Recall: 1.00
              Labels
           0.0       1.0
F-measure     1.000     0.738
Precision     0.999     0.897
Recall        1.000     0.628

# BinaryClassificationMetrics: 用于计算二分类指标
import matplotlib.pyplot as plt
from sklearn.metrics import roc_curve
import seaborn as sns
from pyspark.mllib.evaluation import BinaryClassificationMetrics
roc_data = lm.summary.roc.select('FPR', 'TPR').toPandas()
plt.figure(figsize=(5,5))
p = sns.lineplot(data=roc_data, x='FPR', y='TPR', linewidth=2)
p.set(xlabel='False Positive Rate', ylabel='True Positive Rate')
p.set(title="ROC Curve")
l1 = p.lines[0]
x1 = l1.get_xydata()[:,0]
y1 = l1.get_xydata()[:,1]
p.fill_between(x1, y1, color="lightblue", alpha=0.3)
plt.plot([0,1], [0,1], linestyle=(0, (5,5)), linewidth=2)

[<matplotlib.lines.Line2D at 0x7f388054a300>]

# 使用训练好的模型对训练数据进行预测
predictions = fitted_pipeline.transform(train_data)

# training: Area under precision recall curve (binary classification)
from pyspark.mllib.evaluation import BinaryClassificationMetrics
import matplotlib.pyplot as plt

pr_data = lm.summary.pr.select('recall', 'precision').toPandas()
plt.figure(figsize=(5,5))
p = sns.lineplot(data=pr_data, x='recall', y='precision', linewidth=2)
metrics = BinaryClassificationMetrics(predictions.select('label', 'prediction').rdd.map(tuple))
p.set(title=f"Precision-recall {metrics.areaUnderPR:.3f}")

l1 = p.lines[0]
x1 = l1.get_xydata()[:,0]
y1 = l1.get_xydata()[:,1]
p.fill_between(x1, y1, color="lightblue", alpha=0.3)
plt.plot([0,1], [0,1], linestyle=(0, (5,5)), linewidth=2)

/databricks/spark/python/pyspark/sql/context.py:165: FutureWarning: Deprecated in 3.0.0. Use SparkSession.builder.getOrCreate() instead.
  warnings.warn(

[<matplotlib.lines.Line2D at 0x7f387c918b90>]

# Training Confusion matrix
from pyspark.mllib.evaluation import MulticlassMetrics
import pandas as pd
import numpy as np
# 使用 MulticlassMetrics 计算混淆矩阵
# MulticlassMetrics 用于多分类问题的评估
# 混淆矩阵是一个 N x N 的矩阵，其中 N 是类别的数量
# 混淆矩阵的第 i 行第 j 列的值表示真实类别为 i，预测类别为 j 的样本数量
# 这里使用训练数据 predictions 作为输入数据
metrics = MulticlassMetrics(predictions.select('label', 'prediction').rdd.map(tuple))
# 将混淆矩阵转换为 numpy 数组
# metrics.confusionMatrix() 返回的是一个 Matrix 对象，需要使用 toArray() 方法将其转换为 numpy 数组
# 混淆矩阵是一个 N x N 的矩阵，其中 N 是类别的数量
# 混淆矩阵的第 i 行第 j 列的值表示真实类别为 i，预测类别为 j 的样本数量
confusion_matrix = metrics.confusionMatrix().toArray()

cnf_matrix = pd.DataFrame(confusion_matrix)
plt.figure(figsize=(5,5))
sns.heatmap(cnf_matrix/np.sum(cnf_matrix), annot=True, cmap='RdBu', fmt='.2%', annot_kws={'fontsize': 10})
p.set(xlabel='Predicted', ylabel='Actual', title="Confusion matrix")

/databricks/spark/python/pyspark/sql/context.py:165: FutureWarning: Deprecated in 3.0.0. Use SparkSession.builder.getOrCreate() instead.
  warnings.warn(

[Text(0.5, 24.0, 'Predicted'),
 Text(24.0, 0.5, 'Actual'),
 Text(0.5, 1.0, 'Confusion matrix')]

# 使用训练好的模型对测试数据进行预测
# fitted_pipeline.transform(test_data) 方法将测试数据转换为预测数据
# 返回的 predictions 是一个 DataFrame 对象，包含了测试数据和预测结果
# predictions 包含了原始的测试数据，以及新增的 prediction 列，表示模型的预测结果
# predictions 包含了原始的测试数据，以及新增的 probability 列，表示模型预测为每个类别的概率
# predictions 包含了原始的测试数据，以及新增的 rawPrediction 列，表示模型预测的原始输出
predictions = fitted_pipeline.transform(test_data)

from pyspark.mllib.evaluation import MulticlassMetrics

def classification_metrics_summary(predictions, label='label', prediction='prediction'):
    """
    Multi-class classification summary from prediction data
    """
    labels = [row['label'] for row in predictions.select(label).distinct().collect()]
    metrics = MulticlassMetrics(predictions.select(label, prediction).rdd.map(tuple))
    print(f"Classification metrics summary")
    if len(labels) == 2:
        bin_metrics = BinaryClassificationMetrics(predictions.select(label, prediction).rdd.map(tuple))
        areaUnderPR = bin_metrics.areaUnderPR
        areaUnderROC = bin_metrics.areaUnderROC
        print(f"Area under PR: {areaUnderPR}\t Area under ROC: {areaUnderROC}")
    
    accuracy = metrics.accuracy
    falsePositiveRate = metrics.weightedFalsePositiveRate
    truePositiveRate = metrics.weightedTruePositiveRate
    fMeasure = metrics.weightedFMeasure()
    precision = metrics.weightedPrecision
    recall = metrics.weightedRecall
    print(f"Accuracy: {accuracy:.2f} FPR: {falsePositiveRate:.2f} TPR: {truePositiveRate:.2f}")
    print(f"F-measure: {fMeasure:.2f} Precision: {precision:.2f} Recall: {recall:.2f}")
    print(f"{'Labels':>20}")
    print("    "+''.join([f"{l:>10}" for l in labels]))
    print("Precision"+''.join([f"{l:>10.3f}" for l in [metrics.precision(label) for label in labels]]))
    print("Recall   "+''.join([f"{l:>10.3f}" for l in [metrics.recall(label) for label in labels]]))
    
    return metrics.confusionMatrix().toArray()

# 获得预测结果
confusion_matrix = classification_metrics_summary(predictions)

/databricks/spark/python/pyspark/sql/context.py:165: FutureWarning: Deprecated in 3.0.0. Use SparkSession.builder.getOrCreate() instead.
  warnings.warn(

Classification metrics summary
Area under PR: 0.590936209296225	 Area under ROC: 0.9321066479080778
Accuracy: 1.00 FPR: 0.13 TPR: 1.00
F-measure: 1.00 Precision: 1.00 Recall: 1.00
              Labels
           0.0       1.0
Precision     1.000     0.634
Recall        0.999     0.865

# 绘制testing混淆矩阵图
cnf_matirx = pd.DataFrame(confusion_matrix)
plt.figure(figsize=(5,5))
sns.heatmap(cnf_matrix/np.sum(cnf_matrix), annot=True, cmap='RdBu', fmt='.2%', annot_kws={'fontsize': 10})
p.set(xlabel='Predicted', ylabel='Actual', title="Confusion matrix")

[Text(0.5, 24.0, 'Predicted'),
 Text(24.0, 0.5, 'Actual'),
 Text(0.5, 1.0, 'Confusion matrix')]

# multiple class classification
# wget https://raw.githubusercontent.com/daines-analytics/tabular-data-projects/master/r-classification-faulty-steel-plates/faults.csv

# 缺陷数据集
# 该数据集包含有关钢板缺陷的信息。
# 共有7种不同类型的缺陷：
# Pastry, Z_Scratch, K_Scatch, Stains, Dirtiness, Bumps, Other_Faults
# 该数据集包含1941个实例和34个属性。
# 属性包括：
# X_Minimum, X_Maximum, Y_Minimum, Y_Maximum, Pixels_Area, X_Perimeter, Y_Perimeter, Sum_of_Luminosity, Minimum_of_Luminosity, Maximum_of_Luminosity, Length_of_Conveyer, TypeOfSteel_A300, TypeOfSteel_A400, Steel_Plate_Thickness, Edges_Index, Empty_Index, Square_Index, Outside_X_Index, Edges_X_Index, Edges_Y_Index, Outside_Global_Index, LogOfAreas, Log_X_Index, Orientation_Index, Luminosity_Index, SigmoidOfAreas, Pastry, Z_Scratch, K_Scatch, Stains, Dirtiness, Bumps, Other_Faults

# 读取数据
df = spark.read.csv('/FileStore/tables/faults.csv', inferSchema=True, header=True, mode='DROPMALFORMED')
display(df.limit(2))

# categories in one column (fault)
# 将多个类别列合并为一个列（fault）
import pyspark.sql.functions as F
dff = df.withColumn('fault',
                    F.when(df.Pastry>0, 'Pastry') \
                        .when(df.Z_Scratch>0, 'Z_Scratch') \
                        .when(df.K_Scatch>0, 'K_Scatch') \
                        .when(df.Stains>0, 'Stains') \
                        .when(df.Dirtiness>0, 'Dirtiness') \
                        .when(df.Bumps>0, 'Bumps') \
                            .otherwise('Other_Fault'))
dff = dff.drop('Pastry').drop('Z_Scratch').drop('K_Scatch').drop('Stains').drop('Dirtiness').drop('Bumps').drop('Other_Faults')
display(dff.limit(2))

# 多个类别的分类
# 缺陷数据集
# 准备数据，将数据集分成训练数据和测试数据
# RFormula: 用于将数据转换为 Spark MLlib 可以使用的格式
# LogisticRegression: 逻辑回归模型，用于多分类问题
# Pipeline: 用于将多个 Spark MLlib 步骤组合成一个工作流
# Model: 用于存储训练好的模型
faults = dff
from pyspark.ml.feature import RFormula
from pyspark.ml.classification import LogisticRegression
from pyspark.ml import Pipeline, Model
train_data, test_data = faults.randomSplit([0.6, 0.4], seed=24)
x_cols = list(set(faults.columns) - {'fault'})
formula = "{} ~ {}".format('fault', ' + '.join(x_cols))
print("Formula: {}".format(formula))
pipeline = Pipeline(stages=[
    RFormula(formula=formula),
    LogisticRegression()
])
fitted_model = pipeline.fit(train_data)

Formula: fault ~ Maximum_of_Luminosity + Other_Faults + Square_Index + Pixels_Areas + X_Maximum + Outside_Global_Index + TypeOfSteel_A300 + SigmoidOfAreas + Outside_X_Index + Log_X_Index + X_Perimeter + Length_of_Conveyer + Orientation_Index + Y_Minimum + Sum_of_Luminosity + X_Minimum + Minimum_of_Luminosity + Y_Perimeter + Edges_Index + Steel_Plate_Thickness + Log_Y_Index + Edges_X_Index + Empty_Index + LogOfAreas + Y_Maximum + Edges_Y_Index + TypeOfSteel_A400 + Luminosity_Index

Downloading artifacts:   0%|          | 0/65 [00:00<?, ?it/s]

Uploading artifacts:   0%|          | 0/4 [00:00<?, ?it/s]

# 从pipeline中检索拟合好的模型
# fitted_model.stages 返回的是一个列表，包含了 Pipeline 中的所有模型
# fitted_model.stages[1] 返回的是 Pipeline 中的第二个模型，也就是逻辑回归模型
lm = fitted_model.stages[1]

# 分类模型总结
classification_model_summary(lm, formula, x_cols)

Classification model summary
Equation
	fault ~ Maximum_of_Luminosity + Other_Faults + Square_Index + Pixels_Areas + X_Maximum + Outside_Global_Index + TypeOfSteel_A300 + SigmoidOfAreas + Outside_X_Index + Log_X_Index + X_Perimeter + Length_of_Conveyer + Orientation_Index + Y_Minimum + Sum_of_Luminosity + X_Minimum + Minimum_of_Luminosity + Y_Perimeter + Edges_Index + Steel_Plate_Thickness + Log_Y_Index + Edges_X_Index + Empty_Index + LogOfAreas + Y_Maximum + Edges_Y_Index + TypeOfSteel_A400 + Luminosity_Index

Accuracy: 0.94 FPR: 0.01 TPR: 0.94
F-measure: 0.94 Precision: 0.94 Recall: 0.94
              Labels
           0.0       1.0       2.0       3.0       4.0       5.0       6.0
F-measure     1.000     0.884     0.983     0.886     0.768     1.000     0.831
Precision     1.000     0.878     0.978     0.878     0.797     1.000     0.844
Recall        1.000     0.889     0.987     0.894     0.741     1.000     0.818

# training values
predictions = fitted_model.transform(train_data)
# training: confusion matrix
from pyspark.mllib.evaluation import MulticlassMetrics
import pandas as pd
import numpy as np
metrics = MulticlassMetrics(predictions.select('label', 'prediction').rdd.map(tuple))
confusion_matrix = metrics.confusionMatrix().toArray()
cnf_matrix = pd.DataFrame(confusion_matrix)
plt.figure(figsize=(10,7))
sns.heatmap(cnf_matrix/np.sum(cnf_matrix), annot=True, cmap='RdBu', fmt='.2%', annot_kws={'fontsize': 10})
p.set(xlabel='Predicted', ylabel='Actual', title="Confusion matrix")

/databricks/spark/python/pyspark/sql/context.py:165: FutureWarning: Deprecated in 3.0.0. Use SparkSession.builder.getOrCreate() instead.
  warnings.warn(

[Text(0.5, 24.000000000000007, 'Predicted'),
 Text(24.000000000000007, 0.5, 'Actual'),
 Text(0.5, 1.0, 'Confusion matrix')]

# testing summary
predictions = fitted_model.transform(test_data)
# model fit with test data
confusion_matrix = classification_metrics_summary(predictions)

/databricks/spark/python/pyspark/sql/context.py:165: FutureWarning: Deprecated in 3.0.0. Use SparkSession.builder.getOrCreate() instead.
  warnings.warn(

Classification metrics summary
Accuracy: 0.90 FPR: 0.02 TPR: 0.90
F-measure: 0.90 Precision: 0.90 Recall: 0.90
              Labels
           0.0       1.0       4.0       3.0       2.0       6.0       5.0
Precision     0.993     0.807     0.685     0.870     0.969     0.591     0.917
Recall        1.000     0.823     0.704     0.859     0.908     0.765     0.917

# testing: confusion matrix
cnf_matrix = pd.DataFrame(confusion_matrix)
plt.figure(figsize=(10,7))
p = sns.heatmap(cnf_matrix/np.sum(cnf_matrix), annot=True, cmap='RdBu', fmt='.2%', annot_kws={'fontsize': 10})
p.set(xlabel='Predicted', ylabel='Actual', title="Confusion matrix")

[Text(0.5, 47.7222222222222, 'Predicted'),
 Text(95.72222222222221, 0.5, 'Actual'),
 Text(0.5, 1.0, 'Confusion matrix')]

# testing summary
predictions = fitted_model.transform(test_data)
confusion_matrix = classification_metrics_summary(predictions)

/databricks/spark/python/pyspark/sql/context.py:165: FutureWarning: Deprecated in 3.0.0. Use SparkSession.builder.getOrCreate() instead.
  warnings.warn(

Classification metrics summary
Accuracy: 0.90 FPR: 0.02 TPR: 0.90
F-measure: 0.90 Precision: 0.90 Recall: 0.90
              Labels
           0.0       1.0       4.0       3.0       2.0       6.0       5.0
Precision     0.993     0.807     0.685     0.870     0.969     0.591     0.917
Recall        1.000     0.823     0.704     0.859     0.908     0.765     0.917

import psutil
import platform
import os
env_name = os.environ.get('CONDA_DEFAULT_ENV')
print("当前 conda 环境名：", env_name)
print(platform.system()) # 操作系统名称
print(platform.release()) # 操作系统版本
print(platform.machine()) # 计算机架构
print(platform.processor()) # 处理器类型
# CPU 信息
print(psutil.cpu_count()) # CPU 核数
print(psutil.cpu_freq()) # CPU 频率
# 内存信息
print(psutil.virtual_memory()) # 内存总量、可用内存、已用内存等

Avg. Area Income	Avg. Area House Age	Avg. Area Number of Rooms	Avg. Area Number of Bedrooms	Area Population	Price	Address
79545.45857431678	5.682861321615587	7.009188142792237	4.09	23086.800502686456	1059033.5578701235	208 Michael Ferry Apt. 674
79248.64245482568	6.0028998082752425	6.730821019094919	3.09	40173.07217364482	1505890.91484695	188 Johnson Views Suite 079

Avg_Area_Income	Avg_Area_House_Age	Avg_Area_Number_of_Rooms	Avg_Area_Number_of_Bedrooms	Area_Population	Price	Address	features	label	prediction
35454.714659475445	6.855708363901107	6.018646502679608	4.5	59636.40255302499	1077805.577726322	Unit 4700 Box 1880	Map(vectorType -> dense, length -> 5, values -> List(4.5, 35454.714659475445, 6.018646502679608, 59636.40255302499, 6.855708363901107))	1077805.577726322	905856.8198512727
35797.323121548245	5.544221046634432	7.795138241804151	5.0	24844.200190072384	299863.0401311839	645 Mary Radial	Map(vectorType -> dense, length -> 5, values -> List(5.0, 35797.323121548245, 7.795138241804151, 24844.200190072384, 5.544221046634432))	299863.0401311839	382459.18199658114

Time	V1	V2	V3	V4	V5	V6	V7	V8	V9	V10	V11	V12	V13	V14	V15	V16	V17	V18	V19	V20	V21	V22	V23	V24	V25	V26	V27	V28	Amount	Class
0.0	-1.359807134	-0.072781173	2.536346738	1.378155224	-0.33832077	0.462387778	0.239598554	0.098697901	0.36378697	0.090794172	-0.551599533	-0.617800856	-0.991389847	-0.311169354	1.468176972	-0.470400525	0.207971242	0.02579058	0.40399296	0.251412098	-0.018306778	0.277837576	-0.11047391	0.066928075	0.128539358	-0.189114844	0.133558377	-0.021053053	149.62	0
0.0	1.191857111	0.266150712	0.166480113	0.448154078	0.060017649	-0.082360809	-0.078802983	0.085101655	-0.255425128	-0.166974414	1.612726661	1.065235311	0.489095016	-0.143772296	0.635558093	0.463917041	-0.114804663	-0.18336127	-0.145783041	-0.069083135	-0.225775248	-0.638671953	0.101288021	-0.339846476	0.167170404	0.125894532	-0.008983099	0.014724169	2.69	0

大数据分析与挖掘¶

04. How to do grid search in PySpark and Classification in PySpark¶

PySpark Classification¶

Training vs testing¶

Databricks PySpark vs PySpark¶

X_Minimum	X_Maximum	Y_Minimum	Y_Maximum	Pixels_Areas	X_Perimeter	Y_Perimeter	Sum_of_Luminosity	Minimum_of_Luminosity	Maximum_of_Luminosity	Length_of_Conveyer	TypeOfSteel_A300	TypeOfSteel_A400	Steel_Plate_Thickness	Edges_Index	Empty_Index	Square_Index	Outside_X_Index	Edges_X_Index	Edges_Y_Index	Outside_Global_Index	LogOfAreas	Log_X_Index	Log_Y_Index	Orientation_Index	Luminosity_Index	SigmoidOfAreas	Pastry	Z_Scratch	K_Scatch	Stains	Dirtiness	Bumps	Other_Faults
42	50	270900	270944	267	17	44	24220	76	108	1687	1	0	80	0.0498	0.2415	0.1818	0.0047	0.4706	1.0	1.0	2.4265	0.9031	1.6435	0.8182	-0.2913	0.5822	1	0	0	0	0	0	0
645	651	2538079	2538108	108	10	30	11397	84	123	1687	1	0	80	0.7647	0.3793	0.2069	0.0036	0.6	0.9667	1.0	2.0334	0.7782	1.4624	0.7931	-0.1756	0.2984	1	0	0	0	0	0	0

label	prediction
1077805.577726322	905857.7217549165
299863.0401311839	382461.210155827
599504.0192866956	450579.3222529907
899609.3001428025	760635.9607011373
723750.0652577134	578777.0801798133
968411.6243946048	842346.5123654515
401148.5687913792	489702.4953861884
539483.3966111792	498151.31364190625
529282.0843920948	492255.5311492714
152071.87474956046	224352.27808677126
798639.6541779223	873972.9143344644
509499.588996669	579349.8334724922
664465.3309873971	665907.1230268287
560598.5384309639	580069.651997698
494742.5435776913	463103.0388433356
319495.66759175994	475360.491742461
1073355.783703826	997068.619075343
862865.8153196024	792958.200356191
852703.2636757497	899070.0560973356
1078016.9396491002	1125023.9679705012
1054606.9845532854	904411.2984304121
809486.7099279496	893943.1397758
747933.4869386142	827518.5119610061
324981.9929926853	483642.168372151
624482.7635866479	714422.7040581186
786430.8996307264	819072.0543287611
920540.6539499458	930085.5051769968
1344406.9301585502	1207680.752889615
959625.5301771795	931452.8310015076
497579.4465868728	525086.5568316998
900851.1631095213	929393.3493556743
852590.3528209794	744039.8788878946
802048.4455314139	845147.4308651472
541953.9056802422	596619.4873849619
896944.2442711681	1040887.2171885697
923830.33486809	698322.8158854521
615055.6757921792	776798.7696506479
961354.287727855	1045074.3039248711
1108406.7859168318	934876.0998357511
867714.3838490517	681711.3081251266