add new column in a dataframe depending on another dataframe's row values

Question

I need to add a new column to dataframe DF1 but the new column's value should be calculated using other columns' value present in that DF. Which of the other columns to be used will be given in another dataframe DF2.
eg. DF1

|protocolNo|serialNum|testMethod  |testProperty|
+----------+---------+------------+------------+       
|Product1  |  AB     |testMethod1 | TP1        |
|Product2  |  CD     |testMethod2 | TP2        |

DF2-

|action| type|               value       |        exploded |
+------------+---------------------------+-----------------+
|append|hash |        [protocolNo]       | protocolNo      |
|append|text |            _              |     _           | 
|append|hash | [serialNum,testProperty]  | serialNum       |
|append|hash | [serialNum,testProperty]  | testProperty    |

Now the value of exploded column in DF2 will be column names of DF1 if value of type column is hash.

Required - New column should be created in DF1. the value should be calculated like below-

hash[protocolNo]_hash[serialNumTestProperty] ~~~ here on place of column their corresponding row values should come.

eg. for Row1 of DF1, col value should be

hash[Product1]_hash[ABTP1]

this will result into something like this abc-df_egh-45e after hashing.

The above procedure should be followed for each and every row of DF1.

I've tried using map and withColumn function using UDF on DF1. But in UDF, outer dataframe value is not accessible(gives Null Pointer Exception], also I'm not able to give DataFrame as input to UDF.

Input DFs would be DF1 and DF2 as mentioned above.

Desired Output DF-

|protocolNo|serialNum|testMethod  |testProperty| newColumn      |
+----------+---------+------------+------------+----------------+       
|Product1  |  AB     |testMethod1 | TP1        | abc-df_egh-4je |
|Product2  |  CD     |testMethod2 | TP2        | dfg-df_ijk-r56 |

newColumn value is after hashing

Edit your question and add your code to see what´s happening — Emiliano Martinez
Can you please list the input DFs and the desired Output DF. That would be helpful. — Ganesh

Ganesh Ganesh · Accepted Answer · 2020-01-09T16:23:34

Instead of DF2, you can translate DF2 to case class like Specifications, e.g

case class Spec(columnName:String,inputColumns:Seq[String],action:String,action:String,type:String*){}

Create instances of above class

val specifications = Seq(
Spec("new_col_name",Seq("serialNum","testProperty"),"hash","append")
                     )

Then you can process the below columns

 val transformed =  specifications
        .foldLeft(dtFrm)((df: DataFrame, spec: Specification) => df.transform(transformColumn(columnSpec)))

def transformColumn(spec: Spec)(df: DataFrame): DataFrame = { 

 spec.type.foldLeft(df)((df: DataFrame, type : String) => {
           type match {
                  case "append" => {have a case match of the action and do that , then append with df.withColumn}

}
}

Syntax may not be correct

add new column in a dataframe depending on another dataframe's row values

2 Answers