<h4 dir="auto">Code Sample, a copy-pastable example if possible</h4>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="df = pd.DataFrame([['A', 1],  
                   ['B', 2], 
                   ['B', 3], 
                   ['A', 4]], columns=['Col1', 'Col2'])
df['Col1'] = pd.Categorical(df.Col1)"><pre class="notranslate"><span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>([[<span class="pl-s">'A'</span>, <span class="pl-c1">1</span>],  
                   [<span class="pl-s">'B'</span>, <span class="pl-c1">2</span>], 
                   [<span class="pl-s">'B'</span>, <span class="pl-c1">3</span>], 
                   [<span class="pl-s">'A'</span>, <span class="pl-c1">4</span>]], <span class="pl-s1">columns</span><span class="pl-c1">=</span>[<span class="pl-s">'Col1'</span>, <span class="pl-s">'Col2'</span>])
<span class="pl-s1">df</span>[<span class="pl-s">'Col1'</span>] <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">Categorical</span>(<span class="pl-s1">df</span>.<span class="pl-v">Col1</span>)</pre></div>
<p dir="auto"><a target="_blank" rel="noopener noreferrer nofollow" href="https://camo.githubusercontent.com/ef229ae40aa2db2b89e737d36c6a68dcb4126229520f40dab8d0c3ba7a61ec4c/68747470733a2f2f692e696d6775722e636f6d2f33696e75674c512e706e67"><img src="https://camo.githubusercontent.com/ef229ae40aa2db2b89e737d36c6a68dcb4126229520f40dab8d0c3ba7a61ec4c/68747470733a2f2f692e696d6775722e636f6d2f33696e75674c512e706e67" alt="df" data-canonical-src="https://i.imgur.com/3inugLQ.png" style="max-width: 100%;"></a></p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="pd.merge(df, df, on='Col1', how='inner')
"><pre class="notranslate"><span class="pl-s1">pd</span>.<span class="pl-en">merge</span>(<span class="pl-s1">df</span>, <span class="pl-s1">df</span>, <span class="pl-s1">on</span><span class="pl-c1">=</span><span class="pl-s">'Col1'</span>, <span class="pl-s1">how</span><span class="pl-c1">=</span><span class="pl-s">'inner'</span>)</pre></div>
<h4 dir="auto">Problem description</h4>
<p dir="auto">According to Pandas 0.23.0 doc :</p>
<p dir="auto">for the keyword "how", the especially "inner" config (bolded part):</p>
<blockquote>
<p dir="auto">how : {‘left’, ‘right’, ‘outer’, ‘inner’}, default ‘inner’</p>
<ul dir="auto">
<li>left: use only keys from left frame, similar to a SQL left outer join; preserve key order</li>
<li>right: use only keys from right frame, similar to a SQL right outer join; preserve key order</li>
<li>outer: use union of keys from both frames, similar to a SQL full outer join; sort keys lexicographically</li>
<li>inner: use intersection of keys from both frames, similar to a SQL inner join; <strong>preserve the order of the left keys</strong></li>
</ul>
</blockquote>
<p dir="auto">So the merge is supposed to preserve the order of the left keys.<br>
However, if Categorical Data is used as the key to merge, the order is not preserved.</p>
<p dir="auto">The output dataframe has been sorted as per the join key, and the index has been reseted.<br>
Which obviously provoke some unwanted results.</p>
<p dir="auto">Note that <em>how='left'</em> does provide the correct results (regarding key ordering).</p>
<h4 dir="auto">Expected Output</h4>
<p dir="auto">Expected Output :<br>
<a target="_blank" rel="noopener noreferrer nofollow" href="https://camo.githubusercontent.com/6874ba4fd903ecc54cb1bb0895ca7c424f4193ce4a7a686250a4178efd2cf75d/68747470733a2f2f692e696d6775722e636f6d2f476e74525770452e706e67"><img src="https://camo.githubusercontent.com/6874ba4fd903ecc54cb1bb0895ca7c424f4193ce4a7a686250a4178efd2cf75d/68747470733a2f2f692e696d6775722e636f6d2f476e74525770452e706e67" alt="Expected Output" data-canonical-src="https://i.imgur.com/GntRWpE.png" style="max-width: 100%;"></a></p>
<p dir="auto">Actual Output :<br>
<a target="_blank" rel="noopener noreferrer nofollow" href="https://camo.githubusercontent.com/0d5e5bc9780b6b933e3392a0cdbbcc0cb0b33aee28a053e3f4fa4599ea0a4fe4/68747470733a2f2f692e696d6775722e636f6d2f4e4e5a666871312e706e67"><img src="https://camo.githubusercontent.com/0d5e5bc9780b6b933e3392a0cdbbcc0cb0b33aee28a053e3f4fa4599ea0a4fe4/68747470733a2f2f692e696d6775722e636f6d2f4e4e5a666871312e706e67" alt="Actual Output" data-canonical-src="https://i.imgur.com/NNZfhq1.png" style="max-width: 100%;"></a></p>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
<h2 dir="auto">INSTALLED VERSIONS</h2>
<p dir="auto">commit: None<br>
python: 2.7.14.final.0<br>
python-bits: 64<br>
OS: Linux<br>
OS-release: 4.13.0-38-generic<br>
machine: x86_64<br>
processor: x86_64<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: en_US.UTF-8<br>
LOCALE: None.None</p>
<p dir="auto">pandas: 0.23.0<br>
pytest: None<br>
pip: 9.0.1<br>
setuptools: 36.5.0.post20170921<br>
Cython: None<br>
numpy: 1.14.3<br>
scipy: 1.1.0<br>
pyarrow: None<br>
xarray: None<br>
IPython: 5.4.1<br>
sphinx: None<br>
patsy: None<br>
dateutil: 2.6.1<br>
pytz: 2018.4<br>
blosc: None<br>
bottleneck: None<br>
tables: None<br>
numexpr: None<br>
feather: None<br>
matplotlib: None<br>
openpyxl: None<br>
xlrd: None<br>
xlwt: None<br>
xlsxwriter: None<br>
lxml: None<br>
bs4: None<br>
html5lib: 0.9999999<br>
sqlalchemy: None<br>
pymysql: None<br>
psycopg2: None<br>
jinja2: 2.9.6<br>
s3fs: None<br>
fastparquet: None<br>
pandas_gbq: None<br>
pandas_datareader: None</p>
</details>
<p dir="auto"><a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/chrish42/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/chrish42">@chrish42</a></p>
<p dir="auto">see also:<br>
<a href="https://stackoverflow.com/questions/47793302/python-pandas-dataframe-merge-strange-sort-order-for-how-inner" rel="nofollow">https://stackoverflow.com/questions/47793302/python-pandas-dataframe-merge-strange-sort-order-for-how-inner</a></p>
<p dir="auto">I do not understand the sort order for Python Pandas DataFrame <code class="notranslate">merge</code> function with <code class="notranslate">how="inner"</code>. Example:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="import pandas as pd

