'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 |
---|