topic Re: Optimize the loop in Discussions

Optimize the loop

Jackie_ — Fri, 09 Jun 2023 17:02:31 GMT

Hi,

I have some JSL code that takes a long time to execute for larger datasets.

The code runs a for loop within a for loop to calculate the mean offset by strips. I’m looking for a faster way of doing this.

Is there a way to make the processing faster? The current script takes ~20 mins to complete the loop

Here's the script below:

Names Default To Here( 1 );

dt1 =Data table( "Reference data table.jmp" );
dt2 = Current data table(); /// Select Data table.jmp
Col_List1 = dt2 << Get Column Names( "String" );

For( c = 1, c <= N Col( dt2 ) -1, c++,
	theMatrix = [];
	name = Column( Col_List1[c] ) << Get name;
	if (Contains( Col_List1[c], "Currents" )| Contains( Col_List1[c], "Voltage" )| Contains( Col_List1[c], "Resistance" ),
	For( r = 1, r <= N Rows( dt2 ), r++,
		mean = dt1:Mean[dt1 << Get Rows Where( :Tests == name )];
		measure = mean - Col Mean( If( dt2:Strips == dt2:Strips[r], Column( dt2, c ), . ) );
		theMatrix = theMatrix || Column( dt2, c )[r] + measure;
	);
	Column( dt2, c ) << Set Values( theMatrix );
););

Re: Optimize the loop

txnelson — Mon, 27 Jun 2022 19:57:51 GMT

The 2 places I see issues, are the

mean = dt1:Mean[dt1 << Get Rows Where( :Tests == name )];

and

measure = mean - Col Mean( If( dt2:Strips == dt2:Strips[r], Column( dt2, c ), . ) );

Here is a work around for the issues Check them out and see if the answers are correct

Names Default To Here( 1 );
start=today();
dt1 =Data table( "Reference data table.jmp" );
dt2 = Current data table(); /// Select Data table.jmp

For(i=1,i<=nrows(dt1),i++,
	theTest = dt1:Tests[i];
	try(column(dt2,theTest) << set property("TheMean", dt1:mean[i]));
);

Col_List1 = dt2 << Get Column Names( "String" );

For( c = 1, c <= N Col( dt2 ) -1, c++,
	theMatrix = [];
	name = Column( Col_List1[c] ) << Get name;
	if (Contains( Col_List1[c], "Currents" )| Contains( Col_List1[c], "Voltage" )| Contains( Col_List1[c], "Resistance" ),
	//For( r = 1, r <= N Rows( dt2 ), r++,
	For Each Row( r=row();
		//mean = dt1:Mean[dt1 << Get Rows Where( :Tests == name )];
		mean = column(dt2,col_list1[c]) << get property("theMean");
		//measure = mean - Col Mean( If( dt2:Strips == dt2:Strips[r], Column( dt2, c ), . ) );
		measure = mean - Col Mean(as Column( dt2, c ),:Strips);
		theMatrix = theMatrix || Column( dt2, c )[r] + measure;
	);
	Column( dt2, c ) << Set Values( theMatrix );
););
show((start-today())/60);

Re: Optimize the loop

ErraticAttack — Mon, 27 Jun 2022 22:14:38 GMT

@Jackie_ , I'm not sure what the conditional dt2:Strips == dt2Strips[r] is supposed to resolve to, but it is probably not doing what you want it to do.

Without any test data to test on, here is how I would start optimizing this -- the main one is to take the dt1 << Get Rows Where( :Tests == name ) outside of the loop.

Names Default to Here( 1 );

dt1 = Data Table( "Reference Data Table.jmp" );
dt2 = Current Data Table();

col list1 = dt2 << Get Column Names( "String" );

Summation( c = 1, N Col( dt2 ) - 1,  //summation is faster that For() if you don't need to use Break() or Continue()
	values = [];
	name = Column( dt2, c ) << Get Name;
	If( Contains( name, "Currents" ) | Contains( name, "Voltage" ) | Contains( name, "Resistance" ),
		rows = dt1 << Get Rows Where( :tests == name );		// As Constant() prevents JMP from doing a lookup per row
		col = Column( dt2, c );
		col mean = Col Mean( col );
		For Each Row( dt2,
			mean = dt1:Mean[rows];
			measure = mean - If( dt2:Strips == dt2:Strips /* always true */, col mean, 0 );
			values |/= col[] + measure 	// append value to
		);
		col << Set Values( values )
	);
	0	// summation needs to end in a numeric
)

Re: Optimize the loop

Jackie_ — Mon, 27 Jun 2022 22:57:47 GMT

Thank Jim. It works like charm

Re: Optimize the loop

Jackie_ — Tue, 26 Jul 2022 22:44:48 GMT

Hi @txnelson,

There's some issue in the logic that you suggested. If the reference data table doesn't contain the test that are in the main data table, it will delete the row values from those tests. I only to offset the test that are in the reference data table. Any help would be much appreciated

I have attached the data tables below. I only want to offset the tests that are specified in the reference table

Re: Optimize the loop

txnelson — Wed, 27 Jul 2022 00:32:47 GMT

I modified your code to bypass the processing if the Column Property "theMean" had not been set

Names Default To Here( 1 );
start = Today();
dt1 = Data Table( "Reference data table.jmp" );
dt2 = Current Data Table(); /// Select Data table.jmp

For( i = 1, i <= N Rows( dt1 ), i++,
	theTest = dt1:Tests[i];
	Try( Column( dt2, theTest ) << set property( "TheMean", dt1:mean[i] ) );
);

Col_List1 = dt2 << Get Column Names( "String" );

For( c = 1, c <= N Col( dt2 ) - 1, c++,
	theMatrix = [];
	name = Column( Col_List1[c] ) << Get name;
	If( Contains( Col_List1[c], "Currents" ) | Contains( Col_List1[c], "Voltage" ) | Contains( Col_List1[c], "Resistance" ), 
	//For( r = 1, r <= N Rows( dt2 ), r++,
		mean = .;
		Try( mean = Column( dt2, col_list1[c] ) << get property( "theMean" ) );
		// Don't process if a value for theMean does not exist
		If( Is Missing( mean ) == 0,
			For Each Row(
				r = Row();
		//mean = dt1:Mean[dt1 << Get Rows Where( :Tests == name )];
		
				//measure = mean - Col Mean( If( dt2:Strips == dt2:Strips[r], Column( dt2, c ), . ) );
				measure = mean - Col Mean( As Column( dt2, c ), :Strips );
				theMatrix = theMatrix || Column( dt2, c )[r] + measure;
			);
			Column( dt2, c ) << Set Values( theMatrix );
		);
	);
);
Show( (start - Today()) / 60 );

Re: Optimize the loop

Jackie_ — Wed, 27 Jul 2022 13:16:14 GMT

Thanks a lot Jim.