Index order of joined frame does not respect the order of left frame when right contains duplicates

[Midnighter]

Code Sample

a = pd.DataFrame(index=["b", "a"], data={"foo": range(2)})
b = pd.DataFrame(index=list("baab"), data={"bar": range(4)})
a.join(b, sort=False)

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from __future__ import absolute_import, print_function

import pandas as pd


def main():
    left = pd.read_json(
        '{"commit_hash":{"60b1efeb45d1c777cee4a1ef49be35fce5b84758":"60b1efeb45d1c777cee4a1ef49be35fce5b84758","3f4665356a24d76a9461043f62a2b12dab56c75f":"3f4665356a24d76a9461043f62a2b12dab56c75f","170d6bca566f4e6b2903f13b189383feaa1e5b1f":"170d6bca566f4e6b2903f13b189383feaa1e5b1f","70b3c923ea6736f83046c0b1570004a7f3eb53d5":"70b3c923ea6736f83046c0b1570004a7f3eb53d5","51331e1c572f614fd979ebec9fa4d61008fc316d":"51331e1c572f614fd979ebec9fa4d61008fc316d"},"timestamp":{"60b1efeb45d1c777cee4a1ef49be35fce5b84758":1493821141000,"3f4665356a24d76a9461043f62a2b12dab56c75f":1493828771000,"170d6bca566f4e6b2903f13b189383feaa1e5b1f":1493830007000,"70b3c923ea6736f83046c0b1570004a7f3eb53d5":1493830708000,"51331e1c572f614fd979ebec9fa4d61008fc316d":1493831446000}}')
    left.sort_values("timestamp", inplace=True, kind="mergesort")
    right = pd.read_json(
        '{"commit":{"0":"60b1efeb45d1c777cee4a1ef49be35fce5b84758","1":"170d6bca566f4e6b2903f13b189383feaa1e5b1f","2":"51331e1c572f614fd979ebec9fa4d61008fc316d","3":"51331e1c572f614fd979ebec9fa4d61008fc316d","4":"3f4665356a24d76a9461043f62a2b12dab56c75f","5":"70b3c923ea6736f83046c0b1570004a7f3eb53d5"},"order":{"0":0,"1":1,"2":2,"3":3,"4":4,"5":5}}')
    right.set_index("commit", inplace=True)
    print("*" * 78)
    print("Desired order of index:")
    print("*" * 78)
    print(left)
    print("*" * 78)
    print("To be joined:")
    print("*" * 78)
    print(right)
    print("*" * 78)
    print("Inner join:")
    print("*" * 78)
    print(left.join(right, how="inner"))
    print("*" * 78)
    print("Left outer join:")
    print("*" * 78)
    print(left.join(right, how="left"))


if __name__ == "__main__":
    main()

Problem description

The index order after joining a frame with duplicate index values seems arbitrary or (lexicographically) sorted rather than preserving the order of the left frame.

Expected Output

When joining two frames the left frame should dictate the order of the resulting data frame. This was seemingly fixed by #15582, however, with the above examples this is not the case.

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.5.2.final.0 python-bits: 64 OS: Linux OS-release: 4.8.0-51-generic machine: x86_64 processor: x86_64 byteorder: little LC_ALL: en_IE.UTF-8 LANG: en_US.UTF-8 LOCALE: en_IE.UTF-8

pandas: 0.20.1
pytest: 3.0.7
pip: 9.0.1
setuptools: 35.0.2
Cython: None
numpy: 1.12.1
scipy: 0.19.0
xarray: None
IPython: 6.0.0
sphinx: 1.5.5
patsy: None
dateutil: 2.6.0
pytz: 2017.2
blosc: None
bottleneck: None
tables: None
numexpr: 2.6.2
feather: None
matplotlib: 2.0.1
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 0.999999999
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.9.6
s3fs: None
pandas_gbq: None
pandas_datareader: None

[TomAugspurger]

cc @albertvillanova if you're interested in taking a look.

[jorisvandenbossche]

Using the example from the whatsnew docs:

In [50]: left = pd.DataFrame({'a': [20, 10, 0]}, index=[2, 1, 0])

In [51]: right = pd.DataFrame({'b': [100, 200, 300]}, index=[1, 2, 3])

In [52]: left.join(right)
Out[52]: 
    a      b
2  20  200.0
1  10  100.0
0   0    NaN

In [53]: right = pd.DataFrame({'b': [100, 105, 200, 300]}, index=[1, 1, 2, 3])

In [54]: left.join(right)
Out[54]: 
    a      b
0   0    NaN
1  10  100.0
1  10  105.0
2  20  200.0

[lukemanley]

This looks to be fixed on main and was likely fixed by #54611 and covered by the tests added there.