Update a column value in a spark dataframe based another column

Anandha Geethan

I have a spark dataframe as mentioned below.

val data = spark.sparkContext.parallelize(Seq(
    (1,"", "SNACKS", "BISCUITS - AMBIENT", "BISCUITS - AMBIENT", "", "REFLETS DE FRANCE CROQUANT", "UNCOATED  BISCUIT", "NO PROMOTION", "", "", "400G","",""),
    (2,"GROCERY", "BISCUITS", "SWEET BISCUITS ", "BISCUITS - AMBIENT", "", "", "AMBIENT BISCUIT", "NO PROMOTION", "", "", "400G","","CHOCOS")
  ))
  .toDF("id", "c4", "c1001", "c1002", "c1003", "c1008", "c1008_unmasked", "c1009", "c1011", "c1012", "c1013", "c1015", "c1016", "c1016_unmasked")

data.show(false)

SAMPLE INPUT:

+---+-------+--------+------------------+------------------+-----+--------------------------+-----------------+------------+-----+-----+-----+-----+--------------+
|id |c4     |c1001   |c1002             |c1003             |c1008|c1008_unmasked            |c1009            |c1011       |c1012|c1013|c1015|c1016|c1016_unmasked|
+---+-------+--------+------------------+------------------+-----+--------------------------+-----------------+------------+-----+-----+-----+-----+--------------+
|1  |       |SNACKS  |BISCUITS - AMBIENT|BISCUITS - AMBIENT|     |REFLETS DE FRANCE CROQUANT|UNCOATED  BISCUIT|NO PROMOTION|     |     |400G |     |              |
|2  |GROCERY|BISCUITS|SWEET BISCUITS    |BISCUITS - AMBIENT|     |                          |AMBIENT BISCUIT  |NO PROMOTION|     |     |400G |     |CHOCOS        |
+---+-------+--------+------------------+------------------+-----+--------------------------+-----------------+------------+-----+-----+-----+-----+--------------+

Need to populate column cXXXX with value "MASKED" only if the same cXXXX_unmasked has value in it. Please check the sample output for better understading.

+---+-------+--------+------------------+------------------+------+--------------------------+-----------------+------------+-----+-----+-----+------+--------------+
|id |c4     |c1001   |c1002             |c1003             |c1008 |c1008_unmasked            |c1009            |c1011       |c1012|c1013|c1015|c1016 |c1016_unmasked|
+---+-------+--------+------------------+------------------+------+--------------------------+-----------------+------------+-----+-----+-----+------+--------------+
|1  |       |SNACKS  |BISCUITS - AMBIENT|BISCUITS - AMBIENT|MASKED|REFLETS DE FRANCE CROQUANT|UNCOATED  BISCUIT|NO PROMOTION|     |     |400G |      |              |
|2  |GROCERY|BISCUITS|SWEET BISCUITS    |BISCUITS - AMBIENT|      |                          |AMBIENT BISCUIT  |NO PROMOTION|     |     |400G |MASKED|CHOCOS        |
+---+-------+--------+------------------+------------------+------+--------------------------+-----------------+------------+-----+-----+-----+------+--------------+

Thanks in advance

Lamanus

Here is my try.

val cols = data.columns.filter(_.endsWith("_unmasked"))

val new_data = cols.foldLeft(data) { (df, c) => 
    df.withColumn(c.split("_").head, when(col(c) =!= "" && col(c).isNotNull, lit("MASKED")).otherwise(col(c))) 
}
new_data.show

+---+-------+--------+------------------+------------------+------+--------------------+-----------------+------------+-----+-----+-----+------+--------------+
| id|     c4|   c1001|             c1002|             c1003| c1008|      c1008_unmasked|            c1009|       c1011|c1012|c1013|c1015| c1016|c1016_unmasked|
+---+-------+--------+------------------+------------------+------+--------------------+-----------------+------------+-----+-----+-----+------+--------------+
|  1|       |  SNACKS|BISCUITS - AMBIENT|BISCUITS - AMBIENT|MASKED|REFLETS DE FRANCE...|UNCOATED  BISCUIT|NO PROMOTION|     |     | 400G|      |              |
|  2|GROCERY|BISCUITS|   SWEET BISCUITS |BISCUITS - AMBIENT|      |                    |  AMBIENT BISCUIT|NO PROMOTION|     |     | 400G|MASKED|        CHOCOS|
+---+-------+--------+------------------+------------------+------+--------------------+-----------------+------------+-----+-----+-----+------+--------------+

Collected from the Internet

Please contact [email protected] to delete if infringement.