Set column differences between data frames

Question

Note: This question is inspired by the ideas discussed in this other post: Pandas DataFrame Algebra

Let's say I have two frames Aand B, and for some column col_nametheir values:

A[col_name]   |  B[col_name]  
--------------| ------------
1             |  3
2             |  4
3             |  5
4             |  6

I want to calculate the difference between Aand Bbased on col_name. The result of this operation should be:

Lines Awhere A[col_name]do not match elements in B[col_name].

Below is the result for the above example (other columns are also shown A):

A[col_name] | A[other_column_1] | A[other_column_2]  
------------+-------------------|------------------ 
1           |    'foo'          |  'xyz'            ....
2           |    'bar'          |  'abc'

, A[col_name] B[col_name] np.NaN. undefined, , .. .

Pandas? ( )

Answer 1

- Series isin:

In [11]: df1 = pd.DataFrame([[1, 'foo'], [2, 'bar'], [3, 'meh'], [4, 'baz']], columns = ['A', 'B'])

In [12]: df2 = pd.DataFrame([[3, 'a'], [4, 'b']], columns = ['A', 'C'])

, df1['A'] df2['A']:

In [13]: df1['A'].isin(df2['A'])
Out[13]:
0    False
1    False
2     True
3     True
Name: A, dtype: bool

In [14]: df1[~df1['A'].isin(df2['A'])]  # not in df2['A']
Out[14]:
   A    B
0  1  foo
1  2  bar

, , NaN :

In [21]: df1 = pd.DataFrame([[1, 'foo'], [np.nan, 'bar'], [3, 'meh'], [np.nan, 'baz']], columns = ['A', 'B'])

In [22]: df2 = pd.DataFrame([[3], [np.nan]], columns = ['A'])

In [23]: df1[~df1['A'].isin(df2['A'])]
Out[23]:
    A     B
0 1.0   foo
1 NaN   bar
3 NaN   baz

. ( , ).

- :

In [31]: df1 = pd.DataFrame([[1, 'foo'], [np.nan, 'bar'], [4, 'meh'], [np.nan, 'eurgh']], columns = ['A', 'B'])

In [32]: df2 = pd.DataFrame([[np.nan, 'bar'], [4, 'meh']], columns = ['A', 'B'])

In [33]: cols = ['A', 'B']

In [34]: df2['dummy'] = df2[cols].isnull().any(1)  # rows with NaNs in cols will be True

In [35]: merged = df1.merge(df2[cols + ['dummy']], how='left')

In [36]: merged
Out[36]:
    A      B  dummy
0   1    foo    NaN
1 NaN    bar   True
2   4    meh  False
3 NaN  eurgh    NaN

df2, True NaN . , , False:

In [37]: merged.loc[merged.dummy != False, df1.columns]
Out[37]:
    A      B
0   1    foo
1 NaN    bar
3 NaN  eurgh

.

Answer 2

, , NaN (0), :

def left_difference(L, R, L_on, R_on, NULL_VALUE):
  L[L_on] = L[L_on].fillna(NULL_VALUE)
  L.set_index(L_on, inplace=True)

  R[R_on] = R[R_on].fillna(NULL_VALUE)
  R.set_index(R_on, inplace=True)

  # MultiIndex difference:
  diff = L.ix[L.index - R.index]
  diff = diff.reset_index()

  return diff

, NULL_VALUE , L_on R_on.