Repository: pandas-dev/pandas
Pull request: BUG: DataFrame.join on tz-aware DatetimeIndex
- [x] closes #23931
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Problem statement:
`join` on tz-aware column and tz-aware index wrongly fails (regression from 0.22)
#### Code Sample, a copy-pastable example if possible

```python
df1 = pd.DataFrame(
    {
        'date': pd.date_range(start='2018-01-01', periods=5,tz='America/Chicago'), 
        'vals':list('abcde')
    }
)

df2 = pd.DataFrame(
    {
        'date': pd.date_range(start='2018-01-03',periods=5, tz='America/Chicago'), 
        'vals_2': list('tuvwx')
    }
)
joined = df1.join(df2.set_index('date'),on='date')
```

#### Problem description

In 0.23.4, this raises:
```ValueError: You are trying to merge on datetime64[ns, America/Chicago] and datetime64[ns] columns. If you wish to proceed you should use pd.concat```

This is incorrect - the two columns are both tz-aware and indeed `df2.set_index('date').index.dtype == df1.date.dtype` returns True.

In 0.22.0, this does not raise, and returns the expected output. Haven't run git bisect yet but seems like the logical next step.

#### Expected Output

```                        date vals vals_2
0  2018-01-01 00:00:00-06:00    a    NaN
1  2018-01-02 00:00:00-06:00    b    NaN
2  2018-01-03 00:00:00-06:00    c      t
3  2018-01-04 00:00:00-06:00    d      u
4  2018-01-05 00:00:00-06:00    e      v
```

#### Output of ``pd.show_versions()``

<details>

INSTALLED VERSIONS
------------------
commit: None
python: 3.6.6.final.0
python-bits: 64
OS: Linux
OS-release: 4.4.0-1072-aws
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: C.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.23.4
pytest: None
pip: 18.1
setuptools: 39.2.0
Cython: None
numpy: 1.15.4
scipy: 1.1.0
pyarrow: 0.9.0
xarray: None
IPython: 6.5.0
sphinx: None
patsy: 0.5.0
dateutil: 2.7.5
pytz: 2018.7
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: 0.4.0
matplotlib: 2.1.2
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 1.0.1
sqlalchemy: 1.2.14
pymysql: None
psycopg2: 2.7.6.1 (dt dec pq3 ext lo64)
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

</details>

Hints from issue discussion:
Somewhere in `self._get_merge_keys` we coerce `right`'s index to a ndarray

```python
ipdb> self.right_join_keys
[array(['2018-01-03T06:00:00.000000000', '2018-01-04T06:00:00.000000000',
       '2018-01-05T06:00:00.000000000', '2018-01-06T06:00:00.000000000',
       '2018-01-07T06:00:00.000000000'], dtype='datetime64[ns]')]
ipdb>
```

I'd recommend going through `core/reshape/merge.py::_MergeOperation._get_merge_keys` to see where things go wrong.
I think https://github.com/pandas-dev/pandas/issues/21184 may be affected by the same root cause as this issue.
Thanks @TomAugspurger ! I think this patch should fix the issue (and added tests), if that looks about right I'll submit a PR.

https://github.com/louispotok/pandas/commit/b29c6bc79c36ba311cdb450e3a5d1eabdf7d1d74

@mroeschke haven't checked if this also fixed #21184 but will take a look.
same bug here:

while the following works fine

```py
a = pd.DataFrame({'a': range(5), 'x': pd.date_range('2018-11-01', periods=5, freq='1T', tz=None)})
b = pd.DataFrame({'b': range(5)}, index=pd.date_range('2018-11-01', periods=5, freq='1T', tz=None))
a.join(b, on='x', lsuffix='_')
```

if we change tz to a specific time zone, like following:

```py
a = pd.DataFrame({'a': range(5), 'x': pd.date_range('2018-11-01', periods=5, freq='1T', tz='utc')})
b = pd.DataFrame({'b': range(5)}, index=pd.date_range('2018-11-01', periods=5, freq='1T', tz='utc'))
a.join(b, on='x', lsuffix='_')
```

it failed:

