1、
import pandas as pd
import numpy as np
data = pd.read_excel('original_data.xls')
print('初始状态的数据形状为：', data.shape)
data.drop(labels=["热水器编号","有无水流","节能模式"],axis=1,inplace=True)
print('删除冗余属性后的数据形状为：', data.shape)
data.to_csv('water_heart.csv',index=False)
data = pd.read_csv('water_heart.csv')
threshold = pd.Timedelta('4 min')
data['发生时间'] = pd.to_datetime(data['发生时间'], format = '%Y%m%d%H%M%S')
data = data[data['水流量'] > 0]
sjKs = data['发生时间'].diff() > threshold
sjKs.iloc[0] = True
sjJs = sjKs.iloc[1:]
sjJs = pd.concat([sjJs,pd.Series(True)])
sj = pd.DataFrame(np.arange(1,sum(sjKs)+1),columns = ["事件序号"])
sj["事件起始编号"] = data.index[sjKs == 1]+1
sj["事件终止编号"] = data.index[sjJs == 1]+1
print('当阈值为4分钟的时候事件数目为：',sj.shape[0])
sj.to_csv('sj.csv',index = False)
n = 4
threshold = pd.Timedelta(minutes=5)
data['发生时间'] = pd.to_datetime(data['发生时间'], format='%Y%m%d%H%M%S')
data = data[data['水流量'] > 0]
def event_num(ts):
d = data['发生时间'].diff() > ts
return d.sum() + 1
dt = [pd.Timedelta(minutes=i) for i in np.arange(1, 9, 0.25)]
h = pd.DataFrame(dt, columns=['阈值'])
h['事件数'] = h['阈值'].apply(event_num)
h['斜率'] = h['事件数'].diff()/0.25 # 计算每两个相邻点对应的斜率
h['斜率指标']= h['斜率'].abs().rolling(4).mean()
ts = h['阈值'][h['斜率指标'].idxmin() - n]
if ts > threshold:
ts = pd.Timedelta(minutes=4)
print('计算出的单次用水时长的阈值为：',ts)
data = pd.read_csv('water_heart.csv')
sj = pd.read_csv('sj.csv')
data["发生时间"] = pd.to_datetime(data["发生时间"],format="%Y%m%d%H%M%S")
timeDel = pd.Timedelta("0.5 sec")
sj["事件开始时间"] = data.iloc[sj["事件起始编号"]-1,0].values- timeDel
sj["事件结束时间"] = data.iloc[sj["事件终止编号"]-1,0].values + timeDel
sj['洗浴时间点'] = [i.hour for i in sj["事件开始时间"]]
sj["总用水时长"] = np.int64(sj["事件结束时间"] - sj["事件开始时间"])/1000000000 + 1
for i in range(len(data)-1):
if (data.loc[i,"水流量"] != 0) & (data.loc[i + 1,"水流量"] == 0) :
data.loc[i + 1,"停顿开始时间"] = data.loc[i +1, "发生时间"] - timeDel
if (data.loc[i,"水流量"] == 0) & (data.loc[i + 1,"水流量"] != 0) :
data.loc[i,"停顿结束时间"] = data.loc[i , "发生时间"] + timeDel
indStopStart = data.index[data["停顿开始时间"].notnull()]+1
indStopEnd = data.index[data["停顿结束时间"].notnull()]+1
Stop = pd.DataFrame(data={"停顿开始编号":indStopStart[:-1],
"停顿结束编号":indStopEnd[1:]})
Stop["停顿时长"] = np.int64(data.loc[indStopEnd[1:]-1,"停顿结束时间"].values-
data.loc[indStopStart[:-1]-1,"停顿开始时间"].values)/1000000000
for i in range(len(sj)):
Stop.loc[(Stop["停顿开始编号"] > sj.loc[i,"事件起始编号"]) &
(Stop["停顿结束编号"] < sj.loc[i,"事件终止编号"]),"停顿归属事件"]=i+1
Stop = Stop[Stop["停顿归属事件"].notnull()]
stopAgg = Stop.groupby("停顿归属事件").agg({"停顿时长":sum,"停顿开始编号":len})
sj.loc[stopAgg.index - 1,"总停顿时长"] = stopAgg.loc[:,"停顿时长"].values
sj.loc[stopAgg.index-1,"停顿次数"] = stopAgg.loc[:,"停顿开始编号"].values
sj.fillna(0,inplace=True)
stopNo0 = sj["停顿次数"] != 0
sj.loc[stopNo0,"平均停顿时长"] = sj.loc[stopNo0,"总停顿时长"]/sj.loc[stopNo0,"停顿次数"]
sj.fillna(0,inplace=True)
sj["用水时长"] = sj["总用水时长"] - sj["总停顿时长"]
sj["用水/总时长"] = sj["用水时长"] / sj["总用水时长"]
print('用水事件用水时长与频率特征构造完成后数据的特征为：\n',sj.columns)
print('用水事件用水时长与频率特征构造完成后数据的前5行5列特征为：\n',
sj.iloc[:5,:5])
data["水流量"] = data["水流量"] / 60
sj["总用水量"] = 0
for i in range(len(sj)):
Start = sj.loc[i,"事件起始编号"]-1
End = sj.loc[i,"事件终止编号"]-1
if Start != End:
for j in range(Start,End):
if data.loc[j,"水流量"] != 0:
sj.loc[i,"总用水量"] = (data.loc[j + 1,"发生时间"] -
data.loc[j,"发生时间"]).seconds* \
data.loc[j,"水流量"] + sj.loc[i,"总用水量"]
sj.loc[i,"总用水量"] = sj.loc[i,"总用水量"] + data.loc[End,"水流量"] * 2
else:
sj.loc[i,"总用水量"] = data.loc[Start,"水流量"] * 2
sj["平均水流量"] = sj["总用水量"] / sj["用水时长"]
sj["水流量波动"] = 0
for i in range(len(sj)):
Start = sj.loc[i,"事件起始编号"] - 1
End = sj.loc[i,"事件终止编号"] - 1
for j in range(Start,End + 1):
if data.loc[j,"水流量"] != 0:
slbd = (data.loc[j,"水流量"] - sj.loc[i,"平均水流量"])**2
slsj = (data.loc[j + 1,"发生时间"] - data.loc[j,"发生时间"]).seconds
sj.loc[i,"水流量波动"] = slbd * slsj + sj.loc[i,"水流量波动"]
sj.loc[i,"水流量波动"] = sj.loc[i,"水流量波动"] / sj.loc[i,"用水时长"]
sj["停顿时长波动"] = 0 # 给停顿时长波动赋一个初始值0
for i in range(len(sj)):
if sj.loc[i,"停顿次数"] > 1: # 当停顿次数为0或1时，停顿时长波动值为0，故排除
for j in Stop.loc[Stop["停顿归属事件"] == (i+1),"停顿时长"].values:
sj.loc[i,"停顿时长波动"] = ((j - sj.loc[i,"平均停顿时长"])**2) * j + \
sj.loc[i,"停顿时长波动"]
sj.loc[i,"停顿时长波动"] = sj.loc[i,"停顿时长波动"] / sj.loc[i,"总停顿时长"]
print('用水量和波动特征构造完成后数据的特征为：\n',sj.columns)
print('用水量和波动特征构造完成后数据的前5行5列特征为：\n',sj.iloc[:5,:5])
sj_bool = (sj['用水时长'] >100) & (sj['总用水时长'] > 120) & (sj['总用水量'] > 5)
sj_final = sj.loc[sj_bool,:]
sj_final.to_csv('sj_final.csv',index=False)
print('筛选出候选洗浴事件前的数据形状为：',sj.shape)
print('筛选出候选洗浴事件后的数据形状为：',sj_final.shape)
2、
import pandas as pd
from sklearn.preprocessing import StandardScaler
from sklearn.neural_network import MLPClassifier
import joblib
from sklearn.metrics import classification_report
from sklearn.metrics import roc_curve
import matplotlib.pyplot as plt
Xtrain = pd.read_csv('sj_final.csv')
ytrain = pd.read_excel('water_heater_log.xlsx')
test = pd.read_excel('test_data.xlsx')
x_train, x_test, y_train, y_test = Xtrain.iloc[:,5:],test.iloc[:,4:-1],\
ytrain.iloc[:,-1],test.iloc[:,-1]
stdScaler = StandardScaler().fit(x_train)
x_stdtrain = stdScaler.transform(x_train)
x_stdtest = stdScaler.transform(x_test)
bpnn = MLPClassifier(hidden_layer_sizes = (17,10), max_iter = 200, solver = 'lbfgs',random_state=50)
bpnn.fit(x_stdtrain, y_train)
joblib.dump(bpnn,'water_heater_nnet.m')
print('构建的模型为：\n',bpnn)
bpnn = joblib.load('water_heater_nnet.m')
y_pred = bpnn.predict(x_stdtest)
print('神经网络预测结果评价报告：\n',classification_report(y_test,y_pred))
plt.rcParams['font.sans-serif'] = 'SimHei'
plt.rcParams['axes.unicode_minus'] = False
fpr, tpr, thresholds = roc_curve(y_pred,y_test)
plt.figure(figsize=(6,4))
plt.plot(fpr,tpr)
plt.title('用户用水事件识别ROC曲线——3023')
plt.xlabel('FPR')
plt.ylabel('TPR')
plt.savefig('用户用水事件识别ROC曲线.png')
plt.show()
