left join fails in case of non-unique indices

[behzadnouri]

It seems to me that join operation fails if the index is not of unique values. The particular circumastance that I observed this was with multi-index:

df1.set_index( [ 'col1', 'col2', 'col3' ], inplace=True )
df2.join ( df1, on=['cola', 'colb', 'colc' ], how='left' )

I understand that the above join operation is not well-defined for non-unique index values, but pandas gives wrong values even for unique matches. ( no warnings, error messages whatsoever )

In case checking for index integrity has a heavy performance cost, it should be documented that this method fails if the index is not unique. ( or alternatively have the optional argument to enforce integrity check )

I could get correct join by doing below:

df1.drop_duplicates( cols=[ 'col1', 'col2', 'col3' ], inplace=True )
df1.set_index( [ 'col1', 'col2', 'col3' ], inplace=True )
df2.join ( df1, on=['cola', 'colb', 'colc' ], how='left' )

[jreback]

can u post the frames that reproduce this?
(a minimal sample)

[behzadnouri]

below is an example; As I mentioned above, I understand that the set_index here results in an index of non-unique values, but even for the X, Y, B which is unique we get wrong value;

>>> pd.__version__
'0.12.0-1040-gc435e72'
>>> df1
  col1 col2 col3  val1
0    X    Y    A     1
1    X    Y    A     4
2    X    Y    B     5
3    X    Y    C     6
4    X    Y    C     9
>>> df2
  cola colb colc val2
0    X    Y    A    a
1    X    Y    B    b
2    X    Y    C    c
>>> df1.set_index( [ 'col1', 'col2', 'col3' ], inplace=True )
>>> df2.join ( df1, on=['cola', 'colb', 'colc' ], how='left' )
  cola colb colc val2  val1
0    X    Y    A    a     1
1    X    Y    B    b     4
2    X    Y    C    c     5

[jreback]

works in the case of an outer join, so could be a bug (or their is some sort of uniqueness guarantee there)...mark as a bug

In [12]: df2.join ( df1, on=['cola', 'colb', 'colc' ], how='outer' )
Out[12]: 
  cola colb colc val2  val1
0    X    Y    A    a     1
0    X    Y    A    a     4
1    X    Y    B    b     5
2    X    Y    C    c     6
2    X    Y    C    c     9

[jreback]

closed by #7853