Create new data frame based on another data frame

Question

I have this data frame DF1

emp_no  no_children  status
  1      3            3
  2      2            2
  23     5            5

And another DF2

emp_no  no_children  status
      1      3            3
      2      2            2
      3      5            5

I am trying to create a new dataframe based on DF1 and DF2 e.g

emp_no no_children status
  23     5         5

the new DF should contain emp_no not in DF2

Answer 1

Using ne of the emp_no column and using the slice of df1 dataframe.

df1.loc[df1['emp_no'].ne(df2['emp_no']),:]

  emp_no    no_children status
2     23              5      5

Answer 2

You can use merge with how='left' and indicator=True and then filter rows present in df1.

In [277]: df1.merge(df2, indicator=True, how='left'
              ).query('_merge == "left_only"'
              ).drop('_merge', 1)
Out[277]:
   emp_no  no_children  status
2      23            5       5

---

Details

In [278]: df1.merge(df2, indicator=True, how='left')
Out[278]:
   emp_no  no_children  status     _merge
0       1            3       3       both
1       2            2       2       both
2      23            5       5  left_only

In [279]: df1.merge(df2, indicator=True, how='left').query('_merge == "left_only"')
Out[279]:
   emp_no  no_children  status     _merge
2      23            5       5  left_only

Answer 3

This will give df1 rows where df1['emp_no'] can't be found (~ is the negative) in df2['emp_no']:

df1.loc[~df1['emp_no'].isin(df2['emp_no'])]