Aggregations and Null Handling

groupBy then agg

groupBy("city") followed by agg(...) computes summaries per group with functions like count, sum, avg, min, max and countDistinct. Aggregations on a large dataset trigger a shuffle, because all rows of a group must meet on one machine. Nulls need care: aggregate functions such as sum and avg ignore nulls, but a group where every value is null returns NULL. Decide explicitly whether to fill nulls (na.fill), drop rows (na.drop) or keep them, and remember that comparisons with null are neither true nor false, so use isNull() and isNotNull().

Per-city summary, run

I ran this on Apache Spark 4.0.0 (PySpark, local mode, in the official Docker image). Mumbai's only order has a null amount, so its sum and average are NULL, while Delhi's three orders give 430.0 and 143.3.

orders.groupBy("city").agg(F.count("*").alias("orders"), F.sum("amount").alias("revenue"), F.round(F.avg("amount"), 1).alias("avg")).orderBy("city").show()

Output:

+------+------+-------+-----+
|  city|orders|revenue|  avg|
+------+------+-------+-----+
| delhi|     3|  430.0|143.3|
|mumbai|     1|   NULL| NULL|
|  pune|     2|  320.0|160.0|
+------+------+-------+-----+

Counting and filling nulls, run

I ran this on Apache Spark 4.0.0 (PySpark, local mode, in the official Docker image). One order has a null amount. After filling nulls with 0.0 the total revenue is 750.0.

print(orders.filter(F.col("amount").isNull()).count(), orders.na.fill({"amount": 0.0}).agg(F.sum("amount")).first()[0])

Output:

1 750.0

Quick check: What does `sum` do with null values in a group?

• Doubles the result
• Treats them as 1
• Fails always
• Ignores them (and returns NULL only if all values are null)