What Is The Analogue Of Except Clause In Sql In Pandas?

I have a sample pandas dataframe df: col1 col2 col3 col4 0 a 1.0 2.0 3 1 b NaN NaN 6 2 c NaN 8.0 9

Solution 1:

I think you need set_index of all string columns first:

df2 = df.set_index('col1').subtract(df1.set_index('col1'), axis='columns')
print (df2)
      col2  col3  col4
col1                  
a      0.00.00.0
b      NaNNaNNaNcNaNNaNNaN
d      NaNNaNNaN
e      0.00.00.0
f      0.00.00.0
g      0.00.00.0

Or:

df2 = df.set_index('col1').subtract(df1.set_index('col1'), axis='columns', fill_value=0)
print (df2)
      col2  col3  col4
col1                  
a      0.00.00.0
b      NaNNaN6.0cNaN8.09.0
d      NaN11.012.0
e      0.00.00.0
f      0.00.00.0
g      0.00.00.0

EDIT by edited question:

print (df.isin(df1))
    col1   col2   col3   col4
0TrueTrueTrueTrue1FalseFalseFalseFalse2FalseFalseFalseFalse3FalseFalseFalseFalse4TrueTrueTrueTrue5TrueTrueTrueTrue6TrueTrueTrueTrueprint (df.isin(df1).all(axis=1))
0True1False2False3False4True5True6True
dtype: boolprint (~df.isin(df1).all(axis=1))
0False1True2True3True4False5False6False
dtype: boolprint (df[~(df.isin(df1).all(axis=1))])
  col1  col2  col3  col4
1    b   NaN   NaN     62    c   NaN   8.093    d   NaN  11.012

Solution 2:

I think a Pandas equivalent for SQL EXCEPT (MINUS) would be the following technique:

In[16]: df1Out[16]:
   abc01a5   # duplicatesrowwithindex: 310x429Z9   # existsinDF2, soitshouldNOTappearintheresultset31a5   # duplicatesrowwithindex: 3In[17]: df2Out[17]:
    abc066a5.019Z9.020xNaNIn[18]: (pd.merge(df1, df2, on=df1.columns.tolist(), how='outer', indicator=True)
    ...:    .query("_merge == 'left_only'")
    ...:    .drop('_merge', 1)
    ...: )
    ...:
Out[18]:
   abc01a5.011a5.020x4.0

NOTE: this solution does NOT pay attention at indices