<p dir="auto">The following occurs when using pandas.merge for an (left) outer join with the left_index=True and right_on="something" options. The index of the resulting DataFrame contains duplicate values and the "something" column contains new (compared to before) values that look suspiciously like what the index should be.</p>
<p dir="auto">Is this expected behavior, or could this be a bug?</p>
<p dir="auto">A minimal illustration is:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="
In [2]: pandas.__version__
Out[2]: '0.14.0'

In [3]: df1 = pandas.DataFrame(zeros(8).reshape(4,2), columns=['A', 'B'])

In [4]: df2 = pandas.DataFrame(arange(4).reshape(2,2), columns=['C', 'D'])

In [5]: df1
Out[5]: 
   A  B
0  0  0
1  0  0
2  0  0
3  0  0

In [6]: df2
Out[6]: 
   C  D
0  0  1
1  2  3

In [7]: pandas.merge(df1, df2, left_index=True, right_on='C', how='left')
Out[7]: 
   A  B  C   D
0  0  0  0   1
1  0  0  1 NaN
1  0  0  2   3
1  0  0  3 NaN
"><pre class="notranslate"><span class="pl-v">In</span> [<span class="pl-c1">2</span>]: <span class="pl-s1">pandas</span>.<span class="pl-s1">__version__</span>
<span class="pl-v">Out</span>[<span class="pl-c1">2</span>]: <span class="pl-s">'0.14.0'</span>

<span class="pl-v">In</span> [<span class="pl-c1">3</span>]: <span class="pl-s1">df1</span> <span class="pl-c1">=</span> <span class="pl-s1">pandas</span>.<span class="pl-v">DataFrame</span>(<span class="pl-en">zeros</span>(<span class="pl-c1">8</span>).<span class="pl-en">reshape</span>(<span class="pl-c1">4</span>,<span class="pl-c1">2</span>), <span class="pl-s1">columns</span><span class="pl-c1">=</span>[<span class="pl-s">'A'</span>, <span class="pl-s">'B'</span>])

<span class="pl-v">In</span> [<span class="pl-c1">4</span>]: <span class="pl-s1">df2</span> <span class="pl-c1">=</span> <span class="pl-s1">pandas</span>.<span class="pl-v">DataFrame</span>(<span class="pl-en">arange</span>(<span class="pl-c1">4</span>).<span class="pl-en">reshape</span>(<span class="pl-c1">2</span>,<span class="pl-c1">2</span>), <span class="pl-s1">columns</span><span class="pl-c1">=</span>[<span class="pl-s">'C'</span>, <span class="pl-s">'D'</span>])

<span class="pl-v">In</span> [<span class="pl-c1">5</span>]: <span class="pl-s1">df1</span>
<span class="pl-v">Out</span>[<span class="pl-c1">5</span>]: 
   <span class="pl-v">A</span>  <span class="pl-v">B</span>
<span class="pl-c1">0</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span>
<span class="pl-c1">1</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span>
<span class="pl-c1">2</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span>
<span class="pl-c1">3</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span>

<span class="pl-v">In</span> [<span class="pl-c1">6</span>]: <span class="pl-s1">df2</span>
<span class="pl-v">Out</span>[<span class="pl-c1">6</span>]: 
   <span class="pl-v">C</span>  <span class="pl-v">D</span>
<span class="pl-c1">0</span>  <span class="pl-c1">0</span>  <span class="pl-c1">1</span>
<span class="pl-c1">1</span>  <span class="pl-c1">2</span>  <span class="pl-c1">3</span>

<span class="pl-v">In</span> [<span class="pl-c1">7</span>]: <span class="pl-s1">pandas</span>.<span class="pl-en">merge</span>(<span class="pl-s1">df1</span>, <span class="pl-s1">df2</span>, <span class="pl-s1">left_index</span><span class="pl-c1">=</span><span class="pl-c1">True</span>, <span class="pl-s1">right_on</span><span class="pl-c1">=</span><span class="pl-s">'C'</span>, <span class="pl-s1">how</span><span class="pl-c1">=</span><span class="pl-s">'left'</span>)
<span class="pl-v">Out</span>[<span class="pl-c1">7</span>]: 
   <span class="pl-v">A</span>  <span class="pl-v">B</span>  <span class="pl-v">C</span>   <span class="pl-v">D</span>
