Cython-optimized expanding apply transformations

[bschreck]

I was generating lots of features for time-dependent data, and ended up writing a lot of expanding apply operations in Cython. Would the community want something like this? Imagine you have a dataframe with an "entity" column, a "time" column and some numeric "feature" column, and you want to calculate the expanding sum/mean/mode/etc. of the feature column for each entity.

This is currently not optimized well in Pandas especially for computing the mode for categorical variables where keeping track of state saves a lot of time.

Example of a use case:

import cython_opt # cython functions are defined here

# df like {"project_id": [1,1,1,1,2,2,2,2], "value": [3,4,5,6, 10,11,12,13]}
df.groupby(level='project_id')['value'].transform(lambda x: cython_opt.expanding_mean(x.values))
# output like {"project_id": [1,1,1,1,2,2,2,2], "value": [3, 3.5, 4, 4.5, 10, 10.5, 11, 11.5]}

I can provide lots more examples and functions (I wrote around 20 of these)

[jreback]

You know about these right? https://github.com/pydata/pandas/blob/master/pandas/algos.pyx#L941

of a specific expanding apply for specific functions can be done marginally (so of course could be faster).

Do you have some timings?

[bschreck]

Those are for rolling functions as opposed to expanding from the beginning of the list. I also have other ones that aren't implemented there like mode and total/percentage of each value for categorical variables. I'll check the timings for specific cases and post them here

[jreback]

expanding == rolling when window=len(data)

[jreback]

certainly up for adding additional ones. mode is quite tricky (we do have an impl of that one), but it will in general not return a scalar but a list-like, so tricky to figure out what you want.

[jreback]

This uses the new interface in 0.18.0

In [1]: s = Series(range(5))

In [2]: s.rolling(window=1).sum()
Out[2]: 
0    0.0
1    1.0
2    2.0
3    3.0
4    4.0
dtype: float64

In [3]: s.expanding().sum()
Out[3]: 
0     0.0
1     1.0
2     3.0
3     6.0
4    10.0
dtype: float64

In [4]: s.rolling(window=len(s)).sum()
Out[4]: 
0     NaN
1     NaN
2     NaN
3     NaN
4    10.0
dtype: float64

In [5]: s.rolling(window=len(s), min_periods=1).sum()
Out[5]: 
0     0.0
1     1.0
2     3.0
3     6.0
4    10.0
dtype: float64

There is a correction for the min_periods (e. window is actually window+min_periods), but the code is the same otherwise.

[bschreck]

I know you have a mode for a Series/DataFrame (http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.mode.html) but I can't find a rolling or expanding version anywhere. I see what you mean about it being more complicated because you might want each step to return an ordered list of all values seen in the window, but in many cases I think you'd just want the actual mode right? i.e. the most common value seen so far in the window.

In [5]: num_list = np.random.randint(4, size=10)

In [6]: num_list
Out[6]: array([0, 3, 1, 3, 1, 0, 3, 3, 1, 0])
In [7]: copt.expanding_mode(num_list)
Out[7]: array([0, 0, 0, 3, 3, 3, 3, 3, 3, 3])

I have some others for categorical values that may or may not be too specific to my use case or not be a real statistical value that people care about. But I have a total and percentage of each value so far, which in the Cython returns lists of lists and a mapping array for aligning indices with the values they refer to.

In [8]: copt.expanding_total_and_percentage(num_list)
Out[8]:
(array([[1, 1, 1, 1, 1, 2, 2, 2, 2, 3],
    [0, 1, 1, 2, 2, 2, 3, 4, 4, 4],
    [0, 0, 1, 1, 2, 2, 2, 2, 3, 3]]),
 array([[ 1.        ,  0.5       ,  0.33333333,  0.25      ,  0.2       ,
      0.33333333,  0.28571429,  0.25      ,  0.22222222,  0.3       ],
    [ 0.        ,  0.5       ,  0.33333333,  0.5       ,  0.4       ,
      0.33333333,  0.42857143,  0.5       ,  0.44444444,  0.4       ],
    [ 0.        ,  0.        ,  0.33333333,  0.25      ,  0.4       ,
      0.33333333,  0.28571429,  0.25      ,  0.33333333,  0.3       ]]),
 array([0, 3, 1]))

And I have these two that I call "jitter" and "stability" which probably aren't very rigorously statistical but I thought were useful. Jitter measures the number of times the value has changed divided by the total number of values seen so far. Stability measures the number of times the most common value has been seen divided by the total number of values so far.

In [9]: copt.expanding_jitter(num_lists)
Out[9]:
array([ 0.        ,  0.        ,  0.        ,  0.25      ,  0.2       ,
    0.16666667,  0.14285714,  0.125     ,  0.11111111,  0.1       ])

In [10]: copt.expanding_stability(num_lists)
Out[10]:
array([ 1.        ,  1.        ,  1.        ,  0.75      ,  0.6       ,
    0.66666667,  0.71428571,  0.75      ,  0.77777778,  0.8       ])

[mroeschke]

Looks like this never took off within pandas, and with numba integrated in pandas it might be looking to investigate in shoring up that functionality so closing for now