rolling_apply only applicable to numeric columns

[aschilling]

Hi everybody,

I discovered that the rolling_apply function is only applicable to numeric columns. I think this should be changed as this seems too limited to me. Let's take the following example,

    import datetime as DT
    df = pd.DataFrame({
    'Buyer': 'Carl Mark Carl Joe Joe Carl'.split(),
    'Quantity': [1,3,5,8,9,3],
    'Date' : [
        DT.datetime(2013,9,1,13,0),
        DT.datetime(2013,9,1,13,5),
        DT.datetime(2013,10,1,20,0),
        DT.datetime(2013,10,3,10,0),
        DT.datetime(2013,12,2,12,0),                                      
        DT.datetime(2013,12,2,14,0),
        ]}).set_index('Date')

Now I want to count all new customers each 10 days.

buyers = []

def novices(x):
    new = [n for n in x if n not in buyers]

    if (len(new) > 0): buyers.extend(new) 

    return len(new)


pd.rolling_apply(df['Buyer'], 10, novices)

throws an exception "ValueError: could not convert string to float: Carl"

However, although meaningless a call with a numeric column such as:

pd.rolling_apply(df['Quantity'], 2, novices)

works.

[jreback]

this is a bit of work, because need right now the rollinging functions only accept float dtypes, so need a generator for object types (not that hard) in the code generator. then need to dispatch based on dtype. will put on the board for 0.14.

[aschilling]

Thanks, is there maybe an alternative solution how I could implement the functionality with the current pandas version?

[jreback]

There's an embeded bug here (that's why i am astyping)...separate issue

In [28]: df['Quantity'].astype('float').resample('10D',how=np.sum)
Out[28]: 
Date
2013-09-01 13:00:00     4
2013-09-11 13:00:00   NaN
2013-09-21 13:00:00   NaN
2013-10-01 13:00:00    13
2013-10-11 13:00:00   NaN
2013-10-21 13:00:00   NaN
2013-10-31 13:00:00   NaN
2013-11-10 13:00:00   NaN
2013-11-20 13:00:00   NaN
2013-11-30 13:00:00    12
2013-12-10 13:00:00     0
Freq: 10D, dtype: float64

[aschilling]

Thanks, but I think you got me wrong, I am looking for a possibility to count at each point the number of new customers in the upcoming 10 days. Hence, I need to compare the names of the customers to see if they are new or already known.

[jreback]

I think this will work. It is a 'manual' rolling_apply
you can do anything you want the function (e.g. if you can return a Series with multiple values or a DataFrame)
and can efectively 'group' how you want

The key is return None from the function which concat ignores

In [25]: df
Out[25]: 
                    Buyer  Quantity
Date                               
2013-09-01 13:00:00  Carl         1
2013-09-01 13:05:00  Mark         3
2013-10-01 20:00:00  Carl         5
2013-10-03 10:00:00   Joe         8
2013-12-02 12:00:00   Joe         9
2013-12-02 14:00:00  Carl         3

In [24]: dates = date_range(df.index[0],df.index[-1],freq='D')                                          

In [22]: def f(x,d):
   ....:     if len(x):
   ....:         return Series([x['Quantity'].sum()],index=[d])
   ....:     return None
   ....: 

In [23]: concat([ f(df.loc[(df.index>dates[i-10]) & (df.index<=dates[i])], dates[i]) for i in xrange(10,len(dates)) ])
Out[23]: 
2013-09-11 13:00:00     3
2013-10-02 13:00:00     5
2013-10-03 13:00:00    13
2013-10-04 13:00:00    13
2013-10-05 13:00:00    13
2013-10-06 13:00:00    13
2013-10-07 13:00:00    13
2013-10-08 13:00:00    13
2013-10-09 13:00:00    13
2013-10-10 13:00:00    13
2013-10-11 13:00:00    13
2013-10-12 13:00:00     8
2013-12-02 13:00:00     9
dtype: int64

[aschilling]

Thanks, that is a great solution. Is there any possibility to use in your last statement [23] a TimeOffset (e.g. a week or month) to select the rows for the f function while still having the variable 'dates' on a daily basis?

[jreback]

In [24]: offset = pd.offsets.MonthEnd('1')

In [25]: concat([ f(df.loc[(df.index>d-offset) & (df.index<=d)], d) for d in dates ])
Out[25]: 
2013-09-01 13:00:00    1
2013-09-02 13:00:00    4
2013-09-03 13:00:00    4
2013-09-04 13:00:00    4
2013-09-05 13:00:00    4
2013-09-06 13:00:00    4
2013-09-07 13:00:00    4
2013-09-08 13:00:00    4
2013-09-09 13:00:00    4
2013-09-10 13:00:00    4
2013-09-11 13:00:00    4
2013-09-12 13:00:00    4
2013-09-13 13:00:00    4
2013-09-14 13:00:00    4
2013-09-15 13:00:00    4
...
2013-10-18 13:00:00    13
2013-10-19 13:00:00    13
2013-10-20 13:00:00    13
2013-10-21 13:00:00    13
2013-10-22 13:00:00    13
2013-10-23 13:00:00    13
2013-10-24 13:00:00    13
2013-10-25 13:00:00    13
2013-10-26 13:00:00    13
2013-10-27 13:00:00    13
2013-10-28 13:00:00    13
2013-10-29 13:00:00    13
2013-10-30 13:00:00    13
2013-10-31 13:00:00    13
2013-12-02 13:00:00     9
Length: 61