def correlationFunc(factory, device, measurePoint, timeRange, hashname):
if device == "-1":
P_total, device_index = Tool.getP_totalBySQL(-1, device, measurePoint,
timeRange)
else:
P_total, device_index = Tool.getP_totalBySQL(factory, device,
measurePoint, timeRange)
corr_device = correlation(P_total, device_index, 3, 96)
addSelfDeviceCorrIndex = corr_device.index.tolist()
addSelfDeviceCorrIndex.append(device)
corrData = P_total.loc[:, addSelfDeviceCorrIndex]
corrDataDict = {
'timestamp':
corrData.index.strftime("%Y-%m-%d %H:%M:%S").values.tolist(),
}
for i in addSelfDeviceCorrIndex:
corrDataDict[i] = corrData.loc[:, i].values.tolist()
corrDataJson = json.dumps(corrDataDict, ensure_ascii=False)
# keys:带有设备的文件名 values:相似度
a, b = corr_device.keys(), corr_device.values
resultDict = {}
for i, j in zip(a, b):
resultDict[i] = j
resultJson = json.dumps(resultDict, ensure_ascii=False)
sql = ''' insert into sjtudb.correlation values('%s','%s','%s') ''' % (
hashname, resultJson, corrDataJson)
Tool.excuteSQL(sql)
return hashname
def clusterFunc(factory, device, measurePoint, timeRange, hashname):
P_total, device_index = Tool.getP_totalBySQL(factory, device, measurePoint,
timeRange)
kmeans_hour, labels_hour, kmeans_day, labels_day = cluster(
P_total, device_index, 96)
hourList = kmeans_hour.tolist()
dayList = kmeans_day.tolist()
# print(dayList)
hourX = len(hourList[0])
dayX = len(dayList[0])
resultDict = {
'hourX': list(range(0, hourX)),
'dayX': list(range(0, dayX)),
'hourList': hourList,
'dayList': dayList
}
try:
resultJson = json.dumps(resultDict, ensure_ascii=False)
except Exception as e:
e.with_traceback()
sql = "insert into clusterresult (hashstr,json)values ('%s', '%s')" % (
hashname, resultJson)
Tool.excuteSQL(sql)
return hashname
def predictRealData(factory, device, measurePoint, year, month, day):
P_total, device_index = Tool.getP_totalBySQL(factory, device, measurePoint)
P_total = P_total[year + '-' + month + '-' + day]
day_point = 480 # 一天为480个数据点
P_forecast = P_total.iloc[:, device_index]
y_total = P_forecast[day_point * 7:].reset_index(drop=True)
return np.array(y_total)[-7 * day_point:].tolist()
def baseLine(factory,
device,
measurePoint,
year,
month,
day,
hashname,
day_point=96):
date = datetime.datetime(year, month, day)
start = date - datetime.timedelta(days=8)
end = date + datetime.timedelta(days=2)
print(start)
print(date)
data, device_index = Tool.getP_totalBySQL(
factory, device, measurePoint,
[start.strftime("%Y-%m-%d"),
end.strftime("%Y-%m-%d")])
# data, device_index = Tool.getP_totalBySQL(factory, device, measurePoint)
baseValue, trueValue = baseline(data, device_index, year, month, day,
day_point)
resultDict = {'baseValue': list(baseValue), 'trueValue': list(trueValue)}
try:
resultJson = json.dumps(resultDict, ensure_ascii=False)
except Exception as e:
e.with_traceback()
sql = "insert into baseline (hashstr,json)values ('%s','%s')" % (
hashname, resultJson)
Tool.excuteSQL(sql)
return hashname
def predictFunc(factory, device, measurePoint, timeRange, hashname):
resultFileName = hashname[0:15]
# insertSQL = '''insert into sjtudb.algorithmresult values('%s','%s',null)''' % (parameterHash, resultFileName)
# Tool.excuteSQL(insertSQL)
P_total, device_index = Tool.getP_totalBySQL(factory, device, measurePoint,
timeRange)
corr_device = correlation(P_total, device_index, 3, 96)
a, b = train_forecast(P_total, corr_device, device_index, 96)
lastResult = {'y_true': a, 'y_pred': b}
jsonStr = json.dumps(lastResult)
insertSQL = '''insert into sjtudb.algorithmresult values('%s','%s','%s')''' % (
hashname, resultFileName, jsonStr)
# print(insertSQL)
Tool.excuteSQL(insertSQL)
return hashname
if __name__ == "__main__":
'''
dataDir = 'data\\常州天和印染有限公司\\'
name = '三相总有功功率' #所分析数据项
if os.path.exists('data\\tmp\\P_total.csv'):
P_total = pd.read_csv('data\\tmp\\P_total.csv',index_col=0)
else:
P_total = readData(dataDir, name)
P_total.to_csv('data\\tmp\\P_total.csv') '''
device = '100009' # 所要预测设备(全建筑总出)
day_point = 96 # tianhe:480,new:96 (将原函数中的480改为day_point,20改为day_point//24)
# P_total = pd.read_csv('Ptotal_new.csv', index_col=0)
P_total, device_index = Tool.getP_totalBySQL("11", "11", "100009", "BV")
print(device_index)
# P_total.index = pd.to_datetime(P_total.index)
# P_total = P_total.sort_index()
#
# # 删去全零列设备
# del_list = []
# for i in range(P_total.shape[1]):
# if max(P_total.iloc[:, i]) - min(P_total.iloc[:, i]) == 0:
# del_list.append(i)
# P_total.drop(P_total.columns[del_list], axis=1, inplace=True)
#
# # 补全device名并得到device_index
# for i in range(P_total.shape[1]):
# if device in P_total.columns[i]:
# device = P_total.columns[i]
def profileFeatureFunc(factory, device, measurePoint, timeRange, hashname):
P_total, device_index = Tool.getP_totalBySQL(factory, device, measurePoint,
timeRange)
kmeans_hour, labels_hour, kmeans_day, labels_day = cluster(
P_total, device_index, 96)
temp8760 = pd.read_csv(os.path.join(
os.path.dirname(os.path.abspath(__file__)), 'ShanghaiTemp8760.csv'),
header=None,
sep="[;,]",
engine='python')
staticFeatures, dynamicFeatures, tempload, temp, scattertemp, scatterdataunique = profileFeature(
P_total, device_index, kmeans_hour, kmeans_day, labels_hour,
labels_day, temp8760)
scatter = []
for i in range(len(scattertemp)):
if not (scatterdataunique.iloc[i] == 0):
scatter.append([scattertemp.iloc[i], scatterdataunique.iloc[i]])
staticFeatures[0]
staticFeatures[5] = str(staticFeatures[5])[1:-2]
staticFeatures[7] = staticFeatures[7].tolist()
staticFeatures[8] = staticFeatures[8].tolist()
dynamicFeatures[0] = dynamicFeatures[0].tolist()
dynamicFeatures[2] = dynamicFeatures[2].tolist()
staticFeaturesObj = {
'maxv': staticFeatures[0],
'minv': staticFeatures[1],
'median': staticFeatures[2],
'avg': staticFeatures[3],
'standard': staticFeatures[4],
'fftavg': staticFeatures[5],
'fftstandard': staticFeatures[6],
'featurelineh': staticFeatures[7], #典型特征模式曲线（小时尺度）
'linearfeaturelined': staticFeatures[8], #线性特征模式曲线（天尺度）
}
dynamicFeaturesObj = {
'transfermatrixh': dynamicFeatures[0], #基于聚类结果的马尔科夫转移矩阵（小时尺度）
'entropyh': dynamicFeatures[1],
'transfermatrixd': dynamicFeatures[2], #基于聚类结果的科尔科夫转移矩阵（天尺度）
'entropyd': dynamicFeatures[3]
}
resultDict = {
'static': staticFeaturesObj,
'dynamic': dynamicFeaturesObj,
"load": tempload.tolist(),
"temp": temp.tolist(),
"scatter": scatter
}
try:
resultJson = json.dumps(resultDict, ensure_ascii=False)
except Exception as e:
e.with_traceback()
sql = "insert into profilefeature (hashstr,json)values ('%s','%s')" % (
hashname, resultJson)
Tool.excuteSQL(sql)
return hashname
