title: Add two new columns depending on another dafaframe in pandas
body: <p>I would need help in order to add new columns depending on conditions and another dataframe. I know the diffuculty is high but I tried to add the most exemples I could and to right a simple algorithm in order to do what I need.</p>
  <p>Here is the principal dataframe where we will add the columns :</p>
  <p><strong>df1</strong></p>
  <pre><code>Name        start1 end1 strand length 
  OK0100087.1 187    250  +      63
  OK0100087.1 830    750  -      80
  OK0100087.1 500    625  +      125
  OK0100087.1 375    275  -      100   
  OK0100087.1 150    20   -      120
  OK0100088.1 600    1000  +     400
  </code></pre>
  <p>from this df I would like to add 2 columns called <code>Newstart</code>and ``Newend```
  <strong>df2</strong></p>
  <pre><code>Name        start1 end1 strand length Newstart Newend 
  OK0100087.1 187    250  +      63
  OK0100087.1 830    750  -      80
  OK0100087.1 500    625  +      125
  OK0100087.1 375    275  -      100   
  OK0100087.1 150    20   -      120
  OK0100088.1 600    1000 +      400
  </code></pre>
  <p>and to fill the empty cells we will use anoter df such as :</p>
  <p><strong>df2</strong></p>
  <pre><code>Name        start_plus end_plus start_minus end_minus 
  OK0100087.1_0 0          375      1000        625
  OK0100087.1_1 376        750      624         250 
  OK0100087.1_3 751        1000     249         0 
  OK0100088.1   0          12000    0           12000
  OK0100089.1_0 0          566      3000        2433
  OK0100089.1_1 567        3000     2432        0
  </code></pre>
  <p>the idea is for each row to check the strand value and then do an algorithm such as :</p>
  <pre><code>if df1.strand.eq(&quot;-&quot;):
     df1.Newstart = df1.start1 - df2.end_minus
     df1.Newend = df1.Newstart - df1.length
  
  if df1.strand.eq(&quot;+&quot;):
     df1.Newstart = df1.start1 - df2.start_plus
     df1.Newend = df1.Newstart + df1.length
  </code></pre>
  <p>so let's take as exemple to better understand:</p>
  <p>first <code>Name</code>groupe <strong>OK0100087.1</strong> in <strong>df1</strong></p>
  <pre><code>Name        start1 end1 strand length Newstart Newend 
  OK0100087.1 187    250  +      63
  OK0100087.1 830    750  -      80
  OK0100087.1 500    625  +      125
  OK0100087.1 375    275  -      100   
  OK0100087.1 150    20   -      120
  </code></pre>
  <p>the first row is a <code>+</code> strand, then <code>df1.end1</code> = <strong>250</strong></p>
  <p>in df2, we only keep the <code>df2.Names.str.contains(df1.Name)</code> and <strong>250</strong> is within the interval of</p>
  <pre><code>Name          start_plus end_plus 
  OK0100087.1_0 0          375
  </code></pre>
  <p>(I took start_plus and end_plus as interval because it is a + strand).</p>
  <p>so</p>
  <pre><code>if df1.strand.eq(&quot;+&quot;):
     df1.Newstart = df1.start1 - df2.start_plus
     (df1.Newstart = 187 - 0 = 187
     df1.Newend = df1.Newstart + df1.length
     (df1.Newend =187+63 = 250)
  </code></pre>
  <p>which gives:</p>
  <pre><code>Name        start1 end1 strand length Newstart Newend 
  OK0100087.1 187    250  +      63     187      250
  OK0100087.1 830    750  -      80
  OK0100087.1 500    625  +      125
  OK0100087.1 375    275  -      100   
  OK0100087.1 150    20   -      120
  OK0100088.1 500    700  +      200
  </code></pre>
  <p>now let's do it for the second row:</p>
  <p><code>strand.eq(&quot;-&quot;)</code>, so <code>df1.start1</code> (<strong>830</strong>) is within the interval of :</p>
  <pre><code>Name          start_minus end_minus 
  OK0100087.1_0 1000        625
  </code></pre>
  <p>so</p>
  <pre><code>if df1.strand.eq(&quot;-&quot;):
     df1.Newstart = df1.start1 - df2.end_minus
     (df1.Newstart = 830 - 625 =205)
     df1.Newend = df1.Newstart - df1.length
     df1.Newend = 205 - 80 = 125) 
  </code></pre>
  <p>which gives:</p>
  <pre><code>Name        start1 end1 strand length Newstart Newend 
  OK0100087.1 187    250  +      63     187      250
  OK0100087.1 830    750  -      80     205      125
  OK0100087.1 500    625  +      125
  OK0100087.1 375    275  -      100   
  OK0100087.1 150    20   -      120
  OK0100088.1 500    700  +      200
  </code></pre>
  <p>and so on ...</p>
  <pre><code>Name        start1 end1 strand length Newstart Newend 
  OK0100087.1 187    250  +      63     187      250
  OK0100087.1 830    750  -      80     205      125
  OK0100087.1 500    625  +      125    125      250
  OK0100087.1 375    275  -      100    125      25
  OK0100087.1 150    20   -      120    150      20
  OK0100088.1 600    1000 +      200    33       433
  </code></pre>
  <p>If someone have some idea in order to do that it would be amazing.
  thank you very much</p>