nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to find the average of samples at random intervals?

jojmp

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:

temp.csv

Accepted answers

txnelson

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;
	)
);

All comments

txnelson

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=.
		)
	)
);

jojmp

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?

txnelson

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;
	)
);

jojmp

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