How to use SQL minus query equivalent between two dataframes properly

user_8391643 0
Viewed 142

I have two dataframes each having 1000 rows. The dataframes are same, however, row by row is not same. The following examples can be assumed as truncated version of the dataframes.

df1:

col1 col2 col3

1      2    3
2      3    4
5      6    6
8      9    9

df2:

col1 col2 col3

5      6    6
8      9    9
1      2    3
2      3    4

The dataframes don't have indices and I expect null returned when I implement sql minus query on these. I used the following query, but did not obtain the result as expected. Is there any way to achieve my desired result ?

df3 = df1.merge(df2.drop_duplicates(),how='right', indicator=True)
print(df3)

For instance, if I consider df1 as table1 and df2 as table2, and if I ran the following query in SQL server, I would get null returned (empty table).

SELECT * FROM table1
EXCEPT
SELECT * FROM table2
pandas dataframe python-3.x sql
1 Answers

Yes, you can use the indicator like this:

df1.merge(df2, how='left', indicator='ind').query('ind=="left_only"')

Where df1 is:

   col1  col2  col3
0   1.0   2.0   3.0
1   2.0   3.0   4.0
2   5.0   6.0   6.0
3   8.0   9.0   9.0
4  10.0  10.0  10.0

and df2 is:

   col1  col2  col3
0     5     6     6
1     8     9     9
2     1     2     3
3     2     3     4

Output:

   col1  col2  col3        ind
4  10.0  10.0  10.0  left_only
user_63615310
Related