ERR: left_index AND left_on or right_index AND right_on should raise

[Satyadev592]

There seems to be a bug in the pandas merge , particularly on the left_index.

df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
                'B': ['B0', 'B1', 'B2', 'B3'],
                'C': ['C0', 'C1', 'C2', 'C3'],
                'D': ['D0', 'D1', 'D2', 'D3']},index=[0, 1, 2, 3])

df2 = pd.DataFrame({'A': ['A0', 'A5', 'A6', 'A7'],
                'B': ['B1', 'B5', 'B6', 'B7'],
                'C': ['A1', 'C5', 'C6', 'C7'],
                'D': ['B1', 'D5', 'D6', 'D7']},index=[4, 5, 6, 7])

pd.merge(df1, df2, how='outer', left_index=True, left_on='A', right_on='A')
pd.merge(df1, df2, how='outer', right_index=True, left_on='A', right_on='A')

The above two merges give me different output.

This a fork of a stack overflow post-
http://stackoverflow.com/questions/43779003/will-output-change-if-right-index-is-used-instead-of-left-index-with-both-left-o

[alan-wong]

I just posted the exact same question: #16229 shall I delete mine

[Satyadev592]

That's fine , some moderator will most likely merge them.

[jreback]

The issue here is specifying left_index AND left_on or right_index AND right_on is invalid. Both of these expression should raise a MergeError.

IIRC we fixed this for merge_asof, @chrisaycock ?

[chrisaycock]

@jreback Oddly, this does not appear to be fixed in merge_asof():

In [4]: df1 = pd.DataFrame({'time': [1, 2, 3], 'value': list("abc")}, index=[100, 101, 102])

In [5]: df2 = pd.DataFrame({'time': [1, 3, 3], 'result': list("xyz")}, index=[100, 103, 104])

In [6]: pd.merge_asof(df1, df2, on='time', left_index=True, right_index=True)
Out[6]: 
     time value result
100     1     a      x
101     2     b      x
102     3     c      x

I thought I had checked for that. I will have to add a guard.

[jreback]

thanks @chrisaycock I think we should do a more general check before in any event, as this clearly is an error (for all merge functions). and of course the 'detection' logic (of the parameters) is quite complex atm.

[ngould]

Should this issue also cover preventing on with either left_index or right_index? We're debating implementation of a cross-join over on #5401, and looking for some clarity about the intended semantics of these arguments, when used together. (Personal opinion: it seems weird for on to work with any *_index arg, since both supposedly determine the join key.)