# Aggregations and Null Handling — Apache Spark: Big Data Processing with DataFrames

Source: https://www.geekswithgeeks.com/en/spark/df-aggregation

> Group and summarise data and deal with missing values deliberately.

## 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.

```python
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.

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

Output:

```
1 750.0
```

**Quiz:** What does `sum` do with null values in a group?

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

*Answer:* Ignores them (and returns NULL only if all values are null). Aggregate functions skip nulls, which can hide missing data, so check null counts.