Skip to content

Instantly share code, notes, and snippets.

@ncalm
Last active June 15, 2023 09:42
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save ncalm/f47401e085128e6eec00beed27044e12 to your computer and use it in GitHub Desktop.
Save ncalm/f47401e085128e6eec00beed27044e12 to your computer and use it in GitHub Desktop.
This Excel lambda function intends to mimic the key functionality of the pandas window functions via the rolling object
/*
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