title: How to map values (from a CSV file) with Pandas DataFrame efficiently?
body: <p>I have a CSV such as this:</p>
  
  <p><a href="https://i.stack.imgur.com/RBefh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RBefh.png" alt="enter image description here"></a></p>
  
  <p>Where the first column can have gaps, but the second and the third do not have gaps in the middle of the values.</p>
  
  <p>The values of the column <code>TO_REPLACE</code> should be replaced with the values in the column <code>VALUE_TO_COPY</code> provide that the content of their cells coincide with the cell value of the column <code>TO_SEARCH</code>. So the result should be this:</p>
  
  <p><a href="https://i.stack.imgur.com/hN3J3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hN3J3.png" alt="enter image description here"></a></p>
  
  <p>I have written an script:</p>
  
  <pre><code>import pandas as pd
  import numpy as np
  
  df = pd.read_csv(
      filepath_or_buffer='mapping_test.csv',
      delimiter=',',
      dtype=str
  )
  
  to_replace = df['TO_REPLACE'].copy()
  result = df['TO_REPLACE'].copy()
  
  df = df.set_index('TO_SEARCH')
  df.dropna(
      how='all',
      inplace=True
  )
  
  del df['TO_REPLACE']
  
  for key, value in to_replace.iteritems():
      try:
          result[key] = df.loc[value, 'VALUE_TO_COPY']
      except:
          print('ERROR, not found KEY: {}'.format(key))
  
  result_df = pd.DataFrame(
      data={
          'TO_REPLACE': result,
          'VALUE_TO_COPY': list(df['VALUE_TO_COPY']) + [np.nan] * (len(result) - df['VALUE_TO_COPY'].size),
          'TO_SEARCH': list(df.index) + [np.nan] * (len(result) - df['VALUE_TO_COPY'].size),
      }, 
      columns=['TO_REPLACE','VALUE_TO_COPY','TO_SEARCH']  # to preserve the column order
  )
  
  result_df.to_csv(
      path_or_buf='mapping_result.csv',
      index=False
  )
  </code></pre>
  
  <p>What I do in my code:</p>
  
  <ol>
  <li><p>I read the data from the CSV into a DataFrame</p></li>
  <li><p>I split the DataFrame in two parts. On one hand I store <code>TO_REPLACE</code> as a Series and on the other hand a DataFrame with the columns <code>VALUE_TO_COPY</code> and <code>TO_SEARCH</code>. I use <code>TO_SEARCH</code> as the index of this DataFrame.</p></li>
  <li><p>I iterate over the column <code>TO_REPLACE</code> in order to find the values in the column <code>TO_SEARCH</code>. If the values do not coincide I keep the old value.</p></li>
  <li><p>I build a DataFrame again with the replaced values and I store it into a CSV file.</p></li>
  </ol>
  
  <p>But it is not very efficient. I need to map many thousands of values very often, that's why I need a more efficient code. Any idea to enhance my code?</p>
  
  <p>Maybe I could use the methods <code>map</code> (for Series), <code>apply</code> or <code>applymap</code> (for DF). At least I have discarded <code>apply</code> because it operates on a whole row at a time and <code>applymap</code> operates on the whole DataFrame. Maybe the most useful is <code>map</code>, but I think it iterates over all the values as I did manually. Another possible option I have considered is the method <code>replace</code>, but I have read that <code>map</code> is faster.</p>