<span class="pl-c1">0</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span>   <span class="pl-c1">1</span>
<span class="pl-c1">1</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span>  <span class="pl-c1">1</span> <span class="pl-v">NaN</span>
<span class="pl-c1">1</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span>  <span class="pl-c1">2</span>   <span class="pl-c1">3</span>
<span class="pl-c1">1</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span>  <span class="pl-c1">3</span> <span class="pl-v">NaN</span></pre></div>
<p dir="auto">What I would expect as the result is the following and can be constructed by adding an extra column carrying the left index, doing the join with left_on instead of left_index, and dropping the extra column again:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="
In [8]: df1['i'] = pandas.Series(df1.index)

In [9]: pandas.merge(df1, df2, left_on='i', right_on='C', how='left').drop(['i'],axis=1)
Out[9]: 
   A  B   C   D
0  0  0   0   1
1  0  0 NaN NaN
2  0  0   2   3
3  0  0 NaN NaN
"><pre class="notranslate"><span class="pl-v">In</span> [<span class="pl-c1">8</span>]: <span class="pl-s1">df1</span>[<span class="pl-s">'i'</span>] <span class="pl-c1">=</span> <span class="pl-s1">pandas</span>.<span class="pl-v">Series</span>(<span class="pl-s1">df1</span>.<span class="pl-s1">index</span>)

<span class="pl-v">In</span> [<span class="pl-c1">9</span>]: <span class="pl-s1">pandas</span>.<span class="pl-en">merge</span>(<span class="pl-s1">df1</span>, <span class="pl-s1">df2</span>, <span class="pl-s1">left_on</span><span class="pl-c1">=</span><span class="pl-s">'i'</span>, <span class="pl-s1">right_on</span><span class="pl-c1">=</span><span class="pl-s">'C'</span>, <span class="pl-s1">how</span><span class="pl-c1">=</span><span class="pl-s">'left'</span>).<span class="pl-en">drop</span>([<span class="pl-s">'i'</span>],<span class="pl-s1">axis</span><span class="pl-c1">=</span><span class="pl-c1">1</span>)
<span class="pl-v">Out</span>[<span class="pl-c1">9</span>]: 
   <span class="pl-v">A</span>  <span class="pl-v">B</span>   <span class="pl-v">C</span>   <span class="pl-v">D</span>
<span class="pl-c1">0</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span>   <span class="pl-c1">0</span>   <span class="pl-c1">1</span>
<span class="pl-c1">1</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span> <span class="pl-v">NaN</span> <span class="pl-v">NaN</span>
<span class="pl-c1">2</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span>   <span class="pl-c1">2</span>   <span class="pl-c1">3</span>
<span class="pl-c1">3</span>  <span class="pl-c1">0</span>  <span class="pl-c1">0</span> <span class="pl-v">NaN</span> <span class="pl-v">NaN</span></pre></div>
<p dir="auto">python uses 32 bytes for every int.<br>
numpy uses 4 bytes for an int32 (with which you can index billion of keys).</p>
<p dir="auto">We currently have a hardwired if preventing single-level multi-indexes,<br>
which would allow us to take advantage of factorization to reduce memory<br>
consumption by ~8x for indices which have lots of duplicates.</p>
<p dir="auto">Indexes tend to be unique (by their nature). But the same principle goes<br>
for string columns which are often highligh degenerate.</p>
<p dir="auto">Basically, if we store catagorical data as catagorical, we can<br>
reduce memory footprint drastically. If we do the factorization<br>
at the stage where data is read in (perhaps in conjuction with the iterator based<br>
reader planned for 0.14  <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="8188284" data-permission-text="Title is private" data-url="https://github.com/pandas-dev/pandas/issues/2193" data-hovercard-type="issue" data-hovercard-url="/pandas-dev/pandas/issues/2193/hovercard" href="https://github.com/pandas-dev/pandas/issues/2193">#2193</a>) we can drastically reduce the peak memory usage as well.</p>
<p dir="auto">Update: <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="21549338" data-permission-text="Title is private" data-url="https://github.com/pandas-dev/pandas/issues/5313" data-hovercard-type="issue" data-hovercard-url="/pandas-dev/pandas/issues/5313/hovercard" href="https://github.com/pandas-dev/pandas/issues/5313">#5313</a></p>