AmbiguousTimeError merging two timezone-aware DataFrames with DST change

[tapia]

When merging two DataFrames by a timezone-aware datetime column, if the datetime values doesn't include a DST change, there's no problem:

df1 = pd.DataFrame([pd.to_datetime('2017-10-30 02:00:00+01:00'),
                    pd.to_datetime('2017-10-30 03:00:00+01:00'),
                    pd.to_datetime('2017-10-30 04:00:00+01:00')],columns=['date'])
df1['date'] = df1['date'].dt.tz_localize('UTC').dt.tz_convert('Europe/Madrid')
df1['value'] = 1

df2 = pd.DataFrame([pd.to_datetime('2017-10-30 04:00:00+01:00'),
                    pd.to_datetime('2017-10-30 05:00:00+01:00'),
                    pd.to_datetime('2017-10-30 06:00:00+01:00')],columns=['date'])
df2['date'] = df2['date'].dt.tz_localize('UTC').dt.tz_convert('Europe/Madrid')
df2['value'] = 2

pd.merge(df1, df2, how='outer', on='date')

Output

                       date  value_x  value_y
0 2017-10-30 02:00:00+01:00      1.0      NaN
1 2017-10-30 03:00:00+01:00      1.0      NaN
2 2017-10-30 04:00:00+01:00      1.0      2.0
3 2017-10-30 05:00:00+01:00      NaN      2.0
4 2017-10-30 06:00:00+01:00      NaN      2.0

This is correct. But if the datetime values include a date with DST change, we get an AmbiguousTimeError exception:

df1 = pd.DataFrame([pd.to_datetime('2017-10-29 02:00:00+02:00'),
                    pd.to_datetime('2017-10-29 02:00:00+01:00'),
                    pd.to_datetime('2017-10-29 03:00:00+01:00')],columns=['date'])
df1['date'] = df1['date'].dt.tz_localize('UTC').dt.tz_convert('Europe/Madrid')
df1['value'] = 1

df2 = pd.DataFrame([pd.to_datetime('2017-10-29 03:00:00+01:00'),
                    pd.to_datetime('2017-10-29 04:00:00+01:00'),
                    pd.to_datetime('2017-10-29 05:00:00+01:00')],columns=['date'])
df2['date'] = df2['date'].dt.tz_localize('UTC').dt.tz_convert('Europe/Madrid')
df2['value'] = 2

pd.merge(df1, df2, how='outer', on='date')

Expected output

                       date  value_x   value_y
0 2017-10-29 02:00:00+02:00      1.0       NaN
1 2017-10-29 02:00:00+01:00      1.0       NaN
2 2017-10-29 03:00:00+01:00      1.0       2.0
3 2017-10-29 04:00:00+01:00      NaN       2.0
4 2017-10-29 05:00:00+01:00      NaN       2.0

Actual output

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "(...)/venv/lib/python3.6/site-packages/pandas/core/reshape/merge.py", line 58, in merge
    return op.get_result()
  File "(...)//venv/lib/python3.6/site-packages/pandas/core/reshape/merge.py", line 604, in get_result
    self._maybe_add_join_keys(result, left_indexer, right_indexer)
  File "(...)//venv/lib/python3.6/site-packages/pandas/core/reshape/merge.py", line 715, in _maybe_add_join_keys
    key_col = Index(lvals).where(~mask, rvals)
  File "(...)//venv/lib/python3.6/site-packages/pandas/core/indexes/datetimelike.py", line 809, in where
    result = self._ensure_localized(result)
  File "(...)//venv/lib/python3.6/site-packages/pandas/core/indexes/datetimelike.py", line 230, in _ensure_localized
    result = result.tz_localize(self.tz)
  File "(...)//venv/lib/python3.6/site-packages/pandas/util/_decorators.py", line 118, in wrapper
    return func(*args, **kwargs)
  File "(...)//venv/lib/python3.6/site-packages/pandas/core/indexes/datetimes.py", line 1858, in tz_localize
    errors=errors)
  File "pandas/_libs/tslib.pyx", line 3593, in pandas._libs.tslib.tz_localize_to_utc
pytz.exceptions.AmbiguousTimeError: Cannot infer dst time from Timestamp('2017-10-29 02:00:00'), try using the 'ambiguous' argument

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.2.final.0
python-bits: 64
OS: Darwin
OS-release: 17.3.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: es_ES.UTF-8
LOCALE: es_ES.UTF-8

pandas: 0.21.1
pytest: 3.2.5
pip: 9.0.1
setuptools: 36.8.0
Cython: None
numpy: 1.13.3
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: 1.5.3
patsy: None
dateutil: 2.6.1
pytz: 2017.3
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: 0.9.6
lxml: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.9.6
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[jreback]

yeah, we are joining indicies and do a .where() on them. we drop the tz, do the op in i8, then localize to the original zone.

what we need is an attribute for Timestamp and DatetimeIndex like is_ambiguous, then we could record the ambiguous so we can recreate properly.

interested in a PR?

cc @jbrockmendel

[tapia]

I'd love to, but I don't know the pandas/numpy internals, and merge() doesn't sound like an easy place to start :-) Maybe with some guidance...