how to filter out a null value from spark dataframe

by

Let’s say you have this data setup (so that results are reproducible):

// declaring data types
case class Company(cName: String, cId: String, details: String)
case class Employee(name: String, id: String, email: String, company: Company)

// setting up example data
val e1 = Employee("n1", null, "[email protected]", Company("c1", "1", "d1"))
val e2 = Employee("n2", "2", "[email protected]", Company("c1", "1", "d1"))
val e3 = Employee("n3", "3", "[email protected]", Company("c1", "1", "d1"))
val e4 = Employee("n4", "4", "[email protected]", Company("c2", "2", "d2"))
val e5 = Employee("n5", null, "[email protected]", Company("c2", "2", "d2"))
val e6 = Employee("n6", "6", "[email protected]", Company("c2", "2", "d2"))
val e7 = Employee("n7", "7", "[email protected]", Company("c3", "3", "d3"))
val e8 = Employee("n8", "8", "[email protected]", Company("c3", "3", "d3"))
val employees = Seq(e1, e2, e3, e4, e5, e6, e7, e8)
val df = sc.parallelize(employees).toDF

Data is:

+----+----+---------+---------+
|name|  id|    email|  company|
+----+----+---------+---------+
|  n1|null|[email protected]|[c1,1,d1]|
|  n2|   2|[email protected]|[c1,1,d1]|
|  n3|   3|[email protected]|[c1,1,d1]|
|  n4|   4|[email protected]|[c2,2,d2]|
|  n5|null|[email protected]|[c2,2,d2]|
|  n6|   6|[email protected]|[c2,2,d2]|
|  n7|   7|[email protected]|[c3,3,d3]|
|  n8|   8|[email protected]|[c3,3,d3]|
+----+----+---------+---------+

Now to filter employees with null ids, you will do —

df.filter("id is null").show

which will correctly show you following:

+----+----+---------+---------+
|name|  id|    email|  company|
+----+----+---------+---------+
|  n1|null|[email protected]|[c1,1,d1]|
|  n5|null|[email protected]|[c2,2,d2]|
+----+----+---------+---------+

Coming to the second part of your question, you can replace the null ids with 0 and other values with 1 with this —

df.withColumn("id", when($"id".isNull, 0).otherwise(1)).show

This results in: 

+----+---+---------+---------+
|name| id|    email|  company|
+----+---+---------+---------+
|  n1|  0|[email protected]|[c1,1,d1]|
|  n2|  1|[email protected]|[c1,1,d1]|
|  n3|  1|[email protected]|[c1,1,d1]|
|  n4|  1|[email protected]|[c2,2,d2]|
|  n5|  0|[email protected]|[c2,2,d2]|
|  n6|  1|[email protected]|[c2,2,d2]|
|  n7|  1|[email protected]|[c3,3,d3]|
|  n8|  1|[email protected]|[c3,3,d3]|
+----+---+---------+---------+

More Related Contents:

  1. Spark – load CSV file as DataFrame?
  2. How to define partitioning of DataFrame?
  3. Encoder error while trying to map dataframe row to updated row
  4. Spark Scala: How to convert Dataframe[vector] to DataFrame[f1:Double, …, fn: Double)]
  5. How to define schema for custom type in Spark SQL?
  6. How can I change column types in Spark SQL’s DataFrame?
  7. Spark unionAll multiple dataframes
  8. How can I pass extra parameters to UDFs in Spark SQL?
  9. SparkSQL: How to deal with null values in user defined function?
  10. How to split a dataframe into dataframes with same column values?
  11. How to aggregate values into collection after groupBy?
  12. Dropping a nested column from Spark DataFrame
  13. How to define a custom aggregation function to sum a column of Vectors?
  14. Spark DataFrame: does groupBy after orderBy maintain that order?
  15. Better way to convert a string field into timestamp in Spark
  16. Filling gaps in timeseries Spark
  17. Spark UDAF with ArrayType as bufferSchema performance issues
  18. Apache Spark how to append new column from list/array to Spark dataframe
  19. How to use COGROUP for large datasets
  20. What are possible reasons for receiving TimeoutException: Futures timed out after [n seconds] when working with Spark [duplicate]
  21. Why does join fail with “java.util.concurrent.TimeoutException: Futures timed out after [300 seconds]”?
  22. Create new Dataframe with empty/null field values
  23. Perform a typed join in Scala with Spark Datasets
  24. DataFrame-ified zipWithIndex
  25. Spark: Add column to dataframe conditionally
  26. Apache Spark, add an “CASE WHEN … ELSE …” calculated column to an existing DataFrame
  27. Calculate Cosine Similarity Spark Dataframe
  28. Why is join not possible after show operator?
  29. Difference between two rows in Spark dataframe
  30. Filter spark DataFrame on string contains

Leave a Comment