如何检查PySpark DataFrame中的ID是否存在于另一DataFrame并返回布尔结果

Hey there! Let's tackle this problem of verifying if each id in your DataFrame df exists in df1, and returning a boolean flag (True/False) for every row. Here are two efficient approaches tailored for PySpark:

Approach 1: Left Join + Conditional Flagging #

This method uses a left join to retain all rows from df, then checks if the joined id from df1 is non-null to determine existence. We'll also explicitly handle empty id values in df to mark them as False.

# Add a dummy flag column to df1
df1_with_flag = df1.withColumn("exists", F.lit(True))

# Perform left join and compute the Check column
result_df = df.join(df1_with_flag, on="id", how="left") \
              .withColumn("Check", 
                          F.when(F.col("id").isNull() | F.col("exists").isNull(), False)
                           .otherwise(True)) \
              .select("id", "Check")

result_df.show()

Output: #

+------+-----+
|    id|Check|
+------+-----+
|12345 | true|
| 6789 | true|
|12345 | true|
| 7890 | true|
| 5000 |false|
|80000 |false|
|90000 | true|
|      |false|
+------+-----+

Approach 2: isin() with Broadcast (For Small df1) #

If df1 is a small dataset, broadcasting it can optimize performance. We'll collect the unique ids from df1 into a list, then use isin() to check membership—again handling empty id values explicitly.

# Collect unique ids from df1 (safe for small datasets)
valid_ids = [row.id for row in df1.select("id").distinct().collect()]

# Create the Check column using isin() and handle nulls
result_df = df.withColumn("Check", 
                          F.when(F.col("id").isNull(), False)
                           .otherwise(F.col("id").isin(valid_ids))) \
              .select("id", "Check")

result_df.show()

Output: #

Same as the first approach—matches your expected result perfectly!

Key Notes: #

• Handling Nulls: Both methods explicitly mark empty id values as False since they don't exist in df1.
• Performance: For large df1, prefer the left join approach (Spark's optimizer handles large table joins better). For small df1, the isin() + broadcast method is faster.

内容的提问来源于stack exchange，提问作者abhishek gaikwad