title: Python pandas compare 2 dataframe output new/delete/change values in new column
body: <p>Lets assume I have below 2 dataframes</p>
  
  <pre><code>df1 =
  
  userid  | first_name |  last_name   | title
  -----------------------------------------------
  u1  |   user1   |   test    |   manager
  
  u2  |   user2   |   test    |   creator
  
  u4  |   user4   |   test    |   Analysts
  
  
  df2 =
  
  userid |    first_name  |last_name  |title
  ----------------------------------------------
  u1  |   user1   |   test    |   designer
  u2  |   user2   |   test    |   creator
  u3  |   user2   |   test    |   creator
  </code></pre>
  
  <p>I used panda merge command</p>
  
  <pre><code>pd.merge(df1,df2,how='outer',on=['userid','first_name','last_name','title'],indicator=True)
  </code></pre>
  
  <p>what i am expecting as a result when comparing df1 with df2</p>
  
  <pre><code>userid  |first_name |last_name| title   |    status|
  --------------------------------------------------
  u1  |   user1   |   test    |       manager |   changed
  
  u2  |   user2   |   test    |   creator |   Nochange
  
  u3  |   user2   |   test    |   creator   |  Deleted
  
  u4  |  user4    |   test    |   Analysts  | New
  </code></pre>
  
  <p>But what i get when i use panda merge</p>
  
  <pre><code>userid  |first_name|    last_name|  title   |   _merge
  
  u1  |   user1       |test       |manager    |   left_only
  
  u2  |   user2       |test   |   creator     |both
  
  u3  |   user2       |test   |   creator   |  left_only
  
  u1  |   user1       |test   |   designer |   rigth_only
  </code></pre>
  
  <p>So in the above output i can see the _merge field gives me some data to filter, but I need to find a way identify solution for these 3 below items</p>
  
  <ol>
  <li>u1 userid is showing up twice  ( as per the record the 'title' was changed in df1 so i need to show it as 'changed' in the new or _merge column)</li>
  <li>u3      user2       test        creator -  I need to show up as deleted row </li>
  <li>u4      user4       test        Analysts  - Need to show up as new row</li>
  </ol>
  
  <p>Any idea how i can achieve the desired output?. Any help is really appreciated</p>