```txt
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-84-30f4c345c06a> in <module>
      1 a = pd.DataFrame({'a': range(5), 'x': pd.date_range('2018-11-01', periods=5, freq='1T', tz='utc')})
      2 b = pd.DataFrame({'b': range(5)}, index=pd.date_range('2018-11-01', periods=5, freq='1T', tz='utc'))
----> 3 a.join(b, on='x', lsuffix='_')

lib/python3.7/site-packages/pandas/core/frame.py in join(self, other, on, how, lsuffix, rsuffix, sort)
   6334         # For SparseDataFrame's benefit
   6335         return self._join_compat(other, on=on, how=how, lsuffix=lsuffix,
-> 6336                                  rsuffix=rsuffix, sort=sort)
   6337 
   6338     def _join_compat(self, other, on=None, how='left', lsuffix='', rsuffix='',

lib/python3.7/site-packages/pandas/core/frame.py in _join_compat(self, other, on, how, lsuffix, rsuffix, sort)
   6349             return merge(self, other, left_on=on, how=how,
   6350                          left_index=on is None, right_index=True,
-> 6351                          suffixes=(lsuffix, rsuffix), sort=sort)
   6352         else:
   6353             if on is not None:

lib/python3.7/site-packages/pandas/core/reshape/merge.py in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
     59                          right_index=right_index, sort=sort, suffixes=suffixes,
     60                          copy=copy, indicator=indicator,
---> 61                          validate=validate)
     62     return op.get_result()
     63 

lib/python3.7/site-packages/pandas/core/reshape/merge.py in __init__(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)
    553         # validate the merge keys dtypes. We may need to coerce
    554         # to avoid incompat dtypes
--> 555         self._maybe_coerce_merge_keys()
    556 
    557         # If argument passed to validate,

lib/python3.7/site-packages/pandas/core/reshape/merge.py in _maybe_coerce_merge_keys(self)
    990                 raise ValueError(msg)
    991             elif is_datetime64tz_dtype(lk) and not is_datetime64tz_dtype(rk):
--> 992                 raise ValueError(msg)
    993             elif not is_datetime64tz_dtype(lk) and is_datetime64tz_dtype(rk):
    994                 raise ValueError(msg)

ValueError: You are trying to merge on datetime64[ns, UTC] and datetime64[ns] columns. If you wish to proceed you should use pd.concat
```

**pandas.__version__ == '0.23.4'**
FYI, `pd.merge` works fine for these. Something is buggy in `join` before it calls the merge code.

Commit message: remove space

Patch to review:
diff --git a/doc/source/whatsnew/v0.25.0.rst b/doc/source/whatsnew/v0.25.0.rst
index 4032dc20b2e19..5666360b6ab94 100644
--- a/doc/source/whatsnew/v0.25.0.rst
+++ b/doc/source/whatsnew/v0.25.0.rst
@@ -181,7 +181,7 @@ Reshaping
 - Bug in :func:`pandas.merge` adds a string of ``None`` if ``None`` is assigned in suffixes instead of remain the column name as-is (:issue:`24782`).
 - Bug in :func:`merge` when merging by index name would sometimes result in an incorrectly numbered index (:issue:`24212`)
 - :func:`to_records` now accepts dtypes to its `column_dtypes` parameter (:issue:`24895`)
--
+- Bug in :func:`DataFrame.join` when joining on a timezone aware :class:`DatetimeIndex` (:issue:`23931`)
 
 
 Sparse
diff --git a/pandas/core/reshape/merge.py b/pandas/core/reshape/merge.py
index ad3327e694b67..fb50a3c60f705 100644
--- a/pandas/core/reshape/merge.py
+++ b/pandas/core/reshape/merge.py
@@ -909,7 +909,7 @@ def _get_merge_keys(self):
                               in zip(self.right.index.levels,
                                      self.right.index.codes)]
             else:
-                right_keys = [self.right.index.values]
+                right_keys = [self.right.index._values]
         elif _any(self.right_on):
             for k in self.right_on:
                 if is_rkey(k):
diff --git a/pandas/tests/reshape/merge/test_join.py b/pandas/tests/reshape/merge/test_join.py
index 5d7a9ab6f4cf0..62c9047b17f3d 100644
--- a/pandas/tests/reshape/merge/test_join.py
+++ b/pandas/tests/reshape/merge/test_join.py
@@ -682,6 +682,28 @@ def test_join_multi_to_multi(self, join_type):
         with pytest.raises(ValueError, match=msg):
             right.join(left, on=['abc', 'xy'], how=join_type)
 
+    def test_join_on_tz_aware_datetimeindex(self):
+        # GH 23931
+        df1 = pd.DataFrame(
+            {
+                'date': pd.date_range(start='2018-01-01', periods=5,
+                                      tz='America/Chicago'),
+                'vals': list('abcde')
+            }
+        )
+
+        df2 = pd.DataFrame(
+            {
+                'date': pd.date_range(start='2018-01-03', periods=5,
+                                      tz='America/Chicago'),
+                'vals_2': list('tuvwx')
+            }
+        )
+        result = df1.join(df2.set_index('date'), on='date')
+        expected = df1.copy()
+        expected['vals_2'] = pd.Series([np.nan] * len(expected), dtype=object)
+        assert_frame_equal(result, expected)
+
 
 def _check_join(left, right, result, join_col, how='left',
                 lsuffix='_x', rsuffix='_y'):


Code review comments:
diff_hunk: @@ -181,7 +181,7 @@ Reshaping
 - Bug in :func:`pandas.merge` adds a string of ``None`` if ``None`` is assigned in suffixes instead of remain the column name as-is (:issue:`24782`).
 - Bug in :func:`merge` when merging by index name would sometimes result in an incorrectly numbered index (:issue:`24212`)
 - :func:`to_records` now accepts dtypes to its `column_dtypes` parameter (:issue:`24895`)
--
+- Bug in :func:`DataFrame.join` when joining on a timezone aware :class:`DatetimeIndex` (:issue:`23931`)
path: doc/source/whatsnew/v0.25.0.rst
text: @user1:
this is ok for 0.24.2, can you move