Pandas中的GROUP BY HAVING在SQL中的等效操作

Question

使用groupby的最有效方法是并行应用筛选器，如何实现?

基本上，我在问SQL中下面这个语句的等效方式

select *
...
group by col_name
having condition

我认为这个命令有很多用途，包括条件均值、求和、条件概率等，这将使这个命令非常强大。

我需要非常好的性能，所以理想情况下，这样的命令不应该是在Python中执行多层操作的结果。

- Mannaggia

---

@AndyHayden写了一个使用groupby-filter的不错的例子（链接：https://dev59.com/YmYr5IYBdhLWcg3wucew#18357933）。我认为`filter`是pandas版本的`having条件`。 - unutbu

Answer 1

正如unutbu的评论中所提到的，groupby的过滤器相当于SQL的HAVING:

In [11]: df = pd.DataFrame([[1, 2], [1, 3], [5, 6]], columns=['A', 'B'])

In [12]: df
Out[12]:
   A  B
0  1  2
1  1  3
2  5  6

In [13]: g = df.groupby('A')  #  GROUP BY A

In [14]: g.filter(lambda x: len(x) > 1)  #  HAVING COUNT(*) > 1
Out[14]:
   A  B
0  1  2
1  1  3

您可以编写更复杂的函数（这些函数将应用于每个组），只要它们返回一个普通的布尔值：

In [15]: g.filter(lambda x: x['B'].sum() == 5)
Out[15]:
   A  B
0  1  2
1  1  3

注意：可能存在一个错误，即您不能编写函数来操作您已用于groupby的列...解决方法是手动groupby列，即g = df.groupby(df['A']))。

- Andy Hayden

---

也许将其添加到与SQL比较的部分？（例如此示例） - Jeff

@Jeff 好主意，看着这个，我想知道是否应该考虑同时使用agg和filter的示例（我认为没有一种容易的方法可以在不进行第二次groupby的情况下轻松完成...）这可能是这个问题:s - Andy Hayden

只需链接它们（但您需要第二个 groupby）：DataFrame([[1, 2], [1, 3], [2, 5], [2, 8], [5, 6]], columns=['A', 'B']).groupby('A').filter(lambda x: len(x)>1).groupby('A').sum() - Jeff

对于更复杂的情况，我不得不使用.apply()。https://dev59.com/wmAg5IYBdhLWcg3wm7_7#49476152 - citynorman

很遗憾，filter函数会将DataFrameGroupBy对象“展平”为一个DataFrame，因此如果你想对所选的分组进行操作，你需要进行两次分组。 - undefined

Answer 2

什么是在pandas中使用groupby和并行应用过滤器的最有效方法？

使用groupby.transform +布尔索引

尽管在pandas中等价的语法是groupby.filter，但它非常慢。如果性能很重要，与其在groupby操作期间进行过滤，不如先进行groupby操作，然后在后续步骤中对数据框进行过滤。因为groupby.filter对每个组都调用Python函数（例如lambda），而groupby.transform在整个数据框上调用Cython优化函数，如果有很多组，后者会更快。

使用groupby.transform的重点是它返回一个与原始数据框具有相同索引的数据框，其中填充了聚合值。由于它的输出具有相同的索引，因此可以用它来过滤原始数据框。

所以等价于

SELECT * FROM df GROUP BY colA HAVING COUNT(*) > 1

是

df[df.groupby('colA').transform('size') > 1]

和相等的

SELECT * FROM df GROUP BY colA HAVING SUM(colB) > 5

是

df[df.groupby('colA')['colB'].transform('sum') > 5]

无论如何，正如下面的性能图所示，随着组数的增加，`groupby.transform` + 布尔索引的执行速度比`groupby.filter`要快得多；例如，对于10k个组，它的速度快1000倍。实际上，如果您的数据框有数百万个组，`groupby.filter`可能甚至无法运行，而`groupby.transform` + 布尔索引将在合理的时间内完成任务。

用于生成上述图表的代码

import perfplot
import pandas as pd
import numpy as np

def groupby_filter(df):
    g = df.groupby('A')
    return g.filter(lambda x: x['B'].sum() > 5)

def groupby_transform(df):
    g = df.groupby('A')
    return df[g['B'].transform('sum') > 5]


perfplot.plot(
    kernels=[groupby_filter, groupby_transform],
    n_range=[2**k for k in range(16)],
    setup=lambda n: pd.DataFrame({
        'A': np.random.choice(n, size=n, replace=False), 
        'B': np.random.randint(n, size=n)}),
    xlabel='Number of groups'
)

- cottontail

Answer 3

我按州和县分组，其中最大值大于20，然后使用数据框的loc子查询结果为True

counties=df.groupby(['state','county'])['field1'].max()>20
counties=counties.loc[counties.values==True]

- Golden Lion