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:

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:

SELECT * FROM people 
WHERE Age IS NOT NULL;

Output:

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

​
Example 2: Filling Null Values

PySpark:

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

Spark SQL:

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:

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:

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:

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:

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:

# Drop columns with null values
df_dropped_columns = df.dropna(how="all", subset=["Age"])
df_dropped_columns.show()

Spark SQL:

SELECT * FROM people 
WHERE Age IS NOT NULL;

Output:

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

​
Example 6: Filling Nulls with Column-Specific Values

PySpark:

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

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:

# Drop rows where 'Age' is null
df_dropped = df.dropna(subset=["Age"])
df_dropped.show()

Spark SQL:

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.