BUG: Raise ValueError for non numerical join columns in merge_asof
State: closed

- [x] closes #29130
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

I checked for dtype object. Normally, I would try to check for allowed dtypes, but that would require a lot of dtype checks instead of only one. I hope object dtype is sufficient to exclude

Labels:
name: Reshaping
description: Concat, Merge/Join, Stack/Unstack, Explode
name: Error Reporting
description: Incorrect or improved errors from pandas

Comments:
Hello @phofl! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:





There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2021-01-05 18:33:30 UTC
@jreback 

I think I found a solution. I moved it as you requested to the overwritten _get_merge_keys function. We can only check the on columns, not the by columns for dtype object. So I take the length of the by columns and check all following columns. The check for equal dtypes is performed a few lines before that.
@phofl Can you merge master to fix conflicts?
@dsaxton done
This pull request is stale because it has been open for thirty days with no activity. Please update or respond to this comment if you're still interested in working on this.
@phofl can you merge master and move the note to 1.3
Done
thanks @phofl 
Hello, thought I'd add here a bug(?) that happened with the above change that was just released with pandas 1.3.0. Seems the "GH#29130 Check that merge keys do not have dtype object" makes an assumption that a merge key is not in the index, so the "self.right[self.right_on[0]].dtype" fails when self.right_on[0] is not a column of the dataframe (and equivalent for the left_on side). Perhaps those fields should never be in the index and this just exposes that?
Could you provide a short example please?
Sure, here is an example:

```
data1 = pd.DataFrame({
            'name': ['mary', 'randy'],
            'age': [33, 23],
            'city': ['nyc', 'chi'],
            'date': [pd.to_datetime('20210303'), pd.to_datetime('20210303')],
        })

data2 = pd.DataFrame({
            'name': ['pete', 'mary'],
            'age': [22, 44],
            'city': ['nyc', 'phi'],
            'date': [pd.to_datetime('20210303'), pd.to_datetime('20210505')],
        })

data2= data2.set_index('date')

together = pd.merge_asof(data1.sort_values('date'), data2.sort_values('date'), left_on='date', right_on='date', left_by='city', right_by='city', tolerance=pd.Timedelta('2D'))
```

`~/anaconda3/envs/dailytestenv/lib/python3.8/site-packages/pandas/core/reshape/merge.py in merge_asof(left, right, on, left_on, right_on, left_index, right_index, by, left_by, right_by, suffixes, tolerance, allow_exact_matches, direction)
    579     4 2016-05-25 13:30:00.048   AAPL   98.00       100     NaN     NaN
    580     """
--> 581     op = _AsOfMerge(
    582         left,
    583         right,

~/anaconda3/envs/dailytestenv/lib/python3.8/site-packages/pandas/core/reshape/merge.py in __init__(self, left, right, on, left_on, right_on, left_index, right_index, by, left_by, right_by, axis, suffixes, copy, fill_method, how, tolerance, allow_exact_matches, direction)
   1734         self.direction = direction
   1735
-> 1736         _OrderedMerge.__init__(
   1737             self,
   1738             left,

~/anaconda3/envs/dailytestenv/lib/python3.8/site-packages/pandas/core/reshape/merge.py in __init__(self, left, right, on, left_on, right_on, left_index, right_index, axis, suffixes, copy, fill_method, how)
   1617
   1618         self.fill_method = fill_method
-> 1619         _MergeOperation.__init__(
   1620             self,
   1621             left,

~/anaconda3/envs/dailytestenv/lib/python3.8/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)
    680             warnings.warn(msg, FutureWarning, stacklevel=3)
    681
--> 682         self._validate_specification()
    683
    684         cross_col = None

~/anaconda3/envs/dailytestenv/lib/python3.8/site-packages/pandas/core/reshape/merge.py in _validate_specification(self)
   1782         )
   1783         ro_dtype = (
-> 1784             self.right[self.right_on[0]].dtype
   1785             if not self.right_index
   1786             else self.right.index.dtype

~/anaconda3/envs/dailytestenv/lib/python3.8/site-packages/pandas/core/frame.py in __getitem__(self, key)
   3453             if self.columns.nlevels > 1:
   3454                 return self._getitem_multilevel(key)
-> 3455             indexer = self.columns.get_loc(key)
   3456             if is_integer(indexer):
   3457                 indexer = [indexer]

~/anaconda3/envs/dailytestenv/lib/python3.8/site-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
   3361                 return self._engine.get_loc(casted_key)
   3362             except KeyError as err:
-> 3363                 raise KeyError(key) from err
   3364
   3365         if is_scalar(key) and isna(key) and not self.hasnans:

KeyError: 'date'
`

I have fixed this already, will be in 1.3.1
lol, wow, that was a fast reply, thank you!