> ## Documentation Index
> Fetch the complete documentation index at: https://rajanand.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Spark: Handling Null Values

`NULL` values (often represented as `NA` or `null`) are common in datasets and need to be handled appropriately during data processing. Spark provides several functions to handle null values in DataFrames.

***

## 1. **Common Functions for Handling Null Values**

* **`dropna()`**: Drops rows or columns with null values.
* **`fillna()`**: Fills null values with a specified value.
* **`isnull()`**: Checks if a column contains null values.
* **`coalesce()`**: Returns the first non-null value in a list of columns.
* **`na.drop()`**: Alias for `dropna()`.
* **`na.fill()`**: Alias for `fillna()`.

## 2. **Examples**

### **Example 1: Dropping Rows with Null Values**

**PySpark:**

```python theme={"system"}
from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("NullHandling").getOrCreate()

# Create DataFrame with null values
data = [("Anand", 25), ("Bala", None), ("Kavitha", 28), ("Raj", None)]
columns = ["Name", "Age"]

df = spark.createDataFrame(data, columns)

# Drop rows with null values in any column
df_dropped = df.dropna()
df_dropped.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT * FROM people 
WHERE Age IS NOT NULL;
```

**Output:**

```
+-------+---+
|   Name|Age|
+-------+---+
|  Anand| 25|
|Kavitha| 28|
+-------+---+
```

### **Example 2: Filling Null Values**

**PySpark:**

```python theme={"system"}
# Fill null values in the 'Age' column with 0
df_filled = df.fillna({"Age": 0})
df_filled.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT Name, COALESCE(Age, 0) AS Age 
FROM people;
```

**Output:**

```
+-------+---+
|   Name|Age|
+-------+---+
|  Anand| 25|
|   Bala|  0|
|Kavitha| 28|
|    Raj|  0|
+-------+---+
```

### **Example 3: Checking for Null Values**

**PySpark:**

```python theme={"system"}
from pyspark.sql.functions import isnull

# Add a column indicating whether 'Age' is null
df_with_null_check = df.withColumn("IsAgeNull", isnull("Age"))
df_with_null_check.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT *, Age IS NULL AS IsAgeNull 
FROM people;
```

**Output:**

```
+-------+----+----------+
|   Name| Age|IsAgeNull|
+-------+----+----------+
|  Anand|  25|     false|
|   Bala|null|      true|
|Kavitha|  28|     false|
|    Raj|null|      true|
+-------+----+----------+
```

### **Example 4: Using `coalesce()` to Handle Nulls**

**PySpark:**

```python theme={"system"}
from pyspark.sql.functions import coalesce

# Replace null values in 'Age' with a default value (e.g., 30)
df_with_coalesce = df.withColumn("Age", coalesce("Age", lit(30)))
df_with_coalesce.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT Name, COALESCE(Age, 30) AS Age 
FROM people;
```

**Output:**

```
+-------+---+
|   Name|Age|
+-------+---+
|  Anand| 25|
|   Bala| 30|
|Kavitha| 28|
|    Raj| 30|
+-------+---+
```

### **Example 5: Dropping Columns with Null Values**

**PySpark:**

```python theme={"system"}
# Drop columns with null values
df_dropped_columns = df.dropna(how="all", subset=["Age"])
df_dropped_columns.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT * FROM people 
WHERE Age IS NOT NULL;
```

**Output:**

```
+-------+---+
|   Name|Age|
+-------+---+
|  Anand| 25|
|Kavitha| 28|
+-------+---+
```

### **Example 6: Filling Nulls with Column-Specific Values**

**PySpark:**

```python theme={"system"}
# Fill nulls in 'Age' with 0 and nulls in 'Salary' with 1000
df_filled = df.fillna({"Age": 0, "Salary": 1000})
df_filled.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT Name, 
       COALESCE(Age, 0) AS Age, 
       COALESCE(Salary, 1000) AS Salary 
FROM people;
```

**Output:**

```
+-------+---+------+
|   Name|Age|Salary|
+-------+---+------+
|  Anand| 25|  3000|
|   Bala|  0|  1000|
|Kavitha| 28|  3500|
|    Raj|  0|  1000|
+-------+---+------+
```

### **Example 7: Dropping Rows with Nulls in Specific Columns**

**PySpark:**

```python theme={"system"}
# Drop rows where 'Age' is null
df_dropped = df.dropna(subset=["Age"])
df_dropped.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT * FROM people 
WHERE Age IS NOT NULL;
```

**Output:**

```
+-------+---+
|   Name|Age|
+-------+---+
|  Anand| 25|
|Kavitha| 28|
+-------+---+
```

## 3. **Common Use Cases**

* Cleaning datasets by removing or filling null values.
* Preparing data for machine learning by handling missing values.
* Ensuring data quality by identifying and addressing null values.

## 4. **Performance Considerations**

* Use `dropna()` judiciously, as it can reduce the size of the DataFrame.
* Use `fillna()` with caution, as filling nulls with arbitrary values can introduce bias.
* Use `coalesce()` for efficient handling of nulls in expressions.

## 5. **Key Takeaways**

1. `NULL` values are common in datasets and need to be handled appropriately.
2. Spark provides functions like `dropna()`, `fillna()`, and `coalesce()` to handle nulls.
3. Handling nulls is generally efficient, but operations like `dropna()` can reduce the size of the DataFrame.
4. In Spark SQL, similar functionality can be achieved using `IS NULL`, `COALESCE`, and `CASE` statements.