topic Re: find all rows with value>50 and subsequent 10 rows in Discussions

find all rows with value>50 and subsequent 10 rows

emmablue — Wed, 15 May 2024 18:45:44 GMT

I need to write a script to find all rows in the table with value > 50 and return 10 rows right after those rows. Could someone help me with this script please? Thank you!

Re: find all rows with value>50 and subsequent 10 rows

jthi — Wed, 15 May 2024 19:00:48 GMT

Without any knowledge of the data you have here is one option you could start with (it looks for values over 120 not 50)

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

r = As List(dt << Get Rows Where(:weight > 120));

rs = {};

max_rows = N Rows(dt);
For Each({cur_r}, r,
	Insert Into(rs, Index(cur_r + 1, Min(max_rows, cur_r + 10)));
);

show(r);
show(rs);

// r = {2, 4, 7, 18, 30, 37, 39, 40};
// rs = {[2 3 4 5 6 7 8 9 10 11], [4 5 6 7 8 9 10 11 12 13], [7 8 9 10 11 12 13 14 15 16], [18 19 20 21 22 23 24 25 26 27], [30 31 32 33 34 35 36 37 38 39], [37 38 39 40], [39 40], [40]};

Re: find all rows with value>50 and subsequent 10 rows

jthi — Wed, 15 May 2024 19:55:20 GMT

Here are three different options, they might capture incorrect rows due to off-by-one errors

// Option1
Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

// https://leetcode.com/problems/merge-intervals/solutions/3161191/solution/
merge_intervals = function({intervals}, {Default Local},
	// Assumes array is sorted by first index
	stack = {};
	For Each({interval}, intervals,
		If(N Items(stack) == 0 | stack[N Items(stack)][2] < interval[1],
			Insert Into(stack, Eval List({interval}));
		,
			stack[N Items(stack)][2] = max(stack[N Items(stack)][2], interval[2]);
		);
	);
	return(stack);
);

r_start = (dt << Get Rows Where(:weight > 120)) + 1;
r_end = r_start  + 10;

toohigh = Loc(r_start > N Rows(dt));
r_start[toohigh] = N Rows(dt);

toohigh = Loc(r_end > N Rows(dt));
r_end[toohigh] = N Rows(dt);

rs = As List(r_start || r_end);

res = merge_intervals(rs);

// Option2
Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

r_start = dt << Get Rows Where(:weight > 120);
res2 = dt << get rows where(Any(Row() > r_start & Row() <= r_start + 10));

// Option3
Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

r = As List(dt << Get Rows Where(:weight > 120));
rs = {};

max_rows = N Rows(dt);
For Each({cur_r}, r,
	Insert Into(rs, As List(Index(Min(max_rows, cur_r + 1), Min(max_rows, cur_r + 10))));
);

Re: find all rows with value>50 and subsequent 10 rows

emmablue — Wed, 15 May 2024 20:39:01 GMT

Thank you so much! How can I make rs (all rows selected) into a new data table?

Re: find all rows with value>50 and subsequent 10 rows

jthi — Thu, 16 May 2024 04:58:43 GMT

Select message << Select Rows() to data table.

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

r_start = dt << Get Rows Where(:weight > 120);
res2 = dt << get rows where(Any(Row() > r_start & Row() <= r_start + 10));

dt << Select Rows(res2);

Re: find all rows with value>50 and subsequent 10 rows

txnelson — Thu, 16 May 2024 18:01:45 GMT

To make the selected rows into a new data table one just needs to use the Subset Platform.

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

r_start = dt << Get Rows Where(:weight > 120);
res2 = dt << get rows where(Any(Row() > r_start & Row() <= r_start + 10));

dt << Select Rows(res2);

dtSub = dt << subset( selected rows(1), selected columns(0));