Repository: pandas-dev/pandas
Pull request: BUG-24212 fix usage of Index.take in pd.merge
- [X] closes #24212
- [X] tests added / passed
- [X] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [X] whatsnew entry

The use of `Index.take` in `pd.merge` to create `join_index` causes an indexer with -1 values to take the last value in the index being taken from. This PR allows it to correctly handle absent values.

Problem statement:
Merge Using Index Name Produces Incorrect Result
```
In [1]: import pandas as pd

In [2]: pd.__version__
Out[2]: '0.23.4'

In [3]: df_left_data = [
   ...:     {
   ...:         'id': 11,
   ...:         'left_data': 'left_11'
   ...:     },
   ...:     {
   ...:         'id': 22,
   ...:         'left_data': 'left_22'
   ...:     },
   ...:     {
   ...:         'id': 33,
   ...:         'left_data': 'left_33'
   ...:     }
   ...: ]
   ...: 
   ...: df_left = pd.DataFrame(df_left_data)
   ...: df_left.set_index('id', drop=False, inplace=True)
   ...: df_left
   ...: 
   ...: 
Out[3]: 
    id left_data
id              
11  11   left_11
22  22   left_22
33  33   left_33
In [4]: df_right_data = [
   ...:     {
   ...:         'id': 22,
   ...:         'right_data': 'right_22'
   ...:     },
   ...:     {
   ...:         'id': 33,
   ...:         'right_data': 'right_33'
   ...:     }
   ...: ]
   ...: 
   ...: df_right = pd.DataFrame(df_right_data)
   ...: df_right.set_index('id', drop=True, inplace=True)
   ...: df_right
   ...: 
   ...: 
Out[4]: 
   right_data
id           
22   right_22
33   right_33

In [5]: pd.merge(left=df_left, right=df_right, left_index=True, right_on='id', how='left')
Out[5]: 
    id left_data right_data
id                         
33  11   left_11        NaN
22  22   left_22   right_22
33  33   left_33   right_33
```
#### Problem description
The index of the 1st row should be 11 but it's producing **a wrong value of 33** right now.

The following 2 permutations of parameters produce correct results:
#### Expected Output
```
In [6]: pd.merge(left=df_left, right=df_right, left_index=True, right_index=True, how='left')
Out[6]: 
    id left_data right_data
id                         
11  11   left_11        NaN
22  22   left_22   right_22
33  33   left_33   right_33

In [7]: pd.merge(left=df_left, right=df_right, left_on='id', right_index=True, how='left')
Out[7]: 
    id left_data right_data
id                         
11  11   left_11        NaN
22  22   left_22   right_22
33  33   left_33   right_33
```

#### Output of ``pd.show_versions()``

<details>

[paste the output of ``pd.show_versions()`` here below this line]
INSTALLED VERSIONS
------------------
commit: None
python: 3.6.5.final.0
python-bits: 64
OS: Linux
OS-release: 3.10.0-514.6.1.el7.x86_64
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.23.4
pytest: None
pip: 10.0.1
setuptools: 39.1.0
Cython: None
numpy: 1.15.1
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 6.5.0
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 2.2.3
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 1.0.1
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

</details>

Hints from issue discussion:
Hmm yea that does look strange. Investigation and PRs are always welcome!

Commit message: BUG-24212 fix usage of Index.take in pd.merge

Patch to review:
diff --git a/doc/source/whatsnew/v0.24.0.rst b/doc/source/whatsnew/v0.24.0.rst
index 960b205c49c61..2d548e3696e9a 100644
--- a/doc/source/whatsnew/v0.24.0.rst
+++ b/doc/source/whatsnew/v0.24.0.rst
@@ -1815,6 +1815,7 @@ Reshaping
 - Bug in :func:`DataFrame.unstack` where a ``ValueError`` was raised when unstacking timezone aware values (:issue:`18338`)
 - Bug in :func:`DataFrame.stack` where timezone aware values were converted to timezone naive values (:issue:`19420`)
 - Bug in :func:`merge_asof` where a ``TypeError`` was raised when ``by_col`` were timezone aware values (:issue:`21184`)
