How to check whether multiple columns values of a row are not null and then add a true/false resulting column in Spark Scala

user_14294316 0
Viewed 1769

Hi how's it going? Here are my two dataframes:

  val id_df = Seq(("1","gender"),("2","city"),("3","state"),("4","age")).toDF("id","type")

  val main_df = Seq(("male","los angeles","null"),("female","new york","new york")).toDF("1","2","3")

Here's what they look like in tabular form:

enter image description here

enter image description here

and this is what I would like the resultant dataframe to look like:

enter image description here

I want to check for all the ids in id_df, if they exist in main_df's columns, then check whether all the id values for that row are not null. If they're all not null, then we put "true" in the meets condition column for that row, otherwise we put "false". Notice how id number 4 for age isn't in main_df's columns, so we just ignore it.

How would I do this?

Thanks so much and have a great day.

apache-spark scala
1 Answers

Allow me to start with two short observations:

  1. I believe that it would be safer to avoid naming our columns with single numbers. Think of the case where we need to evaluate the expression 1 is not null. Here it is ambiguous whether we mean column 1 or the value 1 itself.
  2. As far as I am aware, it is not performant to store and process the target columns through a dataframe. That would create an overhead that can be easily avoided by using a single scala collection i.e: Seq, Array, Set, etc.

And here is the solution to your problem:

import org.apache.spark.sql.functions.col

val id_df = Seq(
  ("c1","gender"),
  ("c2","city"),
  ("c3","state"),
  ("c4","age")
).toDF("id","type")

val main_df = Seq(
    ("male", "los angeles", null),
    ("female", "new york", "new york"),
    ("trans", null, "new york")
).toDF("c1","c2","c3")

val targetCols = id_df.collect()
                      .map{_.getString(0)} //get id
                      .toSet //convert current sequence to a set (required for the intersection)
                      .intersect(main_df.columns.toSet) //get common columns with main_df
                      .map(col(_).isNotNull) //convert c1,..cN to col(c[i]).isNotNull
                      .reduce(_ && _) // apply the AND operator between items

// (((c1 IS NOT NULL) AND (c2 IS NOT NULL)) AND (c3 IS NOT NULL))

main_df.withColumn("meets_conditions", targetCols).show(false)

// +------+-----------+--------+----------------+
// |c1    |c2         |c3      |meets_conditions|
// +------+-----------+--------+----------------+
// |male  |los angeles|null    |false           |
// |female|new york   |new york|true            |
// |trans |null       |new york|false           |
// +------+-----------+--------+----------------+
user_7503760
Related