Python Pandas - 快速迭代数据的类别并去除异常值（无需使用for循环）

Question

假设我有一个类似这样的数据框：

import pandas as pd
import numpy as np

data = [[5123, '2021-01-01 00:00:00', 'cash','sales$', 105],
        [5123, '2021-01-01 00:00:00', 'cash','items', 20],
        [5123, '2021-01-01 00:00:00', 'card','sales$', 190],
        [5123, '2021-01-01 00:00:00', 'card','items', 40],
        [5123, '2021-01-02 00:00:00', 'cash','sales$', 75],
        [5123, '2021-01-02 00:00:00', 'cash','items', 10],
        [5123, '2021-01-02 00:00:00', 'card','sales$', 170],
        [5123, '2021-01-02 00:00:00', 'card','items', 35],
        [5123, '2021-01-03 00:00:00', 'cash','sales$', 1000],
        [5123, '2021-01-03 00:00:00', 'cash','items', 500],
        [5123, '2021-01-03 00:00:00', 'card','sales$', 150],
        [5123, '2021-01-03 00:00:00', 'card','items', 20]]

columns = ['Store', 'Date', 'Payment Method', 'Attribute', 'Value']

df = pd.DataFrame(data = data, columns = columns)

商店	日期	支付方式	属性	值
5123	2021年1月1日 00:00:00	现金	销售额	105
5123	2021年1月1日 00:00:00	现金	商品数	20
5123	2021年1月1日 00:00:00	信用卡	销售额	190
5123	2021年1月1日 00:00:00	信用卡	商品数	40
5123	2021年1月2日 00:00:00	现金	销售额	75
5123	2021年1月2日 00:00:00	现金	商品数	10
5123	2021年1月2日 00:00:00	信用卡	销售额	170
5123	2021年1月2日 00:00:00	信用卡	商品数	35
5123	2021年1月3日 00:00:00	现金	销售额	1000
5123	2021年1月3日 00:00:00	现金	商品数	500
5123	2021年1月3日 00:00:00	信用卡	销售额	150
5123	2021年1月3日 00:00:00	信用卡	商品数	20

我希望可以过滤掉异常值并用前两天的平均值代替。 我的“异常值规则”如下：如果某个属性/支付方式的值比前两天该属性/支付方式的平均值大两倍以上或小于一半，则将该异常值替换为前两天该属性/支付方式的平均值。否则保留该值。在此情况下，所有值都应该保留，除了$1000的销售额和5123/'2021-01-03'/'现金'的500件商品。这些值应该被替换为销售额的$90和商品数量的15。

以下是我的尝试（使用for循环，但无法正常工作）。每当我在使用循环和Pandas时，就会在我的脑海中想起一个红色警告。正确的做法是什么？

stores = df['Store'].unique()
payment_methods = df['Payment Method'].unique()
attributes = df['Attribute'].unique()

df_no_outliers = pd.DataFrame()

for store in stores:
    for payment_method in payment_methods:
        for attribute in attributes:

            df_temp = df.loc[df['Store'] == store]
            df_temp = df_temp.loc[df_temp['Payment Method'] == payment_method]
            df_temp = df_temp.loc[df_temp['Attribute'] == attribute]

            df_temp['Value'] = np.where(df_temp['Value'] <= (df_temp['Value'].shift(-1)
                                                                +df_temp['Value'].shift(-2))*2/2,
                                         df_temp['Value'],
                                        (df_temp['Value'].shift(-1)+df_temp['Value'].shift(-2))/2)

            df_temp['Value'] = np.where(df_temp['Value'] >= (df_temp['Value'].shift(-1)
                                                                +df_temp['Value'].shift(-2))*0.5/2,
                                         df_temp['Value'],
                                        (df_temp['Value'].shift(-1)+df_temp['Value'].shift(-2))/2)


            df_no_outliers = df_no_outliers.append(df_temp)

如果有人好奇为什么我要使用这种滚动平均方法，而不是像 Tukey 的方法一样在 1Q 和 3Q 处截断数据多于或少于 1.5*IQR，原因是我的数据是 COVID 期间的时间序列数据，这意味着 IQR 很大（COVID 前有高销售量，然后是销售量极低的深谷），因此 IQR 最终没有过滤掉任何东西。我不想去除 COVID 导致的销售下降，而是想去掉一些错误的数据输入失败（一些商店可能会在某些日子输入一些额外的零...）。我将使用最近 5 或 7 天（即一周）作为滚动过滤器，而不是使用最近两天。我也愿意尝试其他的清理/异常值移除方法。

- edutt

Answer 1

尝试：

#groupby the required columns and compute the rolling 2-day average
average = (df.groupby(["Store","Payment Method","Attribute"], as_index=False)
           .apply(lambda x: x["Value"].rolling(2).mean().shift())
           .droplevel(0).sort_index()
           )

#divide values by the average and keep only those ratios that fall between 0.5 and 2
df['Value'] = df["Value"].where(df["Value"].div(average).fillna(1).between(0.5,2),average)
>>> df
    Store                 Date Payment Method Attribute  Value
0    5123  2021-01-01 00:00:00           cash    sales$  105.0
1    5123  2021-01-01 00:00:00           cash     items   20.0
2    5123  2021-01-01 00:00:00           card    sales$  190.0
3    5123  2021-01-01 00:00:00           card     items   40.0
4    5123  2021-01-02 00:00:00           cash    sales$   75.0
5    5123  2021-01-02 00:00:00           cash     items   10.0
6    5123  2021-01-02 00:00:00           card    sales$  170.0
7    5123  2021-01-02 00:00:00           card     items   35.0
8    5123  2021-01-03 00:00:00           cash    sales$   90.0
9    5123  2021-01-03 00:00:00           cash     items   15.0
10   5123  2021-01-03 00:00:00           card    sales$  150.0
11   5123  2021-01-03 00:00:00           card     items   20.0

- not_speshal

---

差不多了！但是当我这样做时，我会丢失大约10%的数据（我的实际数据有数百万行）。您能否描述一下那些链接方法正在做什么？ - edutt

按不同列进行分组，并计算值的滚动2期均值。然后我们使用此方法过滤介于0.5和2之间的值。哪个部分不清楚？ - not_speshal

这里的.shift()是做什么的？我发现运行没有使用.shift()的代码会产生一个有8个非空条目的系列，而使用.shift()运行会产生正确的有4个非空条目的系列。我以为shift会将行向下移动，所以我本来认为系列中的最后一个条目537.5（在不使用.shift()的情况下）会被删除，留下7个非空条目。与shift问题无关，看起来这两个异常值行只是被完全删除了，而不是用平均值替换，这就解释了为什么我现在缺少数据。 - edutt

明白了。我会编辑您的帖子以展示我的工作。最终在平均数序列中添加了sort_index()，然后使用np.where()将原始数据框中的异常值替换为平均值。 - edutt

@edutt - 我稍微修改了你的编辑，使用 df.where 代替 np.where。这样就不需要 import numpy :) - not_speshal