使用groupby删除Pandas数据框中的异常值 [英] Remove outliers in Pandas dataframe with groupby

问题描述

我有一个报告日期，时间间隔和全年总量的数据框.我希望能够在每个时间间隔内删除异常值.

I have a dataframe of Report Date, Time Interval and Total Volume for a full year. I would like to be able to remove outliers within each Time Interval.

据我所知...

dft.head()

    Report Date Time Interval   Total Volume
5784    2016-03-01  24  467.0
5785    2016-03-01  25  580.0
5786    2016-03-01  26  716.0
5787    2016-03-01  27  803.0
5788    2016-03-01  28  941.0

所以我计算分位数

low = .05
high = .95
dfq = dft.groupby(['Time Interval']).quantile([low, high])
print(dfq).head()

                    Total Volume
Time Interval                   
24            0.05        420.15
              0.95        517.00
25            0.05        521.90
              0.95        653.55
26            0.05        662.75

然后，我希望能够使用类似的东西使用它们来消除每个时间间隔内的离群值...

And then I'd like to be able to use them to remove outliers within each Time Interval using something like this...

dft = dft.apply(lambda x: x[(x>dfq.loc[low,x.name]) & (x < dfq.loc[high,x.name])], axis=0)

非常感谢任何指针/建议.

Any pointers/advice much appreciated.

推荐答案

一种方法是过滤出以下内容:

One way is to filter out as follows:

In [11]: res = df.groupby("Date")["Interval"].quantile([0.05, 0.95]).unstack(level=1)

In [12]: res
Out[12]:
             0.05   0.95
Date
2016-03-01  489.6  913.4

现在，我们可以使用loc和过滤器为每一行查找这些值:

Now we can lookup these values for each row using loc and filter:

In [13]: (res.loc[df.Date, 0.05] < df.Interval.values) & (df.Interval.values < res.loc[df.Date, 0.95])
Out[13]:
Date
2016-03-01    False
2016-03-01     True
2016-03-01     True
2016-03-01     True
2016-03-01    False
dtype: bool

In [14]: df.loc[((res.loc[df.Date, 0.05] < df.Interval.values) & (df.Interval.values < res.loc[df.Date, 0.95])).values]
Out[14]:
   Report        Date  Time  Interval  Total Volume
1    5785  2016-03-01    25     580.0           NaN
2    5786  2016-03-01    26     716.0           NaN
3    5787  2016-03-01    27     803.0           NaN

注意:按时间间隔"进行分组的工作原理相同，但在您的示例中不会过滤任何行！

这篇关于使用groupby删除Pandas数据框中的异常值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！