1 问题概述
数据集data.xlsx中包含238行的深度、岩性描述、水饱和度、油饱和度、岩心和毛管等相关参数信息，挑取有较好线性相关性的信息用以拟合水平渗透率。
任务要求：
- 将所给数据集进行预处理，优选特征后切分成训练集和测试集（建议按4:1切分）；
- 使用Python或自己熟知的语言对训练数据建模（模型不限)；
- 使用建立的模型对测试集的渗透率进行预测，将预测渗透率值作为最后一列合并到测试集数据表中，并计算平均误差。
下面结合任务要求展开本次回归分析实验的介绍。
2 数据预处理
1 剔除无效数据
首先加载数据集，将'岩性描述'一列剔除(汉字无法被拟合)，'油饱和度,%','水饱和度,%'残缺数据过多，故剔除；'孔隙度,%'和'水平渗透率,10-3μm2'中含有空余数据，将空数据所在行整体剔除，实现代码如下：
data = pd.read_excel(r'G:\Loong_2021\bd_fhw\data.xlsx')#加载数据集
data = data.dropna(subset=['孔隙度,%', '水平渗透率,10-3μm2']) #剔除无效行数据
data = data.drop(['岩性描述','水饱和度,%','油饱和度,%']) #剔除无效列数据
2 数据分析及选取
利用seaborn包进行数据的可视化呈现。
seaborn是基于Matplotlib的Python数据可视化库。它提供了一个高级界面，用于绘制引人入胜且内容丰富的统计图形，同时对Matplotlib进行了更高级的API封装，从而使作图更加容易；
seaborn是针对统计绘图的，能满足数据分析90%的绘图需求，需要复杂的自定义图形还需要使用到Matplotlib。
seaborn中的相关性热点图，初步发现数据中包含的线性关系。corr(x,y)是相关系数，用来刻画二维随机变量两个分量间相互关联程度。
corr(x,y) 在-1到1之间，也就是说相关系数介于-1到1之间，并可以对它作一下几个说明明：corr(x,y) =0则称X,Y不相关，不相关是指X,Y没有线性关系，但也有可能有其他关系，比如平方关系，立方关系等 。corr(x,y) =1，则称X与Y完全正相关，corr(x,y) =-1，则称X,Y完全负相关。相关性系数公式
下面展示相关性热点图实现代码及展示效果解读。
import seaborn as sns
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False #汉字编码
sns.heatmap(data.corr()) #相关性热点图,corr()判断相关性
上图中横纵坐标对称，颜色越淡代表相关性（正）越强，越深则代表负相关行越强。根据相关性热点图可初步选定正负相关性较好的数据，如孔隙度、总进汞量、最大进汞饱和度、残留汞饱和度、最大饱和度增量、峰态和最大增量对应直径具有较好的正相关性，而退汞效率具有较好的负相关性。故首先选择如上数据进行训练拟合,并划分训练集和测试集。
x = data[['孔隙度,%','总进汞量,ml','残留汞饱和度,%','最大饱和度增量,%','峰态','最大增量对应直径,μm','最大进汞饱和度,%','退汞效率,%']]
y = data['水平渗透率,10-3μm2']
from sklearn.model_selection import train_test_split #sklearn中包含数据集切分的函数train_test_split,直接调取使用
x_train, x_test, y_train, y_test = train_test_split(x,y,test_size=0.2) #random_state
#测试集参数设0.2，及1：4切分
3 线性回归模型
sklearn库中有很多线性回归的模型，本次作业选取其中的LinearRegression和Lasso以及最小二乘法进行数据测试。
LinearRegression 基本形式
Loss Function梯度下降
原文链接
最小二乘法
原文链接
Lasso回归
Lasso是在最小二乘回归的基础上加上L1正则表达式得到，L1正则表达式同样可以防止模型过拟合。
sklearn对线性回归方法已经做好了封装，调用起来非常方便，如下：
from sklearn.linear_model import LinearRegression #引入LinearRegression模型
#from sklearn.linear_model import Lasso
#import statsmodels.api as sm
lr = LinearRegression()
lr.fit(x_train,y_train) #用LinearRegression回归模型拟合训练集
# las = Lasso(alpha=0.01)
# las.fit(x_train,y_train) #用Lasso回归模型拟合训练集
#model_ols = sm.OLS(y,x).fit() #最小二乘拟合
print("train=",lr.score(x_train,y_train)) #对回归效果进行打分，lr.score方法及计算R的平方
train= 0.6523736250935579
对拟合的相关系数(coef)进行输出.
coeff_df = pd.DataFrame(lr.coef_,x.columns,columns=['Coefficient']) #coef_即W1,...,Wn；intercept_即W0
coeff_df
用拟合的回归模型对测试集进行预测.
predictions=lr.predict(x_test)
predictions.reshape(-1)
array([-2.59539925e+01, 4.31076612e+01, 1.30399742e+02, 5.04344867e+02,
-3.63570322e+01, 1.66409337e+02, 1.48233902e+02, 3.55917832e+02,
-2.06434297e+02, -8.96603664e+00, 4.48635127e+02, 9.75490295e+02,
3.90789114e+01, 1.58733681e+02, 3.00272800e+02, 1.40844112e+03,
2.18009116e+02, -1.09460260e+02, -6.74002294e-01, -6.27163724e+01,
5.40043178e+02, 7.71473581e+02, 5.46043428e+02, -1.25653783e+02,
1.95655312e+03, 2.33817042e+02, 3.39778641e+02, 2.69956394e+02,
-9.97752566e+01, 4.11430872e+02, 3.75070273e+02, 1.13961297e+03,
9.64772236e+01, 1.11353584e+02, 1.34962988e+03, -1.15255627e+02,
5.24330593e+02, -2.21775276e+02, 4.39584814e+02, -1.56036405e+02,
9.57150250e+02, 3.19622873e+02, -1.06216261e+01, 3.56941046e+02,
7.05463731e+02])
4 预测效果评价
得到预测数据后，可以发现预测效果与真实情况存在偏差。首先预测结果中存在少量负值，这与实际情况中渗透率为正不符。为了更加直观的展示预测结果与真实值的分布情况，我画出了测试集的真实值与对应的预测值之差，以此观察偏差效果,并计算平均绝对误差。
sns.distplot((y_test-predictions),bins=50) #y_test-predictions概率分布
predictions_all=lr.predict(x)
# predictions.reshape(-1)
print("平均误差：",(sum(abs(y-predictions_all)))/len(predictions_all)) #计算平均误差
平均误差： 187.7708660295839
由图3可知，y_test-predictions的值主要分布在0附近的范围，大致呈现正态分布，表明预测结果有效，但仍有偏差较大的数据存在。计算平均误差得到平均误差为187.77，由于数据集中渗透率数据主要集中在0-1000，故误差较大。
最后完成将预测值输入到Excel表格中的任务。
def insert_pdt(predictions_all): #将预测数据插入到excel表格
wb = openpyxl.load_workbook(r'G:\Loong_2021\bd_fhw\data.xlsx')
sheetNames = wb.sheetnames
print(sheetNames)
sheet1 = wb.worksheets[0]
for i in range(len(predictions_all)):
sheet1.cell(i+2, 24).value=predictions_all[i] #excel中单元格为X2开始，即第24列，第2行
wb.save(r'G:\Loong_2021\bd_fhw\data.xlsx')
insert_pdt(predictions_all)
