关于问题一中的异常值处理,我选取了拉格朗日插值的方法,但是上篇博客写的模糊,这里写一下:
导入需要的包:
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
导入我们的数据集:
data_285=pd.read_excel('附件三:285号和313号样本原始数据.xlsx',sheet_name='操作变量285')
data_285=data_285.iloc[:,1:]
data_285
data_313=pd.read_excel('附件三:285号和313号样本原始数据.xlsx',sheet_name='操作变量313')
data_313=data_313.iloc[:,1:]
data_313
我们检查不符合3
σ
\sigma
σ原则的数据,并标记为空值:
def three_sigma(data_input):
for i in range(data_input.shape[0]):
for j in range(data_input.shape[1]):
mean=data_input.iloc[:,j].mean()
std=data_input.iloc[:,j].std()
if abs(data_input.iloc[i,j]-mean)>3*std:
data_input.iloc[i,j]=np.nan
else:
continue
return data_input
three_sigma(data_285)
我们检查一下是否存在空值:
data_285.isnull().sum()[data_285.isnull().sum()>0]
我们看一下313的数据集:
data_313_2=three_sigma(data_313)
data_313_2
data_313_2.isnull().sum()[data_313_2.isnull().sum()>0]
我们检查一下空值的位置:
isnull=[]
for i in data_313_2.columns:
for j in data_313_2.index:
if data_313_2.isnull().loc[j,i]:
isnull.append((j,i))
isnull,len(isnull)
第一列为索引位置!
我们测试一下其中的一个值:
data_313_2.loc[37,'S-ZORB.FC_2801.PV']
nan
我们计算计算拉格朗日插值:
from scipy.interpolate import lagrange
#传入存在缺失值的列,缺失值所在0轴坐标index,按前后k个数来计算拉格朗日插值,返回index的拉格朗日插值
def lag_fill(df,i,k):
r=0 if (i-k)<0 else (i-k) # python的三目运算符较为特殊
l=len(df.index) if (i+1+k)>len(df.index) else (i+1+k)
y=df.loc[list(range(r,i))+list(range(i+1,l))] #取index前后k个数据作为y代入拉格朗日函数进行拟合
for j in y.index:
if y.isnull().loc[j]:
y.drop(index=j,inplace=True)
x=y.index
lag=lagrange(x.values,y.values)
return lag(i)
for i in isnull:
fnum=lag_fill(data_313_2.loc[:,i[1]],i[0],1)
data_313_2.loc[i[0],i[1]]=fnum
data_313_2
我们看一下上个位置的数据:
data_313_2.loc[37,'S-ZORB.FC_2801.PV']
859.8040999999996
我们检验一下新数据据是否合适:
data_313_2_new=three_sigma(data_313_2)
data_313_2_new
data_313_2_new.isnull().sum()[data_313_2_new.isnull().sum()>0]
isnull_2=[]
for i in data_313_2_new.columns:
for j in data_313_2_new.index:
if data_313_2_new.isnull().loc[j,i]:
isnull_2.append((j,i))
isnull_2,len(isnull_2)
for j in isnull_2:
fnum_1=lag_fill(data_313_2_new.loc[:,j[1]],j[0],1)
data_313_2_new.loc[j[0],j[1]]=fnum_1
data_313_2_new.isnull().sum()[data_313_2_new.isnull().sum()>0]
Series([], dtype: int64)
再次检查:
data_313_2_new_2=three_sigma(data_313_2_new)
data_313_2_new_2.isnull().sum()[data_313_2_new_2.isnull().sum()>0]
isnull_3=[]
for i in data_313_2_new_2.columns:
for j in data_313_2_new_2.index:
if data_313_2_new_2.isnull().loc[j,i]:
isnull_3.append((j,i))
isnull_3,len(isnull_3)
for m in isnull_3:
fnum_2=lag_fill(data_313_2_new_2.loc[:,m[1]],m[0],1)
data_313_2_new_2.loc[m[0],m[1]]=fnum_2
data_313_2_new_3=three_sigma(data_313_2_new_2)
data_313_2_new_3
isnull_4=[]
for i in data_313_2_new_3.columns:
for j in data_313_2_new_3.index:
if data_313_2_new_3.isnull().loc[j,i]:
isnull_4.append((j,i))
isnull_4,len(isnull_4)
for n in isnull_4:
fnum_3=lag_fill(data_313_2_new_3.loc[:,n[1]],n[0],1)
data_313_2_new_3.loc[n[0],n[1]]=fnum_3
data_313_2_new_4=three_sigma(data_313_2_new_3)
data_313_2_new_4.isnull().sum()[data_313_2_new_4.isnull().sum()>0]
我们的结果为:
Series([], dtype: int64)
此时,我们的数据不再有异常值,处理完成!