Join two dataframes and replace the original column values using Spark Scala

Question

I have two DFs

df1:

+---+-----+--------+
|key|price|    date|
+---+-----+--------+
|  1|  1.0|20210101|
|  2|  2.0|20210101|
|  3|  3.0|20210101|
+---+-----+--------+

df2:

+---+-----+
|key|price|
+---+-----+
|  1|  1.1|
|  2|  2.2|
|  3|  3.3|
+---+-----+

I'd like to replace price column values from df1 with price values from df2 where df1.key == df2.key

Expected output:

+---+-----+--------+
|key|price|    date|
+---+-----+--------+
|  1|  1.1|20210101|
|  2|  2.1|20210101|
|  3|  3.3|20210101|
+---+-----+--------+

I've found some solutions in python but I couldn't come up with a working solution in Scala.

blackbishop · Accepted Answer · 2022-01-05 22:21:54Z

3

Simply join + drop df1 column price:

val df = df1.join(df2, Seq("key")).drop(df1("price"))

df.show
//+---+-----+--------+
//|key|price|    date|
//+---+-----+--------+
//|  1|  1.1|20210101|
//|  2|  2.2|20210101|
//|  3|  3.3|20210101|
//+---+-----+--------+

Or if you have more entries in df1 and you want to keep their price when there is no match in df2 then use left join + coalesce expression:

val df = df1.join(df2, Seq("key"), "left").select(
  col("key"),
  col("date"),
  coalesce(df2("price"), df1("price")).as("price")
)

answered Jan 5, 2022 at 22:21

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

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

3 Comments

dmgcodevil Over a year ago

Hi, I've tried your solution, and the order of the columns changed in the output df, i.e.: key, date, price. Is it possible to preserve the order?

blackbishop Over a year ago

@dmgcodevil Hi! just use .select("key", "price", "date") if you want them in that order

Ged Over a year ago

This is an easy example. What about variable json input replacing that variable input in a df?

Collectives™ on Stack Overflow

Join two dataframes and replace the original column values using Spark Scala

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related