title: merging in pandas vs merging in R
body: <p>I'm afraid I do not quite understand the merging capabilities of pandas, although I much prefer python over R for now.</p>
  
  <p>In R, I have always been able to merge dataframes very easily as follows:</p>
  
  <pre><code>&gt; merge(test,e2s, all.x=T)
        Gene                 Mutation Chromosome Entrez
  1     AGRN                  p.R451H       chr1 375790
  2 C1orf170            p.V663A/V683A       chr1  84808
  3     HES4                   p.R44S       chr1  57801
  4    ISG15                   p.S83N       chr1   9636
  5  PLEKHN1 p.S476P/S511P/S563P/S76P       chr1  84069
  </code></pre>
  
  <p>However, I have been unable to reconstruct this in pandas with merge(how="left,right,inner,outer").. For example:</p>
  
  <pre><code>Outer yields a union, which makes sense:
  x = test.merge(e2s, how="outer")
  In [133]: x.shape
  Out[133]: (46271, 4)
  </code></pre>
  
  <p>But inner yields an empty dataframe, even though <code>Entrez_Gene_Id</code> has been merged successfully:</p>
  
  <pre><code>In [143]: x = test.merge(e2s, how="inner")
  
  In [144]: x
  Out[144]:
  Empty DataFrame
  Columns: [Gene, Mutation, Chromosome, Entrez_Gene_Id]
  Index: []
  
  [0 rows x 4 columns]
  </code></pre>
  
  <p>The intersection should contain one row with the <code>gene : HES4</code>. Is there some sort of string matching I need to turn on for this?:</p>
  
  <p>e2s:</p>
  
  <pre><code>57794   SUGP1
  57795   BRINP2
  57796   DKFZP761C1711
  57798   GATAD1
  57799   RAB40C
  57801   HES4
  57804   POLD4
  57805   CCAR2
  57817   HAMP
  </code></pre>
  
  <p>test:</p>
  
  <pre><code>       Gene                  Mutation Chromosome
  0   PLEKHN1  p.S476P/S511P/S563P/S76P       chr1
  1  C1orf170             p.V663A/V683A       chr1
  2      HES4                    p.R44S       chr1
  3     ISG15                    p.S83N       chr1
  4      AGRN                   p.R451H       chr1
  5    RNF223                   p.P242H       chr1
  </code></pre>
  
  <p><strong>Update:</strong></p>
  
  <p>As far as I know the columns are labelled so that they should merge fine, I only want to merge by the <code>Gene</code> column and keep all test rows:</p>
  
  <pre><code>In [148]: e2s.columns
  Out[148]: Index([u'Gene', u'Entrez_Gene_Id'], dtype='object')
  
  In [149]: test.columns
  Out[149]: Index([u'Gene', u'Mutation', u'Chromosome'], dtype='object')
  </code></pre>
  
  <p>This was done by explicitly renaming the dataframes:</p>
  
  <pre><code>e2s.rename(columns={"Gene":u'Gene',"Entrez_Gene_Id":u'Entrez_Gene_Id'}, inplace=True)
  </code></pre>
  
  <p>to dict:</p>
  
  <pre><code>{u'Chromosome': {0: u'chr1',
    1: u'chr1',
    2: u'chr1',
    3: u'chr1',
    4: u'chr1',
    5: u'chr1'},
   u'Gene': {0: u'PLEKHN1',
    1: u'C1orf170',
    2: u'HES4',
    3: u'ISG15',
    4: u'AGRN',
    5: u'RNF223'},
   u'Mutation': {0: u'p.S476P/S511P/S563P/S76P',
    1: u'p.V663A/V683A',
    2: u'p.R44S',
    3: u'p.S83N',
    4: u'p.R451H',
    5: u'p.P242H'}}
  
  {u'Entrez_Gene_Id': {14118: u'SUGP1',
    14119: u'BRINP2',
    14120: u'DKFZP761C1711',
    14121: u'GATAD1',
    14122: u'RAB40C',
    14123: u'HES4',
    14124: u'POLD4',
    14125: u'CCAR2',
    14126: u'HAMP'},
   u'Gene': {14118: 57794,
    14119: 57795,
    14120: 57796,
    14121: 57798,
    14122: 57799,
    14123: 57801,
    14124: 57804,
    14125: 57805,
    14126: 57817}}
  </code></pre>