Spark Drop Rows with NULL Values in DataFrame

Spark provides drop() function in DataFrameNaFunctions class that is used to drop rows with null values in one or multiple(any/all) columns in DataFrame/Dataset. While reading data from files, Spark API's like DataFrame and Dataset assigns NULL values for empty value on columns.

Something based on a need you many needs to remove these rows that have null values as part of data cleansing. Usually, in traditional SQL, you need to check on every column if the value is null in order to drop however, the Spark drop() function in DataFrameNaFunctions class drop rows that have null values in any columns.

Related:

1. Spark drop() Syntax

Spark drop() function has several overloaded signatures that take different combinations as parameters that are used to remove Rows with NULL values on single, any, all, multiple DataFrame columns. drop() function returns a new DataFrame after dropping the rows/records.

Syntax:


// Spark drop() Syntax

  def drop() : DataFrame
  def drop(how : String) : DataFrame
  def drop(cols : Array[Predef.String]) :DataFrame
  def drop(cols : Seq[Predef.String]) : DataFrame
  def drop(how : String, cols : Array[String]) : DataFrame
  def drop(how : String, cols : Seq[String]) : DataFrame
  def drop(minNonNulls : Int) : DataFrame
  def drop(minNonNulls : Int, cols : Array[String]) : DataFrame
  def drop(minNonNulls : Int, cols : Seq[String]) : DataFrame

2. Spark Drop Rows with NULL Values

Using drop() function of DataFrameNaFunctions you can remove rows with null values in one or multiple(any/all) columns of DataFrame.

DataFrame/Dataset has a variable na which is an instance of class DataFrameNaFunctions. DataFrameNaFunctions class also have method fill() to replace NULL values with empty string on Spark DataFrame

Before we start, Let’s Read CSV File into DataFrame, when we have no values on certain rows of String and Integer columns, spark assigns null values to these empty columns.

The file we are using here is available at GitHub small_zipcode.csv


val filePath="src/main/resources/small_zipcode.csv"
val df=spark.read.options(
  Map("inferSchema"->"true","delimiter"->",","header"->"true"))
  .csv(filePath)
  df.printSchema()
  df.show(false)

This yields the below output. As you see columns type, city and population columns have null values.


// Output:
root
 |-- id: integer (nullable = true)
 |-- zipcode: integer (nullable = true)
 |-- type: string (nullable = true)
 |-- city: string (nullable = true)
 |-- state: string (nullable = true)
 |-- population: integer (nullable = true)

+---+-------+--------+-------------------+-----+----------+
|id |zipcode|type    |city               |state|population|
+---+-------+--------+-------------------+-----+----------+
|1  |704    |STANDARD|null               |PR   |30100     |
|2  |704    |null    |PASEO COSTA DEL SUR|PR   |null      |
|3  |709    |null    |BDA SAN LUIS       |PR   |3700      |
|4  |76166  |UNIQUE  |CINGULAR WIRELESS  |TX   |84000     |
|5  |76177  |STANDARD|null               |TX   |null      |
+---+-------+--------+-------------------+-----+----------+

As you see above DataFrame most of the rows have NULL values except record with id=4. Now, let’s see how to drop or remove rows with null values on DataFrame.

3. Drop Rows with NULL Values in Any Columns

As you have seen at the beginning drop() function has several overloaded signatures. By default drop() without arguments remove all rows that have null values on any column of DataFrame.


df.na.drop().show(false)

This removes all rows with null values and returns the clean DataFrame with id=4 where it doesn't have any NULL values.


// Output:
+---+-------+------+-----------------+-----+----------+
|id |zipcode|type  |city             |state|population|
+---+-------+------+-----------------+-----+----------+
|4  |76166  |UNIQUE|CINGULAR WIRELESS|TX   |84000     |
+---+-------+------+-----------------+-----+----------+

Alternatively you can also get same result with na.drop("any").


// Accepts all or any
df.na.drop("any").show(false)

4. Drop Rows with NULL Values on All Columns

Below example drops all rows that has NULL values on all columns. Our DataFrame doesn't have null values on all rows hence below examples returns all rows.


//m
df.na.drop("all").show(false)

5. Drop Rows with NULL Values on Selected Columns

In order to remove Rows with NULL values on selected columns of Spark DataFrame, use drop(columns:Seq[String]) or drop(columns:Array[String]). To these functions pass the names of the columns you wanted to check for NULL values to delete rows.


df.na.drop(Seq("population","type")).show(false)

The above example remove rows that have NULL values on population and type selected columns.


// Output:
+---+-------+--------+-----------------+-----+----------+
|id |zipcode|type    |city             |state|population|
+---+-------+--------+-----------------+-----+----------+
|1  |704    |STANDARD|null             |PR   |30100     |
|4  |76166  |UNIQUE  |CINGULAR WIRELESS|TX   |84000     |
+---+-------+--------+-----------------+-----+----------+

6. Complete Example of Drop rows with NULL Values

Below is complete example for reference.


package com.aistudylabs.spark.dataframe

import org.apache.spark.sql.SparkSession

object RemoveNullRowsExample extends App{

  val spark: SparkSession = SparkSession.builder()
    .master("local[1]")
    .appName("AIStudyLabs.com")
    .getOrCreate()

  val filePath="src/main/resources/small_zipcode.csv"

  val df = spark.read.options(Map("inferSchema"->"true","delimiter"->",","header"->"true")).csv(filePath)
  df.printSchema()
  df.show(false)

  df.na.drop().show(false)
  // All/any
  df.na.drop("any").show(false)

  df.na.drop(Seq("population","type")).show(false)
}

This complete code is available at GitHubproject.

Conclusion

In this article, you have learned how to delete/remove/drop rows with NULL values in any, all, sing, multiple columns in Spark Dataframe using drop() function of DataFrameNaFunctions. This comes in handy when you need to clean up the DataFrame rows before processing. Hope it is helpful.

Happy Learning !!