title: How to add values in a pandas data frame based on values of two columns of one of the data frame merged
body: <p>I need to calculate the sensitivity and specificity of a test based on another test that is my control. To do this I need to merge three data frames.</p>
  <p>The first concatenation is between a column that contains all cases with another column that contains the control test´s results. (I know how to do this but I show this previous step to let you understand what I need to do at the end).</p>
  <p>First data frame:</p>
  <pre><code>data = [['ch1.1234578C&gt;T'], ['ch2.123459G&gt;A'], ['ch3.234569A&gt;T'], ['chX.246890A&gt;G']]
   
  comparison = pd.DataFrame(data, columns = ['All_common_variants_ID'])
  
  comparison
  
  All_common_variants_ID
  1 ch1.1234578C&gt;t
  2 ch2.123459G&gt;A
  3 ch3.234569A&gt;T
  4 chX.246890A&gt;G
  
  </code></pre>
  <p>Second data frame:</p>
  <pre><code>data = [['ch1.1234578C&gt;T'], ['ch2.123459G&gt;A']]
   
  control = pd.DataFrame(data, columns = ['Sample_ID'])
  
  control
  
  Sample_ID
  1 ch1.1234578C&gt;T
  2 ch2.123459G&gt;A
  </code></pre>
  <p>I have merged these two data frames with this code:</p>
  <pre><code>comparative = comparison.merge(control[['Sample_ID']],left_on='All_common_variants_ID',right_on='Sample_ID',how='outer').fillna('Real negative')
  comparative = comparative.rename(columns={'Sample_ID': 'CONTROL'})
  comparative
  </code></pre>
  <pre><code>All_common_variants_ID  CONTROL
  
  1 ch1.1234578C&gt;T          ch1.1234578C&gt;T  
  2 ch2.123459G&gt;A           ch2.123459G&gt;A
  3 ch3.234569A&gt;T           Real negative
  4 chX.246890A&gt;G           Real negative
  </code></pre>
  <p>Now is where I have the problem.</p>
  <p>I need to concatenate a third data frame (test) under conditions with the first and the second column of the <code>comparative</code> data frame.</p>
  <p>The conditions are:</p>
  <ol>
  <li>If a value of the test data frame matches with a value in the second column add a &quot;True-positive&quot;.</li>
  <li>If. value doesn't match with any value from the second column add &quot; False-negative&quot;.</li>
  <li>If a value that matches with a value of the first column and the second column is &quot;Real negative&quot; add a  &quot;False-positive&quot;.</li>
  <li>For the rest of the cells, add &quot;True-negative&quot;.</li>
  </ol>
  <p>Following the sample provide, this would be the expected result.</p>
  <pre><code>All_common_variants_ID  CONTROL                 Test
  
  1 ch1.1234578C&gt;T          ch1.1234578C&gt;T       True-positive       # ch1.1234578C&gt;T match with the second column
  2 ch2.123459G&gt;A           ch2.123459G&gt;A        False-negative      # ch2.123459G&gt;A is not in my test column
  3 ch3.234569A&gt;T           Real negative        False-positive      # ch3.234569A&gt;T match with first column but second column is real negative
  4 chX.246890A&gt;G           Real negative        True-negative       # chX.246890A&gt;G is not in my test column and is not in the control column.
  
  </code></pre>
  <p>Some comments:</p>
  <ol>
  <li>There are no repetitive values in any column</li>
  <li>All_common_variants_ID contains all values between control and test columns.</li>
  </ol>