title: Mapping CSV Header using a Dictionary
body: <p>I have a reference file that looks like this:</p>
  
  <pre class="lang-none prettyprint-override"><code>Experiment,Array,Drug
  8983,Genechip,Famotidine
  8878,Microarray,Dicyclomine
  8988,Genechip,Etidronate
  8981,Microarray,Flunarizine
  </code></pre>
  
  <p>I successfully created a dictionary mapping the <code>Experiment</code> numbers to the <code>Drug</code> name using the following:</p>
  
  <pre><code>reader = csv.reader(open('C:\Users\Troy\Documents\ExPSRef.txt'))
  #Configure dictionary
  result = {}
  for row in reader:
      key = row[0]
      result[key] = row[2]
  di = result
  </code></pre>
  
  <p>I want to map this dictionary to the header of another file which consists of the experiment number. It currently looks like this:</p>
  
  <pre class="lang-none prettyprint-override"><code>Gene,8988,8981,8878,8983
  Vcp,0.011,-0.018,-0.032,-0.034
  Ube2d2,0.034,0.225,-0.402,0.418
  Becn1,0.145,-0.108,-0.421,-0.048
  Lypla2,-0.146,-0.026,-0.101,-0.011
  </code></pre>
  
  <p>But it should look like this:</p>
  
  <pre class="lang-none prettyprint-override"><code>Gene,Etidronate,Flunarizine,Dicyclomine,Famotidine
  Vcp,0.011,-0.018,-0.032,-0.034
  Ube2d2,0.034,0.225,-0.402,0.418
  Becn1,0.145,-0.108,-0.421,-0.048
  Lypla2,-0.146,-0.026,-0.101,-0.011
  </code></pre>
  
  <p>I tried using:</p>
  
  <pre><code>import csv
  import pandas as pd
  
  reader = csv.reader(open('C:\Users\Troy\Documents\ExPSRef.txt'))
  result = {}
      for row in reader:
      key = row[0]
      result[key] = row[2]
      di = result
  df = pd.read_csv('C:\Users\Troy\Documents\ExPS2.txt')
  df['row[0]'].replace(di, inplace=True)
  </code></pre>
  
  <p>but it returned a <code>KeyError: 'row[0]'</code>.</p>
  
  <p>I tried the following as well, even transposing in order to merge:</p>
  
  <pre><code>import pandas as pd
  
  df1 = pd.read_csv('C:\Users\Troy\Documents\ExPS2.txt',).transpose()
  df2 = pd.read_csv('C:\Users\Troy\Documents\ExPSRef.txt', delimiter=',', engine='python')
  df3 = df1.merge(df2)
  df4 = df3.set_index('Drug').drop(['Experiment', 'Array'], axis=1)
  df4.index.name = 'Drug'
  print df4
  </code></pre>
  
  <p>and this time received <code>MergeError('No common columns to perform merge on')</code>.</p>
  
  <p>Is there a simpler way to map my dictionary to the header that would work?</p>