DataFrame.asof

[changhiskhan]

like Series.asof, should also take skipna={none, 'any', 'all'}

[chrisaycock]

Any thoughts on this? Right now DataFrame.asof() can be emulated via

df.apply(lambda x: x.asof(some_series))

But this is wasteful because it searches each column redundantly.

[jreback]

this is pretty straightforward to do for a frame, (its very similar to the series logic), in fact I would move to core/generic.py (with a small adjustment). PR?

[chrisaycock]

@jreback I can take a stab at it this weekend if you like.

[jreback]

that would be awesome!

[chrisaycock]

@jreback The biggest issue I see is that take_1d() is for arrays rather than DataFrames. The Series.asof() invokes take_1d() to handle indices from asof_locs() that might be -1 (indicating that np.searchsorted() failed to find an appropriate location). Is there an equivalent to take_1d() for DataFrames? I tried calling it in df.apply(), but got an error about shape.

df = pd.DataFrame({'letter':['a', 'b', 'c'], 'number':[1, 2, 3]})
df.apply(lambda x: pd.core.common.take_1d(x.values, [-1, 1]))

Here is a quick-and-dirty hack that produces what I'm looking for:

In [20]: pd.DataFrame(dict(zip(df.columns, map(lambda x: pd.core.common.take_1d(df[x].values, [-1, 1]), df.columns))))
Out[20]: 
  letter  number
0    NaN     NaN
1      b       2

[jreback]

you can just use DAtaFrame.take

[chrisaycock]

@jreback That just treats the -1 the same way regular Python does: wrapping around the end of the array:

In [4]: df.take([-1, 1])    # doesn't matter what I set 'convert' to
Out[4]: 
  letter  number
2      c       3
1      b       2

I'm looking for a way to return a nan when the index is -1, just like in take_1d(). That's how the Series.asof() works to resolve instances when an element in the where parameter occurs before anything in the Series.

[jreback]

you need to look at the internal functions for this, eg take_2d

or you can 1s take with a flattened array then reshape

[bwillers]

I've been toying with this a little and it's not clear that it's possible to avoid doing the asof_locs call for each column, since they might have missings in different positions. So you probably dont end up gaining much over what the df.apply(lambda s: s.asof(t)) approach gives you.

However, if you don't want missings to be ffilled, then you only need to do it once. Perhaps it makes sense to have a flag argument which indicates whether the user wants missings to be ffilled during the asof operation?

[bwillers]

@shoyer As discussed in #10266 this functionality is already available via df.reindex(where, method='ffill'), potentially with a dropna/fillna depending exactly what you want out at the end of the day. So we can probably close this this issue?

[shoyer]

Yep, let's close it.