title: Python pandas: replace values multiple columns matching multiple columns from another dataframe
body: <p>I searched a lot for an answer, the closest question was <a href="https://stackoverflow.com/questions/19017350/compare-2-columns-of-2-different-pandas-dataframes-if-the-same-insert-1-into-th">Compare 2 columns of 2 different pandas dataframes, if the same insert 1 into the other in Python</a>, but the answer to this person's particular problem was a simple merge, which doesn't answer the question in a general way.</p>
  
  <p>I have two large dataframes, df1 (typically about 10 million rows), and df2 (about 130 million rows). I need to update values in three columns of df1 with values from three columns of df2, based on two df1 columns matching two df2 columns. It is imperative that the order of df1 remains unchanged, and that only rows with matching values get updated.</p>
  
  <p>This is how the dataframes look like:</p>
  
  <pre><code>df1
  
  chr    snp  x    pos a1 a2
  1  1-10020  0  10020  G  A    
  1  1-10056  0  10056  C  G    
  1  1-10108  0  10108  C  G
  1  1-10109  0  10109  C  G    
  1  1-10139  0  10139  C  T
  </code></pre>
  
  <p>Note that it's not always the case that the values of "snp" is chr-pos, it can take many other values with no link to any of the columns (like rs1234, indel-6032 etc)</p>
  
  <pre><code>df2
  
  ID           CHR   STOP  OCHR  OSTOP
  rs376643643    1  10040     1  10020
  rs373328635    1  10066     1  10056    
  rs62651026     1  10208     1  10108    
  rs376007522    1  10209     1  10109   
  rs368469931    3  30247     1  10139
  </code></pre>
  
  <p>I need to update ['snp', 'chr', 'pos'] in df1 with df2[['ID', 'OCHR', 'OSTOP']] only when df1[['chr', 'pos']] matches df2[['OCHR', 'OSTOP']]</p>
  
  <p>so in this case, after update, df1 would look like:</p>
  
  <pre><code>chr       snp  x     pos a1 a2    
  1  rs376643643  0  10040  G  A    
  1  rs373328635  0  10066  C  G    
  1  rs62651026   0  10208  C  G    
  1  rs376007522  0  10209  C  G    
  3  rs368469931  0  30247  C  T
  </code></pre>
  
  <p>I have used merge as a workaround:</p>
  
  <pre><code>df1 = pd.merge(df1, df2, how='left', left_on=["chr", "pos"], right_on=["OCHR", "OSTOP"],
                                       left_index=False, right_index=False, sort=False)
  </code></pre>
  
  <p>and then</p>
  
  <pre><code>df1.loc[~df1.OCHR.isnull(), ["snp", "chr", "pos"]] = df1.loc[~df1.OCHR.isnull(), ["ID", "CHR", "STOP"]].values
  </code></pre>
  
  <p>and then remove the extra columns.</p>
  
  <p>Yes, it works, but what would be a way to do that directly by comparing the values from both dataframes, I just don't know how to formulate it, and I couldn't find an answer anywhere; I guess it could be useful to get a general answer on this.</p>
  
  <p>I tried that but it doesn't work:</p>
  
  <pre><code>df1.loc[(df1.chr==df2.OCHR) &amp; (df1.pos==df2.OSTOP),["snp", "chr", "pos"]] = df2.loc[df2[['OCHR', 'OSTOP']] == df1.loc[(df1.chr==df2.OCHR) &amp; (df1.pos==df2.OSTOP),["chr", "pos"]],['ID', ''CHR', 'STOP']].values
  </code></pre>
  
  <p>Thanks,</p>
  
  <p>Stephane</p>