What is the simple formula for “Group By Having” in PySpark?

---

You can use the following syntax to perform the equivalent of a SQL ‘GROUP BY HAVING’ statement in PySpark:

from pyspark.sql.functions import *

#create new DataFrame that only contains rows where team count>2
df_new = df.groupBy('team')
           .agg(count('team').alias('n'))
           .filter(col('n')>2)

This particular example finds the count of each unique value in the team column and then filters the DataFrame to only contain rows where the count of the team column is greater than 2.

The following example shows how to use this syntax in practice.

Example: How to Use “Group By Having” in PySpark

Suppose we have the following PySpark DataFrame that contains information about points scored by various basketball players:

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()

#define data
data = [['A', 11], 
        ['A', 8], 
        ['A', 10], 
        ['B', 6], 
        ['B', 6], 
        ['C', 5],
        ['C', 15],
        ['C', 31],
        ['D', 24]]
  
#define column names
columns = ['team', 'points'] 
  
#create dataframe using data and column names
df = spark.createDataFrame(data, columns) 
  
#view dataframe
df.show()

+----+------+
|team|points|
+----+------+
|   A|    11|
|   A|     8|
|   A|    10|
|   B|     6|
|   B|     6|
|   C|     5|
|   C|    15|
|   C|    31|
|   D|    24|
+----+------+

We can use the following syntax to filter the DataFrame for rows where the count of values in the team column is greater than 2:

from pyspark.sql.functions import *

#create new DataFrame that only contains rows where team count>2
df_new = df.groupBy('team')
           .agg(count('team').alias('n'))
           .filter(col('n')>2)

#view new DataFrame
df_new.show()

+----+---+
|team|  n|
+----+---+
|   A|  3|
|   C|  3|
+----+---+

Notice that each of the rows in the filtered DataFrame have a team count greater than 2.

Note that we could also filter based on a different metric.

For example, we can use the following syntax to calculate the average points value for each team and then filter the DataFrame to only contain rows where the average points value is greater than 10:

from pyspark.sql.functions import *

#create new DataFrame that only contains rows where points avg>10
df_new = df.groupBy('team')
           .agg(avg('points').alias('avg'))
           .filter(col('avg')>10)

#view new DataFrame
df_new.show()

+----+----+
|team| avg|
+----+----+
|   C|17.0|
|   D|24.0|
+----+----+

Notice that the resulting DataFrame only contains rows for the teams with an average points value greater than 10.

Note: You can find the complete documentation for the PySpark filter function .

Additional Resources

The following tutorials explain how to perform other common tasks in PySpark: