how to check a pyspark dataframe value against another dataframe

user_9757923 0
Viewed 771

I have below pyspark dataframe df and I want to check the id are present in another dataframe df1 and return true and false for every row.

from pyspark.sql import SparkSession
from pyspark.sql.functions import *
import pyspark.sql.functions as F

# Create SparkSession

data=[["12345","2020-02-01"],["6789","2019-03-01"],["12345","2021-03-01"],["7890",""],["5000","2021-21-01"],["80000","1900-01-01"],["90000","2000-01-01"],["","2000-01-01"]]
df=spark.createDataFrame(data,["id","Date"])
df.show()

data=[["12345"],["6789"],["7890"],["90000"]]
df1=spark.createDataFrame(data,["id"])
df1.show()

df data frame :

id Date
12345 2020-02-01
6789 2019-03-01
12345 2021-03-01
7890
5000 2021-21-01
80000 1900-01-01
90000 2000-01-01
2000-01-01

df1 dataframe :

id
12345
6789
7890
90000

I am looking to get below output based on the comparsion from df with df1.

id Check
12345 True
6789 True
12345 True
7890 True
5000 False
80000 False
90000 True
False
pyspark dataframe
1 Answers

Just as mentioned in the comments, use a left join. First we need to add an additional column to df1 which helps us identify the ids that are in df1. Then we coalesce the column to get True and False values:

from pyspark.sql import functions as f
df.join(df1.withColumn('Check', f.lit(True)), on="id", how='left')\
    .withColumn("Check", f.coalesce("Check", f.lit(False))).show()

Results in:

+-----+----------+-----+
|   id|      Date|Check|
+-----+----------+-----+
| 5000|2021-21-01|false|
|90000|2000-01-01| true|
| 6789|2019-03-01| true|
| 7890|          | true|
|80000|1900-01-01|false|
|12345|2020-02-01| true|
|12345|2021-03-01| true|
|     |2000-01-01|false|
+-----+----------+-----+

The trick is to add the check column to df1 before the join. Executing

df.join(df1.withColumn('Check', f.lit(True)), on="id", how='left')

results in :

+-----+----------+-----+
|   id|      Date|Check|
+-----+----------+-----+
| 5000|2021-21-01| null|
|90000|2000-01-01| true|
| 6789|2019-03-01| true|
| 7890|          | true|
|80000|1900-01-01| null|
|12345|2020-02-01| true|
|12345|2021-03-01| true|
|     |2000-01-01| null|
+-----+----------+-----+

Now we need to coalesce the Check column to end up with the desired True/False values.

user_95343900
Related