Join result takes the index order of the other (right) DataFrame instead of the calling's (left) one

[albertvillanova]

Code Sample, a copy-pastable example if possible

# Your code here
df1 = pd.DataFrame({'a': [0, 10, 20]})
df2 = pd.DataFrame({'b': [200, 100]}, index=[2,1])

print(df1.join(df2, how='inner'))
print(df2.join(df1, how='inner'))

print(df1.join(df2, how='inner', sort=True))

Problem description

Contrary to what is stated in the documentation of DataFrame.join(), when using the default sort=False, the return DataFrame preserves the index order of the other (right) DataFrame, instead of the index order of the calling (left) DataFrame.

Besides, the sort=True argument does not work.

Expected Output

The expected output is that the return DataFrame should preserve the index order of the calling (left) DataFrame.

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.5.2.final.0 python-bits: 64 OS: Linux OS-release: 3.13.0-108-generic machine: x86_64 processor: x86_64 byteorder: little LC_ALL: None LANG: ca_ES.UTF-8 LOCALE: ca_ES.UTF-8

pandas: 0.19.2
nose: 1.3.7
pip: 8.1.2
setuptools: 27.2.0.post20161106
Cython: 0.24.1
numpy: 1.11.1
scipy: 0.18.1
statsmodels: 0.6.1
xarray: None
IPython: 5.1.0
sphinx: 1.4.6
patsy: 0.4.1
dateutil: 2.5.3
pytz: 2016.6.1
blosc: None
bottleneck: 1.1.0
tables: 3.2.3.1
numexpr: 2.6.1
matplotlib: 1.5.3
openpyxl: 2.3.2
xlrd: 1.0.0
xlwt: 1.1.2
xlsxwriter: 0.9.3
lxml: 3.6.4
bs4: 4.5.1
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: 1.0.13
pymysql: None
psycopg2: None
jinja2: 2.8
boto: 2.42.0
pandas_datareader: 0.2.1

[jreback]

hmm, I think this needs a suite of tests. There are very few for sort=True (and only a single dtype anyhow). It appears you are correct that this violates the guarantee.

[sleepdeprivation]

@jreback noob here, where to write these tests?

edit: found /tests , I guess I'm just looking for guidance ?

[jreback]

pandas/tests/tools/test_join.py (there is a single sorted test now)

[sleepdeprivation]

@jreback OK cool, thanks for the quick reply

[sleepdeprivation]

@jreback So if I just swap self and other in the call to merge in pandas/core/frame.py > _join_compat
, wouldn't that adequately solve the indexing problem?

And then I guess you want tests written for join to test the sort param?

[jreback]

i think this might break a large amount of tests
see what happens

[sleepdeprivation]

Old:

>>> import pandas as pd
>>> # Your code here
... df1 = pd.DataFrame({'a': [0, 10, 20]})
>>> df2 = pd.DataFrame({'b': [200, 100]}, index=[2,1])
>>> 
>>> print(df1.join(df2, how='inner'))
    a    b
2  20  200
1  10  100
>>> print(df2.join(df1, how='inner'))
     b   a
1  100  10
2  200  20
>>> 
>>> print(df1.join(df2, how='inner', sort=True))
    a    b
2  20  200
1  10  100

New:

>>> # Your code here
... df1 = pd.DataFrame({'a': [0, 10, 20]})
>>> df2 = pd.DataFrame({'b': [200, 100]}, index=[2,1])
>>> 
>>> print(df1.join(df2, how='inner'))
DATA FRAME JOIN
     b   a
1  100  10
2  200  20
>>> print(df2.join(df1, how='inner'))
DATA FRAME JOIN
    a    b
2  20  200
1  10  100
>>> 
>>> print(df1.join(df2, how='inner', sort=True))
DATA FRAME JOIN
     b   a
1  100  10
2  200  20

my pytest all succeeds except these fail even with clean code

========================  test_traitsdoc.py  =========================
======================  test_phantom_import.py  ======================
======================  test_plot_directive.py  ======================
========================  test_docscrape.py  =========================
=========================  test_linkcode.py  =========================

They all fail with ImportError, and as I said fail even with clean code so I assume this has nothing to do with the modification...?

[jreback]

note that this is only going to matter for `how='inner'. how='right'|'left'`` will take on the orderings of those respective indexes. ``outer`` I think we cannot guarantee an ordering.

In any event need tests for all of these.

[albertvillanova]

The bug is not at the DataFrame level but at the Index level.

The swap of arguments in the call to merge proposed by @sleepdeprivation is going to generate a new bug with swapped lsuffix and rsuffix. Moreover the order of the columns in the result is also exchanged (first columns from the right DataFrame, then columns from the left one).

The real bug is at the Index level, concretely in the Index.intersection() method. The get_indexer must be called on the right index and with left index as argument, so that it returns a mask which transforms the caller index (right) into the passed index (left), and not the other way around. This is the desired output: a mask on the right index which picks its elements in such a way that it gets aligned with the left index.

I have implemented/updated some tests in my PR.

[albertvillanova]

One question about the tests: I have implemented/updated some tests in files tests/indexes/test_base.py and tests/frame/test_misc_api.py (test_join_index), but as you said there are also tests on join in file tests/tools/test_join.py. I do not understand the logic in the directory structure of the tests.

[jreback]

I think it should be self-evident. you have tests on the indexes themselves, and the api on frames (which only is a dispatch from pd.merge), and the actual impl of merge/join in tools.