title: Speed up vlookup like operation using pandas in python
body: <p>I have written some code to essentially do a excel style vlookup on two pandas dataframes and want to speed it up. </p>
  
  <p>The structure of the data frames is as follows:
  dbase1_df.columns:<br>
      'VALUE', 'COUNT', 'GRID', 'SGO10GEO'  </p>
  
  <p>merged_df.columns:<br>
      'GRID', 'ST0, 'ST1', 'ST2', 'ST3', 'ST4', 'ST5', 'ST6', 'ST7', 'ST8', 'ST9', 'ST10'  </p>
  
  <p>sgo_df.columns:<br>
      'mkey', 'type'  </p>
  
  <p>To combine them, I do the following:<br>
  1. For each row in dbase1_df, find the row where its 'SGO10GEO' value matches the 'mkey' value of sgo_df. Obtain the 'type' from that row in sgo_df.  </p>
  
  <ol>
  <li><p>'type' contains an integer ranging from 0 to 10. Create a column name by appending 'ST' to type.  </p></li>
  <li><p>Find the value in merged_df, where its 'GRID' value matches the 'GRID' value in dbase1_df and the column name is the one we obtained in step 2. Output this value into a csv file.  </p></li>
  </ol>
  
  <p>// Read in dbase1 dbf into data frame  </p>
  
  <p>dbase1_df  = pandas.DataFrame.from_csv(dbase1_file,index_col=False)<br>
  merged_df = pandas.DataFrame.from_csv('merged.csv',index_col=False)  </p>
  
  <p>lup_out.writerow(["VALUE","TYPE",EXTRACT_VAR.upper()])<br>
  // For each unique value in dbase1 data frame:<br>
  for index, row in dbase1_df.iterrows():</p>
  
  <pre><code># 1. Find the soil type corresponding to the mukey
  tmp  = sgo_df.type.values[sgo_df['mkey'] == int(row['SGO10GEO'])]
  if tmp.size &gt; 0:        
      s_type = 'ST'+tmp[0]
      val       = int(row['VALUE'])            
  
      # 2. Obtain hmu value
      tmp_val  = merged_df[s_type].values[merged_df['GRID'] == int(row['GRID'])] 
      if tmp_val.size &gt; 0:
          hmu_val = tmp_val[0]             
          # 4. Output into data frame: VALUE, hmu value
          lup_out.writerow([val,s_type,hmu_val])
      else:
          err_out.writerow([merged_df['GRID'], type, row['GRID']])
  </code></pre>
  
  <p>Is there anything here that might be a speed bottleneck? Currently it takes me around 20 minutes for around ~500,000 rows in dbase1_df; ~1,000 rows in merged_df and ~500,000 rows in sgo_df.</p>
  
  <p>thanks!</p>