# -*- coding: utf-8 -*- import os from keras.optimizers import Adam from sklearn import preprocessing import scipy.io as scio from keras.utils.np_utils import to_categorical from keras.models import Model from keras.layers import Dense, Input from scipy.io import loadmat from keras.models import Sequential from keras.regularizers import l2 import pandas as pd import matplotlib.pyplot as plt from keras.models import Model from keras.layers import Dense, Input,Lambda,Dropout from keras.layers.normalization import BatchNormalization from scipy.io import loadmat from keras import backend as K import numpy as np from keras.losses import mean_squared_error,categorical_crossentropy from keras.callbacks import ModelCheckpoint from keras.models import load_model if __name__ == '__main__': myclasslabel= ['00','04','05'] classes=len(myclasslabel) #150 120 110 50 90 70 50 30 reg_par = 1e-8 train_num_pre=450 test_num_pre=750 save_dir = os.path.join(os.getcwd()) #model_name = 'te_trained_model.h5' #导入TE数据 for index in range(len(myclasslabel)): path0=r'D:\sxt\GraduatePrograms\Tieling\lu\TE_process\TE_process\d' path1=myclasslabel[index] path2='.dat' path3='_te.dat' path=path0 path1 path2 test_path=path0 path1 path3 D00 = pd.read_csv(path,header=None,sep='\s ') test_D00 = pd.read_csv(test_path,header=None,sep='\s ') D00=np.matrix(D00) D00=D00[29:29 train_num_pre,:] test_D00=np.matrix(test_D00) test_D00=test_D00[190:190 test_num_pre,:] if index==0: x_train_pre=D00 x_test_pre=test_D00 else: x_train_pre=np.vstack((x_train_pre,D00)) x_test_pre=np.vstack((x_test_pre,test_D00)) x_train=x_train_pre x_test=x_test_pre train_num=train_num_pre test_num=test_num_pre #以下产生one-hot 编码 c=np.matrix(np.eye(classes)[0]) train_y=np.repeat(c,train_num,axis=0) test_y=np.repeat(c,test_num,axis=0) for i in range(1,classes): c1=np.matrix(np.eye(classes)[i]) y1=np.repeat(c1,train_num,axis=0) y2=np.repeat(c1,test_num,axis=0) train_y=np.vstack((train_y,y1)) test_y=np.vstack((test_y,y2)) onecolumn_train_y=np.argmax(train_y,axis=1) onecolumn_test_y=np.argmax(test_y,axis=1) scaler_train = preprocessing.StandardScaler().fit(x_train) #数据标准化 x_train=scaler_train.transform(x_train) x_test=scaler_train.transform(x_test) #以上是数据处理 # # 第一个自编码器预训练 oridim=x_train.shape[1] # this is our input placeholder input_x = Input(shape=(oridim,),name='main_input') # encoder layers x1 = Dense(200, activation='relu', name='layer_x1',kernel_regularizer=l2(reg_par))(input_x) #200 x1 =BatchNormalization()(x1) x2 = Dense(150, activation='relu', name='layer_x2', kernel_regularizer=l2(reg_par))(x1) #150 x2 =BatchNormalization()(x2) x3 = Dense(50, activation='relu', name='layer_x3', kernel_regularizer=l2(reg_par))(x2) #100 x3 =BatchNormalization()(x3) encoded_output = Dense(classes, activation='softmax',name='layer_encoded')(x3) #这可以直接输出每个类别的概率，可以将encoded_output 或者 x3作为特征输入som中可视化 # construct the autoencoder model autoencoder =Model(input=input_x, output=encoded_output) autoencoder.compile(optimizer='adam', loss='categorical_crossentropy',metrics=['accuracy']) filepath='weights.best.hdf5' checkpoint = ModelCheckpoint(os.path.join(save_dir, filepath), monitor='val_accuracy', verbose=1, save_best_only=True, mode='max') # training history =autoencoder.fit(x_train, train_y, epochs=300, batch_size=32, validation_data=(x_test, test_y), shuffle=True, callbacks=[checkpoint])#用来打乱数据集，每次都会以不同的顺序返回 # print(history.history.keys()) # # Save model and weights # if not os.path.isdir(save_dir): # os.makedirs(save_dir) # model_path = os.path.join(save_dir, filepath) # autoencoder.save(model_path) # print('Saved trained model at %s ' % model_path) # Summarize history for accuracy plt.plot(history.history['accuracy']) plt.plot(history.history['val_accuracy']) plt.title('model accuracy') plt.ylabel('accuracy') plt.xlabel('epoch') plt.legend(['train', 'test'], loc='upper left') plt.show() # Summarize history for loss plt.plot(history.history['loss']) plt.plot(history.history['val_loss']) plt.title('model loss') plt.ylabel('loss') plt.xlabel('epoch') plt.legend(['train', 'test'], loc='upper left') plt.show() #导入最佳模型 bestmodel=load_model(filepath) #bestmodel=autoencoder x_train_reduc= bestmodel.predict(xtrain) x_test_reduc = bestmodel.predict(x_test) #计算每个类别的准确 #计算测试阶段 每一类的准确率 test_acc=np.ones(classes) for i in range (classes): test0=np.argmax(x_test_reduc[i*test_num:(i+1)*test_num,:],axis=1) test_acc[i]=np.mean(np.equal(test0,i)) acc_mean=np.mean(test_acc) #将最后一个隐藏层的特征送去可视化 输入SOM中，这里送入tsne可视化 x3_layer = Model(inputs=bestmodel.input, outputs=bestmodel.get_layer('layer_x3').output) x3_output = x3_layer.predict(x_train) #可以将该特征保存 然后用matlab的SOM进行可视化 x3_output_test = x3_layer.predict(x_test) #需要可视化的数据 x_train_psne=x3_output x_test_psne=x3_output_test mycolors =['red','green','blue','black','peru','yellow','purple','fuchsia','indigo','turquoise','grey','brown', 'slateblue','darkslategray','deeppink','violet','lime','mediumseagreen','darkolivegreen','olive','saddlebrown','teal'] from sklearn import manifold #降维 tsne = manifold.TSNE(n_components=2) embedding_train = tsne.fit_transform(x_train_psne) for i in range(classes): plt.scatter( embedding_train[i*train_num:(i+1)*train_num,0], embedding_train[i*train_num:(i+1)*train_num,1], c=mycolors[i]) plt.title("tsne-train") plt.show() embedding_test = tsne.fit_transform(x_test_reduc) for i in range(classes): plt.scatter( embedding_test [i*test_num:(i+1)*test_num,0], embedding_test[i*test_num:(i+1)*test_num,1], c=mycolors[i]) plt.title("tsne-test") plt.show() # scio.savemat('C:\\Users\\Administrator.000\\Desktop\\sae相关论文\\demoofpaper\\matlab画图专用\\LOW_embedded.mat', {'Lowtrain_feature': embedding_train,'Lowtest_feature':embedding_train}) # scio.savemat('C:\\Users\\Administrator.000\\Desktop\\sae相关论文\\demoofpaper\\matlab画图专用\\Label.mat', {'train_label': onecolumn_train_y,'test_label':onecolumn_train_y}) #
小项目
# -*- coding: utf-8 -*-
#sql="select id,title,sid from 表 where [id] in (select MAX([id]) from 表 group by sid) order by id desc"#定时更新成分时间 可修改
#使用糖转化率作为分类目标，因此不必考虑原材料变化所带来的影响，一个模型即可不必分原材料建立多个模型
# print(f"result_0h: {result_0h}")
from itertools import chain
from PySide2.QtWidgets import QApplication, QTableWidgetItem,QDesktopWidget, QHeaderView
from PySide2.QtUiTools import QUiLoader
import pyqtgraph as pg
import sys
from PySide2.QtCore import Qt, QTimer, QDate
from PySide2 import QtCore, QtGui, QtWidgets
##from PySide2.QtWidgets import QWidget, QLabel, QPushButton, QVBoxLayout, QApplication
#from PySide2 import QtGui
import PySide2
import MySQLdb
import pickle
import numpy as np
import pandas as pd
import datetime
import time
import os
import ctypes
from sklearn import preprocessing
from sklearn.neighbors import KNeighborsClassifier
from random import randrange as rand
#分类模型在线训练部分
from sklearn import preprocessing
from sklearn.discriminant_analysis import LinearDiscriminantAnalysis
import matplotlib.pyplot as plt
#from minisom import MiniSom
from matplotlib.colors import ListedColormap
from sklearn.cross_decomposition import PLSRegression
from sklearn import datasets
from sklearn.model_selection import GridSearchCV
import numpy as np
from imblearn.over_sampling import SMOTE
from collections import Counter
from sklearn.model_selection import train_test_split
#计算精度
from sklearn.metrics import precision_score
from catboost import CatBoostRegressor,CatBoostClassifier
ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID("myappid")
dirname = os.path.dirname(PySide2.__file__)
plugin_path = os.path.join(dirname, 'plugins', 'platforms')
os.environ['QT_QPA_PLATFORM_PLUGIN_PATH'] = plugin_path
#number_batch 存储最近20个批次的罐次序号 在read_onlinedata每1min更新一次
number_batch = [0]*20
number_can = {}
#last_len 判断温度是否更新了
last_len = 0
res_0h = 0
res_8h = 0
res_24h = 0
res_40h = 0
a0 = 0
a8 = 0
a24 = 0
a40 = 0
result_0h = []
result_8h = []
result_24h = []
result_40h = []
#####模型在线更新 新样本区分类别后的预测值和真实值#########
result40h_calss1_pre = []
result40h_calss1_true = []
result40h_calss2_pre = []
result40h_calss2_true = []
result24h_calss1_pre = []
result24h_calss1_true = []
result24h_calss2_pre = []
result24h_calss2_true = []
result8h_calss1_pre = []
result8h_calss1_true = []
result8h_calss2_pre = []
result8h_calss2_true = []
result0h_calss1_pre = []
result0h_calss1_true = []
result0h_calss2_pre = []
result0h_calss2_true = []
# smote过采样之后新样本的自变量（对应各个时刻的检验数据）和因变量（类别）
new_sample_calss1_40h = []
new_sample_calss1_24h = []
new_sample_calss1_8h = []
new_sample_calss1_0h = []
new_sample_calss2_40h = []
new_sample_calss2_24h = []
new_sample_calss2_8h = []
new_sample_calss2_0h = []
new_sampley_calss1_40h = []
new_sampley_calss1_24h = []
new_sampley_calss1_8h = []
new_sampley_calss1_0h = []
new_sampley_calss2_40h = []
new_sampley_calss2_24h = []
new_sampley_calss2_8h = []
new_sampley_calss2_0h = []
# 将UI界面放置在屏幕正中央
def center_window(widget):
window = widget.window()
window.setGeometry(
QtWidgets.QStyle.alignedRect(
QtCore.Qt.LeftToRight,
QtCore.Qt.AlignCenter,
window.size(),
QtGui.QGuiApplication.primaryScreen().availableGeometry(),
# QtGui.QGuiApplication.screens().availableGeometry(),
),
)
class Model: # 程序执行后首先会将新样本使用旧模型的真实值预测值对比进行对比，其次将新样本旧样本混合更新模型
def __init__(self):
#调试使用
# self.newsample_match()
# self.classification_0h()
# self.calssmoudle1_train()
# self.calssmoudle2_train()
self.timer_model = QTimer()
self.timer_model.timeout.connect(self.month_change) # 计时时间查询是否达到一个多月的时间间隔
self.timer_model.start(60 * 1000 * 60 * 24) # 以毫秒计 一天判断一次是否达更新模型的时间要求
#新样本的匹配
def newsample_match(self):
# 首先读取乙醇表中的批次和对应实际值数据
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
cursor_ethanol = db.cursor()
sqltem = "select ID,罐次,乙醇出罐浓度 from ethanol where 罐次 is not null and 乙醇出罐浓度 is not null"
try:
cursor_ethanol.execute(sqltem)
ethanol = cursor_ethanol.fetchall()
except:
ethanol = []
print("乙醇出罐浓度数据读取失败")
cursor_ethanol.close()
db.close()
ethanol_array = np.array(ethanol) # 列表元组
if ethanol:
row = cursor_ethanol.rowcount # 取得记录个数，用于设置表格的行数
# 读取初始时刻检验数据，调用0h分类模型（xgb模型）判断属于哪一类别
for i in range(row): # 对所有批次逐一处理
tank_number = str(ethanol_array[i][1])
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
cursor_inspection = db.cursor()
sqlprediction0h = "select * from prediction0h where tank_no ='%s'" % tank_number
try:
cursor_inspection.execute(sqlprediction0h)
inspection = cursor_inspection.fetchone()
except:
inspection = []
print("初始时刻数据读取失败")
cursor_inspection.close()
db.close()
with open('scaler_train_0h_classification', 'rb')as f:
scaler_test = pickle.load(f) # 导入测试数据的标准化模型
with open('ldamodel_0h_classification', 'rb')as f:
lda_test = pickle.load(f) # 导入xgb模型
if inspection:
value = float(ethanol[i][2]) * 1.195 #乙醇体积比*1.195就是实际出罐的乙醇浓度
inspection_test = scaler_test.transform(np.matrix(inspection[5:]))
prob_inspection = lda_test.predict(inspection_test)
# 1：表示第一批 2：表示第二批
if prob_inspection[0] == 1:
if value >= 15.75:
label = 1
elif (15.4 < value) & (value < 15.75):
label = 2
elif value <= 15.4:
label = 3
# 读prediction40表数据 输入模型获得预测值
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
# 判断40h的表中是否有此批次如果有此批次，确定发酵阶段
cursor40h = db.cursor()
sql40h = "select * from prediction40h where tank_no ='%s'" % tank_number
try:
res_40h = cursor40h.execute(sql40h)
if res_40h == 1:
result_40h = cursor40h.fetchone()
else:
result_40h=[]
except:
result_40h = []
print("Error: handle unable to fecth 40h data1")
cursor40h.close()
db.close()
# 读prediction24表数据 输入模型获得预测值
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
# 判断24h的表中是否有此批次如果有此批次，确定发酵阶段
cursor24h = db.cursor()
sql24h = "select * from prediction24h where tank_no ='%s'" % tank_number
try:
res_24h = cursor24h.execute(sql24h)
if res_24h == 1:
result_24h = cursor24h.fetchone()
else:
result_24h=[]
except:
result_24h = []
print("Error: handle unable to fecth 24h data1")
cursor24h.close()
db.close()
# 读prediction8表数据 输入模型获得预测值
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
# 判断8h的表中是否有此批次如果有此批次，确定发酵阶段
cursor8h = db.cursor()
sql8h = "select * from prediction8h where tank_no ='%s'" % tank_number
try:
res_8h = cursor8h.execute(sql8h)
if res_8h == 1:
result_8h = cursor8h.fetchone()
else:
result_8h=[]
except:
result_8h = []
print("Error: handle unable to fecth 8h data1")
cursor8h.close()
db.close()
# 该批次对应的检验数据不为空才进行处理 如果为空说明此发酵时刻没有匹配到数据没必要进行处理
if result_40h:
new_sample_calss1_40h.append(result_40h[5:]) # 新样本的自变量
new_sampley_calss1_40h.append(value) # 新样本的因变量
# 调用xgb模型
with open('scaler_train_40h', 'rb')as f:
scaler_test = pickle.load(f) # 导入测试数据的标准化模型
with open('ldamodel_40h', 'rb')as f:
lda_test = pickle.load(f) # 导入LDA模型
# 测试数据标准化
x_test = scaler_test.transform(np.matrix(result_40h[5:]))
prob = lda_test.predict(x_test)
result40h_calss1_pre.append(int(prob))#模型的预测值
result40h_calss1_true.append(label)#实际的类别
if result_24h:
new_sample_calss1_24h.append(result_24h[5:]) # 新样本的自变量
new_sampley_calss1_24h.append(value) # 新样本的因变量
# 调用FDA模型
with open('scaler_train_24h', 'rb')as f:
scaler_test = pickle.load(f) # 导入测试数据的标准化模型
with open('ldamodel_24h', 'rb')as f:
lda_test = pickle.load(f) # 导入LDA模型
# 测试数据标准化
x_test = scaler_test.transform(np.matrix(result_24h[5:]))
# 获得测试数据输出的概率投影
prob = lda_test.predict(x_test)
result24h_calss1_pre.append(int(prob))
result24h_calss1_true.append(label)
if result_8h:
new_sample_calss1_8h.append(result_8h[5:]) # 新样本的自变量
new_sampley_calss1_8h.append(value) # 新样本的因变量
# 调用FDA模型
with open('scaler_train_8h', 'rb')as f:
scaler_test = pickle.load(f) # 导入测试数据的标准化模型
with open('ldamodel_8h', 'rb')as f:
lda_test = pickle.load(f) # 导入LDA模型
# 测试数据标准化
x_test = scaler_test.transform(np.matrix(result_8h[5:]))
# 获得测试数据输出的概率投影
prob = lda_test.predict(x_test)
result8h_calss1_pre.append(int(prob))
result8h_calss1_true.append(label)
#如果此批次属于初始时刻，进行以下处理
if inspection:
new_sample_calss1_0h.append(inspection[5:]) # 新样本的自变量
new_sampley_calss1_0h.append(value) # 新样本的因变量
# 调用FDA模型
with open('scaler_train_0h', 'rb')as f:
scaler_test = pickle.load(f) # 导入测试数据的标准化模型
with open('ldamodel_0h', 'rb')as f:
lda_test = pickle.load(f) # 导入LDA模型
# 测试数据标准化
x_test = scaler_test.transform(np.matrix(inspection[5:]))
# 获得测试数据输出的概率投影
prob = lda_test.predict(x_test)
result0h_calss1_pre.append(int(prob)) # 将预测值添加到相应的预测值列表
result0h_calss1_true.append(label) # 将真实值添加到相应的真实值列表
# 类别二新样本匹配
elif prob_inspection[0] == 2:
if value >= 14.6:
label = 1
elif (13.8 < value) & (value < 14.6):
label = 2
elif value <= 13.8:
label = 3
# 读prediction40表数据 输入模型获得预测值
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
# 判断40h的表中是否有此批次如果有此批次，确定发酵阶段
cursor40h = db.cursor()
sql40h = "select * from prediction40h where tank_no ='%s'" % tank_number
try:
res_40h = cursor40h.execute(sql40h)
if res_40h == 1:
result_40h = cursor40h.fetchone()
else:
result_40h = []
except:
result_40h = []
# res_40h = 0
print("Error: handle unable to fecth 40h data2")
cursor40h.close()
db.close()
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
# 判断24h的表中是否有此批次如果有此批次，确定发酵阶段
cursor24h = db.cursor()
sql24h = "select * from prediction24h where tank_no ='%s'" % tank_number
try:
res_24h = cursor24h.execute(sql24h)
if res_24h == 1:
result_24h = cursor24h.fetchone()
else:
result_24h = []
except:
result_24h = []
print("Error: handle unable to fecth 24h data2")
cursor24h.close()
db.close()
# 读prediction8表数据 输入模型获得预测值
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
# 判断8h的表中是否有此批次如果有此批次，确定发酵阶段
cursor8h = db.cursor()
sql8h = "select * from prediction8h where tank_no ='%s'" % tank_number
try:
res_8h = cursor8h.execute(sql8h)
if res_8h == 1:
result_8h = cursor8h.fetchone()
else:
result_8h = []
except:
result_8h = []
print("Error: handle unable to fecth 8h data")
cursor8h.close()
db.close()
if result_40h:
# 将读到的新样本加入到空矩阵中，方便和原来的检验数据进行合并
new_sample_calss2_40h.append(result_40h[5:]) # 新样本的自变量
new_sampley_calss2_40h.append(value) # 新样本的因变量
# 调用xgb模型
with open('scaler_train2_40h', 'rb')as f:
scaler_test = pickle.load(f) # 导入测试数据的标准化模型
with open('ldamodel2_40h', 'rb')as f:
lda_test = pickle.load(f) # 导入LDA模型
# 测试数据标准化
x_test = scaler_test.transform(np.matrix(result_40h[5:]))
# 获得测试数据输出的概率投影
prob = lda_test.predict(x_test)
result40h_calss2_pre.append(int(prob))
result40h_calss2_true.append(label)
if result_24h:
new_sample_calss2_24h.append(result_24h[5:])
new_sampley_calss2_24h.append(value)
# 调用xgb模型
with open('scaler_train2_24h', 'rb')as f:
scaler_test = pickle.load(f) # 导入测试数据的标准化模型
with open('ldamodel2_24h', 'rb')as f:
lda_test = pickle.load(f) # 导入LDA模型
# 测试数据标准化
x_test = scaler_test.transform(np.matrix(result_24h[5:]))
# 获得测试数据输出的概率投影
prob = lda_test.predict(x_test)
result24h_calss2_pre.append(int(prob))
result24h_calss2_true.append(label)
if result_8h: # 不为空才进行下面的预测 否则得不到预测值没办法绘制曲线
new_sample_calss2_8h.append(result_8h[5:])
new_sampley_calss2_8h.append(value)
# 调用xgb模型
with open('scaler_train2_8h', 'rb')as f:
scaler_test = pickle.load(f) # 导入测试数据的标准化模型
with open('ldamodel2_8h', 'rb')as f:
lda_test = pickle.load(f) # 导入LDA模型
# 测试数据标准化
x_test = scaler_test.transform(np.matrix(result_8h[5:]))
# 获得测试数据输出的概率投影
prob = lda_test.predict(x_test)
result8h_calss2_pre.append(int(prob))
result8h_calss2_true.append(label)
if inspection:
new_sample_calss2_0h.append(inspection[5:])
new_sampley_calss2_0h.append(value)
# 调用FDA模型
with open('scaler_train2_0h', 'rb')as f:
scaler_test = pickle.load(f)
with open('ldamodel2_0h', 'rb')as f:
lda_test = pickle.load(f)
# 测试数据标准化
x_test = scaler_test.transform(np.matrix(inspection[5:]))
# 获得测试数据输出的概率投影
prob = lda_test.predict(x_test)
# prob = lda_test.predict_proba(x_test)
# a40 = round(prob[0][0], 3)
# b40 = round(prob[0][1], 3)
# c40 = round(prob[0][2], 3)
result0h_calss2_pre.append(int(prob))
result0h_calss2_true.append(label)
def classification_0h(self):
# 初始时刻分类模型的构建D:\sxt\GraduatePrograms\Tieling\finalsoftware\1206\pyside1209
select_classification = 0 # 0 是0h 8是 8h 24是24h 40是40h 选择训练的是哪部分数据
# data = pd.read_excel(
# 'D:\\TL_Optimization_Model\\TL_Process_Interface\\regression_liquefaction_yeast_out.xlsx',
# sheet_name="Sheet1", )
data = pd.read_excel(
'regression_liquefaction_yeast_out.xlsx',
sheet_name="Sheet1", )
X = data.iloc[:, 1:38] # 取1~36列数据
y = data.loc[:, '乙醇_体积比（出罐）']
# 将数据合并之后进行样本的过采样
sos = SMOTE(random_state=0)
X_resampled, y_resampled = sos.fit_resample(X, y)
print('SMOTE过采样后，训练集 y_resampled 中的分类情况：{}'.format(Counter(y_resampled)))
X_train, X_test, y_train, y_test = train_test_split(X_resampled, y_resampled, test_size=0.2)
# 测试数据标准化
scaler_train = preprocessing.StandardScaler().fit(X_train)
X = scaler_train.transform(X_train)
# lda对测试数据进行聚类，并绘制图像
lda = CatBoostClassifier(iterations=1000,
learning_rate=0.25,
random_seed=888,
depth=8, verbose=100)
#lda = LinearDiscriminantAnalysis(n_components=None)由fda模型改为xgb模型
lda.fit(X, y_train) # 三个label对应三类样本
# X_new = lda.transform(X)#经过转化之后的输入矩阵
# ##绘图数据##
# # 设置X和Y的边界值
# X_train = X_train.values
# X_min, X_max = X_train[:, 0].min() - 1, X_train[:, 0].max() + 1
# y_min, y_max = X_train[:, 1].min() - 1, X_train[:, 1].max() + 1
# # 使用meshgrid函数返回X和Y两个坐标向量矩阵
# h = .01
# xx, yy = np.meshgrid(np.arange(X_min, X_max, h), np.arange(y_min, y_max, h))
# # 设置colormap颜色
# cm_bright = ListedColormap(['#D9e021', '#0D8ECF'])
# plt.scatter(X_train[:, 0], X_train[:, 1], c=y_train, cmap=cm_bright)
# plt.title('样本类别区分模型训练结果')
# plt.axis('tight')
# plt.show()
import pickle
# 标准化数据 模型保存
with open('scaler_train_' + str(select_classification) + 'h_classification', 'wb')as f:
pickle.dump(scaler_train, f)
with open('scaler_train_' + str(select_classification) + 'h_classification', 'rb')as f:
scaler_test = pickle.load(f) # 导入测试数据的标准化模型
with open('ldamodel_' + str(select_classification) + 'h_classification', 'wb')as f:
pickle.dump(lda, f)
with open('ldamodel_' + str(select_classification) + 'h_classification', 'rb')as f:
lda2 = pickle.load(f) # 导入LDA模型
# 模型的测试
# 模型的测试程序
x_test = scaler_test.transform(X_test)
prob = lda2.predict(x_test)
sim_time = np.arange(len(x_test))
# ##绘图数据##
# plt.figure(figsize=(16, 12))
# plt.plot(sim_time, y_test, '*-', sim_time, prob, 'o-.')
# plt.xlabel('批次', fontsize=14)
# plt.ylabel('样本类别（1：calss1 2:calss2）', fontsize=14)
# plt.title('样本分类真实值与预测值对比' + str(select_classification) + 'h', fontsize=14)
# plt.legend(['true', 'predicted'])
# plt.show()
# #train_fig = 'classification_true_predicted_calss20h.png'
# #plt.savefig(train_fig, dpi=500)
print(f"样本类别模型准确率: {precision_score(y_test, prob, average='micro')}")
if precision_score(y_test, prob, average='micro') > 0.8:
pass #继续执行程序
else:
self.classification_0h() #不满足要求再次进行模型训练
# 第一类模型的构建
def calssmoudle1_train(self):
for i in {0, 8, 24, 40}:
select = i
if select == 40:
data = pd.read_excel('sample40hpart1.xlsx',
sheet_name="Sheet1", )
if new_sample_calss1_40h:
X = data.iloc[:, 1:123] # 40h 训练数据
X = np.array(X)
sample40h = np.array(new_sample_calss1_40h)
X = np.vstack((X, sample40h))#新样本自变量融合
y = data.loc[:, '乙醇_体积比（出罐）']
sample40hy = pd.Series(new_sampley_calss1_40h)
y = pd.concat([y, sample40hy])#新样本因变量融合
label = np.zeros((len(X), 1)) # 返回来一个给定形状和类型的用0填充的数组len(x)行，1列
label[np.where(y >= 15.75)] = 1
label[np.where((15.4 < y) & (y < 15.75))] = 2
label[np.where(y <= 15.4)] = 3
# 将数据合并之后进行样本的过采样
sos = SMOTE(random_state=0)
X_resampled, y_resampled = sos.fit_resample(X, label)
print('SMOTE过采样后，训练集 y_resampled 中的分类情况：{}'.format(Counter(y_resampled)))
X_train, X_test, y_train, y_test = train_test_split(X_resampled, y_resampled, test_size=0.2) #划分训练集测试集
elif select == 24:
data = pd.read_excel('sample24hpart1.xlsx',
sheet_name="Sheet1", )
if new_sample_calss1_24h:
X = data.iloc[:, 1:93]
X = np.array(X)
#X = np.concatenate((X1, X2), axis=1)
sample24h = np.array(new_sample_calss1_24h)
X = np.vstack((X, sample24h))
y = data.loc[:, '乙醇_体积比（出罐）']
sample24hy = pd.Series(new_sampley_calss1_24h)
y = pd.concat([y, sample24hy])
label = np.zeros((len(X), 1)) # 返回来一个给定形状和类型的用0填充的数组len(x)行，1列
label[np.where(y >= 15.75)] = 1
label[np.where((15.4 < y) & (y < 15.75))] = 2
label[np.where(y <= 15.4)] = 3
# 将数据合并之后进行样本的过采样
sos = SMOTE(random_state=0)
X_resampled, y_resampled = sos.fit_resample(X, label)
print('SMOTE过采样后，训练集 y_resampled 中的分类情况：{}'.format(Counter(y_resampled)))
X_train, X_test, y_train, y_test = train_test_split(X_resampled, y_resampled, test_size=0.2)
elif select == 8:
data = pd.read_excel('sample8hpart1.xlsx',
sheet_name="Sheet1", )
# 8h训练数据（没有糖化酶、酒母醪、液化醪等数据）
if new_sample_calss1_8h:
X = data.iloc[:, 1:60]
X = np.array(X)
#X2 = data.iloc[:, 82:90]
#X = np.concatenate((X1, X2), axis=1)
sample8h = np.array(new_sample_calss1_8h)
X = np.vstack((X, sample8h))
y = data.loc[:, '乙醇_体积比（出罐）']
sample8hy = pd.Series(new_sampley_calss1_8h)
y = pd.concat([y, sample8hy])
label = np.zeros((len(X), 1)) # 返回来一个给定形状和类型的用0填充的数组len(x)行，1列
label[np.where(y >= 15.75)] = 1
label[np.where((15.4 < y) & (y < 15.75))] = 2
label[np.where(y <= 15.4)] = 3
# 将数据合并之后进行样本的过采样
sos = SMOTE(random_state=0)
X_resampled, y_resampled = sos.fit_resample(X, label)
print('SMOTE过采样后，训练集 y_resampled 中的分类情况：{}'.format(Counter(y_resampled)))
X_train, X_test, y_train, y_test = train_test_split(X_resampled, y_resampled, test_size=0.2)
elif select == 0:
data = pd.read_excel('sample0hpart1.xlsx',
sheet_name="Sheet1", )
if new_sample_calss1_0h:
X = data.iloc[:, 1:38]
X = np.array(X)
sample0h = np.array(new_sample_calss1_0h)
X = np.vstack((X, sample0h))
y = data.loc[:, '乙醇_体积比（出罐）']
sample0hy = pd.Series(new_sampley_calss1_0h)
y = pd.concat([y, sample0hy])
label = np.zeros((len(X), 1)) # 返回来一个给定形状和类型的用0填充的数组len(x)行，1列
label[np.where(y >= 15.75)] = 1
label[np.where((15.4 < y) & (y < 15.75))] = 2
label[np.where(y <= 15.4)] = 3
# 将数据合并之后进行样本的过采样
sos = SMOTE(random_state=0)
X_resampled, y_resampled = sos.fit_resample(X, label)
print('SMOTE过采样后，训练集 y_resampled 中的分类情况：{}'.format(Counter(y_resampled)))
X_train, X_test, y_train, y_test = train_test_split(X_resampled, y_resampled, test_size=0.2)
#匹配到相应阶段的新样本，只要不为空就进行模型的训练
if new_sample_calss1_0h or new_sample_calss1_8h or new_sample_calss1_24h or new_sample_calss1_40h:
# 训练数据标准化
scaler_train = preprocessing.StandardScaler().fit(X_train)
X = scaler_train.transform(X_train)
# lda对测试数据进行聚类，并绘制图像
lda = LinearDiscriminantAnalysis(n_components=2)
lda.fit(X, y_train) # 三个label对应三类样本
X_new = lda.transform(X)
map_color = {1: 'r', 2: 'g', 3: 'b'}
label1 = np.reshape(y_train, (len(y_train),))
label2 = label1.tolist() # 将label转化为列表
color = list(map(lambda x: map_color[x], label2))
# plt.figure(figsize=(16, 12))
# # 将聚类后的样本绘制散点图
# plt.scatter(X_new[:, 0], X_new[:, 1], marker='o', c=color)
# plt.show()
# 标准化数据 模型保存
with open('scaler_train_' + str(select) + 'h', 'wb')as f:
pickle.dump(scaler_train, f)
with open('scaler_train_' + str(select) + 'h', 'rb')as f:
scaler_test = pickle.load(f) # 导入测试数据的标准化模型
with open('ldamodel_' + str(select) + 'h', 'wb')as f:
pickle.dump(lda, f)
with open('ldamodel_' + str(select) + 'h', 'rb')as f:
lda2 = pickle.load(f) # 导入LDA模型
# 模型的测试程序
x_test = scaler_test.transform(X_test)
prob = lda2.predict(x_test)
sim_time = np.arange(len(x_test))
# # 绘制新模型预测值和真实值对比图
# plt.figure(figsize=(16, 12))
# plt.plot(sim_time, y_test, '*-', sim_time, prob, 'o-.')
# plt.xlabel('批次', fontsize=14)
# plt.ylabel('状态分类（1：优 2:中 3：差）', fontsize=14)
# plt.title('Calss2测试集真实值与预测值对比' + str(select) + 'h', fontsize=14)
# plt.legend(['true', 'predicted'])
# plt.show()
# #train_fig = 'test_true_predicted_calss20h.png'
# #plt.savefig(train_fig, dpi=500)
print(f"第一类模型准确率: {precision_score(y_test, prob, average='micro')}")
if precision_score(y_test, prob, average='micro') > 0.40:#绝对准确度＞0.5我们认为满足要求
pass
# # 标准化数据 模型保存
# with open('scaler_train_' + str(select) + 'h', 'wb')as f:
# pickle.dump(scaler_train, f)
# with open('ldamodel_' + str(select) + 'h', 'wb')as f:
# pickle.dump(lda, f)
else:
self.calssmoudle1_train()
def calssmoudle2_train(self): # 构建一个for循环依次对四个模型进行更新
# 第二类模型的构建
for i in {0, 8, 24, 40}:
select2 = i
if select2 == 40:
data = pd.read_excel('sample40hpart2.xlsx',
sheet_name="Sheet1", )
if new_sample_calss2_40h:
X = data.iloc[:, 1:123] # 40h 训练数据
X = np.array(X)
sample40h = np.array(new_sample_calss2_40h)
X = np.vstack((X, sample40h))
y = data.loc[:, '乙醇_体积比（出罐）']
sample40hy = pd.Series(new_sampley_calss2_40h)
y = pd.concat([y, sample40hy])
label = np.zeros((len(X), 1)) # 返回来一个给定形状和类型的用0填充的数组len(x)行，1列
label[np.where(y >= 14.6)] = 1
label[np.where((13.8 < y) & (y < 14.6))] = 2
label[np.where(y <= 13.8)] = 3
# 将数据合并之后进行样本的过采样
sos = SMOTE(random_state=0)
X_resampled, y_resampled = sos.fit_resample(X, label)
print('SMOTE过采样后，训练集 y_resampled 中的分类情况：{}'.format(Counter(y_resampled)))
X_train, X_test, y_train, y_test = train_test_split(X_resampled, y_resampled, test_size=0.2)
elif select2 == 24:
data = pd.read_excel('sample24hpart2.xlsx',
sheet_name="Sheet1", )
if new_sample_calss2_24h:
X = data.iloc[:, 1:93]
# X2 = data.iloc[:, 82:106]
# X = np.concatenate((X1, X2), axis=1)
X=np.array(X)
sample24h = np.array(new_sample_calss2_24h)
X = np.vstack((X, sample24h))
# 给样本加标签1：优 2：中 3：差
y = data.loc[:, '乙醇_体积比（出罐）']
sample24hy = pd.Series(new_sampley_calss2_24h)
y = pd.concat([y, sample24hy])
label = np.zeros((len(X), 1)) # 返回来一个给定形状和类型的用0填充的数组len(x)行，1列
label[np.where(y >= 14.6)] = 1
label[np.where((13.8 < y) & (y < 14.6))] = 2
label[np.where(y <= 13.8)] = 3
# 将数据合并之后进行样本的过采样
sos = SMOTE(random_state=0)
X_resampled, y_resampled = sos.fit_resample(X, label)
print('SMOTE过采样后，训练集 y_resampled 中的分类情况：{}'.format(Counter(y_resampled)))
X_train, X_test, y_train, y_test = train_test_split(X_resampled, y_resampled, test_size=0.2)
elif select2 == 8:
data = pd.read_excel('sample8hpart2.xlsx',
sheet_name="Sheet1", )
# 8h训练数据（没有糖化酶、酒母醪、液化醪等数据）
if new_sample_calss2_8h:
X = data.iloc[:, 1:60]
X = np.array(X)
# X2 = data.iloc[:, 82:90]
# X = np.concatenate((X1, X2), axis=1)
sample8h = np.array(new_sample_calss2_8h)
X = np.vstack((X, sample8h))
y = data.loc[:, '乙醇_体积比（出罐）']
sample8hy = pd.Series(new_sampley_calss2_8h)
y = pd.concat([y, sample8hy])
label = np.zeros((len(X), 1)) # 返回来一个给定形状和类型的用0填充的数组len(x)行，1列
label[np.where(y >= 14.6)] = 1
label[np.where((13.8 < y) & (y < 14.6))] = 2
label[np.where(y <= 13.8)] = 3
# 将数据合并之后进行样本的过采样
sos = SMOTE(random_state=0)
X_resampled, y_resampled = sos.fit_resample(X, label)
print('SMOTE过采样后，训练集 y_resampled 中的分类情况：{}'.format(Counter(y_resampled)))
X_train, X_test, y_train, y_test = train_test_split(X_resampled, y_resampled, test_size=0.2)
elif select2 == 0:
data = pd.read_excel('sample0hpart2.xlsx',
sheet_name="Sheet1", )
if new_sample_calss2_0h:
X = data.iloc[:, 1:38]
X = np.array(X)
sample0h = np.array(new_sample_calss2_0h)
X = np.vstack((X, sample0h))
y = data.loc[:, '乙醇_体积比（出罐）']
sample0hy = pd.Series(new_sampley_calss2_0h)
y = pd.concat([y, sample0hy])
#print(type(y))
#print(y.shape)
label = np.zeros((len(X), 1)) # 返回来一个给定形状和类型的用0填充的数组len(x)行，1列
label[np.where(y >= 14.6)] = 1
label[np.where((13.8 < y) & (y < 14.6))] = 2
label[np.where(y <= 13.8)] = 3
# 将数据合并之后进行样本的过采样
sos = SMOTE(random_state=0)
X_resampled, y_resampled = sos.fit_resample(X, label)
print('SMOTE过采样后，训练集 y_resampled 中的分类情况：{}'.format(Counter(y_resampled)))
X_train, X_test, y_train, y_test = train_test_split(X_resampled, y_resampled, test_size=0.2)
if new_sample_calss2_0h or new_sample_calss2_8h or new_sample_calss2_24h or new_sample_calss2_40h:
# 训练数据标准化
scaler_train = preprocessing.StandardScaler().fit(X_train)
X = scaler_train.transform(X_train)
# 调用XGB模型进行模型训练
# lda = LinearDiscriminantAnalysis(n_components=2)
# lda.fit(X, y_train) # 三个label对应三类样本
xgb = CatBoostClassifier(iterations=1000,
learning_rate=0.25,
random_seed=888,
# custom_metric='F1',
depth=8, verbose=100)
xgb.fit(X, y_train)
# X_new = lda.transform(X)
# map_color = {1: 'r', 2: 'g', 3: 'b'}
# label1 = np.reshape(y_train, (len(y_train),))
# label2 = label1.tolist() # 将label转化为列表
# color = list(map(lambda x: map_color[x], label2))
# plt.figure(figsize=(16, 12))
# # 将聚类后的样本绘制散点图
# plt.scatter(X_new[:, 0], X_new[:, 1], marker='o', c=color)
# plt.show()
# 标准化数据 模型保存
with open('scaler_train2_' + str(select2) + 'h', 'wb')as f:
pickle.dump(scaler_train, f)
with open('scaler_train2_' + str(select2) + 'h', 'rb')as f:
scaler_test = pickle.load(f) # 导入测试数据的标准化模型
with open('ldamodel2_' + str(select2) + 'h', 'wb')as f:
pickle.dump(xgb, f)
with open('ldamodel2_' + str(select2) + 'h', 'rb')as f:
lda2 = pickle.load(f) # 导入LDA模型
# 模型的测试程序
x_test = scaler_test.transform(X_test)
prob = lda2.predict(x_test)
sim_time = np.arange(len(x_test))
# plt.figure(figsize=(16, 12))
# plt.plot(sim_time, y_test, '*-', sim_time, prob, 'o-.')
# plt.xlabel('批次', fontsize=14)
# plt.ylabel('状态分类（1：优 2:中 3：差）', fontsize=14)
# plt.title('Calss2测试集真实值与预测值对比' + str(select2) + 'h', fontsize=14)
# plt.legend(['true', 'predicted'])
# plt.show()
# #train_fig = 'test_true_predicted_calss20h.png'
# #plt.savefig(train_fig, dpi=500)
print(f"第二类模型准确率: {precision_score(y_test, prob, average='micro')}")
if precision_score(y_test, prob, average='micro') > 0.55:#如果绝对准确率大于0.55就进行更新模型的保存
pass
# # 标准化数据 模型保存
# with open('scaler_train2_' + str(select2) + 'h', 'wb')as f:
# pickle.dump(scaler_train, f)
# with open('ldamodel2_' + str(select2) + 'h', 'wb')as f:
# pickle.dump(xgb, f)
else:
self.calssmoudle2_train()#不满足准确率要求，再次训练模型
# 每一月更新一次模型
def month_change(self):
global date_old#将修改之后的变量也设置为globall类型
global count_days
if int(count_days) >= 45 or int(count_days) <= -45: # 只要相差一个月就更新一下 关键温度曲线和操作变量表
date_old = date_new#如果满足条件 立刻将表中最后一个时间赋值给date_old以进入下一个30天循环来更新模型
print('.............开始模型更新................')
self.newsample_match()
self.classification_0h()
self.calssmoudle1_train()
self.calssmoudle2_train()
print('.............完成模型更新................')
else:
print('.............不满足更新模型的时间间隔要求................')
#将UI界面放置在屏幕正中央
def center_window(widget):
window = widget.window()
window.setGeometry(
QtWidgets.QStyle.alignedRect(
QtCore.Qt.LeftToRight,
QtCore.Qt.AlignCenter,
window.size(),
QtGui.QGuiApplication.primaryScreen().availableGeometry(),
# QtGui.QGuiApplication.screens().availableGeometry(),
),
)
class Stock:
def __init__(self):
#！！！初始化程序中的程序出现错误不能影响后续功能的初始化
#####加载ui界面#########
loader = QUiLoader()
loader.registerCustomWidget(pg.PlotWidget)
self.ui = loader.load("main0525.ui")
# self.ui = QUiLoader().load("main0525.ui")
self.ui2 = loader.load("historycurve.ui")
self.ui3 = loader.load("suggestion.ui")
self.ui4 = loader.load("temperature.ui")
# 设置程序图标
self.ui.setWindowIcon(QtGui.QIcon('EthanolProcessOptimization.ico'))
self.ui2.setWindowIcon(QtGui.QIcon('EthanolProcessOptimization.ico'))
self.ui3.setWindowIcon(QtGui.QIcon('EthanolProcessOptimization.ico'))
self.ui4.setWindowIcon(QtGui.QIcon('EthanolProcessOptimization.ico'))
######设置温度曲线######
self.ui.historyPlot.setBackground((250, 250, 250))
# 显示表格线
self.ui.historyPlot.showGrid(x=True, y=True)
self.ui.historyPlot.setLabel("left", "T(℃)",size='2pt')
self.ui.historyPlot.setLabel("bottom", "Time(h)",size='2pt')
# 设置Y轴 刻度 范围
self.ui.historyPlot.setYRange(min=29, max=35) # 最大值(min=29, max=33)
self.ui.historyPlot.setXRange(min=1, max=65) # 最大值
self.ui.historyPlot.addLegend()
# 水平方向，表格大小拓展到适当的尺寸
self.ui.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.ui.tableWidget.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.ui3.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.ui3.tableWidget.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.ui4.tableWidget2.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.ui4.tableWidget2.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)
# 初始化历史浓度区块的时间区段为最近30天
end_date = datetime.datetime.now()
start_date = end_date - datetime.timedelta(days=30)
self.ui.dateEdit.setDate(QDate(start_date.year, start_date.month, start_date.day))
self.ui.dateEdit_2.setDate(QDate(end_date.year, end_date.month, end_date.day))
######定时器设置########
self.table_initial()#对所有的表ui表进行初始化
self.select_you_temperture_inspection()#收集温度和检验数据分别放到class1和class2中，方便之后计算关键温度点和计算置信参数更新操作表
# 定时更新发酵罐温度、液位等信息，一分钟一次
self.read_onlinedata()
self.timer_onlinedata = QTimer()
self.timer_onlinedata.timeout.connect(self.read_onlinedata)
self.timer_onlinedata.start(60 * 1000 * 2) # 以毫秒计
# 定时更新温度，一分钟一次
self.read_temp()
self.timer_temp = QTimer()
self.timer_temp.timeout.connect(self.read_temp)
self.timer_temp.start(60 * 1000 * 2) # 以毫秒计
self.curve1.scene().sigMouseMoved.connect(self.mouseover)
# 按钮功能的实现
self.ui.pushButton.clicked.connect(self.readhistory_prediction)
self.ui.pushButton_3.clicked.connect(self.readhistorycurve)
self.ui.pushButton_2.clicked.connect(self.readhistory_true)
self.ui.pushButton_4.clicked.connect(self.suggestion)
# 罐号选择实现
self.handleSelectionChange()
self.ui.comboBox.currentIndexChanged.connect(self.handleSelectionChange)
#定时更新较优批次表，关键温度，最优操作区间
self.ui.pushButton_5.clicked.connect(self.key_temperature_table)
self.update_operation_interval()
#读取一下乙醇表中最后一行的时间信息
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
Maxtime = db.cursor()
sqlMaxtime = "select DATETIME from ethanol where ID in(select max(ID) from ethanol )" #搜索表中最后一行对应的时间
try:
Maxtime.execute(sqlMaxtime)
result_Maxtime = Maxtime.fetchone()
# print(f"result_0h: {result_0h}")
except:
result_Maxtime = []
print("Error:无法获取乙醇表中的最大时间")
Maxtime.close()
db.close()
global date_old
global date_new
print(f"程序开始运行时乙醇表最后一行时间: {result_Maxtime}")
# date_old = datetime.datetime.strptime(str(result_Maxtime), "%Y-%m-%d")
# date_old = time.mktime(time.strptime(date_old), '%Y-%m-%d')
#date_old = result_Maxtime[0].strftime('%Y-%m-%d'))
date_old = datetime.datetime.strptime(str(result_Maxtime[0]), "%Y-%m-%d %H:%M:%S")
date_old = time.mktime(time.strptime(str(date_old), "%Y-%m-%d %H:%M:%S"))
#隔几天查询一次
self.date_new = QTimer()
self.date_new.timeout.connect(self.date_new_update)
self.date_new.start(60 * 1000 * 60 * 24 ) # 每隔一天查询一次是否达到一个月
def date_new_update(self):
global count_days #两次查询的时间间隔
#将每月更新一次的时间作为起始时间，如果与表中最大时间之差大于30天就进行模型的训练
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
Maxtime_new = db.cursor()
sqlMaxtime_new = "select DATETIME from ethanol where ID in(select max(ID) from ethanol )" # 搜索表中最后一行对应的时间
try:
Maxtime_new.execute(sqlMaxtime_new)
result_Maxtime_new = Maxtime_new.fetchone()
# print(f"result_0h: {result_0h}")
except:
result_Maxtime_new = []
print("Error:无法获取乙醇表中的最大时间")
Maxtime_new.close()
db.close()
date_new = datetime.datetime.strptime(str(result_Maxtime_new[0]), "%Y-%m-%d %H:%M:%S")
date_new = time.mktime(time.strptime(str(date_new), "%Y-%m-%d %H:%M:%S"))
count_days = int((date_new - date_old) / (24 * 60 * 60))#
print(f"距离上次模型更新的天数: {count_days}")
#如果达到一个月更新发酵过程关键温度和变量操作区间
if int(count_days)>=45 or int(count_days)<=-45 :#一个月更新关键温度曲线和操作变量表
self.select_you_temperture_inspection_everymonth()#较优批次表
self.key_temperature()#发酵过程关键温度
self.update_operation_interval()#变量操作区间
#初始化时对现有乙醇表中的所有批次进行判断，将较优批次选择出来
def select_you_temperture_inspection(self):
global ethanol_rowcount
#每月更新一次 将较优批次对应的温度 和检验数据 分两类后存到两个表格中 type1 和 type2 里面包含批次 、 60小时温度 、和检验数据（GI液化 葡萄糖液化 干物液化 葡萄糖酒母 甘油液化
#PH液化 、乙酸液化 、粘度液化 、DP4plus酒母 、 DP4plus液化 、 干物酒母 、DP2液化 、乙酸酒母 、DP2酒母 、死亡率 、糖化酶发酵罐 、酒母醪发酵罐 ）
#1、从ethanol表读取较优批次 建表 向表中插入 温度信息和上述液化数据（可先插入所有液化数据之后 再以编号的形式 赋给表格）
#注意 依次判断每个批次属于 那个部分 用每个部分的区间判断是否为较优批次 如果为较优批次则进行插值操作
#从乙醇出罐浓度表读取数据
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
cursor_ethanol = db.cursor()
sqltem = "select ID,罐次,乙醇出罐浓度 from ethanol where 罐次 is not null and 乙醇出罐浓度 is not null "
try:
cursor_ethanol.execute(sqltem)
ethanol = cursor_ethanol.fetchall()
except:
ethanol = []
print ("乙醇出罐浓度数据读取失败")
cursor_ethanol.close()
ethanol_array = np.array(ethanol)#列表元组
if ethanol:
#self.row = cursor_ethanol.rowcount # 取得记录个数，用于设置表格的行数
ethanol_rowcount = cursor_ethanol.rowcount # 取得记录个数，用于设置表格的行数
for i in range(ethanol_rowcount):
tank_number=str(ethanol_array[i][1])
#2在温度表中筛选该批次对应的60个温度信息，限制不为空的情况下 将读取到的温度信息 和ethanol拼接起来
#1在prediction40h表中筛选该批次对应的所有（或者在酒母四十小时表中筛选该批次 ，限制不为空的情况下，将检验信息和第一步数据拼接起来）
#0通过0小时应该有的指标来判断 这些批次到底是第一批次还是第二批次 然后根据每个批次的范围来判断是不是较优 系列 然后分别对两部分提取温度信息 和 检验数据 拼接起来
#将经过拼接的两部分数据 分别写进两个mysql表格 方便进一步计算均值 和 置信区间参数
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
cursor_inspection = db.cursor()
sqlprediction0h = "select * from prediction0h where tank_no ='%s'"%tank_number
try:
cursor_inspection.execute(sqlprediction0h)
inspection = cursor_inspection.fetchone()
except:
inspection = []
print ("初始时刻数据读取失败")
cursor_inspection.close()
db.close()
#判断这一批次属于哪个类别
with open('scaler_train_0h_classification', 'rb')as f:
scaler_test = pickle.load(f) #导入测试数据的标准化模型
with open('ldamodel_0h_classification', 'rb')as f:
lda_test = pickle.load(f) #导入LDA模型
#测试数据标准化
if inspection:#有的批次不出现在prediction0h表中，inspection为空，就不执行类别判定程序
inspection_test = scaler_test.transform(np.matrix(inspection[5:]))
prob_inspection = lda_test.predict(inspection_test)
if prob_inspection[0]==1:#建立第一类的表格(包含温度、检验数据等信息)
###零、首先判断是不是属于较优批次###
if float(ethanol[i][2])*1.195>=15.75:
class1_data=[int(ethanol[i][0]),float(ethanol[i][1]),float(ethanol[i][2])]#ID，罐次，乙醇浓度
###一、先建表###
#首先判断表格是否存在
db = MySQLdb.connect("localhost", "root", "123456", "sdic_ecust", charset='utf8', autocommit=1)
cursor_insert = db.cursor()
try:
sql = """ create table if not exists tableName(Type1)(
ID int ,
tank_no float,
ethanol float ,
x1 float,
x2 float,
x3 float,
x4 float,
x5 float,
x6 float,
x7 float,
x8 float,
x9 float,
x10 float,
x11 float,
x12 float,
x13 float,
x14 float,
x15 float,
x16 float,
x17 float,
x18 float,
x19 float,
x20 float,
x21 float, x22 float, x23 float, x24 float, x25 float, x26 float, x27 float, x28 float, x29 float, x30 float, x31 float, x32 float, x33 float, x34 float, x35 float,x36 float, x37 float, x38 float, x39 float, x40 float,x41 float, x42 float, x43 float, x44 float,
x45 float, x46 float, x47 float, x48 float, x49 float, x50 float, x51 float, x52 float, x53 float, x54 float, x55 float, x56 float, x57 float, x58 float, x59 float, x60 float, x61 float,x62 float,x63 float,x64 float,x65 float,x66 float,x67 float,x68 float,x69 float,
x70 float,x71 float,x72 float,x73 float,x74 float,x75 float,x76 float,x77 float,x78 float,x79 float,x80 float,x81 float,x82 float,t83 float,t84 float,t85 float,t86 float,t87 float,t88 float,t89 f