h2oai / h2oai/datatable

Rolling aggregate support based on windows within a DT

Open
#1,500 5 comments 3 reactions 0 assignees View on GitHub
cust-goldmansachs new feature
Dominant language
C++
Stars
1.9k
Forks
164
Avg merge
7h 31m
Merged PRs (30d)
1

Description

I'd like to see the ability to get different rolling aggregations of my dataset based on order and grouping columns. Pandas has robust support for these type of actions. https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.rolling.html.
It would also be nice to easily assign these to new columns without having to make nested for loops.
Below is a function I use in Pandas to achieve this functionality
```
def create_roll_columns (x,g_c,roll,roll_cols,roll_types):
#roll types [sum,mean,min]
#g_c needs to be a list
#lag will set the number of lag cols
for i in roll_cols:
for j in range(2,roll+1):
for aggregation in roll_types:
if aggregation == 'sum':
nm = '{0}_sum_{1}'.format(i, str(j))
x[nm] = x.groupby(g_c)[i].rolling(j, min_periods=1).sum().reset_index(0,drop=True)
if aggregation == 'mean':
nm = '{0}_mean_{1}'.format(i, str(j))
x[nm] = x.groupby(g_c)[i].rolling(j, min_periods=1).mean().reset_index(0,drop=True)
if aggregation == 'min':
nm = '{0}_min_{1}'.format(i, str(j))
x[nm] = x.groupby(g_c)[i].rolling(j, min_periods=1).min().reset_index(0,drop=True)
return(x)
```

Example code could be something like - df(select=mean(f.x), group="y",window = 3, align='r')
-group = windowing columns for the select statement
-window = many rows is looks back or forward in a given group
- Align is right, left, center and coordinates where the window happens relative to the given record.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.