How to find the average of samples at random intervals?

[#1]

I have a csv file that stores temp values. I have to find the group average for certain set of samples according to below steps:

1. Make all temp values les than 25 equal to zero i.e.column post_process

2. group_avg column is derived by ignoring rows with greater than or equal to 4 consecutive zeroes. Followed by calculating the average of the group of samples as shown below

3. final_average column is the results generated from group_avg column

How can we script this process using jsl?

Attaching the csv file

Final result is final_average as in the table below:

[#2]

Here is a script that creates your data table with just the TEMP column, and then the remaining columns are created and formula are used to populate them.

Names Default To Here( 1 );
dt = New Table( "temp_temp",
	Add Rows( 54 ),
	New Column( "TEMP",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[23.23019577, 23.44946668, 23.56111955, 23.47601143, 23.36428639,
			23.24016499, 32.42091411, 23, 32.26821731, 32.14990084, 32.42091411,
			32.26821731, 32.14990084, 23.23019577, 23.44946668, 23.56111955,
			23.47601143, 23.36428639, 23.24016499, 32.15353181, 32.16785998, 21,
			32.502366, 32.16261133, 32.15757688, 32.16790474, 23.23019577,
			23.44946668, 23.56111955, 23.47601143, 23.36428639, 23.24016499,
			32.42091411, 10, 32.26821731, 32.14990084, 32.41334166, 32.17456755,
			32.52383338, 32.17310331, 19.28571429, 19.28571429, 19.28571429,
			19.28571429, 34.62948075, 34.28766971, 34.29470736, 34.61532486,
			34.386807, 34.36554216, 34.58410721, 34.28726312, 23.57142857,
			23.57142857]
		)
	)
);

dt << New Column( "post process", formula( If( :TEMP <= 25, 0, :TEMP ) ) );
dt:post process << delete formula;

dt << New Column( "group_avg",
	formula(
		value = .;
		If( Row() == 1,
			flag = 0;
			zeroCounter = 0;
		);
		If( flag == 0 & :post process > 0,
			flag = 1;
			captureMatrix = [];
			Show( "start", Row(), flag );
		);
		If( :post process == 0 & flag == 1,
			zeroCounter
			++);
		If( flag == 1,
			captureMatrix = captureMatrix || Matrix( :post process );
			Show( "cap", Row(), captureMatrix );
			If( zeroCounter == 4 | Row() == N Rows( dt ),
				For( i = N Cols( captureMatrix ), i >= 1, i--,
					If( captureMatrix[i] != 0,
						Break(),
						captureMatrix[i] = .
					)
				);
				value = Mean( captureMatrix );
				Show( value );
				Show( Row(), captureMatrix, zeroCounter, Index( 1, N Cols( captureMatrix ) - (zeroCounter - 1) ) );
				flag = 0;
				zeroCounter = 0;
				Show( flag );
				captureMatrix = [];
			);
		);
		value;
	)
);

dt << new column("final Avg",
	formula(
		if(row()==1,
			theRows = current data table() << get rows where(isMissing(:group_avg)==0);
		);
		If(row()<= nrows(theRows),
			value=:group_avg[theRows[Row()]],
			value=.
		)
	)
);

[#3]

This scripts works fine for small files. But in case of large files where we have 16000 zeroes instead of 4 JMP stops responding. Is there any way to prevent this and run the script without hang?

[#4 · Accepted solution]

I suspect the issue is that by mistake, I left several "Show()" statements in the code, which forced a lot of writing to the JMP log.

Below is the "post process" column specification, which is the part that I assume is having the issue that is making JMP hang.  Try running it and see if it will run without hanging

dt << New Column( "group_avg",
	formula(
		value = .;
		If( Row() == 1,
			flag = 0;
			zeroCounter = 0;
		);
		If( flag == 0 & :post process > 0,
			flag = 1;
			captureMatrix = [];
		);
		If( :post process == 0 & flag == 1,
			zeroCounter
			++);
		If( flag == 1,
			captureMatrix = captureMatrix || Matrix( :post process );
			If( zeroCounter == 4 | Row() == N Rows( dt ),
				/*For( i = N Cols( captureMatrix ), i >= 1, i--,
					If( captureMatrix[i] != 0,
						Break(),
						captureMatrix[i] = .
					)
				);*/
				if( Row() == N Rows( dt ), zerocounter++ );
				value = Mean( captureMatrix[ 1:: ncols(captureMatrix) - (zeroCounter - 1)] );
				flag = 0;
				zeroCounter = 0;
				captureMatrix = [];
			);
		);
		value;
	)
);

[#5]

The first script gives the correct answer but the output of both the scripts are not same, why have you commented some part of the code

[#6]

The second version of the code is more efficient. Given the amount of data you are running I thought it would help.  It should provide the same results.  If it does not, you can try just removing all of the Show() statements from the first version of the code, and if that works.