Filter pyspark dataframe based on another dataframe

Question

I need help because I wanted to filter some data from a dataframe as a criterion for another dataframe but I didn't want to use SQL commands.

df1

id ; create ; change ; name
1  ;2020-12-01;;Paul
2  ;2020-12-02;;Mary
3  ;2020-12-03;;David
4  ;2020-12-04;;Marley


df2
id ; create ; change ; name
1  ;2020-12-01;2020-12-30;Paul
2  ;2020-12-02;;Mary
3  ;2020-12-03;;David
4  ;2020-12-04;2020-12-30;Marley
5  ;2020-12-30;;Ted

df3

I wanted to create the df3 dataframe with the following rule where the id (df2) containing change pre-filled with the date 2020-12-30 and exists in df1 not to be inserted in df3

id ; create ; change ; name
2  ;2020-12-02;;Mary
3  ;2020-12-03;;David

mck · Accepted Answer · 2020-12-30 07:12:22Z

2

You can first do a semi-join of df2 with df1, and then filter the change column.

df3 = df2.join(df1, ['id', 'create', 'name'], 'semi') \
         .filter("change is null or change != '2020-12-30'") \
         .select('id', 'create', 'change', 'name')

df3.show()
+---+----------+------+-----+
| id|    create|change| name|
+---+----------+------+-----+
|  2|2020-12-02|  null| Mary|
|  3|2020-12-03|  null|David|
+---+----------+------+-----+

answered Dec 30, 2020 at 7:12

mck

42.7k13 gold badges44 silver badges62 bronze badges

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

Collectives™ on Stack Overflow

Filter pyspark dataframe based on another dataframe

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