CODEWITHSUNDEEP
scalahadoopapache-spark
Santhosh Hirekerur
Santhosh Hirekerur

Reputation: 876

How to Iterate Rows and compare one row column value to next row column value in Scala?

I am new to scala. I need some immediate help.

I have M*N spark sql dataframe something like below. I need to compare each row column values with next row column value.

Some thing like A1 to A2,A1 to A3, so on up to N . B1 to B2 B1 to B3 .

Could you someone please guide me how can compare row wise in spark sql?

ID  COLUMN1 Column2
1   A1  B1
2   A2  B2
3   A3  B3

Thank you in Advance Santhosh

Upvotes: 1

Views: 2922

Answers (1)

Tzach Zohar
Tzach Zohar

Reputation: 37842

If I understand the question correctly - you want to compare (using some function) each value to the value of the same column in the previous record. You can do that using the lag Window Function:

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.Column
import org.apache.spark.sql.functions._
import spark.implicits._

// some data...
val df = Seq(
  (1, "A1", "B1"),
  (2, "A2", "B2"),
  (3, "A3", "B3")
).toDF("ID","COL1", "COL2")

// some made-up comparisons - fill in whatever you want...
def compareCol1(curr: Column, prev: Column): Column = curr > prev
def compareCol2(curr: Column, prev: Column): Column = concat(curr, prev)

// creating window - ordered by ID
val window = Window.orderBy("ID")

// using the window with lag function to compare to previous value in each column
df.withColumn("COL1-comparison", compareCol1($"COL1", lag("COL1", 1).over(window)))
  .withColumn("COL2-comparison", compareCol2($"COL2", lag("COL2", 1).over(window)))
  .show()

// +---+----+----+---------------+---------------+
// | ID|COL1|COL2|COL1-comparison|COL2-comparison|
// +---+----+----+---------------+---------------+
// |  1|  A1|  B1|           null|           null|
// |  2|  A2|  B2|           true|           B2B1|
// |  3|  A3|  B3|           true|           B3B2|
// +---+----+----+---------------+---------------+

Upvotes: 1

Related Questions