liner_regression_test_2

./data/predict2.xls

• column1 入炉煤Ad
• column2 入炉煤S
• column3 入炉煤V
• column4 焦炭Ad
• column5 焦炭S

import xlrd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib as mpl
import pandas as pd
from pandas import Series, DataFrame
from sklearn.linear_model import LinearRegression  # 线性回归

# 在行内显示(默认)
# %matplotlib inline
mpl.rcParams['font.sans-serif'] = ['SimHei']
mpl.rcParams['axes.unicode_minus'] = False

# 打开execl
workbook = xlrd.open_workbook('./data/predict2.xls')

Data_sheet = workbook.sheets()[0]  # 通过索引获取第一个sheet

rowNum = Data_sheet.nrows  # sheet行数
colNum = Data_sheet.ncols  # sheet列数

# 获取所有单元格的内容
# 去掉第一行和第一列
list = []
for i in range(rowNum - 1):
    rowlist = []
    for j in range(colNum - 1):
        rowlist.append(Data_sheet.cell_value(i + 1, j + 1))
    list.append(rowlist)
# print(list)

# 计算list，分别提取第1、2、3、...、n列，每列单独成为一个list
dataMat = np.array(list)
computed_list = []
for i in range(colNum - 1):
    if i == colNum - 1:
        computed_list.append(dataMat[:,i:])
    else:
         computed_list.append(dataMat[:,i:i+1])

# print(computed_list)

# column1 入炉煤Ad
# column2 入炉煤S
# column3 入炉煤V
# column4 焦炭Ad
# column5 焦炭S

# 绘制散点图
# plt.title('入炉煤Ad-焦炭Ad')
# plt.xlabel('入炉煤Ad')
# plt.ylabel('焦炭Ad')
# plt.scatter(computed_list[0], computed_list[3])
# plt.show()

# plt.title('入炉煤V-焦炭Ad')
# plt.xlabel('入炉煤V')
# plt.ylabel('焦炭Ad')
# plt.scatter(computed_list[2], computed_list[3])
# plt.show()

# plt.scatter(computed_list[0], computed_list[3])
# plt.scatter(computed_list[2], computed_list[3])
# plt.show()

# 入炉煤Ad、入炉煤V
X1 = dataMat[:,[0,2]]
# 焦炭Ad
y1 = computed_list[3]

# ========线性回归========
model = LinearRegression(copy_X=True, fit_intercept=True, n_jobs=1, normalize=False)
model.fit(X1, y1)   # 线性回归建模
# print('系数矩阵:\n',model.coef_)
# print('截距:\n',model.intercept_)
print('焦炭Ad = ({0}) + ({1})入炉煤Ad + ({2})入炉煤V'.format(round(model.intercept_[0], 3),round(model.coef_[0][0], 3),round(model.coef_[0][1],3)))
# print()

# 入炉煤S、入炉煤V
X2 = dataMat[:,[1,2]]
# 焦炭S
y2 = computed_list[4]
model.fit(X2, y2)   # 线性回归建模
# print('系数矩阵:\n',model.coef_)
# print('截距:\n',model.intercept_)
print('焦炭S = ({0}) + ({1})入炉煤S + ({2})入炉煤V'.format(round(model.intercept_[0], 3),round(model.coef_[0][0], 3),round(model.coef_[0][1],3)))

焦炭Ad = (13.43) + (0.207)入炉煤Ad + (-0.068)入炉煤V
焦炭S = (0.56) + (0.588)入炉煤S + (-0.008)入炉煤V