how to update second dataframe's exists value if row exists in first dataframe

Question

i have two data frames i want to check if df1 contains any row in df2 where key is a and b, if equal then change exists to true in df2 and add the new rows from df1 with exists False

df1

a | b | c | d
1 | 1 | 3 | 4
2 | 2 | 4 | 1
3 | 3 | 5 | 3

df2

a | b | c | d
1 | 1 | 4 | 5
4 | 4 | 3 | 2

this should look like

df3

a | b | c | d | exists
1 | 1 | 4 | 5 | True
4 | 4 | 3 | 2 | False
1 | 1 | 3 | 4 | False
2 | 2 | 4 | 1 | False
3 | 3 | 5 | 3 | False

so far i have this

val newdf = df1.join(df2, df1("a")===df2("a") && df1("b") === df2("b"), "left")
   .select(df2("a"), df2("b"),df2("c"),df2("d"),when(df2("a").isNull, false).otherwise(true).alias("exists"))

which returns

a | b | c | d | exists
1 | 1 | 4 | 5 | True
rest of the rows are null

1 | 1 | 3 | 4 | False did this row going to be in df3 too? because there is matching row in df1.. — notNull
– notNull, Commented Aug 3, 2020 at 20:06
yes all rows from both dataframe will be in df3. the one with matching from df2 will have exists true in df3 — j.doe
– j.doe, Commented Aug 3, 2020 at 20:10

notNull · Accepted Answer · 2020-08-03 20:19:12Z

1

Try with left_semi, left_anti joins then unionAll the datasets.

Example:

df2.join(df1,Seq("a","b"),"left_semi").withColumn("exists",lit("True")).
unionAll(df2.join(df1,Seq("a","b"),"left_anti").withColumn("exists",lit("False"))).
unionAll(df1.withColumn("exists",lit("False"))).show()
//+---+---+---+---+------+
//|  a|  b|  c|  d|exists|
//+---+---+---+---+------+
//|  1|  1|  4|  5|  True|
//|  4|  4|  3|  2| False|
//|  1|  1|  3|  4| False|
//|  2|  2|  4|  1| False|
//|  3|  3|  5|  3| False|
//+---+---+---+---+------+

edited Aug 3, 2020 at 20:19

answered Aug 3, 2020 at 20:11

notNull

31.8k4 gold badges41 silver badges58 bronze badges

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

1 Comment

j.doe Over a year ago

that works, thanks so one other thing, df2 serves as a truth table, so the next time it will be read it will have the exists column. if i add that i get this error Union can only be performed on tables with the same number of columns, but the first table has 8 columns and the 2th table has 7 columns;;

Collectives™ on Stack Overflow

how to update second dataframe's exists value if row exists in first dataframe

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