#10-1
import pandas as pd
import matplotlib.pyplot as plt
inputfile="D:\数据分析\original_data.xls"
data=pd.read_excel(inputfile)
lv_non=pd.value_counts(data['有无水流'])['无']
lv_move=pd.value_counts(data['有无水流'])['有']
fig=plt.figure(figsize=(6,5))
plt.rcParams['font.sans-serif']='SimHei'
plt.rcParams['axes.unicode_minus']=False
plt.bar(x=range(2),height=[lv_non,lv_move],width=0.4,alpha=0.8,color='skyblue')
plt.xticks([index for index in range(2)],['无','有'])
plt.xlabel('水流状态')
plt.ylabel('记录数')
plt.title('学号3108不同水流状态记录数')
plt.show()
plt.close()
water=data['水流量']
fig=plt.figure(figsize=(5,8))
plt.boxplot(water,
patch_artist=True,
labels=['水流量'],
boxprops={'facecolor':'lightblue'})
plt.title('学号3108水流量分布箱型图')
plt.grid(axis='y')
plt.show()
#10-2
import pandas as pd
import numpy as np
data=pd.read_excel("D:\数据分析\original_data.xls")
print('初始状态的数据形状为：',data.shape)
data.drop(labels=["热水器编号","有无水流","节能模式"],axis=1,inplace=True)
print('删除冗余属性后的数据形状为：',data.shape)
data.to_csv("D:\数据分析\water_heart.csv",index=False)
#10-3
data=pd.read_csv("D:\数据分析\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("D:\数据分析\sj.csv",index=False)
#10-4
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)
#10-5
data=pd.read_csv("D:\数据分析\water_heart.csv")
sj=pd.read_csv("D:\数据分析\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])
#10-6
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
for i in range(len(sj)):
if sj.loc[i,"停顿次数"]>1:
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])
#10-7
sj_bool=(sj['用水时长']>100)&(sj['总用水时长']>120)&(sj['总用水量']>5)
sj_final=sj.loc[sj_bool,:]
sj_final.to_excel("D:/数据分析/sj_final.xlsx",index=False)
print('筛选出候选洗浴事件前的数据形状为：',sj.shape)
print('筛选出候选洗浴事件后的数据形状为：',sj_final.shape)
#10-8
import pandas as pd
from sklearn.preprocessing import StandardScaler
from sklearn.neural_network import MLPClassifier
import joblib
Xtrain=pd.read_excel("D:/数据分析/sj_final.xlsx")
ytrain=pd.read_excel("D:/数据分析/water_heater_log.xlsx")
test=pd.read_excel("D:/数据分析/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)
#10-9
from sklearn.metrics import classification_report
from sklearn.metrics import roc_curve
import matplotlib.pyplot as plt
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曲线学号3108')
plt.xlabel('FPR')
plt.ylabel('TPR')
plt.savefig('用户用水事件识别ROC曲线.png')
plt.show()
