Table of Contents
PySpark is a powerful tool for data analysis that allows users to perform SQL-like operations on large datasets. One of the key functionalities of SQL is the “Group By Having” clause, which allows users to filter grouped data based on specific conditions. With PySpark, this can be achieved by using the “groupBy” function, followed by the “having” function, which allows users to specify the desired conditions for filtering. This feature is particularly useful for data analysts looking to group and filter data based on certain criteria, providing a more efficient and comprehensive approach to data analysis. By utilizing PySpark’s “Group By Having” clause, analysts can gain valuable insights and make data-driven decisions with ease.
PySpark: A Simple Formula for “Group By Having”
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: