This Excel lambda function intends to mimic the key functionality of the pandas window functions via the rolling object
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
pd.rolling.aggregate | |
Calculates a rolling aggregate of a variable where each window is of size window and the function agg is applied to each window | |
Inputs: | |
x: a single-column numerical variable, sorted in the order the user expects to calculate rolling calculations on | |
window: an integer specifying the window length/width. For example, if window is 3, then the aggregate will be applied over the set of 3 rows ending in the current row | |
agg: a text string specifying which aggregate function should be applied over each window | |
Returns: | |
An array with ROWS(x) rows and 1 column containing the result of the aggregation over each window. For the first window-1 rows, the output array will show NA() | |
*/ | |
pd.rolling.aggregate | |
=LAMBDA(x,window,agg, | |
LET( | |
_x,x, | |
_w,window, | |
_agg,agg, | |
_aggs,{"average";"count";"counta";"max";"min" | |
;"product";"stdev.s";"stdev.p";"sum";"var.s" | |
;"var.p";"median";"mode.sngl";"kurt";"skew" | |
;"sem"}, | |
_thk,LAMBDA(x,LAMBDA(x)), | |
_fn_aggs,MAKEARRAY(ROWS(_aggs),1, | |
LAMBDA(r,c, | |
CHOOSE( | |
r, | |
_thk(LAMBDA(x,AVERAGE(x))), | |
_thk(LAMBDA(x,COUNT(x))), | |
_thk(LAMBDA(x,COUNTA(x))), | |
_thk(LAMBDA(x,MAX(x))), | |
_thk(LAMBDA(x,MIN(x))), | |
_thk(LAMBDA(x,PRODUCT(x))), | |
_thk(LAMBDA(x,STDEV.S(x))), | |
_thk(LAMBDA(x,STDEV.P(x))), | |
_thk(LAMBDA(x,SUM(x))), | |
_thk(LAMBDA(x,VAR.S(x))), | |
_thk(LAMBDA(x,VAR.P(x))), | |
_thk(LAMBDA(x,MEDIAN(x))), | |
_thk(LAMBDA(x,MODE.SNGL(x))), | |
_thk(LAMBDA(x,KURT(x))), | |
_thk(LAMBDA(x,SKEW(x))), | |
_thk(LAMBDA(x,STDEV.S(x)/SQRT(_w))) | |
) | |
) | |
), | |
_fn,XLOOKUP(_agg,_aggs,_fn_aggs), | |
_i,SEQUENCE(ROWS(x)), | |
_s,SCAN(0,_i, | |
LAMBDA(a,b, | |
IF( | |
b<_w, | |
NA(), | |
_thk( | |
MAKEARRAY(_w,1, | |
LAMBDA(r,c, | |
INDEX(_x,b-_w+r) | |
) | |
) | |
) | |
) | |
) | |
), | |
_out,SCAN(0,_i,LAMBDA(a,b,_fn()(INDEX(_s,b,1)()))), | |
_out | |
) | |
); | |
/* | |
pd.rolling.aggregates | |
Calculates rolling aggregates of a variable using an arbitrary set of window lengths and aggregate function | |
Inputs: | |
x: a single-column numerical variable, sorted in the order the user expects to calculate rolling calculations on | |
windows: a 1-dimensional array of window lengths. A window is an integer that describes the length, in terms of array elements or spreadsheet rows, of a window over which an aggregate will be calculated. For example, if a window is 3, then the aggregate will be applied over the set of 3 rows ending in the current row. Each element in windows maps to an element in aggs, allowing for either differing window sizes per aggregation, or different window sizes for the same aggregation | |
aggs: a 1-dimensional array of aggregate functions to be applied to each window. Each aggregate maps to an element of windows. Supported aggregates are shown in the _aggs variable in pd.rolling.aggregate. | |
Returns: | |
An array with ROWS(x) rows and MAX(COUNT(windows),COUNTA(aggs)) columns. If COUNT(windows) <> COUNTA(aggs), then the smaller array will be resized to the length of the longer array, with additional elements being filled with the right-most/bottom-most element of the original array. | |
For example: | |
windows = {2,3,4} | |
aggs = {"sum"} | |
aggs will be resized to three elements and each new element will be filled with the right-most element of the existing array, so: | |
{"sum","sum","sum"} | |
The net result is that the output array will have three columns, showing respectively rolling 2 sum, rolling 3 sum and rolling 4 sum of x | |
*/ | |
pd.rolling.aggregates =LAMBDA(x,windows,aggs, | |
LET( | |
_tr,LAMBDA(arr,LET(x,FILTER(arr,arr<>""),IF(ROWS(x)=1,TRANSPOSE(x),x))), | |
_a,_tr(aggs), | |
_w,_tr(windows), | |
_resize,ROWS(_a)<>ROWS(_w), | |
_rs,LAMBDA(arr,resize_to,MAKEARRAY(resize_to,1,LAMBDA(r,c,IF(r<=ROWS(arr),INDEX(arr,r,1),INDEX(arr,ROWS(arr),1))))), | |
_ms,MAX(ROWS(_a),ROWS(_w)), | |
_ar,IF(_resize,_rs(_a,_ms),_a), | |
_wr,IF(_resize,_rs(_w,_ms),_w), | |
_out, | |
MAKEARRAY( | |
ROWS(x), | |
_ms, | |
LAMBDA(r,c, | |
INDEX(pd.rolling.aggregate(x,INDEX(_wr,c,1),INDEX(_ar,c,1)),r,1) | |
) | |
), | |
_out | |
) | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment