删除同一离群两个时间序列(Deleting the same outliers in two tim

我有一个关于两个时间序列消除异常的问题。有一次系列包括现货市场价格和其他包括功率输出。这两个系列是2012年至2016年，并且都CSV文件与一个时间戳，然后一个值。至于例如用于功率输出：2012-01-01 00：00：00,2335.2152646951617和用于价格：2012-01-01 00：00：00,17.2

由于现货市场价格波动非常大，有很多的异常值，我已筛选他们。对于第二个时间序列，我有相同的时间戳，被淘汰在时间序列中价格的删除值。我想过产生与被删除的值的列表，写一个循环，在第二个时间序列相同的时间戳删除值。但到目前为止，还没有工作，我真的不上。有没有人有一个想法？

我的Python代码看起来如下：

import pandas as pd
import matplotlib.pyplot as plt

power_output = pd.read_csv("./data/external/power_output.csv", delimiter=",", parse_dates=[0], index_col=[0])
print(power_output.head())
plt.plot(power_output)

spotmarket = pd.read_csv("./data/external/spotmarket_dhp.csv", delimiter=",", parse_dates=[0], index_col=[0])
print(spotmarket.head())

r = spotmarket['price'].pct_change().dropna() * 100
print(r)
plt.plot(r)

Q1 = r.quantile(.25)
Q3 = r.quantile(.75)
q1 = Q1-2*(Q3-Q1)
q3 = Q3+2*(Q3-Q1)

a = r[r.between(q1, q3)]
print(a)
plt.plot(a)

有人可以帮我吗？

Answer 1:

如果你的问题是关于如何比较两个时间戳，你可以看看这个。

基本上，你可以这样做：

out = r[~r.between(q1, q3)] # negation of your between to get the outliers
df=pd.merge(spotmarker,out,on=['date'],how="outer",indicator=True)
df=df[df['_merge']=='left_only']

这是节省只有那些仅在左数据帧在场的人行合并操作

Answer 2:

下面的建议是基于从我的一个答案以前的帖子。你可以解决你的问题，合并的一系列既并将它们存储在大熊猫数据帧。然后你可以使用任何期望的技术来识别和删除异常值。看看上面提到的职位。

下面是使用一个片段，可以处理多个系列我拿上您的具体问题：

由于我没有你的数据访问，下面的代码片段将产生两个系列，其中一人有一个独特的异常：

def sample(colname):

    base = 100
    nsample = 20
    sigma = 10

    # Basic df with trend and sinus seasonality 
    trend1 = np.linspace(0,1, nsample)
    y1 = np.sin(trend1)
    dates = pd.date_range(pd.datetime(2016, 1, 1).strftime('%Y-%m-%d'), periods=nsample).tolist()
    df = pd.DataFrame({'dates':dates, 'trend1':trend1, 'y1':y1})
    df = df.set_index(['dates'])
    df.index = pd.to_datetime(df.index)

    # Gaussian Noise with amplitude sigma
    df['y2'] = sigma * np.random.normal(size=nsample)
    df['y3'] = df['y2'] + base + (np.sin(trend1))
    df['trend2'] = 1/(np.cos(trend1)/1.05)
    df['y4'] = df['y3'] * df['trend2']

    df=df['y4'].to_frame()
    df.columns = [colname]

    return(df)

df_sample1 = sample(colname = 'series1')
df_sample2 = sample(colname = 'series2')
df_sample2['series2'].iloc[10] = 800
df_sample1.plot()
df_sample2.plot()

系列1 - 无异常

系列2 - 一个显着异常

现在，您可以合并这些系列是这样的：

# Merge dataframes
df_merged = pd.merge(df_sample1, df_sample2, how='outer', left_index=True, right_index=True)
df_merged.plot()

什么被认为是一个异常值将取决于全面的数据集的性质。在这种情况下，可以使用设定的水平用于识别离群值sscipy.zscore() 在以下的情况下，具有差每个观测超过3被认为是异常值。

# A function for removing outliers
def noSpikes(df, level, keepFirst):

    # 1. Get some info about the original data:

    ##%%
    #df = df_merged
    #level = 3
    #keepFirst = True
    ##%%

    firstVal = df[:1]
    colNames = df.columns
    colNumber = len(df.columns)

    #cleanBy = 'Series1'

    # 2. Take the first difference and 
    df_diff = df.diff()

    # 3. Remove missing values
    df_clean = df_diff.dropna()

    # 4. Select a level for a Z-score to identify and remove outliers
    df_Z = df_clean[(np.abs(stats.zscore(df_clean)) < level).all(axis=1)]
    ix_keep = df_Z.index

    # 5. Subset the raw dataframe with the indexes you'd like to keep
    df_keep = df.loc[ix_keep]

    # 6. 
    # df_keep will be missing some indexes.
    # Do the following if you'd like to keep those indexes
    # and, for example, fill missing values with the previous values
    df_out = pd.merge(df_keep, df, how='outer', left_index=True, right_index=True)

    # 7. Keep only the original columns (drop the diffs)
    df_out = df_out.ix[:,:colNumber]

    # 8. Fill missing values
    df_complete = df_out.fillna(axis=0, method='ffill')

    # 9. Reset column names
    df_complete.columns = colNames

    # Keep the first value
    if keepFirst:
        df_complete.iloc[0] = firstVal.iloc[0]

    return(df_complete)

df_clean = noSpikes(df = df_merged, level = 3, keepFirst = True)
df_clean.plot()

让我知道这是如何工作为你。

下面是一个简单的复制粘贴整个事情：

# Imports
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
from scipy import stats

np.random.seed(22)

# A function for noisy data with a trend element
def sample(colname):

    base = 100
    nsample = 20
    sigma = 10

    # Basic df with trend and sinus seasonality 
    trend1 = np.linspace(0,1, nsample)
    y1 = np.sin(trend1)
    dates = pd.date_range(pd.datetime(2016, 1, 1).strftime('%Y-%m-%d'), periods=nsample).tolist()
    df = pd.DataFrame({'dates':dates, 'trend1':trend1, 'y1':y1})
    df = df.set_index(['dates'])
    df.index = pd.to_datetime(df.index)

    # Gaussian Noise with amplitude sigma
    df['y2'] = sigma * np.random.normal(size=nsample)
    df['y3'] = df['y2'] + base + (np.sin(trend1))
    df['trend2'] = 1/(np.cos(trend1)/1.05)
    df['y4'] = df['y3'] * df['trend2']

    df=df['y4'].to_frame()
    df.columns = [colname]

    return(df)

df_sample1 = sample(colname = 'series1')
df_sample2 = sample(colname = 'series2')
df_sample2['series2'].iloc[10] = 800
df_sample1.plot()
df_sample2.plot()

# Merge dataframes
df_merged = pd.merge(df_sample1, df_sample2, how='outer', left_index=True, right_index=True)
df_merged.plot()

# A function for removing outliers
def noSpikes(df, level, keepFirst):

    # 1. Get some info about the original data:
    firstVal = df[:1]
    colNames = df.columns
    colNumber = len(df.columns)

    #cleanBy = 'Series1'

    # 2. Take the first difference and 
    df_diff = df.diff()

    # 3. Remove missing values
    df_clean = df_diff.dropna()

    # 4. Select a level for a Z-score to identify and remove outliers
    df_Z = df_clean[(np.abs(stats.zscore(df_clean)) < level).all(axis=1)]
    ix_keep = df_Z.index

    # 5. Subset the raw dataframe with the indexes you'd like to keep
    df_keep = df.loc[ix_keep]

    # 6. 
    # df_keep will be missing some indexes.
    # Do the following if you'd like to keep those indexes
    # and, for example, fill missing values with the previous values
    df_out = pd.merge(df_keep, df, how='outer', left_index=True, right_index=True)

    # 7. Keep only the original columns (drop the diffs)
    df_out = df_out.ix[:,:colNumber]

    # 8. Fill missing values
    df_complete = df_out.fillna(axis=0, method='ffill')

    # 9. Reset column names
    df_complete.columns = colNames

    # Keep the first value
    if keepFirst:
        df_complete.iloc[0] = firstVal.iloc[0]

    return(df_complete)

df_clean = noSpikes(df = df_merged, level = 3, keepFirst = True)
df_clean.plot()