BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Highlighted
jojmp

Not applicable

Joined:

Jan 3, 2019

How to calculate moving average of column with 10 million entries ?

I am trying to run the below formula on a column with 10 million rows to calculate the moving average , but the script keeps running forever and jmp stops responding: thisTable << New Column( "av", Numeric, Continuous, Formula( Mean( :x[Index( Row() - 99999, Row() )] ) ) ); Is there any limitation or something wrong with the formula. How can I calculate the moving average in this case?

3 REPLIES 3
txnelson

Super User

Joined:

Jun 22, 2012

Re: How to calculate moving average of column with 10 million entries ?

Try this and see if it runs faster

Col Moving Average( :x, 1, 999999, 0 )

Look into the Scripting Index for documentation and example

Or, you might try this, which eliminates the requirement to calculate the mean for a list of 100000 rows for each row's calculation, and cut it down to a single subtraction, a single addition, and one division.

If( Row() == 1,
	theLag = 100000;
	theSum = Sum( :x[Index( 1, theLag )] );
);
If(
	Row() < theLag, theAvg = Col Moving Average( :x, 1, theLag, 0 ),
	Row() == theLag, theAvg = theSum / theLag,
	theAvg = ((theSum - :x[Row() - theLag]) + :x) / theLag
);
theAvg;
Jim
jojmp

Not applicable

Joined:

Jan 3, 2019

Re: How to calculate moving average of column with 10 million entries ?

this script does not work on JMP12 (Col Moving Average not supported on JMP12)
txnelson

Super User

Joined:

Jun 22, 2012

Re: How to calculate moving average of column with 10 million entries ?

The Col Moving Average() function can easily be taken out of the formula.  Taking the approach provided in the second formula in my previous post, the calculation of the moving average by summing the data and then dividing, will give you what you want.  Please make sure you understand how the code that is provided to you works.  Here is a new modification of the formula:

If( Row() == 1,
	theLag = 100000;
	theSum = 0;
);
If(
	Row() <= theLag, 
	theSum = theSum + :x;
	theAvg = theSum/Row(),
	theAvg = ((theSum - :x[Row() - theLag]) + :x) / theLag
);
theAvg;
Jim