3.PySpark DataFrame groupBy Operation

---

The groupBy operation in PySpark is used to group data based on one or more columns. Once grouped, you can apply various aggregation functions like count, sum, avg, max, min, etc.

Syntax

DataFrame.groupBy(*cols)

Here, *cols can be one or more column names or expressions.

Common Aggregation Functions

• count()
• sum(column)
• avg(column)
• max(column)
• min(column)
• agg(*exprs)

Examples

Sample DataFrame

data = [
    ("Alice", "Sales", 3000),
    ("Bob", "Sales", 4000),
    ("Alice", "Sales", 4000),
    ("Catherine", "HR", 4000),
    ("David", "HR", 5000),
    ("Eve", "IT", 3000)
]
columns = ["Name", "Department", "Salary"]

df = spark.createDataFrame(data, schema=columns)
df.show()

1. Group By Single Column and Count

df.groupBy("Department").count().show()

Output:

+----------+-----+
|Department|count|
+----------+-----+
|        HR|    2|
|       IT|    1|
|     Sales|    3|
+----------+-----+

2. Group By Multiple Columns and Count

df.groupBy("Department", "Name").count().show()

Output:

+----------+-----+-----+
|Department| Name|count|
+----------+-----+-----+
|     Sales|Alice|    2|
|        HR|David|    1|
|        HR|Catherine|    1|
|       IT|  Eve|    1|
|     Sales|  Bob|    1|
+----------+-----+-----+

3. Group By with Sum Aggregation

df.groupBy("Department").sum("Salary").show()

Output:

+----------+-----------+
|Department|sum(Salary)|
+----------+-----------+
|        HR|      9000 |
|       IT|      3000  |
|     Sales|     11000 |
+----------+-----------+

4. Group By with Multiple Aggregations

df.groupBy("Department").agg(
    count("*").alias("Total_Count"),
    sum("Salary").alias("Total_Salary"),
    avg("Salary").alias("Average_Salary"),
    max("Salary").alias("Max_Salary"),
    min("Salary").alias("Min_Salary")
).show()

Output:

+----------+-----------+-----------+--------------+-----------+-----------+
|Department|Total_Count|Total_Salary|Average_Salary|Max_Salary|Min_Salary|
+----------+-----------+-----------+--------------+-----------+-----------+
|        HR|          2|       9000|         4500.0|       5000|       4000|
|       IT|          1|       3000|         3000.0|       3000|       3000|
|     Sales|          3|      11000|         3666.67|       4000|       3000|
+----------+-----------+-----------+--------------+-----------+-----------+

5. Using agg Method with Expressions

from pyspark.sql.functions import expr

df.groupBy("Department").agg(
    expr("count(*) as Total_Count"),
    expr("sum(Salary) as Total_Salary"),
    expr("avg(Salary) as Average_Salary"),
    expr("max(Salary) as Max_Salary"),
    expr("min(Salary) as Min_Salary")
).show()

Output is the same as above.

6. Group By with Having Clause (Filtering Groups)

df.groupBy("Department").agg(
    sum("Salary").alias("Total_Salary")
).filter(col("Total_Salary") > 5000).show()

Output:

+----------+-----------+
|Department|Total_Salary|
+----------+-----------+
|        HR|       9000|
|     Sales|      11000|
+----------+-----------+

---

Head to Next

Pages: 1 2 3 4 5 6 7 8 9 10 11 12