Compare the values in multiple columns in one Dataframe with multiple rows in one single column for target dataframe for the same record in Scala?

Question

My source and target is like this

Source DataFrame

key     col1    col2    col3    col4    col5    col6
1       AA      BB      CC      null    null    null
2       SS      null    null    null    null    null
3       AA      CC      RR      SS      DD      null

Target DataFrame

Key     Column
1       AA
1       BB
1       CC
2       SS
3       AA
....

I want to compare these 2 values to check if they are populating properly and there is no duplication. I have tried several ways but all are very slow,

One way I tried is:

Read column "key" in a list,
Then iterate over the source and get all the col values in array for that key,
Remove nulls from the array then sort the array.
From target similar operation to store all the values in array for the key and then sort the array and compare the array with:

 sourceArray.sameElements(targetArray)

Is there any easy solution to this. I think I am over-complicating this simple problem.

blackbishop · Accepted Answer · 2022-01-21 20:32:22Z

2

You can create array from all columns except key, filter the null values in the array then explode it:

val df1 = df.withColumn(
  "Column", 
  array(df.columns.filter(_!="key").map(col(_)):_*)
).select(
  col("key"),
  explode(expr("filter(Column, x -> x is not null)")).as("Column")
)

df1.show
//+---+------+
//|key|Column|
//+---+------+
//|  1|    AA|
//|  1|    BB|
//|  1|    CC|
//|  2|    SS|
//|  3|    AA|
//|  3|    CC|
//|  3|    RR|
//|  3|    SS|
//|  3|    DD|
//+---+------+

Or simply using stack expression to unpivot the columns then filter out nulls:

val df1 = df.selectExpr(
  "key",
  "stack(6, col1, col2, col3, col4, col5, col6) as Column"
).filter("Column is not null")

edited Jan 21, 2022 at 20:32

answered Jan 21, 2022 at 20:20

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compare the values in multiple columns in one Dataframe with multiple rows in one single column for target dataframe for the same record in Scala?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related