Use a column value as column name

user_4954623 0
Viewed 79

I need some help with a DataFrame.

I have a math form in a column and I want to apply the form to the DataFrame, saving the result in other column.

DF:

val metricsRows = List(
Row.fromSeq(Seq("Math1","double_1 + double_2",12.0,12.0,null)),
Row.fromSeq(Seq("Math2","double_2 - double_1",1000.0,10.0,null)),
Row.fromSeq(Seq("Math3","double_1 + double_1",12.02,19.02,null)),
)


val metricsSchema = StructType(Seq(
    StructField("Id", StringType, nullable = false),
    StructField("form", StringType, nullable = false),
    StructField("double_1", DoubleType, nullable = false),
    StructField("double_2", DoubleType, nullable = false),
    StructField("double_result", DoubleType, nullable = false))
    )

val metricsDF = sqlc.createDataFrame(sc.parallelize(metricsRows), metricsSchema)

The result that I like to have:

+-------+---------------------+----------+----------+--------------+
|Id     |form                 |double_1  |double_2  |double_result |
+-------+---------------------+----------+----------+--------------+
|Math1  |double_1 + double_2  |12.0      |12.0      |24.4          |
|Math2  |double_2 - double_1  |1000.0    |10.0      |-990.0        |
|Math3  |double_1 + double_1  |12.02     |19.02     |24.04         |
+-------+---------------------+----------+----------+--------------+

But I don't know how to get the form column values and interpret as column names.

I tried this:

val resultDF= metricsDF.withColumn("double_result",expr(col("form").toString()))

And the output is:

+-------+---------------------+----------+----------+---------------------+
|Id     |form                 |double_1  |double_2  |double_result        |
+-------+---------------------+----------+----------+---------------------+
|Math1  |double_1 + double_2  |12.0      |12.0      |double_1 + double_2  |
|Math2  |double_2 - double_1  |1000.0    |10.0      |double_2 - double_1  |
|Math3  |double_1 + double_1  |12.02     |19.02     |double_1 + double_1  |
+-------+---------------------+----------+----------+---------------------+

How can I do this? I tried other options with no results.

Thank you!

apache-spark scala
2 Answers

I donot think Spark or Scala provides inbuilt support for interpreting Math expressions using variable names

But you can work around this using java library ScriptEngineManager

//Sample data : 
val df = Seq(("Math1","double_1 + double_2",12.0,12.0),("Math2","double_2 - double_1",1000.0,10.0),("Math3","double_1 + double_1",12.02,19.02)
).toDF("Id","form","double_1","double_2")

import javax.script.SimpleBindings;
import javax.script.ScriptEngineManager
import java.util.Map
import java.util.HashMap


def calculateFunction = (mathExpression: String, double_1 : Double, double_2 : Double ) => {
    val vars: Map[String, Object] = new HashMap[String, Object]();
    vars.put("double_1",double_1.asInstanceOf[Object])
    vars.put("double_2",double_2.asInstanceOf[Object])
    val engine = new ScriptEngineManager().getEngineByExtension("js");
    val result = engine.eval(mathExpression, new SimpleBindings(vars));
    result.asInstanceOf[Double]
}

val calculateUDF = spark.udf.register("calculateFunction",calculateFunction)

val resultDF = df.withColumn("double_result",calculateUDF($"form",$"double_1",$"double_2"))

resultDF.show

+-----+-------------------+--------+--------+------+
|   Id|               form|double_1|double_2|  test|
+-----+-------------------+--------+--------+------+
|Math1|double_1 + double_2|    12.0|    12.0|  24.0|
|Math2|double_2 - double_1|  1000.0|    10.0|-990.0|
|Math3|double_1 + double_1|   12.02|   19.02| 24.04|
+-----+-------------------+--------+--------+------+

I would recommend to replace the Math expression parser to more native library of scala if you find one. But this works.

user_70945200

You can do it, but only if the number of different forms is not very big:

// collect all distinct forms
val forms = df.select($"form").distinct().as[String].collect()
// build up expression
val columnExpression = forms.foldLeft(when(lit(false),null)){case (a,v) => a.when($"form"===v,expr(v))}

val resultDF = df
  .withColumn("double_result",columnExpression)
user_11385230
Related