left join on index with multiple matches now works (bug #5391)

    import sys, os
    sys.path = [os.path.expanduser('~/dev/pandas/build/lib.linux-x86_64-3.4')] \
            + [os.path.expanduser('~/dev/pandas/pandas/tools/tests/')] \
            + sys.path

    import numpy as np
    import pandas as pd
    from test_merge import TestMerge

    from numpy import nan
    from pandas import DataFrame
    import pandas.util.testing as tm

    # left join with multiple matches - single index case
    left = DataFrame([
        ['c', 0],
        ['b', 1],
        ['a', 2]],
        columns=['tag', 'val'],
        index=[0, 2, 1])
                                                    
    right = DataFrame([
        ['v', 'a'],
        ['w', 'c'],
        ['x', 'c'],
        ['y', 'd'],
        ['z', 'a']],
        columns=['char', 'tag'])
                                                    
    right.set_index('tag', inplace=True)
    result = left.join(right, on='tag', how='left')
                                                    
    expected = DataFrame([
       ['c', 0, 'w'],
       ['c', 0, 'x'],
       ['b', 1, nan],
       ['a', 2, 'v'],
       ['a', 2, 'z']],
       columns=['tag', 'val', 'char'],
       index=[0, 0, 2, 1, 1])
                                                    
    tm.assert_frame_equal(result, expected)
    print(left, right, result, sep='\n')

    # left join with multiple matches - multi index case
    left = DataFrame([
        ['X', 'Y', 'C', 'a'],
        ['X', 'Y', 'D', 'b'],
        ['X', 'Y', 'A', 'c'],
        ['X', 'Y', 'B', 'd']],
        columns=['cola', 'colb', 'colc', 'tag'],
        index=[3, 2, 0, 1])
                                                                       
    right = DataFrame([
        ['X', 'Y', 'A', 1],
        ['X', 'Y', 'A', 4],
        ['X', 'Y', 'B', 5],
        ['X', 'Y', 'C', 6],
        ['X', 'Y', 'C', 9]],
        columns=['col1', 'col2', 'col3', 'val'])

    right.set_index(['col1', 'col2', 'col3'], inplace=True)
    result = left.join(right, on=['cola', 'colb', 'colc'], how='left')
                                          
    expected = DataFrame([
        ['X', 'Y', 'C', 'a', 6.0],
        ['X', 'Y', 'C', 'a', 9.0],
        ['X', 'Y', 'D', 'b', nan],
        ['X', 'Y', 'A', 'c', 1.0],
        ['X', 'Y', 'A', 'c', 4.0],
        ['X', 'Y', 'B', 'd', 5.0]],
        columns=['cola', 'colb', 'colc', 'tag', 'val'],
        index=[3, 3, 2, 0, 0, 1])
                  
    tm.assert_frame_equal(result, expected)

    print(left, right, result, sep='\n')

This closes on: https://github.com/pydata/pandas/issues/5391
By providing all the matches when doing left join on index, both in the case of single index and multi-index. It also preserves the index order of the calling (left) DataFrame (as it used to), though when there are multiple matches the indices repeat and the index loses integrity.

The added test cases should be self-explanatory.

Thank you,
does this work similarly on right / inner / outer when multiple matches? e.g. is left special case behavior, if so, why is that? if not, can you test with other how's. thanks.