I have org.apache.spark.sql.DataFrame = [_1: string, _2: string] with column containing the value in below format.

Input

I want to find the average from column _2 and also count the number of elements in it as shown below. What will be the most efficient way since this logic will be applied to millions of rows

Output

CodePudding user response：

Split the string column then use array functions size aggregate:

val df2 = df
  .withColumn("arr_2", split($"_2", ","))
  .withcolumn("Count_2", size($"arr_2"))
  .withcolumn(
    "Average_2",
    aggregate($"arr_2", lit(0), (s, x) => s   x, s => s / $"Count_2")
  ).drop("arr_2")