df2 = pd.DataFrame({'a': (6, 7, 8, 6), 'b': (&quot;w&quot;, &quot;x&quot;, &quot;y&quot;, &quot;z&quot;)})
print(df2)
print(&quot;left:&quot;)
dfMerge2 = pd.merge(df2, df2, on='a', how=&quot;left&quot;)
print(dfMerge2)
dfMerge = pd.merge(df2, df2, on='a', how=&quot;inner&quot;)
print(&quot;inner:&quot;)
print(dfMerge)"><pre class="notranslate"><code class="notranslate">import pandas as pd

df2 = pd.DataFrame({'a': (6, 7, 8, 6), 'b': ("w", "x", "y", "z")})
print(df2)
print("left:")
dfMerge2 = pd.merge(df2, df2, on='a', how="left")
print(dfMerge2)
dfMerge = pd.merge(df2, df2, on='a', how="inner")
print("inner:")
print(dfMerge)
</code></pre></div>
<p dir="auto">Result:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="   a  b
0  6  w
1  7  x
2  8  y
3  6  z
left:
   a b_x b_y
0  6   w   w
1  6   w   z
2  7   x   x
3  8   y   y
4  6   z   w
5  6   z   z
inner:
   a b_x b_y
0  6   w   w
1  6   w   z
2  6   z   w
3  6   z   z
4  7   x   x
5  8   y   y"><pre class="notranslate"><code class="notranslate">   a  b
0  6  w
1  7  x
2  8  y
3  6  z
left:
   a b_x b_y
0  6   w   w
1  6   w   z
2  7   x   x
3  8   y   y
4  6   z   w
5  6   z   z
inner:
   a b_x b_y
0  6   w   w
1  6   w   z
2  6   z   w
3  6   z   z
4  7   x   x
5  8   y   y
</code></pre></div>
<p dir="auto">I would expect that for <code class="notranslate">how="inner"</code> the order of the resulting rows with</p>
<p dir="auto"><code class="notranslate">6   z   w</code> and</p>
<p dir="auto"><code class="notranslate">6   z   z</code></p>
<p dir="auto">would be the same as with <code class="notranslate">how="left"</code>, as the documentation <a href="https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html" rel="nofollow">https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html</a> says:</p>
<ul dir="auto">
<li>left: use only keys from left frame, similar to a SQL left outer join; <em><strong>preserve key order</strong></em></li>
<li>inner: use intersection of keys from both frames, similar to a SQL inner join; <em><strong>preserve the order of the left keys</strong></em></li>
</ul>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
<h2 dir="auto">[paste the output of <code class="notranslate">pd.show_versions()</code> here below this line]<br>
INSTALLED VERSIONS</h2>
<p dir="auto">commit: None<br>
python: 3.6.2.final.0<br>
python-bits: 64<br>
OS: Linux<br>
OS-release: 4.4.0-103-generic<br>
machine: x86_64<br>
processor: x86_64<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: en_US.UTF-8<br>
LOCALE: en_US.UTF-8<br>
pandas: 0.20.3<br>
pytest: None<br>
pip: 9.0.1<br>
setuptools: 36.4.0<br>
Cython: None<br>
numpy: 1.13.1<br>
scipy: 0.19.1<br>
xarray: None<br>
IPython: None<br>
sphinx: None<br>
patsy: 0.4.1<br>
dateutil: 2.6.1<br>
pytz: 2017.2<br>
blosc: None<br>
bottleneck: None<br>
tables: None<br>
numexpr: None<br>
feather: None<br>
matplotlib: 2.0.2<br>
openpyxl: None<br>
xlrd: None<br>
xlwt: None<br>
xlsxwriter: None<br>
lxml: None<br>
bs4: None<br>
html5lib: None<br>
sqlalchemy: 1.1.13<br>
pymysql: 0.7.9.None<br>
psycopg2: None<br>
jinja2: None<br>
s3fs: None<br>
pandas_gbq: None<br>
pandas_datareader: None</p>
</details>