Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
RVhydrA
Level III

Moving minimum (moving window)

Does anyone know how to calculate a moving minimum (or for that matter how to iteratively select X number of lag rows and do anything to them such as calculate a min, max, range, etc. In this specific example i want to look back over say the last 100 rows and get the minimum value, then move to the next row down and do the same again (over and over). 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
vince_faller
Super User

Re: Moving minimum (moving window)

Would something like this work?

 

Names default to here( 1 );
dt = open("$SAMPLE_DATA\Big Class.jmp");

dt << New Column("Min height 5 back", 
	Formula(
		Min( // since we want a minumum 
			:height[// the column we want the minimum of
				// a slice of the current row minus 5 (minimum 1 so it's still on the datatable)
				// and current row
				Min( Row() - 5 )::Row() 
			] 
		)
	)
);
Vince Faller - Predictum

View solution in original post

2 REPLIES 2
Highlighted
kofi
Level III

Re: Moving minimum (moving window)

The macro below isn't particularly elegant, but will do the job. 

 

For ( i= 100, i <= N Rows ( dt ), i++,

 dt << Select Rows (Index (i-99, i));
 
 dt << Subset(
	Selected Rows( 1 ),
	Selected Columns ( 0 ),
	Output Table ("out")
);

Current Data Table ( Data Table ("out"));

x = Col Minimum ( :YourDataColumn );

Close ( Data Table ( "out"), no save);

Column (  "YourMovingWindow" )[i] = x;

)

Sub-setting the selected data, the way I've done it, is slow. Perhaps another user has a better idea how to access the selected data and extract the desired product.

I also wonder how a column formula could look like to achieve the results without a macro.

 

Cheers,

Kofi

Highlighted
vince_faller
Super User

Re: Moving minimum (moving window)

Would something like this work?

 

Names default to here( 1 );
dt = open("$SAMPLE_DATA\Big Class.jmp");

dt << New Column("Min height 5 back", 
	Formula(
		Min( // since we want a minumum 
			:height[// the column we want the minimum of
				// a slice of the current row minus 5 (minimum 1 so it's still on the datatable)
				// and current row
				Min( Row() - 5 )::Row() 
			] 
		)
	)
);
Vince Faller - Predictum

View solution in original post