#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()
标签:数据分析,loc,plt,第十章,sj,pd,水流量,data From: https://www.cnblogs.com/hxs6/p/17259595.html