'Find the Max value of an Array column and find associated value in another Array with in the dataframe

I have a csv file with below data.

Id Subject Marks
1 M,P,C 10,8,6
2 M,P,C 5,7,9
3 M,P,C 6,7,4

I Need to find out Max value in the Marks column for each Id and find the Associate subject from the subject column.

My desired result should be:

Id Subject Marks
1 M 10
2 C 9
3 P 7

I am reading the csv file and make the Subject & Marks as Array column using comma value split.


import org.apache.spark.SparkConf
import org.apache.spark.SparkContext
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions._
import org.apache.spark.sql._
import org.apache.spark.sql.types._
import org.apache.spark.sql.Column

 val spark = SparkSession.builder().getOrCreate()
    import spark.implicits._
    
    val df = spark.read.format("CSV")
                  .option("header", "true")
                  .option("delimiter", "|")
                  .option("inferSchema", "true")
                  .load("file:///p:/test/Data/test.csv")

   val df1 = df.select(col("id"),
                        split(col("subjects"),",").as("subjects"),
                        split(col("Makrs"),",").as("Makrs")
                      )

   df1.printSchema()

df1 schema is:

root
 |-- id: integer (nullable = true)
 |-- Sub: array (nullable = true)
 |    |-- element: string (containsNull = true)
 |-- Mark: array (nullable = true)
 |    |-- element: string (containsNull = true)

And df1 data is;

+---+---------+----------+
| id| subjects|     Makrs|
+---+---------+----------+
|  1|[M, P, C]|[10, 8, 6]|
|  2|[M, P, C]| [5, 7, 9]|
|  3|[M, P, C]| [6, 7, 4]|
+---+---------+----------+

I Am stuck how to find the Max value in Array column in a dataframe.

I tried array_max but getting an error that not found: value array_max

df1.withColumn("MaxMarks", array_max($"Makrs")).show()



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source