水流数据分析

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('学号3106水流量分布箱型图')
plt.grid(axis='y')
plt.show()

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)

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)

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("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])

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)

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)

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()