Repository: pandas-dev/pandas
Pull request: issue #27642 - timedelta merge asof with tolerance
- [X] closes #27642 
- [x] tests 2 / 1 [added / passed] 
- [X] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry
- [ ] \(Optional) allow users to pass datetime.timedelta objects for tolerance

Problem statement:
merge_asof(): cannot use tolerance flag when the index is a TimedeltaIndex
#### Code Sample

```python
import pandas as pd 
import numpy as np

print(
    """
    \nPandas merge_asof() bug:
    
    \tUnimplemented error?
    \tcannot use tolerance flag when my index is a timedelta (not a timestamp)
    \tjust documenting so I can try to add this functionality
        
    """)

print(f"pandas version: {pd.__version__}")
print(f"numpy version: {np.__version__}")

delta_300 = pd.timedelta_range(start='0 minutes', freq='3333334 N', periods=301, name='Time')
delta_120 = pd.timedelta_range(start='0 minutes', freq='8333334 N', periods=301, name='Time')

df_300hz = pd.DataFrame({'my300hz_data': np.arange(301)}, index=delta_300)
df_120hz = pd.DataFrame({'my120hz_data': np.arange(301)}, index=delta_120)

print(df_300hz)
print(df_120hz) 

### this throws error "pandas.errors.MergeError: key must be integer, timestamp or float"
merged = pd.merge_asof(df_120hz, df_300hz, on='Time', direction='nearest', tolerance=pd.Timedelta('15 ms'))

### The line below works, but output is not what I want
# merged = pd.merge_asof(df_120hz, df_300hz, on='Time', direction='nearest')

merged.set_index('Time', inplace=True)
print(merged)
```

#### Problem description

I need to see NaNs when I merge and there is a gap in my data, without begin able to use the tolerance flag my data gets smoothed.

Current work around, convert all my TimedeltaIndex's to a time stamp. Since I do not have a date for this data I am using unix time. This feels bulky since I am going to drop the date when I save the file anyway.

#### Expected Output

[301 rows x 1 columns]
                 my120hz_data  my300hz_data
Time                                       
00:00:00                    0             0
00:00:00.008333             1             2
00:00:00.016666             2             5
00:00:00.025000             3             7
00:00:00.033333             4            10
...                       ...           ...
00:00:02.466666           296           NaN
00:00:02.475000           297           NaN
00:00:02.483333           298           NaN
00:00:02.491666           299           NaN
00:00:02.500000           300           NaN

#### Output of ``pd.show_versions()``

INSTALLED VERSIONS
------------------
commit           : None
python           : 3.7.3.final.0
python-bits      : 64
OS               : Linux
OS-release       : 4.19.0-5-amd64
machine          : x86_64
processor        : 
byteorder        : little
LC_ALL           : None
LANG             : en_US.UTF-8
LOCALE           : en_US.UTF-8

pandas           : 0.25.0
numpy            : 1.17.0
pytz             : 2019.1
dateutil         : 2.8.0
pip              : 18.1
setuptools       : 40.8.0
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : None
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : None
IPython          : None
pandas_datareader: None
bs4              : None
bottleneck       : None
fastparquet      : None
gcsfs            : None
lxml.etree       : None
matplotlib       : None
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : None
pytables         : None
s3fs             : None
scipy            : 1.3.0
sqlalchemy       : None
tables           : None
xarray           : None
xlrd             : None
xlwt             : None
xlsxwriter       : None

Hints from issue discussion:
`merge_asof` should have raised a `NotImplementedError` with `TimedeltaIndex`, but I think it's reasonable to support it for that data type. 
I think adding an elif statement to handle timedeltaIndex then verify tolerance is positive gives the result I desire.

```
            elif is_timedelta64_dtype(lt):
                if not isinstance(self.tolerance, Timedelta):
                    raise MergeError(msg)
                if self.tolerance < Timedelta(0):
                    raise MergeError("tolerance must be positive")
```

line 1665 merge.py

This does give the results I desire, so not unimplemented?
I haven't tried to break it though.

also add "is_timedelta64_dtype" to `from pandas.core.dtypes.common import ()`

or use `is_datetimelike()`  or `is_datetime_or_timedelta_dtype()` for if check?
Still works after I add gaps to my 300Hz data.

```
import pandas as pd 
import numpy as np

print(
    """
    \nPandas merge_asof() bug:
    
    \tUnimplemented error?
    \tcannot use tolerance flag when my index is a timedelta (not a timestamp)
    \tjust documenting so I can try to add this functionality
        
    """)

print(f"pandas version: {pd.show_versions()}")
print(f"numpy version: {np.__version__}")

delta_300 = pd.timedelta_range(start='0 minutes', freq='3333334 N', periods=301, name='Time')
delta_120 = pd.timedelta_range(start='0 minutes', freq='8333334 N', periods=301, name='Time')

df_300hz = pd.DataFrame({'my300hz_data': np.arange(301)}, index=delta_300)
df_120hz = pd.DataFrame({'my120hz_data': np.arange(301)}, index=delta_120)

df_300hz.drop(df_300hz.index[10:110], inplace=True)

print(df_300hz)
print(df_120hz) 

merged = pd.merge_asof(df_120hz, df_300hz, on='Time', direction='nearest', tolerance=pd.Timedelta('5 ms'))

merged.set_index('Time', inplace=True)
print(merged.to_string())
```
Right, you can just add the `is_timedelta64_dtype ` check here:

https://github.com/pandas-dev/pandas/blob/0fd888c8b318d4041b0ae53c64715eca0d345dd3/pandas/core/reshape/merge.py#L1638

PR's welcome!

Commit message: #27642

Patch to review:
diff --git a/pandas/core/reshape/merge.py b/pandas/core/reshape/merge.py
index fc32a8f0dd044..9be1146c0efb1 100644
--- a/pandas/core/reshape/merge.py
+++ b/pandas/core/reshape/merge.py
@@ -35,6 +35,7 @@
     is_number,
     is_numeric_dtype,
     is_object_dtype,
+    is_timedelta64_dtype,
     needs_i8_conversion,
 )
 from pandas.core.dtypes.missing import isnull, na_value_for_dtype
@@ -1635,7 +1636,11 @@ def _get_merge_keys(self):
                 )
             )
 
-            if is_datetime64_dtype(lt) or is_datetime64tz_dtype(lt):
+            if(
+                is_datetime64_dtype(lt)
+                or is_datetime64tz_dtype(lt)
+                or is_timedelta64_dtype(lt)
+            ):
                 if not isinstance(self.tolerance, Timedelta):
                     raise MergeError(msg)
                 if self.tolerance < Timedelta(0):


Code review comments:
diff_hunk: @@ -1635,7 +1636,11 @@ def _get_merge_keys(self):
                 )
             )
 
-            if is_datetime64_dtype(lt) or is_datetime64tz_dtype(lt):
+            if(
path: pandas/core/reshape/merge.py
text: @user1:
This may throw a linter warning (space after the `if`).

@author:
black wants to put a space here