df1 subtract df2 not working as expected in Pyspark when the records are not in order

user_7760910 0
Viewed 626

I have 2 files. In one file1 I have 998 records and in another file2, I have 1000 records. Now, when I try df1.subtract(df2) it gives 998 records and when I try df2.subtract(df1) it gives 1000 as the count. I also compared it manually in excel. All the records are same the except the additional records in file2. So, the expected output for df2.subtract(df1) should be 2. Where am I going wrong? Is there any way to match two data frames irrespective of its rows order?

df1:

enter image description here

df2:
enter image description here

First Approach:

df1 = spark.read.option("header","true").csv("Sales_records-1.csv")
df2 = spark.read.option("header","true").csv("Sales_records-2.csv")


#Finding the rows which are not present in second dataframe
df3 = df2.exceptAll(df1)
pyspark dataframe hadoop python
1 Answers

So, I found that my data had unique ID and I used the below code to find different records when the counts are different in two datasets. If the counts are different in two datasets, in-built methods like subtract or exceptAll won't work. I even tried various types of joins but didn't work out. You may have to do something similar like I did.

df2.createOrReplaceTempView("temp2")
df1.createOrReplaceTempView("temp1")

spark.sql("select * from temp2 where `Order ID` not in (select `Order ID` from temp1)").show()

So, this gives me the 2 records that I had been looking for from df2.subtract(df1)

Hope this approach will help someone someday!

user_77609100
Related