How would I combine the functionality of a rolling and a variable window?

[skwasha]

I have a set of data (a list of orders received throughout the day). Each of the orders is timestamped but they are not evenly spaced out. E.g., there might be two in 10 minutes and then none for another 30 min, etc. I need to add a column to each order/row that is a sum of another column from the previous orders within the last X hours.

I can't use a normal rolling window (unless I'm missing something) because the period in this case is time based and not a simple count. As I understand it, a variable window would simply split my data into distinct windows and I wouldn't get the "rolling" aspect.

Any help appreciated.

[ashleydavis]

Rolling window isn't based on a time period it's based on a number of records.

See in this example from the guide:

function computePctChange (window) {
    const amountChange = window.last() - window.first(); // Compute amount of change.
    const pctChange = (amountChange / window.first()) * 100; // Compute % change.
    return pctChange;
}

const pctChangeSeries = series.rollingWindow(2).select(computePctChange);

The 2 that's passed in here is only specifies the number of elements to include in each window, it doesn't mean 2 seconds, 2 hours, 2 days or anything like that.

[ashleydavis]

Variable windows are close to rolling windows but the size of each window is computed by the predicate function, when it returns true for two particular items that will create a new window.

[ashleydavis]

If you can share example code and data with me I might be able to help you get the result you want.

[skwasha]

Hi,
Thanks for weighing in. I understand that the rolling window is based on a set number of elements. That's exactly my problem. I don't want it based on a number of elements, rather a predicate (like a variable window). I don't have any code but here's an example of the sort of data to hopefully clarify the situation.

Date  | data| desired new col
--    |  -- | --
10:01 |  1  | 1
10:02 |  1  | 2
10:11 |  1  | 3
10:22 |  1  | 4
11:04 |  1  | 3
11:43 |  1  | 2
12:13 |  1  | 2
12:32 |  1  | 3
13:04 |  1  | 3
13:22 |  1  | 3
13:31 |  1  | 4
13:50 |  1  | 4
14:21 |  1  | 4
14:47 |  1  | 3
15:03 |  1  | 3
16:03 |  1  | 2
16:29 |  1  | 2

So, in the new col I'm running a sum of the data for the past hour. Hopefully this helps?

Thanks again for any help.

[ashleydavis]

Yep got it.

What code do you have so far?

[skwasha]

I tried a variable window like so:

const totalsdf = alldf.variableWindow((a, b) => {
	return Math.abs(a.updatedAt - b.updatedAt) <= 46400;
});

note 1: updatedAt is in epoch seconds
note 2: I was playing with the time frames to verify it was just creating distinct windows instead of sliding style windows

[ashleydavis]

Are you able to prep some working code for me to start from? Maybe in a git repo that includes the data? Happy to help just don't have much time to do the setup work.

[skwasha]

I'll try and upload some sample data/code tonite.

thx!

[ashleydavis]

I have an idea anyway on how to solve it... but I need play with the code to see if it's going to work ;)

[skwasha]

Here ya go. Hoping this is what you wanted.
FWIW the actual window size (in time) doesn't matter much at this point. I will be using hours in some cases and days in others. I just wanna figure out how to do it first.

[ashleydavis]

Thanks, looks good. I'll try find time tomorrow to figure it out.

[ashleydavis]

Hey, which is it the "premium" field in the data set that you are trying to sum up per hour?

[skwasha]

There's a column called 'premium'... are you not seeing it in the data? It's not a big deal. Feel free to use the 'costBasis' column instead. Again, at this point for me it's less about the particular column than it is about figuring out the mechanics of being able to do that sort of windowing/aggregation.

[ashleydavis]

Ok cool, it's there. Just wanted to check that's the one!

[ashleydavis]

Hi @skwasha, checkout what I've done with your code here:

https://github.com/ashleydavis/data-forge-windows

I used groupBy to group by hour and do the sum you wanted.

Let me know how it looks.

[skwasha]

Hi @ashleydavis thanks so much for this... This is close but as I see it, you're aggregating by the static hour right (i.e., everything between 10 and 11, but not everything between 10:30 and 11:30 as well)? I still need the windowed result for each row - back to the sliding window issue. Maybe I'm missing something?