How do I modify dataframe rows based on another dataframe in Spark?

Question

I have a dataframe, df2 such as:

ID | data
--------
1 | New
3 | New 
5 | New

and a main dataframe, df1:

ID | data | more
----------------
1 | OLD | a
2 | OLD | b
3 | OLD | c
4 | OLD | d
5 | OLD | e

I want to achieve something of the sort:

ID | data | more
----------------
1 | NEW | a
2 | OLD | b
3 | NEW | c
4 | OLD | d
5 | NEW | e

I want to update df1 based on df2, keeping the original values of df1 when they dont exist in df2.

Is there a fast way to do this than using isin? Isin is very slow when df1 and df2 are both very large.

pasha701 · Accepted Answer · 2019-06-29 18:42:54Z

3

With left join and "coalesce":

val df1 = Seq(
  (1, "OLD", "a"),
  (2, "OLD", "b"),
  (3, "OLD", "c"),
  (4, "OLD", "d"),
  (5, "OLD", "e")).toDF("ID", "data", "more")

val df2 = Seq(
  (1, "New"),
  (3, "New"),
  (5, "New")).toDF("ID", "data")

// action
val result = df1.alias("df1")
  .join(
    df2.alias("df2"),$"df2.ID" === $"df1.ID", "left")
  .select($"df1.ID",
    coalesce($"df2.data", $"df1.data").alias("data"),
    $"more")

Output:

+---+----+----+
|ID |data|more|
+---+----+----+
|1  |New |a   |
|2  |OLD |b   |
|3  |New |c   |
|4  |OLD |d   |
|5  |New |e   |
+---+----+----+

answered Jun 29, 2019 at 18:42

pasha701

7,2171 gold badge17 silver badges22 bronze badges

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

1 Comment

Ahmed Over a year ago

Is it possible to do this for dynamic columns and not listing all columns in the select statement?

Collectives™ on Stack Overflow

How do I modify dataframe rows based on another dataframe in Spark?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related