+- Bug in :func:`merge` when merging by index name would sometimes result in an incorrectly numbered index (:issue:`24212`)
 
 .. _whatsnew_0240.bug_fixes.sparse:
 
diff --git a/pandas/core/reshape/merge.py b/pandas/core/reshape/merge.py
index 7861a122afdb6..6a3a0e4f6ca31 100644
--- a/pandas/core/reshape/merge.py
+++ b/pandas/core/reshape/merge.py
@@ -758,12 +758,26 @@ def _get_join_info(self):
             if self.right_index:
                 if len(self.left) > 0:
                     join_index = self.left.index.take(left_indexer)
+                    if (self.how == 'right' and -1 in left_indexer
+                            and not isinstance(self.right.index, MultiIndex)):
+                        join_list = join_index.to_numpy()
+                        absent = left_indexer == -1
+                        join_list[absent] = self.right.index.to_numpy()[absent]
+                        join_index = Index(join_list, dtype=join_index.dtype,
+                                           name=join_index.name)
                 else:
                     join_index = self.right.index.take(right_indexer)
                     left_indexer = np.array([-1] * len(join_index))
             elif self.left_index:
                 if len(self.right) > 0:
                     join_index = self.right.index.take(right_indexer)
+                    if (self.how == 'left' and -1 in right_indexer
+                            and not isinstance(self.left.index, MultiIndex)):
+                        join_list = join_index.to_numpy()
+                        absent = right_indexer == -1
+                        join_list[absent] = self.left.index.to_numpy()[absent]
+                        join_index = Index(join_list, dtype=join_index.dtype,
+                                           name=join_index.name)
                 else:
                     join_index = self.left.index.take(left_indexer)
                     right_indexer = np.array([-1] * len(join_index))
diff --git a/pandas/tests/reshape/merge/test_merge.py b/pandas/tests/reshape/merge/test_merge.py
index 2080fc542bc61..3408161641831 100644
--- a/pandas/tests/reshape/merge/test_merge.py
+++ b/pandas/tests/reshape/merge/test_merge.py
@@ -1102,6 +1102,16 @@ def test_merge_incompat_dtypes_error(self, df1_vals, df2_vals):
         with pytest.raises(ValueError, match=msg):
             pd.merge(df2, df1, on=['A'])
 
+    def test_merge_on_index_with_more_values(self):  # GH 24212
+        df1 = pd.DataFrame([[1, 2], [2, 4], [3, 6], [4, 8]],
+                           columns=['a', 'b'])
+        df2 = pd.DataFrame([[3, 30], [4, 40]],
+                           columns=['a', 'c'])
+        df1.set_index('a', drop=False, inplace=True)
+        df2.set_index('a', inplace=True)
+        result = pd.merge(df1, df2, left_index=True, right_on='a', how='left')
+        assert 1 in result.index
+
 
 @pytest.fixture
 def left():


Code review comments:
diff_hunk: @@ -1102,6 +1102,16 @@ def test_merge_incompat_dtypes_error(self, df1_vals, df2_vals):
         with pytest.raises(ValueError, match=msg):
             pd.merge(df2, df1, on=['A'])
 
+    def test_merge_on_index_with_more_values(self):  # GH 24212
+        df1 = pd.DataFrame([[1, 2], [2, 4], [3, 6], [4, 8]],
+                           columns=['a', 'b'])
+        df2 = pd.DataFrame([[3, 30], [4, 40]],
+                           columns=['a', 'c'])
+        df1.set_index('a', drop=False, inplace=True)
+        df2.set_index('a', inplace=True)
+        result = pd.merge(df1, df2, left_index=True, right_on='a', how='left')
+        assert 1 in result.index
path: pandas/tests/reshape/merge/test_merge.py
text: @user1:
Hmm...I think we should just check the entire `DataFrame` instead of just an element in `result.index`.  Also, comment on what's being tested for here.