Expand type specializations and multiple "by" parameters in merge_asof()

[chrisaycock]

pd.merge_asof() can take an integer or floating-point number in the on parameter, and it can take an integer or an object in the by parameter. Specifically, the user's types are promoted to int64_t, double, or object as needed. That means, for example, that an int32_t is permitted, but we'll have to create a copy of the user's column to promote to int64_t.

This brings a question of whether we should add type specializations for every integer and floating-point number for better performance.

A second issue to consider is that only one column is permitted in the by parameter. But the user may wish to match on both ticker symbol and stock exchange, for example. To allow for this, the implementation logic would need to allow arrays of objects/integers for our grouping.

[jreback]

can you show some specific examples which fail and/or you want to work (in the top section). This will provide some examples / tests cases.

[chrisaycock]

Performance

Using merge_asof_noby from the join_merge.py benchmark:

k = merge_asof_noby()
k.setup()

Runtime of the 64-bit case:

In [4]: %timeit k.time_merge_asof_noby()
100 loops, best of 3: 12.4 ms per loop

Now cast to 32-bit:

k.df1.time = np.int32(k.df1.time)
k.df2.time = np.int32(k.df2.time)

And re-run it:

In [7]: %timeit k.time_merge_asof_noby()
100 loops, best of 3: 18.6 ms per loop

The added overhead comes from converting the time column back to 64-bit.

---

Multi-By

Let's say I have these DataFrames:

trades = pd.DataFrame({
    'time': pd.to_datetime(['20160525 13:30:00.023',
                            '20160525 13:30:00.023',
                            '20160525 13:30:00.046',
                            '20160525 13:30:00.048',
                            '20160525 13:30:00.050']),
    'ticker': ['MSFT', 'MSFT',
               'GOOG', 'GOOG', 'AAPL'],
    'exch': ['ARCA', 'NSDQ', 'NSDQ', 'BATS', 'NSDQ'],
    'price': [51.95, 51.95,
              720.77, 720.92, 98.00],
    'quantity': [75, 155,
                 100, 100, 100]},
    columns=['time', 'ticker', 'exch',
             'price', 'quantity'])

quotes = pd.DataFrame({
    'time': pd.to_datetime(['20160525 13:30:00.023',
                            '20160525 13:30:00.023',
                            '20160525 13:30:00.030',
                            '20160525 13:30:00.041',
                            '20160525 13:30:00.045',
                            '20160525 13:30:00.049']),
    'ticker': ['GOOG', 'MSFT', 'MSFT',
               'MSFT', 'GOOG', 'AAPL'],
    'exch': ['BATS', 'NSDQ', 'ARCA', 'ARCA',
             'NSDQ', 'ARCA'],
    'bid': [720.51, 51.95, 51.97, 51.99,
            720.50, 97.99],
    'ask': [720.92, 51.96, 51.98, 52.00,
            720.93, 98.01]},
    columns=['time', 'ticker', 'exch', 'bid', 'ask'])

I want to join by both the ticker symbol and the stock exchange:

In [80]: trades
Out[80]: 
                     time ticker  exch   price  quantity
0 2016-05-25 13:30:00.023   MSFT  ARCA   51.95        75
1 2016-05-25 13:30:00.023   MSFT  NSDQ   51.95       155
2 2016-05-25 13:30:00.046   GOOG  NSDQ  720.77       100
3 2016-05-25 13:30:00.048   GOOG  BATS  720.92       100
4 2016-05-25 13:30:00.050   AAPL  NSDQ   98.00       100

In [81]: quotes
Out[81]: 
                     time ticker  exch     bid     ask
0 2016-05-25 13:30:00.023   GOOG  BATS  720.51  720.92
1 2016-05-25 13:30:00.023   MSFT  NSDQ   51.95   51.96
2 2016-05-25 13:30:00.030   MSFT  ARCA   51.97   51.98
3 2016-05-25 13:30:00.041   MSFT  ARCA   51.99   52.00
4 2016-05-25 13:30:00.045   GOOG  NSDQ  720.50  720.93
5 2016-05-25 13:30:00.049   AAPL  ARCA   97.99   98.01

In [82]: pd.merge_asof(trades, quotes, on='time', by=['ticker', 'exch'])
Out[82]: 
                     time ticker  exch   price  quantity     bid     ask
0 2016-05-25 13:30:00.023   MSFT  ARCA   51.95        75     NaN     NaN
1 2016-05-25 13:30:00.023   MSFT  NSDQ   51.95       155   51.95   51.96
2 2016-05-25 13:30:00.046   GOOG  NSDQ  720.77       100  720.50  720.93
3 2016-05-25 13:30:00.048   GOOG  BATS  720.92       100  720.51  720.92
4 2016-05-25 13:30:00.050   AAPL  NSDQ   98.00       100     NaN     NaN

I.e., the expected result is:

expected = pd.DataFrame({
    'time': pd.to_datetime(['20160525 13:30:00.023',
                            '20160525 13:30:00.023',
                            '20160525 13:30:00.046',
                            '20160525 13:30:00.048',
                            '20160525 13:30:00.050']),
    'ticker': ['MSFT', 'MSFT',
               'GOOG', 'GOOG', 'AAPL'],
    'exch': ['ARCA', 'NSDQ', 'NSDQ', 'BATS', 'NSDQ'],
    'price': [51.95, 51.95,
              720.77, 720.92, 98.00],
    'quantity': [75, 155,
                 100, 100, 100],
    'bid': [np.nan, 51.95, 720.50, 720.51, np.nan],
    'ask': [np.nan, 51.96, 720.93, 720.92, np.nan]},
    columns=['time', 'ticker', 'exch',
             'price', 'quantity', 'bid', 'ask'])

For the by parameter to take an array of column names, the hashing solution must be able to handle more than one object.

---

These two requests are of low priority right now, so I personally won't be able to get to them right away.

[jreback]

does the 'by' issue work? (and just is somewhat slower)?

[chrisaycock]

@jreback No, the by parameter is limited to a scalar right now. Can the PyObjectHashTable take tuples?

[jreback]

yes it can take tuples. normally that is not a good idea, but might work here.

mainly I would like to raise if there is something that is non-sensical (ATM). If it works but is slow that is fine as well.

[chrisaycock]

It raises an error now, yes.

In [2]: pd.merge_asof(trades, quotes, on='time', by=['ticker', 'exch'])
...

MergeError: can only asof by a single key

[jreback]

@chrisaycock perfect. Then good to go for now.