merging in pandas vs merging in R <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>