I need to compare the dataframe value with the list value and get the list of value which is not present in dataframe.
list =['682.9', '682.12', '682.11', '682.13', '682.14', '682.15']
Dataframe:
+-----------+
|sheetnumber|
+-----------+
| 682.11|
| 682.12|
| 682.13|
| 682.14|
| 682.15|
| 783.4|
+-----------+
Expected output:
['682.9']
You can convert the list into pyspark dataframe and do a left anti join with the other dataframe :
my_list = ['682.9', '682.12', '682.11', '682.13', '682.14', '682.15']
mylist_df = spark.createDataFrame([(n,) for n in my_list], ["sheetnumber"])
result = mylist_df.join(df, ["sheetnumber"], "left_anti")
output = [row.sheetnumber for row in result.collect()]
print(output)
#['682.9']
You can use exceptAll to compare the list (converted to a dataframe) with your dataframe:
mylist = ['682.9', '682.12', '682.11', '682.13', '682.14', '682.15']
diff = spark.createDataFrame([[i] for i in mylist]).exceptAll(df)
diff.show()
+-----+
| _1|
+-----+
|682.9|
+-----+
And to get the results back as a list, you can do
result = [r[0] for r in diff.collect()]
# ['682.9']
You can use the filter/where statement and check if it is in the list
my_list = ['682.9', '682.12', '682.11', '682.13', '682.14', '682.15']
df2=df.where(~df.sheetnumber.isin(my_list))
df2.show()
+-----------+
|sheetnumber|
+-----------+
| 783.4|
+-----------+
df3=df.filter(~df.sheetnumber.isin(my_list))
df3.show()
+-----------+
|sheetnumber|
+-----------+
| 783.4|
+-----------+
the ~ stands for not