Search code examples
pythonsqlpandasfiltergroup-by

Pandas equivalent of GROUP BY HAVING in SQL

What is the most efficient way to use groupby and in parallel apply a filter in pandas?

Basically I am asking for the equivalent in SQL of

select *
...
group by col_name
having condition

I think there are many uses cases ranging from conditional means, sums, conditional probabilities, etc. which would make such a command very powerful.

I need a very good performance, so ideally such a command would not be the result of several layered operations done in python.

Solution

  • As mentioned in unutbu's comment, groupby's filter is the equivalent of SQL'S 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

    You can write more complicated functions (these are applied to each group), provided they return a plain ol' bool:

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

    Note: potentially there is a bug where you can't write you function to act on the columns you've used to groupby... a workaround is the groupby the columns manually i.e. g = df.groupby(df['A'])).