Issue 1:
Code Sample, a copy-pastable example if possible

df = pd.DataFrame([['A', 1],
['B', 2],
['B', 3],
['A', 4]], columns=['Col1', 'Col2'])
df['Col1'] = pd.Categorical(df.Col1)

pd.merge(df, df, on='Col1', how='inner')

Problem description

According to Pandas 0.23.0 doc :

for the keyword "how", the especially "inner" config (bolded part):

how : {‘left’, ‘right’, ‘outer’, ‘inner’}, default ‘inner’

left: use only keys from left frame, similar to a SQL left outer join; preserve key order

right: use only keys from right frame, similar to a SQL right outer join; preserve key order

outer: use union of keys from both frames, similar to a SQL full outer join; sort keys lexicographically

inner: use intersection of keys from both frames, similar to a SQL inner join; preserve the order of the left keys

So the merge is supposed to preserve the order of the left keys.

However, if Categorical Data is used as the key to merge, the order is not preserved.

The output dataframe has been sorted as per the join key, and the index has been reseted.

Which obviously provoke some unwanted results.

Note that how='left' does provide the correct results (regarding key ordering).

Expected Output

Expected Output :

Actual Output :

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None

python: 2.7.14.final.0

python-bits: 64

OS: Linux

OS-release: 4.13.0-38-generic

machine: x86_64

processor: x86_64

byteorder: little

LC_ALL: None

LANG: en_US.UTF-8

LOCALE: None.None

pandas: 0.23.0

pytest: None

pip: 9.0.1

setuptools: 36.5.0.post20170921

Cython: None

numpy: 1.14.3

scipy: 1.1.0

pyarrow: None

xarray: None

IPython: 5.4.1

sphinx: None

patsy: None

dateutil: 2.6.1

pytz: 2018.4

blosc: None

bottleneck: None

tables: None

numexpr: None

feather: None

matplotlib: None

openpyxl: None

xlrd: None

xlwt: None

xlsxwriter: None

lxml: None

bs4: None

html5lib: 0.9999999

sqlalchemy: None

pymysql: None

psycopg2: None

jinja2: 2.9.6

s3fs: None

fastparquet: None

pandas_gbq: None

pandas_datareader: None

@chrish42

Issue 2:
see also:

https://stackoverflow.com/questions/47793302/python-pandas-dataframe-merge-strange-sort-order-for-how-inner

I do not understand the sort order for Python Pandas DataFrame merge function with how="inner". Example:

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)

Result:

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

I would expect that for how="inner" the order of the resulting rows with

6 z w and

6 z z

would be the same as with how="left", as the documentation https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html says:

left: use only keys from left frame, similar to a SQL left outer join; preserve key order

inner: use intersection of keys from both frames, similar to a SQL inner join; preserve the order of the left keys

Output of pd.show_versions()

[paste the output of pd.show_versions() here below this line]

INSTALLED VERSIONS

commit: None

python: 3.6.2.final.0

python-bits: 64

OS: Linux

OS-release: 4.4.0-103-generic

machine: x86_64

processor: x86_64

byteorder: little

LC_ALL: None

LANG: en_US.UTF-8

LOCALE: en_US.UTF-8

pandas: 0.20.3

pytest: None

pip: 9.0.1

setuptools: 36.4.0

Cython: None

numpy: 1.13.1

scipy: 0.19.1

xarray: None

IPython: None

sphinx: None

patsy: 0.4.1

dateutil: 2.6.1

pytz: 2017.2

blosc: None

bottleneck: None

tables: None

numexpr: None

feather: None

matplotlib: 2.0.2

openpyxl: None

xlrd: None

xlwt: None

xlsxwriter: None

lxml: None

bs4: None

html5lib: None

sqlalchemy: 1.1.13

pymysql: 0.7.9.None

psycopg2: None

jinja2: None

s3fs: None

pandas_gbq: None

pandas_datareader: None